Mapping ER diagrams to relational tables

Mapping turns an ER design into tables, keys and constraints by a fixed sequence of rules, so that every entity, attribute and relationship of the diagram ends up in exactly one place. Each strong entity set becomes a table. A weak entity set becomes a table keyed by its owner’s key plus its partial key. A 1:N relationship becomes a foreign key on the N side, a 1:1 relationship a foreign key that is also unique, and an M:N relationship a table of its own. Multivalued attributes and n-ary relationships get tables too, and a specialisation can be stored in four layouts with different costs. This lesson applies the rules to a college diagram, runs the result in SQLite and in PostgreSQL 18.6, counts how many tables a diagram needs under a stated convention, and ends with a hostel diagram for you to map.

The rules, in order

Entities come first, because relationships refer to them. Teorey, Yang and Fry described a design method of this kind for the extended ER model in 1986, and the order below is the one most books follow:

1. Strong entity set: a table, with one column for each simple attribute or part of a composite one and none for a derived attribute. Its key becomes the PRIMARY KEY; other candidate keys are UNIQUE and NOT NULL.
2. Weak entity set: a table with the owner’s key added. Its PRIMARY KEY is (owner’s key, partial key), with a foreign key to the owner, usually ON DELETE CASCADE.
3. 1:N relationship: a foreign key in the table of the N side, plus the relationship’s attributes; NOT NULL when the N side’s participation is total.
4. 1:1 relationship: a foreign key in one of the two tables, plus the relationship’s attributes. It is UNIQUE, and it goes on the side with total participation so that it can be NOT NULL; merge the tables when both sides are total.
5. M:N relationship: a table with a foreign key to each side, plus the relationship’s attributes; its PRIMARY KEY is the two foreign keys together.
6. Multivalued attribute: a table with the owner’s key and the value; PRIMARY KEY (owner’s key, value), with a foreign key to the owner.
7. n-ary relationship: a table with a foreign key to every participant; its PRIMARY KEY is made of the participants that can be many.
8. Specialisation: one of four layouts, compared below.

The college diagram

The diagram to map collects the relationships of this module’s earlier lessons in one college:

• DEPARTMENT, keyed by dept_code, with a unique name and a building.
• INSTRUCTOR, keyed by instructor_id. WORKS_IN: instructor N:1 department, total for the instructor, with the attribute joined_on. HEADS: instructor 1:1 department, partial on both sides, since a post can be vacant.
• STUDENT, keyed by roll_no, with a two-part name, a unique email and a multivalued phone.
• COURSE, keyed by course_code, with a title and credits. OFFERS: department 1:N course, total for the course. REQUIRES: a recursive M:N relationship between a course and its prerequisites.
• SECTION, a weak entity owned by COURSE, with the partial key (term, section_no). TEACHES: instructor 1:N section, total for the section. HELD_IN: classroom 1:N section, partial for the section, since some sections meet online.
• CLASSROOM, keyed by room_id, with a building and a capacity.
• ENROLS: student M:N section, with the attribute grade.

Entities: strong and weak

Each strong entity set becomes a table, with one column for each simple attribute and one for each part of a composite one: the student’s name becomes first_name and last_name. A derived attribute gets no column. The key becomes the PRIMARY KEY, and every other candidate key gets UNIQUE and NOT NULL, which is how student.email and department.name keep their guarantee.

In SQLite, write NOT NULL on primary key columns too. The SQL standard says that a primary key implies NOT NULL, but SQLite’s documentation explains that an early bug let NULL into primary key columns, and the behaviour stays for compatibility: only an INTEGER PRIMARY KEY, a column declared NOT NULL, or a table that is WITHOUT ROWID or STRICT refuses NULL there.

The weak entity set SECTION becomes a table with its owner’s key added, course_code, and its primary key is that key together with the partial key: (course_code, term, section_no). The identifying relationship needs no table of its own; the foreign key from section to course is that relationship, and ON DELETE CASCADE writes the existence dependency down.

Binary relationships: where the foreign key goes

1:N. The foreign key goes on the N side. Each instructor works in one department, so instructor.dept_code holds one value; on the other side a department would need a list of instructors, which no single column can hold. WORKS_IN is total for the instructor, so the column is NOT NULL, and its attribute joined_on moves into the same table, because each instructor has exactly one such date.

