Mapping the ER Model to Relational DBs

last updated 20-sep-12

Database Design

Goal of design is to generate a formal specification of the database schema

Methodology:

  1. Use E-R model to get a high-level graphical view of essential components of enterprise and how they are related
  2. Then convert E-R diagram to SQL Data Definition Language (DDL), or whatever database model you are using

E-R Model is not SQL based.

The E-R Model: The enterprise the database represents is viewed as set of

 

 


Representation of Entity Type in Relational Model

Mapping #1: Each entity type always corresponds to a relation

Mapping #2: The attributes of a relation contains at least the attributes of an entity type

Problem: Recall that the entity type can have multi- valued attributes.

Possible solution: Use several rows to represent a single entity

Problems with this solution:

so, the resulting relation must be further transformed--> Normalization

 

 


Relationship mapping

Relationship: connects two or more entities into an association/relationship

Relationship Type: set of similar relationships

Distinction -

Entity types and most relationship types in the E-R model are mapped to relations (relational model)

Relationship Types may also have attributes in the E-R model. 

Relationships tend to be verbs; attributes of relationships are again nouns


Roles

Problem: recursive relationships can relate elements of same entity type

e.g., the ReportsTo relationship type relates two elements of the Employee entity type:

We do not have distinct names for the roles

It is not clear who reports to whom

Solution: the role name of relationship type need not be same as name of entity type from which participants are drawn

Mapping #7: If the cardinality is 1-many or 1-1 of a recursive relationship, then a second attribute of the same domain as the key may be added to the entity relation to establish the relationship. Attributes of the relationship can also be added to the entity relation, but may be a good reason to create a separate relation with the attributes and keys of the entities.

Mapping #8: for many-many recursive relationships, you create a relation including the attributes of the relation but with the primary keys of the entity included twice, one for each role.


Schema of a Relationship Type

Contains the following features:

Role names, Ri, and their corresponding entity sets. Roles must be single valued (the number of roles is called its degree)

Attribute names, Aj, and their corresponding domains. Attributes in the E-R model may be set or multi-valued.

Key: Minimum set of roles and attributes that uniquely identify a relationship

Relationship: <e1, en; a1, ak>

 

 


Representation of Relationship Type in Relational Model

Attributes of corresponding relation are

Ex.:


Representation in Relational Model

Candidate key of corresponding table = candidate key of relation

Except when there are set/multi- valued attributes

Example: Teaching (CrsCode, SectNo, Id, RoomNo, TA)

Representation in SQL notes

Each role of relationship type produces a foreign key in corresponding relation.

The foreign key references the table corresponding to the entity type from which role values are drawn.

 


Examples

CREATE TABLE WorksIn (
   Since DATE,       -- attribute
   Status CHAR (10), -- attribute
   ProfId INTEGER,   -- role (key of Professor)
   DeptId CHAR (4),  -- role (key of Department)
   PRIMARY KEY (ProfId), -- since a professor works in at most one department
   FOREIGN KEY (ProfId) REFERENCES Professor (Id),
   FOREIGN KEY (DeptId) REFERENCES Department 
)

 

 

CREATE TABLE Sold (
  Price INTEGER,      -- attribute
  Date DATE,          -- attribute
  ProjId INTEGER,     -- role
  SupplierId INTEGER, -- role
  PartNumber INTEGER, -- role
  PRIMARY KEY (ProjId, SupplierId, PartNumber, Date),
  FOREIGN KEY (ProjId) REFERENCES Project (Id),
  FOREIGN KEY (SupplierId) REFERENCES Supplier (Id),
  FOREIGN KEY (PartNumber) REFERENCES Part (Number) 
)

 


Key Constraint (special case)

If, for a particular participant entity type, each entity participates in at most one relationship, its corresponding role is a foreign key relationship type.

The representation in an E-R diagram is an arrow.

Relational model representation: the key of the relation corresponding to the entity type is the key of the relation corresponding to the relationship type


Type Hierarchies and Relational Model

Supertypes and subtypes can be represented as separate relations.

We need a way of identifying each subtype entity with its (unique) related supertype entity

 


Type Hierarchies and Relational Model

Redundancy is eliminated if  the IsA hierarchy is not disjoint

 


Participation Constraint

If every entity participates in at least one relationship, a participation constraint holds:

Representation

Inclusion dependency: Every professor works in at least one dept.

in relational model: (easy)

  • Professor (Id) references WorksIn (ProfId)
  • in SQL:

  • Special case: Every professor works in exactly one dept. (easy)

    FOREIGN KEY Id REFERENCES WorksIn (ProfId)

  • General case (not so easy):

    CREATE ASSERTION ProfsInDepts 
       CHECK ( NOT EXISTS (
            SELECT * FROM Professor P
               WHERE NOT EXISTS (
                  SELECT * FROM WorksIn W
                    WHERE P.Id = W.ProfId ) ) )

     

     

     

     


    Participation Constraint in Relational Model

    Example (here you cant use the foreign key in Professor)


    Participation and Key Constraint

    If every entity participates in exactly one relationship, both a participation and a key constraint hold:

    In SQL:

    CREATE TABLE Professor (
       Id INTEGER, 
       
       PRIMARY KEY (Id), -- Id cant be null
       FOREIGN KEY (Id) REFERENCES WorksIn (ProfId)
              --all professors participate 
    )


    Participation and Key Constraint in Relational Model (again)

    Alternate solution if both key and participation constraints apply: merge the tables representing the entity and relationship sets