DBMS Normalization Notes

Exam writing formula: Definition -> real-time problem -> dependency -> violating table -> diagram -> normalized tables -> conclusion.

Quick Roadmap
Topic Main idea Real-time example

Normalization Organize tables to reduce redundancy and anomalies College student-course-department database

Functional dependency One attribute determines another RollNo -> StudentName

Partial dependency Part of composite key determines non-prime attribute StudentID -> StudentName in Enrollment

Transitive dependency Key determines attribute through non-key attribute RollNo -> DeptID -> DeptName

Multivalued dependency One entity has independent multiple values Student ->> Phone and Student ->> Hobby

Join dependency Relation can be reconstructed from multiple projections Supplier-Part-Project

1NF Remove multi-valued cells Separate student phone numbers

2NF Remove partial dependency Separate Student, Course and Enrollment

3NF Remove transitive dependency Separate Student and Department

BCNF Every determinant must be a super key Separate TeacherCourse

4NF Remove independent MVDs Separate StudentPhone and StudentHobby

5NF Remove non-trivial join dependencies SPJ decomposed into SP, SJ, PJ

Normalization path

Raw data 1NF 2NF 3NF BCNF 4NF 5NF

Sample Real-Time Database Used in Notes
Scenario: A college stores student enrollment, course, department, teacher, phone, hobby and project data. Poor design stores many
facts in one table, causing repeated data and anomalies.

Unnormalized StudentCourseDepartment table

StudentID StudentName CourseID CourseName DeptID DeptName Teacher Marks

S1 Aman C1 DBMS D1 Computer Science Dr Rao 88

S1 Aman C2 OS D1 Computer Science Dr Mehta 76

S2 Riya C1 DBMS D1 Computer Science Dr Rao 91

S3 Kabir C3 AI D2 Applications Dr Khan 84

Important dependencies in this table

Dependency Meaning

StudentID -> StudentName, DeptID One student ID identifies student name and department

DeptID -> DeptName One department ID identifies one department name

CourseID -> CourseName, Teacher One course ID identifies course and teacher

StudentID, CourseID -> Marks Marks depends on both student and course

Dependency chain example

1. Normalization
Definition: Normalization is a database design technique used to organize data into proper tables so that redundancy, update anomaly,
insertion anomaly and deletion anomaly are reduced.
Real-time example: If DeptName is stored with every student enrollment row, the same department name repeats many times. If the
department name changes, it must be updated in many rows.

Anomaly example table

StudentID StudentName DeptID DeptName CourseName

S1 Aman D1 Computer Science DBMS

S1 Aman D1 Computer Science OS

S2 Riya D1 Computer Science DBMS

S3 Kabir D2 Applications AI

Why normalization is needed

Repeated data Update anomaly Insertion anomaly Deletion anomaly Normalized tables

Anomaly Real-time explanation Solution through normalization

Update anomaly Changing Computer Science to CSE requires many row Store department once in Department table.
updates.

Insertion anomaly Cannot insert a new department unless a student exists. Create separate Department table.

Deletion anomaly Deleting last student of D2 may delete Applications department Keep department independent of students.
info.

Exam conclusion: Normalization divides large, redundant tables into smaller meaningful tables while preserving data using proper keys and
joins.
2. Different Types of Dependency
Dependencies describe how attributes depend on each other. They are the foundation for deciding normal forms.

Dependency summary table

Dependency Meaning Real-time example Importance

Functional Dependency (FD) X -> Y: X determines Y RollNo -> StudentName Used in 2NF, 3NF, BCNF

Trivial FD Y is part of X StudentID, CourseID -> StudentID Always true

Non-trivial FD Y is not part of X RollNo -> Name Useful dependency

Full dependency Attribute depends on whole composite key StudentID, CourseID -> Marks Correct for enrollment marks

Partial dependency Attribute depends on part of composite key StudentID -> StudentName Violates 2NF

Transitive dependency Key -> non-key -> non-key StudentID -> DeptID -> DeptName Violates 3NF

Multivalued dependency (MVD) X ->> Y: X has independent multiple Y Student ->> Phone Used in 4NF
values

Join dependency (JD) Relation can be reconstructed by joining SPJ = SP JOIN SJ JOIN PJ Used in 5NF
projections

Functional dependency diagram

Transitive dependency diagram

MVD idea

Student Multiple phones Multiple hobbies Independent facts

3. First Normal Form (1NF)

Definition: A relation is in 1NF if every cell contains only atomic/single values and there are no repeating groups.
Real-time example: A student may have multiple phone numbers. Storing all phones in one column violates 1NF.

Table violating 1NF

StudentID StudentName PhoneNumbers

S1 Aman 9991112222, 8881112222

S2 Riya 7771112222

S3 Kabir 6661112222, 5551112222

1NF decomposition diagram

