| 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/00071476/Lab08/ |
Upload File : |
The employeework table field meanings are:
employeeId is a unique ID number assigned to the employee.
name is the full name of the employee.
SSN is the Social Security Number of the employee.
projectWork is a field that currently takes multiple values related to the work the employee performs.
code is the unique code that is given to a project.
name is the name of the project.
date is the date the employee worked on the project.
hoursworked is the number of hours the employee worked on the project that day.
spouseName is the full name of the employee's spouse (it will be empty/null if the employee is unmarried).
spouseSSN is the Social Security Number of the employee's spouse (it will be empty/null if the employee is unmarried).
spouseIsInsured is the full name of the employee's spouse (it will be empty/null if the employee is unmarried).
Business logic:
If an employee is unmarried, no data is entered in the spouseName, spouseSSN, or spouseIsInsured fielda.
If an employee works on a project multiple times in one day, the hours worked are totaled and only the total hours worked is entered (There will be only one entry per project/date).
Each project code is unique.
UNF:
employeework (employeeId*, name, SSN, projectWork, spouseName, spouseSSN, spouseIsInsured)
1NF:
employee (employeeId*, firstName, lastName, SSN, marriageStatus)
project (projectCode*, projectName)
employeeProject (employeeId@*, projectCode@*, dateWorked, hoursWorked)
employeeSpouse (spouseFirstName, spouseLastName, spouseIsInsured, spouseSSN*, employeeID@*)
Notes:
Created a new table to store employee information, separating it from project and work-related details.
Introduced firstName and lastName columns to store the full name of the employee.
Removed the name column, as we now have separate firstName and lastName columns.
Added marriageStatus column to capture the marital status of the employee.
project (projectCode, projectName)*
Renamed employeework to employeeProject
Created a new table to store project information, separating it from employee and work-related details.
Removed the projectWork column, as we now have a dedicated table for projects.
Added projectName column to capture the name of the project.
employeeProject (employeeId@, projectCode@, dateWorked, hoursWorked)
Created a new table to capture the many-to-many relationship between employees and projects regarding work details.
Removed the projectWork column, as we now have a dedicated table for work-related details.
Introduced dateWorked and hoursWorked columns to store the date an employee worked on a project and the corresponding hours worked.
employeeSpouse (spouseFirstName, spouseLastName, spouseIsInsured, spouseSSN, employeeID@)**
Created a new table to capture information about the spouse of an employee, separating it from employee details.
Removed the spouseName, spouseSSN, and spouseIsInsured columns from the employee table.
Added spouseFirstName, spouseLastName, spouseIsInsured, and spouseSSN columns to store spouse information.
2NF:
employee (employeeId*, firstName, lastName, employeeSSN)
employeeMarriage (employeeId@*, employeeMarriageStatus)
employeeSpouse (spouseFirstName, spouseLastName, spouseIsInsured, spouseSSN*, employeeID@*, employeeMarriageStatus)
project (projectCode*, projectName)
OK
employeeProject (employeeId@*, projectCode@*)
projectWork (employeeId@*, projectCode@*, dateWorked*, hoursWorked)
Notes:
Removed the marriageStatus column from the employee table and created a new table, employeeMarriage, to capture marital status information.
Created employeeMarriage table to store marital status information.
Removed the marriageStatus column from the employee table, as it is now captured in the employeeMarriage table.
3NF:
employee (employeeId*, firstName, lastName, employeeSSN)
OK
employeeMarriage (employeeId@*, employeeMarriageStatus)
employeeSpouseDetails (spouseSSN*, spouseFirstName, spouseLastName, spouseIsInsured)
employeeSpouse (spouseSSN@*, employeeId@)
project (projectCode*, projectName)
OK
employeeProject (employeeId@*, projectCode@*)
OK
projectWork (employeeId@*, projectCode@*, dateWorked*, hoursWorked)
OK
Notes:
Created a new table, employeeSpouseDetails, to store details about the spouse.
The table employeeSpouse now references employeeSpouseDetails using spouseSSN.
Removed employeeMarriageStatus from employeeSpouse as it's uneccessary. If you're a spouse, you're married.
All data rows:
employee (employeeId*, firstName, lastName, employeeSSN)
1 Avram Hinton 123456789
2 Xerxes Hayes 345678901
3 Kristen Acosta 456789012
4 Harper Mcmahon 567890123
employeeMarriage (employeeId@*, employeeMarriageStatus)
1 Married
2 Unmarried
3 Married
4 Unmarried
employeeSpouseDetails (spouseSSN*, spouseFirstName, spouseLastName, spouseIsInsured)
234567890 Lucy Merritt No
567890123 Kylee Acosta Yes
employeeSpouse (spouseSSN@*, employeeId@)
234567890 1
567890123 3
project ( projectCode*, projectName)
Chr0944 Dining room table chairs
Tbl0944 Kitchen counters
Kit0944 Dining room table
employeeProject (employeeId@*, projectCode@*)
1 Chr0944
1 Tbl0944
2 Kit0944
2 Tbl0944
3 Kit0944
4 Kit0944
4 Chr0944
projectWork (employeeId@*, projectCode@*, dateWorked*, 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 Kit0944 2023-02-06 8
4 Chr0944 2023-02-05 4