| 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 : /nginx/html/uploads/20245/CST1600/41/15650461/Lab08/ |
Upload File : |
UNF:
employeework (employeeId*, name, SSN, projectWork, spouseName, spouseSSN, spouseIsInsured)
1NF:
THe first thing we need to do is move employeeId, name, and SSN to a separate table (I'll just call that employee) and also change the name to first and last name. We'll create a separate table for spouse (and we'll call that table employeeSpouse) and their insurance. We'll also have to create new tables for projects because there are repeating entries in those fields. We'll assign a projectId as the primary key in that table and then put the projectCode and projectName. We'll create a projectWork table to tie the employeeId to the projectId with the date and the hours worked.
employee (employeeId*, employeeLastName^, employeeFirstName^, employeeSSN^)
employeeSpouse (employeeId@, spouseSSN*, spouseLastName^, spouseFirstName^, spouseIsInsured^)
project (projectId*, projectCode^, projectName^)
projectWork (employeeId@, projectId@, date^, hoursWorked^)
2NF:
OK
3NF:
OK
All data rows:
employee (employeeId*, employeeLastName^, employeeFirstName^, employeeSSN^)
1, Hinton, Avram, 123456789
2, Hayes, Xerxes, 345678901
3, Acosta, Kristen, 456789012
4, Mcmahon, Harper, 567890123
employeeSpouse (employeeId@, spouseSSN*, spouseLastName^, spouseFirstName^, spouseIsInsured^)
1, 234567890, Merritt, Lucy, No
3, 567890123, Acosta, Kylee, Yes
project (projectId*, projectCode^, projectName^)
1, Chr0944, Dining room table chairs
2, Tbl0944, Dining room table
3, Kit0944, Kitchen counters
projectWork (employeeId@, projectId@, date^, hoursWorked^)
1, 1, 2023-02-05, 8
1, 2, 2023-02-06, 6
1, 2, 2023-02-07, 6
2, 3, 2023-02-05, 8
2, 3, 2023-02-06, 8
2, 2, 2023-02-07, 4
3, 3, 2023-02-05, 8
4, 3, 2023-02-05, 4
4, 1, 2023-02-05, 4
4, 3, 2023-02-06, 8