Normalization II
CS 341 Database Systems
Normalization Forms
Fall 2025 Database Systems – Abeera Tariq / Maria Rahim 2
Normalize up to 3NF
Fall 2025 Database Systems – Abeera Tariq / Maria Rahim 3
1NF
Fall 2025 Database Systems – Abeera Tariq / Maria Rahim 4
Functional Dependencies
Fall 2025 Database Systems – Abeera Tariq / Maria Rahim 5
Identifying Dependencies
• 1NF
ClientRental (clientNo, propertyNo, cName, pAddress, rentStart, rentFinish, rent,
ownerNo, oName)
Partial dependencies
clientNo → cName
propertyNo → pAddress, rent, ownerNo, oName
Transitive dependencies
ownerNo → oName
Fall 2025 Database Systems – Abeera Tariq / Maria Rahim 6
Converting to 2 NF
Partial dependencies
clientNo → cName
propertyNo → pAddress, rent, ownerNo, oName
Rental (clientNo, propertyNo, rentStart, rentFinish)
Client (clientNo, cName)
PropertyOwner (propertyNo, pAddress, rent, ownerNo, oName)
Fall 2025 Database Systems – Abeera Tariq / Maria Rahim 7
2NF
Fall 2025 Database Systems – Abeera Tariq / Maria Rahim 8
Converting to 3NF
Transitive dependencies
ownerNo → Oname
Owner (ownerNo, oName)
PropertyForRent (propertyNo, pAddress, rent, ownerNo)
Rental (clientNo, propertyNo, rentStart, rentFinish)
Client (clientNo, cName)
Fall 2025 Database Systems – Abeera Tariq / Maria Rahim 9
3NF
Fall 2025 Database Systems – Abeera Tariq / Maria Rahim 10
Higher-Level Normal
Forms
11
Boyce-Codd Normal Form (BCNF)
• Every determinant in the table is a candidate key
• Determinant is attribute whose value determines other values in row
• 3NF table with one candidate key is already in BCNF
Fall 2025 Database Systems – Abeera Tariq / Maria Rahim 12
• The CLASS table has two candidate keys:
• CLASS_CODE
• CRS_CODE + CLASS_SECTION
• 1NF: Key attributes defined, all non-key attributes are determined by the key.
(True for both candidate keys).
• 2NF: 1NF and no partial dependencies on either candidate key.
• 3NF: 2NF and no transitive dependencies.
Fall 2025 Database Systems – Abeera Tariq / Maria Rahim 13
3NF Table Not in BCNF
• Note these functional
dependencies
• A + B → C ,D
• A + C → B, D
• C→B
• Candidate Keys:
(A+B) and (A+C)
Fall 2025 Database Systems – Abeera Tariq / Maria Rahim 14
Decomposition
of Table
Structure to
Meet BCNF
Fall 2025 Database Systems – Abeera Tariq / Maria Rahim 15
Scenario Conditions
• CLASS_CODE identifies a class uniquely. This condition illustrates the
case in which a course might generate many classes for example
same course has more sections.
• A student can take many classes and earn grades.
• A staff member can teach many classes, but each class is taught by
only one staff member.
Fall 2025 Database Systems – Abeera Tariq / Maria Rahim 16
STU_ID + CLASS_CODE (Candidate Key)
STU_ID + STAFF_ID (Candidate Key)
Fall 2025 Database Systems – Abeera Tariq / Maria Rahim 17
Anomalies
• The problem: Trying to describe two things:
staff assignments to classes and student enrollment information.
• Such a dual-purpose table structure will cause anomalies.
• For example, if a different staff member is assigned to teach class 32456, two
rows will require updates, thus producing an update anomaly.
• If student 135 drops class 28458, information about who taught that class is lost,
thus producing a deletion anomaly.
• The solution to the problem is to decompose the table structure
Fall 2025 Database Systems – Abeera Tariq / Maria Rahim 18
Fall 2025 Database Systems – Abeera Tariq / Maria Rahim 19
Fourth Normal Form (4NF)
• A relation will be in 4NF if it is in Boyce Codd Normal Form (BCNF)
and has no multi-valued dependency.
• For a dependency A → B, if for a single value of A, multiple values
of B exists, then the relation will be a multi-valued dependency.
Fall 2025 Database Systems – Abeera Tariq / Maria Rahim 20
Fourth Normal Form (4NF)
• Suppose an employee can
have multiple assignments
and can also be involved in
multiple service
organizations.
• Suppose:
• Employee 10123 does
volunteer work for the Red
Cross and United Way.
• The same employee might be
assigned to work on three
projects: 1, 3, and 4.
Fall 2025 Database Systems – Abeera Tariq / Maria Rahim 21
Rules for 4NF
If you follow proper design rules, you will not have such problems.
Discussion on 4NF is largely academic if your tables conform to the
following rules.
1. All attributes must be dependent on the primary key, but they must
be independent of each other.
2. No row may contain two or more multivalued facts about an entity.
Fall 2025 Database Systems – Abeera Tariq / Maria Rahim 22
Conversion to 4 NF
• The solution is to eliminate the problems caused by the
multivalued dependency.
• You do this by creating new tables for the components of
the multivalued dependency.
Fall 2025 Database Systems – Abeera Tariq / Maria Rahim 23
The multivalued dependency is
resolved and eliminated by
creating the ASSIGNMENT and
SERVICE_V1 tables. Those tables
are said to be in 4NF.
Fall 2025 Database Systems – Abeera Tariq / Maria Rahim 24
5NF - Fifth Normal Form
• Fifth normal form (5NF), also known as project join normal form (PJNF),
addresses the issue in which a table cannot be decomposed anymore
without losing data or creating incorrect information.
• In other words, a table is in 4NF and cannot have further lossless
decompositions.
• Reduce 4NF to 5 NF, by removing pairwise cyclic dependencies (appearing
within composite primary keys with three or more component attributes) to
three or more parent entities
Fall 2025 Database Systems – Abeera Tariq / Maria Rahim 25
5 NF
• This addresses problems that arise from representing associations
between multiple entities with interdependencies.
• Making it 5NF consists of adding parent tables, one for each
meaningful combination that has children in the original table.
• A table with such information is 5NF if the information cannot be
represented in multiple smaller entities alone.
Fall 2025 Database Systems – Abeera Tariq / Maria Rahim 26
The entities relate to each other cyclically. To resolve
5 NF - this, we would need to establish parent tables with
Actor - Play, Play - Theater, and Theater - Actor. These
EXAMPLE would each contain a portion of the Primary Key in the
Actor, Play, and Theater table.
Actor Play Theater Actor Play Theatre
Play Theatre Actor
Billy Bob Catcher in the Rye West 42nd
Ann Catcher in the Rye West 42nd
John Catch-22 Broadway
Lily Hamlet Broadway
Actor
Lisa Cats West 42nd Play
Andy Cats Darlington Theatre
Fall 2025 Database Systems – Abeera Tariq / Maria Rahim 28
Lossless Decomposition when the decomposed tables are joined it
recreates the original table. There is no missing data nor new erroneous data.
A table is in fifth normal form (5NF) when it is in 4NF and it cannot have further lossless decompositions.
Here the original table is not in 5NF, but the decomposed tables are in 5NF
Fall 2025 Database Systems – Abeera Tariq / Maria Rahim 29
Normalization Forms
Fall 2025 Database Systems – Abeera Tariq / Maria Rahim 30
Why Denormalization
• Normalization is one of many database design goals
• Normalized table requirements
• Additional processing – process together decomposed tables
• Loss of system speed – lots of joins
• Normalization purity is sometimes difficult to sustain due to conflict in:
• Design efficiency
• Information requirements
• Processing
Fall 2025 Database Systems – Abeera Tariq / Maria Rahim 32
Data Modeling Checklist
Fall 2025 Database Systems – Abeera Tariq / Maria Rahim 33
Fall 2025 Database Systems – Abeera Tariq / Maria Rahim 34
Fall 2025 Database Systems – Abeera Tariq / Maria Rahim 35
• ER-Model design process usually caters to the normalization process but often
things get missed out.
• In most cases for OLTP systems, a normalized ERD
up to 3 NF suffice also ensuring data integrity and avoidance of anomalies.
Fall 2025 Database Systems – Abeera Tariq / Maria Rahim 36