Normalization (1NF–3NF) and denormalization – IB Computer Science exam questions with mark scheme

1Stretch6 marks

An unnormalized table stores orders:

ORDERS(OrderID, OrderDate, CustomerID, CustomerName, CustomerCity, {ProductID, ProductName, UnitPrice, Quantity})

The items in braces repeat for each product on the order. Construct a database in third normal form (3NF) from this table. In your answer, identify the problem removed at each stage (1NF, 2NF, 3NF) and give the final relations with primary keys marked (PK) and foreign keys marked (FK). [6]

1NF: the repeating group (product details) must be removed so every attribute is atomic(1)
1NF relation has a composite key (OrderID, ProductID) (allow 1NF done by moving the repeating group to a separate table, e.g. OrderItem(OrderID, ProductID, ProductName, UnitPrice, Quantity), keyed on (OrderID, ProductID))(1)
2NF: ProductName and UnitPrice depend only on ProductID, and OrderDate, CustomerID, CustomerName, CustomerCity only on OrderID: partial (partial-key) dependencies on the composite key (if the repeating group was split off at 1NF, identifying the ProductID partial dependency alone is enough)(1)
Partial dependencies removed to separate tables (Product, Orders, OrderLine)(1)
3NF: CustomerName and CustomerCity depend on CustomerID, not directly on OrderID (transitive dependency), so move to Customer(1)
Final: Customer(CustomerID PK, CustomerName, CustomerCity); Product(ProductID PK, ProductName, UnitPrice); Orders(OrderID PK, OrderDate, CustomerID FK); OrderLine(OrderID PK/FK, ProductID PK/FK, Quantity). Allow different table names; allow other key notation (e.g. underline, asterisk) if clear; allow a price-at-sale field in OrderLine if justified(1)
Answer: Customer, Product, Orders, OrderLine (see mark scheme)
Examiner tip: Name the type of dependency (partial or transitive) as well as the attribute involved.

2Core4 marks

A sports club stores its members in this table:

MemberID	Name	Sports
M01	Ava Kerr	Tennis, Swimming
M02	Leo Ruiz	Rowing
M03	Ana Bose	Swimming, Rowing, Tennis

(a) State what is meant by an atomic value in a relational database. [1]

(b) Explain why this table is not in first normal form (1NF). [1]

(c) The table is rewritten as a single table in 1NF with exactly one sport in each row. State the primary key of the new table and the number of rows it will contain. [2]

(a) A value that cannot be divided further into meaningful parts / one single value per field (cell) (accept "indivisible")(1)
(b) The Sports field holds more than one value in a cell (e.g. "Tennis, Swimming" for M01), so values are not atomic / it is a repeating group (must refer to Sports or the data; "data is repeated" alone = 0)(1)
(c) Composite (concatenated) key of MemberID and Sport (accept MemberID + Sports; MemberID alone = 0, because it is no longer unique)(1)
(c) 6 rows(1)
Answer: (a) a single, indivisible value (b) Sports holds several values in one cell (c) PK (MemberID, Sport); 6 rows
Examiner tip: MemberID alone is not unique once each sport has its own row, so the key must include the sport as well.

3Standard4 marks

An exam board stores entries in the relation:

EXAM_ENTRY(CandidateNo, PaperCode, CandidateName, PaperTitle, Mark)

The primary key is the composite key (CandidateNo, PaperCode). All values are atomic.

(a) Identify one partial-key dependency in EXAM_ENTRY. [1]

(b) Explain why EXAM_ENTRY is in first normal form (1NF) but not in second normal form (2NF). [2]

(c) Another relation, CANDIDATE(CandidateNo, CandidateName, DateOfBirth), is in 1NF. Explain why it must also be in 2NF. [1]

(a) CandidateNo → CandidateName OR PaperCode → PaperTitle (either; must show the determinant is only part of the key; ignore notation used for the arrow)(1)
(b) It is in 1NF because every attribute is atomic / there are no repeating groups and it has a primary key(1)
(b) Not in 2NF because a non-key attribute (CandidateName / PaperTitle) depends on only part of the composite key, not the whole key (Mark depends on the whole key, so that alone does not break 2NF)(1)
(c) Its primary key is a single attribute, so no non-key attribute can depend on only part of the key / partial dependencies are impossible (accept "it has no composite key")(1)
Answer: (a) CandidateNo → CandidateName (or PaperCode → PaperTitle) (b) atomic, but non-key attributes depend on part of the composite key (c) single-attribute key, so no partial dependency is possible
Examiner tip: For 2NF always ask: does any non-key field depend on just one part of a composite key? Mark depends on both parts, CandidateName on one.

