| 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/15510774/Lab08/ |
Upload File : |
UNF: employeework (employeeId*, name, SSN, projectWork, spouseName, spouseSSN, spouseIsInsured) ----------------------------------------------------- 1NF: Violations: - name is a multi-value attribute - spouseName is a multi-value attribute - projectWork is a multi-value attribute Create Fields/Tables: employeework (employeeId*, firstName, lastName, SSN, *code, projectName, date*, hoursworked, spouseFirstName, spouseLastName, spouseSSN, spouseIsInsured) Why: - I changed the name field into two fields - firstName and lastName - so that the individual names could be accessed by the database - I changed the spouseName field into two fields - spouseFirstName and spouseLastName - so that the individial names could be accessed by the database - I added the code, projectName, date, and hoursworked fields into the employeework table so that the projectWork field no longer contained multiple values and was no longer needed - I made the employeeId, code, and date a composite key. By doing this, one employee can work on multiple different projects, and they can also work on the same project multiple times as long as it is entered as a different day Rename: None ----------------------------------------------------- 2NF: Violations: - code and projectName aren't dependent on employeeId or date - spouseFirstName, spouseLastName, spouseSSN, and spouseIsInsured aren't directly dependent on date or code - employeeId, firstName, lastName, and SSN aren't dependent on date or code Create Fields/Tables: employee (employeeId*, firstName, lastName, SSN, spouseFirstName, spouseLastName, spouseSSN, spouseIsInsured) employeework (employeeId*@, code*@, date*, hoursworked) projectId (code*, projectName) Why: - I created an employee table to hold the information that is directly related to the primary key of employeeId. This includes the employee's first and last name, SSN, spouse first and last name, spouseSSN, and whether or not the spouse is insured - I also created a projectId table to hold the information directly related to a specific project. This includes the code and the project name - This leaves the hoursworked to be the only value left in the employeework table because it is dependent on who the employee is, what code (or project) they worked on, and the date they worked on it Rename: None ----------------------------------------------------- 3NF: Violations: - spouse information (or lack of information) is redundant if the employee doesn't have a spouse Create Fields/Tables: employee (employeeId*, firstName, lastName, SSN) employeework (employeeId*@, code*@, date*, hoursworked) projectId (code*, projectName) employeeSpouse (employeeId*@, spouseFirstName, spouseLastName, spouseSSN, spouseIsInsured) Why: - I created an employeeSpouse table so that the excess use of nulls for spouse information would be eliminated if an employee didn't have a spouse Rename: None ----------------------------------------------------- All data rows: employeeId* firstName lastName SSN 1 Avram Hinton 123456789 2 Xerxes Hayes 345678901 3 Kristen Acosta 456789012 4 Harper Mcmahon 567890123 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 code* projectName Chr0944 Dining room table chairs Tbl0944 Dining room table Kit0944 Kitchen counters employeeId*@ spouseFirstName spouseLastName sposueSSN spouseIsInsured 1 Lucy Merritt 234567890 No 3 Kylee Acosta 567890123 Yes