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.
- Slides: 14