| 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/MichaelMalz/CST1600/Resources/Notes/Archive/ |
Upload File : |
* = PK
@ = FK
^ = Unique
From Database-Design-2nd-Edition ch12
UNF:
Student_Grade_Report (StudentNo*, StudentName, Major, CourseNo*, CourseName, InstructorNo, InstructorName, InstructorLocation, Grade)
1NF:
There are no non-atomic values, or repeating fields like Course1, Course2, Course3, nor are there repeating tables for courses.
So we ask 'What information in the table will cause data to be repeated?'
In the Student_Grade_Report table, the repeating data is the course information. A student can take many courses.
Remove the repeating data into a new table. In this case, it’s the course information for each student.
Student (StudentNo*, StudentName, Major)
StudentCourse (StudentNo*@, CourseNo*, CourseName, InstructorNo, InstructorName, InstructorLocation, Grade)
2NF:
The Student table is already in 2NF because it has a single-column PK.
When examining the Student Course table, we see that not all the attributes are fully dependent on the composite PK; specifically, all course information.
The only attribute that is fully dependent is grade. (StudentNo*@, CourseNo*).
Student (StudentNo*, StudentName, Major)
student_course (StudentNo*@, CourseNo*@, Grade)
Course (CourseNo*, CourseName, InstructorNo, InstructorName, InstructorLocation)
3NF:
The InstructorName in the Course table is also not directly dependent on the CourseNo; it is dependant on the InstructorNo.
We have to know if the InstructorLocation data is for the Instructor's office or for the room the course is offered in. I am going with Instructor's Office.
Student (StudentNo*, StudentName, Major)
student_course (StudentNo*@, CourseNo*@, Grade)
Course (CourseNo*, CourseName, InstructorNo@)
Instructor (InstructorNo*, InstructorName, InstructorLocation)
More:
When looking back at 1NF, What if a student can also have more than one Major?
Then the Major data must also be placed in a new table. This also stops the deletion anomoly for Major data.
Student (StudentNo*, StudentName)
student_course (StudentNo*@, CourseNo*@, Grade)
student_major(StudentNo*@, Major*@)
Course (CourseNo*, CourseName, InstructorNo@)
Instructor (InstructorNo*, InstructorName, InstructorLocation)
Major ( Major*)
From https://beginnersbook.com/2015/05/normalization-in-dbms/ (after merging data from the first two examples)
UNF:
employee( emp_id, emp_name, emp_address, emp_mobile, emp_dept )
101 Rick Delhi D001
101 Rick Delhi D002
123 Maggie Agra D890
102 Jon Kanpur 8812121212
9900012222
103 Ron Chennai 7778881212
104 Lester Bangalore 9990000123
8123450987
166 Glenn Chennai D900
166 Glenn Chennai D004
1NF:
Not all fields contain atomic values; some emp_mobile fields contain two values. So emp_mobile data must go into another table.
There is also data in this table that causes repeating data. An employee cam be in more than one department.
Remove this, also to another table.
employee ( emp_id*, emp_name, emp_address )
employee_mobile ( emp_id*@, emp_mobile* )
employee_dept ( emp_id*@, emp_dept* )
2NF, 3NF:
These tables follow the rules, so we are done.