Part 5 of 5 · 3 min read
Exercises
Easy
Easy 1: State a dependency (15 minutes)
State the functional dependency that holds between customer_id and address in customers.
Done when: customer_id → address is correctly stated.
Hint
customer_id → address.
Easy 2: Check with a query (15 minutes)
Write a query that would reveal a violation of customer_id → address, if one existed.
Done when: a correct GROUP BY customer_id HAVING COUNT(DISTINCT address) > 1 query is given.
Hint
SELECT customer_id, COUNT(DISTINCT address) FROM customers GROUP BY customer_id HAVING COUNT(DISTINCT address) > 1;
Easy 3: Full or partial (15 minutes)
In order_items (order_id, book_id, quantity), state whether quantity's dependency on the composite key is full or partial.
Done when: full is correctly identified, since quantity needs both columns together.
Hint
Full, since neither order_id nor book_id alone determines quantity.
Medium
Medium 1: Find a partial dependency (25 minutes)
In order_items (order_id, book_id, quantity, book_title), assuming book_title was mistakenly duplicated into this table, identify the partial dependency.
Done when: book_id → book_title is correctly identified as a partial dependency on the composite key.
Hint
book_id → book_title is a partial dependency, since book_title doesn't need order_id at all.
Medium 2: Apply transitivity (25 minutes)
Given author_id → country and country → timezone, derive the implied dependency using transitivity.
Done when: author_id → timezone is correctly derived.
Hint
author_id → timezone, derived by chaining the two given dependencies.
Medium 3: Reflexivity in plain terms (25 minutes)
Explain, in plain language, why {customer_id, full_name} → customer_id is trivially true by reflexivity.
Done when: the explanation correctly notes that Y here is a subset of X's own columns, making the dependency automatic.
Hint
Y (customer_id) is literally one of the columns already in X ({customer_id, full_name}), so the dependency holds with no need to check any data.
Hard
Hard 1: Spotting a hidden functional dependency (30 minutes)
A books table has columns book_id, title, publisher_name, publisher_country. Identify a functional dependency here that signals a design problem, and explain the problem it signals.
Done when: publisher_name → publisher_country is correctly identified, with the issue named as publisher_country not actually depending on book_id, suggesting publisher data belongs in its own table.
Hint
publisher_name → publisher_country holds and signals that publisher information doesn't depend on book_id at all, suggesting a separate publishers table is missing.
Hard 2: A dependency that doesn't hold (35 minutes)
Explain why title → book_id would not be a valid functional dependency to assume, even if it happens to hold for the data currently in the table.
Done when: the explanation correctly notes a functional dependency must hold for all possible valid data, not merely the data currently present, and two books could plausibly share a title.
Hint
A functional dependency is a rule about all valid data, not a coincidence of today's rows; nothing stops two different books from sharing a title in the future.
Hard 3: Using augmentation correctly (40 minutes)
Given order_id → placed_on, use augmentation to derive a new dependency by adding customer_id to both sides, and explain in plain words what the derived dependency actually means.
Done when: order_id, customer_id → placed_on, customer_id is correctly derived, with a plain-language explanation that this just restates the original fact with an extra, already-known column tagging along.
Hint
order_id, customer_id → placed_on, customer_id; this just says the same fact (order_id determines placed_on) still holds once customer_id is tagged onto both sides, adding no new information.