| 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 : |
This database stores student majors.
Business Logic:
Students may have more than one major.
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)
1NF:
student(studentID*, student_firstName, student_lastName)
phone(mobilePhone, homePhone, studentID@)
major(MajorCode*, studentID@*)
NOTE:
Problem: home and mobile phone in the same field when in 1NF only one piece of data can be in a field.
Separated the student Name field to firstName and lastName.
Created a phone table to seperate the home and mobile phone fields.
Created a separate table for majors and included a composite primary key (MajorCode, studentID) to uniquely identify each major associated with a student.
2NF:
student(studentID*, student_firstName, student_lastName)
2NF: OK
phone (mobilePhone, homePhone, studentID@)
2NF: OK
major(MajorCode*, studentID@*)
2NF: OK
NOTE: All tables appear to be in 2NF since each non-prime attribute is fully functionally dependent on the entire primary key.
3NF:
student(studentID*, student_firstName, student_lastName)
3NF: OK
phone (mobilePhone, homePhone, studentID@)
3NF: OK
major(MajorCode*, studentID@*)
3NF: OK
NOTE: All tables appear to be in 3NF as there are no transitive dependencies, and each non-prime attribute is directly dependent on the primary key.
Three data rows:
student(studentID*, student_firstName, student_lastName)
34673656 Avram Hinton
83681840 Hayes Gibson
94086534 Kristen Acosta
phone (studentID@, mobilePhone, homePhone)
34673656 676-683-6301 103-391-7811
83681840 447-149-5334
94086534 972-658-1570
major(studentID@*, MajorCode*)
34673656 NSA
34673656 CSS
34673656 CST
83681840 CST
94086534 CP
94086534 MAD