Stuck on Normalization (1NF–3NF) and denormalization?

Get it sorted in a free 30-minute session

We work through your weakest questions on this topic on Zoom. I reply the same day.

Free 30-min session on this topic Send me 5 questions a day

4Standard4 marks

The diagram shows the functional dependencies in the relation STAFF(StaffID, StaffName, DeptCode, DeptName, DeptFloor). An arrow from A to B means "A determines B". StaffID is the primary key.

(a) Identify the transitive dependency shown in the diagram and explain why it means STAFF is not in third normal form (3NF). [2]

(b) Construct a set of relations in 3NF that hold the same data. Show the primary key and any foreign key of each relation. [2]

(a) StaffID → DeptCode → DeptName, DeptFloor / DeptName and DeptFloor depend on StaffID only through DeptCode (accept DeptCode → DeptName, DeptFloor identified as the transitive dependency)(1)
(a) 3NF requires every non-key attribute to depend only on the primary key; here non-key attributes (DeptName, DeptFloor) depend on another non-key attribute (DeptCode)(1)
(b) DEPARTMENT(DeptCode PK, DeptName, DeptFloor) (allow any sensible table name)(1)
(b) STAFF(StaffID PK, StaffName, DeptCode FK) — DeptCode must be kept in STAFF and identified as the foreign key (removing DeptCode from STAFF = 0 for this mark)(1)
Answer: Transitive: StaffID → DeptCode → DeptName, DeptFloor. 3NF: STAFF(StaffID PK, StaffName, DeptCode FK); DEPARTMENT(DeptCode PK, DeptName, DeptFloor)
Examiner tip: Students often move DeptCode out of STAFF entirely; it must stay behind as the foreign key or the link is lost.

5Core4 marks

A music school records every lesson in one table, LESSON. The primary key is LessonID.

LessonID	LessonDate	StudentID	StudentName	TeacherID	TeacherName	TeacherExt
L101	03 Mar	S14	Priya	T2	Mr Okafor	ext 214
L102	03 Mar	S09	Tom	T3	Ms Lund	ext 220
L103	04 Mar	S22	Ellis	T2	Mr Okafor	ext 214
L104	05 Mar	S14	Priya	T2	Mr Okafor	ext 214
L105	05 Mar	S31	Zara	T5	Mrs Grey	ext 231
L106	06 Mar	S09	Tom	T3	Ms Lund	ext 220
L107	07 Mar	S22	Ellis	T2	Mr Okafor	ext 214

(a) Teacher T2 moves office and gets a new phone extension. State how many rows must be changed, and explain the problem this causes. [2]

(b) The school hires a new teacher, T8, who has not yet been given any lessons. Describe the problem in adding T8 to this table. [1]

(a) 4 rows(1)
(a) Update anomaly: the extension is stored redundantly, so if any one of the rows is missed the table holds two different extensions for T2 / the data becomes inconsistent(1)
(b) Insertion anomaly: a row needs a LessonID (primary key cannot be null), so T8 cannot be stored until they have a lesson / a dummy lesson would have to be invented(1)
(c) Deletion anomaly: L105 is the only row for teacher T5, so Mrs Grey and her extension (ext 231) are lost from the database (must refer to T5 / Mrs Grey)(1)
Answer: (a) 4 rows; risk of inconsistent extensions (update anomaly) (b) no lesson, no row, because LessonID is required (c) all of T5’s details are lost
Examiner tip: Anomaly answers must use the data given: name the rows, the teacher and what is lost, not just the anomaly name.

6Hard6 marks

A veterinary practice stores vaccination records in a single table:

VACCINATION(PetID, VaccineCode, DateGiven, PetName, OwnerID, OwnerName, OwnerPhone, VaccineName, Manufacturer, BatchNo)

The primary key is (PetID, VaccineCode, DateGiven). All values are atomic. The following rules apply:

each pet has exactly one owner, but an owner can have several pets;
each vaccine code identifies one vaccine name and one manufacturer;
the batch number is recorded for each individual injection.

