CHAPTER 5 – NORMALIZATION
Example 1
Consider the following dependency diagram and normalize till 3NF
form
Answer
Step 1: Convert to 1NF (First Normal Form)
1NF requires that:
1. The table has only atomic values (no repeating groups or multi-
valued attributes).
The given table has no multi-valued attributes or repeating groups,
so it is already in 1NF.
Step 2: Convert to 2NF (Second Normal Form)
2NF requires that:
1. The table is in 1NF.
2. All non-key attributes are fully functionally dependent on the
entire primary key (i.e., no partial dependencies).
Since the primary key is empno, let's analyze the functional
dependencies:
1. empno → hours, ename:
o hours and ename are fully dependent on empno. This is
fine.
2. contract_no → hotel_no, hotel_loc, hours:
o contract_no determines hotel_no, hotel_loc, and hours.
This means that contract_no is acting as a partial key
because it determines attributes (hotel_no, hotel_loc, and
hours) not dependent on empno.
o This introduces a partial dependency since hotel_no,
hotel_loc, and hours are dependent on contract_no,
which is not the primary key.
3. hotel_no → hotel_loc:
o Here, hotel_loc is dependent on hotel_no, but not on
empno. This is a transitive dependency that will need to
be addressed to achieve 3NF.
Thus, the table has partial dependencies, and we need to decompose
it to remove them.
Step 3: Decompose for 2NF
We need to split the table to remove the partial dependencies. We'll
create new tables based on the functional dependencies.
Decompose Based on contract_no → hotel_no, hotel_loc, hours:
Create a new table for contracts:
Contract(contract_no, hotel_no, hotel_loc, hours)
The remaining attributes in the EMP table will now depend
solely on empno:
Employee(empno, ename, contract_no)
Step 4: Convert to 3NF (Third Normal Form)
3NF requires that:
1. The table is in 2NF.
2. There are no transitive dependencies, meaning all non-key
attributes should depend only on the primary key, not on other
non-key attributes.
Let's examine the current tables:
Employee Table (empno → ename, contract_no):
● This table is already in 3NF because there are no transitive
dependencies.
Contract Table (contract_no → hotel_no, hotel_loc, hours):
● There is a transitive dependency here: hotel_no → hotel_loc.
● To remove this transitive dependency, we need to decompose
this table.
Decompose Based on hotel_no → hotel_loc:
● Create a new table for hotels:
Hotel(hotel_no, hotel_loc)
● Modify the Contract table to remove hotel_loc (since it is now
captured by the Hotel table):
Contract(contract_no, hotel_no, hours)
Final 3NF Schema
After decomposition, the database schema in 3NF consists of the
following tables:
1. Employee Table:
o Contains employee information and their contract
association:
Employee(empno, ename, contract_no)
2. Contract Table:
o Contains the contract information and hours worked:
Contract(contract_no, hotel_no, hours)
3. Hotel Table:
o Contains hotel information:
Hotel(hotel_no, hotel_loc)
EXAMPLE 2
Construct a dependency diagram of relation and optimize it up to
BCNF Normal form
ANSWER
construct a dependency diagram for the relation Table1(A, B, C, D, E,
F, G) with the given dependencies and normalize it up to BCNF
(Boyce-Codd Normal Form), we will follow these steps:
Step-by-Step Breakdown:
1. List of Functional Dependencies (FDs):
Given:
1. A → C, D, E, F, G
2. B → C, D, E, F, G
3. E → G
4. C → B
2. Dependency Diagram:
Let's create a dependency diagram to show the functional
dependencies.
● A determines C, D, E, F, and G.
● B determines C, D, E, F, and G.
● E determines G.
● C determines B.
Here's a diagram description (imagine arrows showing
dependencies):
A -----> C, D, E, F, G
B -----> C, D, E, F, G
E -----> G
C -----> B
Step 3: Analyze for Normalization up to BCNF
We will now normalize the relation step-by-step to BCNF.
Step 4: First Normal Form (1NF)
1NF requires that:
1. All attributes must contain only atomic values.
2. No repeating groups.
Since no repeating groups or non-atomic values are present, the
relation is already in 1NF.
Step 5: Second Normal Form (2NF)
2NF requires:
1. The relation must be in 1NF.
2. All non-prime attributes must be fully functionally dependent
on the entire primary key (i.e., no partial dependencies).
Let's determine the candidate keys:
● Candidate keys are the minimal sets of attributes that can
determine all other attributes.
From the dependencies:
● Both A and B individually determine all attributes, meaning
both A and B are candidate keys.
Now, we need to check for partial dependencies:
● In this case, there are no partial dependencies because A and B
each determine all other attributes.
Thus, the relation is already in 2NF.
Step 6: Third Normal Form (3NF)
3NF requires:
1. The relation must be in 2NF.
2. There must be no transitive dependencies (i.e., non-prime
attributes must not depend on other non-prime attributes).
Let's check for transitive dependencies:
● C → B introduces a transitive dependency because C
determines B, and B is part of a candidate key (but not a prime
attribute in this context).
● E → G introduces a transitive dependency because E
determines G, and G is dependent on a non-prime attribute.
We will need to decompose the relation to remove these transitive
dependencies.
Step 7: Decompose to 3NF
To achieve 3NF, we will break the transitive dependencies.
Decompose for C → B:
● We split the relation into two tables:
1. Table1(A, C, D, E, F, G): This retains the attributes
determined by A but removes B.
2. Table2(C, B): This table stores the dependency C → B.
Decompose for E → G:
● We split another table to resolve the transitive dependency:
1. Table1(A, C, D, E, F): Remove G from this table.
2. Table3(E, G): This table stores the dependency E → G.
Now we have:
1. Table1(A, C, D, E, F)
2. Table2(C, B)
3. Table3(E, G)
These tables are in 3NF because there are no partial or transitive
dependencies remaining.
Step 8: Boyce-Codd Normal Form (BCNF)
BCNF is a stricter form of 3NF. It requires:
1. The relation must be in 3NF.
2. Every determinant must be a candidate key.
Let’s check if our tables satisfy BCNF:
1. Table1(A, C, D, E, F):
o A is a candidate key, and it determines all attributes in
this table.
o This table is in BCNF.
2. Table2(C, B):
o C is a candidate key, and it determines B.
o This table is in BCNF.
3. Table3(E, G):
o E is a candidate key, and it determines G.
o This table is in BCNF.
Final BCNF Schema:
After normalizing up to BCNF, we have the following tables:
1. Table1(A, C, D, E, F):
o Attributes: A, C, D, E, F
o Dependency: A → C, D, E, F
2. Table2(C, B):
o Attributes: C, B
o Dependency: C → B
3. Table3(E, G):
o Attributes: E, G
o Dependency: E → G
