ER to Relational Mapping

ER-to-relational mapping is the process of converting a conceptual ER/EER design into a relational database schema (tables, keys, and constraints).

📌 This step is also called logical database design or data model mapping.

• Input: ER or EER diagram

• Output: Relational schema (tables with PKs, FKs, constraints)

---

Why Do We Need Mapping?

• ER/EER models are conceptual

• Relational databases store data in tables

• Mapping provides a systematic algorithm to convert ER constructs into relations

• Used by CASE tools and DBMS design software

---

Example:

ER-to-Relational Mapping Algorithm (7 Steps)

---

Step 1: Mapping of Regular (Strong) Entity Types

Step 1 in ER-to-Relational mapping deals with converting regular (strong) entity types from the ER schema into relations (tables) in the relational schema.

A regular entity type is an entity that:

• Has its own primary key

• Does not depend on another entity for its existence

---

General Rules for Step 1

For each regular entity type E in the ER schema:

1. Create a Relation

• Create a relation R with the same name as the entity type E.

2. Include Attributes

• Include all simple attributes of the entity type.

• If an attribute is composite, include only its simple components.

  • Example: If Name = {Fname, Minit, Lname}, include Fname, Minit, Lname separately.

3. Choose the Primary Key

• Select one key attribute of the entity type as the primary key of the relation.

• If the key is composite, then all its simple attributes together form the primary key of the relation.

4. Handle Multiple Keys

• If an entity type has multiple candidate keys:

  • Choose one as the primary key

  • Keep information about the remaining keys for:

    • Declaring UNIQUE constraints

    • Indexing

    • Future design decisions

5. What Is NOT Included Yet

At this stage:

• Foreign keys

• Relationship attributes

are not included.
They are added later in subsequent mapping steps (Steps 3, 4, and 5).

---

Applying Step 1 to the COMPANY Example

From the ER diagram (Figure 9.1), the regular entity types are:

• EMPLOYEE

• DEPARTMENT

• PROJECT

1. EMPLOYEE Relation

• Simple attributes included:

  • Fname, Minit, Lname, Ssn, Bdate, Address, Sex, Salary

• Primary key:

  • Ssn

• Attributes such as Super_ssn and Dno are not added yet (they come from relationships and will be added later).

2. DEPARTMENT Relation

• Simple attributes included:

  • Dname, Dnumber

• Primary key:

  • Dnumber

• Dname is known to be unique, so this information is kept for later use.

• Relationship attributes like Mgr_ssn and Mgr_start_date are added in later steps.

3. PROJECT Relation

• Simple attributes included:

  • Pname, Pnumber, Plocation

• Primary key:

  • Pnumber

• Pname is also a unique key, and this information is stored for future design decisions.

• The attribute Dnum (foreign key) is added later.

---

Result of Step 1

• The relations created at this step are called entity relations

• Each tuple in these relations represents one entity instance

• No relationships are represented yet

The output of this step is shown in Figure 9.3(a).

---

Summary

• Step 1 converts strong entities into relations

• Only simple attributes are included

• One key is chosen as the primary key

• Additional keys are noted for later use

• Foreign keys and relationship attributes are not added yet

This step forms the foundation of the relational schema before relationships are mapped

Step 2: Mapping Weak Entity Types

A weak entity type is an entity that:

• Does not have a primary key of its own

• Is existence-dependent on another entity called the owner entity

• Is identified using:

  • The primary key of the owner entity, and

  • A partial key (also called a discriminator)

---

General Rules for Step 2

For each weak entity type W with owner entity type E:

1. Create a Relation

• Create a new relation R corresponding to the weak entity type W.

2. Include Attributes of the Weak Entity

• Include all simple attributes of W.

• If W has composite attributes, include only their simple components.

3. Include Owner’s Primary Key as a Foreign Key

• Include the primary key attribute(s) of the owner entity E in R.

• These attributes act as:

  • Foreign keys referencing the owner entity relation

  • Part of the primary key of the weak entity relation

• This step maps the identifying relationship between W and E.

4. Define the Primary Key of the Weak Entity Relation

• The primary key of R is a composite key formed by:

  • The primary key of the owner entity

  • The partial key of the weak entity

• This ensures that:

  • Each weak entity instance is uniquely identified

  • Identification is possible only through the owner entity

5. Order of Mapping (Weak-on-Weak Case)

• If a weak entity depends on another weak entity, then:

  • Map the owner weak entity first

  • Determine its primary key before mapping the dependent weak entity

---

Applying Step 2 to the COMPANY Example

Weak Entity Type: DEPENDENT

