| Server IP : 134.29.175.74 / Your IP : 216.73.216.160 Web Server : nginx/1.10.2 System : Windows NT CST-WEBSERVER 10.0 build 19045 (Windows 10) i586 User : Administrator ( 0) PHP Version : 7.1.0 Disable Function : NONE MySQL : OFF | cURL : ON | WGET : OFF | Perl : OFF | Python : OFF | Sudo : OFF | Pkexec : OFF Directory : C:/nginx/html/uploads/20245/CST1600/41/16411022/Lab08/ |
Upload File : |
UNF: employeework (employeeId*, name, SSN, projectWork, spouseName, spouseSSN, spouseIsInsured) 1NF: Name, SSN, spouseName, spouseSSN, and spouseIsInsured are all dependant on EmployeeId so I made it the primary key. I also put projectCode, projectName, date, and hoursWorked into a new table named "ProjectWork" with projectCode as the primary key and employeeID as the foreign key linking it to the employee table. Employee table: employeeId*, name, SSN, spouseName, spouseSSN, spouseIsInsured ProjectWork table: employeeId@, projectCode*, projectName, date, hoursWorked 2NF: Added WorkLog table and moved date and hoursWorked to it to remove partial dependicies, employeeId and projectCode are used as foreign keys. Removed employeeID from ProjectWork table to prevent transitive dependancy. Employee table: employeeId*, name, SSN, spouseName, spouseSSN, spouseIsInsured ProjectWork table: projectCode*, projectName, date, hoursWorked WorkLog table: employeeId@, projectCode@, date, hoursWorked 3NF: Added Spouse table with employeeId as a foreign/primary key to remove transitive dependency. Employee table: employeeId*, name, SSN Spouse table: employeeId*@employee, spouseName, spouseSSN, spouseIsInsured ProjectWork table: projectCode*, projectName, date, hoursWorked WorkLog table: employeeId@employee, projectCode@, date, hoursWorked All data rows: Employee table employeeId* name SSN 1 Avram Hinton 123456789 2 Xerxes Hayes 345678901 3 Kristen Acosta 456789012 4 Harper Mcmahon 567890123 Spouse table employeeId*@ spouseName spouseSSN spouseIsInsured 1 Lucy Merritt 234567890 No 3 Kylee Acosta 567890123 Yes Project table projectCode* projectName Chr0944 Dining room table chairs Tbl0944 Dining room table Kit0944 Kitchen counters WorkLog employeeId@ projectCode@ date hoursWorked 1 Chr0944 2023-02-05 8 1 Tbl0944 2023-02-06 6 1 Tbl0944 2023-02-07 6 2 Kit0944 2023-02-05 8 2 Kit0944 2023-02-06 8 2 Tbl0944 2023-02-07 4 3 Kit0944 2023-02-05 8 4 Kit0944 2023-02-05 4 4 Chr0944 2023-02-05 4 4 Kit0944 2023-02-06 8