Chapter 17 — Exercises
Mostly design exercises — do them on paper or in a free diagram tool (dbdiagram.io, draw.io). Use Mercado as a reference model. (answer in Appendix) = worked solution in Answers. ⭐ = stretch.
Group A — Identify the pieces
A1 (17.1) From this requirement, list the entities, attributes, and relationships: "Customers place orders. Each order contains one or more products. Each product belongs to a category and is made by a supplier." (answer in Appendix)
17.2 Classify each as simple, composite, multi-valued, derived, or key: a customer's email; a customer's phone numbers; a person's full address; an order's total; a product's product_id.
17.3 In Mercado, list the attributes of the products entity and mark which is the key. Which attribute is semi-structured (and why is that interesting)?
Group B — Cardinality & participation
17.4 State the cardinality (1:1 / 1:N / M:N) of each: customer↔orders; order↔products; category↔products; employee↔manager; product↔reviews. (answer in Appendix)
17.5 Which Mercado relationships are M:N? How is each resolved?
17.6 For order↔customer, state the participation on each side (total/partial) and how it maps to NOT NULL vs nullable. (answer in Appendix)
17.7 ⭐ Give an example of a genuine 1:1 relationship (not in Mercado) and explain why you'd model it as two tables rather than one.
Group C — Diagrams
17.8 Draw a crow's-foot diagram for the customer–order–order_item–product portion of Mercado. Mark cardinality and participation. (answer in Appendix)
17.9 Draw the self-referencing relationships for categories (parent) and employees (manager).
17.10 ⭐ Translate your 17.8 crow's-foot diagram into Chen notation (entities, relationship diamonds, 1/N/M labels).
Group D — Spot the pattern
17.11 Which design pattern does each describe: (a) books and authors where each can have many of the other; (b) a folder that contains folders; (c) users who can each hold several roles? (answer in Appendix)
17.12 ⭐ A comment can be attached to either a post or a photo. What pattern is this, and why is it trickier than a normal relationship? (You'll solve it in Chapter 21.)
Group E — Critique a model
17.13 A students table has a column courses holding 'MATH101, CS200, ENG110'. What's wrong (which relationship is being mismodeled), and how should it be modeled? (answer in Appendix)
17.14 A design makes address a single text column on customers. What kind of attribute is an address, and what problems does the single-column choice cause?
17.15 ⭐ A team modeled "an order's products" by adding product1_id, product2_id, product3_id columns to orders. Explain every problem with this and give the correct model.
Group F — Progressive project
17.16 List your domain's entities and, for each, its attributes (marking keys, multi-valued, derived).
17.17 Identify every relationship with cardinality and participation. Flag the M:N ones.
17.18 Draw the full crow's-foot ER diagram for your domain.
17.19 ⭐ Note which standard patterns (order-items, hierarchy, M:N junction, tagging, polymorphic) appear in your design.
Self-check. If you can take a paragraph of requirements and produce a correct crow's-foot diagram — entities, attributes, cardinality, participation, M:N resolved — you're modeling like a designer. Next: turning the diagram into tables.