• Owner entity: EMPLOYEE

• Partial key: Dependent_name

Relation Created

DEPENDENT(Essn, Dependent_name, Sex, Bdate, Relationship)

Explanation:

• Essn:

  • Taken from the primary key Ssn of EMPLOYEE

  • Acts as a foreign key referencing EMPLOYEE

  • Renaming from Ssn to Essn is optional and done for clarity

• Dependent_name:

  • Partial key of DEPENDENT

• Primary Key:

  • {Essn, Dependent_name} (composite key)

This combination uniquely identifies each dependent per employee.

---

Referential Integrity and CASCADE Option

Since a weak entity:

• Cannot exist without its owner

It is common to specify:

ON UPDATE CASCADE
ON DELETE CASCADE

Why CASCADE?

• If an EMPLOYEE is deleted → all related DEPENDENT tuples must be deleted

• If the employee’s primary key changes → the foreign key in DEPENDENT must update automatically

This correctly enforces the existence dependency of weak entities.

---

Key Observations

• Weak entity relations:

  • Always have composite primary keys

  • Always include foreign keys referencing owner entities

• The identifying relationship is not stored separately

  • It is implicitly represented through the foreign key

• CASCADE actions are strongly recommended for weak entities

---

Summary

• Step 2 maps weak entities into relations

• Owner’s primary key + partial key → composite primary key

• Foreign key enforces existence dependency

• CASCADE actions maintain referential integrity

Step 3: Mapping of Binary 1:1 Relationship Types

A binary 1:1 relationship is a relationship where:

• Each entity in Entity A is related to at most one entity in Entity B, and

• Each entity in Entity B is related to at most one entity in Entity A.

When converting such relationships from an ER model to a relational model, we first identify:

• Relation S and relation T, corresponding to the two participating entity types.

There are three possible mapping approaches, but the foreign key approach is preferred in most cases.

---

1. Foreign Key Approach (Preferred Method)

How it works:

• Choose one relation (S) and include in it:

  • The primary key of the other relation (T) as a foreign key

  • Any simple attributes of the relationship

• The best choice for S is the entity with total participation in the relationship.

Why choose total participation?

• It avoids NULL values in the foreign key.

• Every tuple in S must participate in the relationship.

---

Example: MANAGES Relationship

• Relationship: MANAGES

• Entities involved:

  • EMPLOYEE

  • DEPARTMENT

• Participation:

  • DEPARTMENT has total participation (every department must have a manager)

  • EMPLOYEE has partial participation

Mapping Decision:

• Choose DEPARTMENT as relation S

• Add:

  • Mgr_ssn → foreign key referencing EMPLOYEE(Ssn)

  • Mgr_start_date → attribute of the MANAGES relationship

Resulting Relation:

DEPARTMENT(Dnumber, Dname, Mgr_ssn, Mgr_start_date)

---

Why not put the foreign key in EMPLOYEE?

• Most employees do not manage a department

• This would result in many NULL values

• Example: if only 2% of employees manage departments, then 98% of rows would have NULL foreign keys

---

Why not store foreign keys in both relations?

• Causes redundancy

• Leads to consistency maintenance problems

• Updates must be synchronized in two places

---

2. Merged Relation Approach

How it works:

• Combine:

  • Both entity types

  • The relationship

• Create one single relation

When is this possible?

• Both entities have total participation

• Both relations always have the same number of tuples

Drawback:

• Not flexible

• Rarely used in real-world database design

---

3. Cross-Reference (Relationship Relation) Approach

How it works:

• Create a new relation R to represent the relationship

• Include:

  • Primary keys of both entities as foreign keys

  • Relationship attributes

• One foreign key becomes the primary key

• The other becomes a unique key

Drawbacks:

• Requires an extra table

• Requires additional join operations

• Less efficient for 1:1 relationships

Note:

• This approach is mandatory for M:N relationships

• Only used for 1:1 relationships in special cases

---

Summary of 1:1 Relationship Mapping

Approach	When Used	Advantages	Disadvantages
Foreign Key	Default choice	Simple, efficient, fewer joins	Needs careful placement
Merged Relation	Both sides total participation	No joins needed	Inflexible
Relationship Relation	Rare cases	Clear separation	Extra table, extra joins

---

Key Points

• Foreign key approach is preferred

• Place the foreign key on the total participation side

• Avoid NULL values and redundancy

• Relationship attributes are stored with the foreign key

Step 4: Mapping Binary 1:N Relationship Types

A binary 1:N relationship is one where:

• One entity (1-side) can be related to many entities

