| 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/13940605/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:
student (studentID*, firstName, lastName)
student_phone (studentID*@, *number, type)
student_major (studentID*@, MajorCode*)
'HomeMobilePhoneNumbers' violates 1NF by having multiple values in some rows.
I created a 'student', 'student_phone' and a 'student_major' table.
I determined the primary key to be 'studentID'.
The 'studentID' field in 'student' is the primary field. This is followed by 'firstName' and 'lastName', which I split from my original field of 'Name' so that they are atomic.
The table named 'student_phone' includes the 'studentID' as the primary field, but it is referencing 'student' so it is foreign. In this table I also include their phone 'number' as a composite primary key. This way multiple numbers can be entered per student. I also have the phone 'type' (referencing home vs mobile).
The 'student_major' table also has 'studentID' as a primary field, but it is referencing the 'student' table so it is also a foreign key. I then made a new field named 'MajorCode' which can hold MajorCode 1, 2 or 3 for each individual student. This is a composite primary key so that multiple majors may be assigned to a student. These together represent 'major'.
2NF:
OK
3NF:
student (studentID*, firstName, lastName)
student_phone (studentID*@, number*, type)
student_major (studentID*@, MajorCode*@)
major (MajorCode*)
'MajorCode' needs to be stored inside a table so that the existence of the data on majors is not lost if a student is deleted. For this reason I created a new table named 'majors' that consists solely of the data in 'MajorCode'. The field 'MajorCode' in 'student_major' must then be a foreign composite primary field, as it is now referring to the primary field 'MajorCode in table 'major'.
Three data rows:
student (studentID*, firstName, lastName)
34673656, Avram, Hinton
83681840, Hayes, Gibson
94086534, Kristen, Acosta
student_phone (studentID*@, number*, type)
34673656, 103-391-7811, home
34673656, 676-683-6301, mobile
83681840, 447-149-5334, home
student_major (studentID*@, MajorCode*@)
34673656, NSA
34673656, CSS
34673656, CST
major (MajorCode*)
NSA
CSS
CST