| 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/12965044/Lab08/ |
Upload File : |
UNF: employeework (employeeId*, name, SSN, projectWork, spouseName, spouseSSN, spouseIsInsured) employeework projectWork employeeId* name SSN code projectName date hoursWorked spouseName spouseSSN spouseIsInsured 1 Avram Hinton 123456789 Chr0944 Dining Room Table Chairs 2/5/2023 8 Lucy Merritt 234567890 No Tbl0944 Dining Room Table 2/6/2023 6 Tbl0944 Dining Room Table 2/7/2023 6 2 Xerxes Hayes 345678901 Kit0944 Kitchen Counters 2/5/2023 8 Kit0944 Kitchen Counters 2/6/2023 8 Tbl0944 Dining Room Table 2/7/2023 4 3 Kirsten Acosta 456789012 Kit0944 Kitchen Counters 2/5/2023 8 Kylee Acosta 567890123 Yes 4 Harper Mcmahon 567890123 Kit0944 Kitchen Counters 2/5/2023 4 Tbl0944 Dining Room Table 2/5/2023 4 Kit0944 Kitchen Counters 2/4/2023 8 1NF: employeeId* name SSN projectcode projectName projectdate projectHoursWorked spouseName spouseSSN spouseIsInsured 1 Avram Hinton 123456789 Chr0944 Dining Room Table Chairs 2/5/2023 8 Lucy Merritt 234567890 No 1 Avram Hinton 123456789 Tbl0944 Dining Room Table 2/6/2023 6 Lucy Merritt 234567890 No 1 Avram Hinton 123456789 Tbl0944 Dining Room Table 2/7/2023 6 Lucy Merritt 234567890 No 2 Xerxes Hayes 345678901 Kit0944 Kitchen Counters 2/5/2023 8 Null Null Null 2 Xerxes Hayes 345678901 Kit0944 Kitchen Counters 2/6/2023 8 Null Null Null 2 Xerxes Hayes 345678901 Tbl0944 Dining Room Table 2/7/2023 4 Null Null Null 3 Kirsten Acosta 456789012 Kit0944 Kitchen Counters 2/5/2023 8 Kylee Acosta 567890123 Yes 4 Harper Mcmahon 567890123 Kit0944 Kitchen Counters 2/5/2023 4 Null Null Null 4 Harper Mcmahon 567890123 Tbl0944 Dining Room Table 2/5/2023 4 Null Null Null 4 Harper Mcmahon 567890123 Kit0944 Kitchen Counters 2/4/2023 8 For the first normal form, the first step was to break up the "projectWork" data set into it's separate inputs. Then an entry was created for each employee project to satisfy 1NF parameters by removing multiple entries in a single field (particularly the data points for in the projectWork set). 2NF: employeeInfo employeeId* name SSN spouseName spouseSSN spouseIsInsured 1 Avram Hinton 123456789 Lucy Merritt 234567890 No 2 Xerxes Hayes 345678901 Null Null Null 3 Kirsten Acosta 456789012 Kylee Acosta 567890123 Yes 4 Harper Mcmahon 567890123 Null Null Null employeeProjects employeeId*@ projectcode* projectName projectdate projectHoursWorked 1 Chr0944 Dining Room Table Chairs 2/5/2023 8 1 Tbl0944 Dining Room Table 2/6/2023 6 1 Tbl0944 Dining Room Table 2/7/2023 6 2 Kit0944 Kitchen Counters 2/5/2023 8 2 Kit0944 Kitchen Counters 2/6/2023 8 2 Tbl0944 Dining Room Table 2/7/2023 4 3 Kit0944 Kitchen Counters 2/5/2023 8 4 Kit0944 Kitchen Counters 2/5/2023 4 4 Tbl0944 Dining Room Table 2/5/2023 4 4 Kit0944 Kitchen Counters 2/4/2023 8 For the second normal form, the data was split into two tables - the "employeeInfo" table (employeeID*, name, SSN, spouseName, spouseName, spouseIsInsured) and the "employeeProjects"table (employeeID*@, projectCode*, projectName, projectDate, projectHoursWorked). Both of these tables leverage the use of the employeeID (appearing as a foreign key in the employeeProjects table) as a reference point which is easily usable in a query. These tables were broken up to allow a project row without having to enter the employeeInfo fields. 3NF: employeeInfo employeeId* name SSN^ 1 Avram Hinton 123456789 2 Xerxes Hayes 345678901 3 Kirsten Acosta 456789012 4 Harper Mcmahon 567890123 employeeSpouse employeeId*@ spouseName spouseSSN^ spouseIsInsured 1 Lucy Merritt 234567890 No 3 Kylee Acosta 567890123 Yes projectID projectcode* projectName^ Chr0944 Dining Room Table Chairs Tbl0944 Dining Room Table Kit0944 Kitchen Counters employeeProjects employeeId*@ projectcode*@ projectdate projectHoursWorked 1 Chr0944 2/5/2023 8 1 Tbl0944 2/6/2023 6 1 Tbl0944 2/7/2023 6 2 Kit0944 2/5/2023 8 2 Kit0944 2/6/2023 8 2 Tbl0944 2/7/2023 4 3 Kit0944 2/5/2023 8 4 Kit0944 2/5/2023 4 4 Tbl0944 2/5/2023 4 4 Kit0944 2/4/2023 8 For the third normal form, the spouse data was split off from the employeeInfo section as the data does depend on the employeeID but do not directly impact the employee entries. The other change made was creating the "projectID" table to reduce unnecessary data entries in the employeeProject table. All data rows: 1NF: employeeId* name SSN projectcode projectName projectdate projectHoursWorked spouseName spouseSSN spouseIsInsured 1 Avram Hinton 123456789 Chr0944 Dining Room Table Chairs 2/5/2023 8 Lucy Merritt 234567890 No 1 Avram Hinton 123456789 Tbl0944 Dining Room Table 2/6/2023 6 Lucy Merritt 234567890 No 1 Avram Hinton 123456789 Tbl0944 Dining Room Table 2/7/2023 6 Lucy Merritt 234567890 No 2 Xerxes Hayes 345678901 Kit0944 Kitchen Counters 2/5/2023 8 Null Null Null 2 Xerxes Hayes 345678901 Kit0944 Kitchen Counters 2/6/2023 8 Null Null Null 2 Xerxes Hayes 345678901 Tbl0944 Dining Room Table 2/7/2023 4 Null Null Null 3 Kirsten Acosta 456789012 Kit0944 Kitchen Counters 2/5/2023 8 Kylee Acosta 567890123 Yes 4 Harper Mcmahon 567890123 Kit0944 Kitchen Counters 2/5/2023 4 Null Null Null 4 Harper Mcmahon 567890123 Tbl0944 Dining Room Table 2/5/2023 4 Null Null Null 4 Harper Mcmahon 567890123 Kit0944 Kitchen Counters 2/4/2023 8 2NF: employeeInfo employeeId* name SSN spouseName spouseSSN spouseIsInsured 1 Avram Hinton 123456789 Lucy Merritt 234567890 No 2 Xerxes Hayes 345678901 Null Null Null 3 Kirsten Acosta 456789012 Kylee Acosta 567890123 Yes 4 Harper Mcmahon 567890123 Null Null Null employeeProjects employeeId*@ projectcode* projectName projectdate projectHoursWorked 1 Chr0944 Dining Room Table Chairs 2/5/2023 8 1 Tbl0944 Dining Room Table 2/6/2023 6 1 Tbl0944 Dining Room Table 2/7/2023 6 2 Kit0944 Kitchen Counters 2/5/2023 8 2 Kit0944 Kitchen Counters 2/6/2023 8 2 Tbl0944 Dining Room Table 2/7/2023 4 3 Kit0944 Kitchen Counters 2/5/2023 8 4 Kit0944 Kitchen Counters 2/5/2023 4 4 Tbl0944 Dining Room Table 2/5/2023 4 4 Kit0944 Kitchen Counters 2/4/2023 8 3NF: employeeInfo employeeId* name SSN^ 1 Avram Hinton 123456789 2 Xerxes Hayes 345678901 3 Kirsten Acosta 456789012 4 Harper Mcmahon 567890123 employeeSpouse employeeId*@ spouseName spouseSSN^ spouseIsInsured 1 Lucy Merritt 234567890 No 3 Kylee Acosta 567890123 Yes projectID projectcode* projectName^ Chr0944 Dining Room Table Chairs Tbl0944 Dining Room Table Kit0944 Kitchen Counters employeeProjects employeeId*@ projectcode*@ projectdate projectHoursWorked 1 Chr0944 2/5/2023 8 1 Tbl0944 2/6/2023 6 1 Tbl0944 2/7/2023 6 2 Kit0944 2/5/2023 8 2 Kit0944 2/6/2023 8 2 Tbl0944 2/7/2023 4 3 Kit0944 2/5/2023 8 4 Kit0944 2/5/2023 4 4 Tbl0944 2/5/2023 4 4 Kit0944 2/4/2023 8