| 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/20245/CST1600/41/14842474/Lab08/ |
Upload File : |
UNF: employeework (employeeId*, name, SSN, projectWork, spouseName, spouseSSN, spouseIsInsured) 1NF: 1. First I would create a new field called spouseID, each spouse would have a unique ID to identify them. This will make it easier to identidy them instead of constantly using the employeeID. •employeework (employeeId*, name, SSN^, projectWork, spouseName, spouseSSN^, spouseIsInsured, spouseID) 2. Both name and spouseName are holding more than one value so I would split them into employeeFirstName, employeeLastName, spouseFirstName, and spouseLastName respectively. •employeework (employeeId*, employeeFirstName, EmployeeLastName, SSN^, projectWork, spouseFirstName, spouseLastName spouseSSN^, spouseIsInsured, spouseID) 3. I would also move employeeFirstName, employeeLastName into their own tables employee (which also will contain employeeID, SSN and spouseID) and spouseFirstName, spouseLastNameinto their own table called spouse (which will also contain spouseID). Once the tables are made, SSN, spouseID spouseFirstName and spouseLastName and employeeFirstName and employeelastName can be removed from the employeework table which leaves us with: • employeework (employeeId*, projectWork, spouseSSN^, spouseIsInsured) • employee (employeeID*@, employeeFirstName, employeeLastName, spouseID@, SSN^) • spouse (spouseID*, spouseFirstName, spouseLastName) 4. Next I would move spouseSSN and spouseIsInsured to our spouse table. Once the tables are made, spouseSSN and spouseIsInsured can be deleted from the employeework table. • employeework (employeeId*, projectWork) • employee (employeeID*@, employeeFirstName, employeeLastName, spouseID@, SSN^) • spouse (spouseID*, spouseFirstName, spouseLastName, spouseSSN^, spouseIsInsured) 5. projectWork contains more than one value so we are going to make a new table called projectinfo which contains the product code and name. I am also going to rename code to projectCode and name to projectName so they are not so ambiguous. The productCode will be added to the employeework table. The date and hoursworked values will be split into their own values called date and hoursWorked but will remain in the employeework table. Once all the values are transferred and and all values are moved to their respective tables, the projectWork field should be empty and can be deleted. Date, employeeID, and projectCode are now the primary keys in the employeework table • employeework (employeeId*@, projectCode*@, date*, hoursWorked) • employee (employeeID*, employeeFirstName, employeeLastName, spouseID@, SSN^) • spouse (spouseID*, spouseFirstName, spouseLastName, spouseSSN^, spouseIsInsured) • projectinfo (projectCode*, projectName) 6. Finally, I renamed date to projectDate and SSN to employeeSSN just to further clarify. • employeework (employeeId*@, projectCode*@, projectDate*, hoursWorked) • employee (employeeID*, employeeFirstName, employeeLastName, spouseID@, employeeSSN^) • spouse (spouseID*, spouseFirstName, spouseLastName, spouseSSN^, spouseIsInsured) • projectinfo (projectCode*, projectName) 2NF:OK 3NF:OK All data rows: employee (employeeID*, employeeFirstName, employeeLastName, spouseID@, employeeSSN^) 1, Avram, Hinton, ABC, 123456789 2, Xerxes, Hayes, null, 345678901 3, Kristen, Acosta, DEF, 456789012 4, Harper, Mcmahon, null, 567890123 spouse (spouseID*, spouseFirstName, spouseLastName, spouseSSN^, spouseIsInsured) ABC, Lucy, Merritt, 985324584, No DEF, Kylee, Acosta, 567890123, Yes projectinfo (projectCode*, projectName) Chr0944, Dining room table chairs Tbl0944, Dining room table Kit0944, Kitchen counters employeework (employeeId*@, projectCode*@, projectDate*, hoursWorked) 123456789, Chr0944, 2023-02-05; 8 123456789, Tbl0944, 2023-02-06; 6 123456789, Tbl0944, 2023-02-07; 6 345678901, Kit0944, 2023-02-05; 8 345678901, Kit0944, 2023-02-06; 8 345678901, Tbl0944, 2023-02-07; 4 456789012, Kit0944, 2023-02-05; 8 567890123, Kit0944, 2023-02-05; 4 567890123, Chr0944, 2023-02-05; 4 567890123, Kit0944, 2023-02-06; 8