Normal Forms

Chittaranjan Pradhan

Database Management Normal Forms

1NF(First Normal

System 17 Form)

Partial FD

Normal Forms 2NF(Second Normal

Form)

Transitive FD

3NF(Third Normal
Form)

BCNF (Boyce-Codd
Normal Form)

Chittaranjan Pradhan
School of Computer Engineering,
KIIT University
17.1
Normal Forms
Normal Forms
Chittaranjan Pradhan

Normal Forms

1NF(First Normal
Form)

Partial FD

2NF(Second Normal
Normal Forms Form)

Transitive FD
• Normal forms provide a stepwise progression towards the
3NF(Third Normal
construction of normalized relation schemas, which are Form)

free from data redundancies BCNF (Boyce-Codd

Normal Form)

• A relation schema is said to be in a particular normal form

if it is satisfying certain defined conditions

17.2
Normal Forms
1NF(First Normal Form)
Chittaranjan Pradhan

Normal Forms
1NF(First Normal Form) 1NF(First Normal
Form)
A relation is in 1NF iff the values in the relation are atomic and Partial FD

single-valued for every attribute in the relation 2NF(Second Normal

Form)

Transitive FD
Module Dept Lecturer Text 3NF(Third Normal
M1 D1 L1 T1 ,T2 Form)

M2 D1 L1 T1 ,T3 BCNF (Boyce-Codd

Course Normal Form)
M3 D1 L2 T4
M4 D2 L3 T1 ,T5
M5 D2 L4 T6

• As the Text column values are not atomic, this relation is

not present in 1NF
• To convert this non-1NF relation into a 1NF relation, split
up the non-atomic values

17.3
Normal Forms
1NF(First Normal Form)...
Chittaranjan Pradhan
Module Dept Lecturer Text
M1 D1 L1 T1
Normal Forms
M1 D1 L1 T2
1NF(First Normal
M2 D1 L1 T1 Form)
Course1 M2 D1 L1 T3 Partial FD
M3 D1 L2 T4 2NF(Second Normal
Form)
M4 D2 L3 T1
Transitive FD
M4 D2 L3 T5
3NF(Third Normal
M5 D2 L4 T6 Form)

BCNF (Boyce-Codd
Module Dept Lecturer Text1 Text2 Normal Form)
M1 D1 L1 T1 T2
M2 D1 L1 T1 T3
Course2
M3 D1 L2 T4
M4 D2 L3 T1 T5
M5 D2 L4 T6

Corollary: As the relation schema contains no data values,

therefore all relation schemas are in 1NF
Anomalies in 1NF Relations:
• Insertion anomalies
• Updation anomalies
• Deletion anomalies 17.4
Normal Forms
Partial FD
Chittaranjan Pradhan

Normal Forms

1NF(First Normal
Form)

Partial FD
Partial FD 2NF(Second Normal
Form)
A FD A → B is a partial FD, if some attribute of A can be Transitive FD
removed and the FD still holds. That means there is some 3NF(Third Normal
proper subset of A, C ⊂ A, such that C → B Form)

BCNF (Boyce-Codd
Normal Form)

• Key attributes: are the attributes which are part of some

candidate key
• Non-key attributes: are the attributes which are not part
of any candidate key

17.5
Normal Forms
2NF(Second Normal Form)
Chittaranjan Pradhan
2NF(Second Normal Form)
A relation is in 2NF iff the following two conditions are met Normal Forms

simultaneously: 1NF(First Normal

Form)

• It is in 1NF Partial FD

2NF(Second Normal
• No non-key attribute is partially dependent on any key Form)

Transitive FD

A non-2NF relation can be decomposed into 2NF relations by 3NF(Third Normal

Form)
following: BCNF (Boyce-Codd
• Create a new relation by using the attributes from the Normal Form)

offending FD as the attributes in the new relation. The

determinant of the FD becomes the primary key of the
new relation
• The attribute on the RHS of the FD is then eliminated from
the original relation
• If more than one FD prevents the relation from being 2NF,
repeat steps 1 and 2 for each offending FD
• If the same determinant appears in more than one FD,
place all the attributes functionally dependent on this
determinant as non-key attributes in the relation having the
determinant as the primary key 17.6
Normal Forms
2NF(Second Normal Form)...
Chittaranjan Pradhan