• The other entity (N-side) is related to at most one entity on the 1-side
  (example: one department → many employees)

There are two possible mapping approaches in relational design.

1. Foreign Key Approach (Preferred)

Idea

Place a foreign key in the relation on the N-side of the relationship.

Why this works

Each entity on the N-side can be associated with only one entity on the 1-side, so a single foreign key is sufficient.

General Rules

For a binary 1:N relationship R:

1. Identify the relation S on the N-side

2. Identify the relation T on the 1-side

3. Add the primary key of T as a foreign key in S

4. Add any simple attributes of the relationship to S

---

Application to the Example

(a) WORKS_FOR (DEPARTMENT → EMPLOYEE)

• 1-side: DEPARTMENT

• N-side: EMPLOYEE

• Action:

  • Add Dnumber (PK of DEPARTMENT) as foreign key in EMPLOYEE

  • Rename it Dno

(b) SUPERVISION (EMPLOYEE → EMPLOYEE, recursive)

• An employee supervises many employees

• Action:

  • Add Ssn (PK of EMPLOYEE) as foreign key in EMPLOYEE

  • Rename it Super_ssn

• This is a recursive relationship, so the foreign key references the same table

(c) CONTROLS (DEPARTMENT → PROJECT)

• 1-side: DEPARTMENT

• N-side: PROJECT

• Action:

  • Add Dnumber (PK of DEPARTMENT) as foreign key in PROJECT

  • Rename it Dnum

All these foreign keys appear in Figure 9.2.

---

Why the Foreign Key Approach Is Preferred

• Fewer tables

• Simpler queries

• No unnecessary joins

• Efficient storage

Step 5: Mapping Binary M:N Relationship Types

A binary M:N (many-to-many) relationship is one where:

• Each entity in one set can be related to many entities in the other set, and

• Vice versa.

Because of this many-to-many cardinality, M:N relationships cannot be represented using a single foreign key in one of the participating relations.

---

Why a Separate Relation Is Required

In the traditional relational model (without multivalued attributes):

• A single foreign key works for 1:1 or 1:N relationships

• But for M:N relationships, this is not possible

• Therefore, the only valid option is to create a relationship relation (also called a cross-reference table)

---

General Mapping Rules for an M:N Relationship

For each binary M:N relationship type R:

1. Create a new relation S to represent the relationship

2. Include:

   • The primary key of each participating entity relation

   • These keys become foreign keys in S

3. The combination of these foreign keys forms the primary key of S

4. Include any simple attributes of the relationship as attributes of S

Each tuple in the new relation represents one relationship instance between the two entities.

---

Application to the Example: WORKS_ON

Relationship

• WORKS_ON between EMPLOYEE and PROJECT

• Type: M:N

  • An employee can work on many projects

  • A project can have many employees

Mapping Steps

1. Create a new relation: WORKS_ON

2. Add foreign keys:

   • Essn → references EMPLOYEE(Ssn)

   • Pno → references PROJECT(Pnumber)

3. Add relationship attribute:

   • Hours (number of hours an employee works on a project)

4. Define the primary key:

   • {Essn, Pno} (composite primary key)

This mapping is shown in Figure 9.3(c).

---

Referential Integrity and CASCADE Option

• Each WORKS_ON tuple depends on both EMPLOYEE and PROJECT

• Therefore, CASCADE should be used for:

  • ON UPDATE

  • ON DELETE

This ensures that:

• If an employee or project is deleted or updated,

• The related WORKS_ON tuples are automatically updated or removed,

• Maintaining database consistency.

---

Relationship Relation for 1:1 and 1:N (Comparison)

Although the relationship relation approach can also be used for:

• 1:1 relationships

• 1:N relationships

It is not recommended unless:

• Few relationship instances exist

• The goal is to avoid many NULL foreign key values

Primary Key Choice in Those Cases

• 1:N relationship → primary key is the foreign key referencing the N-side

• 1:1 relationship → either foreign key may be chosen as the primary key

---

Summary

• M:N relationships must always be mapped using a separate relation

• The relationship relation:

  • Contains foreign keys from both entities

  • Uses their combination as a primary key

  • Stores relationship attributes

• This approach ensures correctness and preserves relational integrity

Step 6: Mapping of Multivalued Attributes

What is a multivalued attribute?

A multivalued attribute is an attribute that can have more than one value for a single entity instance.

📌 Example:
A DEPARTMENT can have multiple locations, so LOCATIONS is a multivalued attribute.

---

Why special mapping is needed

The basic relational model does not allow multiple values in a single attribute (no lists or sets in one column).
Therefore, multivalued attributes cannot be stored directly in the entity’s relation.

