عرض عملي: إنشاء قاعدة بيانات Library مع القيود

Written Explanation

Step 1: user and database (once, as postgres)

This is the only step that needs the superuser. Run it once:

CREATE USER library_app WITH PASSWORD 'change-me';
CREATE DATABASE library OWNER library_app;

Use a throwaway password for a local lab, or set it with \password library_app so it does not appear in your history. If the user or database already exists, you will get an "already exists" error; the statements are not re-runnable by design, and that is fine because they are setup, not part of the schema. Do not put real passwords in files you commit.

From now on, connect as the application user:

psql -U library_app -d library

Step 2: the schema script

Save this file as schema.sql. It was run against PostgreSQL 18.3 for this lesson.

-- schema.sql: rebuilds the Library tables from scratch.
-- Drop in reverse dependency order (children before parents).
DROP TABLE IF EXISTS loans;
DROP TABLE IF EXISTS books;
DROP TABLE IF EXISTS members;
DROP TABLE IF EXISTS authors;

CREATE TABLE authors (
  id      INTEGER PRIMARY KEY,
  name    TEXT NOT NULL,
  country TEXT
);

CREATE TABLE books (
  id        INTEGER PRIMARY KEY,
  isbn      TEXT NOT NULL UNIQUE,
  title     TEXT NOT NULL,
  author_id INTEGER NOT NULL REFERENCES authors (id),
  copies    INTEGER NOT NULL CHECK (copies >= 0)
);

CREATE TABLE members (
  id    INTEGER PRIMARY KEY,
  name  TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE
);

CREATE TABLE loans (
  id        INTEGER PRIMARY KEY,
  book_id   INTEGER NOT NULL REFERENCES books (id),
  member_id INTEGER NOT NULL REFERENCES members (id),
  loan_date DATE NOT NULL DEFAULT CURRENT_DATE
);

-- Seed data: parents first, then children.
INSERT INTO authors (id, name, country) VALUES
  (1, 'Ursula Le Guin', 'USA'),
  (2, 'Chinua Achebe', 'Nigeria');
INSERT INTO books (id, isbn, title, author_id, copies) VALUES
  (1, '978-0-00-000001-1', 'The Dispossessed', 1, 3),
  (2, '978-0-00-000002-2', 'Things Fall Apart', 2, 2);
INSERT INTO members (id, name, email) VALUES
  (1, 'Sara', 'sara@example.com'),
  (2, 'Omar', 'omar@example.com');
INSERT INTO loans (id, book_id, member_id) VALUES (1, 1, 1);

(The ISBN values are made up for the exercise.) Three design points:

• Order matters. A DROP TABLE books would fail while loans still points at it, so loans goes first. For CREATE it is the reverse: authors and members before books and loans, because a foreign key can only point at a table that exists. Seed rows follow the same parent-first order, since a book needs its author to exist.
• DROP TABLE IF EXISTS makes the first lines harmless on a fresh database (PostgreSQL prints a notice and skips) and clean on a database that already has the tables. That is what makes the script re-runnable.
• Constraints live in the table definitions, not in a separate step: NOT NULL, UNIQUE, CHECK, and REFERENCES.

Step 3: run it, twice

psql -U library_app -d library -v ON_ERROR_STOP=1 -f schema.sql

On a fresh database the first lines print NOTICE: table "loans" does not exist, skipping, then DROP TABLE lines, CREATE TABLE four times, and INSERT 0 2, INSERT 0 2, INSERT 0 2, INSERT 0 1. Run the same command again: the notices disappear (the tables exist), the drops succeed, and the same CREATE TABLE and INSERT lines follow. Check:

SELECT (SELECT count(*) FROM authors) a, (SELECT count(*) FROM books) b,
       (SELECT count(*) FROM members) m, (SELECT count(*) FROM loans) l;

 a | b | m | l
---+---+---+---
 2 | 2 | 2 | 1

After two runs the counts are 2, 2, 2, and 1, not doubled: each run starts clean. For a stronger test, as postgres run DROP DATABASE library; and CREATE DATABASE library OWNER library_app;, then run the script again. It works from nothing, which is the acceptance rule of the lab.

The -v ON_ERROR_STOP=1 option stops the script at the first error. We confirmed that it stops on a failing statement in Lesson 2.

Step 4: the planned mistake, a loan for a member who does not exist

INSERT INTO loans (id, book_id, member_id) VALUES (2, 1, 999);

ERROR:  insert or update on table "loans" violates foreign key constraint "loans_member_id_fkey"
DETAIL:  Key (member_id)=(999) is not present in table "members".

This is the error the lab asks you to record. Read it: the table (loans), the rule (a foreign key named loans_member_id_fkey), and the value (999). The row was not inserted.

Then break the other rules in turn (use ids that are free, such as 3). The messages are in this lesson's real run:

Step 5: the rollback

BEGIN;
UPDATE books SET copies = copies - 1 WHERE id = 1;
SELECT id, copies FROM books WHERE id = 1;   -- shows 2
ROLLBACK;
SELECT id, copies FROM books WHERE id = 1;   -- shows 3 again

Seed copies are 3, so the first SELECT shows 2 and the second shows 3. To show a commit, end with COMMIT; instead and check that the value stays at 2 (then re-run schema.sql to restore the starting state).