Chapter 3 — Exercises

Run the SQL ones against mercado (sample data). The conceptual ones build the vocabulary you'll use for the rest of the book. (answer in Appendix) = worked solution in Answers. ⭐ = stretch.

Group A — Vocabulary

3.1 Define each term in one sentence and give a Mercado example: relation, tuple, attribute, domain, degree, cardinality. (answer in Appendix)

3.2 The chapter says "a relation is a set of tuples." Name two consequences of the word set (think: duplicates, order).

3.3 Why must you use ORDER BY if you want rows in a specific order? What does the relational model say about the "natural" order of rows?

3.4 Explain the difference between a domain and a data type. How can a CHECK constraint narrow a domain beyond what the type allows?

Group B — Keys

3.5 For the customers table, list one superkey, both candidate keys, and the chosen primary key. (answer in Appendix)

3.6 Mercado uses surrogate primary keys but also declares email and sku as UNIQUE. Explain the reasoning for both choices in two sentences.

3.7 order_items has the composite primary key (order_id, product_id). In plain English, what real-world rule does that key enforce? What would change if the key were just (order_id)?

3.8 Run \d inventory. What is its primary key, and what does each row represent? (answer in Appendix)

3.9 ⭐ Suppose Mercado had used email as the primary key of customers (a natural key). Describe two concrete problems this would cause when (a) a customer changes their email and (b) you join orders to customers.

Group C — Foreign keys & referential integrity

3.10 List three foreign keys in the orders table (use \d orders). For each, name the table it references. (answer in Appendix)

3.11 Try to insert an order for a non-existent customer:

INSERT INTO orders (customer_id, order_date, status)
VALUES (9999, now(), 'pending');

What error do you get, and which integrity rule prevented it?

3.12 orders.employee_id is a nullable foreign key. What does a NULL there mean in business terms, and why is nullable appropriate here when customer_id is NOT NULL?

3.13 ⭐ Without running it, predict what happens if you try to DELETE FROM customers WHERE customer_id = 1; while customer 1 has orders. (Hint: addresses cascades, but orders.customer_id has no cascade.) Then reason about why Mercado was designed this way.

Group D — NULL and three-valued logic

3.14 Write a query to find all customers with no phone number. Then explain why WHERE phone = NULL returns nothing. (answer in Appendix)

3.15 Predict the result of each, then verify with SELECT ... ;:

SELECT 5 = NULL;
SELECT NULL = NULL;
SELECT NULL IS NULL;
SELECT (5 > 3) AND NULL;

3.16 Run both and explain why the counts differ:

SELECT COUNT(*) FROM customers;
SELECT COUNT(phone) FROM customers;

(answer in Appendix)

3.17 ⭐ A colleague writes WHERE loyalty_tier <> 'gold' to "get all non-gold customers." If loyalty_tier could be NULL, what's the bug? Rewrite the condition to also include the null-tier customers. (Mercado's column is NOT NULL, so reason about the general case.)

Group E — Constraints

3.18 Try to insert a review with rating = 7. What happens, and which constraint stops it? (answer in Appendix)

3.19 Try to insert a product with price = -10. What constraint fires? Write the constraint that's being enforced (find it with \d products).

3.20 Name the integrity constraint responsible for each guarantee: (a) no two customers share an email; (b) every order's customer exists; (c) rating is between 1 and 5; (d) email is always present.

3.21 ⭐ The chapter claims constraints make integrity "automatic … for every client and program forever." Explain why enforcing a rule in the database is more reliable than enforcing it in application code, using the rating example.

Group F — Reading a schema

3.22 Using only \d commands, map out which tables reference customers via a foreign key. (Check addresses, orders, reviews.) (answer in Appendix)

3.23 ⭐ Draw (on paper or in text) the relationships among orders, order_items, and products. Label each as one-to-many or many-to-many, and explain how order_items makes the many-to-many possible.

Group G — Progressive project

3.24 For each central entity in your project, choose a primary key (prefer a surrogate) and list any natural keys you'll protect with UNIQUE.

3.25 Identify every foreign key your design needs so far. Write each as "table.column → referenced_table.column."

3.26 Write at least three constraints your data must obey (one NOT NULL, one UNIQUE, one CHECK). Express each in words and in rough SQL.

3.27 ⭐ List two attributes that may legitimately be NULL in your domain and two that must always be present. Justify each.

Self-check. If you can identify keys and foreign keys from \d output, query for NULLs correctly, and explain why a constraint belongs in the database, you've mastered the model. Chapter 4 turns these relations into algebra.