| 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/16241522/Lab07/ |
Upload File : |
UNF:
student(Name, HomeMobilePhoneNumbers, StudentID, MajorCode1, MajorCode2, MajorCode3)
1NF:
In the given UNF table, there's a violation of 1NF because multiple major codes and phone numbers are stored in a single row.
Let's start normalizing the data:
1NF:
less
student(*studentID*, Name, HomePhoneNumber, MobilePhoneNumber)
major(*MajorCode*, Description)
student_major(*studentID*@student.studentID, *MajorCode*@major.MajorCode)
Explanation:
Created the student table with separate columns for Name, HomePhoneNumber, and MobilePhoneNumber.
Created the major table to store unique major codes with their descriptions.
Introduced the student_major table to manage the many-to-many relationship between students and majors.
Three data rows in 3NF format:
student:
studentID | Name | HomePhoneNumber | MobilePhoneNumber
--------------------------------------------------------------
34673656 | Avram Hinton | 103-391-7811 | 676-683-6301
83681840 | Hayes Gibson | 447-149-5334 | (NULL)
94086534 | Kristen Acosta | (NULL) | 972-658-1570
major:
MajorCode | Description
-----------------------
NSA | National Security Agency
CSS | Computer Science
CST | Computer Systems Technology
student_major:
studentID | MajorCode
---------------------
34673656 | NSA
34673656 | CSS
34673656 | CST
83681840 | CST
94086534 | CP
94086534 | MAD
2NF:ok
Given the current tables (student, major, and student_major), they already meet the requirements of 2NF as no partial dependencies exist
3NF:ok
it has no transitive dependencies.
Three data rows:
student:
studentID | Name | HomePhoneNumber | MobilePhoneNumber
--------------------------------------------------------------
34673656 | Avram Hinton | 103-391-7811 | 676-683-6301
83681840 | Hayes Gibson | 447-149-5334 | (NULL)
94086534 | Kristen Acosta | (NULL) | 972-658-1570
major:
MajorCode | Description
-----------------------
NSA | National Security Agency
CSS | Computer Science
CST | Computer Systems Technology
CP | Computer Programming
MAD | Media Arts and Design
WP | Web Programming
student_major:
studentID | MajorCode
---------------------
34673656 | NSA
34673656 | CSS
34673656 | CST
83681840 | CST
94086534 | CP
94086534 | MAD