1:1. The foreign key can go on either side, and it must be UNIQUE, or the relationship would quietly become 1:N. Put it on the side with total participation: there it can be NOT NULL, and no row carries an empty column. HEADS is partial on both sides, so department.head_id is UNIQUE and nullable. When both sides are total, the two entity sets can share one table. Keeping two tables with a NOT NULL foreign key in each direction creates a cycle that no single INSERT can satisfy: one of the constraints then has to wait until the end of the transaction, with a foreign key declared DEFERRABLE INITIALLY DEFERRED.

M:N. The relationship becomes a table with a foreign key to each side, and the two together form its primary key, so a student can be in a section only once. Its attributes, such as grade, become columns of that table.

Recursive relationships follow the same rules with one table on both ends: REQUIRES is M:N, so course_prerequisite has two foreign keys to course, named after the roles.

-- The college diagram mapped to tables, one rule at a time.
PRAGMA foreign_keys = ON;

-- Strong entities: one table each; the key becomes the PRIMARY KEY, other candidate keys UNIQUE NOT NULL.
CREATE TABLE department (
  dept_code TEXT NOT NULL PRIMARY KEY,
  name      TEXT NOT NULL UNIQUE,
  building  TEXT NOT NULL
);
CREATE TABLE classroom (
  room_id  TEXT NOT NULL PRIMARY KEY,
  building TEXT NOT NULL,
  capacity INTEGER NOT NULL CHECK (capacity > 0)
);
-- WORKS_IN (N:1, total for INSTRUCTOR): a NOT NULL foreign key on the N side, with the relationship's attribute.
CREATE TABLE instructor (
  instructor_id INTEGER PRIMARY KEY,
  name          TEXT NOT NULL,
  dept_code     TEXT NOT NULL REFERENCES department (dept_code),
  joined_on     TEXT NOT NULL
);
-- HEADS (1:1, partial on both sides): a nullable foreign key made UNIQUE, so nobody heads two departments.
-- SQLite's ADD COLUMN takes no UNIQUE, hence the separate index.
ALTER TABLE department ADD COLUMN head_id INTEGER REFERENCES instructor (instructor_id);
CREATE UNIQUE INDEX department_head ON department (head_id);

-- The composite name becomes two columns; the multivalued phone gets a table of its own.
CREATE TABLE student (
  roll_no    TEXT NOT NULL PRIMARY KEY,
  first_name TEXT NOT NULL,
  last_name  TEXT NOT NULL,
  email      TEXT NOT NULL UNIQUE
);
CREATE TABLE student_phone (
  roll_no TEXT NOT NULL REFERENCES student (roll_no) ON DELETE CASCADE,
  phone   TEXT NOT NULL,
  PRIMARY KEY (roll_no, phone)
);
-- OFFERS (1:N, total for COURSE).
CREATE TABLE course (
  course_code TEXT NOT NULL PRIMARY KEY,
  title       TEXT NOT NULL,
  credits     INTEGER NOT NULL CHECK (credits BETWEEN 1 AND 6),
  dept_code   TEXT NOT NULL REFERENCES department (dept_code)
);
-- REQUIRES (recursive M:N): a table with two foreign keys to COURSE, one per role.
CREATE TABLE course_prerequisite (
  course_code TEXT NOT NULL REFERENCES course (course_code),
  prereq_code TEXT NOT NULL REFERENCES course (course_code),
  PRIMARY KEY (course_code, prereq_code),
  CHECK (course_code <> prereq_code)
);
-- SECTION is weak: the owner's key plus the partial key (term, section_no).
-- TEACHES (1:N, total for SECTION) and HELD_IN (1:N, partial for SECTION) become foreign keys here.
CREATE TABLE section (
  course_code   TEXT NOT NULL REFERENCES course (course_code) ON DELETE CASCADE,
  term          TEXT NOT NULL,
  section_no    INTEGER NOT NULL,
  instructor_id INTEGER NOT NULL REFERENCES instructor (instructor_id),
  room_id       TEXT REFERENCES classroom (room_id),
  PRIMARY KEY (course_code, term, section_no)
);
-- ENROLS (M:N, with the attribute grade): its key is the two participants' keys together.
CREATE TABLE enrolment (
  roll_no     TEXT NOT NULL REFERENCES student (roll_no),
  course_code TEXT NOT NULL,
  term        TEXT NOT NULL,
  section_no  INTEGER NOT NULL,
  grade       TEXT CHECK (grade IN ('A', 'B', 'C', 'D', 'F')),
  PRIMARY KEY (roll_no, course_code, term, section_no),
  FOREIGN KEY (course_code, term, section_no) REFERENCES section (course_code, term, section_no)
);

-- What the catalogue says.
SELECT count(*) AS tables FROM sqlite_schema WHERE type = 'table';

