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, 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, 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, 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.