[Database] Relational Schema Design ER to Relational Mapping

ER schema

Mapping Result : Relational schema

What to Map?

• (Regular) Entity Types
• Weak Entity Types
• Binary 1 : M Relationships
• Binary M : N Relationships
• Binary 1 : 1 Relationships
• Recursive Relationships
• Multi-Valued Attributes
• Superclass/Subclass : IS-A Relationship

Mapping Guidelines

• Need to satisfy the following constraints;
  • 1 : M, M : N, 1 : 1 relationship
  • Total / Partial participation
  • Key Integrity
  • Referential Integrity
• Avoid many null values.
• Consider performance (= retrieval time).
• Avoid redundancy

Entity Types

• For regular entity type E, create a relation R that includes all the simple attributes of E.
• Choose one of the keys of E as primary key (PK) for R.
• If the chosen key of E is composite, the set of simple attributes that form it will together form the PK of R.
• Each entity in E corresponds a row (tuple) in R
• Each attribute in E corresponds a column (attribute) in R

Weak Entity Types

• For weak entity type W, create a relation R and include all the attributes of W.
• Find W’s owner entity type E;
• Include as foreign key (FK) of R the PK of the owner E.
• PK of R is {PK of owner E, partial key of R}

1 : M Relationship

Total/Total

• Case 1 : Both sides are ‘Total’:
  • For 1 : M relationship R, create a relation S representing the entity type at the “M-side” of the relationship.
• Include as FK in S the PK of the relation T that represents the entity type at the “1 – side”.
  • : Why? This is because we must satisfy key integrity;
• Include any simple attributes of the 1 : M relationship as attributes of S.

Partial/Partial

• Case 2 : Both sides are ‘Partial’
  • For 1 : M relationship R, create a new relation S to represent R.
• Include as FK in S the PKs of each relations that represent the participating entity types;
  • : Why? This is because we need to avoid many null values.)
• Also, include any simple attributes of the relationship type R as attributes of S.

M : N Relationship

• For M : N relationship R, create a new relation S to represent R.
• Include as FK in S the PKs of each relations that represent the participating entity types;
• The combination of each FKs will form the PK of S.
• Also, include any simple attributes of the M : N relationship type as attributes of S.

1 : 1 Relationship

For 1 : 1 relationship R, create the relations S and T that correspond to the entity types participating in R.

• Case 1. Foreign Key : Two Relations
  • The one side (say, S) is total and the other side (say, T) is partial.
  • Include as FK in relation S the PK of relation T.
• Case 2. Merge relations : Single Relation
  • Both sides (say, S and T) are total.
  • Merge two relations S and T and their relationship into a single relation.
• Case 3. Create a new relation : Three Relations
  • Both sides (say, S and T) are partial.
  • Create a new relation R by including the PKs of the relations S and T.

Recursive

1 : M Relationship

1 : 1 Relationship

M : N Relationship

Recursive Relationship: Exercise

Multi-Valued Attributes

• For each multi-valued attribute A, create a new relation R.
• R includes the attribute A, plus the PK K of the relation S that represents the entity type including A. Then, remove A from the relation S.
• The PK of R is {A, K} where K is FK.
• If the multi-valued attribute is composite, we include its simple components.