| 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 : /nginx/html/uploads/20243/CST1600/41/16523677/Lab08/ |
Upload File : |
UNF: employeework (employeeId*, name, SSN, projectWork, spouseName, spouseSSN, spouseIsInsured) 1NF: 1)The employee work table needs to be split up for numerous reasons. One is that some employees may be married while some are not so including the spouse within the employee table will create null fields. The spouse can be linked back to the employee table for employees who are married eliminating the null field. 2)Another issue is that the name field is not in atomic form and it would also allow for better sorting and search capabilities if the name was separated into individual columns. This would apply in both the employee and the spouse tables. 3)The project work field contains information that is specific to a project and most likely it is possible that multiple employees would work on the same projects on the same and/or different days. Also, if an employee was deleted or archived after no longer being employed, some or all of the data on a project would be lost. The projectWork field also currently takes in multiple values which should be split into separate fields for proper indexing. projectWork should be a table and the values would be the fields in that table. 4)The employeework table should be renamed to employee table since it will no longer contain projectWork data in it. The resulting entity tables are: employee, spouse, projectwork employee(employeeID*, employeeFirstName, employeeLastName, SSN) spouse(spouseSSN*, employeeID@, spouseFirstName, spouseLastName, spouseIsInsured) projectWork(code*, employeeID@ name, date, hoursworked) 2NF: 1)The SSN is not needed in the employee table because there is already a unique identifier in this table and also it is most likely that other employees would have access to these tables that would not need to see the SSN. It is not specified if this table is the only data on the employee, but it does not have the birthdate or other important employee information needed for tax purposes so it could be removed from this table as long as the SSN exists somewhere else. 2)For the spouse table, in the assignment instructions it indicates that the spouseIsInsured fields is the full name of the employee's spouse, but this field should be a Yes/No field if the spouse is covered. The spouseName has already been split into two fields. Also, the important information in this field is whether or not an employee's spouse is insured so a yes/no field would be easier to change. 3)The spouse SSN needs to be the primary key because that is something that will not change. For example, in the future a spouse could divorce one employee and marry another so the associated employee id would change but the SSN would not. The resulting entity tables are: employee, spouse, projectwork employee(employeeID*, employeeFirstName, employeeLastName) spouse(spouseSSN*, employeeID@, spouseFirstName, spouseLastName, spouseIsInsured) projectWork(code*, employeeID@, name, date, hoursworked) 3NF: 1)The project work table should really be split into two different tables, one with the project code and name, then another that links the employee, dates, and hours worked to that specific project. The way it is now could still result in null values on specific projects if the employee is removed or archived from the database. The first table should be project and the second could be the projectWork table 2)The name field should be changed to projectName because there are multiple fields that include the word name in the field and it would make it more identifiable. The same should be done with the code field so it is easily identifiable as the projectCode. 3)Because all of the individual employee's hours worked on the same date are totaled together for each project, you could combine the employeeID and date fields together to create a unique id in the projectWork table that could be linked back to the specific project. This would also help in the situation where multiple employees work on the same project. There may be instances where any employee will work on multiple projects on the same day but the combination of the employeeID_date will still also allow for that when paired with the projectCode. The resulting entity tables are: employee, spouse, project, projectwork employee(employeeID*, employeeFirstName, employeeLastName) spouse(spouseSSN*, employeeID@, spouseFirstName, spouseLastName, spouseIsInsured) project(projectCode*, projectName) projectWork(employeeID_date*, projectCode@, hoursworked) All data rows: employee: (employeeId*, employeeFirstName, employeeLastName) 1 Avram Hinton 2 Xerxes Hayes 3 Kristen Acosta 4 Harper Mcmahon spouse (spouseSSN*, employeeID@ spouseFirstName, spouseLastName, spouseIsInsured) 234567890 1 Lucy Merritt No 567890123 2 Kylee Acosta Yes project (projectCode*, projectName) Chr0944 Dining room table chairs Tbl0944 Dining room table Kit0944 Kitchen counters projectWork (employeeID_date*, projectCode@, hoursworked) 1_2023-02-05 Chr0944 8 1_2023-02-06 Tbl0944 6 1_2023-02-07 Tbl0944 6 2_2023-02-05 Kit0944 8 2_2023-02-06 Kit0944 8 2_2023-02-07 Tbl0944 4 3_2023-02-05 Kit0944 8 4_2023-02-05 Kit0944 4 4_2023-02-05 Chr0944 4 4_2023-02-06 Kit0944 8