| 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/16420972/Lab07/ |
Upload File : |
UNF:
student_grade_report (StudentNo, StudentName, Majors, ClassID, CourseNo, CourseName, InstrNo, InstrName, Room, Grade)
1NF:
The primary key is a composite key of StudentNo and ClassID.
Created tables for non-atomic fields: student, major, instructor, room.
Created linking tables for multiple values: student_grade_report_instructor, student_grade_report_major, student_grade_report_room.
Removed non-atomic fields (StudentName, Majors, InstrNo, Room) from student_grade_report table.
Renamed student_grade_report to studentclassgrade.
student (StudentNo*, firstName, lastName)
major (MajorCode*)
instructor (InstrNo*, firstName, lastName)
room (Room*)
student_major (StudentNo*, MajorCode*@)
studentclassgrade (StudentNo*@, ClassID*@, Grade)
2NF:
All tables have composite primary keys, but they do not have non-key fields.
OK for 2NF.
3NF:
Created a course table for CourseNo and CourseName dependency.
course (CourseNo*, CourseName)
Updated class table with CourseNo as a foreign key.
class (ClassID*, CourseNo@)
Updated studentclassgrade table with StudentNo as a foreign key.
studentclassgrade (StudentNo*@, ClassID*@, Grade)
Three data rows:
class (ClassID*, CourseNo@)
123456, CST1611
345678, CST1600
234567, CST2608
class_instructor (ClassID*@, InstrNo*@)
123456, 00007967
345678, 00007967
234567, 00007967
234567, 00008843
class_room (ClassID*@, Room*@)
123456, rm327
345678, rm327
234567, rm328
234567, B48
course (CourseNo*, CourseName)
CST1611, Web Server Administration
CST1600, Relational Databases
CST2608, Linux Server Administration
instructor (InstrNo*, firstName, lastName)
00007967, Jim, Martinson
00008843, Rocky, Hyberger
major (MajorCode*)
NSA
CP
CST
MAD
room (Room*)
rm328
B48
rm327
student (StudentNo*, firstName, lastName)
34673656, Avram, Hinton
94086534, Kristen, Acosta
83681840, Hayes, Gibson
student_major (StudentNo*@, MajorCode*@)
34673656, NSA
94086534, CP
83681840, CST
studentclassgrade (StudentNo*@, ClassID*@, Grade)
34673656, 123456, A
94086534, 345678, F
34673656, 234567, C