CSE Roadmap

Core tier · about 2 hours · 5 parts

Part 5 of 5 · 3 min read

Exercises

Easy

Easy 1: Find the candidate key (15 minutes)

Hint

{author_id} alone works; {name} alone also works under the stated assumption.

For authors (author_id, name, country), assuming no two authors share a name, name two different candidate keys.

**Done when:** two valid candidate keys are named, each justified as unique and minimal.

Easy 2: Super key but not candidate (15 minutes)

Hint

{author_id} by itself is already unique, so adding country is wasted.

Explain why {author_id, country} is a super key of authors but not a candidate key.

**Done when:** the explanation correctly identifies the wasted column.

Easy 3: Pick the primary key (15 minutes)

Hint

author_id is usually preferred since it never changes, while a name-based key could collide later if the assumption breaks.

Given the two candidate keys from Easy 1, explain which one is the better choice for a primary key, and why.

**Done when:** one candidate key is chosen with a stated reason (stability, simplicity, or similar).

Medium

Medium 1: Build a composite key (25 minutes)

Hint

The table needs order_id, book_id, and likely quantity, with PRIMARY KEY (order_id, book_id).

Design a table order_items recording which books appear in which order, where one order can contain many books and one book can appear in many orders. Give its columns and its composite primary key.

**Done when:** a table with a correct composite primary key over two foreign-key columns is given.

Medium 2: Referential integrity in action (25 minutes)

Hint

The database checks the foreign key constraint and rejects the insert before it is stored.

Explain what should happen, and why, when someone tries to insert a row into orders with a customer_id that does not exist in customers.

**Done when:** the explanation correctly states that the insert is rejected, and names referential integrity as the reason.

Medium 3: A foreign key that isn't unique (25 minutes)

Hint

Many different order rows legitimately hold the same customer_id, which a UNIQUE constraint would forbid.

Explain why orders.customer_id does not need a UNIQUE constraint, even though it is a foreign key, using the bookstore's actual order pattern as the example.

**Done when:** the explanation correctly notes that one customer can have many orders, so the foreign key column legitimately repeats.

Hard

Hard 1: Choosing between two primary key candidates (30 minutes)

Hint

An auto-generated book_id never needs to change even if the ISBN data turns out to be wrong; a real-world identifier used as a key creates problems when that real-world fact changes.

A books table could use either an auto-generated book_id or the book's real-world ISBN as its primary key. Argue for one choice over the other, considering what happens if an ISBN is later found to be wrong or shared by two editions.

**Done when:** one choice is defended with a concrete scenario involving the other option's weakness.

Hard 2: Self-referencing foreign key (35 minutes)

Hint

The top-level category's parent_id column should allow NULL, meaning "no parent."

Design a categories table where a book category can have a parent category (for example, "Science Fiction" under "Fiction"), using a self-referencing foreign key. Describe what value the top-level category's foreign key column should hold.

**Done when:** a table with a self-referencing foreign key is given, and the top-level case (likely NULL) is addressed explicitly.

Hard 3: When a composite key should become a surrogate key (40 minutes)

Hint

A later table like returns that needs to reference one specific order-item row is far easier to write against a single-column key than a two-column one.

order_items (order_id, book_id, quantity) uses a composite primary key {order_id, book_id}. Describe one concrete reason a team might instead add a single auto-generated order_item_id as the primary key, keeping {order_id, book_id} as a regular unique constraint.

**Done when:** a concrete reason is given (for example, a later table needing to reference one order-item row directly).