| 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/20253/CST1600/41/16801913/Lab08/ |
Upload File : |
UNF: employeework (employeeId*, name, SSN, projectWork, spouseName, spouseSSN, spouseIsInsured) 1NF: I created 3 new tables from the original emplyeework table. EmployeeTable contains info on the employee, spouseTable contains the spouse's info and the table projectWork contains all the subcategories that made up the original projectWork column. Primary key at this stage is employeeID. employeeTable(employeeID*, name, SSN) spouseTable(employeeID*, spouseName, spouseSSN, spouseIsInsured) projectWork(employeeID*, projectCode, projectName, projectDate, hoursWorked) 2NF: The table projectWork does not meet 2NF. The projectName is not dependent on employeeID. With the data from projectWork I created two tables, codes & hoursWorked. Codes contains the projectCode and the associated names, and uses the projectCode as the primary key. HoursWorked uses the employeeID and projectDate as the primary key and the table contains the corresponding projectCode and the hoursWorked. EmployeeTable and spouseTable meet 2NF. projectWork--->codes(projectCode*, projectName) --->hoursWorked(employeeID+projectDate*, projectCode, hoursWorked 3NF: SpouseTable does not meet 3NF. The insurance status of the spouse isn't directly tied to the employeeID. I created two new tables from spouseTable. The new spouseTable contains the primary key as employeeiID and that is linked to the spouseSSN. The second new table is spouses, this uses the spouseSSN as the primary key with spouseName and spouseIsInsured being the data in the table. The hoursWorked table primary key of employee+projectDate needs to be updated. The -'s in the date were removed. EmployeeTable & codes meet 3NF. employeeTable(employeeID*, name, SSN) spouseTable(employeeID*, spouseName, spouseSSN, spouseIsInsured)--->spouseTable(employeeID*, spouseSSN) --->spouses(spouseSSN*, spouseName, spouseIsInsured) codes(projectCode*, projectName) hoursWorked(employeeID+projectDate*, projectCode, hoursWorked All data rows: employeeTable employeeID* Name SSN 1 Avram Hinton 123456789 2 Xerxes Hayes 345678901 3 Kristen Acosta 456789012 4 Harper Mcmahon 567890123 spouseTable employeeID* spouseSSN 1 234567890 2 3 567890123 4 spouses spouseSSN* spouseName spouseIsInsured 234567890 Lucy Merrit No 567890123 Kylee Acosta Yes codes projectCode* projectName Chr0944 Dining room table chairs Tbl0944 Dining room table Kit0944 Kitchen counters hoursWorked employeeID+projectDate* projectCode hoursWorked 120230205 Chr0944 8 120230206 Tbl0944 6 120230207 Tbl0944 6 220230205 Kit0944 8 220230206 Kit0944 8 220230207 Tbl0944 4 320230205 Kit0944 8 420230205 Kit0944 4 420230205 Chr0944 4 420230206 Kit0944 8