NORMALISATION
DATABASE MANAGEMENT SYSTEM
Presented by :
Priti Kumari
CSE-25/15
Computer Science and Engineering
CONTENTS
Introduction
Functional dependency
Normalization
Normal Forms and their Relation
First Normal Form
Second Normal Form
Third Normal Form
Boyce-codd Normal Form
Fourth Normal Form
Fifth Normal Form
Conclusion
References
2
Introduction
Database Management System
collection of interrelated data.
set of programs to access those data.
Keys
set of attributes uniquely identifying an entire tupple.
- Primary key
- Super key
- Candidate key
3
Functional dependency
Set of constraints between two attributes.
given a relation R, x and y are proper subset of R
x y (y is functionally dependent on x)
Fully functional dependency
Partial functional dependency
Transitive functional dependency
Trivial functional dependency
- y is proper subset of x.
- x is super key.
4
Normalization
A process of designing database which:
Avoids data redundancy.
Removes Functional dependencies.
minimizes use of null values.
Removes anomalies
- Update anomalies STUDENT-ID SUBJECT-ID TEACHER
- Insertion anomalies
- Deletion anomalies
5
Types of Normal Forms
First Normal Form (1 NF)
Second Normal Form (2 NF)
Third Normal Form (3 NF)
Boyce-Codd Normal Form (BCNF)
Fourth Normal Form (4 NF)
Fifth Normal Form (5 NF)
Practically normalization is done up to 3NF or BCNF.
6
Relation between Normal Forms
1NF a relation which is in
2NF 5 NF is also in 1NF,
3NF 2NF, 3NF, BCNF, 4NF.
BCNF
4NF In this way all
5NF Normal Forms
are related.
First step is 1NF.
7
First Normal Form
1 NF enforces following criteria:
No repeating groups.
Single valued attributes.
Student-record Table-
STUDENT- STUDENT- SUBJECT- SUBJECT- TEACHER- TEACHER’S-
ID NAME ID NAME NAME LOCATION
10 ARUN CS-1,CS-2, DBMS, OS, T-1, T-2, T-4 A-BLOCK,
CS-4 C++ B-BLOCK,
D-BLOCK
11 PUJA CS-1,CS-3 DBMS, T-1, T-3 A-BLOCK,
NETWORK C-BLOCK
8
Decomposition in 1NF
▪ Separate record for each repeating group.
▪ Define a primary key.
student-record table-
STUDENT- SUBJECT- STUDENT- SUBJECT- TEACHER- TEACHER’S-
ID ID NAME NAME NAME LOCATION
10 CS-1 ARUN DBMS T-1 A-BLOCK
10 CS-2 ARUN OS T-2 B-BLOCK
10 CS-4 ARUN C++ T-4 D-BLOCK
11 CS-1 PUJA DBMS T-1 A-BLOCK
11 CS-3 PUJA NETWORK T-3 C-BLOCK
9
Second Normal Form
Removes partial Functional dependency.
STUDENT-ID STUDENT-NAME
SUBJECT-ID SUBJECT-NAME
Subject-teacher table-
Student table- SUBJECT- SUBJECT- TEACHER- TEACHER’S-
ID NAME NAME LOCATION
STUDENT – STUDENT-
ID NAME CS-1 DBMS T-1 A-BLOCK
10 ARUN CS-2 OS T-2 B-BLOCK
CS-3 NETWORK T-3 C-BLOCK
11 PUJA
CS-4 C++ T-4 D-BLOCK
10
Third Normal Form
Removes Transitive functional dependency.
SUBJECT-ID TEACHER-NAME
TEACHER-NAME TEACHER’S-LOCATION
Subject table- teacher table-
SUBJECT- SUBJECT- SUBJECT- TEACHER’S-
ID NAME TEACHER LOCATION
CS-1 DBMS T-1 A-BLOCK
CS-2 OS T-2 B-BLOCK
CS-3 NETWORK T-3 C-BLOCK
CS-4 C++ T-4 D-BLOCK
11
Boyce-codd Normal Form
Advanced form of 3 NF.
Known as 3.5 NF.
Removes non-trivial functional dependency.
course table-
STUDENT- SUBJECT- TEACHER
NAME NAME
TEACHER SUBJECT-NAME
ARUN DBMS T-1
but non prime attribute
PUJA OS T-2
cannot determine prime
AMAN DBMS T-3 attribute.
12
Decomposition to BCNF
STUDENT- SUBJECT- SUBJECT- TEACHER
NAME NAME NAME
ARUN DBMS DBMS T-1
PUJA DBMS DBMS T-2
AMAN OS OS T-3
13
Fourth Normal Form
Removes multivalued dependency.
x y (x multi-determines y)
SUBJECT-NAME TEACHER SUBJECT- TEACHER BOOK
NAME
SUBJECT-NAME BOOK DBMS T-1 KORTH
DBMS T-2 NAVATHE
but TEACHER and BOOK
attributes not dependent OS T-3 GELVIN
14
Decomposition to 4NF
SUBJECT-NAME TEACHER SUBJECT-NAME BOOK
DBMS T-1 DBMS KORTH
DBMS T-2 DBMS NAVATHE
OS T-3 OS GELVIN
15
Fifth Normal Form
Known as Project join Normal Form (PJ/Nf).
Does not have any join dependency.
Joining must be lossless.
- decomposed tables can recreate the original table.
16
Decomposition to 5NF
SELLER COMPANY PRODUCT
S-1 COCA-COLA THUMPS UP
S-2 PEPSICO PEPSI
SELLER COMPANY SELLER PRODUCT COMPANY PRODUCT
S-1 COCA-COLA S-1 THUMPS UP COCA-COLA THUMPS UP
S-2 PEPSICO S-2 PEPSI PEPSICO PEPSI
17
Conclusion
It makes the relational model more informative and
user friendly.
No redundant data.
No anomalies.
18
References
file:///E:/Study/Fundamentals_of_Database_Systems,_6th_
Edition_(0136086209).pdf
Database System concepts by- Silberschatz
Korth
Sudarshan
Fundamentals of Database System- Elmasri
Navathe
19
THANK YOU!!
20
