# Relational databases: choose a book and audit the result grain — Study worksheet

[Study guides](https://unireadinglist.com/guides/relational-databases/)

[Course topic: Database Systems](https://unireadinglist.com/subjects/computer-science/#course-databases)

Sources checked: 2026-11-04

Study route and exercise: University Reading List editorial project.

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

- [ ] I need a free first design text
  Use Watt and Eng for entities, relationships, keys and normalization, then connect its SQL introduction to a small checked dataset. Keep the chosen database dialect visible.

- [ ] I need a course-wide systems reference
  Use Database System Concepts 7e when the course also needs storage, transactions and processing. Select the relational-language and design chapters first, rather than reading advanced systems topics before you can explain one join.

- [ ] I can query but need formal reasoning
  Use Foundations of Databases for relational queries and dependencies after discrete mathematics and logic. It supplies another kind of explanation; do not expect an up-to-date engine tutorial.

## Compare the three books

### Database Design

Adrienne Watt; Nelson Eng · 2 · 2014
Free design-first spine

Choose it when: You want a readable route from entities through dependencies to relational tables.
Use another route when: You need one source for advanced concurrency, cost-based optimization or all current SQL dialect details.
Selected reading: Second edition: chapters 7–12, then 15–16; use the earlier modeling chapters when entity/relationship distinctions remain unclear.
Limits and trade-offs: BCcampus identifies the 2014 edition and its SQL additions. Its examples and model conventions are not automatically the behavior of every current engine.

#### Try before choosing

From chapters 7–12, distinguish a room, a booking and a tag; specify the key and the number of rows each relationship can produce.
Check your work: One booking can have several tags, so a join can change row grain. An entity name alone does not establish a one-to-one relationship.

Your notes:

________________________________

[Official free reading](https://opentextbc.ca/dbdesign01/)

[Book record and access options](https://unireadinglist.com/subjects/computer-science/database-design-watt-eng/)

### Database System Concepts

Abraham Silberschatz; Henry F. Korth; S. Sudarshan · 7 · 2019
Relational and systems reference

Choose it when: Your syllabus requires a broad account beyond schema design, with query languages and transaction concepts in one edition.
Use another route when: You need only a short first design course or expect the public companion to provide the complete commercial book.
Selected reading: Seventh edition: chapters 2–4, 6–7; chapter 17 introduces transactions after the relational core.
Limits and trade-offs: The authors identify March 2019 and ISBN 9780078022159. Public contents, slides and practice resources have different access scope from the complete textbook.

#### Try before choosing

After sections 3.6–3.7 and 4.1, predict COUNT(\*), COUNT(column) and SUM(column) for each room before executing the example.
Check your work: A room with no booking and a booking with an unknown seat count are distinct states even when both sums display NULL.

Your notes:

________________________________

ISBN: 9780078022159

[Book record and access options](https://unireadinglist.com/subjects/computer-science/database-system-concepts/)

### Foundations of Databases

Serge Abiteboul; Richard Hull; Victor Vianu · Complete authors’ online draft
Formal-query companion

Choose it when: You already understand relational design and want arguments about queries, constraints and expressiveness.
Use another route when: You need a first SQL course, vendor-specific configuration or a claim that set theory directly describes duplicate-preserving SQL.
Selected reading: Locate the relational-model, query-language and dependency discussions in the authorized draft; no unverified current chapter/page map is assigned.
Limits and trade-offs: The catalogue identifies an authors’ online draft, not a freshly verified print edition. The endpoint was unavailable in this source check; use the accessible design spine and verify your legitimate draft access before assigning reading.

#### Try before choosing

Represent a tiny query as a mathematical relation, then state what must change when identical SQL rows are retained.
Check your work: Distinguish set membership from multiplicity and NULL from an ordinary value. An equivalence claim needs its semantic assumptions, not just similar notation.

Your notes:

________________________________

[Official free reading](https://webdam.di.ens.fr/Alice/)

[Book record and access options](https://unireadinglist.com/subjects/computer-science/foundations-databases-abiteboul-hull-vianu/)

## Check your preparation

### What does a key establish?

Your notes:

________________________________

### Why can COUNT(\*) be misleading after LEFT JOIN?

Your notes:

________________________________

### Does NULL mean zero?

Your notes:

________________________________

## Six steps with a concrete output

- [ ] Choose an entity grain

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.

Your notes:

________________________________

- [ ] Declare dependencies

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.

Your notes:

________________________________

- [ ] Predict the join

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.

Your notes:

________________________________

- [ ] Choose the denominator

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.

Your notes:

________________________________

- [ ] Test multiplicity

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.

Your notes:

________________________________

- [ ] Write a reproducible query note

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.

Your notes:

________________________________

## 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.

Your notes:

________________________________

## 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?

Your notes:

________________________________

## 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.

## Answer and interpretation

### What does a key establish?

It identifies rows under declared constraints. A column with different values in one sample is not thereby a proven key for every permitted database.

### Why can COUNT(\*) be misleading after LEFT JOIN?

It counts the joined rows, including a null-extended row for an unmatched left row. Count a non-null right-side identifier when the question concerns matched records.

### Does NULL mean zero?

No. It marks missing/unknown information under the chosen representation. Zero is a value; absence of a booking is another relationship state. State the reporting rule before replacing NULL.

### Original SQL example: no match is not an unknown value

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.

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.

## Original SQL example: no match is not an unknown value — sql

```sql
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

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.

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

### Rows versus entities

A joined row is not automatically one customer, booking or room. Name the grain before using a count.

### NULL replaced without a rule

COALESCE can implement an explicitly justified output convention, but cannot discover a missing measurement.

### A sample as a constraint proof

Sample uniqueness and successful test queries do not establish keys, dependencies or correctness for every allowed state.

## Sources and edition checks

- [BCcampus: Database Design second-edition contents and access](https://opentextbc.ca/dbdesign01/)
  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](https://www.db-book.com/)
  The authors confirm seventh edition,2019 and the selected ISBN; public companion material is not represented as the complete book.

- [Authors: seventh-edition contents](https://www.db-book.com/toc-dir/toc.pdf)
  The authors’ contents verify the assigned query, design and transaction chapters.

- [Authors: Foundations of Databases online draft](https://webdam.di.ens.fr/Alice/)
  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](https://www.sqlite.org/lang_aggfunc.html)
  COUNT and SUM/NULL semantics are checked against the engine’s official reference.

- [SQLite: SELECT and join processing](https://www.sqlite.org/lang_select.html)
  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](https://unireadinglist.com/editorial/)