Student(StudentID, Name, PhoneNumbers) Student(StudentID, Name) StudentPhone(StudentID, PhoneNo)

After 1NF
StudentID StudentName

S1 Aman

S2 Riya

S3 Kabir

StudentID PhoneNo

S1 9991112222

S1 8881112222

S2 7771112222

S3 6661112222

S3 5551112222

Exam points: 1NF removes multi-valued attributes, repeating groups and non-atomic values. It does not remove partial or transitive
dependency.
4. Second Normal Form (2NF)
Definition: A relation is in 2NF if it is in 1NF and every non-prime attribute is fully dependent on the whole candidate key. It removes
partial dependency.
Real-time example: In Enrollment(StudentID, CourseID, StudentName, CourseName, Marks), the key is (StudentID, CourseID). Marks
depends on both, but StudentName depends only on StudentID and CourseName depends only on CourseID.

Table violating 2NF

StudentID CourseID StudentName CourseName Marks

S1 C1 Aman DBMS 88

S1 C2 Aman OS 76

S2 C1 Riya DBMS 91

S3 C3 Kabir AI 84

Partial dependency diagram

2NF decomposition diagram

Enrollment(StudentID, CourseID, StudentName, Student(StudentID, Course(CourseID, Enrollment(StudentID,

CourseName, Marks) StudentName) CourseName) CourseID, Marks)

After 2NF
StudentID StudentName

S1 Aman

S2 Riya

S3 Kabir

CourseID CourseName

C1 DBMS

C2 OS

C3 AI

StudentID CourseID Marks

S1 C1 88

S1 C2 76

S2 C1 91

S3 C3 84

Exam points: 2NF is important only when the candidate key is composite. If the primary/candidate key has one attribute, the relation is
automatically free from partial dependency.
5. Third Normal Form (3NF)
Definition: A relation is in 3NF if it is in 2NF and there is no transitive dependency of a non-prime attribute on a candidate key.
Real-time example: StudentID determines DeptID, and DeptID determines DeptName. Therefore StudentID indirectly determines
DeptName. This is transitive dependency.

Table violating 3NF

StudentID StudentName DeptID DeptName

S1 Aman D1 Computer Science

S2 Riya D1 Computer Science

S3 Kabir D2 Applications

S4 Neha D3 Engineering

Transitive dependency diagram

3NF decomposition diagram

Student(StudentID, Name, DeptID, DeptName) Student(StudentID, Name, DeptID) Department(DeptID, DeptName)

After 3NF
StudentID StudentName DeptID

S1 Aman D1

S2 Riya D1

S3 Kabir D2

S4 Neha D3

DeptID DeptName

D1 Computer Science

D2 Applications

D3 Engineering

Formal condition: For every FD X -> A, either X is a super key or A is a prime attribute. In simple exam language, remove key -> non-key ->
non-key dependency.
6. Boyce-Codd Normal Form (BCNF)
Definition: A relation is in BCNF if for every non-trivial functional dependency X -> Y, X must be a super key. BCNF is stricter than 3NF.
Real-time example: In a teaching table, one teacher teaches only one course, so Teacher -> Course. But Teacher does not identify the
complete row because many students can study under the same teacher.

Table violating BCNF

StudentID CourseID Teacher

S1 C1 Dr Rao

S2 C1 Dr Rao

S3 C2 Dr Mehta

S4 C3 Dr Khan

Functional dependency Problem

Teacher -> CourseID Teacher is determinant but not a super key, so BCNF is violated.

StudentID, CourseID -> Teacher Composite key identifies teacher for a student's course.

BCNF decomposition diagram

Teaching(StudentID, CourseID, Teacher) TeacherCourse(Teacher, CourseID) StudentTeacher(StudentID, Teacher)

After BCNF
Teacher CourseID

Dr Rao C1

Dr Mehta C2

Dr Khan C3

StudentID Teacher

S1 Dr Rao

S2 Dr Rao

S3 Dr Mehta

S4 Dr Khan

Exam points: 3NF may allow some dependencies where RHS is prime, but BCNF always requires the determinant to be a super key. BCNF
decomposition is lossless but may not always preserve dependencies.
7. Fourth Normal Form (4NF)
Definition: A relation is in 4NF if it is in BCNF and has no non-trivial multivalued dependency X ->> Y unless X is a super key.
Real-time example: A student can have multiple phone numbers and multiple hobbies. Phone numbers and hobbies are independent
facts. Storing both in one table creates repeated combinations.

Table violating 4NF

StudentID PhoneNo Hobby

S1 999 Cricket

S1 999 Music

S1 888 Cricket

S1 888 Music

S2 777 Dance

MVD Meaning

StudentID ->> PhoneNo One student may have many phone numbers.

StudentID ->> Hobby One student may have many hobbies.

PhoneNo and Hobby are independent Every phone is unnecessarily combined with every hobby.

4NF decomposition diagram

