| 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) employeeSpouse (employeeId*@, spouseFirstName, spouseLastName, spouseSSN, spouseIsInsured) projectWork (code*, name) employeeWork (workId*, 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. The workId will auto-increment every time any employee works on a project. 2NF: OK 3NF: employee (employeeId*, firstName, lastName, SSN, spouseID@) employeeSpouse (employeeId*@, spouseFirstName, spouseLastName, spouseSSN, spouseIsInsured) projectWork (code*, name) employeeWork (workId*, employeeId@, code@, date, hoursWorked) Within the employeeWork table, the workID is the new Primary Key. EmployeeId and code fields are now separate foreign keys, so an employee can work on the same project multiple times. EmployeeId is the primary key for the employeeSpouse table. All data rows: employee (employeeId*, firstName, lastName, SSN) 1, Avram, Hinton, 123456789 2, Xerxes, Hayes, 345678901 3, Kristen, Acosta, 456789012 4, Harper, Mcmahon, 567890123 employeeSpouse (EmployeeId*@, spouseFirstName, spouseLastName, spouseSSN, spouseIsInsured) 1, Lucy, Merritt, 234567890, FALSE 3, Kylee, Acosta, 567890123, TRUE projectWork (code*, name) Chr0944, Dining room table chairs Tbl0944, Dining room table Kit0944, Kitchen counters employeeWork (workId*, employeeId@, code@, date, hoursWorked) 1 1, Chr0944, 2023-02-05, 8 2 1, Tbl0944, 2023-02-06, 6 3 1, Tbl0944, 2023-02-07, 6 4 2, Kit0944, 2023-02-05, 8 5 2, Kit0944, 2023-02-06, 8 6 2, Tbl0944, 2023-02-07, 4 7 3, Kit0944, 2023-02-05, 8 8 4, Kit0944, 2023-02-05, 4 9 4, Chr0944, 2023-02-05, 4 10 4, Kit0944, 2023-02-06, 8