CSE Roadmap

Core tier · about 3 hours · 5 parts

Part 5 of 5 · 3 min read

Exercises

Easy

Easy 1: Map an entity (15 minutes)

Map a Publisher entity with attributes name and founded_year to a table.

Done when: a correct CREATE TABLE publishers with a primary key is given.

Hint

CREATE TABLE publishers (publisher_id INTEGER PRIMARY KEY, name TEXT NOT NULL, founded_year INTEGER);

Easy 2: Map a one-to-many relationship (15 minutes)

Given Publisher-to-Book is one-to-many, state which table gets the new foreign key.

Done when: books, correctly identified as the many side, is named.

Hint

books is the many side, so it receives the new foreign key.

Easy 3: Map a many-to-many relationship (15 minutes)

State what kind of table Student-to-Course, a many-to-many relationship, requires.

Done when: a junction table is correctly named.

Hint

A junction table, such as enrollments.

Medium

Medium 1: Write the foreign key (25 minutes)

Write the CREATE TABLE for books, including its foreign key to publishers.

Done when: a correct publisher_id INTEGER NOT NULL REFERENCES publishers(publisher_id) column is included.

Hint

Add publisher_id INTEGER NOT NULL REFERENCES publishers(publisher_id) as a column in books.

Medium 2: Write the junction table (25 minutes)

Write the CREATE TABLE for an enrollments junction table between students and courses, including a grade attribute belonging to the enrollment itself.

Done when: a correct junction table with both foreign keys, a composite primary key, and the grade column is given.

Hint

CREATE TABLE enrollments (student_id INTEGER REFERENCES students(student_id), course_id INTEGER REFERENCES courses(course_id), grade TEXT, PRIMARY KEY (student_id, course_id));

Medium 3: Choose the one-to-one side (25 minutes)

For a one-to-one relationship between Employee and ParkingSpot, where not every employee has an assigned spot, decide which table gets the foreign key, and why.

Done when: parking_spots (or equivalent), the side where the relationship is optional, is correctly chosen, with that reasoning stated.

Hint

parking_spots gets the foreign key to employees, since most parking spots, not most employees, are the side expected to have a match.

Hard

Hard 1: A relationship with its own attribute (30 minutes)

Explain why quantity in order_items cannot correctly be placed on either orders or books alone, tying the answer back to this module's mapping rules.

Done when: the explanation correctly states quantity belongs to the many-to-many relationship itself, which is exactly why the junction table, not either original entity, is where it must live.

Hint

Quantity depends on one specific order-and-book pairing, which only the junction table represents; placing it on either original entity would lose that pairing-specific meaning.

Hard 2: Mapping a specialization hierarchy (35 minutes)

Using the Account/CustomerAccount/StaffAccount hierarchy from the previous module, propose a table mapping using one table per subclass, linked by a shared primary key.

Done when: three correctly linked tables are proposed, with customer_accounts.customer_id and staff_accounts.staff_id each referencing accounts' primary key.

Hint

customer_accounts (customer_id PK REFERENCES accounts(account_id), loyalty_points) and staff_accounts (staff_id PK REFERENCES accounts(account_id), role).

Hard 3: Choosing the wrong side for a one-to-one relationship (40 minutes)

Explain what would go wrong, practically, if the foreign key for Employee-to-ParkingSpot were instead placed on employees, given that most employees have no spot at all.

Done when: the explanation correctly notes this would leave the parking_spot_id column NULL for most employees, which works but is a less natural fit than placing it on the smaller, optional side.

Hint

Most employees would have a NULL parking_spot_id, which is valid SQL but means the common case is "no value," the opposite of the usual expectation that a foreign key points somewhere meaningful.