Chapter 18 — Exercises
Mapping exercises — produce DDL (or describe the tables). Use Mercado as the reference. (answer in Appendix) = worked solution in Answers. ⭐ = stretch.
Group A — Entities & attributes
18.1 Map this entity to a CREATE TABLE with proper types/keys: book(isbn, title, page_count, published_on, price). Choose the PK deliberately. (answer in Appendix)
18.2 A customer has a composite address (line, city, state, postal). How do you map it? Why not one column?
18.3 A customer can have several phone numbers. Map this multi-valued attribute. (answer in Appendix)
Group B — 1:N relationships
18.4 One author writes many books; each book has one author. Which table gets the foreign key, and why? Write it. (answer in Appendix)
18.5 In Mercado, orders.employee_id is nullable but orders.customer_id is NOT NULL. Explain how participation drove each choice.
18.6 ⭐ For a 1:N "department has many employees," choose the ON DELETE action for the FK and justify it (what should happen to employees if a department is deleted?).
Group C — M:N relationships
18.7 Map "students enroll in courses" (M:N) to tables, including the enrollment's grade. Where does grade go and why? (answer in Appendix)
18.8 Map "books have many authors; authors write many books" with a junction table.
18.9 ⭐ A teammate maps M:N by adding tag1, tag2, tag3 columns. List every problem and give the correct junction-table design.
Group D — Inheritance
18.10 Describe the three inheritance mappings (single-table, table-per-subclass, table-per-concrete) and one pro/con of each. (answer in Appendix)
18.11 A payment is a card payment OR a bank transfer, each with distinct required fields. Which mapping would you choose if integrity matters and the subtypes have many distinct attributes? Why?
18.12 ⭐ When is single-table inheritance the right call despite its nullable columns?
Group E — Weak entities & self-references
18.13 Why is order_items a weak entity, and what is its primary key? (answer in Appendix)
18.14 Map a self-referencing relationship: categories with a parent category. Write the column.
18.15 ⭐ Map "an employee has one manager (also an employee)" and explain why manager_id is nullable.
Group F — Full mapping
18.16 Take the Chapter 17 Case Study 1 course-registration ER model and write the complete DDL (students, courses, sections, instructors, rooms, terms, enrollment, prerequisite). (answer in Appendix — partial)
18.17 ⭐ Verify your DDL by listing, for each relationship in the model, which rule you applied and where the FK/junction landed.
Group G — Progressive project
18.18 Apply all the mapping rules to your Chapter 17 ER diagram and write schema.sql for your project.
18.19 Load it and insert a few rows; try to violate a constraint to confirm enforcement.
18.20 ⭐ For one inheritance/subtype situation in your domain (if any), pick a mapping and justify it.
Self-check. If you can take any ER diagram and produce correct DDL — FK on the right side, M:N as a junction, relationship attributes on the junction — the mapping is mechanical for you now. Next: is the schema well-formed? (Normalization.)