CSE Roadmap

Core tier · about 3 hours · 5 parts

Part 5 of 5 · 3 min read

Exercises

Easy

Easy 1: Name the pieces (15 minutes)

For "a Customer writes a Review of a Book," name the entities, one attribute of Customer, and the relationship.

Done when: Customer and Book are named as entities, one attribute is given, and "writes" (or similar) is named as the relationship.

Hint

Customer and Book are entities; "address" or "name" is an attribute of Customer; "writes" is the relationship.

Easy 2: Classify the cardinality (15 minutes)

Classify the cardinality between Customer and Review, assuming a customer can write many reviews but each review is written by exactly one customer.

Done when: one-to-many is correctly identified.

Hint

One-to-many, since many reviews trace back to one customer each.

Easy 3: Spot the many-to-many (15 minutes)

Explain why Book-to-Review is many-to-many if a book can receive many reviews and, separately, a customer can review many books.

Done when: many-to-many is correctly justified from both directions.

Hint

A book connects to many reviews, and (through different customers) many different reviewers, satisfying many-to-many from both sides.

Medium

Medium 1: Draw a small ER diagram (25 minutes)

Draw an ER diagram for Author, Book and Publisher, where one publisher publishes many books, and one author can write many books, but each book has exactly one author and one publisher.

Done when: both relationships are drawn with correct one-to-many cardinality in both cases.

Hint

Publisher 1 ----< many Book, and Author 1 ----< many Book, drawn as two separate one-to-many relationships into Book.

Medium 2: Predict the junction table (25 minutes)

Given a many-to-many relationship between Student and Course, predict what table will be needed once this diagram is translated into real tables.

Done when: a junction table, correctly named something like enrollments, is predicted.

Hint

An enrollments table, holding a foreign key to Student and a foreign key to Course.

Medium 3: Attribute or entity (25 minutes)

Decide whether "genre" should be an attribute of Book, or its own entity, if a book can have more than one genre.

Done when: genre is correctly identified as needing to be its own entity (with a many-to-many relationship to Book), since a single attribute cannot hold more than one value cleanly.

Hint

Genre should be its own entity, connected to Book through a many-to-many relationship, since a single attribute cannot represent several values per book cleanly.

Hard

Hard 1: A one-to-one relationship in practice (30 minutes)

Describe a realistic one-to-one relationship in the bookstore domain, and explain why it is sometimes modeled as two separate tables rather than merged into one.

Done when: a real one-to-one case is described, such as Customer to a separate CustomerPrivateNotes table, with a reason for the split, such as access control or optional data.

Hint

A CustomerPrivateNotes table kept separate from Customer might exist so that access to sensitive notes can be restricted independently of ordinary customer data.

Hard 2: Cardinality that changes the design entirely (35 minutes)

Explain how the design would change if, contrary to this subject's simplified model, a book could actually have more than one author.

Done when: the explanation correctly identifies that Author-to-Book would become many-to-many, requiring a junction table instead of a simple foreign key on Book.

Hint

Author-to-Book becomes many-to-many, needing a junction table (such as book_authors) rather than a single author_id foreign key directly on Book.

Hard 3: Translate a vague requirement into entities (40 minutes)

A requirement says: "Track which warehouse currently stocks which books, and how many of each." Identify the entities, the relationship, and its cardinality.

Done when: Warehouse and Book are correctly identified as entities, a many-to-many "stocks" relationship is named, and quantity is correctly identified as an attribute of that relationship rather than of either entity alone.

Hint

Warehouse and Book are entities; "stocks" is a many-to-many relationship between them; quantity belongs to the relationship itself, not to Warehouse or Book alone, since it depends on the specific pairing of the two.