| 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/20243/CST1600/41/16317500/Lab07/ |
Upload File : |
* (asterisk) to denote a primary key field.
@ (at sign) to denote a foreign key field.
^ (caret) to denote a unique field that is not a primary key.
UNF:
student(Name, HomeMobilePhoneNumbers, StudentID, MajorCode1, MajorCode2, MajorCode3)
1NF: Table student violates 1NF: No primary key field, scattered data
student (StudentID*, FirstName, LastName)
34673656, Avram, Hinton
83681840, Hayes, Gibson
94086534, Kristen, Acosta
HomePhoneNumber (StudentID*@, PhoneNumber*)
34673656, 103-391-7811
83681840, 447-149-5334
94086534, 972-658-1570
MobilePhoneNumber (StudentID*@, PhoneNumber*)
34673656, 676-683-6301
Student_Major (StudentID*@, Majorcode*@)
34673656, NSA
34673656, CSS
34673656, CST
83681840, CST
94086534, CP
94086534, MAD
Major (MajorCode*)
NSA
CSS
CP
MAD
1NF notes:
I split up the UNF as much as possible to make things simple. It was mostly breaking things up, which led to the creation of student, student_Phonenumber and Student_major. These are all using data listed except for Major. I created that section to hold the codes for each of the three students majors and to link up to student_major, which links the studentID and Majorcode together rather than having major1, major2 and major3.
Following grading I revised it slightly to split up the phone numbers by home and mobile. I attempted types, but quickly realized that doing it by types would result in four lines rather than three at the bottom- at least each way I could think of it. I also broke up StudentName into FirstName and LastName.
2NF:Okay
student (StudentID*, FirstName, LastName)
34673656, Avram, Hinton
83681840, Hayes, Gibson
94086534, Kristen, Acosta
HomePhoneNumber (StudentID*@, PhoneNumber*)
34673656, 103-391-7811
83681840, 447-149-5334
94086534, 972-658-1570
MobilePhoneNumber (StudentID*@, PhoneNumber*)
34673656, 676-683-6301
Student_Major (StudentID*@, Majorcode*@)
34673656, NSA
34673656, CSS
34673656, CST
83681840, CST
94086534, CP
94086534, MAD
Major (MajorCode*)
NSA
CSS
CP
MAD
2NF notes:
There wasn't much redundancy in the data to begin with.
3NF:Okay
student (StudentID*, FirstName, LastName)
34673656, Avram, Hinton
83681840, Hayes, Gibson
94086534, Kristen, Acosta
HomePhoneNumber (StudentID*@, PhoneNumber*)
34673656, 103-391-7811
83681840, 447-149-5334
94086534, 972-658-1570
MobilePhoneNumber (StudentID*@, PhoneNumber*)
34673656, 676-683-6301
Student_Major (StudentID*@, Majorcode*@)
34673656, NSA
34673656, CSS
34673656, CST
83681840, CST
94086534, CP
94086534, MAD
Major (MajorCode*)
NSA
CSS
CP
MAD
3NF notes:
If anything, splitting Student to Student_PhoneNumber was a mistake on my end. As it turned out, it wasn't a mistake and wasn't done properly.
Three data rows:
Name; HomePhoneNumber; MobilePhoneNumber; 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