| 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/20255/CST1600/41/12965044/Lab07/ |
Upload File : |
UNF: student(Name, HomeMobilePhoneNumbers, StudentID, MajorCode1, MajorCode2, MajorCode3) Name HomeMobilePhoneNumber StudentID Major Code 1 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 1NF: For the first normal form I removed the comma separated values from the "HomeMobilePhoneNumber" column. Name PhoneNumber StudentID Major Code 1 MajorCode2 MajorCode3 Avram Hinton 103-391-7811 34673656 NSA CSS CST Avram Hinton 676-683-6301 34673656 NSA CSS CST Hayes Gibson 447-149-5334 83681840 CST Null Null Kristen Acosta 972-658-1570 94086534 CP MAD Null This alleviated the bunching of data particularly in this example for Avram, as he had both a Home and mobile number. 1NF - OK 2NF: For the second normal form, I created two tables from the first normal form - a Students Table (*StudentID, Name, PhoneNumber) and a StudentMajors table (*StudentID, MajorCode). Students: *StudentID Name ^PhoneNumber 34673656 Avram Hinton 103-391-7811 34673656 Avram Hinton 676-683-6301 83681840 Hayes Gibson 447-149-5334 94086534 Kristen Acosta 972-658-1570 StudentMajors: *StudentID MajorCode 34673656 NSA 34673656 CSS 34673656 CST 83681840 CST 94086534 CP 94086534 MAD The "HomeMobilePhoneNumber" column was truncated to "PhoneNumber" as using the Student ID as the primary key allows each student to have more than one phone number without having data be cell mates. This split was done as the student's phone number does not impact the student major, and thus is not crucial information for referencing a student's major. And Vice Versa, a student's Major does not determine their phone numbers or name. 3NF: For the third normal form, the data was broken down into three tables from the previous two, creating a Students table(*StudentID, Name), a StudentPhone Table (StudentID, PhoneNumber), and a Student Major Table (@StudentID, "@"MajorCode) Students: *StudentID Name 34673656 Avram Hinton 83681840 Hayes Gibson 94086534 Kristen Acosta StudentPhone: *@StudentID *PhoneNumber 34673656 103-391-7811 34673656 676-683-6301 83681840 447-149-5334 94086534 972-658-1570 StudentMajor: *@StudentID *"@" MajorCode 34673656 NSA 34673656 CSS 34673656 CST 83681840 CST 94086534 CP 94086534 MAD The tables were broken down to their smallest components, now using the "StudentID" as the primary key across all three tables. This avoids duplicate names (John Smith for example) from agitating queries as the tables will reference student ID's which are unique. The logic behind the phone numbers is the same as the student names, as for example in the full data set, Collette and Hayes share a home phone number which again by only cross referencing the student ID will avoid most issues with data integrity. Finally - to address the "@" in front of MajorCode - the only change I would have made or added would be to create a MajorList table to reinforce the "Each major code is unique" clause. This would mean that the StudentMajor table would be a junction table as well. Here's an example of what the MajorList table would look like (Some of the major names are not correct - hence the table's exclusion from the previous tables - a more concrete reference would be needed before the data would be added.) MajorList: *MajorCode MajorName NSA National Security Administration (obviously not it) CSS Computer Science (I know its not this either) CST Computer Science and Technology CP Compuiter Programming MAD Mobile App Development Another change that be implemented would be to create a PhoneID table - to store each phone number once in the data base once and use the phone ID in place of the Phonenumber column in the "StudentPhone" table. PhoneID: PhoneID PhoneNumber 1 103-391-7811 2 676-683-6301 3 447-149-5334 4 972-658-1570 The only reason this was not used was that it did not seem super necessary in the data set provided, as there were only four students that had an overlap with their phone numbers, but for scalability the PhoneID would most likely be better suited for larger data sets as it would be a lot simpler to maintain and reference. Three data rows: UNF Name HomeMobilePhoneNumber StudentID Major Code 1 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 1NF Name PhoneNumber StudentID Major Code 1 MajorCode2 MajorCode3 Avram Hinton 103-391-7811 34673656 NSA CSS CST Avram Hinton 676-683-6301 34673656 NSA CSS CST Hayes Gibson 447-149-5334 83681840 CST Null Null Kristen Acosta 972-658-1570 94086534 CP MAD Null 2NF Students: *StudentID Name ^PhoneNumber 34673656 Avram Hinton 103-391-7811 34673656 Avram Hinton 676-683-6301 83681840 Hayes Gibson 447-149-5334 94086534 Kristen Acosta 972-658-1570 StudentMajors: *StudentID MajorCode 34673656 NSA 34673656 CSS 34673656 CST 83681840 CST 94086534 CP 94086534 MAD 3NF Students: *StudentID Name 34673656 Avram Hinton 83681840 Hayes Gibson 94086534 Kristen Acosta StudentPhone: *@StudentID *PhoneNumber 34673656 103-391-7811 34673656 676-683-6301 83681840 447-149-5334 94086534 972-658-1570 StudentMajor: *@StudentID *"@" MajorCode 34673656 NSA 34673656 CSS 34673656 CST 83681840 CST 94086534 CP 94086534 MAD