The Relational Database Design ER and EERtoRelational Mapping

The Relational Database Design ER- and EER-to-Relational Mapping

ER to Relational Mapping • Mapping of regular entity types • Multivalued attributes • Mapping of relationship – Binary relationship – N-ary relationship • Mapping of weak entity types • Mapping of specialization and generalization

FIGURE 7. 1: The ER conceptual schema diagram for the COMPANY database.

Regular Entity Types • Mapping – Create a new relation. – Include all the (simple) attributes of the entity. – Choose one of the key attribute as primary key.

Multivalued Attributes • Mapping – Create a new relation R for each multivalued A. – Include an attribute corresponding to A and the primary key attribute K (as a foreign key in R) – The primary key of R is K and A.

Binary Relationships • Binary M: N relationships – Create a new relation S. – Include as foreign keys the primary keys of the participating entities. The primary key of S is the combination of these. – Include all the (simple) attributes of the relationship type.

Binary Relationships • Binary 1: 1 relationships – Choose the entity with total participation. – Include the primary key of the other relation as a foreign key. – Include all the (simple) attributes of the relationship.

Binary Relationships • Binary 1: N relationships – Choose the entity at the N side. – Include the primary key of the other relation as a foreign key. – Include all the (simple) attributes of the relationship.

N-ary Relationships • Mapping – Create a new relation S. – Include as foreign keys the primary keys of the participating entities. The primary key of S is the combination of these. – Include all the (simple) attributes of the relationship type.

Weak Entity Types • Mapping – Create a relation. – Include all the (simple) attributes of the weak entity. – Include as a foreign key the primary key of the owner entity. – The primary key is the combination of the primary key of the owner and the partial key of the weak entity.

FIGURE 7. 2: Result of mapping the COMPANY ER schema into a relational schema.

FIGURE 7. 3: Mapping the n-ary relationship type SUPPLY from Figure 4. 11 a.

Specialization and Generalization • Mapping – Create a relation for the superclass (similar to mapping a regular entity) – Create a relation for each subclass, include primary key of the superclass (as its primary key) and local attributes.

FIGURE 7. 4: Options for mapping specialization or generalization.