SELECT m.name AS table_name, group_concat(p.name, ', ' ORDER BY p.pk) AS primary_key
FROM sqlite_schema AS m, pragma_table_info(m.name) AS p
WHERE m.type = 'table' AND p.pk > 0
GROUP BY m.name
ORDER BY m.name;

SELECT m.name AS from_table, group_concat(f."from", ', ' ORDER BY f.seq) AS foreign_key,
       f."table" AS to_table, f.on_delete
FROM sqlite_schema AS m, pragma_foreign_key_list(m.name) AS f
WHERE m.type = 'table'
GROUP BY m.name, f.id
ORDER BY m.name, to_table, foreign_key;

┌────────┐
│ tables │
├────────┤
│ 9      │
└────────┘
┌─────────────────────┬────────────────────────────────────────┐
│     table_name      │              primary_key               │
├─────────────────────┼────────────────────────────────────────┤
│ classroom           │ room_id                                │
│ course              │ course_code                            │
│ course_prerequisite │ course_code, prereq_code               │
│ department          │ dept_code                              │
│ enrolment           │ roll_no, course_code, term, section_no │
│ instructor          │ instructor_id                          │
│ section             │ course_code, term, section_no          │
│ student             │ roll_no                                │
│ student_phone       │ roll_no, phone                         │
└─────────────────────┴────────────────────────────────────────┘
┌─────────────────────┬───────────────────────────────┬────────────┬───────────┐
│     from_table      │          foreign_key          │  to_table  │ on_delete │
├─────────────────────┼───────────────────────────────┼────────────┼───────────┤
│ course              │ dept_code                     │ department │ NO ACTION │
│ course_prerequisite │ course_code                   │ course     │ NO ACTION │
│ course_prerequisite │ prereq_code                   │ course     │ NO ACTION │
│ department          │ head_id                       │ instructor │ NO ACTION │
│ enrolment           │ course_code, term, section_no │ section    │ NO ACTION │
│ enrolment           │ roll_no                       │ student    │ NO ACTION │
│ instructor          │ dept_code                     │ department │ NO ACTION │
│ section             │ room_id                       │ classroom  │ NO ACTION │
│ section             │ course_code                   │ course     │ CASCADE   │
│ section             │ instructor_id                 │ instructor │ NO ACTION │
│ student_phone       │ roll_no                       │ student    │ CASCADE   │
└─────────────────────┴───────────────────────────────┴────────────┴───────────┘

The catalogue confirms the mapping: nine tables, one for each of the six entity sets, one for the multivalued phone and one for each of the two M:N relationships; the other five relationships are foreign keys. SQLite’s ADD COLUMN does not accept UNIQUE, which is why head_id gets its uniqueness from a separate index. The on_delete column shows the two cascades: phone numbers go with their student and sections with their course.

Multivalued attributes and n-ary relationships

A multivalued attribute becomes a table holding the owner’s key and one value per row: student_phone (roll_no, phone), keyed on both columns so that a number appears once per student. An n-ary relationship becomes a table with a foreign key to every participant. SUPPLIES (supplier, part, project) gets the key (supplier, part, project) when any combination can occur; when each pair of a part and a project has exactly one supplier, the 1 on SUPPLIER makes (part, project) the key and the supplier an ordinary column.

Specialisation: four layouts

A specialisation has no single mapping. The four common layouts trade NULLs, joins and the constraints they can enforce:

• A table per class: vehicle, car and truck, each subclass table keyed by the vehicle’s key. A whole car takes a join of vehicle and car, all vehicles are one table, and nothing is NULL; disjointness and totality need extra checks across the tables. Choose it when the subclasses have many attributes and relationships of their own and queries cover all vehicles.
• Subclass tables only: car and truck, each repeating the vehicle’s columns. A whole car is one row, but all vehicles take a UNION, and nothing is NULL. Totality is built in, since no vehicle exists outside a subclass, while disjointness needs checks across the tables. Choose it for total, disjoint subclasses that are rarely queried together.
• One table with a type column: vehicle, with every subclass column and a type. Each vehicle is one row and the other kinds’ columns are NULL. The type column makes the specialisation disjoint by itself, and type NOT NULL makes it total. Choose it for a disjoint specialisation with few subclass attributes.
• One table with flags: vehicle, with every subclass column and one boolean flag per subclass. Each vehicle is one row, with NULLs as above, and several flags can be true, so a vehicle can be several kinds at once; a CHECK can still demand at least one flag. Choose it for an overlapping specialisation with few subclass attributes.

