3  Normalization

IntroLearning Objectives

After studying this chapter, you will be able to:

• explain what a functional dependency is and recognize one in a set of data;
• determine the key of a table, including when it consists of several columns;
• convert a document or worksheet into unnormalized form;
• bring a table structure step by step into first, second, and third normal form;
• test a design against third normal form, even if that design was made with an ERD.

In Chapter 2 you created a design by starting from a description of the real world. With normalization you work the other way around: you start from the data itself. That could be an Excel worksheet, a form, an invoice, or a report from an existing system. You break this data down step by step into a set of tables without repetition and without anomalies.

At each step you test the tables against a fixed rule, a normal form. We cover three: first, second, and third normal form, abbreviated as 1NF, 2NF, and 3NF. In practice, a design in third normal form is almost always good enough.

3.1 Functional dependency

Normalization revolves around one concept: the functional dependency.

Functional dependency

Attribute B is functionally dependent on attribute A if each value of A is associated with exactly one value of B. We write this as A → B and say: A determines B. A is called the determinant.

A few examples from the online store:

• CustomerID → Name. Customer number C01 is always associated with the same name, Johnson.
• ProductID → Description, Price. Product P10 always has the same description and price.
• OrderID → OrderDate, CustomerID. An order has one date and is placed by one customer.
• (OrderID, ProductID) → Quantity. The quantity belongs to the combination of an order and a product. The order number alone is not enough (an order contains several products), and neither is the product number alone (a product appears in several orders).

The reverse, Name → CustomerID, does not hold: several customers may be named Johnson.

ImportantDependencies follow from the business rules

You cannot reliably infer whether a functional dependency exists from a few rows of sample data. The fact that a table happens not to contain two customers with the same name does not mean that Name → CustomerID. Dependencies follow from the rules of the organization: what can occur in reality? If in doubt, check with the client.

3.1.1 Keys

Functional dependencies also let you define the key of a table precisely. A key is an attribute, or a combination of attributes, that determines all other attributes of the table, and from which you cannot omit any attribute without losing that property. A key that consists of more than one attribute is called a composite key.

3.2 Step 0: from document to unnormalized form

Normalization starts with a source. Take the order confirmation that the online store sends to customer Johnson.

Order confirmation

Order number: 1001
Date: March 2, 2026
Customer number: C01
Name: Johnson
City: Boston

ProductID	Description	Price	Quantity	Subtotal
P10	Coffee beans 1 kg	14.95	2	29.90
P12	Paper filters	3.49	1	3.49

Total: $33.39

From this document, you first draw up a list of all the attributes. While doing so, pay attention to three things.

1. Leave out derived data. Subtotal and Total can always be calculated from Price and Quantity. They don’t belong in the database.
2. Choose clear, unique names. Two attributes that are both called Number become OrderID and CustomerID.
3. Mark repeating groups. One order confirmation has several product lines. That part repeats within a single document; it is placed between braces.

NoteNotation for repeating groups

There are different conventions for showing a repeating group, such as braces {}, square brackets [], an asterisk, …

The most widely used notation is braces, and that is the notation used in this textbook.

Next, you determine the key of the whole. Each document has a unique order number, so OrderID is the key. The result is called the unnormalized form (UNF):

• Order(OrderID, OrderDate, CustomerID, Name, City, {ProductID, Description, Price, Quantity})

3.3 Step 1: first normal form

First normal form (1NF)

A table is in first normal form if each attribute contains a single value and there are no repeating groups.

You resolve a repeating group by splitting it off into a table of its own. The new table takes along the key of the original table; otherwise you wouldn’t know which order a line belongs to. Then you determine the key of the new table: which attribute determines which line it is within one order? That is ProductID. The key therefore becomes the combination (OrderID, ProductID).

• Order(OrderID, OrderDate, CustomerID, Name, City)
• OrderLine(OrderID, ProductID, Description, Price, Quantity)

TipThe flat table is also 1NF

The table in Table 1.1 in Chapter 1 is also in first normal form: each cell contains a single value and there are no repeating groups. The key of that table is (OrderID, ProductID). Yet you saw all the anomalies there. First normal form on its own is therefore not enough; that is what the next steps are for.

3.4 Step 2: second normal form

Second normal form (2NF)

A table is in second normal form if it is in first normal form and every attribute that is not part of the key depends on the whole key and not on just part of it.

A dependency on part of the key is called a partial dependency. It can only occur with a composite key. A table in 1NF with a single-attribute key is therefore automatically in 2NF as well.

Check the tables from step 1:

• Order has a single-attribute key and is therefore in 2NF.
• OrderLine has the composite key (OrderID, ProductID). Look at each remaining attribute:
  • Quantity depends on the whole key: (OrderID, ProductID) → Quantity. Fine.
  • Description and Price depend only on ProductID: ProductID → Description, Price. That is a partial dependency.

You resolve a partial dependency by splitting off the part of the key, together with the attributes that depend on it, into a new table. The key part also stays in the original table, now as a foreign key.

• Order(OrderID, OrderDate, CustomerID, Name, City)
• OrderLine(OrderID, ProductID, Quantity)
• Product(ProductID, Description, Price)

