| 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/20245/CST1600/41/14842474/Lab07/ |
Upload File : |
UNF:
student(Name, HomeMobilePhoneNumbers, StudentID, MajorCode1, MajorCode2, MajorCode3)
1NF:
1. Name and HomeMobilePhoneNumbers both contain more than one value in the same row so these will have to change. To combat this I split Name into FirstName, FastName and split HomeMobilePhoneNumbers into HomePhoneNumber and MobilePhoneNumber.
• student(StudentID*, FirstName, LastName, HomePhoneNumber, MobilePhoneNumber, MajorCode1, MajorCode2, MajorCode3)
2. MajorCode1, MajorCode2, and MajorCode3 should should be combined into one field in a table to avoid repeating groups. So here I would create a table called studentmajor. MajorCode1, MajorCode2, and MajorCode3 would be conmbined and now called MajorCode.
• studentmajor(StudentID*, MajorCode*)
3. MajorCode1, MajorCode2, and MajorCode3 can now be removed from the student table. That leaves us with the following:
• student(StudentID*, FirstName, LastName, HomePhoneNumber, MobilePhoneNumber)
• studentmajor(StudentID*@, MajorCode*)
4. I also added a major table that just contains the major code field. This ensures that all MajorCodes are unique.
• student(StudentID*, FirstName, LastName, HomePhoneNumber, MobilePhoneNumber)
• major(MajorCode*)
• studentmajor(StudentID*@, MajorCode*@)
5. I would also construct a new table called studentphonenumber. The studentphonenumber ensures that no student lists a phone number more than once. I created a new field called PhoneType which tells us whether the listed phone number is home or mobile. HomePhoneNumber and MobilePhoneNumber would be combined into PhoneNumber in the new table with the PhoneType denoting whether the phone number came from the HomePhoneNumber column or the MobilePhoneNumber column. Once the new tables are made, you can remove HomePhoneNumber and MobilePhoneNumber from the student table.
• student(StudentID*, FirstName, LastName)
• major(MajorCode*)
• studentmajor(StudentID*@, MajorCode*@)
• studentphonenumber(StudentID*@, PhoneNumber*, PhoneType)
The table is now in 1NF because there are no repeating fields and no field holds more than one value.
2NF:OK
3NF:OK
Three data rows:
student (StudentID*, FirstName, LastName)
34673656, Avram, Hinton
83681840, Hayes, Gibson
94086534, Kristen, Acosta
major (MajorCode*)
NSA
CSS
CST
CP
MAD
studentmajor (StudentID*@, MajorCode*@)
34673656, NSA
34673656, CSS
34673656, CST
83681840, CST
94086534, CP
94086534, MAD
studentphonenumber(StudentID*@, PhoneNumber*, PhoneType)
34673656, 103-391-7811, Home
34673656, 676-683-6301, Mobile
83681840, 447-149-5334, Home
94086534, 972-658-1570, Mobile