From ER Diagram to Schema

There is a mechanical mapping, and once you know it the translation is quick. Each entity becomes a table with its attributes as columns and its identifier as the primary key. Each relationship becomes either a foreign key on one of the two tables, or a separate link table, and which one depends on the cardinality and on whether the relationship carries attributes of its own.

-- Entities become tables.
CREATE TABLE authors (
  id INTEGER PRIMARY KEY,
  name VARCHAR(160) NOT NULL,
  born INTEGER
);

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  published_year INTEGER,
  price_cents INTEGER NOT NULL
);

-- A many-to-many relationship with its own attribute becomes a link table.
CREATE TABLE book_authors (
  book_id INTEGER NOT NULL REFERENCES books (id) ON DELETE CASCADE,
  author_id INTEGER NOT NULL REFERENCES authors (id) ON DELETE CASCADE,
  role VARCHAR(40) NOT NULL DEFAULT 'author',
  PRIMARY KEY (book_id, author_id)
);

INSERT INTO authors (id, name, born) VALUES
  (1, 'Ursula K. Le Guin', 1929),
  (2, 'Italo Calvino', 1923);

INSERT INTO books (id, title, published_year, price_cents) VALUES
  (1, 'A Wizard of Earthsea', 1968, 1200),
  (2, 'Invisible Cities',     1972, 990);

INSERT INTO book_authors (book_id, author_id, role) VALUES
  (1, 1, 'author'),
  (2, 2, 'author');

SELECT b.title, a.name AS author, ba.role
FROM book_authors AS ba
JOIN books AS b ON b.id = ba.book_id
JOIN authors AS a ON a.id = ba.author_id
ORDER BY b.title;

Two rows come back: A Wizard of Earthsea by Ursula K. Le Guin and Invisible Cities by Italo Calvino.

The three decisions that matter. First, a one-to-many relationship — one author, many books — becomes a foreign key on the many side. The author id goes into the books table, not the other way round, and that placement is what makes the relationship one-to-many rather than many-to-many.

Second, a many-to-many relationship becomes a link table whose composite primary key is the pair of foreign keys. That primary key is what stops the same book and author being linked twice. And if the relationship has attributes of its own — the role here, which could be 'author' or 'translator' — those attributes live in the link table, because they describe the pairing and not either entity.

Third, the cardinality becomes a constraint. A mandatory participation on the many side means the foreign key is NOT NULL. An optional one means it may be NULL. A one-to-one relationship becomes a unique foreign key, so the second row cannot point at the same parent.

One department, many employees. The foreign key goes on the many side, and whether it is NOT NULL depends on whether every employee must belong to a department.

CREATE TABLE departments (
  id INTEGER PRIMARY KEY,
  name VARCHAR(60) NOT NULL,
  location VARCHAR(80)
);

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  title VARCHAR(80) NOT NULL,
  department_id INTEGER REFERENCES departments (id),
  hired_on TEXT NOT NULL DEFAULT (datetime('now'))
);

INSERT INTO departments (id, name, location) VALUES
  (1, 'Research',    'Cambridge'),
  (2, 'Engineering', 'London');

INSERT INTO employees (id, name, title, department_id) VALUES
  (1, 'Ada Lovelace',   'Head of Research', 1),
  (2, 'Grace Hopper',   'Director',         2),
  (3, 'Alan Turing',    'Researcher',       1),
  (4, 'Joan Clarke',    'Contractor',       NULL);

SELECT e.name, e.title, d.name AS department
FROM employees AS e
LEFT JOIN departments AS d ON d.id = e.department_id
ORDER BY e.id;

Four rows come back. The first three have a department, and Joan Clarke shows a NULL department because her department_id is NULL and the LEFT JOIN preserves her row.

Three decisions in that schema are worth naming. The foreign key sits on employees, the many side, because that is what makes it one-to-many — putting it on departments would say one employee per department. The column is nullable because a contractor might legitimately have no department; if every employee must have one, declare it NOT NULL. And the query uses a LEFT JOIN rather than an inner one precisely because that column is nullable — an inner join would silently drop Joan.

One thing the diagram does not tell you and you have to decide: what happens when a department is deleted. The default is that the delete is refused, because employees still reference it. If a department is really just a grouping and losing it should be harmless, you would soft-delete it instead. If the employees genuinely do not outlive their department, ON DELETE CASCADE expresses that — but it is a decision about the business, not about the diagram.