CSE Roadmap

Core tier · about 3 hours · 8 parts

Part 8 of 8 · 3 min read

Exercises

Easy

Easy 1: Name the relationship (20 minutes)

Name the relationship type for each: a country and its cities, students and courses, an employee and their manager, a book and its chapters.

Done when: four answers, each with a reason and one example row from each side.

Hint

Ask "how many on each side?".

Easy 2: Normalize it (20 minutes)

A table orders has columns order_id, customer_name, customer_city, book. Three rows share one customer. Split it into two tables and write the rows.

Done when: two tables with their keys, and a note of how many rows change when the customer moves.

Hint

Customer facts go into their own table with a key.

Easy 3: Match the model (20 minutes)

Pick a model for each: a chat inbox, a login session, a bank ledger, a map of friends, a sensor log of 5,000 readings per second.

Done when: five picks, each with the main question that justifies it.

Hint

Match the question to the model's strength.

Medium

Medium 1: Cost of the split (30 minutes)

A profile is read 1,000 times a second. In the relational form, it takes 4 queries of 4 ms each, run in turn. In the document form, it takes one query of 6 ms. Compute the database time per second in each form. Then state what changes if the profile is also updated 10 times a second and the relational form needs 3 writes.

Done when: read times and write counts for both forms, with the sums.

Hint

Read time per second = reads × time per read.

Medium 2: Denormalize on purpose (30 minutes)

A news site shows each article with its author's name. Articles are read 10,000 times for each time an author changes their name. Compare storing the name in every article with joining to an authors table. Decide, and say when you would change your mind.

Done when: a decision with numbers on both sides and one trigger for reversing it.

Hint

Compare the cost of reads against the cost of the rare write.

Medium 3: Design a row key (30 minutes)

A wide-column store holds 8,000 delivery trucks that report their position every 10 seconds. Design the row key and the columns so that "the route of truck 12 on a given day" is one row read. Compute the number of columns per row and the rows written per day.

Done when: a row key, a column plan, 8,640 columns per row and a row count.

Hint

86,400 seconds per day divided by 10 gives 8,640 positions.

Hard

Hard 1: Pick stores for a ride-hailing app (45 minutes)

A ride-hailing app has riders, drivers, trips, payments, live driver positions and ratings. Choose a store for each kind of data. For each, write the main read, the main write and the thing given up.

Done when: a table of at least six rows, each with a store, a read, a write and a trade-off.

Hint

Money and trips need rules, live positions need speed.

Hard 2: Friends of friends (45 minutes)

A social app has 50 million users, each with 150 friends on average. Estimate the rows touched to find people within two steps and within three steps (as upper bounds). Describe how a relational design and a graph design differ in the work.

Done when: two estimates, and a short comparison naming the cost that grows.

Hint

Multiply the friend counts, and remember overlaps make the real number lower.

Hard 3: Change the shape (50 minutes)

A document store holds 20 million users with an address string. The product now needs street, city and postcode. Plan the change with schema-on-read, including how old and new documents live together and when the old shape can be removed. Compare with a relational migration of the same data.

Done when: a step-by-step plan for each approach, with one risk per step and a rule for removing the old shape.

Hint

Write the new fields first, read both shapes, and remove the old one last.