# The Normal Forms 3NF And BCNF

Topics: Database normalization, Third normal form, Relational model Pages: 27 (1137 words) Published: January 7, 2015
The Normal Forms
3NF and BCNF
Yunliang Jiang

Housekeeping
• HW2 due tonight
– Upload a single PDF/DOC file to Compass

• Stage 3 due tonight
• Midterm tomorrow
– During class time.

Preview

Normalization
Solution: Normal Forms
Introducing 3NF and BCNF
3NF
Examples
BCNF

Normalization
• Normalization is the process of efficiently
organizing data in a database with two goals
in mind
• First goal: eliminate redundant data
– for example, storing the same data in more than
one table

• Second Goal: ensure data dependencies make
sense
– for example, only storing related data in a table

Benefits of Normalization

Less storage space
Less data inconsistency
Clearer data relationships
Flexible Structure

The Solution: Normal Forms
• Bad database designs results in:
– redundancy: inefficient storage.
– anomalies: data inconsistency, difficulties in
maintenance

• 1NF, 2NF, 3NF, BCNF are some of the early
forms in the list that address this problem

Third Normal Form (3NF)
1) Meet all the requirements of the 1NF

2) Meet all the requirements of the 2NF
3) Remove columns that are not dependent upon
the primary key.

1) First normal form -1NF
• 1NF : if all attribute values are atomic: no
repeating group, no composite attributes
– Really easy to achieve

• The following table is not in 1NF
DPT_NO

MG_NO

EMP_NO

EMP_NM

D101

12345

20000
20001
20002

Carl Sagan
Mag James
Larry Bird

D102

13456

30000
30001

Jim Carter
Paul Simon

Table in 1NF
DPT_NO

MG_NO

EMP_NO

EMP_NM

D101

12345

20000

Carl Sagan

D101

12345

20001

Mag James

D101

12345

20002

Larry Bird

D102

13456

30000

Jim Carter

D102

13456

Paul Simon
30001

• all attribute values are atomic because there are no repeating group and no composite attributes.

2) Second Normal Form
• Second normal form (2NF) further addresses
the concept of removing duplicative data:
– A relation R is in 2NF if
• (a) R is 1NF , and
• (b) all non-prime attributes are fully dependent on the
candidate keys. Which is creating relationships between
these new tables and their predecessors through the
use of foreign keys.

– A prime attribute appears in a candidate key.
– There is no partial dependency in 2NF.
Example is next…

No dependencies on non-key attributes
Inventory
Description

Supplier

Cost

There are two non-key fields. So, here are the questions:
•If I know just Description, can I find out Cost? No, because we have more than one supplier for the same product.
•If I know just Supplier, and I find out Cost? No, because I need to know what the Item is as well.
Therefore, Cost is fully, functionally dependent upon the
ENTIRE PK (Description-Supplier) for its existence.
Inventory
Description

Supplier

Cost

CONTINUED…
Inventory
Description

Supplier

Cost

•If I know just Description, can I find out Supplier Address? No, because we have more than one supplier for the same product. •If I know just Supplier, and I find out Supplier Address? Yes. The Address does not depend upon the description of the item. Therefore, Supplier Address is NOT functionally dependent upon the ENTIRE PK (Description-Supplier)

for its existence.

Supplier
Name

So putting things together
Inventory
Description

Supplier

Cost

Inventory
Description

Supplier

Cost
Supplier

Name

The above relation is now in 2NF since the relation has no nonkey attributes.

3NF – Remove columns that are not dependent upon
the primary key.
So for every nontrivial functional dependency X --> A,
(1) X is a superkey, or
(2) A is a prime (key) attribute.

Everything is dependent on the key or is in the key

Example of 3NF
Books...