Normalize this table to third normal form (3NF). Explain each dependency you remove, and give the final relations with primary keys (PK) and foreign keys (FK) marked. [6]

Partial dependency: PetName and OwnerID (and so OwnerName, OwnerPhone) depend only on PetID, which is part of the key(1)
Partial dependency: VaccineName and Manufacturer depend only on VaccineCode, which is part of the key(1)
Removing the partial dependencies gives PET, VACCINE and a link table holding the full key + BatchNo (2NF)(1)
Transitive dependency: OwnerName and OwnerPhone depend on OwnerID, a non-key attribute of PET (PetID → OwnerID → OwnerName), so they are moved to an OWNER table(1)
OWNER(OwnerID PK, OwnerName, OwnerPhone); PET(PetID PK, PetName, OwnerID FK); VACCINE(VaccineCode PK, VaccineName, Manufacturer)(1)
VACCINATION(PetID PK/FK, VaccineCode PK/FK, DateGiven PK, BatchNo) — BatchNo must stay here as it depends on the whole key (allow different table names and key notation if clear; do not award if DateGiven is dropped from the key)(1)
Answer: OWNER(OwnerID, OwnerName, OwnerPhone); PET(PetID, PetName, OwnerID FK); VACCINE(VaccineCode, VaccineName, Manufacturer); VACCINATION(PetID FK, VaccineCode FK, DateGiven, BatchNo) with composite PK (PetID, VaccineCode, DateGiven)
Examiner tip: BatchNo belongs to one injection, so it depends on the whole three-part key; moving it into VACCINE loses marks.

7Hard5 marks

A news website stores its data in 3NF:

ARTICLE(ArticleID, Title, Body, PublishDate, AuthorID, SectionID)
AUTHOR(AuthorID, AuthorName, Biography)
SECTION(SectionID, SectionName)

Every time an article page is viewed, the server joins all three tables to display the title, author name and section name. The site has about 2 million page views a day but publishes only about 40 new articles a day, and authors rarely change their names.

The developer proposes denormalizing the database by also storing AuthorName and SectionName in every ARTICLE row.

Evaluate this proposal. [5]

Advantage: each page view can be answered from ARTICLE alone with no joins, so reads/queries are faster / use less server processing(1)
The workload is read-intensive (about 2 million reads to 40 writes a day, a ratio of 50 000 : 1), so faster reads benefit almost every request(1)
Disadvantage: AuthorName and SectionName are now stored redundantly in many rows, using more storage(1)
Disadvantage: if an author/section name changes, every one of their article rows must be updated; a missed row gives inconsistent data (update anomaly / loss of integrity), so extra application code or triggers are needed(1)
Justified conclusion: e.g. denormalization is worthwhile here because names rarely change and reads vastly outnumber writes, provided updates to names are handled carefully (accept a reasoned opposite conclusion, e.g. caching instead)(1)
Answer: Likely justified: big read-speed gain for a 50 000 : 1 read/write workload; cost is redundancy and the risk of inconsistent names, which is small because names rarely change.
Examiner tip: “Evaluate” needs both sides and a conclusion tied to the figures given; listing only benefits caps the answer.

8Standard5 marks

A library’s LOAN table is not normalized. Each of its 24 000 loan records stores the borrower’s name (32 bytes) and address (64 bytes). The loans were made by 1 500 different borrowers.

The table is normalized: each LOAN record now stores only a BorrowerID (3 bytes), and a new BORROWER table stores one record per borrower containing BorrowerID (3 bytes), name (32 bytes) and address (64 bytes).

(a) Calculate the storage, in bytes, used for borrower data in the LOAN table before normalization. [1]

(b) Calculate the total storage, in bytes, used for borrower data after normalization. [2]

(d) Apart from saving storage, state one benefit of the normalized design. [1]

(a) 24 000 × (32 + 64) = 2 304 000 bytes(1)
(b) Method: 24 000 × 3 (= 72 000) + 1 500 × 99 (= 148 500) (allow 1 500 × (3 + 32 + 64))(1)
(b) 220 500 bytes (accept 220.5 kB)(1)
(c) (2 304 000 − 220 500) ÷ 2 304 000 × 100 = 90.4% (allow follow-through from their (a) and (b))(1)
(d) A borrower’s address is stored once, so a change of address is made in one place / no inconsistent copies (update anomalies avoided) / a borrower can be stored before they borrow anything (accept any valid integrity benefit; ignore “less redundancy” repeated from the storage point unless consistency is mentioned)(1)
Answer: (a) 2 304 000 bytes (b) 220 500 bytes (c) 90.4% (d) e.g. address updated in one place only, so no inconsistent copies
Examiner tip: Remember the BORROWER table needs its own BorrowerID too: 99 bytes per borrower, not 96.

