| 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/16322022/Lab07/ |
Upload File : |
Hannen M Kafati Avila RWC 11-15-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: Table: (StudentID*,Name,HomeNumber,MobileNumber,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(ID*, NAME, HOME AND MOBILE NUMBER, MAJOR CODE). 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... 1NF StudentID* | Name |Home Number |Mobile Number |MajorCode* ------------------------------------------------------------------------------------------------ 34673656 |Avram Hinton |103-391-7811 |676-683-6301 |NSA 34673656 |Avram Hinton |103-391-7811 |676-683-6301 |CSS 34673656 |Avram Hinton |103-391-7811 |676-683-6301 |CST 83681840 |Hayes Gibson |447-149-5334 | |CST 94086534 |Kristen Acosta | |972-658-1570 |CP 94086534 |Kristen Acosta | |972-658-1570 |MAD 2NF: STUDENT INFO 2NF TABLE: (StudentID*, Name, HomeNumber, MobileNumber) MAJOR 2NF TABLE: (StudentID*@,MajorCode* 1)Created a separated table for student personal Info, containing: ID*, NAME, HOME AND MOBILE NUMBER. 2)StudentID is a primary key for both Majorcode table and Student Table. 3)Created a separated table for MajorCode in which there's only StudenID and MajorCode info. 4)There is no partial dependencies. 5)Renaming is not needed. 3NF: OK for 3NF, there's no further changes I can make to this table. Three data rows: STUDENT INFO 2NF TABLE: StudentID* | Name |HomeNumber |MobileNumber | ---------------------------------------------------------------------- 34673656 |Avram Hinton |103-391-7811 |676-683-6301 | 83681840 |Hayes Gibson |447-149-5334 | | 94086534 |Kristen Acosta | |972-658-1570 | MAJOR 2NF TABLE: StudentID*@ | MajorCode*| ---------------------------- 34673656 |NSA 34673656 |CSS 83681840 |CST 94086534 |CP 94086534 |MAD