After designing the Entity Relationship (ER) Model, the next step is to map it to the Relational Model so it can be implemented in a relational database (like MySQL, PostgreSQL, SQL Server). This mapping converts entities, attributes, relationships, and constraints into tables, columns, primary keys, foreign keys, and constraints.

For beginners, understanding the mapping rules makes it easier to turn a conceptual design into working SQL schemas.

Mapping Strong Entities

A strong entity is converted into a relation (table) as follows:

  • Each entity type becomes a table.

  • Attributes of the entity become columns in the table.

  • The key attributes (or key set) of the entity become the primary key of the table.

Example

STUDENT(Roll_No, Name, Branch, CGPA)

  • Table name: STUDENT

  • Columns: Roll_No, Name, Branch, CGPA

  • Primary key: Roll_No

Mapping Attributes

  • Simple attributes → regular columns

  • Composite attributes → split into their component columns

  • Multi-valued attributes → usually placed in a separate table with a foreign key

  • Key attributes → part of the primary key or candidate key

Example of Multi-Valued Attribute

Assume STUDENT has a multi-valued attribute Phone.

Create table:

STUDENT_PHONE(Student_ID, Phone)

Student_ID is a foreign key referencing STUDENT.

Mapping Binary Relationships (1:1, 1:M, M:N)

One-to-One (1:1)

  • Choose one of the two tables.

  • Add the primary key of the other table as a foreign key.

Example

PERSON(PID, Name, Address)

PASSPORT(PID, Issue_Date)

PID in PASSPORT references PERSON(PID).

One-to-Many (1:M)

  • Add the primary key of the “one” side table as a foreign key in the “many” side table.

Example

DEPARTMENT(DID, DName)

EMPLOYEE(EID, Name, DID)

DID in EMPLOYEE references DEPARTMENT(DID).

Many-to-Many (M:N)

  • Create a separate junction table (relationship table).

  • Include foreign keys from both participating tables.

  • Make the combination of these foreign keys the primary key of the new table.

  • Add any relationship attributes (like Grade, Date) as columns in the junction table.

Example

STUDENT(Roll_No, Name, Branch)

COURSE(Course_ID, Course_Name, Credits)

ENROLLMENT(Roll_No, Course_ID, Grade, Semester)

  • Roll_No references STUDENT(Roll_No)

  • Course_ID references COURSE(Course_ID)

  • Primary key: {Roll_No, Course_ID}

Mapping Weak Entities

A weak entity becomes a separate table.

Its primary key is formed by:

  • The primary key of the strong entity (as a foreign key)

  • The partial key attributes of the weak entity

All other attributes of the weak entity become regular columns.

Example

STUDENT(Roll_No, Name, Branch)

(strong entity)

ENROLLMENT(Semester, Grade)

(weak entity, with partial key Semester)

Table:

ENROLLMENT(Roll_No, Semester, Grade)

  • Primary key: {Roll_No, Semester}

  • Foreign key: Roll_No references STUDENT(Roll_No)

Mapping Generalization / Specialization (EER)

Several strategies exist; a common one is:

  • Create a table for the superclass with common attributes.

  • Create tables for each subclass with subclass-specific attributes.

  • Keep the key of the superclass in each subclass table as a foreign key and often as the primary key.

Example

PERSON(PID, Name, Address)

Subclass tables:

STUDENT(PID, Roll_No, Branch)

EMPLOYEE(PID, Emp_ID, Dept, Salary)

Here, PID in STUDENT and EMPLOYEE references PERSON(PID) and is the primary key of each subclass table.

Mapping Aggregation

  • Treat the aggregated relationship as a relationship table (like a junction table).

  • The table contains foreign keys from the entities involved in the original relationship plus any relationship attributes.

  • If the aggregation participates in a higher-level relationship, link that to the aggregation table as you would to any other table.

Example

ENROLLMENT(Roll_No, Course_ID, Grade, Semester)

If FACULTY supervises ENROLLMENT:

SUPERVISED_BY(Faculty_ID, Roll_No, Course_ID)

or a similar structure.

Summary

Mapping ER Model to Relational Model means converting entities into tables, attributes into columns, and relationships into foreign keys or junction tables. Strong entities, weak entities, binary relationships, and advanced EER concepts like generalization, specialization, and aggregation all follow clear mapping rules. For beginners, learning these rules step by step helps turn drawings on paper into actual, working relational database schemas.