CS 340 — Database Systems

Normalization (1NF–BCNF)
Class Notes • July 27, 2026

1. Why Normalize?
Normalization is the process of organizing relational tables to reduce redundancy and avoid update,
insertion, and deletion anomalies. Without it, the same fact can end up stored in multiple rows, and
keeping those copies consistent becomes the application's problem instead of the database's.
Normalization works by decomposing tables according to a series of increasingly strict normal
forms, each one eliminating a specific category of anomaly.

2. Anomalies Normalization Prevents

• Update anomaly — a fact stored redundantly in multiple rows can be updated in some rows but

not others, leaving the data inconsistent.

• Insertion anomaly — you can't record a fact (e.g., a new product and its price) without also

having an unrelated fact available (e.g., an order that uses it).

• Deletion anomaly — deleting one fact accidentally destroys another, unrelated fact stored in the

same row (e.g., deleting the last order for a product erases the product's price entirely).

3. Keys, Refresher
• Candidate key — a minimal set of attributes that uniquely identifies a row.

• Primary key — the candidate key chosen to be the table's main identifier.

• Superkey — any set of attributes that uniquely identifies a row, not necessarily minimal (a

candidate key plus extra attributes is still a superkey).

• Foreign key — an attribute (or set of attributes) in one table that references the primary key of

another, enforcing referential integrity.

4. Functional Dependencies
A functional dependency X → Y means that the value of attribute set X uniquely determines the
value of Y: any two rows agreeing on X must also agree on Y. Normal forms are formally defined in
terms of functional dependencies, and identifying them correctly is the real skill behind
normalization — the 'splitting tables' part is mechanical once the dependencies are known.

• Full functional dependency — Y depends on the whole of a composite key X, not just part of it.

• Partial dependency — Y depends on only part of a composite key (this is what 2NF eliminates).

• Transitive dependency — X → Y and Y → Z, so X → Z indirectly through Y (this is what 3NF

eliminates).

5. The Normal Forms

5.1 First Normal Form (1NF)

• Each column holds atomic (indivisible) values — no comma-separated lists inside a single cell.

• Each row is unique, typically enforced with a primary key.

• No repeating groups or arrays within a single column (e.g., no 'Phone1, Phone2, Phone3'

columns).

A table with a 'Phones' column containing '555-1234, 555-5678' violates 1NF; the fix is a separate
PhoneNumbers table with one row per phone number, linked back by a foreign key.

5.2 Second Normal Form (2NF)

• Must already satisfy 1NF.

• No partial dependency: every non-key attribute must depend on the entire primary key, not just

part of it.

2NF is only a meaningful concern when the primary key is composite (made of more than one
attribute) — if the key is a single column, 1NF automatically implies 2NF.
5.3 Third Normal Form (3NF)

• Must already satisfy 2NF.

• No transitive dependency: non-key attributes must depend only on the primary key, not on other

non-key attributes.

A classic example: a table with (StudentID, StudentName, DeptID, DeptBuilding) has DeptBuilding
depending on DeptID, not directly on StudentID — a transitive dependency that 3NF removes by
splitting out a Departments table.

5.4 Boyce-Codd Normal Form (BCNF)

A stricter version of 3NF: for every non-trivial functional dependency X → Y, X must be a superkey.
3NF has a subtle loophole BCNF closes — it's possible to satisfy 3NF while still having a non-
superkey determine part of a candidate key, which BCNF forbids. Most schemas that satisfy 3NF
also satisfy BCNF in practice, but not always.

5.5 Fourth and Fifth Normal Form (Brief)

Beyond BCNF, 4NF addresses multi-valued dependencies (e.g., an employee having independent,
unrelated sets of skills and languages shouldn't be cross-joined into one table), and 5NF addresses
join dependencies that cannot be decomposed further without losing information. These are rarely
needed in ordinary application schemas but matter in specialized, highly normalized data
warehouses.

6. Worked Example: Orders Table

Consider an unnormalized table of orders:

OrderID CustomerName Product ProductPrice

101 Ama Owusu Laptop 5000
101 Ama Owusu Mouse 80
102 Kofi Mensah Laptop 5000
The composite key here is (OrderID, Product). ProductPrice depends only on Product, not on the full
key — a partial dependency, which violates 2NF. CustomerName depends only on OrderID, another
partial dependency. The 2NF-compliant fix splits this into three tables:
Orders(OrderID, CustomerName)

Products(Product, ProductPrice)

OrderItems(OrderID, Product)

Now ProductPrice is stored exactly once per product, no matter how many orders reference it —
updating a price is a single-row change instead of a multi-row hunt-and-replace, and a product's price
can be recorded before any order ever references it.

7. Denormalization: The Deliberate Tradeoff

Normalization isn't free: more tables means more joins, and joins cost query time. In read-heavy
analytical systems — reporting dashboards, data warehouses — schemas are sometimes deliberately
denormalized, reintroducing some redundancy to avoid expensive joins on every query. Star schemas
in data warehousing are a well-known example: a central fact table joined to denormalized dimension
tables, optimized for fast aggregate queries rather than for update safety.

8. Practical Guidance
• Most production schemas target 3NF as a practical balance between anomaly-freedom and query

complexity.

• Normalize first for correctness, then denormalize selectively and deliberately, backed by

profiling, if performance requires it.

• Foreign keys and constraints should still be declared even in denormalized designs, to catch

inconsistencies as early as possible.

9. Key Takeaways
• Each normal form builds on the previous one — a table cannot be in 3NF without also being in

2NF and 1NF.

• Normalization is fundamentally about functional dependencies, not just visually 'splitting tables'

— always ask what determines what.

• BCNF closes a subtle loophole left open by 3NF involving non-superkey determinants.
 • Denormalization is a legitimate, deliberate performance tradeoff in read-heavy systems, not a

mistake — but it should be a conscious choice, not a default.

10. Practice Problems

1. Given a table Enrollment(StudentID, CourseID, StudentName, CourseName, Grade), identify all

functional dependencies and normalize it to 3NF.

2. Explain, with an example, why a table can satisfy 3NF but fail BCNF.

3. Design a star schema for an e-commerce sales fact table with dimensions for Customer, Product,

and Date.

4. Give an example of an insertion anomaly in an unnormalized schema of your own choosing.

11. Worked Solutions

Solution 1

In Enrollment(StudentID, CourseID, StudentName, CourseName, Grade), the primary key is

(StudentID, CourseID). StudentName depends only on StudentID, and CourseName depends only on
CourseID — both partial dependencies, violating 2NF. The 3NF-compliant decomposition is
Students(StudentID, StudentName), Courses(CourseID, CourseName), and Enrollments(StudentID,
CourseID, Grade), where Grade genuinely depends on the whole composite key.

Solution 2

Consider a table Bookings(RoomID, TimeSlot, Guest) where the key is (RoomID, TimeSlot), plus a
rule that each Guest can only book one room at a time, so Guest → TimeSlot is also a valid
dependency. This table can satisfy 3NF (Guest is not a non-key attribute causing a transitive
dependency in the usual sense) while still violating BCNF, because Guest determines TimeSlot but
Guest alone is not a superkey of the table.

Solution 3

A star schema would have a central Fact_Sales table with foreign keys CustomerKey, ProductKey,
DateKey, plus measures like Quantity and Revenue. Dim_Customer, Dim_Product, and Dim_Date
would each be denormalized dimension tables holding descriptive attributes (e.g., Dim_Date holding
Year, Quarter, Month, DayOfWeek directly, rather than requiring joins or date-function calls to
derive them), optimized for fast aggregation and filtering in reporting queries.

Solution 4

In a single Orders table storing (OrderID, ProductName, ProductPrice, CustomerID), you cannot add
a new product to the catalog and record its price until at least one order for it exists, because the
ProductPrice attribute only exists as part of an order row — that's a textbook insertion anomaly, fixed
by giving Products its own table independent of Orders.

12. Glossary
• Anomaly — an inconsistency risk (update, insertion, or deletion) caused by redundant data in an

unnormalized table.

• Candidate key — a minimal attribute set that uniquely identifies a row.

• Functional dependency — a rule X → Y meaning X's value determines Y's value.

• Normal form — a formally defined level of redundancy-freedom a table can satisfy (1NF, 2NF,

3NF, BCNF, and beyond).

• Referential integrity — the guarantee that a foreign key always points to an existing row in the

referenced table.

• Star schema — a denormalized data warehouse design with a central fact table and surrounding

dimension tables.

• Transitive dependency — X → Y → Z, so X determines Z only indirectly through Y.

13. Frequently Asked Questions

Do I always need to normalize all the way to BCNF?

No. Most application databases stop at 3NF, which eliminates the vast majority of real-world
anomalies while keeping the schema reasonably simple to query. BCNF and beyond are used
selectively, mainly when a specific anomaly caused by a non-superkey determinant is actually
observed or anticipated.

Is a normalized schema always slower to query?

Not necessarily — normalized schemas can be faster for writes and for queries that only touch one
table, and modern query planners handle joins efficiently, especially with proper indexes on foreign
keys. The join overhead becomes a real concern mainly at large analytical scale, which is why
denormalization is targeted there specifically rather than applied everywhere by default.

How do functional dependencies relate to foreign keys?

A foreign key is essentially a functional dependency made explicit and enforced by the database
engine: it guarantees that the referencing column determines a valid, existing row in the referenced
table, which is exactly the kind of dependency normalization theory is built around.

14. Extended Case Study: Normalizing a Startup's Customer

Database
Consider a small e-commerce startup whose engineering team started, as many do, with a single wide
Customers spreadsheet-turned-table: CustomerID, CustomerName, ShippingAddress,
LastOrderProduct, LastOrderPrice, LastOrderDate, and a free-text Notes field where support staff jot
down anything relevant. As the company grew, this design started causing real, tangible problems
that map directly onto the anomalies described in Section 2.

The first problem discovered was an update anomaly: a customer's shipping address changed, but
because the address only lived in this one table and different team members updated different records
at different times, some downstream reports (built by joining against older cached exports) kept
showing the old address for weeks after the change. The second problem was an insertion anomaly of
the LastOrderProduct/LastOrderPrice pair: marketing wanted to pre-load a catalog of upcoming
products with their launch prices before any customer had ordered them, but the schema had no way
to record a product's price except as a side effect of a customer's order, since ProductPrice only
existed inside a customer row. The third was a deletion anomaly: when a customer account was
deleted for GDPR compliance, the only record of that product's most recent sale price sometimes
disappeared along with it, corrupting historical pricing reports that had nothing to do with that
specific customer.

The fix followed exactly the decomposition process in Section 6. The team identified the functional
dependencies: CustomerName and ShippingAddress depend only on CustomerID; ProductPrice
depends only on a product identifier, not on any particular customer or order; and the relationship
between a customer and a specific order is its own many-to-one fact. The redesigned schema split the
single wide table into Customers(CustomerID, CustomerName, ShippingAddress),
Products(ProductID, ProductName, ProductPrice), and Orders(OrderID, CustomerID, ProductID,
OrderDate) — with the free-text Notes field moved into its own Notes(NoteID, CustomerID, Text,
Timestamp) table, since a customer could have many notes over time, and cramming them into a
single column had been silently truncating older notes whenever a new one was appended.

After the migration, each of the three anomalies became structurally impossible rather than merely
'usually avoided by careful engineering discipline': a product's price can be created, read, and
updated independently of any customer or order; deleting a customer for compliance no longer
touches product pricing history at all, since that data lives in an entirely separate table with no
dependency on CustomerID; and an address update is a single-row change in Customers,
immediately reflected everywhere else that joins against it. This is the practical payoff of
normalization — it's not an academic exercise, it removes entire categories of bug by making
inconsistent states impossible to represent in the schema in the first place, rather than merely
unlikely.

15. Appendix: Normalization and Real-World Schema Design

Tools
Modern schema design rarely happens on a whiteboard alone; it's worth connecting the theory above
to the tools and processes teams actually use, since the concepts translate directly.

Entity-Relationship (ER) Modeling

Before normalizing, most teams first sketch an ER diagram: entities (which become tables), attributes
(which become columns), and relationships (which become foreign keys, and sometimes junction
tables for many-to-many relationships). A well-drawn ER diagram often lands close to 3NF
naturally, because entities are typically chosen to represent single real-world concepts — which is
exactly the intuition normalization formalizes mathematically via functional dependencies.

Migrations and Schema Evolution

In practice, normalization isn't a one-time upfront exercise; schemas evolve as requirements change,
using migration scripts that alter tables incrementally in production. The startup case study above is
typical: teams often start with a wider, less-normalized table for speed of initial development, and
normalize specific parts of the schema once a specific anomaly is actually observed causing real
problems, rather than normalizing everything maximally from day one, since over-normalizing a
schema no one is stressing yet trades away simplicity for a benefit not yet needed.

ORMs and the Object-Relational Impedance Mismatch

Object-Relational Mapping tools (e.g., Django ORM, SQLAlchemy, ActiveRecord, Hibernate) let
application code work with normalized tables as if they were nested objects, automatically generating
the joins needed to reassemble a Customer object together with its related Orders and Notes. This
matters for normalization in practice because it removes much of the historical argument for
denormalizing 'to avoid writing joins by hand' — the ORM writes the joins, so the application code
stays simple even when the underlying schema is properly normalized.

16. Appendix: Quick Reference Summary

Normal form Requirement
1NF Atomic columns, unique rows, no repeating groups
2NF 1NF, plus no partial dependency on a composite key
3NF 2NF, plus no transitive dependency through a non-key attribute
BCNF 3NF, plus every determinant of a dependency is a superkey

17. Study Summary

Before an exam, it helps to be able to reconstruct the whole chain of reasoning from first principles
rather than memorizing the normal forms as isolated rules. Start from the anomalies: redundant data
causes update, insertion, and deletion problems, which is the entire motivation for normalizing in the
first place. Then recall that every normal form is defined in terms of functional dependencies — 1NF
is about atomicity and repeating groups, 2NF is about partial dependencies on a composite key, 3NF
is about transitive dependencies through a non-key attribute, and BCNF closes the remaining
loophole by requiring every determinant to be a superkey.

When decomposing a table by hand, the reliable process is: list every attribute, identify every
functional dependency you can find between them, identify the candidate key(s), and then check each
dependency against the current normal form's rule. Any violation tells you exactly how to split the
table — the violating dependency becomes its own table, keyed by its determinant, with the
dependent attribute moved out of the original table and replaced by a foreign key reference.
Repeating this process against increasingly strict normal forms, one violation at a time, is both the
theoretical definition and the practical algorithm for normalizing any schema.