| 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/JimMartinson/Classes/CST1600/Labs/ |
Upload File : |
UNF:
employeework (employeeId*, name, SSN, projectWork, spouseName, spouseSSN, spouseIsInsured)
1NF:
name is non-atomic. Split into firstname, lastname.
projectWork is non-atomic. split into projectCode, projectName, workdate, hoursworked.
spouseName is non-atomic. Split into spouseFirstname, spouseLastname.
employeeId can no longer be the PK. It must be a composite of employeeId, projectCode, and workdate.
This causes a problem as you could enter multiple hoursworked from tha same project on the same day.
employeework (employeeId*, firstname, lastname, SSN, projectCode, projectName, workdate, hoursworked, spouseFirstname, spouseLastname, spouseSSN, spouseIsInsured)
2NF:
firstname, lastname, SSN, spouseFirstname, spouseLastname, spouseSSN, spouseIsInsured are only relate to the employeeId.
Create an employee table to hold that data.
Leave only employeeId in the employeework table and make it a FK.
projectName is only related to the projectCode.
Create a project table to hold that data and remove projectName from the employeework table.
Then projectCode is now a FK into the project table.
employee (employeeId*, firstname, lastname, SSN, spouseFirstname, spouseLastname, spouseSSN, spouseIsInsured)
employeework (employeeId*@, projectCode*@, workdate*, hoursworked)
project (projectCode*, projectName)
3NF:
While spouseFirstname, spouseLastname, spouseSSN, spouseIsInsured are related to the employeeId, they may all be nulls.
Create an employeespouse table to hold that data and link it back to the employee via the employeeId,
Remove spouseFirstname, spouseLastname, spouseSSN, spouseIsInsured from the employee table.
employee (employeeId*, firstname, lastname, SSN)
employeespouse (employeeId*, spouseFirstname, spouseLastname, spouseSSN, spouseIsInsured)
employeework (employeeId*@, projectCode*@, workdate*, hoursworked)
project (projectCode*, projectName)
All data rows:
employee (employeeId*, firstname, lastname, SSN)
1 Avram Hinton 123456789
2 Xerxes Hayes 345678901
3 Kristen Acosta 456789012
4 Harper Mcmahon 567890123
employeespouse (employeeId*, spouseFirstname, spouseLastname, spouseSSN, spouseIsInsured)
1 Lucy Merritt 234567890 No
3 Kylee Acosta 567890123 Yes
employeework (employeeId*@, projectCode*@, workdate*, 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
project (projectCode*, projectName)
Chr0944 Dining room table chairs
Tbl0944 Dining room table
Kit0944 Kitchen counters
Original data:
employeeId*, name, SSN, projectWork, spouseName, spouseSSN, spouseIsInsured
1, Avram Hinton, 123456789, code; name; date; hoursworked, Lucy Merritt, 234567890, No
Chr0944; Dining room table chairs; 2023-02-05; 8
Tbl0944; Dining room table; 2023-02-06; 6
Tbl0944; Dining room table; 2023-02-07; 6
2, Xerxes Hayes, 345678901, code; name; date; hoursworked
Kit0944; Kitchen counters; 2023-02-05; 8
Kit0944; Kitchen counters; 2023-02-06; 8
Tbl0944; Dining room table; 2023-02-07; 4
3, Kristen Acosta, 456789012, code; name; date; hoursworked, Kylee Acosta, 567890123, Yes
Kit0944; Kitchen counters; 2023-02-05; 8
4, Harper Mcmahon, 567890123, code; name; date; hoursworked
Kit0944; Kitchen counters; 2023-02-05; 4
Chr0944; Dining room table chairs; 2023-02-05; 4
Kit0944; Kitchen counters; 2023-02-06; 8