Normal Forms

1NF(First Normal
Form)

Module Dept Lecturer Text Partial FD

M1 D1 L1 T1 2NF(Second Normal
Form)
M1 D1 L1 T2
Transitive FD
M2 D1 L1 T1
3NF(Third Normal
Course M2 D1 L1 T3 Form)
M3 D1 L2 T4 BCNF (Boyce-Codd
Normal Form)
M4 D2 L3 T1
M4 D2 L3 T5
M5 D2 L4 T6

F={Module→Dept, Module→Lecturer, Lecturer→Dept,

{Module, Text}→{Dept, Lecturer}}
Here, Key:{Module, Text}

17.7
Normal Forms
2NF(Second Normal Form)...
Chittaranjan Pradhan

Normal Forms

Module Dept Lecturer 1NF(First Normal

Form)
M1 D1 L1
M2 D1 L1 Partial FD
Course1 M3 D1 L2 2NF(Second Normal
M4 D2 L3 Form)
M5 D2 L4 Transitive FD

3NF(Third Normal
F1 ={Module→{Dept, Lecturer}, Lecturer→Dept} Form)

BCNF (Boyce-Codd
Module Text Normal Form)

M1 T1
M1 T2
M2 T1
Course2 M2 T3
M3 T4
M4 T1
M4 T5
M5 T6

F2 ={{Module, Text}}→{{Module, Text}}

17.8
Normal Forms
2NF(Second Normal Form)...
Chittaranjan Pradhan

Normal Forms

1NF(First Normal
Form)
Corollary: If the primary key has a single attribute, then the Partial FD
relation is in 2NF 2NF(Second Normal
Form)

Transitive FD
Anomalies in 2NF Relations:
3NF(Third Normal
• Insertion anomalies Form)

BCNF (Boyce-Codd
• Updation anomalies Normal Form)

• Deletion anomalies
Q: R=(A, B, C, D, E), & F={A → {B, C, D, E}, {A, B} → {C, D, E},
C → E, D → E}

Q: R=(A, B, C, D, E), & F={{A, B} → {C, D, E}, B → C, A → D}

17.9
Normal Forms
Transitive FD
Chittaranjan Pradhan

Normal Forms

1NF(First Normal
Form)

Partial FD

2NF(Second Normal
Form)

Transitive FD Transitive FD

3NF(Third Normal
A FD A → C is a transitive FD, if there are some set of Form)

attributes B such that A → B and B → C are non-trivial FDs BCNF (Boyce-Codd

Normal Form)

A → B non-trivial means B is not a subset of A

17.10
Normal Forms
3NF(Third Normal Form)
Chittaranjan Pradhan

3NF(Third Normal Form)

Normal Forms

A relation is in 3NF iff the following two conditions are satisfied 1NF(First Normal
Form)
simultaneously:
Partial FD
• It is in 2NF 2NF(Second Normal
Form)
• No non-key attribute is transitively dependent on the key Transitive FD

The process of decomposing the non-3NF relation into 3NF 3NF(Third Normal
Form)
relations is similar to the process of decomposing the non-2NF BCNF (Boyce-Codd
relation to 2NF relations Normal Form)

Module Dept Lecturer

M1 D1 L1
M2 D1 L1
Course M3 D1 L2
M4 D2 L3
M5 D2 L4

F={Module→{Dept, Lecturer}, Lecturer→Dept}

This relation is not present in 3NF because Module → Lecturer

and Lecturer → Dept
17.11
Normal Forms
3NF(Third Normal Form)...
Chittaranjan Pradhan

Normal Forms

1NF(First Normal
Module Lecturer Form)
M1 L1
M2 L1 Partial FD
Course1 M3 L2 2NF(Second Normal
M4 L3 Form)
M5 L4
Transitive FD

3NF(Third Normal
F1 ={Module→Lecturer} Form)

BCNF (Boyce-Codd
Lecturer Dept Normal Form)
L1 D1
Course2 L2 D1
L3 D2
L4 D2

