Relational databases: choose a book and audit the result grain

Scope and responsibility

An introductory undergraduate route through relational design and query meaning, not production database administration. The executable example uses SQLite 3 and was checked with SQLite 3.51.0 in an isolated in-memory database. Its rows and room bookings are invented. The advanced authors’ draft is a later theory option, not a beginner SQL manual or a promise about a modern product version.

Choose your starting point

Compare the three books

Check your preparation

Six steps with a concrete output

These are your own study marks, not an assessment. They are not sent to this site.

1. Choose an entity grain

   I have produced this output

   Selected reading: Watt chapters 7–10; Database System Concepts chapter 2.

   Make this: Draw room, booking and tag relations and state what one row represents.

   Check your work: Room and booking IDs are not interchangeable counting units.

2. Declare dependencies

   I have produced this output

   Selected reading: Watt 11–12 and the systems text’s relational-design chapter.

   Make this: Write candidate keys, functional dependencies and a justified decomposition for a toy booking ledger.

   Check your work: Do not infer a dependency from only three convenient sample rows; state the domain rule that would enforce it.

3. Predict the join

   I have produced this output

   Selected reading: Watt 15–16; systems text 4.1.

   Make this: List every row of the example’s left join by hand before aggregation.

   Check your work: Include the unmatched room’s null-extended row, and preserve two distinct A bookings with equal seat counts.

4. Choose the denominator

   I have produced this output

   Selected reading: Systems text 3.6–3.7 and SQLite’s aggregate reference.

   Make this: Compute three counts and one sum for each room, explaining each output column.

   Check your work: Different count expressions answer different questions; a working query is not its interpretation.

5. Test multiplicity

   I have produced this output

   Selected reading: The follow-up packet and the formal companion’s query perspective.

   Make this: Join tag metadata, identify the changed grain and repair the plan without erasing valid duplicate values.

   Check your work: Check cardinality at every join. A DISTINCT keyword is not a general repair for a wrong relationship.

6. Write a reproducible query note

   I have produced this output

   Selected reading: Return to the book that resolves your weakest step.

   Make this: Keep schema assumptions, dialect, input rows, query, expected output and a counterexample together.

   Check your work: Another reader can reproduce NULL and duplicate behavior. Correctness on this snapshot does not prove a concurrency or performance claim.

Worked exercise

Original SQL example: no match is not an unknown value

The invented room registry is A, B, C. Bookings(id, room, seats) are (1, A,2),(2, A,2),(3, B, NULL); C has no booking. Left-join rooms to bookings and predict, per room, COUNT(*), COUNT(booking.id), COUNT(booking.seats) and SUM(booking.seats).

Assumptions

Booking IDs are unique and never NULL. NULL seats means an unknown count, not a cancelled booking or zero. The registry is complete for this exercise, no rows change during the query, and SQL retains the two equal known values. Only SQLite 3 aggregate and left-join semantics are used.

1. A joins to two distinct bookings, both with seats=2. Its four outputs are 2,2,2,4. SUM(DISTINCT seats) would be 2 and would wrongly erase a legitimate repeated value.
2. B joins to booking 3: outputs are 1,1,0, NULL. The booking exists, but there is no known seat value. SUM ignores NULL inputs and returns NULL when none are non-NULL.
3. C receives one null-extended join row: outputs are 1,0,0, NULL. COUNT(*) is one although matched bookings are zero. This is why the identifier count and seat-value count must remain separate.
4. If a report requires zero for a room with no booking, that is a stated business rule. Replacing every NULL sum by zero would also turn B’s unknown count into a false measurement.

Answer and interpretation

The rows are A:(2,2,2,4), B:(1,1,0, NULL), C:(1,0,0, NULL), in the requested column order. Inspect the meaning of every count before calling the result a total number of bookings or seats.

            WITH room(room_id) AS (VALUES ('A'), ('B'), ('C')),
booking(id, room_id, seats) AS (
  VALUES (1, 'A', 2), (2, 'A', 2), (3, 'B', NULL)
)
SELECT r.room_id, COUNT(*) AS joined_rows,
       COUNT(b.id) AS bookings, COUNT(b.seats) AS known_counts,
       SUM(b.seats) AS seats
FROM room AS r
LEFT JOIN booking AS b ON b.room_id = r.room_id
GROUP BY r.room_id
ORDER BY r.room_id;
          

Test the result

Execute the supplied single statement in an empty SQLite session. Then change B’s seats from NULL to 0: its known-value count becomes 1 and sum 0, while its booking count remains 1. The change proves why zero and unknown must not be conflated.

Change an assumption

Follow-up: adding descriptive rows multiplies measures

Keep the bookings and add tags (booking_id, label)=(1, red),(1, blue),(2, red). Enrich the booking query with a LEFT JOIN to tags before summing seats. What happens to A, and how can a report retain tag counts without changing booking measures?

1. Booking 1 now appears twice and booking 2 once. A has three joined booking rows and a naive seat sum of 2 + 2 + 2 = 6, despite only two bookings and a known seat sum of 4.
2. Collapse tags to one row per booking first, with tag counts 2 and 1, then left-join that relation to the unique bookings. Booking measures stay at their original grain: A bookings 2, seats 4, tag count 3.
3. SUM(DISTINCT seats) gives 2, still wrong. Aggregating bookings before another many-side join also fails if that join duplicates the aggregate again; the final relationship must preserve the measure’s grain.

Answer and interpretation

A correct plan keeps one row per booking when summing booking measures, or computes booking and tag measures separately before joining one-row-per-room summaries. The repair follows keys and cardinality, not a cosmetic DISTINCT.

Test the result

Write the intermediate rows and verify the repaired A measures 2/4/3. B’s unknown seats remain unknown; adding tag metadata supplies no new seat measurement.

Common mistakes

Before moving on

• I can explain each output column and its row grain.
• I can distinguish an unmatched record, a missing value and a measured zero.
• I can build a counterexample to an incorrect join or DISTINCT repair.

Sources and edition checks

Sources checked: 2026-11-04

• BCcampus: Database Design second-edition contents and access

  BCcampus confirms the 2014 second edition, chapters and authorized free access; the activities are this site’s original work.

• Authors: Database System Concepts, seventh-edition identity

  The authors confirm seventh edition,2019 and the selected ISBN; public companion material is not represented as the complete book.

• Authors: seventh-edition contents

  The authors’ contents verify the assigned query, design and transaction chapters.

• Authors: Foundations of Databases online draft

  The existing authors’ draft identity is retained; a failed fresh retrieval is not used to invent a new year, print ISBN or reuse licence.

• SQLite: built-in aggregate semantics

  COUNT and SUM/NULL semantics are checked against the engine’s official reference.

• SQLite: SELECT and join processing

  LEFT JOIN null-extension and processing are checked against the official SELECT reference; no production administration is implied.

These are proposed study activities, not official course assignments or a claim that every book has been read cover to cover.

How we curate →