Lab 01 — Model and implement the catalogue

Objectives

After this lab, learners can:

• Turn a written domain description into a normalized schema.
• Seed it repeatably and check the result with a query.

Before You Start

• A scratch database you can drop and recreate, as set up in the study guide.
• The keys and normal forms in the lecture note.

Steps

1. Read the catalogue description and list its entities.

   three entities, plus one table for the many-to-many.

2. Write CREATE TABLE for each, with a primary key on every one, and run the script.

   CREATE TABLE
   CREATE TABLE
   CREATE TABLE
   CREATE TABLE

   Hint

   Create author and book before book_author: a foreign key needs its target to exist.

3. Write a seed script inserting two authors, three books and one loan. Run it twice from an empty database.

   the same row counts both times.

4. Insert a loan for a book id that does not exist.

   ERROR:  insert or update on table "loan" violates foreign key constraint

5. Count the loans per author with a left join.

   every author, including those with zero loans.

Acceptance

• The schema script runs from an empty database with no errors.
• Every table has a primary key, and every relationship a foreign key.
• The seed script gives the same row counts on a second clean run.
• A loan for a missing book is rejected by the database.

Stretch

Add a member table and make every loan reference one. Which existing rows break, and how do you migrate them?