USING ORACLE DBMS PLEASE*
Please see instructions and required files below.
DROP TABLE EMPLOYEE; DROP TABLE DEPENDENT; DROP TABLE DEPT_LOCATIONS; DROP TABLE DEPARTMENT; DROP TABLE WORKS_ON; DROP TABLE PROJECT; CREATE TABLE DEPT_LOCATIONS ( Dnumbersmallintnot null, Dlocation varchar(15)not null, CONSTRAINT Dept_locations_PK PRIMARY KEY (Dnumber, Dlocation) ); CREATE TABLE DEPARTMENT ( Dnamevarchar(15), Dnumbersmallint not null, Mgr_SSNinteger, Mgr_start_datedate, CONSTRAINT Department_PK PRIMARY KEY (Dnumber) ); CREATE TABLE WORKS_ON ( Essnintegernot null, Pnosmallintnot null, Hoursdecimal(4,2), CONSTRAINT Works_On_PK PRIMARY KEY (Essn, Pno) ); CREATE TABLE PROJECT ( Pnamevarchar(20), Pnumbersmallintnot null, Plocationvarchar(20), Dnum smallint, CONSTRAINT Project_PK PRIMARY KEY (Pnumber) ); CREATE TABLE DEPENDENT ( Essnintegernot null, Dependent_Namevarchar(15) not null, Sexchar(1), Bdatedate, Relationshipvarchar(10), CONSTRAINT Dependent_PK PRIMARY KEY (Essn, Dependent_Name) ); CREATE TABLE EMPLOYEE ( Fname varchar(15) not null, Minitchar(1), Lnamevarchar(15) not null, SSNinteger not null, Bdatedate, Addressvarchar(50), Sexchar(1), Salarydecimal(8,2), Super_SSNinteger, Dnosmallint, CONSTRAINT Employee_PK PRIMARY KEY (SSN), CONSTRAINT Employee_FK1 FOREIGN KEY (Dno) REFERENCES DEPARTMENT(Dnumber) ); INSERT INTO DEPARTMENT VALUES ('Research', 5, 333445555, DATE '1988-05-22'); INSERT INTO DEPARTMENT VALUES ('Administration', 4, 987654321, DATE '1995-01-01'); INSERT INTO DEPARTMENT VALUES ('Headquarters', 1, 888665555, DATE '1981-06-19'); 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 (123456789, 1, 32.5); INSERT INTO WORKS_ON VALUES (123456789, 2, 7.5); INSERT INTO WORKS_ON VALUES (666884444, 3, 40); INSERT INTO WORKS_ON VALUES (453453453, 1, 20); INSERT INTO WORKS_ON VALUES (453453453, 2, 20); INSERT INTO WORKS_ON VALUES (333445555, 2, 10); INSERT INTO WORKS_ON VALUES (333445555, 3, 10); INSERT INTO WORKS_ON VALUES (333445555, 10, 10); INSERT INTO WORKS_ON VALUES (333445555, 20, 10); INSERT INTO WORKS_ON VALUES (999887777, 30, 30); INSERT INTO WORKS_ON VALUES (999887777, 10, 10); INSERT INTO WORKS_ON VALUES (987987987, 10, 35); INSERT INTO WORKS_ON VALUES (987987987, 30, 5); INSERT INTO WORKS_ON VALUES (987654321, 30, 20); INSERT INTO WORKS_ON VALUES (987654321, 20, 15); INSERT INTO WORKS_ON VALUES (888665555, 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 (333445555, 'Alice', 'F', DATE '1986-04-05', 'Daughter'); INSERT INTO DEPENDENT VALUES (333445555, 'Theodore', 'M', DATE '1983-10-25', 'Son'); INSERT INTO DEPENDENT VALUES (333445555, 'Joy', 'F', DATE '1958-05-03', 'Spouse'); INSERT INTO DEPENDENT VALUES (987654321, 'Abner', 'M', DATE '1942-02-28', 'Spouse'); INSERT INTO DEPENDENT VALUES (123456789, 'Michael', 'M', DATE '1988-01-04', 'Son'); INSERT INTO DEPENDENT VALUES (123456789, 'Alice', 'F', DATE '1988-12-30', 'Daughter'); INSERT INTO DEPENDENT VALUES (123456789, 'Elizabeth', 'F', DATE '1967-05-05', 'Spouse'); INSERT INTO EMPLOYEE VALUES ('John', 'B', 'Smith', 123456789, DATE '1965-01-09', '731 Fondren, Houston, TX', 'M', 30000, 333445555, 5); INSERT INTO EMPLOYEE VALUES ('Franklin', 'T', 'Wong', 333445555, DATE '1955-12-08', '638 Voss, Houston, TX', 'M', 40000, 888665555, 5); INSERT INTO EMPLOYEE VALUES ('Alicia', 'J', 'Zelaya', 999887777, DATE '1968-01-09', '3321 Castle, Spring, TX', 'F', 25000, 987654321, 4); INSERT INTO EMPLOYEE VALUES ('Jennifer','S','Wallace', 987654321, DATE '1941-06-20', '291 Berry, Bellaire, TX', 'F', 43000, 888665555, 4); INSERT INTO EMPLOYEE VALUES ('Ramesh','K', 'Narayan', 666884444, DATE '1962-09-15','975 Fire Oak, Humble, TX','M',38000, 333445555, 5); INSERT INTO EMPLOYEE VALUES ('Joyce', 'A', 'English', 453453453, DATE '1972-07-31', '5631 Rice, Houston, TX', 'F', 25000, 333445555, 5); INSERT INTO EMPLOYEE VALUES ('Ahmad', 'V', 'Jabbar', 987987987, DATE '1969-03-29', '980 Dallas, Houston, TX', 'M', 25000, 987654321, 4); INSERT INTO EMPLOYEE VALUES ('James', 'E', 'Borg', 888665555, DATE '1937-11-10', '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 ( Dnumbersmallintnot null, Dlocation varchar(15)not null, CONSTRAINT Dept_locations_PK PRIMARY KEY (Dnumber, Dlocation) ); CREATE TABLE DEPARTMENT ( Dnamevarchar(15), Dnumbersmallint not null, Mgr_SSNinteger, Mgr_start_datedate, CONSTRAINT Department_PK PRIMARY KEY (Dnumber) ); CREATE TABLE WORKS_ON ( Essnintegernot null, Pnosmallintnot null, Hoursdecimal(4,2), CONSTRAINT Works_On_PK PRIMARY KEY (Essn, Pno) ); CREATE TABLE PROJECT ( Pnamevarchar(20), Pnumbersmallintnot null, Plocationvarchar(20), Dnum smallint, CONSTRAINT Project_PK PRIMARY KEY (Pnumber) ); CREATE TABLE DEPENDENT ( Essnintegernot null, Dependent_Namevarchar(15) not null, Sexchar(1), Bdatedate, Relationshipvarchar(10), CONSTRAINT Dependent_PK PRIMARY KEY (Essn, Dependent_Name) ); CREATE TABLE EMPLOYEE ( Fname varchar(15) not null, Minitchar(1), Lnamevarchar(15) not null, SSNinteger not null, Bdatedate, Addressvarchar(50), Sexchar(1), Salarydecimal(8,2), Super_SSNinteger, Dnosmallint, CONSTRAINT Employee_PK PRIMARY KEY (SSN), CONSTRAINT Employee_FK1 FOREIGN KEY (Dno) REFERENCES DEPARTMENT(Dnumber) ); INSERT INTO DEPARTMENT VALUES ('Research', 5, 333445555, DATE '1988-05-22'); INSERT INTO DEPARTMENT VALUES ('Administration', 4, 987654321, DATE '1995-01-01'); INSERT INTO DEPARTMENT VALUES ('Headquarters', 1, 888665555, DATE '1981-06-19'); 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 (123456789, 1, 32.5); INSERT INTO WORKS_ON VALUES (123456789, 2, 7.5); INSERT INTO WORKS_ON VALUES (666884444, 3, 40); INSERT INTO WORKS_ON VALUES (453453453, 1, 20); INSERT INTO WORKS_ON VALUES (453453453, 2, 20); INSERT INTO WORKS_ON VALUES (333445555, 2, 10); INSERT INTO WORKS_ON VALUES (333445555, 3, 10); INSERT INTO WORKS_ON VALUES (333445555, 10, 10); INSERT INTO WORKS_ON VALUES (333445555, 20, 10); INSERT INTO WORKS_ON VALUES (999887777, 30, 30); INSERT INTO WORKS_ON VALUES (999887777, 10, 10); INSERT INTO WORKS_ON VALUES (987987987, 10, 35); INSERT INTO WORKS_ON VALUES (987987987, 30, 5); INSERT INTO WORKS_ON VALUES (987654321, 30, 20); INSERT INTO WORKS_ON VALUES (987654321, 20, 15); INSERT INTO WORKS_ON VALUES (888665555, 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 (333445555, 'Alice', 'F', DATE '1986-04-05', 'Daughter'); INSERT INTO DEPENDENT VALUES (333445555, 'Theodore', 'M', DATE '1983-10-25', 'Son'); INSERT INTO DEPENDENT VALUES (333445555, 'Joy', 'F', DATE '1958-05-03', 'Spouse'); INSERT INTO DEPENDENT VALUES (987654321, 'Abner', 'M', DATE '1942-02-28', 'Spouse'); INSERT INTO DEPENDENT VALUES (123456789, 'Michael', 'M', DATE '1988-01-04', 'Son'); INSERT INTO DEPENDENT VALUES (123456789, 'Alice', 'F', DATE '1988-12-30', 'Daughter'); INSERT INTO DEPENDENT VALUES (123456789, 'Elizabeth', 'F', DATE '1967-05-05', 'Spouse'); INSERT INTO EMPLOYEE VALUES ('John', 'B', 'Smith', 123456789, DATE '1965-01-09', '731 Fondren, Houston, TX', 'M', 30000, 333445555, 5); INSERT INTO EMPLOYEE VALUES ('Franklin', 'T', 'Wong', 333445555, DATE '1955-12-08', '638 Voss, Houston, TX', 'M', 40000, 888665555, 5); INSERT INTO EMPLOYEE VALUES ('Alicia', 'J', 'Zelaya', 999887777, DATE '1968-01-09', '3321 Castle, Spring, TX', 'F', 25000, 987654321, 4); INSERT INTO EMPLOYEE VALUES ('Jennifer','S','Wallace', 987654321, DATE '1941-06-20', '291 Berry, Bellaire, TX', 'F', 43000, 888665555, 4); INSERT INTO EMPLOYEE VALUES ('Ramesh','K', 'Narayan', 666884444, DATE '1962-09-15','975 Fire Oak, Humble, TX','M',38000, 333445555, 5); INSERT INTO EMPLOYEE VALUES ('Joyce', 'A', 'English', 453453453, DATE '1972-07-31', '5631 Rice, Houston, TX', 'F', 25000, 333445555, 5); INSERT INTO EMPLOYEE VALUES ('Ahmad', 'V', 'Jabbar', 987987987, DATE '1969-03-29', '980 Dallas, Houston, TX', 'M', 25000, 987654321, 4); INSERT INTO EMPLOYEE VALUES ('James', 'E', 'Borg', 888665555, DATE '1937-11-10', '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: Correctness 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 surrogate key instead. Create a new table to store the SSN and its mapping to the appropriate surrogate 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 current –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. Currently the Works On table captures the hours worked for a week. For the existing data, set the date to "2-17-2023" and set the hours to the current 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 current 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 Jetter SSN: 666778888 Date of Birth: 1998-04-10 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: Jerry Jetter Son: Steve Kelly Son: Robbie Kelly Son: Charlie Kelly New Employee Name: Calvin Kool SSN: 166161666 Date of Birth: 2001-03-15 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: Terry M Ambrose SSN: 999336666 Date of Birth: 1978-05-05 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 bringing our data current (which you wouldn't really do of course in real