CSE Roadmap

Core tier · about 2 hours · 5 parts

Part 5 of 5 · 4 min read

Exercises

Easy

Easy 1: Name the pieces (15 minutes)

For the orders table from this page, name its schema, give one example row, and state which single column is its foreign key.

Done when: schema, one example row, and the foreign key column are each named correctly.

Hint

The schema is the three column names and types; the foreign key is the column that references another table's primary key.

Easy 2: Relation versus relationship (15 minutes)

Explain in two sentences why a single table with no foreign keys at all is still correctly called a relation.

Done when: the explanation names the mathematical meaning of "relation" and does not lean on foreign keys to justify it.

Hint

A relation only needs a set of tuples over fixed domains. Nothing in that definition mentions other tables.

Easy 3: Read a line in a diagram (15 minutes)

Given reviews (review_id PK, customer_id FK -> customers.customer_id, book_id FK -> books.book_id, rating), state which two tables reviews connects to, and which direction each connection points.

Done when: both connected tables are named, each with the direction of its reference stated correctly.

Hint

Follow each FK -> arrow to see which table and column it names.

Medium

Medium 1: Draw the diagram (25 minutes)

Draw, in the plain-text style from part 3, the schema diagram for authors (author_id PK, name), books (book_id PK, author_id FK, title) and orders (order_id PK, customer_id FK, book_id FK, placed_on), assuming customers also exists.

Done when: all four tables appear with correct primary keys, and every foreign key arrow points at the correct table and column.

Hint

Draw one box per table and one line per FK -> arrow, with the arrow direction matching the text.

Medium 2: Two instances, one schema (25 minutes)

Using the customers schema, write two different instances (2 rows each) that both satisfy the same schema. State in one sentence what stayed the same between them and what changed.

Done when: two valid instances are written, and the comparison correctly names the schema as unchanged and the row values as different.

Hint

Keep the column names and types the same across both instances. Only the row values should differ.

Medium 3: Spot the missing attribute (25 minutes)

A table is described as orders (order_id, customer_id, placed_on), with no column marked as a primary key. Explain what could go wrong if two rows shared an identical order_id, and what marking order_id as the primary key prevents.

Done when: a concrete problem from a duplicate order_id is described, and the preventive role of a primary key is stated correctly.

Hint

Two rows with the same order_id make it impossible to say which order a later update or delete refers to.

Hard

Hard 1: A relation with no obvious key (30 minutes)

A table book_genres (book_id, genre) records that a book belongs to a genre. A book can have more than one genre. Explain why neither book_id alone nor genre alone can be the primary key, and propose what the real primary key should be.

Done when: both single-column candidates are correctly ruled out, and a working primary key using both columns together is proposed.

Hint

If either column alone were the key, two rows with the same book_id but different genres would break uniqueness. The key needs both columns together.

Hard 2: Diagram versus schema drift (35 minutes)

Describe a realistic situation where a schema diagram, drawn once and kept as documentation, could stop matching the database's real schema. Describe one concrete way a team could detect that drift before it causes a bug.

Done when: a plausible cause of drift is described, and one concrete detection method is proposed.

Hint

A column added or renamed directly in the database, without updating the diagram, is the most common cause. A diagram generated from the live schema detects this automatically.

Hard 3: Translate a plain-English requirement into a relation (40 minutes)

A requirement says: "Track which employee processed which order, and when." Define a table that satisfies this, listing its attributes, its primary key, and any foreign keys. Assume employees (employee_id PK, name) already exists.

Done when: a complete table definition is given with a primary key and at least one correctly directed foreign key.

Hint

The table needs its own primary key, a foreign key to employees, a foreign key to orders, and a timestamp column.