SQL for Data Analytics · Drawing diagrams
Drawing diagrams
One-to-Many ER Diagrams4 min
In the video I write the CREATE TABLE statements for class and instructor. class has an instructor_id column that points at instructor, and that becomes a FOREIGN KEY:
CREATE TABLE instructor (
id INT,
first_name VARCHAR(255) NOT NULL,
last_name VARCHAR(255) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE class (
id INT,
title VARCHAR(255) NOT NULL,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
instructor_id INT,
PRIMARY KEY (id),
FOREIGN KEY (instructor_id) REFERENCES instructor(id)
);
In the video, instructor also has an annual_salary column. The college database on this site leaves it out, so it's left out here too.
instructor_id has the same type as the id it points at. It's the only column in class that can be NULL. instructor comes first in the script, because the foreign key in class can only point at a table that's already there.
Many-to-Many ER Diagrams5 min
In the video, enrollment is the joining table between student and class. It has no ID of its own. Its primary key is the two foreign keys together, a composite primary key:
CREATE TABLE enrollment (
student_id INT,
class_id INT,
enrollment_time DATETIME NOT NULL,
gpa DECIMAL(2, 1),
PRIMARY KEY (student_id, class_id),
FOREIGN KEY (student_id) REFERENCES student(id),
FOREIGN KEY (class_id) REFERENCES class(id)
);
student and class both have to exist before enrollment is created, because enrollment has a foreign key to each of them.
Creating Entity Relationship Diagrams6 min
A note on this video
In the video I draw the diagram in drawSQL. On this site you draw in the box below, which works the same way. Add table makes a new table, and you type its name at the top. + column adds a column, with its name and its type. The key icon at the left of a column makes it part of the primary key. NN makes a column NOT NULL, and UQ makes it UNIQUE. To draw a line, drag from the circle at the right end of a column to a column in another table. A new line has "one" at both ends. Click an end to switch it between one and many. To delete a line, click it and press Delete. You don't need an account, and your diagram is saved in this browser.
Here's the blog from the video, to try the box out. It has users, posts, and comments. A user can write many posts and many comments, and a post can have many comments.
Draw the blog database from the video: users (id, username, email, password), posts (id, title, body, user_id), and comments (id, body, user_id, post_id). Every id is an INT and the table's primary key.
At the end of the video I read each line both ways to check it. A comment is on one post, and a post can have many comments.
A restaurant booking service keeps its guests and their reservations in these two tables.
Guests
| ID | FirstName | LastName | Phone |
|---|---|---|---|
| 1 | Nathaniel | Mandrake | (111) 222 3333 |
| 2 | David | Martinez | (333) 444 5555 |
| 3 | Valentine | Wiggin | (666) 777 8888 |
Reservations
| ID | GuestID | ReservationDateTime |
|---|---|---|
| 1 | 3 | 2024-01-02 19:00:00 |
| 2 | 1 | 2024-01-03 09:30:00 |
| 3 | 2 | 2024-01-03 10:00:00 |
| 4 | 3 | 2024-01-04 17:30:00 |
Draw the Guests and Reservations tables, with a type for every column, a primary key in each, and the line between them.
Read the line in your diagram both ways. What does each reading say about guests and reservations?
How I'd answer it
A guest can have many reservations. Valentine Wiggin, guest 3, already has two.
A reservation belongs to one guest. Each reservation has one spot for a GuestID.
The crow's foot goes at Reservations, the table that holds the foreign key.
An author can write many books, and a book can have more than one author. The tables have these columns:
Authors:AuthorIDandAuthorNameBooks:BookIDandTitleBooksAuthors, the joining table:BookIDandAuthorID
| BookID | AuthorID |
|---|---|
| 101 | 1 |
| 102 | 2 |
| 103 | 2 |
| 103 | 1 |
| 104 | 3 |
| 105 | 3 |
Draw Authors, Books, and the joining table BooksAuthors, with the lines between them. BooksAuthors has no ID of its own. Its primary key is BookID and AuthorID together.
There's no line between Authors and Books in your diagram. Why not, if an author can write many books?
How I'd answer it
A line between Authors and Books would need a crow's foot at both ends, and a database can't store that directly. One of the two tables would need a list of IDs in a single column.
The joining table holds those connections instead. Each of the two lines in the diagram is a one-to-many: one book has many rows in BooksAuthors, and one author has many rows in BooksAuthors. Together, they make the many-to-many between Authors and Books.