---

Mapping Rule

For each multivalued attribute A:

1. Create a new relation R

2. Include:

   • Attribute A (or its simple components if composite)

   • The primary key K of the entity (or relationship) that owns A
     → K is included as a foreign key in R

3. The primary key of R is the combination of A and K

This ensures:

• Each value of the multivalued attribute is stored separately

• The values are correctly linked to the owning entity

---

Example: DEPARTMENT and LOCATIONS

ER Model:

• Entity: DEPARTMENT

• Multivalued attribute: LOCATIONS

Mapping:

Create a new relation:

DEPT_LOCATIONS(Dnumber, Dlocation)

• Dnumber → primary key of DEPARTMENT (foreign key here)

• Dlocation → one location of a department

• Primary key = {Dnumber, Dlocation}

📌 Result:

• Each department location is stored as a separate tuple

• If a department has 3 locations, there will be 3 rows in DEPT_LOCATIONS

---

Referential Integrity (CASCADE Option)

Because the multivalued attribute depends on the entity:

• ON DELETE CASCADE → deleting a department deletes its locations

• ON UPDATE CASCADE → updating a department key updates related rows

This maintains data consistency.

---

Composite Multivalued Attributes

If the multivalued attribute is composite:

• Include only its simple components

• Not all components must always be part of the primary key

• Some components may act like a partial key, similar to weak entities

---

Modern DBMS Note

In some modern relational systems:

• Multivalued attributes can be stored as array types

• This avoids creating a separate relation
  However, in the traditional relational model, a separate table is required

---

Summary of Step 6

Concept	Description
Multivalued attribute	Attribute with multiple values
Mapping method	Create a new relation
Attributes in new relation	Multivalued attribute + owner’s primary key
Primary key	Combination of both attributes
Referential action	CASCADE on update and delete

---

Key Line ✍️

Multivalued attributes are mapped by creating a separate relation containing the attribute and the primary key of the owning entity, with a composite primary key.

Step 7: Mapping of N-ary Relationship Types (n > 2)

What is an n-ary relationship?

An n-ary relationship is a relationship type that involves more than two entity types (where n > 2).

📌 Example:
A SUPPLY relationship involving:

• SUPPLIER

• PART

• PROJECT

This is a ternary (3-ary) relationship.

---

Why a special mapping is required

In the relational model:

• A relationship involving more than two entities cannot be represented using a single foreign key

• Just like M:N relationships, n-ary relationships require a separate relationship relation

---

Mapping Rule for N-ary Relationships

For each n-ary relationship type R (n > 2):

1. Create a new relation S to represent the relationship

2. Include in S:

   • The primary keys of all participating entity relations
     → These become foreign keys in S

   • Any simple attributes of the relationship
     (or simple components if composite)

3. Primary key of S:

   • Usually a combination of all foreign keys

   • This uniquely identifies each relationship instance

This approach is called the relationship relation (cross-reference) option

---

Example: Ternary Relationship SUPPLY

ER Model Description

The relationship SUPPLY relates:

• A SUPPLIER (Sname)

• A PART (Part_no)

• A PROJECT (Proj_name)

Meaning:

Supplier s supplies part p to project j

---

Relational Mapping

Create a new relation:

SUPPLY(Sname, Part_no, Proj_name, Quantity)

• Sname → FK referencing SUPPLIER

• Part_no → FK referencing PART

• Proj_name → FK referencing PROJECT

• Quantity → relationship attribute

• Primary Key = {Sname, Part_no, Proj_name}

Each tuple represents one supply instance.

---

Special Case: Cardinality Constraint = 1

If any participating entity has a cardinality of 1 in the relationship:

• The foreign key corresponding to that entity should not be included in the primary key

• This is because that entity does not contribute to uniqueness

📌 This depends on semantic constraints, as discussed in ER modeling (Section 3.9.2).

---

Referential Integrity

Since the relationship depends on all participating entities:

• ON DELETE CASCADE and ON UPDATE CASCADE are typically used

• Deleting any participating entity removes related relationship tuples

---

Summary of Step 7

Aspect	Description
Relationship type	N-ary (n > 2)
Mapping approach	Relationship (cross-reference) relation
New relation includes	Primary keys of all participating entities + relationship attributes
Foreign keys	Reference each participating entity
Primary key	Usually a combination of all foreign keys
Special case	Exclude FK from PK if entity has cardinality 1

---

Key Statement ✍️

N-ary relationships are mapped by creating a separate relationship relation containing foreign keys to all participating entity relations, with a composite primary key.