From ER diagrams to tables

An ER diagram describes what a database should record; a relational schema says how, as tables, columns, keys and foreign keys. The translation from one to the other is largely mechanical, following a handful of rules, but each rule involves choices, especially for one-to-one relationships, weak entities and specialization hierarchies. This lesson applies the rules to the Riverbend library diagram from lesson 6, producing a complete schema that runs in SQLite, and explains the alternatives and their trade-offs. Lesson 8 then checks the resulting tables for redundancy using functional dependencies.

Strong entities and their attributes

Each strong entity set becomes a table. Its simple attributes become columns, its key becomes the primary key, and NOT NULL marks attributes that every entity must have. Composite attributes are flattened into one column per component: the branch address becomes street, city and postal_code. Derived attributes, such as the number of days a loan is overdue, are normally not stored but computed in queries or views, so they never go stale. If a natural key is long, unstable or might be missing, a surrogate integer key is introduced and the natural key kept with a UNIQUE constraint, as with branch, whose name is unique but whose identifier is branch_id.

Multivalued attributes cannot be stored in one column without breaking atomicity, so each becomes a separate table containing the owner's key and the value, with the pair as the primary key. A member's phone numbers become the table member_phone(card_no, phone). This rule is the root of first normal form and of good design generally: a list of values hidden in a single text column, such as '780-555-0101; 780-555-0199', cannot be indexed, constrained or queried reliably.

Relationships

How a relationship becomes tables depends on its cardinality. The general rule is to put the foreign key on the side that relates to at most one entity, because a column holds one value.

A one-to-many relationship needs no table of its own: the foreign key on the many side records it fully. A many-to-many relationship always needs a junction table (also called a link, bridge or associative table), because neither participant can hold a list of partners. For a one-to-one relationship, either side can hold the foreign key with a UNIQUE constraint; putting it on the side with total participation avoids NULLs. If both sides are total and the entities share a lifecycle, merging them into one table is often simplest.

Weak entities and associative entities

A weak entity set becomes a table whose primary key combines the owner's key with the weak entity's partial key; the owner's key is also a foreign key, normally ON DELETE CASCADE, because a weak entity cannot exist without its owner. Copy therefore gets the key (isbn, copy_no). A consequence is that any table referring to a copy needs both columns as a composite foreign key, as loan does. If that becomes awkward, a designer may give copies a surrogate barcode key instead, at the cost of declaring (isbn, copy_no) UNIQUE separately.

Lesson 6 argued that loans, which happen repeatedly between the same member and copy and carry their own dates, are best modelled as an associative entity. In tables, that means loan gets its own surrogate key, loan_id, rather than a primary key of (card_no, isbn, copy_no), which would forbid borrowing the same copy twice. Including date_out in a composite key is an alternative. Holds, by contrast, are kept as a plain many-to-many relationship keyed by (card_no, isbn), because the library allows only one hold per member per title at a time.

The complete library schema

CREATE TABLE branch (
    branch_id   INTEGER PRIMARY KEY,
    name        TEXT NOT NULL UNIQUE,
    street      TEXT NOT NULL,
    city        TEXT NOT NULL,
    postal_code TEXT NOT NULL
);
CREATE TABLE title (
    isbn      TEXT PRIMARY KEY,
    title     TEXT NOT NULL,
    pub_year  INTEGER CHECK (pub_year BETWEEN 1450 AND 2100)
);
CREATE TABLE author (
    author_id INTEGER PRIMARY KEY,
    name      TEXT NOT NULL
);
CREATE TABLE writes (                              -- M:N Author-Title
    author_id INTEGER REFERENCES author(author_id),
    isbn      TEXT    REFERENCES title(isbn),
    PRIMARY KEY (author_id, isbn)
);
CREATE TABLE copy (                                -- weak entity owned by title
    isbn      TEXT    NOT NULL REFERENCES title(isbn) ON DELETE CASCADE,
    copy_no   INTEGER NOT NULL,
    condition TEXT    NOT NULL DEFAULT 'good' CHECK (condition IN ('new', 'good', 'worn', 'damaged')),
    branch_id INTEGER NOT NULL REFERENCES branch(branch_id),   -- N:1, total participation
    PRIMARY KEY (isbn, copy_no)
);
CREATE TABLE member (
    card_no   INTEGER PRIMARY KEY,
    name      TEXT NOT NULL,
    email     TEXT UNIQUE
);
CREATE TABLE member_phone (                        -- multivalued attribute
    card_no   INTEGER REFERENCES member(card_no) ON DELETE CASCADE,
    phone     TEXT,
    PRIMARY KEY (card_no, phone)
);
CREATE TABLE student_member (                      -- ISA subclass
    card_no   INTEGER PRIMARY KEY REFERENCES member(card_no) ON DELETE CASCADE,
    school    TEXT NOT NULL
);
CREATE TABLE staff_member (                        -- ISA subclass
    card_no   INTEGER PRIMARY KEY REFERENCES member(card_no) ON DELETE CASCADE,
    hire_date TEXT NOT NULL,
    branch_id INTEGER NOT NULL REFERENCES branch(branch_id),
    supervisor_card INTEGER REFERENCES staff_member(card_no)   -- recursive, optional
);
CREATE TABLE loan (                                -- associative entity
    loan_id   INTEGER PRIMARY KEY,
    card_no   INTEGER NOT NULL REFERENCES member(card_no),
    isbn      TEXT    NOT NULL,
    copy_no   INTEGER NOT NULL,
    date_out  TEXT    NOT NULL,
    due_date  TEXT    NOT NULL,
    returned  TEXT,
    FOREIGN KEY (isbn, copy_no) REFERENCES copy(isbn, copy_no),
    CHECK (due_date > date_out),
    CHECK (returned IS NULL OR returned >= date_out)
);
CREATE TABLE hold (                                -- M:N Member-Title with attributes
    card_no   INTEGER REFERENCES member(card_no) ON DELETE CASCADE,
    isbn      TEXT    REFERENCES title(isbn),
    placed_on TEXT    NOT NULL,
    PRIMARY KEY (card_no, isbn)
);

