| 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/20255/CST1600/41/14904400/Lab08/ |
Upload File : |
UNF:
employeework (employeeId*, name, SSN, projectWork, spouseName, spouseSSN, spouseIsInsured)
employeeId*, name, SSN, projectWork, spouseName, spouseSSN, spouseIsInsured
1, Avram Hinton, 123456789, code; projectName; date; hoursworked, Lucy Merritt, 234567890, No
Chr0944; Dining room table chairs; 2023-02-05; 8
Tbl0944; Dining room table; 2023-02-06; 6
Tbl0944; Dining room table; 2023-02-07; 6
2, Xerxes Hayes, 345678901, code; name; date; hoursworked
Kit0944; Kitchen counters; 2023-02-05; 8
Kit0944; Kitchen counters; 2023-02-06; 8
Tbl0944; Dining room table; 2023-02-07; 4
3, Kristen Acosta, 456789012, code; name; date; hoursworked, Kylee Acosta, 567890123, Yes
Kit0944; Kitchen counters; 2023-02-05; 8
4, Harper Mcmahon, 567890123, code; name; date; hoursworked
Kit0944; Kitchen counters; 2023-02-05; 4
Chr0944; Dining room table chairs; 2023-02-05; 4
Kit0944; Kitchen counters; 2023-02-06; 8
==1NF:A relation is said to be in 1NF (first normal form), if it doesn’t contain any multi-valued attribute. In other words you can say that a relation is in 1NF if each attribute contains only atomic(single) value only.
!Violation! projectWork is violating atomicity.
Create new table:
-Table: EmployeeProjects
*employeeId@ *projectCode projectName *date hoursWorked
@1 *Chr0944 Dining room table chairs *2023-02-05 8
@1 *Tbl0944 Dining room table *2023-02-06 6
@1 *Tbl0944 Dining room table *2023-02-07 6
@2 *Kit0944 Kitchen counters *2023-02-05 8
@2 *Kit0944 Kitchen counters *2023-02-06 8
@2 *Tbl0944 Dining room table *2023-02-07 4
@3 *Kit0944 Kitchen counters *2023-02-05 8
@4 *Kit0944 Kitchen counters *2023-02-05 4
@4 *Chr0944 Dining room table chairs *2023-02-05 4
@4 *Kit0944 Kitchen counters *2023-02-06 8
- Rename the original table to "Employees" and split First and Lastname (because its good habit i guess) after creating "EmployeeProjects" "Employees" looks like this:
Table: Employees
*employeeId FirstName LastName SSN spouseName spouseSSN spouseIsInsured
*1 Avram 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)
Note- renamed because table no longer contains work and only contains data pertaining to the individual.
>Done 1NF ok<
==2NF: Builds on 1NF by We need to remove redundant data from a table that is being applied to multiple rows. and placing them in separate tables. It requires all non-key attributes to be fully functional on the primary key
!Violation! DISCOVERED partial dependency in EmployeeProjects: projectName only depends on projectCode no depending on full composite key of employeeId, projectCode and date.
-Create new table:
Table: Projects
*projectCode projectName
*Chr0944 Dining room table chairs
*Tbl0944 Dining room table
*Kit0944 Kitchen counters
-EmployeeProjects after "Projects" creation
-Table: EmployeeProjects
*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
>Done 2NF ok<
==3NF: Extends 2NF by ensuring that all non-key attributes are not only fully functional on the primary key but also independent of each other. This eliminates transitive dependency.
?Violation? DETECTED (i think atleast) transitive dependency (not directly dependent on the primary key) in Employees. spouseName and spouseIsInsured depend on spouseSSN and not directly on employeeId. create new table to fix.
Table: Spouses
*spouseSSN spouseName spouseIsInsured
*234567890 Lucy Merritt No
*567890123 Kylee Acosta Yes
-Employees updated
Table: Employees
*employeeId FirstName LastName SSN spouseSSN@[Spouses.spouseSSN]
*1 Avram Hinton 123456789 @234567890
*2 Xerxes Hayes 345678901 (null)
*3 Kristen Acosta 456789012 @567890123
*4 Harper Mcmahon 567890123 (null)
>DONE 3NF ok<
All data rows:
>Table: Employees
*employeeId FirstName LastName SSN spouseSSN@[Spouses.spouseSSN]
*1 Avram Hinton 123456789 @234567890
*2 Xerxes Hayes 345678901 (null)
*3 Kristen Acosta 456789012 @567890123
*4 Harper Mcmahon 567890123 (null)
>Table: Spouses
*spouseSSN spouseName spouseIsInsured
*234567890 Lucy Merritt No
*567890123 Kylee Acosta Yes
>Table: EmployeeProjects
*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
>Table: Projects
*projectCode projectName
*Chr0944 Dining room table chairs
*Tbl0944 Dining room table
*Kit0944 Kitchen counters