“Subclass tables only” also has a gap that matters for foreign keys: no single table holds every vehicle, so nothing can refer to “any vehicle”. PostgreSQL’s table inheritance looks like a way to get the first layout for free, but its documentation warns that unique constraints, primary keys and foreign keys apply only to single tables, not to their inheritance children, so a primary key on the parent does not stop duplicates in a child table.

The other EER constructs map with the same tools. A union type gets a table with a key of its own, which each united entity set’s table refers to with a unique foreign key. An aggregation’s relationship table becomes the target of a foreign key, as in the monitored assignments of the previous lesson.

The same tables on PostgreSQL

The DDL above is close to portable. The PostgreSQL version differs in one place: ADD COLUMN there accepts UNIQUE directly. The script after it starts a throwaway PostgreSQL 18.6 server, runs the file statement by statement and stops the server again; besides creating the tables, the file counts the keys in PostgreSQL’s catalogue and tries three inserts and deletes that the rules should refuse:

-- The same college tables on PostgreSQL 18.6, then three of their rules put to the test.
SHOW server_version;

CREATE TABLE department (
  dept_code text PRIMARY KEY,
  name      text NOT NULL UNIQUE,
  building  text NOT NULL
);
CREATE TABLE classroom (
  room_id  text PRIMARY KEY,
  building text NOT NULL,
  capacity integer NOT NULL CHECK (capacity > 0)
);
CREATE TABLE instructor (
  instructor_id integer PRIMARY KEY,
  name          text NOT NULL,
  dept_code     text NOT NULL REFERENCES department (dept_code),
  joined_on     date NOT NULL
);
-- PostgreSQL accepts UNIQUE in ADD COLUMN, so no separate index is needed here.
ALTER TABLE department ADD COLUMN head_id integer UNIQUE REFERENCES instructor (instructor_id);
CREATE TABLE student (
  roll_no    text PRIMARY KEY,
  first_name text NOT NULL,
  last_name  text NOT NULL,
  email      text NOT NULL UNIQUE
);
CREATE TABLE student_phone (
  roll_no text NOT NULL REFERENCES student (roll_no) ON DELETE CASCADE,
  phone   text NOT NULL,
  PRIMARY KEY (roll_no, phone)
);
CREATE TABLE course (
  course_code text PRIMARY KEY,
  title       text NOT NULL,
  credits     integer NOT NULL CHECK (credits BETWEEN 1 AND 6),
  dept_code   text NOT NULL REFERENCES department (dept_code)
);
CREATE TABLE course_prerequisite (
  course_code text NOT NULL REFERENCES course (course_code),
  prereq_code text NOT NULL REFERENCES course (course_code),
  PRIMARY KEY (course_code, prereq_code),
  CHECK (course_code <> prereq_code)
);
CREATE TABLE section (
  course_code   text NOT NULL REFERENCES course (course_code) ON DELETE CASCADE,
  term          text NOT NULL,
  section_no    integer NOT NULL,
  instructor_id integer NOT NULL REFERENCES instructor (instructor_id),
  room_id       text REFERENCES classroom (room_id),
  PRIMARY KEY (course_code, term, section_no)
);
CREATE TABLE enrolment (
  roll_no     text NOT NULL REFERENCES student (roll_no),
  course_code text NOT NULL,
  term        text NOT NULL,
  section_no  integer NOT NULL,
  grade       text CHECK (grade IN ('A', 'B', 'C', 'D', 'F')),
  PRIMARY KEY (roll_no, course_code, term, section_no),
  FOREIGN KEY (course_code, term, section_no) REFERENCES section (course_code, term, section_no)
);

-- How many keys of each kind the catalogue holds.
SELECT constraint_type, count(*) AS constraints
FROM information_schema.table_constraints
WHERE table_schema = 'public' AND constraint_type IN ('PRIMARY KEY', 'UNIQUE', 'FOREIGN KEY')
GROUP BY constraint_type
ORDER BY constraint_type;

INSERT INTO department (dept_code, name, building) VALUES ('CSE', 'Computer Science', 'Block A');
INSERT INTO instructor VALUES (7, 'R. Iyer', 'CSE', '2019-07-01');
INSERT INTO course VALUES ('CS101', 'Programming', 4, 'CSE'), ('CS205', 'Databases', 4, 'CSE');
INSERT INTO section VALUES ('CS101', '2026-odd', 1, 7, NULL), ('CS205', '2026-odd', 1, 7, NULL);
INSERT INTO student VALUES ('H24-017', 'Meera', 'Iyer', 'meera.iyer@college.example');
INSERT INTO enrolment VALUES ('H24-017', 'CS205', '2026-odd', 1, NULL);

