| 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/16523677/Lab07/ |
Upload File : |
UNF: student(Name, HomeMobilePhoneNumbers, Student ID, MajorCode1, MajorCode2, MajorCode3) 1NF: 1)There is no primary key. The Student ID column is the only unique field so that would be the primary key table. This can be used to create the one to many relationship with the other tables. 2)There are duplicates within the Name column and two students have the same first and last name so name cannot be used as primary key. 3)The HomeMobilePhoneNumbers has more than one value and needs to be broken up. No student can have the same phone number more than once so the number can be the primary key when combined with the primary key from the student table. 4)The other issue with the HomeMobilePhoneNumbers column is the two numbers are separated by a semicolon and it would be easy to forget to type that in when there is only one phone number. This means that the type of phone number could be incorrectly identified. Also, if it decided later on to add more types of phone numbers, it would create issues. 5)The first and last name need to be split into two fields because you should not have two values in the same field and names could change over time so it would make it easier to change the name. 6)The major table needs to be it's own table to avoid having the information deleted if a student is deleted. I created a linking table to allow for multiple values: PhoneNumbers and added a column to identify the type of phone number. I also renamed the HomeMobilePhoneNumbers to PhoneNumber because it will no longer contain multiple phone numbers in one column. The PhoneNumbers table also needs to connect to the student table, so the studentID is included in PhoneNumbers as a foreign key. The HomeMobilePhoneNumbers column was removed from the student table. These are the resulting entity tables: student, PhoneNumbers, Majors student (studentID*, FirstName, LastName) PhoneNumbers: (studentID@, PhoneNumber*, PhoneType) Majors (MajorCode*) 2NF: There is no limit on how many majors that a student can have so having the MajorCode1, MajorCode2, MajorCode3 columns does not work. Because the student can have each major only once, we can create another table to identify a one to many relationship with the student table. I created another linking table to be able to create a one to many relationship between the student and major table called StudentMajors with a column named MajorCode because a singluar major will be identified. This table will also include the studentID as a foreign key. It not limit the number of majors that a student can have like with the MajorCode1, MajorCode2, MajorCode3 columns did. I removed the MajorCode1, MajorCode2,and MajorCode3 columns from the student table. The student can have multiple majors but cannot have the same major twice so the MajorCode for each student is unique. These are the resulting entity tables: student, PhoneNumbers, StudentMajors. student(studentID*, Name) PhoneNumbers: (studentID@, PhoneNumber*, PhoneType) StudentMajors: (studentID@, MajorCode*) 3NF: Ok for 3F Three data rows: student (studentID*, FirstName LastName) 34673656 Avram Hinton 83681840 Hayes Gibson 94086534 Kristen Acosta PhoneNumbers: (studentID@, PhoneNumber*, PhoneType) 34673656 103-391-7811 Home 34673656 676-683-6301 Mobile 83681840 447-149-5334 Home 94086534 972-658-1570 Mobile StudentMajors: (StudentID@, MajorCode*) 34673656 NSA 34673656 CSS 34673656 CST 83681840 CST 94086534 CP 94086534 MAD