| 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/16322022/Lab08/ |
Upload File : |
Hannen M Kafati Avila RWC 11-29-2023
CST 1600
------------------------------------------------------------------------
UNF:
employeework (employeeId*, name, SSN, projectWork, spouseName, spouseSSN, spouseIsInsured)
1NF:
2NF:
3NF:
All data rows:
===========================================================
Sample Data:
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 - Dinning room table -2023-02-07 -4 | | | |
3 |Kristen Acosta|456789012|code - name - date -hoursworked |Kaylee 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 | | | |
UNF:
employeework (employeeId*, name, SSN, projectWork, spouseName, spouseSSN, spouseIsInsured)
-employeeID* is both a Primary Key and Foreign Key(*@) Throughout the whole NF process
-------------------------------------------------------------------------------------------
1NF:
By removing the repeating groups within a table, I made each field to contain only the atomic values, removing columns and rows, I created 4 new tables, employeeTable, projectTable, employeeProject and spouseTable; This last table helps to avoid null values for employees who are unmarried.
1)employeeTable:
(employeeID*,fistName, lastName, SSN)
2)projectTable:
(projectCode*, projectName, date, hoursWorked)
3)employeeProject
(employeeID@, projectCode@)
4)spouseTable:
(employeeID@*, spouseName, spouseSSN, spouseIsInsured)
----------------
2NF:
I eliminated partial dependencies in the table, each non-prime attribute(name,hoursworked,date) depends on the entire primary key (employeeID),
avoiding issues with partial dependencies.
1)employeeTable:
(employeeID*, fistName, lastName, SSN)
2)projectTable:
(projectCode*, projectName)
3)(renamed it)projectLog:
(employeeID@, projectCode@, date, hoursWorked)
4)spouseTable:
(employeeID@*, spouseName, spouseSSN, spouseIsInsured)
----------------
3NF:
OK, for 3NF there's no further changes I can make to this table, the final 2nf structure ensured data integrity, reducing redundancy, and adhering to normalization.
----------------
ALL data rows:
------------------
1)employeeTable:
(employeeID*, name, SSN)
employeeID* |firstName |lastName| SSN |
------------------------------------------|
1 |Avram |Hinton |123456789|
2 |Xerxes |Hayes |345678901|
3 |Kristen |Acosta |456789012|
4 |Harper |Mcmahon |567890123|
2)projectTable:
(projectCode*, projectName)
projectCode*| projectName |
------------|------------------------|
Chr0944 |Dining room table chairs|
Tbl0944 |Dining room table |
Kit0944 |Kitchen Counters |
3)(renamed it)projectLog:
(employeeID@, projectCode@, date, hoursWorked)
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 |
4)spouseTable:
(employeeID@*, spouseName, spouseSSN, spouseIsInsured)
employeeID* | spouseName | spouseSSN | spouseIsInsured |
------------|----------------|-----------|-----------------|
1 |Lucy Merrit | 234567890 | No |
2 |Kaylee Acosta | 567890123 | Yes |
Hannen M Kafati Avila RWC 11-29-2023
CST 1600
------------------------------------------------------------------------