Untitled

USE BUDT702_DB_Student_038;

DROP TABLE IF EXISTS [TerpsEnterprise.Assign];
DROP TABLE IF EXISTS [TerpsEnterprise.Apply];
DROP TABLE IF EXISTS [TerpsEnterprise.City];
DROP TABLE IF EXISTS [TerpsEnterprise.Project];
DROP TABLE IF EXISTS [TerpsEnterprise.Skill];
DROP TABLE IF EXISTS [TerpsEnterprise.Marry];
DROP TABLE IF EXISTS [TerpsEnterprise.Employee];
DROP TABLE IF EXISTS [TerpsEnterprise.Department];

CREATE TABLE [TerpsEnterprise.Department] (
    departmentId CHAR(10) NOT NULL,
    departmentName VARCHAR(100) NOT NULL,
    departmentPhone CHAR(15),
    CONSTRAINT pk_Department_departmentId PRIMARY KEY (departmentId)
);

CREATE TABLE [TerpsEnterprise.Employee] (
    employeeId CHAR(10) NOT NULL,
    employeeFirstName VARCHAR(100) NOT NULL,
    employeeLastName VARCHAR(100) NOT NULL,
    employeeDOB DATE NOT NULL,
    departmentId CHAR(10),
    CONSTRAINT pk_Employee_employeeId PRIMARY KEY (employeeId),
    CONSTRAINT fk_Employee_departmentId FOREIGN KEY (departmentId)
        REFERENCES [TerpsEnterprise.Department] (departmentId)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

CREATE TABLE [TerpsEnterprise.Marry] (
    employeeId1 CHAR(10) NOT NULL,
    employeeId2 CHAR(10) NOT NULL,
    dateMarry DATE,
    CONSTRAINT pk_Marry_employeeId1_employeeId2 PRIMARY KEY (employeeId1, employeeId2),
    CONSTRAINT fk_Marry_employeeId1 FOREIGN KEY (employeeId1)
        REFERENCES [TerpsEnterprise.Employee] (employeeId)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION,
    CONSTRAINT fk_Marry_employeeId2 FOREIGN KEY (employeeId2)
        REFERENCES [TerpsEnterprise.Employee] (employeeId)
        ON DELETE NO ACTION
        ON UPDATE NO ACTION
);


CREATE TABLE [TerpsEnterprise.Skill] (
    skillId CHAR(10) NOT NULL,
    skillName VARCHAR(100) NOT NULL,
    CONSTRAINT pk_Skill_skillId PRIMARY KEY (skillId)
);

CREATE TABLE [TerpsEnterprise.Project] (
    projectId CHAR(10) NOT NULL,
    projectName VARCHAR(100) NOT NULL,
    projectEstCost DECIMAL(15, 2) NOT NULL,
    CONSTRAINT pk_Project_projectId PRIMARY KEY (projectId)
);

CREATE TABLE [TerpsEnterprise.City] (
    cityId CHAR(10) NOT NULL,
    cityName VARCHAR(100) NOT NULL,
    cityState VARCHAR(50) NOT NULL,
    cityPopulation INT,
    CONSTRAINT pk_City_cityId PRIMARY KEY (cityId)
);

CREATE TABLE [TerpsEnterprise.Apply] (
    employeeId CHAR(10) NOT NULL,
    skillId CHAR(10) NOT NULL,
    projectId CHAR(10) NOT NULL,
    CONSTRAINT pk_Apply_employeeId_skillId_projectId PRIMARY KEY (employeeId, skillId, projectId),
    CONSTRAINT fk_Apply_employeeId FOREIGN KEY (employeeId)
        REFERENCES [TerpsEnterprise.Employee] (employeeId)
        ON DELETE NO ACTION
        ON UPDATE CASCADE,
    CONSTRAINT fk_Apply_skillId FOREIGN KEY (skillId)
        REFERENCES [TerpsEnterprise.Skill] (skillId)
        ON DELETE NO ACTION
        ON UPDATE CASCADE,
    CONSTRAINT fk_Apply_projectId FOREIGN KEY (projectId)
        REFERENCES [TerpsEnterprise.Project] (projectId)
        ON DELETE NO ACTION
        ON UPDATE CASCADE
);

CREATE TABLE [TerpsEnterprise.Assign] (
    employeeId CHAR(10) NOT NULL,
    projectId CHAR(10) NOT NULL,
    cityId CHAR(10) NOT NULL,
    CONSTRAINT pk_Assign_employeeId_projectId PRIMARY KEY (employeeId, projectId),
    CONSTRAINT fk_Assign_employeeId FOREIGN KEY (employeeId)
        REFERENCES [TerpsEnterprise.Employee] (employeeId)
        ON DELETE NO ACTION
        ON UPDATE CASCADE,
    CONSTRAINT fk_Assign_projectId FOREIGN KEY (projectId)
        REFERENCES [TerpsEnterprise.Project] (projectId)
        ON DELETE NO ACTION
        ON UPDATE CASCADE,
    CONSTRAINT fk_Assign_cityId FOREIGN KEY (cityId)
        REFERENCES [TerpsEnterprise.City] (cityId)
        ON DELETE NO ACTION
        ON UPDATE CASCADE
);


INSERT INTO [TerpsEnterprise.Department] VALUES 
('D01', 'Software Engineer', '2029859182');
INSERT INTO [TerpsEnterprise.Employee] VALUES 
('E01', 'Ameya', 'Shah', '2002-03-18', 'D01');
INSERT INTO [TerpsEnterprise.Employee] VALUES
('E02', 'Disha', 'Savla', '2002-12-12', 'D01');
INSERT INTO [TerpsEnterprise.Marry] VALUES 
('E01', 'E02', '2028-06-15');
INSERT INTO [TerpsEnterprise.Skill] VALUES 
('S01', 'Programming');
INSERT INTO	[TerpsEnterprise.Project] VALUES 
('P01', 'Project BUDT702', 5000000.00);
INSERT INTO [TerpsEnterprise.City] VALUES 
('C01', 'College Park', 'MD', 50000);
INSERT INTO [TerpsEnterprise.Apply] VALUES 
('E01', 'S01', 'P01');
INSERT INTO [TerpsEnterprise.Assign] VALUES 
('E01', 'P01', 'C01');

SELECT * FROM [TerpsEnterprise.Department];
SELECT * FROM [TerpsEnterprise.Employee];
SELECT * FROM [TerpsEnterprise.Marry];
SELECT * FROM [TerpsEnterprise.Skill];
SELECT * FROM [TerpsEnterprise.Project];
SELECT * FROM [TerpsEnterprise.City];
SELECT * FROM [TerpsEnterprise.Apply];
SELECT * FROM [TerpsEnterprise.Assign];