Mapping the ER Model to Relational DBs

last updated 26-sep-19

Database Design

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


  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 database represented is viewed as a graphical drawing of

Relational Model: The database is viewed as a


Representation of Entity Type in Relational Model

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

---> Person(....)

Mapping #2: The attributes of a relation contains at least the simple 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 is the process we will study to help deal with this and would result in:

Persons(SSN, FirstName, LastName, Address, Birthdate)
Hobbies(SSN, Hobby)


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)

If no "Since" attribute, the relations could be (with some appropriate attribute renaming and additions)

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

Projects(ProjId, Name, TotalCost, StartDate)
Parts(UPC, PartName, Weight, WSPrice)
Suppliers(SupId, Name, Address)
Sold(ProjId, UPC, SupId, Date, Price)

Relationships tend to be verbs; attributes of relationships are nouns or adverbs


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 always 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.

Employees(EmpID, Name, Address, Salary, SupervisorID)

Persons(PID, Name, Address, SpouseID, Mdate)

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.

Assume multiple marriages are now recorded, thus many-to-many

MarriedTo(HusbandID, WifeID, MarDate, DivDate)




Courses (CrsCode, SectNo, Enroll)
Professor (Id, DeptId, Name)
Teaching (CrsCode, SecNo, Id, RoomNo)

Assuming a 1-many cardinaltiy for WorksIn

Professor(Id, Name, DeptId,Since,Status)


Assuming a many to many cardinality for WorksIn

Real SQL code

   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 



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



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

NOTE: The material below on participation constraints is specialized cases and may be reviewed at a later time.

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

Participation Constraint

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


Inclusion dependency: Every professor works in at least one depít.

in relational model: (easy)

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

  • Special case: Every professor works in exactly one depít. (easy)


  • General case (not so easy):

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





    Participation Constraint in Relational Model

    Example (here you canít 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 canít 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