-- expect: error (WORKS_IN is total: an instructor needs a department)
INSERT INTO instructor (instructor_id, name, joined_on) VALUES (8, 'S. Das', '2024-01-02');

-- expect: error (an enrolment needs an existing section)
INSERT INTO enrolment VALUES ('H24-017', 'CS205', '2026-odd', 2, NULL);

-- expect: error (the cascade from course to section stops at the enrolment)
DELETE FROM course WHERE course_code = 'CS205';

-- CS101 has a section but no enrolments, so the cascade goes through.
DELETE FROM course WHERE course_code = 'CS101';

SELECT course_code, term, section_no FROM section;

"""Throwaway PostgreSQL 18.6 servers for the examples: run SQL files statement by statement and print each statement
with what it returned, or keep several connections open at once to show what concurrent transactions do.

The server is the pinned PostgreSQL 18.6 from the mise installations next to the Python that runs this file. It is
created in a new folder under the run's temporary folder (TMPDIR), listens only on a Unix socket in that folder (no
network port), and is stopped and removed afterwards. learn:verify's run sandbox cannot read the home folder, where
mise keeps PostgreSQL, so these sessions are recorded with LEARN_VERIFY_NETWORK=allow; they read only this folder and
the toolchain, and they open no network connection.

Statements that create or change tables, and long INSERTs, are printed as their first line only (the SQL files on the
page show them in full). A statement whose comment says "expect: error" must fail, and every other statement must
succeed; otherwise the session exits with status 1, so a run that went wrong is never recorded as if it had worked.
"""

import os
import shutil
import subprocess
import sys
import tempfile
from pathlib import Path

PG_VERSION = "18.6"
# .../installs/python/3.14.8/bin/python3 -> .../installs
INSTALLS = Path(sys.executable).resolve().parents[3]
BIN = INSTALLS / "postgres" / PG_VERSION / "bin"


def _statements(sql):
    """Splits a SQL file into statements: each ends with a semicolon at the end of a line. Comment lines stay with
    the statement that follows them."""
    out, lines = [], []
    for line in sql.splitlines():
        if not line.strip() and not lines:
            continue
        lines.append(line)
        if line.rstrip().endswith(";") and not line.lstrip().startswith("--"):
            out.append("\n".join(lines).strip())
            lines = []
    if any(not line.lstrip().startswith("--") and line.strip() for line in lines):
        sys.exit("the file ends with a statement that has no semicolon")
    return out


def _shown(statement):
    """The lines of a statement as the session prints them: a statement that creates or changes a table, or a long
    INSERT, by its first line only (the SQL file on the page shows it in full)."""
    lines = statement.splitlines()
    code = [line for line in lines if not line.lstrip().startswith("--")]
    if len(code) > 1 and code[0].split()[0].upper() in ("CREATE", "ALTER"):
        return [code[0].rstrip() + " …"]
    if len(code) > 1 and code[0].split()[0].upper() == "INSERT":
        return [line for line in lines if line.lstrip().startswith("--")] + [code[0].rstrip() + " …"]
    return lines


def _run(args, **kw):
    return subprocess.run(args, stdout=subprocess.PIPE, stderr=subprocess.STDOUT, text=True, **kw)


def run_file(name):
    """Runs one SQL file on a server of its own."""
    with Server() as pg:
        pg.play(name)


