| 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/20255/CST1600/41/15706840/Lab08/ |
Upload File : |
UNF:
employeework (employeeId*, name, SSN, projectWork, spouseName, spouseSSN, spouseIsInsured)
employeeId*, name, SSN, projectWork, spouseName, spouseSSN, spouseIsInsured
1, Avram Hinton, 123456789, code; projectName; 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
1NF:
This database is not in 1NF largely because of the projectWork column, which is holding a separate table inside of it.
To make the database into first normal form, i started with the giant blob in the middle. Fist off I split the project code and names into their own separate table. Next I created a table called shift codes to categorize the individual shifts worked by the employees and hold the data for date and hours worked. I then created the ID tables, one for employees and one for spouses to hold the information on each employee and spouse. Lastly I created a table for the work that was done, assigning each shift to the employee that worked it.
projectCodes (projectCode*, projectName)
shiftCodes (shiftCode*, date, projectCode@, hoursWorked)
employeeIDs (ID*, firstName, lastName, SSN, spouseID@)
spouseIDs (spouseID*, spouseFirstName, spouseLastName, spouseSSN, spouseIsInsured)
work (ID*@, shiftCode@)
2NF:
The tables are in second normal form because there are no composite keys.
3NF:
The tables are in third normal form because it's basically just assigning each type of data to either a code or an ID, and each of these codes and IDs holds only the data that depends solely on the primary key. The last table is also third normal form because the shift depends on who worked it.
All data rows:
projectCodes (projectCode*, projectName)
Chr0944 Dining room table chairs
Tbl0944 Dining room table
Kit0944 Kitchen counters
shiftCodes (shiftCode*, date, projectCode@, hoursWorked)
1 2023-02-05 Chr0944 8
2 2023-02-06 Tbl0944 6
3 2023-02-07 Tbl0944 6
4 2023-02-05 Kit0944 8
5 2023-02-06 Kit0944 8
6 2023-02-07 Tbl0944 4
7 2023-02-05 Kit0944 8
8 2023-02-05 Kit0944 4
9 2023-02-05 Chr0944 4
10 2023-02-06 Kit0944 8
employeeIDs (ID*, firstName, lastName, SSN, spouseID@)
1 Avram Hinton 123456789 1
2 Xerxes Hayes 345678901
3 Kristen Acosta 456789012 2
4 Harper Mcmahon 567890123
spouseIDs (spouseID*, spouseFirstName, spouseLastName, spouseSSN, spouseIsInsured)
1 Lucy Merritt 234567890 No
2 Kylee Acosta 567890123 Yes
work (ID*@, shiftCode@)
1 1
1 2
1 3
2 4
2 5
2 6
3 7
4 8
4 9
4 10