| 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/00193071/Lab08/ |
Upload File : |
UNF: employeework (employeeId*, name, SSN, projectWork, spouseName, spouseSSN, spouseIsInsured) 1NF: 1) Both the Employee and Spouse need to have names seperated into First and Last name columns. "e_" for Employee and "s_" for Spouse. 2) Spouse does not have an ID field, but spouses have a SSN that would work better as unique identifier than a name. The (s_SSN*) is PK for "spouse" table. 3) To link employee to spouse made the "employee_spouse" table with (e_Id*) and (s_SSN*). 4) Did a couple relabels of fields given in the projectWork as they are all pretty generic and could refer to any code or date; code is (p_code), name is (p_name), date is (workDate) 5) Split and relabeled the projectWork into "project_id" and "employee_project" "project_id" that just gives a table about projects and has no data that has anything to do with employees, as projects don't need dates or hours to have a code and name. Can add more projects without affecting any employee. "employee_project" this will link with (p_code*) and (e_Id*) to bring in employee and project with their date and hours. Table: employee (e_Id*, e_Firstname, e_Lastname, e_SSN) spouse ( s_SSN*, s_Firstname, s_Lastname, s_Insured) employee_spouse (e_Id*@, s_SSN*@) project_id (p_code*, p_name) employee_project (e_Id*@, p_code*@, workDate*, hoursworked) 2NF: OK for 2NF 3NF: 1) Added table "spouse_insured" that will give a YES/NO in column if insured and removed that column from "spouse" table. The Spouse can change if they recieve insurance in future. employee (e_Id*, e_Firstname, e_Lastname, e_SSN) spouse ( s_SSN*, s_Firstname, s_Lastname) spouse_insured (s_SSN*@, s_Insured) employee_spouse (e_Id*@, s_SSN*@) project_id (p_code*, p_name) employee_project (e_Id*@, p_code*@, workDate*, hoursworked) All data rows: employee (e_Id*, e_Firstname, e_Lastname, e_SSN) 1 Avram Hinton 123456789 2 Xerxes Hayes 345678901 3 Kristen Acosta 456789012 4 Harper Mcmahon 567890123 spouse ( s_SSN*, s_Firstname, s_Lastname,) 234567890 Lucy Merritt 567890123 Kylee Acosta spouse_insured (s_SSN*@, s_Insured) 234567890 No 567890123 Yes employee_spouse (e_Id*@, s_SSN*@) 1 234567890 3 567890123 project_id (p_code*, p_name) Chr0944 Dining room table chairs Tbl0944 Dining room table Kit0944 Kitchen counters employee_project (e_Id*@, p_code*@, workDate*, 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