| 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/16973452/Lab08/ |
Upload File : |
UNF:
employeework (employeeId*, SSN^, projectWork, spouseName, spouseSSN, spouseIsInsured)
1NF:
Violations in the employeework table:
- The projectwork column values contain sub-tabes (mutiple rows and
columns).
- The name and SpouseName columns are non-atomic and can be broken into
first and last names.
Changes Made:
employeeDetails(employeeId*, firstName, LastName, SSN, spouseFirstName,
spouseLastName, spouseSSN, spouseIsInsured)
projectWork(employeeId*@, code*, projectName, date*, hourseworked)
employeework table:
- No longer used. All data moved to the more appropriately named
employeeDetails and projectWork tables. The data in these tables can be
connected via SQL query and the foreign key relationships.
employeeDetails table:
- New table that contains the following columns from the employeework
table. This is so no data is lost.
- employeeId, Name, SSN, spouseName, spouseSSN spouseIsInsured
- Decomposed the name and SpouseName fields into first and last names so
the columns are atomic.
- employeeId is the primary key for this table so it is 1NF compliant.
projectWork table:
- New table that contains the projectWork column from the employeework table.
- Data in this original column was split into 4 new columns:
- code, projectName, date, hourseworked
- The employeeId column was added to this table.
- The primary key is composed from employeeId, code and date.
- employeeId is a foreign key that connects back to the employeeDetails table.
!!! Important !!! - I considered labeling the SSN fields as unique. However,
because their value creation is outside of the company's control and their can
be errors leading to potential duplicates. I opted NOT to specify these as
unique values even though they **should** be unique. I did not want the
database logic to be breakable by a 3rd party field.
2NF:
Violations in the projectWork table:
- The projectName column is only dependent on the code portion of the
composite primary key.
Changes Made:
employeeDetails(employeeId*, firstName, LastName, SSN, spouseFirstName,
spouseLastName, spouseSSN, spouseIsInsured)
projectWork(employeeId*@, code*@, date*, hourseworked)
project(code*, projectName)
projectWork table:
- removed project name column from table to fix the above violation.
project table:
- Create table that contains code and projectName columns from projectWork
table so no data is lost and to fix the above violation.
- Made code the primary key so this new table is still 1NF compliant.
3NF:
Violations in the employeeDetails table:
- The spouseSSN and spouseIsInsured columns depend on the spouse name
columns which are not primary keys.
Changes Made:
employeeDetails(employeeId*, firstName, LastName, SSN, spouseId@)
spouseDetails(spouseId*, FirstName, LastName, SSN, IsInsured)
projectWork(employeeId*@, code*@, date*, hourseworked)
project(code*, projectName)
employeeDetails table:
- removed the following columns so all remaining columns are dependent on
the primary key.
- spouseFirstName, spouseLastName, spouseSSN, spouseIsInsured
- Added the spouseId column as a foreign key to show the relationship with
the spouseDetails table.
spouseDetails table:
- New table created to contain removed column data from employeedetails
table so no data is lost.
- Renamed columns from employeedetails table, removing "spouse" prefix as
it is now redundant in this table. Their new names are:
- FirstName, LastName, SSN, IsInsured
- Created new primary key spouseId so this table is 1NF compliant.
All data rows:
-- employeeDetails
employeeId*, firstName, LastName, SSN, spouseId@
1, Avram, Hinton, 123456789, 1,
2, Xerxes, Hayes, 345678901, NULL,
3, Kristen, Acosta, 456789012, 3,
4, Harper, Mcmahon, 567890123, NULL,
-- spouseDetails
spouseId*, FirstName, LastName, SSN, IsInsured
1, Lucy, Merritt, 234567890, No
3, Kylee, Acosta, 567890123, Yes
-- projectWork
employeeId*@, code*@, 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
-- project
code*, projectName,
Chr0944, Dining room table chairs,
Tbl0944, Dining room table,
Kit0944, Kitchen counters,