Mapping ER Diagrams to Relational Tables (Part 1)

Designing an ER diagram is only the first step in database development. While an ER diagram helps us understand the structure of a system, a Database Management System (DBMS) cannot directly use it.

Computers store data in tables, not rectangles, ellipses, and diamonds.

Therefore, after designing an ER diagram, we must convert it into relational tables.

This process is called ER Mapping or Mapping ER Diagrams to the Relational Model.

It is one of the most important topics in DBMS because almost every real-world database follows this process.

---

Learning Objectives

After completing this lesson, you will be able to:

• Understand what ER Mapping is.
• Explain why ER Mapping is necessary.
• Convert strong entities into tables.
• Convert weak entities into tables.
• Identify primary keys.
• Understand foreign keys.
• Map simple attributes into columns.
• Avoid common mistakes during database design.

---

What is ER Mapping?

ER Mapping is the process of converting an Entity-Relationship Diagram (ER Diagram) into relational database tables.

Simply put,

ER Diagram → Database Tables

The relational model stores information using tables, rows, and columns.

Therefore, every entity, attribute, and relationship in the ER diagram must eventually become part of one or more database tables.

---

Why Do We Need ER Mapping?

Imagine you have created the following ER diagram.

Student
   |
 Enrolls
   |
Course

This diagram is easy for humans to understand.

However, MySQL cannot execute an ER diagram.

Instead, it expects something like this:

Student
-------------------------
Student_ID
Name
Email

Course
-------------------------
Course_ID
Course_Name
Credits

ER Mapping converts the visual design into tables that the DBMS understands.

---

Database Design Process

Database development usually follows these steps.

Requirements
      │
      ▼
ER Diagram
      │
      ▼
ER Mapping
      │
      ▼
Relational Tables
      │
      ▼
SQL Implementation
      │
      ▼
Database

Notice that mapping happens before SQL programming.

---

Real-Life Example

Suppose you are building a Hospital Management System.

The ER diagram contains

• Patient
• Doctor
• Medicine
• Appointment

These are conceptual objects.

After mapping, they become database tables.

Patient

Patient_ID
Name
Age
Gender

Doctor

Doctor_ID
Name
Specialization

Appointment

Appointment_ID
Date
Time

This is how every professional database is built.

---

Rule 1: Every Strong Entity Becomes One Table

This is the simplest mapping rule.

Every Strong Entity in the ER Diagram becomes one relational table.

Example ER Diagram

+-----------+
| Student   |
+-----------+

Attributes

• Student_ID
• Name
• Email
• Phone

After mapping

Student

Student_ID
Name
Email
Phone

Every attribute becomes a column.

---

Example 2

ER Diagram

+------------+
| Employee   |
+------------+

Attributes

• Employee_ID
• Name
• Salary
• Department

Relational Table

Employee

Employee_ID
Name
Salary
Department

Very simple.

One strong entity always becomes one table.

---

Rule 2: Every Simple Attribute Becomes a Column

Each simple attribute is directly converted into a column.

Example

Entity

Student

Attributes

• Student_ID
• Name
• Age
• Gender

Table

Student

Student_ID
Name
Age
Gender

There is nothing complicated here.

Each attribute simply becomes one column.

---

What Happens to Composite Attributes?

Composite attributes are not stored as one column.

Instead, each component becomes a separate column.

Example

Address consists of

• House Number
• Street
• City
• State
• PIN Code

Incorrect

Address

Correct

House_No
Street
City
State
PIN_Code

This makes searching and filtering much easier.

We will study this in detail in Part 2.

---

Rule 3: Every Table Needs a Primary Key

A relational table must uniquely identify every row.

Therefore,

Every mapped table should contain a Primary Key.

Example

Student

Student_ID
Name
Email

Primary Key

Student_ID

Without a primary key,

• Duplicate rows become possible.
• Data becomes difficult to update.
• Relationships become impossible.

---

What is a Primary Key?

A Primary Key is a column that uniquely identifies every row in a table.

Characteristics

• Unique
• Cannot be NULL
• One Primary Key per table
• Identifies one record

Example

Student

Student_ID

Student_ID

101

102

103

Each value is unique.

---

Examples of Primary Keys

Table	Primary Key
Student	Student_ID
Employee	Employee_ID
Doctor	Doctor_ID
Customer	Customer_ID
Book	Book_ID
Department	Department_ID

---

Quiz 1

Question 1

Every Strong Entity becomes

A. Relationship

B. Table

C. Attribute

D. View

Answer

Table

---

Question 2

Every Simple Attribute becomes

A. Database

B. Table

C. Column

D. Relationship

Answer

Column

---

Rule 4: Mapping Weak Entities

Weak entities cannot exist independently.

They depend on another entity.

Example

Employee

↓

Dependent

The dependent has no meaning without its employee.

---

Weak Entity Example

ER Diagram

Employee

Employee_ID

↓

Dependent

Dependent_Name
Age
Relationship

Mapped Tables

Employee

Employee_ID
Name
Department

Dependent

Employee_ID
Dependent_Name
Age
Relationship

Notice something important.

The weak entity also stores the owner’s primary key.

This creates a connection between both tables.

---

Composite Primary Key

Many weak entities require a Composite Primary Key.

Example

Dependent

Employee_ID

Dependent_Name

Together they uniquely identify each dependent.

Neither column alone is sufficient.

---

Why Can’t Weak Entities Have Their Own Primary Key?

Suppose two employees both have a dependent named

“John”

If only the dependent name is stored,

John
John

Which John belongs to which employee?

We don’t know.

Therefore,

Employee_ID is included.

Now

Employee_ID   Dependent_Name

1001          John

1002          John

No confusion exists.

---

Introduction to Foreign Keys

A Foreign Key connects two tables.

It stores the Primary Key of another table.

Example

Student

Student_ID
Name

Enrollment

Enrollment_ID

Student_ID
Course_ID

Here,

Student_ID inside Enrollment is a Foreign Key.

It points to Student.

Foreign Keys maintain relationships between tables.

---

Why Are Foreign Keys Important?

Without Foreign Keys,

tables become isolated.

The database cannot determine

• which student enrolled,
• which customer placed an order,
• which employee belongs to a department.

Foreign Keys establish these connections.

---

Real-Life Banking Example

Tables

Customer

Customer_ID
Name

Account

Account_ID

Customer_ID

Balance

Customer_ID inside Account tells us

which customer owns the account.

Without this column,

ownership information would be lost.

---

Quiz 2

Question 1

A Weak Entity depends on

A. SQL

B. Owner Entity

C. Database

D. Table

Answer

Owner Entity

---

Question 2

A Foreign Key is used to

A. Delete data

B. Connect tables

C. Create indexes

D. Store images

Answer

Connect tables

---

Common Mistakes Students Make

Mistake 1

Creating one table for every attribute.

Incorrect

Student_Table

Name_Table

Age_Table

Correct

Student

Student_ID

Name

Age

---

Mistake 2

Ignoring the Primary Key.

Every table should have a unique identifier.

---

Mistake 3

Treating Composite Attributes as one column.

Instead of

Address

Use

Street
City
State
PIN_Code

---

Mistake 4

Giving Weak Entities independent identities.

Weak entities must always reference their owner.

---

Memory Trick

Remember the rule:

Entity
↓

Table

Attribute
↓

Column

Primary Key
↓

Unique Identifier

Foreign Key
↓

Connection

Weak Entity
↓

Needs Owner

If you remember these five rules, you can map most basic ER diagrams without difficulty.

---

Interview-Focused Questions