| 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/00103226/Lab08/ |
Upload File : |
* (asterisk) to denote a primary key field. @ (at sign) to denote a foreign key field. Append [table.field] if the table and field a foreign key points to is not obvious. ^ (caret) to denote a unique field that is not a primary key. UNF: employeeWork (employeeId*, name, SSN, projectWork, spouseName, spouseSSN, spouseIsInsured) 1NF: employee (employeeId*, firstName, lastName, SSN, spouseID) employeeSpouse (spouseID*, employeeId, spouseFirstName, spouseLastName, spouseSSN) spouseInsurance (spouseID*, spouseIsInsured) projectWork (code*, name) employeeWork (employeeId*, code, date, hoursWorked) As always, name should be separated into First and Last. projectWork needs to be its own table, just like major was in the previous assignment, though it only needs the Code and Name, as the Date and HoursWorked need to be tied to the Employee, hence the making of the employeeWork table. If all of the spouse information was in the Employee table, there could potentially be NULL after NULL after NULL. With creating a new field, spouseID, we can make an employeeSpouse table with the spousal info. If the Employee isn't married, the spouseID will be NULL in that table. If they ARE married, then it links to the employeeSpouse. I created a spouseInsurance table that contains the spouseID and whether or not they are insured. 2NF: OK 3NF: employee (employeeId*, firstName, lastName, SSN, spouseID@) employeeSpouse (spouseID*, employeeId*@, spouseFirstName, spouseLastName, spouseSSN) spouseInsurance (spouseID*@, spouseIsInsured) projectWork (code*, name) employeeWork (employeeId*@, code*@, date, hoursWorked) Within the employeeWork table, the employeeId and code fields are composite primary keys linked to the employee table and projectWork table. SpouseID is the primary key for the employeeSpouse, and employeeID is a foreign key in the same table. spouseID is a foreign key in the employee table and links to spouseID in employee if present. spouseInsurance has a composite primary of spouseID@ and then states whether or not that individual has insurance. All data rows: employee (employeeId*, firstName, lastName, SSN, spouseID@) 1, Avram, Hinton, 123456789, S1 2, Xerxes, Hayes, 345678901, NULL 3, Kristen, Acosta, 456789012, S2 4, Harper, Mcmahon, 567890123, NULL employeeSpouse (spouseID*, employeeId*@, spouseFirstName, spouseLastName, spouseSSN) S1, 1, Lucy, Merritt, 234567890 S2, 3, Kylee, Acosta, 567890123 spouseInsurance (spouseID*@, spouseIsInsured) S1, FALSE S2, TRUE projectWork (code*, name) Chr0944, Dining room table chairs Tbl0944, Dining room table Kit0944, Kitchen counters employeeWork (employeeId*@, code*@, date, hoursWorked) 1, Chr0944, 2023-02-05, 8 1, Tbl0944, 2023-02-06, 6 1, Tbl0944, 2023-02-07, 6 2, Kit0944, 2023-02-05, 8 2, Kit0944, 2023-02-06, 8 2, Tbl0944, 2023-02-07, 4 3, Kit0944, 2023-02-05, 8 4, Kit0944, 2023-02-05, 4 4, Chr0944, 2023-02-05, 4 4, Kit0944, 2023-02-06, 8