Transcript 9. Konvertering fra ER-diagram til relationer / tabeller

Conversion from ER diagram to
relational model
ER is a visual model
The relational model is a matemathical model
Conversion from ER diagram to
relational model
1
Database development as part of
systems development
• Analysis phase
– Draw an ER-diagram
• Design phase
– Convert ER-diagram to relational model.
– Check if the relations are sufficiently
normalized.
• Realization phase
– Create tables in a DBMS.
Conversion from ER diagram to
relational model
2
Procedure for conversion from
ER to relational model
• Strong entity types
– have their own relation with all the singlevalued attributes.
– composite attributes: Simple attributes
(“leaves”) in the relations.
– primary key transferred from ER to the relation.
Conversion from ER diagram to
relational model
3
Converting weak entities
• Weak entity types
– have their own relation with all single-valued
attributes
– partial key transferred from ER to the relation
– foreign key to referring to the “strong” entity
(identifying relationship).
– primary key = partial key + foreign key to
“strong” entity.
Conversion from ER diagram to
relational model
4
Binary relationships 1:1
• Binary 1:1 relationships (rare)
– data from the relationship (if any) integrated
into one of the related entities [your choice].
– one of the related relations gets a foreign key to
the other [your choice: easy to find a “way”
from on to the other, but not vice versa]
– 1:1 relations are rare in ER-diagrams {they are
usually modeled as a single entity }
Conversion from ER diagram to
relational model
5
Binary relationships 1:N
• Binary relationships with cardinality 1:N
– data from the relationship (if any) integrated
into the relation with the “N”
– The “N” relation has a foreign key to the “1”
relation.
Conversion from ER diagram to
relational model
6
Binary relationships N:M
• Binary relationships with cardinality N:M
– Own relation with 2 foreign keys (1 for each of
the relation entities)
– Date (like timestamps, etc.) on the relationships
goes into the relation.
Conversion from ER diagram to
relational model
7
Multi valued attributes
• Multi valued attributes
– Own relation
– Foreign key to the “owning” relation.
Conversion from ER diagram to
relational model
8
N-ary relationships
• Relationships with N (more than 2) entities
– Own relation with N foreign keys (1 for each
related entity)
Conversion from ER diagram to
relational model
9
Inheritance, page 295
• ER-modeling allows inheritance between
entity types.
• Problem
– No inheritance in the relational model.
• Solution
– Try to avoid inheritance in ER.
Conversion from ER diagram to
relational model
10