CSE Roadmap

Common tier · about 3 hours · 5 parts

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.