| 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/20253/CST1600/41/16899030/Lab07/ |
Upload File : |
UNF: student(Name, HomeMobilePhoneNumbers, StudentID, MajorCode1, MajorCode2, MajorCode3) 1NF: 1) There is no primary key after looking over the fields I have deduced that the best candidate for primary key is StudentID, since is unique to every student regardless of the other factors, such as name or majors, which two students can have identical data for, or phone numbers, which don't make for intuitive identifiers in my opinion. 2) Phone number violates 1NF by containing multiple entries in the same field, and the MajorCodes fields are redundtant and ignore the business logic. to fix this I've created a pair of one-many relation tables: phone_number and student_major. within phone_number HomeMobilePhoneNumbers was renamed to phone and instead of listing all data at once, now has two seperate entries with a unique field denoting the type of phone the number relates to. something similar happens with student major, where all three MajorCode fields taking up a single column within the table, with there being a unique entry for every major that relates to a studentID. this leaves three tables, phone_number, student_major, and the original student table, which now only contains StudentID and Name. student(StudentID*, Name) phone_number(StudentID*@, phone*, type) student_major(StudentID*@, major) 2NF: 1) phone_numbers has to be evaluated for composite primary keys, it passes becuase type depends soley on phone, which depends soley on studentID. student(StudentID*, Name) phone_number(StudentID*@, phone*, type) student_major(StudentID*@, major) 3NF: OK student(StudentID*, Name) phone_number(StudentID*@, phone*, type) student_major(StudentID*@, major) Three data rows: //note: I did not know how to interpret "first three rows" of sample data, so I- //went with the idea that you want to show how the sample data would look after 3NF- //regardless of how many rows it took up, rather than use the sample data and show //the first three rows of result when converting it to comply with 3NF student(StudentID*, Name) 34673656 , Avram Hinton 83681840 , Hayes Gibson 94086534 , Kristen Acosta phone_number(StudentID*@, phone* , type) 34673656 , 103-391-7811 , Home 34673656 , 676-683-6301 , Mobile 83681840 , 447-149-5334 , Home 94086534 , 972-658-1570 , Home student_major(StudentID*@, major) 34673656 , NSA 34673656 , CSS 34673656 , CST 83681840 , CST 94086534 , CP 94086534 , MAD