class Server:
    """A throwaway PostgreSQL 18.6 server for sessions that need several open connections at once."""

    def __enter__(self):
        if not (BIN / "postgres").exists():
            sys.exit(f"PostgreSQL {PG_VERSION} was not found next to this Python")
        self.base = Path(tempfile.mkdtemp(prefix="pg"))
        if len(str(self.base / ".s.PGSQL.5432")) > 103:
            sys.exit(f"the socket path in {self.base} is longer than Unix allows (103 bytes): use a shorter TMPDIR")
        self.data = self.base / "data"
        self.env = {**os.environ, "PGHOST": str(self.base), "PGUSER": "learner", "PGDATABASE": "postgres"}
        done = _run([BIN / "initdb", "-D", self.data, "-U", "learner", "--auth=trust", "--no-instructions", "--no-sync",
                     "-E", "UTF8", "--locale=C"], env=self.env)
        if done.returncode != 0:
            sys.exit(f"initdb failed:\n{done.stdout}")
        options = f"-c listen_addresses='' -c unix_socket_directories='{self.base}'"
        options += " -c fsync=off -c full_page_writes=off"  # a throwaway server: no need to survive a power cut
        log = self.base / "server.log"
        done = _run([BIN / "pg_ctl", "-D", self.data, "-o", options, "-l", log, "-w", "start"], env=self.env)
        if done.returncode != 0:
            sys.exit(f"the server did not start:\n{(self.base / 'server.log').read_text()}")
        self.connections = []
        return self

    def __exit__(self, *exc):
        for c in self.connections:
            c.close()
        _run([BIN / "pg_ctl", "-D", self.data, "-m", "immediate", "-w", "stop"], env=self.env)
        shutil.rmtree(self.base, ignore_errors=True)

    def play(self, name, quiet=False):
        """Runs a SQL file statement by statement and prints each with its result; quiet prints only a summary line
        (and any statement that did not do what its comment expects)."""
        failed, count = False, 0
        for statement in _statements(Path(name).read_text()):
            expect_error = "expect: error" in statement
            done = _run([BIN / "psql", "-X", "-v", "VERBOSITY=default", "-c", statement], env=self.env)
            count += 1
            unexpected = (done.returncode != 0) != expect_error
            if not quiet or unexpected:
                print("\n".join(("=> " if i == 0 else "   ") + line for i, line in enumerate(_shown(statement))))
                print(done.stdout.rstrip("\n"))
                print()
            if unexpected:
                failed = True
                print(f"[the statement above {'succeeded' if expect_error else 'failed'}, which was not expected]")
        if quiet and not failed:
            print(f"-- {name}: {count} statements, each as expected")
            print()
        if failed:
            sys.exit(1)

    def sql(self, statement):
        """Runs one statement on a connection of its own and returns what psql printed."""
        return _run([BIN / "psql", "-X", "-c", statement], env=self.env).stdout.rstrip("\n")

    def connect(self, name):
        c = Connection(name, self.env)
        self.connections.append(c)
        return c


class Connection:
    """An open psql session: one connection, so a transaction stays open between statements."""

    MARK = "-- statement done --"

    def __init__(self, name, env):
        self.name = name
        self.p = subprocess.Popen([BIN / "psql", "-X", "-v", "ON_ERROR_STOP=0"], stdin=subprocess.PIPE,
                                  stdout=subprocess.PIPE, stderr=subprocess.STDOUT, text=True, bufsize=1,
                                  env={**env, "PGAPPNAME": name})

    def send(self, statement):
        """Sends a statement without waiting for its result."""
        self.p.stdin.write(f"{statement}\n\\echo '{self.MARK}'\n")
        self.p.stdin.flush()

    def result(self):
        """Waits for the result of the statement sent last."""
        lines = []
        for line in self.p.stdout:
            if line.rstrip("\n") == self.MARK:
                break
            lines.append(line.rstrip("\n"))
        return "\n".join(lines)

    def run(self, statement):
        self.send(statement)
        return self.result()

    def close(self):
        if self.p.poll() is None:
            self.p.stdin.close()
            self.p.wait(timeout=10)

"""Creates the college tables on a throwaway PostgreSQL 18.6 server and prints every statement with its result."""

from pg_session import run_file

run_file("college.sql")

=> -- The same college tables on PostgreSQL 18.6, then three of their rules put to the test.
   SHOW server_version;
 server_version 
----------------
 18.6
(1 row)

