| 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 7 - 1st Normalization
Grading Criteria
Total possible points: 15
-15 1NF violation with multiple values in a field. Further grading will not be done.
-5 1NF violation using multiple fields in a table for the same data type.
-5 1NF violation using multiple tables for the same data type.
See 1NF.txt for examples of how to resolve 1NF violations.
-4 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.
-2 Incorrect primary key.
-2 Duplication of data. (not covered by an insert or update anomaly)
-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 changes 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 7.
Instead you submitted normalization from the example text file.
-1.5 Work submitted late.
-15 Your CST1600_NevaehTimm_Lab07.txt is not a .txt (text) file, it is a .rtf (rich text format) with a .txt extension.
Please convert/save the file in .txt format and re-submit the lab.
=e
Table changes:
New table/field name Original name Description
-1 You are missing the sub headings for table changes:
New table/field name Original name Description
-1 You do not have a xxx table defined.
-1 You still used the xxx field in the yyy table below.
-1 You still used the xxx field in the yyy and zzz tables below.
-1 There was no xxx field used in your tables.
-2 Data duplication.
You are storing the Name in two tables.
3NF:
OK.
-1 Incorrect foreign key.
There is no `Student ID` field in any other table and you did not define what `Student ID` was a FK to.
===============================================================================================================
-5 1NF violation.
`name` is non-atomic. It contains a first and last name.
It should be split into two fields, on for the first name and one for the last name.
-5 1NF violation. The `phone` field contains multiple values.
-5 1NF violation.
The `PhoneNumber1` and `PhoneNumber1` are repeating fields of the same type of data (phone numbers).
You need a linked table to hold a phone number and type.
See https://cst.ridgewater.edu/JimMartinson/CST1600/Resources/1NF.txt for an example.
-5 1NF violation. The `HomePhoneNumber` and `MobilePhoneNumber` tables define repeating groups of data.
You need a linked table to hold a phone number and type.
See https://cst.ridgewater.edu/JimMartinson/CST1600/Resources/1NF.txt for an example.
-5 1NF violation. The `Major1`, `Major2` and `Major3` fields in the `Major` table are repeating groups of data.
-5 1NF violation. The `Major1`, `Major2`, and `Major3` tables define repeating groups of data.
You need a major table and a linked table to hold the StudentID and major.
See http://cst.ridgewater.edu/JimMartinson/CST1600/Resources/1NF.txt for an example of how to resolve this.
-5 1NF violation. The `HomePhone` and `MobilePhone` tables define repeating tables of data.
See http://cst.ridgewater.edu/JimMartinson/CST1600/Resources/1NF.txt for an example of how to resolve this.
-5 1NF violation. The `student_major1`, `student_major2`, and `student_major3` tables define repeating tables of data.
See http://cst.ridgewater.edu/JimMartinson/CST1600/Resources/1NF.txt for an example of how to resolve this.
You really only need one of these tables to store the data.
5 points
=e
-5 1NF violation. The `homephone` and `mobilephone` tables define repeating tables of data.
See http://cst.ridgewater.edu/JimMartinson/CST1600/Resources/1NF.txt for an example of how to resolve this.
You should use a solution like you did for majors but add a field to store the phone type.
10 points
=e
-5 1NF violation. The `HomePhoneNumber` and `MobilePhoneNumber` in the `Students` table are repeating groups of data (phone numbers).
See http://cst.ridgewater.edu/JimMartinson/CST1600/Resources/1NF.txt for an example of how to resolve this.
10 points
=e
-0 Delete anomoly.
Deleting a student could delete the last entry for a major and lose that data.
-4 2NF violation.
The MajotTitles are not dependent on the full primary key.
-1 Improper table name. The `student` and phoneNumber tables contains a space; lines 21 & 22.
-1 Improper field name. The `student ID` fields in the `student` and phoneNumber tables contains a space.
-2 The `student` table has no primary key.
-2 No primary key.
The table must have a primary key. It may be one field or a composite key.
-1 Incorrect primary key.
A primary key should be the simplest field(s) with unique values.
Look at the field meanings from the lab.
Also, names cannot be trusted to be unique. If you look at the entire data set you will find two students with the name`John Smith`.
-1 Incorrect primary key.
Without `MajorID` as part of a composite primary key, a student can only be assigned one major.
-1 Incorrect primary key.
Without MajorCode as part of the composite primary key instead of StudentMajorNumber it is possible to assign a student the same major more than once.
-1 Incorrect primary key.
Without `phoneNumber` as part of a composite primary key only one phone number may be entered per student.
-1 Incorrect primary key.
With only studentID as the PK you can only enter one phone number for each student.
Look into using a composite PK.
-1 Incorrect primary key.
A primary key should be the simplest field(s) with unique values.
Look at the field meanings from the lab.
Also, names cannot be trusted to be unique. If you look at the entire data set you will find two students with the name`John Smith`.
-1 Incorrect primary key.
With StudentID as the only PK you cannot enter a second major for a student.
Look into using a composite PK.
-1 Incorrect foreign key.
The Name field is not a PK is any other table.
-1 Missing foreign key.
Should phoneNumberTypeID be a foreign key?
-1 Missing foreign key.
Should studentID be a foreign key?
-1 Incorrect unique field.
More than one student could have the same phone number.
-1 Missing unique field.
CourseName should be a unique field (CourseName^), otherwise the same major code could be entered again.
-2 Data duplication.
StudentID should also be a foreign key.
-2 Data duplication.
StudentID and MajorCode should also be a foreign keys.
-1 The `PhoneType` table definitions under 3NF and Three data rows do not match.
-1 It looks like you are using the PhoneType field in the `phone` table as a foreign key but is is not defined as one.
It is also hard to tell what table.field it is pointing too as there is no PhoneType field in any other table.
Assuming PhoneType points to PhoneType.PhoneID. then the phone table data looks ok.
-1 Inconsistent naming and table definitions.
The table definitions and names do not match between your 3NF definitions and your data rows.
student vs Student, major(MajorID*,MajorCode^) vs Major(MajorID*,MajorCode^,MajorTitle), etc.
-1 Did not describe new or renamed fields. See step 4.
While I can tell what was changed due to context and content, you were required to document it.
-2 Deletion anomoly.
Without a table to store the `MajorCode`, majors could be lost if a student is deleted.
-2 Data loss. The type of phone number is not recorded.
You need to add a field to record the phone type.
-4 Data loss. The majors that a student has are lost.
You need a one-to-many table to store the student major data in.
-4 Data loss. The 2nd or 3rd majors that a student has are lost.
You need a one-to-many table to store the student major data in.
Three data rows:
-5 Tables in Three data rows section does not match tables in 3NF section.
You did not show the data that would have been in the tables defined under 3NF.
Well done!