| 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/20253/CST1600/41/16750839/Lab08/ |
Upload File : |
#Note: Primary key: (*) Foreign Key: (@)
UNF:
employeework (employeeId*, name, SSN, projectWork, spouseName, spouseSSN, spouseIsInsured)
1NF:
Note: I first took projectWork and separated it into its different values. For 1NF the values for a category have to be consistent. As given to me projectWork does not fit that standard. As a result I split it into the varying values of projectCode, projectName, date, and hoursWorked.This keeps the consistent data type. I also split it into two different tables. One pertaining to the employee details (employeedetails) and one that focused on the work the employee is doing (employeework). In doing this I not only ensure that the data is more organized, but I also improve data integrity by making the projects separate to the employee details (doesn't pull up a lotta info that's not necessary to the project). This also prepares for further normalization in 2NF and 3NF. I also designated SSN as a unique feild
employeedetails(employeeId*, name, SSN^, spouseName, spouseSSN, spouseIsInsured)
employeework(employeeId@, projectCode*, projectName, date, hoursWorked)
2NF:
Note: 2NF specializes in removing partial dependencies. The project name was only reliant on projectCode, not employeeId. As a result I removed it into its own table (project). This one uses the projectCode as a foreign key in order to list projectName without partial dependencies. I also added a unique feild to projectname in the project table. This is because there are no repeats in this table allowing for each projectName to only appear onece, hence its unique. I also made both employeeId and projectCode (in employeework table) composite primary keys to help with data integrity.
employeedetails(employeeId*, name, SSN^, spouseName, spouseSSN, spouseIsInsured)
project(projectCode@*, projectName^)
employeework(employeeId@*, projectCode@*, date, hoursWorked)
3NF:
Note: Working the tables any further is not needed. Technically the one I used for 2NF would also work for 3NF. I briefly considered a seperate spouse table, BUT that would be unneccesary and not wise as a spouse can easily have a null value. This makes it impossible to give them a primary key, thus the table would have a lot of errors. As a result, I did not rework
employeedetails(employeeId*, name, SSN, spouseName, spouseSSN, spouseIsInsured)
project(projectCode@*, projectName^)
employeework(employeeId@*, projectCode@*, date, hoursWorked)
All data rows:
employeedetails(employeeId*, name, SSN, spouseName, spouseSSN, spouseIsInsured)
1, Avarm Hinton, 123456789, Lucy Merritt, 234567890, no
2, Xerxes Hayes, 345678901, null, null, null
3, Kristen Acosta, 456789012, Kylee Acosta, 567890123, yes
4, Harper Mcmahon, 567890123, null, null, null
project(projectCode@*, projectName^)
Chr0944, Dining room table chairs
Tbl0944, Dining room table
kit0944, Kitchen counters
employeework(employeeId@*, 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