| 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/16899030/Lab08/ |
Upload File : |
UNF: employeework (employeeId*, name, SSN, projectWork, spouseName, spouseSSN, spouseIsInsured) 1NF: employeeID is the primary key so no other PK needs to be created for employeework, I will also NOT mark SSN as a unique field as people can have multiple SSNs associated with their names. 1) SpouseName, SpouseSSN, and SpouseIsInsured violate atomic values becuase not everyone has a spouse, to resolve this I will create a seperate table called spouseInfo, all spouse information will be moved there. 2) data must be segregated properly, the name and spouseName fields do not do this, so I will break them into four seperate fields: empFirstName, empLastName, spouseFirstName, and spouseLastName 3)projectWork has multiple entries, the project code and its name. This violates the rule of atomic values, to solve this I will split these up into segregated fields under the names of code, proejctName, date, and hoursworked. this leaves us with two tables, employeework and spouse_info employeework (employeeId*, empFirstName, empLastName, SSN, code, projectName, date, hoursworked) spouse_info (employeeId*, spouseFirstName, spouseLastName, spouseSSN, spouseIsInsured) 2NF: 1) projectName does not relate to employeeID, but it does relate to the code, and the same applies to date and hours worked. to deal with this, a new table must be created with code as the primary key, I will name this table projectinfo. this leaves us with three tables, employeework, spouse_info, and projectinfo employeework (employeeId*, empFirstName, empLastName, SSN) spouse_info (employeeId*, spouseFirstName, spouseLastName, spouseSSN, spouseIsInsured) projectinfo (employeeId*, code*, projectName, date, hoursworked) 3NF: OK All data rows: employeework (employeeId*, empFirstName, empLastName, SSN) 1, Avram, Hinton, 123456789 2, Xerxes, Hayes, 345678901 3, Kristen, Acosta, 456789012 4, Harper, Mcmahon, 567890123 spouse_info (employeeId*, spouseFirstName, spouseLastName, spouseSSN, spouseIsInsured) 1, Lucy, Merritt, 234567890, No 3, Kylee, Acosta, 567890123, Yes projectinfo (employeeId*, code*, projectName, date, hoursworked) 1, Chr0944, Dining room table chairs, 2023-02-05, 8 1, Tbl0944, Dining room table, 2023-02-06, 6 1, Tbl0944, Dining room table, 2023-02-07, 6 2, Kit0944, Kitchen counters, 2023-02-05, 8 2, Kit0944, Kitchen counters, 2023-02-06, 8 2, Tbl0944, Dining room table, 2023-02-07, 4 3, Kit0944, Kitchen counters, 2023-02-05, 8 4, Kit0944, Kitchen counters, 2023-02-05, 4 4, Chr0944, Dining room table chairs, 2023-02-05, 4 4, Kit0944, Kitchen counters, 2023-02-06, 8