StudentInfo(StudentID, PhoneNo, Hobby) StudentPhone(StudentID, PhoneNo) StudentHobby(StudentID, Hobby)

After 4NF
StudentID PhoneNo

S1 999

S1 888

S2 777

StudentID Hobby

S1 Cricket

S1 Music

S2 Dance

Exam points: 4NF handles multivalued facts. It is used when two or more independent multi-valued attributes are stored together.
8. Fifth Normal Form (5NF)
Definition: A relation is in 5NF, also called Project-Join Normal Form, if every non-trivial join dependency is implied by candidate keys. It
removes redundancy caused by complex join dependencies.
Real-time example: In a supply system, Supplier-Part-Project data may be represented by three pairwise facts: supplier supplies part,
supplier works on project, and part is used in project.

SPJ table before 5NF

Supplier Part Project

S1 P1 J1

S1 P2 J1

S2 P1 J2

S3 P3 J2

Join dependency idea

SPJ relation Project into SP, SJ, PJ Join projections Reconstruct SPJ

5NF decomposition diagram

SPJ(Supplier, Part, Project) SP(Supplier, Part) SJ(Supplier, Project) PJ(Part, Project)

After 5NF
Supplier Part

S1 P1

S1 P2

S2 P1

S3 P3

Supplier Project

S1 J1

S2 J2

S3 J2

Part Project

P1 J1

P2 J1

P1 J2

P3 J2

Exam points: 5NF is less common in simple databases but important for complex many-to-many-to-many relationships. It avoids redundancy
when a relation can be reconstructed by joining smaller projections.
9. Important Decomposition Properties
Normalization should not destroy information. Two important properties are lossless join and dependency preservation.

Lossless join decomposition

A decomposition of relation R into R1 and R2 is lossless if joining R1 and R2 gives exactly the original relation.
pi_R1(r) JOIN pi_R2(r) = r

Lossless join diagram

Original table r Project R1 and R2 Join R1 and R2 Same original table

Property Meaning Example

Lossless No spurious tuples after join Student JOIN Department gives original StudentDept data

Lossy Join creates extra fake rows R1(A,B) JOIN R2(B,C) may create extra A-B-C combinations

Dependency preserving decomposition

A decomposition is dependency preserving if all original functional dependencies can be checked in decomposed tables without joining
them.
FD Preserved in table Explanation

StudentID -> StudentName Student(StudentID, StudentName) Can be checked directly

DeptID -> DeptName Department(DeptID, DeptName) Can be checked directly

StudentID, CourseID -> Marks Enrollment(StudentID, CourseID, Marks) Can be checked directly

Exam conclusion: Lossless join is compulsory. Dependency preservation is desirable because constraints can be checked easily.
10. Normal Forms Comparison Table
Normal form Removes Main rule Real-time example

1NF Repeating groups and multi-valued cells All values must be atomic Separate student phone numbers

2NF Partial dependency No non-prime attribute depends on part of composite key StudentName moved from Enrollment
to Student

3NF Transitive dependency No key -> non-key -> non-key dependency DeptName moved to Department

BCNF Non-key determinant dependency Every determinant must be a super key Teacher -> Course separated

4NF Multivalued dependency No non-trivial MVD unless determinant is super key Separate phones and hobbies

5NF Join dependency No non-trivial JD unless implied by keys SPJ separated into SP, SJ, PJ

11. Marks-wise Exam Question Bank

2 Marks Questions
Question Answer

Q1. Define normalization. Normalization organizes data to reduce redundancy and anomalies.

Q2. What is FD? X -> Y means X determines Y. Example: RollNo -> Name.

Q3. What is partial dependency? A non-prime attribute depends on part of a composite key.

Q4. What is transitive dependency? Key determines an attribute through another non-key attribute.

Q5. What is MVD? X ->> Y means X has multiple independent values of Y.

Q6. Define 1NF. All values must be atomic and no repeating groups.

Q7. Define BCNF. For every FD X -> Y, X must be a super key.

Q8. Define 5NF. 5NF removes non-trivial join dependencies.

3/5 Marks Expected Questions

Question Answer writing structure

Q1. Explain normalization with real-time example. Use StudentCourseDepartment table, explain anomalies, then decompose into Student, Department, Course
and Enrollment.

Q2. Explain different types of dependency. Explain FD, full, partial, transitive, MVD and JD with table examples.

Q3. Explain 1NF, 2NF and 3NF. Use phone table for 1NF, enrollment table for 2NF, student-department table for 3NF.

Q4. Explain BCNF with example. Use Teaching(StudentID, CourseID, Teacher) and Teacher -> CourseID violation.

Q5. Explain 4NF and 5NF. Use StudentPhoneHobby for 4NF and Supplier-Part-Project for 5NF.

Q6. Explain lossless and dependency preserving Show projection-join equality and FD preservation in decomposed tables.
decomposition.

Final Revision Formula