Normalization

CS 341 Database Systems

Database Tables and Normalization
Table is basic building block in database design

Normalization is process for assigning attributes to entities such that

• Reduces data redundancies

• Helps eliminate data anomalies
• Produces controlled redundancies to link tables

Normalization stages

• 1NF - First normal form

• 2NF - Second normal form
• 3NF - Third normal form
• 4NF - Fourth normal form
• BCNF – Boyce Codd normal form
• 5NF – Fifth normal form

Fall 2025 Database Systems - Abeera Tariq / Maria Rahim 2

Informal Normalization Guidelines

• Attribute types from multiple entity types should not be

combined in a single relation
• Avoid excessive amount of NULL values in a relation

Fall 2025 Database Systems - Abeera Tariq / Maria Rahim 3

Normalization Forms

Fall 2025 Database Systems - Abeera Tariq / Maria Rahim 4

Fall 2025 Database Systems - Abeera Tariq / Maria Rahim 5
A closer look

Fall 2025 Database Systems - Abeera Tariq / Maria Rahim 6

Need for Normalization

• The project number (PROJ_NUM) is intended to be primary key or

at least part of a PK, but it contains NULLs
• Table entries invite data inconsistencies
• Table displays data anomalies

Fall 2025 Database Systems - Abeera Tariq / Maria Rahim 7

 Update
Data Anomalies Insertion
Deletion
 Update Anomalies
Modifying the JOB_CLASS for employee number 104 requires (potentially)
many alterations, one for each EMP_NUM = 104.

Fall 2025 Database Systems - Abeera Tariq / Maria Rahim 9

 Insertion Anomalies
Just to complete a row definition, a new employee must be assigned to a
project. If the employee is not yet assigned, a phantom project must be
created to complete the employee data entry.

Fall 2025 Database Systems - Abeera Tariq / Maria Rahim 10

 Deletion Anomalies
Suppose that only one employee is associated with a given project. If that
employee leaves the company and the employee data are deleted, the project
information will also be deleted.

Fall 2025 Database Systems - Abeera Tariq / Maria Rahim 11

Normalization Process

• The objective of normalization is to ensure that each table conforms to

the concept of well-formed relations, that is, tables that have the
following characteristics:
• Each table represents a single subject.
• No data item will be unnecessarily stored in more than one table (in short, tables
have minimum controlled redundancy).
• All nonprime attributes in a table are dependent on the primary key—the entire
primary key and nothing but the primary key.
• Each table is void of insertion, update, or deletion anomalies ensuring the
integrity and consistency of the data.

Fall 2025 Database Systems - Abeera Tariq / Maria Rahim 12

 Unnormalized Form (UNF)

• A table that
contains one or
more repeating
groups.

Fall 2025 Database Systems - Abeera Tariq / Maria Rahim 13

Fall 2025 Database Systems - Abeera Tariq / Maria Rahim 14
Functional Dependencies

• A functional dependency X → Y, between two sets of attribute

types X and Y implies that a value of X uniquely determines a
value of Y
• there is a functional dependency from X to Y or Y is functionally
dependent on X A B
1 4
• X → Y holds if whenever two tuples have the same value for X, 1 5
they must have the same value for Y 3 7
• Example: Consider r(A,B) with the following instance of r.
On this instance,
A → B does NOT hold,
but B → A does hold.
Fall 2025 Database Systems - Abeera Tariq / Maria Rahim 15
Partial Vs Transitive Dependency

• Partial
• Dependency on part of composite primary key

• Transitive
• One nonprime attribute depends on another nonprime attribute
• A transitive dependency exists when there are functional dependencies
such that X → Y, Y → Z, and X is the primary key.
In that case, the dependency X → Z is a transitive dependency because X
determines the value of Z via Y.

Fall 2025 Database Systems - Abeera Tariq / Maria Rahim 16

First Normal Form (1 NF)

• The first normal form (1 NF) states that every attribute type of a
relation must be atomic and single valued
• No composite or multivalued attribute types (domain constraint!)
• SUPPLIER(SUPNR, NAME(FIRST NAME, LAST NAME), SUPSTATUS)
• SUPPLIER(SUPNR, FIRST NAME, LAST NAME, SUPSTATUS)

Fall 2025 Database Systems - Abeera Tariq / Maria Rahim 17

Conversion to 1NF

Step 1: Eliminate the Repeating Groups

Step 2: Identify the Primary Key

Step 3: Identify All Dependencies

Fall 2025 Database Systems - Abeera Tariq / Maria Rahim 18

Unnormalized Form (UNF)

Fall 2025 Database Systems - Abeera Tariq / Maria Rahim 19

UNF

Fall 2025 Database Systems - Abeera Tariq / Maria Rahim 20

Identify Primary Key

Fall 2025 Database Systems - Abeera Tariq / Maria Rahim 21

First Normal
form (1NF)

Fall 2025 Database Systems - Abeera Tariq / Maria Rahim 22

Converted to 1NF - Proceed to identify dependencies

Fall 2025 Database Systems - Abeera Tariq / Maria Rahim 23

