| 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/Lab07/ |
Upload File : |
UNF:
student(Name, HomeMobilePhoneNumbers, StudentID, MajorCode1, MajorCode2, MajorCode3)
Name HomeMobilePhoneNumbers studentID MajorCode1 MajorCode2 MajorCode3
Avram Hinton 103-391-7811;676-683-6301 34673656 NSA CSS CST
Hayes Gibson 447-149-5334 83681840 CST
Kristen Acosta ;972-658-1570 94086534 CP MAD
1NF:
Violations of 1NF:
- The Name column is not atomic.
- HomeMobilePhoneNumbers column is not atomic.
- Because studentID is not clearly a primary key, there may be duplicate
records in the table.
- General data problem, the 3 MajorCode columns can not encode more than 3
majors.
Updated Table Layout:
student(StudentID*, FirstName, LastName)
student_phone(StudentID*@, PhoneNumber*, PhoneType)
major(StudentID*@, MajorCode*)
Changes made:
- student table:
- Indicated StudentID is a primary key
- Removed non-atomic HomeMobilePhoneNumbers
- Removed MajorCode columns because they are not able to represent all
possible major combinations.
- student_phone table:
- This table was added to contain 1-to-many phone number relations as a
solution to the non-atomic HomeMobilePhoneNumbers column.
- PhoneNumber column contains all home and mobile numbers from the student
table. One phone number per row.
- The PhoneType column encodes if a phone number corresponds to a mobile or
home phone.
- The StudentID column is added as a foreign key to link back to the
student table.
- The table's composite key is made from the StudentID and PhoneNumber
columns.
- major table:
- This table was added to contain 1-to-many major code relations so more
than 3 majors per student could be represented.
- The MajorCode column contains data that was in the MajorCode1, MajorCode2
and MajorCode3 columns.
- The StudentID column is added as a foreign key to connect the table to
the student table.
- The table's composite key is made from the StudentID and MajorCode
columns.
2NF:
Violations of 2NF:
- In the student_phone table, the PhoneType column is only dependent on the
PhoneNumber portion of the primary key and not the StudentID portion.
- Think of this like the phone determines the phone type, not who owns the
phone.
Updated Table Layout:
student(StudentID*, FirstName, LastName)
student_phone(StudentID*@, PhoneNumber*@)
phone(PhoneNumber*, PhoneType)
major(StudentID*@, MajorCode*)
Changes made:
- student_phone table:
- Removed PhoneType column from table because only dependent on the
PhoneNumber portion of the primary key.
- phone table:
- This table is newly created.
- The PhoneNumber column is added as the primary key.
- The PhoneType column contains the information that was in the
student_phone.PhoneType column.
3NF:
OK
Three data rows:
student table:
StudentID*, FirstName, LastName
34673656 Avram Hinton
83681840 Hayes Gibson
94086534 Kristen Acosta
student_phone table:
StudentID*@, PhoneNumber*@
34673656 103-391-7811
34673656 676-683-6301
83681840 447-149-5334
94086534 972-658-1570
phone table:
PhoneNumber*, PhoneType
103-391-7811 Home
676-683-6301 Mobile
447-149-5334 Home
972-658-1570 Mobile
major table:
StudentID*@, MajorCode*
34673656 NSA
34673656 CSS
34673656 CST
83681840 CST
94086534 CP
94086534 MAD