| 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/13940605/Lab08/ |
Upload File : |
* (asterisk) to denote a primary key field @ (at sign) to denote a foreign key field ^ (caret) to denote a unique field that is not a primary key UNF: employeework (employeeId*, name, SSN, projectWork, spouseName, spouseSSN, spouseIsInsured) 1NF: employee (employeeID*, empSSN*, empFirstName, empLastName) projectWork (employeeID*@, projectCode*, projectName*, date*, hoursworked) spouse (empSSN*@, spouseSSN*, spouseFirstName, spouseLastName, spouseIsInsured) Fields 'name', 'projectWork', and 'spouseName' all have multiple values which violates 1NF. I created the 'employee' table which has the composite primary keys of 'employeeID' and 'empSSN'. There is also the 'empFirstName' and 'empLastName' fields. The 'projectWork' table splits up into the fields of 'employeeID', 'projectCode', 'projectName', 'date', and 'hoursworked'. 'employeeID', 'projectCode', 'projectName' and 'date' are all composite primary keys. 'employeeID' is also foreign, relating to the 'employee' table. The 'spouse' table starts with 'empID' primary foreign key. This is to retain the relation between the employee and their spouse. I then have the 'spouseSSN' as a composite primary key. After that is all the data about the spouse- 'spouseFirstName', 'spouseLastName', and 'spouseIsInsured'. 2NF: OK 3NF: project (projectCode*, projectName*) employee (empID*, empSSN*, empFirstName, empLastName) projectWork (empID*@, projectCode*@, date*, hoursworked) spouse (empID*@, spSSN*, spFirstName, spLastName, spInsured) I had to make sure that information would not be lost if some data was deleted. To start, I created the 'project' table, which holds the data for 'projectCode' and 'projectName'. This way, the data about projects will not be lost if an employee's data is deleted. I renamed any instances of "employee" to "emp" in my field names for clarity. I renamed any instances of "spouse" to "sp" in my field names for clarity. The rest of my tables and fields remain the same, aside from making 'projectCode' and 'projectName' foreign in the 'projectWork' table, relating back to my new 'project' table. I also removed 'projectName' from the 'projectWork' table as that seemed like unnecessary repetition now that the data is stored in the 'project' table. All data rows: project (projectCode*, projectName*) Chr0944, Dining room table chairs Tbl0944, Dining room table Kit0944, Kitchen counters employee (empID*, empSSN*, empFirstName, empLastName) 1, 123456789, Avram, Hinton 2, 345678901, Xerxes, Hayes 3, 456789012, Kristen, Acosta 4, 567890123, Harper, Mcmahon projectWork (empID*@, projectCode*@, 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 spouse (empID*@, spSSN*, spFirstName, spLastName, spInsured) 1, 234567890, Lucy, Merritt, No 2 3, 567890123, Kylee, Acosta, Yes 4