Partial
PROJ_NUM

Fall 2025 Database Systems - Abeera Tariq / Maria Rahim 24

Partial
EMP_NUM

Fall 2025 Database Systems - Abeera Tariq / Maria Rahim 25

Transitive
JOB_CLASS

Fall 2025 Database Systems - Abeera Tariq / Maria Rahim 26

First Normal
form (1NF)
dependency
diagram

Fall 2025 Database Systems - Abeera Tariq / Maria Rahim 27

Second Normal Form (2 NF)

• A relation R is in the second normal form (2 NF) if it satisfies 1 NF and

every non-prime attribute type A in R is fully functional dependent
on the key of R OR there are no partial dependencies
• If the relation is not in second normal form, we must:
• Decompose it and set up a new relation for each partial key
together with its dependent attribute types
• Keep a relation with the original primary key and any attribute
types that are fully functional dependent on it

Fall 2025 Database Systems - Abeera Tariq / Maria Rahim 28

Fall 2025 Database Systems - Abeera Tariq / Maria Rahim 29
Conversion to 2NF

• Start with 1NF format:

Example:
• Write each key component on separate line Proj_Num
Emp_Num
• Each component will become key in a new table Proj_Num, Emp_Num

• Write dependent attributes after each key

PROJECT (PROJ_NUM, PROJ_NAME)

EMPLOYEE (EMP_NUM, EMP_NAME, JOB_CLASS, CHG_HOURS)
ASSIGNMENT (PROJ_NUM, EMP_NUM, ASSIGN_HOURS)
Fall 2025 Database Systems - Abeera Tariq / Maria Rahim 30
Converted to 2NF

Fall 2025 Database Systems - Abeera Tariq / Maria Rahim 31

 Third Normal Form (3 NF)

• A functional dependency X → Z in a relation R is a transitive

dependency if there is a set of attribute types Y that is neither a
candidate key nor a subset of any key of R, and both X → Y and Y → Z
hold
• A relation is in the third normal form (3 NF) if it satisfies 2 NF and no
non-prime attribute type of R is transitively dependent on the primary
key
• If the relation is not in third normal form, we need to decompose the
relation R and set up a relation that includes the non-key attribute
types that functionally determine the other non-key attribute types

Fall 2025 Database Systems - Abeera Tariq / Maria Rahim 32

Conversion to 3NF

• Create separate table(s) to eliminate transitive functional dependencies

PROJECT (PROJ_NUM, PROJ_NAME)
ASSIGNMENT (PROJ_NUM, EMP_NUM, ASSIGN_HOURS)
EMPLOYEE (EMP_NUM, EMP_NAME, JOB_CLASS)
JOB (JOB_CLASS, CHG_HOUR)

• In 3NF
• Contains no transitive dependencies

Fall 2025 Database Systems - Abeera Tariq / Maria Rahim 33

Converted to
3NF
Fall 2025 Database Systems - Abeera Tariq / Maria Rahim 34
Improving the Design

• Minimize Data Entry Errors

• Job_class to Job_code with Job_class/description as attribute
• Evaluate Naming Conventions
• CHG_HOUR to JOB_CHG_HOUR to indicate belonging to Job table

• Refine Attribute Atomicity

• EMP_NAME to EMP_LNAME, EMP_FNAME, EMP_INITIAL

Fall 2025 Database Systems - Abeera Tariq / Maria Rahim 35

Improving the Design

• Identify New Attributes

• Consider the real-world scenario and add required attributes e.g
EMP_HIREDATE
• Identify New Relationships
• Initial report required modelling project manager so add new relation
• Refine Primary Keys as Required for Data Granularity
• Granularity refers to the level of detail represented by the values stored in a
table’s row. Data stored at its lowest level of granularity is said to be atomic data.
Re-evaluate granularity of ASSIGN_HOURS
Fall 2025 Database Systems - Abeera Tariq / Maria Rahim 36
Fall 2025 Database Systems - Abeera Tariq / Maria Rahim 37
Fall 2025 Database Systems - Abeera Tariq / Maria Rahim 38
Fall 2025 Database Systems - Abeera Tariq / Maria Rahim 39
Normalization and Database Design

• Normalization should be part of the design process

• E-R Diagram provides macro view
• Normalization provides micro view of entities
• Focuses on characteristics of specific entities
• May yield additional entities
• Difficult to separate normalization from E-R diagramming
• Business rules must be determined

Fall 2025 Database Systems - Abeera Tariq / Maria Rahim 40

Initial ERD for Contracting Company

Fall 2025 Database Systems - Abeera Tariq / Maria Rahim 42

Modified ERD for Contracting Company

JOB_CLASS(FK)

JOB_CLASS

Fall 2025 Database Systems - Abeera Tariq / Maria Rahim 43

Final ERD for Contracting Company

JOB_CLASS(FK) (IE)

JOB_CODE

Fall 2025 Database Systems - Abeera Tariq / Maria Rahim 44

Normalization Forms

Fall 2025 Database Systems - Abeera Tariq / Maria Rahim 45

