| 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/20243/CST1600/41/16317500/Lab08/ |
Upload File : |
* (asterisk) to denote a primary key field.
@ (at sign) to denote a foreign key field.
^ (caret) to denote a unique field that is not a primary key.
UNF:
employeework (employeeId*, name, SSN, projectWork, spouseName, spouseSSN, spouseIsInsured)
1NF:
employeeWork(employeeID*, name, SSN, spouseName, spouseSSN, spouseisInsured)
1, Avram Hinton, 123456789, Lucy Merritt, 234567890, No
2, Xerxes Hayes, 345678901,
3, Kristen Acosta, 456789012, Kylee Acosta, 567890123, Yes
4, Harper Mcmahon, 567890123
projectWork(employeeID*@, projectName, code, date, hoursWorked)
1, Dining room table chairs, Chr0944, 2023-02-05, 8
1, Dining room table, Tbl0944, 2023-02-06, 6
1, Dining room table, Tbl0944, 2023-02-07, 6
2, Kitchen counters, Kit0944, 2023-02-05, 8
2, Kitchen counters, Kit0944, 2023-02-06, 8
3, Kitchen counters, Kit0944, 2023-02-05, 8
4, Kitchen counters, Kit0944, 2023-02-05, 4
4, Dining room table chairs, Chr0944, 2023-02-05, 4
4, Kitchen counters, Kit0944, 2023-02-06, 8
1NF notes:
First thing I did was separate the UNF into three tables for ease of looking at the table. Last time when I tried to tackle everything at once it didn't go so well, so this time I'm going to try to actually go through each stage of NF.
2NF:
employee(employeeId*, eFirstName, eLastName, SSN)
1, Avram, Hinton, 123456789
2, Xerxes, Hayes, 345678901
3, Kristen, Acosta, 456789012
4, Harper, Mcmahon, 567890123
spouseInfo(spouseId*, sFirstName, sLastName spouseSSN)
1, Lucy, Merritt, 234567890
2, Kylee, Acosta, 567890123
Employee_Spouse(employeeId*@, spouseId*@, spouseIsInsured,
1, 1, No
3, 2, Yes
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
3, 3, 2023-02-05, 8
4, 3, 2023-02-05, 4
4, 1, 2023-02-05, 4
4, 3, 2023-02-06, 8
ProjectInfo(ProjectID*, ProjectName, code)
1, Dining room table chairs, Chr0944
2, Dining room table, Tbl0944
3, Kitchen counters Kit0944
2NF notes:
I had some help putting this together. I seperated out the spouse and the employee tables before combining the two back together to separate things out more, but couldn't find a good way to separate the ProjectWork table. I did give the projects a specialized ID on noticing that we had two dates with the same amount of hours worked and no good way to separate between dates and project codes. I then went back and moved ProjectID into ProjectInfo, to hopefully fix the 2NF errors present before.
3NF: OK
All data rows:
EmployeeID eFirstName eLastName EmployeeSSN ProjectName Date HoursWorked sFirstName sLastName SpouseSSN SpouseInsured
1 Avram Hinton 123456789 Dining room table chairs 2023-02-05 8 Lucy Merritt 234567890 No
1 Avram Hinton 123456789 Dining room table 2023-02-06 6 Lucy Merritt 234567890 No
1 Avram Hinton 123456789 Dining room table 2023-02-07 6 Lucy Merritt 234567890 No
2 Xerxes Hayes 345678901 Kitchen counters 2023-02-05 8
2 Xerxes Hayes 345678901 Kitchen counters 2023-02-06 8
3 Kristen Acosta 456789012 Kitchen counters 2023-02-05 8 Kylee Acosta 567890123 Yes
4 Harper Mcmahon 567890123 Kitchen counters 2023-02-05 4
4 Harper Mcmahon 567890123 Dining room table chairs 2023-02-05 4
4 Harper Mcmahon 567890123 Kitchen counters 2023-02-06 8