SELECT COUNT(*) AS tables FROM sqlite_schema WHERE type = 'table';

tables
------
11

Each design decision from lesson 6 is visible: the weak Copy table with its composite key and cascading owner reference; NOT NULL on copy.branch_id for total participation; junction tables writes and hold; member_phone for the multivalued attribute; a separate table for each subclass; a recursive supervisor reference that may be NULL; and CHECK constraints for rules that the ER diagram recorded in notes, such as due dates following the date out. Storing dates as ISO 8601 text (YYYY-MM-DD) in SQLite makes string comparison agree with date order, which is what the CHECK constraints rely on; other systems have a native DATE type.

-- Who has which copy out right now, and where it lives
SELECT m.name, t.title, l.copy_no, b.name AS branch, l.due_date
FROM loan l
JOIN member m ON m.card_no = l.card_no
JOIN copy c   ON c.isbn = l.isbn AND c.copy_no = l.copy_no
JOIN title t  ON t.isbn = c.isbn
JOIN branch b ON b.branch_id = c.branch_id
WHERE l.returned IS NULL
ORDER BY l.due_date;

-- Titles with their authors, combined into one string
SELECT t.title, group_concat(a.name, ' and ') AS authors
FROM title t JOIN writes w ON w.isbn = t.isbn JOIN author a ON a.author_id = w.author_id
GROUP BY t.isbn ORDER BY t.title;

-- The same member borrowing the same copy twice is allowed by the loan_id key
SELECT card_no, isbn, copy_no, COUNT(*) AS times_borrowed FROM loan GROUP BY card_no, isbn, copy_no HAVING COUNT(*) > 1;

name  title                      copy_no  branch     due_date
----  -------------------------  -------  ---------  ----------
Noor  The Northern Lights Atlas  1        Central    2026-10-22
Owen  Small Engines              1        Northside  2026-10-26

title                      authors
-------------------------  ------------------------
Bread and Salt             S. Patel
Small Engines              S. Patel
The Northern Lights Atlas  R. Okoye and M. Gauthier

card_no  isbn               copy_no  times_borrowed
-------  -----------------  -------  --------------
5001     978-0-00-000001-1  1        2

Specialization hierarchies

An ISA hierarchy can be mapped in three main ways, and the choice depends on the disjointness and completeness constraints and on how the data is used.

The library's specialization is overlapping (a staff member can also be a student) and partial (most members are neither), which rules out the table-per-concrete-subclass approach, since ordinary members would have nowhere to live. Table per class handles both properties naturally: a member who is both student and staff has a row in each subclass table. Object-relational mapping tools, discussed in lesson 11, offer all three strategies under names like joined, table-per-class and single-table inheritance.

Checking the mapping

After mapping, several checks catch common errors. Every foreign key should reference a primary or unique key. Every M:N relationship should have become a junction table, and every multivalued attribute a separate table. NOT NULL should match total participation. Composite keys of weak entities should include the owner's key. Constraints that the diagram could not show should appear as CHECK constraints, UNIQUE constraints, or documented application rules. Finally, the tables should be checked for redundancy, a step the ER model does not guarantee; functional dependencies and normal forms, the subject of lesson 8, make that check precise.

import sqlite3, textwrap

con = sqlite3.connect(":memory:")
con.executescript(textwrap.dedent("""
    CREATE TABLE branch (branch_id INTEGER PRIMARY KEY, name TEXT NOT NULL);
    CREATE TABLE copy (isbn TEXT NOT NULL, copy_no INTEGER NOT NULL,
                       branch_id INTEGER NOT NULL REFERENCES branch(branch_id),
                       PRIMARY KEY (isbn, copy_no));
    CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, isbn TEXT, copy_no INTEGER,
                       FOREIGN KEY (isbn, copy_no) REFERENCES copy(isbn, copy_no));
"""))
# SQLite's schema tables describe the mapping: list each table's foreign keys
for (table,) in con.execute("SELECT name FROM sqlite_schema WHERE type='table' ORDER BY name"):
    fks = con.execute(f"PRAGMA foreign_key_list({table})").fetchall()
    for fk in fks:
        print(f"{table}.{fk[3]} -> {fk[2]}.{fk[4]}")
    notnull = [c[1] for c in con.execute(f"PRAGMA table_info({table})") if c[3]]
    print(f"  {table}: NOT NULL columns {notnull}")

  branch: NOT NULL columns ['name']
copy.branch_id -> branch.branch_id
  copy: NOT NULL columns ['isbn', 'copy_no', 'branch_id']
loan.isbn -> copy.isbn
loan.copy_no -> copy.copy_no
  loan: NOT NULL columns []

The catalogue query shows the composite foreign key of loan as two lines, one per column, and the NOT NULL columns of each table. Real projects automate such audits, and many teams generate an ER diagram back from the live schema with tools to compare it with the intended design.