| 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 : /nginx/html/uploads/20243/CST1600/41/00103226/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) A student's name needs to be separated into FirstName and LastName. Home and mobile numbers could be different, and a student may not have one of them. A student can have more than one major. 1NF: student (StudentID*, FirstName, LastName) phone (StudentID, HomePhone, MobilePhone) major (MajorCode*, MajorName) studentMajor (StudentID*, MajorCode@) Here we split things up a bit. It makes sense to split the phone numbers up and put them in their own table. Name cannot be an atomic value, whereas FirstName can, as can LastName. HomeMobilePhoneNumbers should be separated into separate fields. Having them together doesn't make sense, as a student could have both a home number and a cell number, or just one of the two. Or maybe none? The "major" table will keep track of major names and codes. 2NF: OK 3NF: student (StudentID*, FirstName, LastName) phone (StudentID*@, HomePhone, MobilePhone) major (MajorCode*, MajorName) studentMajor (StudentID*@, MajorCode*@) StudentID and MajorCode are composite primary key. They are also foreign keys which relate to the student and major tables. Three data rows: student (StudentID*, FirstName, LastName) 34673656, Avram, Hinton 83681840, Hayes, Gibson 94086534, Kristen, Acosta phone (StudentID@, HomePhone, MobilePhone) 34673656, 103-391-7811, 676-683-6301 83681840, 447-149-5334, NULL 94086534, 972-658-1570, NULL major (MajorCode*, MajorName) NSA Nebula Space Analysis CSS Carnivorous Satellite Scenarios CST Catapult Salamander Trigonometry MAD Moth Ant Debate CP Contiguous Petunias studentMajor (StudentID*@, MajorCode*@) 34673656 NSA 34673656 CSS 34673656 CST 83681840 CST 94086534 CP 94086534 MAD