| 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/JimMartinson/Classes/CST1600/Labs/ |
Upload File : |
Lab 8 - 2nd Normalization
Grading Criteria
Total possible points: 15
-15 1NF violation. Further grading will not be done.
-3 2NF violation.
-3 3NF violation.
-4 Data loss. (Data not in tables or link between data is lost).
-4 Deletion anomaly.
-3 Insert or update anomaly.
-3 No primary key. You do not have a primary key in this table.
-2 Incorrect primary key.
-2 Duplication of data.
-1 Duplication of table for same data type.
-1 Incorrect foreign key.
-1 Incorrect unique key.
-1 Bad table or field name.
-1 New or renamed field not listed under Table field meanings are section.
-2 Table in Three data rows section does not match table in 3NF section.
-1 Data incorrect in Three data rows section.
There are some things I expect. If they are incorrect you will lose the following:
Work submitted incorrectly: -2 pts. This covers errors such as incorrect files names, incorrect file formats, poor formatting, etc.
Second and subsequent submission of work for grading: -2 pts.
Work submitted late: -2 pts per week.
I reserve the right not to apply the deduction points at my absolute discretion.
-15 You did not normalize the table from Lab 8.
Instead you submitted normalization from the example text file from lab 7.
UNF:
employeework (employeeId*, name, SSN, projectWork, spouseName, spouseSSN, spouseIsInsured)
3NF:
employee (employeeId*, firstname, lastname, SSN)
-5 1NF violation.
The `name` field is non-atomic. It holds a first and last name.
-2 Incorrect primary key.
You should not be using a composite PK.
You could then enter another employee with a duplicate employeeId but a different SSN.
Then employeeId would not work as a FK in the projectWork table.
-2 Incorrect primary key.
You should not be using a composite PK.
With `firstname` and `lastname` as PKs, you could enther another employee with the same `employeeId`.
employeespouse (employeeId*, spouseFirstname, spouseLastname, spouseSSN, spouseIsInsured)
-5 1NF violation.
The spouseName field is non-atomic. It holds a first and last name.
-4 Data loss. Which employee does this spouse belong too?
-1 Incorrect unique key. spouseName and spouseIsInsured cannot be unique.
If spouseIsInsured is unique, there can be only two spouses, one insured and one not.
-1 Incorrect primary key.
The name field should not be used as the PK/FK as an employee name may not be unique.
-2 Incorrect primary key.
With `e_Id` and `s_SSN` as a composite key, an employee could have more than one spouse.
-1 Incorrect primary key.
With `spouseSSN` as the PK, an employee could have more than one spouse.
Either use employeeID as the PK or make it a unique field.
What happens if a spouse divorces one employee and marries another?
employeework (employeeId*@, projectCode*@, workdate*, hoursworked)
-2 Incorrect primary key.
With employeeId as the PK, the employee can only work once.
-2 Incorrect primary key.
With `projectId` and `employeeId` as the composite PK, the employee can only work once on a project.
-4 Deletion anomaly. Deleting an employee could lose all information about a project.
Try making a project table. The a FK to that table would identify the project.
-1 Incorrect unique key.
With `code` being unique, only one work entry can be inserted for each project.
project (projectCode*, projectName)
-4 Data loss. with projectID as the PK, only one date and hoursWorked can be entered per project.
Should date and hoursWorked be moved to the employeeProjects table?
-2 Incorrect primary key.
You should not be using a composite PK.
You could then enter another project with a duplicate projectCode but a different name.
Then projectCode would not work as a FK in the projectWork table.