Normalization
Uploaded by
vidhi.umitNormalization
Uploaded by
vidhi.umitNORMALIZATION and Func onal dependency
A func onal dependency occurs when the value of one a ribute (or a set of a ributes)
uniquely determines the value of another a ribute. This rela onship is denoted as:
X→Y
Here, X is the determinant, and Y is the dependent a ribute. This means that for each
unique value of X, there is precisely one corresponding value of Y.
Consider a table named Students with the following a ributes:
StudentID StudentName StudentAge
101 Rahul 23
102 Ankit 22
103 Aditya 22
104 Sahil 24
105 Ankit 23
The above table has the following func onal dependencies
StudentID -> StudentName
StudentID -> StudentAge
Note that the func onal dependencies StudentName -> StudentAge or StudentAge ->
StudentName Do not hold.
How to represent func onal dependency in DBMS?
Func onal dependency is expressed in the form of equa ons. For example, if we
have an employee record with fields "EmployeeID", "FirstName" and "LastName" we
can specify the func on as follows:
EmployeeID -> FirstName, LastName
To represent func onal dependency in DBMS has two main features: le (LHS) and
right (RHS) of the arrow (->).
For example, if we have a table with a ributes "X", "Y" and "Z" and the a ribute "X"
can determine the value of the a ributes "Y" and "Z".
X -> Y, Z
This symbol indicates that the value in a ributes "X" determines the values in
a ributes "Y" and "Z". So if you know the value of "X", you can also determine the
value of "Y" and "Z".
Benefits of Functional Dependency in DBMS
The concept of normalization is based on functional dependencies.
Using functional dependencies, we break a table in multiple tables that helps us in
preventing duplicate data and hence Improves Data Quality, less errors and better
database design.
NORMALIZATION
Normaliza on in DBMS is the process of organizing data in a database to reduce
redundancy (duplicate data) and improve data integrity (accuracy and consistency).
Normaliza on means dividing a large table into smaller, related tables and linking them
using keys.
Why Normaliza on is Needed
Without normaliza on:
Same data is stored mul ple mes
Data becomes inconsistent
Hard to update/delete
With normaliza on:
No duplica on
Easy updates
Be er data consistency
Example: Before Normaliza on
Student_ID Student_Name Course Instructor
101 Rahul DBMS Mehta
102 Priya DBMS Mehta
A er Normaliza on
Student Table
| Student_ID | Student_Name |
Course Table
| Course | Instructor |
Enrollment Table
| Student_ID | Course |
Thus:
No repeated data
Easy to manage
Types of Normaliza on
Normal Form Purpose
1NF Remove mul -valued a ributes
2NF Remove par al dependency
3NF Remove transi ve dependency
e.g
Student_ID Student_Name Courses Instructor
101 Rahul DBMS, OS Mehta, Sharma
102 Priya DBMS Mehta
Problems:
Mul ple values in one cell (Courses, Instructor)
Data redundancy
Hard to update/delete
Step 1: First Normal Form (1NF)
Rule:
Remove repea ng groups
Each field must contain atomic (single) values
Student_ID Student_Name Course Instructor
101 Rahul DBMS Mehta
101 Rahul OS Sharma
102 Priya DBMS Mehta
Now: Each cell has only one value
But Data redundancy exists (Student_Name repeated)
Step 2: Second Normal Form (2NF)
Rule:
Must be in 1NF
Remove par al dependency
Non-key a ributes must depend on full primary key
A full primary key is a key in which no single column alone can uniquely iden fy a
row — you need all the columns combined.
Primary Key here = (Student_ID, Course)
Problem:
Student_Name depends only on Student_ID (not full key)
Convert to 2NF
Student Table
Student_ID Student_Name
101 Rahul
102 Priya
Course Table
Course Instructor
DBMS Mehta
OS Sharma
Enrollment Table
Student_ID Course
101 DBMS
101 OS
102 DBMS
Now: No par al dependency
But Instructor depends on Course (not fully normalized yet)
Third Normal Form (3NF)
Rule:
Must be in 2NF
Remove transi ve dependency
Non-key a ributes should not depend on other non-key a ributes
Issue:
Instructor depends on Course (not directly on key)
Convert to 3NF:
Student Table
| Student_ID | Student_Name |
Course Table
| Course_ID | Course_Name |
Instructor Table
| Instructor_ID | Instructor_Name |
Course_Instructor Table
| Course_ID | Instructor_ID |
Enrollment Table
| Student_ID | Course_ID |
Now:
No redundancy
No transi ve dependency
Fully normalized
Final Structure (3NF)
Student(Student_ID, Name)
Course(Course_ID, Name)
Instructor(Instructor_ID, Name)
Enrollment(Student_ID, Course_ID)
Course_Instructor(Course_ID, Instructor_ID)
Normal Form Focus
1NF Remove mul -values
2NF Remove par al dependency
3NF Remove transi ve dependency
Boyce–Codd Normal Form (BCNF)
Defini on
A rela on is in BCNF if for every func onal dependency:
X→Y
X must be a super key.
In simple words:
Every determinant should be a super key.
Why BCNF?
Removes redundancy more strictly than 3NF
Eliminates update anomalies
Ensures be er database design
Example:
Rela on:
R(Student, Course, Instructor)
Func onal Dependencies:
(Student, Course) → Instructor
Instructor → Course
Problem:
Instructor → Course
Instructor is not a super key
So, rela on is NOT in BCNF
Convert to BCNF
Decompose into:
1. R1(Instructor, Course)
2. R2(Student, Instructor)
Now:
Instructor → Course (valid, Instructor is key in R1)
No viola on. So BCNF achieved
QUESTIONS:
1. Define normaliza on and explain its objec ves.
2. Explain 1NF, 2NF, and 3NF with suitable examples.
3. Explain func onal dependency.
4. Differen ate between 2NF and 3NF.
5. What is BCNF? How is it different from 3NF?