| 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/00071476/Lab07/ |
Upload File : |
The purpose of this lab is to demonstrate the ability to normalize data.
Heidi Sabacky
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:
StudentID is a unique number assigned to each student.
StudentName is the student's full name.
Majors are the code(s) for the student's major(s). Each code is unique.
HomeMobilePhoneNumbers are the phone numbers for each student. Home number is listed first, followed by a semicolon(;),
then the mobile number. No student may have the same phone number more than once.
UNF:
student(Name, HomeMobilePhoneNumbers, StudentID*, MajorCode1, MajorCode2, MajorCode3)
First 3 rows of given 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
1NF:
student(studentID*, student_firstName, student_lastName)
studentphone(studentID@*, Phone*, Type)
major(MajorCode*, MajorID*)
Notes:
Problem Identified:
The HomeMobilePhoneNumbers field in the student table violated 1NF as it contained both home and mobile phone numbers in the same field.
Solution:
Separated the Name field into student_firstName and student_lastName to ensure that each field contains atomic values.
Created a new table studentphone to separate the phone numbers and introduced the Type field to specify whether the phone number is a home or mobile number.
Introduced a new table major with a composite key (MajorID, MajorCode) to handle majors and satisfy the deletion anomaly.
Result:
The student table now adheres to 1NF with atomic values in each field.
The studentphone table ensures that phone numbers are stored separately, and the Type field ensures that each phone number is associated with a specific type (home or mobile).
The major table is in 1NF, addressing the deletion anomaly and ensuring atomic values.
Sample Data:
student(studentID*, student_firstName, student_lastName)
34673656 Avram Hinton
83681840 Hayes Gibson
94086534 Kristen Acosta
studentphone(studentID@*, Phone*, Type)
34673656 103-391-7811 Home
34673656 676-683-6301 Mobile
83681840 447-149-5334 Home
94086534 972-658-1570 Home
major(MajorCode*, MajorID*)
CP 001
CST 002
CSS 003
MAD 004
NSA 005
2NF:
student(studentID*, student_firstName, student_lastName)
studentphone(studentID@*, Phone*, Type)
major(MajorCode*, MajorID*)
student_major(studentID@*, MajorID@*)
Notes:
Introduced a new junction table student_major to handle the many-to-many relationship between students and majors.
The student table now focuses on student-related information, and major information is managed in the major table and linked through the student_major junction table.
This design ensures that each table is in 2NF, adhering to the principles of normalization. The relationships are well-defined, and data redundancy is minimized.
Sample Data:
student(studentID*, student_firstName, student_lastName)
34673656 Avram Hinton
34673656 Avram Hinton
34673656 Avram Hinton
83681840 Hayes Gibson
94086534 Kristen Acosta
94086534 Kristen Acosta
studentphone(studentID@*, Phone*, Type)
34673656 103-391-7811 Home
34673656 676-683-6301 Mobile
83681840 447-149-5334 Home
94086534 972-658-1570 Home
major(MajorCode*, MajorID*)
CP 001
CST 002
CSS 003
MAD 004
NSA 005
studentmajor(studentID@*, MajorID@*)
34673656 002
34673656 003
34673656 005
83681840 002
94086534 001
3NF:
student(studentID*, student_firstName, student_lastName)
OK
studentphone(studentID@*, Phone*, Type)
OK
major(MajorCode*, MajorID*)
OK
Each table maintains atomic values and is free from transitive dependencies, adhering to the principles of normalization.
Sample Data:
student(studentID*, student_firstName, student_lastName)
34673656 Avram Hinton
34673656 Avram Hinton
34673656 Avram Hinton
83681840 Hayes Gibson
94086534 Kristen Acosta
94086534 Kristen Acosta
studentphone(studentID@*, Phone*, Type)
34673656 103-391-7811 Home
34673656 676-683-6301 Mobile
83681840 447-149-5334 Home
94086534 972-658-1570 Home
major(MajorCode*, MajorID*)
CP 001
CST 002
CSS 003
MAD 004
NSA 005
studentmajor(studentID@*, MajorID@*)
34673656 002
34673656 003
34673656 005
83681840 002
94086534 001