Above, we assumed that the price depends only on the product. That holds as long as prices never change. If the store does adjust its prices from time to time, then the price on the order confirmation is the price at the time of ordering. That price depends on the combination of order and product and stays in OrderLine as SalePrice. The current price in Product then comes from another source, such as the price list. Here you see once again that functional dependencies follow from the business rules. You encountered the same choice in Chapter 2.

3.5 Step 3: third normal form

Third normal form (3NF)

A table is in third normal form if it is in second normal form and no attribute that is not part of the key depends on another attribute that is also not part of the key.

A dependency through an intermediate attribute is called a transitive dependency. In the table Order:

• OrderID → CustomerID, and
• CustomerID → Name, City.

So Name and City do not depend directly on the key OrderID, but on it through CustomerID. That is exactly the cause of the update anomaly from Chapter 1: a customer’s city is repeated with every order.

You resolve a transitive dependency by splitting off the determinant (CustomerID), together with the attributes that depend on it, into a new table. The determinant stays in the original table as a foreign key.

• Customer(CustomerID, Name, City)
• Order(OrderID, OrderDate, CustomerID)
• OrderLine(OrderID, ProductID, Quantity)
• Product(ProductID, Description, Price)

Check the other tables too. In OrderLine and Product, there is no attribute outside the key that determines another attribute. All four tables are now in third normal form.

TipMemory aid

In a table in third normal form, every attribute depends on the key (1NF), the whole key (2NF), and nothing but the key (3NF).

3.6 The result

The normalized design is the same as the design you created with an ERD in Chapter 2. So you can also show it as an ERD (Figure 3.1). You can read off the relationships from the foreign keys: a foreign key is always on the many side of a relationship.

