| 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(ProjectID*, employeeId*@, projectName, code, date hoursWorked) 1, 1, Dining room table chairs, Chr0944, 2023-02-05, 8 2, 1, Dining room table, Tbl0944, 2023-02-06, 6 3, 1, Dining room table, Tbl0944, 2023-02-07, 6 4, 2, Kitchen counters, Kit0944, 2023-02-05, 8 5, 2, Kitchen counters, Kit0944, 2023-02-06, 8 6, 3, Kitchen counters, Kit0944, 2023-02-05, 8 7, 4, Kitchen counters, Kit0944, 2023-02-05, 4 8, 4, Dining room table chairs, Chr0944, 2023-02-05, 4 9, 4, Kitchen counters, Kit0944, 2023-02-06, 8 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. 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