F2 ={Lecturer→Dept}

Corollary: A 2NF relation is in 3NF if no non-key attribute

functionally determines any other non-key attribute

17.12
Normal Forms
3NF(Third Normal Form)...
Chittaranjan Pradhan
The 3NF helped us to get rid of the anomalies caused by
dependencies of a non-key attribute on another non-key
Normal Forms
attribute
1NF(First Normal
Form)

However, relations in 3NF are still susceptible to anomalies Partial FD

when the relations have two overlapping candidate keys or 2NF(Second Normal
Form)
when non-key attribute functionally determines a key attribute. Transitive FD

Overlapping candidate keys means composite candidate keys 3NF(Third Normal

Form)
with at least one attribute in common among themselves
BCNF (Boyce-Codd
Normal Form)

Note: A database should normally be in 3NF at least

Q: Lecturer = (lectid, lectname, courseid, coursename) &

F={lectid → lectname, lectid → courseid, lectid → coursename,
courseid → coursename}

Q: R=(B, C, E), F= {E → B, {B,C} → E}

Q: Store = (order, product, customer, address, qty, unitprice) &

F= {order → {customer, address}, customer → address,
product → unitprice, {order, product} → qty} 17.13
Normal Forms
BCNF (Boyce-Codd Normal Form)
Chittaranjan Pradhan
BCNF (Boyce-Codd Normal Form)
A relation is in BCNF iff the following two conditions are Normal Forms

satisfied simultaneously: 1NF(First Normal

Form)

• It is in 3NF Partial FD

2NF(Second Normal
• If for every non-trivial functional dependency, the Form)

determinant is a key Transitive FD

3NF(Third Normal
The process of decomposing the non-BCNF relation into BCNF Form)

relations is a simple process. For each non-trivial FD where the BCNF (Boyce-Codd
Normal Form)
determinant is not the key, construct new relations

Student Course Time

Rahul Database 12 : 00
Pratik Database 12 : 00
Student Praveen Database 15 : 00
Praveen Programming 10 : 00
Rajib Programming 10 : 00
Shivam Programming 13 : 00

F={{Student, Course} → Time, Time → Course, {Student,

Time} → Course}
Key={Student, Course} and {Student, Time} 17.14
Normal Forms
BCNF (Boyce-Codd Normal Form)...
Chittaranjan Pradhan

Normal Forms

1NF(First Normal
Form)
This relation is not present in BCNF as in FD Time → Course; Partial FD
the determinant {Time} is not a key 2NF(Second Normal
Form)

Transitive FD
After the conversion of this relation to BCNF, create a new
3NF(Third Normal
relation R1 =(Time, Course) with set of FDs F1 ={Time → Form)

Course} BCNF (Boyce-Codd

Normal Form)

The original relation is changed to R=(Student, Time) as

{Student, Time} set is also the key of the relation

Here, we have lost the functional dependency {Student,

Course} → Time

17.15
Normal Forms
BCNF (Boyce-Codd Normal Form)...
Chittaranjan Pradhan
Corollary: If a relation has only one candidate key, then
3NF and BCNF are same. That means if a relation is in 3NF
Normal Forms
having only one candidate key, then it is also present in
1NF(First Normal
BCNF Form)

Partial FD

Note: Normalization to 3NF is always lossless and 2NF(Second Normal

Form)
dependency preserving. But, normalization to BCNF is Transitive FD

lossless, but may not preserve all the functional 3NF(Third Normal
Form)
dependencies
BCNF (Boyce-Codd
Normal Form)

Q: R =(A, B, C, D, E), F={A → {B, E}, C → D}. Decompose the

relation to BCNF

Q: R=(A, B, C, D), F={{A, B} → {C, D}, C → B}. Decompose the

relation into BCNF

Q: R =(A, B, C, D, E, G), F={{A, B} → {C, D}, {B, C} → {D, A}, C

→ G, B → E}. Decompose this relation to BCNF

Q: R =(A, B, C, D) & F= {{A, C} → {B, D}, {B, C} → {D, A}, A →

B, B → A}. Decompose this relation to BCNF 17.16