| 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/16322022/Lab07/ |
Upload File : |
Hannen M Kafati Avila RWC 11-29-2023 CST 1600 ------------------------------------------------------------------------ Business Logic: -Each student may have more than one major. (no limit - which the UNF table does not allow) -No student may have the same major more than once. -No student may have the same phone number more than once. Note: The home number is listed first, followed by a semicolon(;), -then the mobile number. -Each major code is unique. Field Meanings: -Student Name: is student student's full name. -Student Home and Mobile Phone numbers -StudentID unique number assigned to each student -MajorCode1,MajorCode2 and MajorCode3 codes for the student's majors. Sample Data: Name |HomeMobilePhoneNumbers |studentID |MajorCode1 |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 | UNF: Student(Name, HomeMobilePhoneNumbers, StudentID, MajorCode1, MajorCode2, MajorCode3) -StudentID is both a Primary Key and Foreign Key(*@) Throughout the whole NF process -------------------------------------------------------------------------------------------- 1NF: 1) StudentTable: (StudentID*@,firstName, lastName) 2) StudentPhone: (StudentID*@,Name,PhoneNumber) 3) MajorTable: (MajorCode*) 4) StudentMajorTb: (StudentID*, MajorCode*) 1)Violates the Atomic Value Rule for 1NF in which it can only have 1 atomic value per column, so the logic behind this is that, if we see the HomeMobile phone numbers column, there is more than one value for phones numbers, that’s why I separated home number from mobile number, and made a new table where it only has the phone Number and type(ID*, NAME, Phone Number, type). 2)Null values where there is no information available. 3)RENAMED AND DELETED ALL THE COLUMNS FOR MC1,MC2,MC3, ETC => Less columns = Less redundancy for Major Codes 1,2,3... ------------------ 2NF 1)I ensured that there are no partial dependencies. 2)There are no attributes that depend only on a part of the primary key, in this case, StudentID and MajorCode. Each non-prime atrribute depend on the primary key. 3)Each non-key attribute depend fully on the entire primary key. OK FOR 2NF There's no further changes I can make to this table. ------------------ 3NF: 1) The tables don't have transitive dependencies, that's why each non-prime attribute depends only on the primary key and not on other non-key attributes. 2) Everything is separated properly into distinct tables, for eg:studentTable,studentPhone,MajorTable,StudentMajorTable. OK for 3NF there's no further changes I can make to this table. ================================================================================================================= Three data rows: ------------------ 1) StudentTable: (StudentID*@,firstName, lastName) StudentTable StudentID*@ |firstName|lastName| -----------------------------------| 34673656 |Avram |Hinton | 83681840 |Hayes |Gibson | 94086534 |Kristen |Acosta | 2) StudentPhone: (StudentID*@,Name,PhoneNumber,Type) StudentID*@|firstName|lastName |phoneNumber* | type | -----------------------------------------------------------| 34673656 |Avram |Hinton |103-391-7811 |Home | 34673656 |Avram |Hinton |676-683-6301 |Mobile | 83681840 |Hayes |Gibson |447-149-5334 |Home | 94086534 |Kristen |Acosta |972-658-1570 |Mobile | 3) MajorTable: (MajorCode*) | MajorCode*| |-----------| |NSA | |CSS | |CST | |CP | |MAD | 4) StudentMajorTable: (StudentID*, MajorCode*) StudentID*@ | MajorCode*| ---------------------------- 34673656 |NSA 34673656 |CSS 83681840 |CST 94086534 |CP 94086534 |MAD