9Stretch5 marks

A college timetable is stored in the table below. The designer states that the primary key is (ModuleCode, GroupNo).

ModuleCode	ModuleTitle	GroupNo	Tutor	Room
CS210	Databases	1	Patel	B12
CS210	Databases	2	Novak	B14
CS220	Networks	1	Patel	B12
CS230	Algorithms	1	Hale	C03
CS230	Algorithms	2	Hale	C03

(a) Use the data to show that the functional dependency ModuleCode → Tutor does not hold. [1]

(b) Identify a partial-key dependency that is consistent with the data. [1]

(c) A student notices that each tutor always appears with the same room and concludes that Tutor → Room is a functional dependency. Explain why the data alone cannot prove this. [2]

(d) The college confirms that every tutor teaches in one fixed room. State the highest normal form the table is in, and justify your answer. [1]

(a) CS210 appears with two different tutors (Patel for group 1, Novak for group 2), so one ModuleCode does not determine a single Tutor(1)
(b) ModuleCode → ModuleTitle (ModuleTitle depends on only part of the key) (do not accept anything that the data contradicts, e.g. ModuleCode → Room)(1)
(c) Sample data only shows the rows stored now; it can disprove a dependency but not prove one / a future valid row (e.g. Patel in another room) could break it(1)
(c) A functional dependency is a rule about all possible valid data, so it must come from the business rules / be confirmed with the client (the college)(1)
(d) 1NF only: values are atomic, but ModuleTitle depends on only part of the key (partial dependency), so it is not in 2NF (and Tutor → Room is also a transitive dependency) (no mark for "1NF" without justification)(1)
Answer: (a) CS210 has Patel and Novak (b) ModuleCode → ModuleTitle (c) data can only refute an FD; FDs come from business rules for all possible data (d) 1NF, because of the partial dependency ModuleCode → ModuleTitle
Examiner tip: One counter-example disproves a dependency, but no amount of matching rows proves one: dependencies come from the rules of the business.

10Hard5 marks

A student designs a cinema booking database and claims it is in third normal form (3NF):

FILM(FilmID, Title, Certificate, DurationMins)
SCREEN(ScreenNo, Capacity)
SCREENING(ScreeningID, FilmID, ScreenNo, StartTime, Title)
BOOKING(BookingID, ScreeningID, SeatRef, CustomerEmail, CustomerName)

Each customer is identified by a unique email address.

(a) Identify two relations that are not in 3NF. For each, explain the dependency that breaks 3NF and state how the design should be corrected. [4]

(b) Suggest why a developer might deliberately keep Title in SCREENING. [1]

(a) SCREENING: Title depends on FilmID, a non-key attribute (ScreeningID → FilmID → Title): transitive dependency(1)
(a) Remove Title from SCREENING; it is already in FILM and can be found via the FilmID foreign key(1)
(a) BOOKING: CustomerName depends on CustomerEmail, a non-key attribute (BookingID → CustomerEmail → CustomerName): transitive dependency(1)
(a) Create CUSTOMER(CustomerEmail PK, CustomerName) and keep CustomerEmail in BOOKING as a foreign key (removing CustomerEmail from BOOKING = 0)(1)
(b) Denormalization for performance: the listings page reads screenings with film titles very often, so storing Title avoids a join and makes this frequent read faster (must link to reading speed/fewer joins)(1)
Answer: (a) SCREENING: FilmID → Title, remove Title; BOOKING: CustomerEmail → CustomerName, move to CUSTOMER(CustomerEmail PK, CustomerName) with CustomerEmail as FK (b) deliberate denormalization to avoid a join on a frequently read listing
Examiner tip: For each fault, name the non-key attribute that is doing the determining; “data is duplicated” alone does not identify a transitive dependency.

Stuck on this topic?

Book a free 30-minute session on WhatsApp and we'll find exactly which step is costing you the marks. Or build a timed paper on it.

Free 30-minute session