This page illustrates how to create a database from a conceptual model.

Reading

Chapter 9.1 in Fundamentals of Database Systems describes a procedure similar (but not identical) to the one described here. If you wish to dig deeper there is relevant information also in chapters 14 and 15.
Neither logical nor physical modelling is covered in Database System Concepts, but there is some related information in sections 7.9 and 7.10.

Videos

The videos define the primary key type as SERIAL. That is fine, but a better alternative is INT GENERATED ALWAYS AS IDENTITY. Both alternatives have the same purpose, that the values of the primary key column will be given generated values, which are guaranteed to be unique, instead of having to be assigned manually. The difference is that INT GENERATED ALWAYS AS IDENTITY is standard SQL, while SERIAL is a postgres extension. Also, INT GENERATED ALWAYS AS IDENTITY prohibits accidentally assigning a value manually.

Logical and physical models, part 1
Content

00:00 Lecture Content
00:45 Definition of Logical Model
05:46 Definition of Physical Model
07:06 The Simplified Approach (Log-Phys Model) Used Here
09:27 Log-Phys Model, Step 1, Tables
11:20 Log-Phys Model, Step 2, Single-Valued Attributes
11:57 Naming Convention
13:42 Log-Phys Model, Step 3, Multi-Valued Attributes
14:17 Log-Phys Model, Step 4, Column Types
33:55 Log-Phys Model, Step 5, Column Constraints

Logical and physical models, part 2
Content

00:00 Log-Phys Model, Step 6, PK for Strong Entities
17:00 Log-Phys Model, Step 7, One-To-One and One-To-Many Relations

Logical and physical models, part 3
Content

00:00 Log-Phys Model, Step 8, Many-To_Many Relations
12:37 Log-Phys Model, Step 9, PK and FK for Multi-Valued Attribute Tables
20:17 Log-Phys Model, Step 10, Normalization
22:33 Log-Phys Model, Step 11, Verify That All Operations Can Be Performed

The slides are here, log-phys-mod.pdf

Models

Here is the model created in the videos above, VisitDb.asta, VisitDb.png

Here is the same model created with Lucidchart, visitdb-lucidchart.pdf. The purpose is to illustrate how to use Lucidchart, and also to show some alternative ways of creating the same database. Note that all surrogate keys have been renamed to <table name>_id in this model. This has the advantage that PK and FK have the same name, and that natural joins (joins based on column name) are possible. The disadvantage is that there is duplicated information, the table name is present in both table name and column name.

Here is an SQL script which creates the database described in the Lucidchart model above, visitdb.sql. Note that there are some PostgresSQL specific details in the script. Also note that the script does not create the database itself, it just creates the tables. An empty database must be created before the script is executed.