How do you turn an ER diagram into a set of CREATE TABLE statements?
There are four rules, and they are the same for every diagram.
An entity becomes a table. An attribute becomes a column. A relationship becomes a foreign key, and where it goes depends on the shape of the relationship. And the cardinality tells you whether that foreign key has to be there and whether it can be repeated.
-- Entities become tables.
CREATE TABLE authors (
id INTEGER PRIMARY KEY,
name VARCHAR(160) NOT NULL
);
CREATE TABLE books (
id INTEGER PRIMARY KEY,
title VARCHAR(200) NOT NULL,
author_id INTEGER NOT NULL,
FOREIGN KEY (author_id) REFERENCES authors (id)
);
So here are the four shapes of relationship and where the foreign key goes.
A one to many relationship, such as one author and many books, puts the foreign key in the many side. The books table holds the author number, and it is not null, because every book has one author.
A many to many relationship, such as authors and books, needs a table of its own for the link. That link table has a column for each side, and the pair of them is the key.
CREATE TABLE book_authors (
book_id INTEGER NOT NULL,
author_id INTEGER NOT NULL,
PRIMARY KEY (book_id, author_id),
FOREIGN KEY (book_id) REFERENCES books (id),
FOREIGN KEY (author_id) REFERENCES authors (id)
);
A one to one relationship puts the foreign key on either side, and it gets a unique rule so that the other row cannot point at the same one.
And an optional relationship, where one side may be missing, means the foreign key is allowed to be null.
So the rule to remember is this. The link goes on the many side, and a many to many relationship always needs a table of its own.
The next question in this topic asks for a one to many set of tables.