DROP TABLE EMPLOYEE; DROP TABLE DEPENDENT; DROP TABLE DEPT_LOCATIONS; DROP TABLE DEPARTMENT; DROP TABLE WORKS_ON; DROP TABLE PROJECT; CREATE TABLE DEPT_LOCATIONS ( Dnumber smallint not...

DROP TABLE EMPLOYEE;
DROP TABLE DEPENDENT;
DROP TABLE DEPT_LOCATIONS;
DROP TABLE DEPARTMENT;
DROP TABLE WORKS_ON;
DROP TABLE PROJECT;
CREATE TABLE DEPT_LOCATIONS
( Dnumber        smallint    not null    ,
Dlocation         varchar(15)    not null    ,
CONSTRAINT Dept_locations_PK PRIMARY KEY (Dnumber, Dlocation)
);
CREATE TABLE DEPARTMENT
( Dname        varchar(15)            ,
Dnumber        smallint     not null    ,
Mgr_SSN        integer            ,
Mgr_start_date    date                ,
CONSTRAINT Department_PK PRIMARY KEY (Dnumber)
);
CREATE TABLE WORKS_ON
( Essn        integer        not null    ,
Pno        smallint    not null    ,
Hours    decimal(4,2)        ,
CONSTRAINT Works_On_PK PRIMARY KEY (Essn, Pno)
);
CREATE TABLE PROJECT
( Pname        varchar(20)            ,
Pnumber        smallint    not null    ,
Plocation    varchar(20),
Dnum XXXXXXXXXXsmallint,
CONSTRAINT Project_PK PRIMARY KEY (Pnumber)
);
CREATE TABLE DEPENDENT
( Essn        integer    not null    ,
Dependent_Name    varchar(15) not null    ,
Sex            char(1)            ,
Bdate        date                ,
Relationship    varchar(10)            ,
CONSTRAINT Dependent_PK PRIMARY KEY (Essn, Dependent_Name)
);
CREATE TABLE EMPLOYEE
( Fname     varchar(15) not null    ,
Minit    char(1)            ,
Lname    varchar(15) not null    ,
SSN        integer     not null    ,
Bdate    date                ,
Address    varchar(50)            ,
Sex        char(1)            ,
Salary    decimal(8,2)        ,
Super_SSN    integer            ,
Dno        smallint            ,            
CONSTRAINT Employee_PK PRIMARY KEY (SSN),
CONSTRAINT Employee_FK1 FOREIGN KEY (Dno) REFERENCES DEPARTMENT(Dnumber)
);
INSERT INTO DEPARTMENT VALUES ('Research', 5, XXXXXXXXXX, DATE ' XXXXXXXXXX');
INSERT INTO DEPARTMENT VALUES ('Administration', 4, XXXXXXXXXX, DATE ' XXXXXXXXXX');
INSERT INTO DEPARTMENT VALUES ('Headquarters', 1, XXXXXXXXXX, DATE ' XXXXXXXXXX');
INSERT INTO DEPT_LOCATIONS VALUES (1, 'Houston');
INSERT INTO DEPT_LOCATIONS VALUES (4, 'Stafford');
INSERT INTO DEPT_LOCATIONS VALUES (5, 'Bellaire');
INSERT INTO DEPT_LOCATIONS VALUES (5, 'Sugarland');
INSERT INTO DEPT_LOCATIONS VALUES (5, 'Houston');
INSERT INTO WORKS_ON VALUES XXXXXXXXXX, 1, 32.5);
INSERT INTO WORKS_ON VALUES XXXXXXXXXX, 2, 7.5);
INSERT INTO WORKS_ON VALUES XXXXXXXXXX, 3, 40);
INSERT INTO WORKS_ON VALUES XXXXXXXXXX, 1, 20);
INSERT INTO WORKS_ON VALUES XXXXXXXXXX, 2, 20);
INSERT INTO WORKS_ON VALUES XXXXXXXXXX, 2, 10);
INSERT INTO WORKS_ON VALUES XXXXXXXXXX, 3, 10);
INSERT INTO WORKS_ON VALUES XXXXXXXXXX, 10, 10);
INSERT INTO WORKS_ON VALUES XXXXXXXXXX, 20, 10);
INSERT INTO WORKS_ON VALUES XXXXXXXXXX, 30, 30);
INSERT INTO WORKS_ON VALUES XXXXXXXXXX, 10, 10);
INSERT INTO WORKS_ON VALUES XXXXXXXXXX, 10, 35);
INSERT INTO WORKS_ON VALUES XXXXXXXXXX, 30, 5);
INSERT INTO WORKS_ON VALUES XXXXXXXXXX, 30, 20);
INSERT INTO WORKS_ON VALUES XXXXXXXXXX, 20, 15);
INSERT INTO WORKS_ON VALUES XXXXXXXXXX, 20, null);
INSERT INTO PROJECT VALUES ('ProductX', 1, 'Bellaire', 5);
INSERT INTO PROJECT VALUES ('ProductY', 2, 'Sugarland', 5);
INSERT INTO PROJECT VALUES ('ProductZ', 3, 'Houston', 5);
INSERT INTO PROJECT VALUES ('Computerization', 10, 'Stafford', 4);
INSERT INTO PROJECT VALUES ('Reorganization', 20, 'Houston', 1);
INSERT INTO PROJECT VALUES ('Newbenefits', 30, 'Stafford', 4);
INSERT INTO DEPENDENT VALUES XXXXXXXXXX, 'Alice', 'F', DATE ' XXXXXXXXXX', 'Daughter');
INSERT INTO DEPENDENT VALUES XXXXXXXXXX, 'Theodore', 'M', DATE ' XXXXXXXXXX', 'Son');
INSERT INTO DEPENDENT VALUES XXXXXXXXXX, 'Joy', 'F', DATE ' XXXXXXXXXX', 'Spouse');
INSERT INTO DEPENDENT VALUES XXXXXXXXXX, 'Abner', 'M', DATE ' XXXXXXXXXX', 'Spouse');
INSERT INTO DEPENDENT VALUES XXXXXXXXXX, 'Michael', 'M', DATE ' XXXXXXXXXX', 'Son');
INSERT INTO DEPENDENT VALUES XXXXXXXXXX, 'Alice', 'F', DATE ' XXXXXXXXXX', 'Daughter');
INSERT INTO DEPENDENT VALUES XXXXXXXXXX, 'Elizabeth', 'F', DATE ' XXXXXXXXXX', 'Spouse');
INSERT INTO EMPLOYEE VALUES ('John', 'B', 'Smith', XXXXXXXXXX, DATE ' XXXXXXXXXX', '731 Fondren, Houston, TX', 'M', 30000, XXXXXXXXXX, 5);
INSERT INTO EMPLOYEE VALUES ('Franklin', 'T', 'Wong', XXXXXXXXXX, DATE ' XXXXXXXXXX', '638 Voss, Houston, TX', 'M', 40000, XXXXXXXXXX, 5);
INSERT INTO EMPLOYEE VALUES ('Alicia', 'J', 'Zelaya', XXXXXXXXXX, DATE ' XXXXXXXXXX', '3321 Castle, Spring, TX', 'F', 25000, XXXXXXXXXX, 4);
INSERT INTO EMPLOYEE VALUES ('Jennifer','S','Wallace', XXXXXXXXXX, DATE ' XXXXXXXXXX', '291 Be
y, Bellaire, TX', 'F', 43000, XXXXXXXXXX, 4);
INSERT INTO EMPLOYEE VALUES ('Ramesh','K', 'Narayan', XXXXXXXXXX, DATE ' XXXXXXXXXX','975 Fire Oak, Humble, TX','M',38000, XXXXXXXXXX, 5);
INSERT INTO EMPLOYEE VALUES ('Joyce', 'A', 'English', XXXXXXXXXX, DATE ' XXXXXXXXXX', '5631 Rice, Houston, TX', 'F', 25000, XXXXXXXXXX, 5);
INSERT INTO EMPLOYEE VALUES ('Ahmad', 'V', 'Ja
ar', XXXXXXXXXX, DATE ' XXXXXXXXXX', '980 Dallas, Houston, TX', 'M', 25000, XXXXXXXXXX, 4);
INSERT INTO EMPLOYEE VALUES ('James', 'E', 'Borg', XXXXXXXXXX, DATE ' XXXXXXXXXX', '450 Stone, Houston, TX', 'M', 55000, null, 1);
COMMIT;
COMMIT;

DROP TABLE EMPLOYEE;
DROP TABLE DEPENDENT;
DROP TABLE DEPT_LOCATIONS;
DROP TABLE DEPARTMENT;
DROP TABLE WORKS_ON;
DROP TABLE PROJECT;
CREATE TABLE DEPT_LOCATIONS
( Dnumber        smallint    not null    ,
Dlocation         varchar(15)    not null    ,
CONSTRAINT Dept_locations_PK PRIMARY KEY (Dnumber, Dlocation)
);
CREATE TABLE DEPARTMENT
( Dname        varchar(15)            ,
Dnumber        smallint     not null    ,
Mgr_SSN        integer            ,
Mgr_start_date    date                ,
CONSTRAINT Department_PK PRIMARY KEY (Dnumber)
);
CREATE TABLE WORKS_ON
( Essn        integer        not null    ,
Pno        smallint    not null    ,
Hours    decimal(4,2)        ,
CONSTRAINT Works_On_PK PRIMARY KEY (Essn, Pno)
);
CREATE TABLE PROJECT
( Pname        varchar(20)            ,
Pnumber        smallint    not null    ,
Plocation    varchar(20),
Dnum XXXXXXXXXXsmallint,
CONSTRAINT Project_PK PRIMARY KEY (Pnumber)
);
CREATE TABLE DEPENDENT
( Essn        integer    not null    ,
Dependent_Name    varchar(15) not null    ,
Sex            char(1)            ,
Bdate        date                ,
Relationship    varchar(10)            ,
CONSTRAINT Dependent_PK PRIMARY KEY (Essn, Dependent_Name)
);
CREATE TABLE EMPLOYEE
( Fname     varchar(15) not null    ,
Minit    char(1)            ,
Lname    varchar(15) not null    ,
SSN        integer     not null    ,
Bdate    date                ,
Address    varchar(50)            ,
Sex        char(1)            ,
Salary    decimal(8,2)        ,
Super_SSN    integer            ,
Dno        smallint            ,            
CONSTRAINT Employee_PK PRIMARY KEY (SSN),
CONSTRAINT Employee_FK1 FOREIGN KEY (Dno) REFERENCES DEPARTMENT(Dnumber)
);
INSERT INTO DEPARTMENT VALUES ('Research', 5, XXXXXXXXXX, DATE ' XXXXXXXXXX');
INSERT INTO DEPARTMENT VALUES ('Administration', 4, XXXXXXXXXX, DATE ' XXXXXXXXXX');
INSERT INTO DEPARTMENT VALUES ('Headquarters', 1, XXXXXXXXXX, DATE ' XXXXXXXXXX');
INSERT INTO DEPT_LOCATIONS VALUES (1, 'Houston');
INSERT INTO DEPT_LOCATIONS VALUES (4, 'Stafford');
INSERT INTO DEPT_LOCATIONS VALUES (5, 'Bellaire');
INSERT INTO DEPT_LOCATIONS VALUES (5, 'Sugarland');
INSERT INTO DEPT_LOCATIONS VALUES (5, 'Houston');
INSERT INTO WORKS_ON VALUES XXXXXXXXXX, 1, 32.5);
INSERT INTO WORKS_ON VALUES XXXXXXXXXX, 2, 7.5);
INSERT INTO WORKS_ON VALUES XXXXXXXXXX, 3, 40);
INSERT INTO WORKS_ON VALUES XXXXXXXXXX, 1, 20);
INSERT INTO WORKS_ON VALUES XXXXXXXXXX, 2, 20);
INSERT INTO WORKS_ON VALUES XXXXXXXXXX, 2, 10);
INSERT INTO WORKS_ON VALUES XXXXXXXXXX, 3, 10);
INSERT INTO WORKS_ON VALUES XXXXXXXXXX, 10, 10);
INSERT INTO WORKS_ON VALUES XXXXXXXXXX, 20, 10);
INSERT INTO WORKS_ON VALUES XXXXXXXXXX, 30, 30);
INSERT INTO WORKS_ON VALUES XXXXXXXXXX, 10, 10);
INSERT INTO WORKS_ON VALUES XXXXXXXXXX, 10, 35);
INSERT INTO WORKS_ON VALUES XXXXXXXXXX, 30, 5);
INSERT INTO WORKS_ON VALUES XXXXXXXXXX, 30, 20);
INSERT INTO WORKS_ON VALUES XXXXXXXXXX, 20, 15);
INSERT INTO WORKS_ON VALUES XXXXXXXXXX, 20, null);
INSERT INTO PROJECT VALUES ('ProductX', 1, 'Bellaire', 5);
INSERT INTO PROJECT VALUES ('ProductY', 2, 'Sugarland', 5);
INSERT INTO PROJECT VALUES ('ProductZ', 3, 'Houston', 5);
INSERT INTO PROJECT VALUES ('Computerization', 10, 'Stafford', 4);
INSERT INTO PROJECT VALUES ('Reorganization', 20, 'Houston', 1);
INSERT INTO PROJECT VALUES ('Newbenefits', 30, 'Stafford', 4);
INSERT INTO DEPENDENT VALUES XXXXXXXXXX, 'Alice', 'F', DATE ' XXXXXXXXXX', 'Daughter');
INSERT INTO DEPENDENT VALUES XXXXXXXXXX, 'Theodore', 'M', DATE ' XXXXXXXXXX', 'Son');
INSERT INTO DEPENDENT VALUES XXXXXXXXXX, 'Joy', 'F', DATE ' XXXXXXXXXX', 'Spouse');
INSERT INTO DEPENDENT VALUES XXXXXXXXXX, 'Abner', 'M', DATE ' XXXXXXXXXX', 'Spouse');
INSERT INTO DEPENDENT VALUES XXXXXXXXXX, 'Michael', 'M', DATE ' XXXXXXXXXX', 'Son');
INSERT INTO DEPENDENT VALUES XXXXXXXXXX, 'Alice', 'F', DATE ' XXXXXXXXXX', 'Daughter');
INSERT INTO DEPENDENT VALUES XXXXXXXXXX, 'Elizabeth', 'F', DATE ' XXXXXXXXXX', 'Spouse');
INSERT INTO EMPLOYEE VALUES ('John', 'B', 'Smith', XXXXXXXXXX, DATE ' XXXXXXXXXX', '731 Fondren, Houston, TX', 'M', 30000, XXXXXXXXXX, 5);
INSERT INTO EMPLOYEE VALUES ('Franklin', 'T', 'Wong', XXXXXXXXXX, DATE ' XXXXXXXXXX', '638 Voss, Houston, TX', 'M', 40000, XXXXXXXXXX, 5);
INSERT INTO EMPLOYEE VALUES ('Alicia', 'J', 'Zelaya', XXXXXXXXXX, DATE ' XXXXXXXXXX', '3321 Castle, Spring, TX', 'F', 25000, XXXXXXXXXX, 4);
INSERT INTO EMPLOYEE VALUES ('Jennifer','S','Wallace', XXXXXXXXXX, DATE ' XXXXXXXXXX', '291 Be
y, Bellaire, TX', 'F', 43000, XXXXXXXXXX, 4);
INSERT INTO EMPLOYEE VALUES ('Ramesh','K', 'Narayan', XXXXXXXXXX, DATE ' XXXXXXXXXX','975 Fire Oak, Humble, TX','M',38000, XXXXXXXXXX, 5);
INSERT INTO EMPLOYEE VALUES ('Joyce', 'A', 'English', XXXXXXXXXX, DATE ' XXXXXXXXXX', '5631 Rice, Houston, TX', 'F', 25000, XXXXXXXXXX, 5);
INSERT INTO EMPLOYEE VALUES ('Ahmad', 'V', 'Ja
ar', XXXXXXXXXX, DATE ' XXXXXXXXXX', '980 Dallas, Houston, TX', 'M', 25000, XXXXXXXXXX, 4);
INSERT INTO EMPLOYEE VALUES ('James', 'E', 'Borg', XXXXXXXXXX, DATE ' XXXXXXXXXX', '450 Stone, Houston, TX', 'M', 55000, null, 1);
COMMIT;
COMMIT;

PROJECT QUESTIONS
Your Mission …
Create an executable plan to make the required changes to the company relations database
The plan should include:
· Directions that are step-by-step
· Copy of SQL to run
· Copy of Excel template with formulas
· You may not wholesale export data, drop tables, recreate database, reload tables
Your plan will be judged based on:
    Co
ectness
    40%
    Your solution needs to work and meet the goals of each task.
    User Friendliness
    15%
    Others may need to execute your plan, make sure your documentation is easy to follow.
    Repeatability
    20%
    Think about if you had to do this process every month - is the process set up to be repeatable?
    Scalability
    20%
    Think of our Company Relations as a sample.  Your process should work just as well even if there are thousands of employees.
    Professional
    5%
    I'm picky - spelling, grammar, pagination, indenting - it all counts :-)
 
List of Required Changes:
3. Replace SSN throughout the database, use a su
ogate key instead.  Create a new table to store the SSN and its mapping to the appropriate su
ogate key.
4. Delete the Department Location Table - Pretty straightforward!
5. Add a project description to Project and populate with some made-up data.
6. Bring data cu
ent –Increase age by 25 (Employees & Dependents), Increase Salary by 50K
7. Add date to Works On table, convert existing data to daily instead of weekly. Figure out a PK.  Cu
ently the Works On table captures the hours worked for a week.  For the existing data, set the date to  " XXXXXXXXXX" and set the hours to the cu
ent hours / 5. 
8. Add last name to Dependent, seed with the employee’s last name.  You can do this entirely through one SQL statement (after you ALTER the table structure). 
9. Add all applicable PK and FK constraints based on the new database design.  Note - during this step and other steps - you may need to delete cu
ent constraints on tables in order to restructure the tables.  At the end of the day though, make sure all tables have the appropriate constraints.
10. Add at least one of each of the following constraints: CHECK, UNIQUE, NOT NULL.
11. Add more data to these tables – I supply this data below.
 
 
Three new employees have joined the company. Add these employees to your updated database.
New Employee
Name: Janis Jette
SSN: XXXXXXXXXX
Date of Birth: XXXXXXXXXX
Address: 2134 Reed Street, Bellaire, TX Gender: F
Salary: 75,000
Supervisor: Jennifer Wallace Department: 4
Projects: 1 and 2 TimeCard:
20. 2/20  P1: 2
21. 2/21  P1: 8
22. 2/22  P1: 7
23. 2/23  P1: 3
24. 2/24  P1: 1
P2: 8 P2: 0 P2: 1 P2: 5 P2: 7
Dependents: Spouse: Je
y Jetter Son: Steve Kelly Son: Ro
ie Kelly Son: Charlie Kelly
New Employee
Name: Calvin Kool
SSN: XXXXXXXXXX
Date of Birth: XXXXXXXXXX
Address: 2134 Connor Street, Bellaire, TX Gender: M
Salary: 78,000
Supervisor: Jennifer Wallace Department: 4
Projects: 2 TimeCard:
20. 2/20  P2: 8
21. 2/21  P2: 8
22. 2/22  P2: 8
23. 2/23  P2: 8
2/24 P2: 8
Dependents: Spouse: Katrina Kool
New Employee
Name: Te
y M Am
ose
SSN: XXXXXXXXXX
Date of Birth: XXXXXXXXXX
Address: 2345 Pike Street, Houston, TX Gender: M
Salary: 105,000 Supervisor: James Borg Department: 1
Projects: 20, 2 TimeCard:
20. 2/20  P20: 3
21. 2/21  P20: 4
22. 2/22  P20: 7
23. 2/23  P20: 2
24. 2/24  P20: 8
Dependents: No dependents
P2: 8 P2: 8 P2: 6 P2: 4 P2: 5
Q. What is the best way to update the age of employees and dependents?  Do we need a column for age?
A. For the age task,  you'll want to change the actual birth date of the employee and the dependents.  The trouble of adding a column for age is it would need to be constantly updated to be accurate.
Now, this task is a little out of character for the project as having a birth date for each employee makes perfect sense.  This task is more about
inging our data cu
ent (which you wouldn't really do of course in real