Example –
Normalize to 3 NF

46
 ORDER
Customer No: 001964 Order Number: 00012345
Name: Amna Ali Order Date: 25-Sep-2023
Address: House 101
Block 7 Gulshan-e-Iqbal
Karachi
Product Product Unit Order Line
Number Description Price Quantity Total
ER101 Eraser 20.00 5 100.00
PN502 Pencil 10.00 10 100.00
PN404 Pen 100.00 1 100.00

Order Total: 300.00

ORDER (orderNo, orderDate, custNo, custName, custAdd,

(prodNo, prodDesc, unitPrice, ordQty, lineTotal)*, orderTotal

Fall 2025 Database Systems - Abeera Tariq / Maria Rahim 47

 UNF

orderNo orderDate custNo custName custAdd ()* orderTotal

00012345 25-Sep- 001964 Amna Ali House 101 [(ER101, Eraser, 20.00, 5, 100.00), 300.00
2023 Block 7 (PN502, Pencil, 10.00,10, 100.00),
Gulshan-e- (PN404, Pen, 100.00, 1, 100.00)]
Iqbal
Karachi

Fall 2025 Database Systems - Abeera Tariq / Maria Rahim 48

 Converting to 1 NF

orderNo orderDate custNo custName custAdd ()* orderTotal

00012345 25-Sep- 001964 Amna Ali House 101 ER101, Eraser, 20.00, 5, 100.00 300.00
2023 Block 7
Gulshan-e-
Iqbal Karachi
00012345 25-Sep- 001964 Amna Ali House 101 PN502, Pencil, 10.00,10, 100.00 300.00
2023 Block 7
Gulshan-e-
Iqbal Karachi
00012345 25-Sep- 001964 Amna Ali House 101 PN404, Pen, 100.00, 1, 100.00 300.00
2023 Block 7
Gulshan-e-
Iqbal Karachi

Fall 2025 Database Systems - Abeera Tariq / Maria Rahim 49

 Converting to 1 NF

orderNo orderDate custNo custName custAd prodNo prodDesc unitPrice ordQty lineTotal orderTotal
d

00012345 25-Sep-2023 001964 Amna Ali House ER101 Eraser 20.00 5 100.00 300.00
101
Block 7
Gulsha
n-e-
Iqbal
Karachi

00012345 25-Sep-2023 001964 Amna Ali House PN502 Pencil 10.00 10 100.00 300.00
101
Block 7
Gulsha
n-e-
Iqbal
Karachi

00012345 25-Sep-2023 001964 Amna Ali House PN404 Pen 100.00 1 100.00 300.00
101
Block 7
Gulsha
n-e-
Iqbal
Karachi

Fall 2025 Database Systems - Abeera Tariq / Maria Rahim 50

Identifying Dependencies

• 1NF
ORDER (orderNo, orderDate, custNo, custName, custAdd,
prodNo, prodDesc, unitPrice, ordQty, lineTotal, orderTotal)

Partial dependencies
orderNo → orderDate, custNo, CustName, custAdd,orderTotal
prodNo → prodDesc, unitPrice
Transitive dependencies
custNo → custName, custAdd

Fall 2025 Database Systems - Abeera Tariq / Maria Rahim 51

Converting to 2NF

Partial dependencies
orderNo → orderDate, custNo, CustName, custAdd, orderTotal
prodNo → prodDesc, unitPrice

ORDER (orderNo, orderDate, custNo, custName, custAdd, orderTotal)

PRODUCT (prodNo, prodDesc, unitPrice)
ORDER_DETAILS (orderNo, prodNo, ordQty, lineTotal)

Fall 2025 Database Systems - Abeera Tariq / Maria Rahim 52

Converting to 3NF

Transitive dependencies
custNo → custName, custAdd

ORDER (orderNo, orderDate, custNo, orderTotal)

PRODUCT (prodNo, prodDesc, unitPrice)
ORDER_DETAILS (orderNo, prodNo, ordQty, lineTotal)
CUSTOMER (custNo, custName, custAdd)

Fall 2025 Database Systems - Abeera Tariq / Maria Rahim 53

Normalization

UNF ORDER (orderNo, orderDate, custNo, custName, custAdd,

(prodNo, prodDesc, unitPrice, ordQty, lineTotal)*, orderTotal

1NF ORDER (orderNo, orderDate, custNo, custName, custAdd,

prodNo, prodDesc, unitPrice, ordQty, lineTotal, orderTotal)
2NF ORDER (orderNo, orderDate, custNo, custName, custAdd, orderTotal)
PRODUCT (prodNo, prodDesc, unitPrice,)
ORDER_DETAILS (orderNo, prodNo, ordQty, lineTotal)

3NF ORDER (orderNo, orderDate, custNo, orderTotal)

PRODUCT (prodNo, prodDesc, unitPrice,)
ORDER_DETAILS (orderNo, prodNo, ordQty, lineTotal)
CUSTOMER (custNo, custName, custAdd)

Fall 2025 Database Systems - Abeera Tariq / Maria Rahim 54