Logical and Physical Models
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
Links to an external site.
Content
00:00
Links to an external site. Lecture Content
00:45
Links to an external site. Definition of Logical Model
05:46
Links to an external site. Definition of Physical Model
07:06
Links to an external site. The Simplified Approach (Log-Phys Model) Used Here
09:27
Links to an external site. Log-Phys Model, Step 1, Tables
11:20
Links to an external site. Log-Phys Model, Step 2, Single-Valued Attributes
11:57
Links to an external site. Naming Convention
13:42
Links to an external site. Log-Phys Model, Step 3, Multi-Valued Attributes
14:17
Links to an external site. Log-Phys Model, Step 4, Column Types
31:55
Links to an external site. Log-Phys Model, Step 5, Column Constraints
Logical and physical models, part 2
Links to an external site.
Content
00:00
Links to an external site. Log-Phys Model, Step 6, PK for Strong Entities
17:00
Links to an external site. Log-Phys Model, Step 7, One-To-One and One-To-Many Relations
Logical and physical models, part 3
Links to an external site.
Content
00:00
Links to an external site. Log-Phys Model, Step 8, Many-To_Many Relations
12:37
Links to an external site. Log-Phys Model, Step 9, PK and FK for Multi-Valued Attribute Tables
20:17
Links to an external site. Log-Phys Model, Step 10, Normalization
22:33
Links to an external site. Log-Phys Model, Step 11, Verify That All Operations Can Be Performed
The slides are here, log-phys-mod.pdf Download log-phys-mod.pdf
Models
Here is the model created in the videos above, VisitDb.asta Download VisitDb.asta, VisitDb.png Download VisitDb.png
Here is the same model created with Lucidchart, visitdb-lucidchart.pdf Download 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 Download 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.