FEN 2015-09-01 1

Introduction to the database field:

Transforming ER models torelational schemas

Seminar:Introduction to relational databases

Transformation

2

Design of Relational Tables

• The conceptual model must be transformed to an implementation model,

• e.g. relational schemas.

• This can be done using a seven steps algorithm

• Does not always yield an optimal design, but provides a good starting point for the final design of tables

FEN 2015-09-01

Transformation

3

Design of Relational Tables

Step 1: For each regular entity create a table:• For composite attributes only the components are included.• Multi-value attributes are not included (they are considered in step 6).• Choose a primary key.

FEN 2015-09-01

4

Design of Relational Tables

Step 2: For each weak entity create a table• All attributes from the weak entity are included.• The primary key from the owner is included as foreign key.• The primary key is composed by the owner’s primary key and the partial key.

FEN 2015-09-01

5

Design of Relational Tables

Step 3: For each (binary) 1:1-relationinclude the primary key of one participant as foreign key in the other

• Any attributes on the relation are included with the key.• If possible, include on a side with total participation.

FEN 2015-09-01

6

Design of Relational Tables

Step 4: For each (binary) 1:n relationinclude primary key of 1-side as foreign key on n-side

• Any attributes on the relation are included with the key on the n-side.

FEN 2015-09-01

7

Design of Relational Tables

Step 5: For each (binary) n:m relation create table with the primary keys of participatingentities as foreign keys

• Any attribute on the relation is included in the new table.

• Primary key is composed of the foreign keys.• This may also be applied to binary 1:1- and 1:n

relations – in particularly if there are relatively few instants of the relation.

FEN 2015-09-01

8

Design of Relational Tables

Step 6: For each multi value attribute create a table with primary key of the entity as foreign key and the multi value attribute

• The primary key of the new table is composed of the foreign key and the multi value attribute.

FEN 2015-09-01

9

Design of Relational Tables

Step 7: For each n-ary (n>2) relation create a table with the primary keys of all participating entity as foreign keys

• Any attribute on the relation is included.• The primary key is composed of the included foreign keys.

FEN 2015-09-01

10

Design of Relational Tables

Result:

FEN 2015-09-01

11

Example/Exercise (Elmasri)

FEN 2015-09-01

Use the seven steps algorithm to design tables for this database.