ER and EER to Relational Mapping

Flashcards covering key vocabulary and concepts from a lecture on mapping conceptual designs into logical designs, specifically focusing on ER and EER to relational mapping algorithms.

Relational Database Design

The process of designing a relational database schema based on a conceptual schema design, using ER-to-Relational Mapping and EER-to-Relational Mapping.

ER-to-Relational Mapping

A seven-step algorithm to convert the basic ER model constructs into relations in a relational database design.

Regular Entity Type Mapping

For each regular entity type, create a relation R that includes all the simple attributes of E; called entity relations, where each tuple represents an entity instance.

Weak Entity Type Mapping

For each weak entity type, create a relation R and include all simple attributes of the entity type as attributes of R; include primary key attribute of owner as foreign key attributes of R.

Binary 1:1 Relationship Type Mapping

Approaches include foreign key approach, merged relationship approach, and cross-reference or relationship relation approach.

Binary 1:N Relationship Type Mapping

Identify relation that represents participating entity type at the N-side of the relationship type (call it S); include primary key of other entity type as foreign key in S; include simple attributes of 1:N relationship type as attributes of S.

Binary M:N Relationship Type Mapping

Create a new relation S; include primary key of participating entity types as foreign key attributes in S; include any simple attributes of M:N relationship type.

Multivalued Attributes Mapping

Create a new relation (R); Primary key of R is the combination of A (multivalued attribute) and K (primary key of R); If the multivalued attribute is composite, include its simple components.

N-ary Relationship Types Mapping

Create a new relation S to represent R; Include primary keys of participating entity types as foreign keys; Include any simple attributes as attributes.

EER Model Constructs Mapping

Extending ER-to-relational mapping algorithm to include specialization, generalization, and union types.

Specialization or Generalization Mapping Options

Option 8A (Multiple relations - superclass and subclasses), Option 8B (Multiple relations - subclass relations only), Option 8C (Single relation with one type attribute), Option 8D (Single relation with multiple type attributes).

Mapping of Union Types (Categories)

Defining superclasses have different keys; specify a new key attribute (Surrogate key).

Option 8A (Multiple relations - superclass and subclasses)

Create a relation for the superclass entity type; create a relation for each subclass; superclass key becomes primary key and foreign key to subclass relations.

Option 8B (Multiple relations - subclass relations only)

Create a relation for each subclass; include attributes of superclass in each subclass relation; superclass key becomes primary key of subclass relations.

Option 8C (Single relation with one type attribute)

Create a single relation including all attributes of the superclass and subclasses; include a type attribute to indicate the subclass to which each tuple belongs.

Option 8D (Single relation with multiple type attributes)

Create a single relation including all attributes of the superclass and subclasses; include a Boolean type attribute for each subclass

Surrogate Key

A system-generated identifier for an entity that is not derived