=> CREATE TABLE department ( …
CREATE TABLE

=> CREATE TABLE classroom ( …
CREATE TABLE

=> CREATE TABLE instructor ( …
CREATE TABLE

=> -- PostgreSQL accepts UNIQUE in ADD COLUMN, so no separate index is needed here.
   ALTER TABLE department ADD COLUMN head_id integer UNIQUE REFERENCES instructor (instructor_id);
ALTER TABLE

=> CREATE TABLE student ( …
CREATE TABLE

=> CREATE TABLE student_phone ( …
CREATE TABLE

=> CREATE TABLE course ( …
CREATE TABLE

=> CREATE TABLE course_prerequisite ( …
CREATE TABLE

=> CREATE TABLE section ( …
CREATE TABLE

=> CREATE TABLE enrolment ( …
CREATE TABLE

=> -- How many keys of each kind the catalogue holds.
   SELECT constraint_type, count(*) AS constraints
   FROM information_schema.table_constraints
   WHERE table_schema = 'public' AND constraint_type IN ('PRIMARY KEY', 'UNIQUE', 'FOREIGN KEY')
   GROUP BY constraint_type
   ORDER BY constraint_type;
 constraint_type | constraints 
-----------------+-------------
 FOREIGN KEY     |          11
 PRIMARY KEY     |           9
 UNIQUE          |           3
(3 rows)

=> INSERT INTO department (dept_code, name, building) VALUES ('CSE', 'Computer Science', 'Block A');
INSERT 0 1

=> INSERT INTO instructor VALUES (7, 'R. Iyer', 'CSE', '2019-07-01');
INSERT 0 1

=> INSERT INTO course VALUES ('CS101', 'Programming', 4, 'CSE'), ('CS205', 'Databases', 4, 'CSE');
INSERT 0 2

=> INSERT INTO section VALUES ('CS101', '2026-odd', 1, 7, NULL), ('CS205', '2026-odd', 1, 7, NULL);
INSERT 0 2

=> INSERT INTO student VALUES ('H24-017', 'Meera', 'Iyer', 'meera.iyer@college.example');
INSERT 0 1

=> INSERT INTO enrolment VALUES ('H24-017', 'CS205', '2026-odd', 1, NULL);
INSERT 0 1

=> -- expect: error (WORKS_IN is total: an instructor needs a department)
   INSERT INTO instructor (instructor_id, name, joined_on) VALUES (8, 'S. Das', '2024-01-02');
ERROR:  null value in column "dept_code" of relation "instructor" violates not-null constraint
DETAIL:  Failing row contains (8, S. Das, null, 2024-01-02).

=> -- expect: error (an enrolment needs an existing section)
   INSERT INTO enrolment VALUES ('H24-017', 'CS205', '2026-odd', 2, NULL);
ERROR:  insert or update on table "enrolment" violates foreign key constraint "enrolment_course_code_term_section_no_fkey"
DETAIL:  Key (course_code, term, section_no)=(CS205, 2026-odd, 2) is not present in table "section".

=> -- expect: error (the cascade from course to section stops at the enrolment)
   DELETE FROM course WHERE course_code = 'CS205';
ERROR:  update or delete on table "section" violates foreign key constraint "enrolment_course_code_term_section_no_fkey" on table "enrolment"
DETAIL:  Key (course_code, term, section_no)=(CS205, 2026-odd, 1) is still referenced from table "enrolment".

=> -- CS101 has a section but no enrolments, so the cascade goes through.
   DELETE FROM course WHERE course_code = 'CS101';
DELETE 1

=> SELECT course_code, term, section_no FROM section;
 course_code |   term   | section_no 
-------------+----------+------------
 CS205       | 2026-odd |          1
(1 row)

The catalogue holds 9 primary keys, 3 unique constraints (department.name, department.head_id and student.email) and the same 11 foreign keys that SQLite listed. The three refusals show the mapping at work: the NOT NULL foreign key of a total relationship, a two-column foreign key into the weak entity’s table, and a cascade from course to section that stopped at enrolment, whose foreign key has no cascade of its own. The course without enrolments was deleted along with its section.

Counting the tables a diagram needs

The number of tables follows from the rules: one for each entity set, strong or weak, one for each multivalued attribute and one for each M:N or n-ary relationship, while 1:N and 1:1 relationships add none. Conventions differ in one case. A 1:1 relationship with total participation on both sides can keep its two tables or be merged into one, so the minimum depends on whether merging is allowed. The program states both conventions and counts six diagrams:

"""Counts the tables an ER diagram maps to, under two conventions, and names them.

The mapping rules: every strong and every weak entity set gets a table (a weak entity's identifying relationship
needs none); every multivalued attribute gets a table; every M:N and every n-ary relationship gets a table; a 1:N or
1:1 relationship becomes a foreign key in a table that exists anyway. The two conventions differ only for a 1:1
relationship with total participation on both sides: "keep" leaves the two entity tables, "merge" stores both entity
sets in one table.
"""

DIAGRAMS = {
    "A. Instructors work in departments": {
        "entities": ["DEPARTMENT", "INSTRUCTOR"],
        "relationships": [("WORKS_IN", ("INSTRUCTOR", "DEPARTMENT"), "N:1", ("total", "partial"))],
    },
    "B. Students enrol in courses": {
        "entities": ["STUDENT", "COURSE"],
        "relationships": [("ENROLS", ("STUDENT", "COURSE"), "M:N", ("partial", "partial"))],
    },
    "C. Every citizen holds exactly one ID card": {
        "entities": ["CITIZEN", "ID_CARD"],
        "relationships": [("HOLDS", ("CITIZEN", "ID_CARD"), "1:1", ("total", "total"))],
    },
    "D. Employees with phones and dependants": {
        "entities": ["EMPLOYEE"],
        "weak": {"DEPENDANT": "EMPLOYEE"},
        "multivalued": [("EMPLOYEE", "phone")],
    },
    "E. The college of this lesson": {
        "entities": ["DEPARTMENT", "INSTRUCTOR", "STUDENT", "COURSE", "CLASSROOM"],
        "weak": {"SECTION": "COURSE"},
        "multivalued": [("STUDENT", "phone")],
        "relationships": [
            ("WORKS_IN", ("INSTRUCTOR", "DEPARTMENT"), "N:1", ("total", "partial")),
            ("HEADS", ("INSTRUCTOR", "DEPARTMENT"), "1:1", ("partial", "partial")),
            ("OFFERS", ("DEPARTMENT", "COURSE"), "1:N", ("partial", "total")),
            ("REQUIRES", ("COURSE", "COURSE"), "M:N", ("partial", "partial")),
            ("TEACHES", ("INSTRUCTOR", "SECTION"), "1:N", ("partial", "total")),
            ("HELD_IN", ("CLASSROOM", "SECTION"), "1:N", ("partial", "partial")),
            ("ENROLS", ("STUDENT", "SECTION"), "M:N", ("partial", "partial")),
        ],
    },
    "F. A ternary supply": {
        "entities": ["SUPPLIER", "PART", "PROJECT"],
        "relationships": [("SUPPLIES", ("SUPPLIER", "PART", "PROJECT"), "n-ary", None)],
    },
}


def tables(diagram, convention):
    """The tables of a diagram: entity tables (merged where the convention says so), then the extra ones."""
    groups = {e: [e] for e in [*diagram["entities"], *diagram.get("weak", {})]}
    extra = [f"{owner}_{attr}".lower() for owner, attr in diagram.get("multivalued", [])]
    for name, members, ratio, participation in diagram.get("relationships", []):
        if ratio in ("M:N", "n-ary"):
            extra.append(name.lower())
        elif ratio == "1:1" and convention == "merge" and participation == ("total", "total"):
            a, b = members
            merged = groups.pop(a) + groups.pop(b)
            groups["+".join(merged)] = merged
    return ["+".join(g).lower() for g in groups.values()] + extra


def count(names):
    return f"{len(names)} table{'' if len(names) == 1 else 's'}: {', '.join(names)}"


for title, diagram in DIAGRAMS.items():
    keep, merge = tables(diagram, "keep"), tables(diagram, "merge")
    print(title)
    print(f"  keep:  {count(keep)}")
    print(f"  merge: {count(merge) if merge != keep else 'the same'}")

A. Instructors work in departments
  keep:  2 tables: department, instructor
  merge: the same
B. Students enrol in courses
  keep:  3 tables: student, course, enrols
  merge: the same
C. Every citizen holds exactly one ID card
  keep:  2 tables: citizen, id_card
  merge: 1 table: citizen+id_card
D. Employees with phones and dependants
  keep:  3 tables: employee, dependant, employee_phone
  merge: the same
E. The college of this lesson
  keep:  9 tables: department, instructor, student, course, classroom, section, student_phone, requires, enrols
  merge: the same
F. A ternary supply
  keep:  4 tables: supplier, part, project, supplies
  merge: the same

Diagram E is the college of this lesson, and its nine tables are the nine that college.sql created. Only diagram C, where every citizen holds exactly one card and every card belongs to one citizen, changes with the convention.

Interview questions

In a 1:N relationship, which table gets the foreign key, and why? The table on the N side. Each row there is related to at most one row on the 1 side, so a single column can hold the key of its partner, while a row on the 1 side would need a list, which one column cannot hold. The relationship’s attributes move to the N side for the same reason. When the N side’s participation is total, the foreign key is NOT NULL; when it is partial, the column is nullable. A table between the two is never needed for 1:N; it would only add a join.

Key takeaways

• Map entities before the relationships that refer to them: strong entity sets, then weak ones keyed by the owner’s key plus the partial key.
• 1:N puts a foreign key on the N side; 1:1 a UNIQUE foreign key, preferably on the side with total participation; M:N gets a table keyed by both foreign keys.
• Multivalued attributes and n-ary relationships get tables; a 1 in an n-ary relationship takes that participant out of the key.
• Specialisation has four layouts that trade NULLs, joins and enforceable constraints; PostgreSQL inheritance does not carry keys to child tables.
• Total participation becomes NOT NULL, existence dependency becomes ON DELETE CASCADE, and a cascade stops at any reference without one.
• Table counts follow from the rules, apart from the merge convention for 1:1 relationships that are total on both sides: state the convention, then count.