Chapter 1 — Exercises

Chapter 1 is conceptual — you haven't installed PostgreSQL yet (that's Chapter 2). So these exercises build understanding and judgment rather than SQL syntax. They're worth doing seriously: the mental models you form here make every later chapter easier. Write your answers in your own words.

Exercises marked (answer in Appendix) have worked solutions in Answers to Selected Exercises. A ⭐ marks a stretch problem.

Group A — Concept checks

1.1 In your own words, explain the difference between a database and a database management system (DBMS). Give an example of each from the chapter. (answer in Appendix)

1.2 The chapter lists five problems that databases solve. Name all five from memory, then write one sentence for each describing the problem.

1.3 What does it mean to say SQL is a declarative language? Contrast it with telling a computer how to do something step by step.

1.4 Explain the phrase "the DBMS is an abstraction layer." What complexity does it hide from you?

1.5 Why is durability more than just "saving a file"? What does a database guarantee that a manual save does not?

1.6 Define redundancy in data, and explain how redundancy leads to integrity problems. Use the "customer changed her email" story as your example. (answer in Appendix)

Group B — Diagnose the failure

For each scenario, identify which of the five problems (structure, integrity, concurrency, durability, querying-at-scale) is the primary culprit. Some scenarios involve more than one — name the main one and justify it.

1.7 A shared spreadsheet shows a product's quantity as -3. Nobody can explain how stock went negative. (answer in Appendix)

1.8 Two support agents update the same customer record at the same time; when they save, one agent's notes disappear.

1.9 A report says the company has 5,000 customers, but the marketing team counts 5,180 distinct email addresses, because some customers were entered twice with slightly different spellings.

1.10 A "revenue this year" calculation has been wrong for three months because a column was accidentally deleted and the formula silently broke.

1.11 Opening the master data file now takes two minutes, and sorting it freezes the computer.

1.12 After a power outage, the last four hours of entered orders are gone. (answer in Appendix)

1.13 ⭐ A scenario that's tricky: a spreadsheet stores each order's customer name directly in the order row (not a reference). The company wants to email all customers who ordered in March. Which problem(s) make this harder than it should be, and why? Explain how a properly designed database would change the situation.

Group C — History and landscape

1.14 Who proposed the relational model, and in what year? What was the core idea of his proposal? (answer in Appendix)

1.15 Hierarchical and network databases came before the relational model. What was the main drawback that the relational model fixed?

1.16 The NoSQL movement of the 2000s traded away some relational guarantees. What did it gain in exchange, and what kinds of organizations drove its adoption?

1.17 Give two concrete examples of how PostgreSQL absorbed ideas from the NoSQL world (hint: think about flexible data and search).

1.18 Match each database to its category: PostgreSQL, Redis, MongoDB, Neo4j, Cassandra, SQLite. Categories: relational, document, key-value, wide-column, graph. (One category will have two databases.)

1.19 ⭐ The chapter claims "the relational model is right for the vast majority of data problems." Steel-man the opposing view: describe a realistic application for which a non-relational database might genuinely be a better default, and say which kind.

Group D — Reading SQL (no need to run it)

You can't execute SQL yet, but you can start to read it. For each query, describe in plain English what question it's asking. Don't worry about exact syntax.

1.20 (answer in Appendix)

SELECT first_name, last_name
FROM customers
WHERE loyalty_tier = 'gold';

1.21

SELECT COUNT(*)
FROM orders
WHERE status = 'cancelled';

1.22

SELECT category_id, COUNT(*) AS num_products
FROM products
GROUP BY category_id;

1.23 ⭐ Looking at this query, what relationship between two tables does the JOIN seem to rely on? (You'll learn joins in Chapter 6 — just reason from the names.)

SELECT o.order_id, c.email
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id;

Group E — Design thinking

1.24 Take the opening spreadsheet story. List the separate things (entities) the store is tracking that probably each deserve their own table. For each, name two pieces of information (attributes) you'd store about it. (answer in Appendix)

1.25 Explain, in two or three sentences, why storing a customer's email once (and referencing it) is better than copying it into every order. Connect your answer to at least two of the five problems.

1.26 ⭐ "We'll just use a folder of JSON files as our database." Write a short, friendly reply (3–4 sentences) to a teammate who proposes this for a new multi-user web app, explaining the risks without being dismissive.

Group F — Progressive project kickoff

1.27 Choose your project domain: e-commerce, library, healthcare clinic, university registration, or another real system that interests you. Write one paragraph describing what the system does and who uses it.

1.28 List 8–10 questions your system will eventually need to answer (e.g., for a library: "Which members have overdue books?"). These will become your future queries — keep the list; you'll return to it in Chapter 3 and Chapter 17.

1.29 List the main things (entities) your system tracks — aim for 6–10. Don't design tables yet; just name the nouns.

1.30 ⭐ For two of your entities, describe the relationship between them in plain English, and guess whether it's one-to-many or many-to-many (e.g., "a member can borrow many books, and over time a book can be borrowed by many members → many-to-many"). You'll formalize this thinking in Part III.

Self-check. If you can confidently do 1.2 (the five problems), 1.7–1.12 (diagnosing failures), and 1.27–1.29 (your project kickoff), you're ready for Chapter 2. Keep your project notes somewhere safe — they're the spine of everything you'll build.