Create an executable plan to make the required changes to the company relations database
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 life). For this you can just write an SQL Statement: UPDATE Employee SET BDate = BDate + 25 years You'll have to work out the syntax, and also do something similar for salary.