| 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/Lab08/ |
Upload File : |
DAVYAEL KARDELL
LAB08
UNF:
student_grade_report (StudentNo, StudentName, Majors, ClassID, CourseNo, CourseName, InstrNo, InstrName, Room, Grade)
1NF:
There are two methods you could use when fixing 1NF violations:
1) Where there are non-atomic data values in a field, repeat each row as many times as needed and remove all but one of the non-atomic values.
For each of the fields that had non-atomic values, make that field part of the PK.
This will usually lead to 2NF violations later, so I call this the Slower Method.
2) Create a table for each field that has non-atomic data values, create a table to hold those values and create a new table to link them.
This will remove a lot of 2NF violations later, so I call this the Faster Method.
This example uses the Faster Method.
1) There was no primary key.
After looking at the candidate keys, I determined the PK to be a composite key of StudentNo and ClassID.
2) The StudentName, Majors, InstrNo, InstrName, and Room fields have multiple values in some rows.
These fields violate 1NF and need to use a one-to-many relationship to other tables for the students, majors, instructors, and rooms.
I created the tables: student, major, instructor, and room.
The InstrName depends on the InstrNo, so it is moved to the new instructor table. I also broke up into firstName and lastName fields.
I created linking tables to allow for multiple values: student_grade_report_instructor, student_grade_report_major, and student_grade_report_room.
I then removed the StudentName, Majors, InstrNo, and Room from the student_grade_report table
3) The CourseNo does not depend on the StudentNo, only the ClassID.
I created the table class to store class data, and removed CourseNo and CourseName from the student_grade_report table.
This led to the following entity tables: class, course, instructor, major, room, and student.
The following one-to-many tables: class_instructor, class_room, and student_major.
Then the student_grade_report table (Now containing only StudentNo, ClassID, and Grade) was re-named studentclassgrade.
class ( ClassID*, CourseNo, CourseName )
class_instructor( ClassID*@, InstrNo*@)
class_room(ClassID*@, Room*@)
instructor (InstrNo*, firstName, lastName)
major (MajorCode*)
room (Room*)
student (StudentNo*, firstName, lastName)
student_major (StudentNo*, MajorCode*@)
studentclassgrade (StudentNo*, ClassID*, Grade)
2NF:
The following tables have composite PKs and need to be evaluated for 2NF: class_instructor, class_room, studentclassgrade, and student_major.
The tables class_instructor, class_room, and student_major do not have any non-key fields and pass 2NF.
The studentclassgrade table also passes 2NF because the Grade depends on both the StudentNo and ClassID.
OK for 2NF.
3NF:
1) The CourseName in the class table depends on the CourseNo, not the ClassID.
I created a course table for that and removed CourseName from the class table.
class ( ClassID*, CourseNo@ )
class_instructor( ClassID*@, InstrNo*@)
class_room(ClassID*@, Room*@)
course (CourseNo*, CourseName)
instructor (InstrNo*, firstName, lastName)
major (MajorCode*)
room (Room*)
student (StudentNo*, firstName, lastName)
student_major (StudentNo*@, MajorCode*@)
studentclassgrade (StudentNo*@, ClassID*@, Grade)
Three data rows: I have done all six rows for this example, you need to do it for only the first three rows of the sample data.
class (ClassID*, CourseNo@)
123456, CST1611
345678, CST1600
234567, CST2608
456789, CST1022
567890, CST1600
class_instructor (ClassID*@, InstrNo*@)
123456, 00007967
345678, 00007967
234567, 00007967
234567, 00008843
456789, 00008612
567890, 00007967
class_room (ClassID*@, Room*@)
123456, rm327
345678, rm327
234567, rm328
234567, B48
456789, rm345
456789, B67
567890, rm327
course (CourseNo*, CourseName)
CST1611, Web Server Administration
CST1600, Relational Databases
CST2608, Linux Server Administration
CST1022