erDiagram
    Customer ||--o{ Order : places
    Order ||--|{ OrderLine : contains
    Product ||--o{ OrderLine : "appears in"
    Customer {
        string CustomerID PK
        string Name
        string City
    }
    Order {
        int OrderID PK
        date OrderDate
        string CustomerID FK
    }
    OrderLine {
        int OrderID PK, FK
        string ProductID PK, FK
        int Quantity
    }
    Product {
        string ProductID PK
        string Description
        decimal Price
    }

Figure 3.1: The result of normalization, shown as an ERD

Finally, check whether the anomalies from Chapter 1 have disappeared:

• A customer’s city is stored in one place. A move is a single change. No update anomaly.
• You can add a new product to Product, even if nobody has ordered it. No insert anomaly.
• If you delete an order, the customer’s data remains in Customer. No delete anomaly.

NoteHigher normal forms

Beyond the first three normal forms, there are stricter forms, such as Boyce-Codd normal form (BCNF) and the fourth and fifth normal forms. They solve problems that only occur in special situations, for example in tables with several overlapping composite keys. For most practical designs, third normal form is sufficient, and that is also the level required in this book.

3.7 The step-by-step plan in summary

0. Unnormalized form: list all attributes from the source, leave out derived data, mark repeating groups, and determine the key.
1. First normal form: split each repeating group off into its own table, take along the key of the original table, and determine the key of the new table.
2. Second normal form: for each composite key, look for attributes that depend on part of the key, and split them off together with that key part.
3. Third normal form: look for attributes that depend on another attribute outside the key, and split them off together with their determinant.
4. Merge and check: merge tables that have the same key. This may be necessary if you have normalized several documents, such as an order confirmation and a customer list. Then check whether the anomalies have disappeared.

TipNormalization as a check on an ERD

Even if you created a design with an ERD, normalization is useful. Convert the ERD into tables and go through each table: does every attribute depend on the key, the whole key, and nothing but the key? This way you find mistakes that don’t stand out in the drawing, such as an attribute placed with the wrong entity.

WarningCommon mistakes

3.8 Common mistakes

• Leaving in derived data, such as subtotals and totals.
• Forgetting to take along the key of the original table when splitting off a repeating group.
• After splitting off in step 2 or 3, also removing the determinant from the original table. It must stay there as a foreign key; otherwise the relationship is lost.
• Inferring functional dependencies from coincidental sample data instead of from the business rules.
• Thinking that a table with a single-attribute key can still have second normal form problems. Such a table is automatically in 2NF; go straight to third normal form.

3.9 Exercises

Exercise 1: functional dependencies

A library keeps track of loans in the following table. A member can borrow the same book on different days.

Table 3.1: Loans of a library

MemberID	MemberName	ISBN	Title	LoanDate	ReturnDate
12	Baker	9781234567897	Databases in Practice	2026-01-10	2026-01-31
12	Baker	9781987654329	Excel for Economists	2026-01-10	2026-01-24
15	Smith	9781234567897	Databases in Practice	2026-02-03	2026-02-20

1. List all functional dependencies.
2. What is the key of this table?

1. MemberID → MemberName
   ISBN → Title
   (MemberID, ISBN, LoanDate) → ReturnDate
2. The key is (MemberID, ISBN, LoanDate). MemberID and ISBN together are not enough, because a member can borrow the same book more than once.

Normalizing gives: Member(MemberID, MemberName), Book(ISBN, Title), and Loan(MemberID, ISBN, LoanDate, ReturnDate).

Exercise 2: which normal form?

For each table, determine which normal form it is in (UNF, 1NF, 2NF, or 3NF). Explain your answer.

1. Employee(EmployeeID, Name, DepartmentID, DepartmentName)
2. Enrollment(StudentID, CourseCode, EnrollmentDate, CourseName)
3. Product(ProductID, Description, Price)
4. Customer(CustomerID, Name, PhoneNumbers), where PhoneNumbers contains values such as “617-555-0142, 617-555-0199”

1. 2NF. The key consists of one attribute, so the table is automatically in 2NF. There is a transitive dependency: DepartmentID → DepartmentName.
   Solution: split off Department(DepartmentID, DepartmentName).

2. 1NF. CourseName depends only on CourseCode, which is part of the key.
   Solution: split off Course(CourseCode, CourseName).

3. 3NF. Description and Price depend directly and only on ProductID.

4. UNF. PhoneNumbers contains multiple values in one attribute.
   Solution: a separate table Phone(CustomerID, PhoneNumber).

Exercise 3: grade report

A degree program keeps track of student results in a worksheet (Table 3.2). Each course has one instructor as coordinator. Normalize this worksheet to third normal form and draw the result as an ERD.

Table 3.2: Student results

StudentID	Name	Program	CourseCode	CourseName	Credits	InstructorCode	InstructorName	Grade
2401	Yilmaz	BUS	STA1	Statistics 1	3	MRV	Rivera	B+
2401	Yilmaz	BUS	DBS	Databases	4	KBR	Brooks	A-
2402	Hendricks	ACC	STA1	Statistics 1	3	MRV	Rivera	C
2403	Miller	BUS	DBS	Databases	4	KBR	Brooks	C-

1NF. The worksheet has no repeating groups and each cell contains a single value. The key is (StudentID, CourseCode).

• Result(StudentID, CourseCode, Name, Program, CourseName, Credits, InstructorCode, InstructorName, Grade)

2NF. StudentID → Name, Program and CourseCode → CourseName, Credits, InstructorCode, InstructorName are partial dependencies.

• Student(StudentID, Name, Program)
• Course(CourseCode, CourseName, Credits, InstructorCode, InstructorName)
• Result(StudentID, CourseCode, Grade)

3NF. In Course, InstructorCode → InstructorName holds: a transitive dependency.

• Student(StudentID, Name, Program)
• Instructor(InstructorCode, InstructorName)
• Course(CourseCode, CourseName, Credits, InstructorCode)
• Result(StudentID, CourseCode, Grade)

erDiagram
    Student ||--o{ Result : earns
    Course ||--o{ Result : "is earned in"
    Instructor ||--o{ Course : coordinates
    Student {
        int StudentID PK
        string Name
        string Program
    }
    Course {
        string CourseCode PK
        string CourseName
        int Credits
        string InstructorCode FK
    }
    Instructor {
        string InstructorCode PK
        string InstructorName
    }
    Result {
        int StudentID PK, FK
        string CourseCode PK, FK
        string Grade
    }

Note: if a student is allowed to retake a course several times, (StudentID, CourseCode) is no longer a valid key. In that case, an exam date, for example, must be added to the key.

Exercise 4: bike rental

The bike rental company from exercise 3 in Chapter 2 gives the customer a rental slip with every rental.

Rental slip

Rental number: 501
Period: June 10, 2026 through June 12, 2026
Customer number: 17
Name: Peters
Phone: 617-555-0123

BikeID	Type	DailyRate
3	E-bike	25.00
8	City bike	12.50

Total per day: $37.50

1. Normalize the rental slip to third normal form. Assume that the daily rate can differ per bike.
2. Compare your result with the solution of the ERD exercise. Are the designs the same?
3. Suppose all bikes of the same type have the same daily rate. What changes in your design?

a. UNF (Total per day is derived and is dropped):

• Rental(RentalID, StartDate, EndDate, CustomerID, Name, Phone, {BikeID, Type, DailyRate})

1NF:

• Rental(RentalID, StartDate, EndDate, CustomerID, Name, Phone)
• RentalLine(RentalID, BikeID, Type, DailyRate)

2NF: BikeID → Type, DailyRate is a partial dependency.

• Rental(RentalID, StartDate, EndDate, CustomerID, Name, Phone)
• RentalLine(RentalID, BikeID)
• Bike(BikeID, Type, DailyRate)

3NF: CustomerID → Name, Phone is a transitive dependency.

• Customer(CustomerID, Name, Phone)
• Rental(RentalID, StartDate, EndDate, CustomerID)
• RentalLine(RentalID, BikeID)
• Bike(BikeID, Type, DailyRate)

2. The design is the same as that of the ERD exercise, except for one point. There, the daily rate was also stored in RentalLine, to record the rate at the time of rental. That is a choice that depends on the business rules, not on the method.

3. Then Type → DailyRate holds. In the table Bike, that is a transitive dependency, and it must be split off: BikeType(Type, DailyRate) and Bike(BikeID, Type).