---
title: "Normalize sale relation up to 4NF"
subject: "Database Management System"
chapter: 4
key: "semester_6/database_management_system:normalization-sale-example"
source: https://www.bctengineer.com/q/semester_6/database_management_system/normalization-sale-example
site: BCT Engineering
---

# Normalize sale relation up to 4NF

Database Management System · Chapter 4: Database Constraints & Normalization

- Asked in: year not given
- Video: [1NF 2NF 3NF DBMS](https://www.youtube.com/watch?v=xPzgK6sOCfg) — Saghir School
- Answer checked by Claude Opus 5.5 (high effort) on 2026-10-14
- Page: https://www.bctengineer.com/q/semester_6/database_management_system/normalization-sale-example
- Whole chapter: https://www.bctengineer.com/md/semester_6/database_management_system/chapter-4

## Question

Normalize the following relation up to 4NF (show 1NF, 2NF, 3NF and 4NF).
UNF: sale (cust_name, item, ship-add, newsletter, supplier, supplier-phone, price)
| Cust_name | Item | Ship-add | Newsletter | supp | supp-phone | price |
|---|---|---|---|---|---|---|
| A | a | bkt | a1 | M | AX | 100 |
| B | b | ktm | b1 | S | BX | 200 |
| C | a, b | bkt | a1, b1 | M, S | AX, BX | 300 |
| D | c | dhr | c1 | S | CX | 800 |

## Short answer

1NF: remove repeating groups. 2NF: split partial dependencies → CUSTOMER, ITEM, SALE. 3NF: remove transitive dependencies → separate SUPLIER. Final 4NF schema: CUSTOMER(cust_name, ship_add), SALE(cust_name, item), ITEM(item, newsletter, supp, price), SUPPLIER(supp, supp_phone).

## Answer

#### Assumptions (read from the sample data)
- A customer has one shipping address (cust_name → ship_add).
- Newsletter, supplier and price depend on the item (item → newsletter, supplier, price). Customer C bought items a and b; her price 300 is the sum 100 + 200 of the item prices, so price is taken as the item price.
- Each supplier has one phone (supp → supp_phone).

#### Unnormalized form (UNF)
Customer C has repeating groups (two items, two newsletters, two suppliers, two phones in one row).

#### 1NF: remove repeating groups (one value per cell)
Each item bought becomes a separate row. Primary key: (cust_name, item).

| cust_name | item | ship_add | newsletter | supp | supp_phone | price |
|---|---|---|---|---|---|---|
| A | a | bkt | a1 | M | AX | 100 |
| B | b | ktm | b1 | S | BX | 200 |
| C | a | bkt | a1 | M | AX | 100 |
| C | b | bkt | b1 | S | BX | 200 |
| D | c | dhr | c1 | S | CX | 800 |

FDs: cust_name → ship_add; item → newsletter, supp, price; supp → supp_phone.

(Data problem: S has phone BX in row B and CX in row D. We treat CX as an entry error or a different supplier; in a real design supp → supp_phone must hold.)

#### 2NF: remove partial dependencies
ship_add depends only on cust_name, and newsletter, supp, supp_phone, price depend only on item. Both are parts of the composite key, so split:

- CUSTOMER(**cust_name**, ship_add)
- ITEM(**item**, newsletter, supp, supp_phone, price)
- SALE(**cust_name**, **item**)  (both foreign keys)

#### 3NF: remove transitive dependencies
In ITEM, item → supp and supp → supp_phone, so supp_phone depends on the key through supp. Split:

- CUSTOMER(**cust_name**, ship_add)
- ITEM(**item**, newsletter, supp, price)  (supp is a foreign key)
- SUPPLIER(**supp**, supp_phone)
- SALE(**cust_name**, **item**)

#### BCNF check
In every relation the left side of each FD is a key, so the schema is also in BCNF.

#### 4NF: remove multivalued dependencies
4NF requires that every non-trivial MVD X →→ Y has X as a superkey. In the original UNF, a customer's items, and an item's newsletters and suppliers, were the multi-valued sets. After the split:

- SALE(cust_name, item) is all key; the only MVD is trivial.
- ITEM: newsletter and supp are single-valued per item (functional, not multi-valued), so there is no non-trivial MVD.

So the four relations are already in **4NF**:

```text
CUSTOMER(cust_name, ship_add)
SALE(cust_name, item)
ITEM(item, newsletter, supp, price)
SUPPLIER(supp, supp_phone)
```

If a business rule allowed an item to have several independent newsletters and several independent suppliers (item →→ newsletter | supp), ITEM would be split further into ITEM_NEWSLETTER(item, newsletter) and ITEM_SUPPLIER(item, supp, price) to reach 4NF.

## Check yourself

The answer's key points as multiple-choice questions, with the correct option and why.

1. What is done to reach 1NF for customer C who bought items a and b?
   A. Delete the row
   B. Split the repeating groups into separate rows, one value per cell, with key (cust_name, item)
   C. Add a new column for each item
   D. Remove the price column
   Answer: B. 1NF needs atomic values, so each item bought becomes its own row. (Key point: 1NF)

2. Why is the 1NF table split for 2NF?
   A. ship_add depends only on cust_name, and newsletter, supp, price depend only on item, which are parts of the composite key
   B. supp_phone depends on supp
   C. price is not numeric
   D. The table has too few rows
   Answer: A. Attributes depending on only part of the composite key are partial dependencies and move to CUSTOMER and ITEM. (Key point: 2NF partial dependency)

3. What transitive dependency is removed for 3NF?
   A. cust_name -> ship_add
   B. item -> supp and supp -> supp_phone, so supp_phone moves to SUPPLIER
   C. item -> price
   D. cust_name -> item
   Answer: B. supp_phone depends on the key item only through supp, so SUPPLIER(supp, supp_phone) is created. (Key point: 3NF transitive dependency)

4. Why are the resulting relations already in 4NF?
   A. Newsletter and supplier are single-valued per item, so there is no non-trivial MVD
   B. Because the tables have few rows
   C. Because they are in 1NF
   D. Because SALE has two foreign keys
   Answer: A. 4NF is violated only by non-trivial MVDs whose left side is not a superkey, and none exist after the split. (Key point: 4NF)

## Related in Database Management System

- [SQL: increase balances by 100% and 50%](https://www.bctengineer.com/question.md?key=semester_6%2Fdatabase_management_system%3Asql-update-balance-rates) — Database Management System, chapter 3
- [RA: computer dept employees and supervisors](https://www.bctengineer.com/question.md?key=semester_6%2Fdatabase_management_system%3Ara-computer-emp-supervisor) — Database Management System, chapter 3
- [RA: delete Electrical dept projects](https://www.bctengineer.com/question.md?key=semester_6%2Fdatabase_management_system%3Ara-delete-electrical-projects) — Database Management System, chapter 3
- [RA: projects count per department](https://www.bctengineer.com/question.md?key=semester_6%2Fdatabase_management_system%3Ara-projects-per-department) — Database Management System, chapter 3
- [RA: employees on Network over 15 hours](https://www.bctengineer.com/question.md?key=semester_6%2Fdatabase_management_system%3Ara-network-project-15-hours) — Database Management System, chapter 3

## Related in other subjects

- [RECORD.txt item cost with VAT](https://www.bctengineer.com/question.md?key=semester_1%2Fcomputer_programming%3Afile-record-txt-invoice) — Computer Programming, chapter 10
- [Built-up beam, unsupported 4 m, 40 kN/m + 100 kN](https://www.bctengineer.com/question.md?key=semester_6%2Fdesign_of_steel_and_timber_structure%3Abuiltup-beam-unsupported-4m-40knm) — Design of Steel and Timber Structure, chapter 7
- [Extreme value of x²+y²+z² with ax+by+cz=p](https://www.bctengineer.com/question.md?key=semester_2%2Fmathematics%3Aextreme-value-x2-y2-z2) — Mathematics, chapter 1, asked 5×
- [Pressure to drive oil up inclined pipe](https://www.bctengineer.com/question.md?key=semester_4%2Fhydraulics%3Aviscous-fluid-upslope-pressure) — Hydraulics, chapter 1