How to analyze normalization

1. Create Functional Dependencies of all columns in a table
2. Make it 1NF
3. Make it 2NF
4. Make it 3NF
5. Make it BCNF


Example 1: (Read this example for sure)


Refer to this very simple tutorial : http://phlonx.com/resources/nf3/




Example 2:

Grade_report(StudNo,StudName,(Major,Adviser,
(CourseNo,Ctitle,InstrucName,InstructLocn,Grade)))

Analyze Functional dependencies
--------------------------------
StudNo -> StudName
CourseNo -> Ctitle,InstrucName
InstrucName -> InstrucLocn
StudNo,CourseNo,Major -> Grade
StudNo,Major -> Advisor
Advisor -> Major

Unnormalised (Before any normalization)
----------------------------------------
Grade_report(StudNo,StudName,(Major,Advisor,
(CourseNo,Ctitle,InstrucName,InstructLocn,Grade)))

Apply 1NF Remove repeating groups
---------------------------------
Student(StudNo,StudName)
StudMajor(StudNo,Major,Advisor)
StudCourse(StudNo,Major,CourseNo,
Ctitle,InstrucName,InstructLocn,Grade)

Apply 2NF Remove partial key dependencies
-----------------------------------------
Student(StudNo,StudName)
StudMajor(StudNo,Major,Advisor)
StudCourse(StudNo,Major,CourseNo,Grade)
Course(CourseNo,Ctitle,InstrucName,InstructLocn)

Apply 3NF Remove transitive dependencies
----------------------------------------
Student(StudNo,StudName)
StudMajor(StudNo,Major,Advisor)
StudCourse(StudNo,Major,CourseNo,Grade)
Course(CourseNo,Ctitle,InstrucName)
Instructor(InstructName,InstructLocn)

Apply BCNF
------------
* BCNF Every determinant is a candidate key
* Student : only determinant is StudNo
* StudCourse: only determinant is StudNo,Major
* Course: only determinant is CourseNo
* Instructor: only determinant is InstrucName
* StudMajor: the determinants are
* StudNo,Major, or
* Adviser

Only StudNo,Major is a candidate key.

* BCNF
Student(StudNo,StudName)
StudCourse(StudNo,Major,CourseNo,Grade)
Course(CourseNo,Ctitle,InstrucName)
Instructor(InstructName,InstructLocn)
StudMajor(StudNo,Advisor)
Adviser(Adviser,Major)

Reference : Tutorial-1