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.