ER diagram to table mapping in SQL - Step-by-Step Execution

Concept Flow - ER diagram to table mapping

Start with ER Diagram

↓

Identify Entities

↓

Create Tables for Entities

↓

Identify Attributes

↓

Add Columns to Tables

↓

Identify Relationships

↓

Add Foreign Keys or Create Join Tables

↓

Finalize Tables with Keys and Constraints

This flow shows how to convert an ER diagram step-by-step into database tables by mapping entities, attributes, and relationships.

Execution Sample

SQL

Entity: Student(id, name, age)
Entity: Course(id, title)
Relationship: Enroll(Student, Course)

-- Mapping to tables:
CREATE TABLE Student (id INT PRIMARY KEY, name VARCHAR(50), age INT);
CREATE TABLE Course (id INT PRIMARY KEY, title VARCHAR(100));
CREATE TABLE Enroll (student_id INT, course_id INT, PRIMARY KEY(student_id, course_id), FOREIGN KEY(student_id) REFERENCES Student(id), FOREIGN KEY(course_id) REFERENCES Course(id));

This example shows how entities and a many-to-many relationship from an ER diagram become tables with keys and foreign keys.

Execution Table

Step	Action	Input	Output
1	Identify Entities	ER Diagram with Student, Course	Entities: Student, Course
2	Create Tables for Entities	Entities: Student, Course	Tables: Student(id, name, age), Course(id, title)
3	Identify Attributes	Entities	Attributes: Student(id, name, age), Course(id, title)
4	Add Columns to Tables	Attributes	Tables with columns as attributes
5	Identify Relationships	ER Diagram with Enroll relationship	Relationship: Enroll(Student, Course)
6	Map Relationship	Many-to-many Enroll	Create Enroll table with student_id, course_id as foreign keys
7	Add Keys and Constraints	Tables and relationships	Primary keys and foreign keys added to tables
8	Final Tables	All mappings	Student, Course, Enroll tables ready for database

💡 All entities and relationships from ER diagram are mapped to tables with keys and constraints.

Variable Tracker

Variable	Start	After Step 2	After Step 4	After Step 6	Final
Entities	ER Diagram	Student, Course identified	Same	Same	Same
Tables	None	Student, Course tables created	Tables with columns	Enroll table added	All tables with keys and constraints
Relationships	ER Diagram	None	None	Enroll identified	Enroll mapped to table

Key Moments - 2 Insights

Why do we create a separate table for the Enroll relationship?

Because Enroll is a many-to-many relationship, it cannot be represented by foreign keys in just one table. We create a join table with foreign keys referencing both Student and Course tables, as shown in execution_table step 6.

How do attributes in the ER diagram become columns in tables?

Each attribute of an entity becomes a column in the corresponding table. For example, Student's attributes id, name, and age become columns in the Student table, as shown in execution_table steps 3 and 4.

Visual Quiz - 3 Questions

Look at the execution_table at step 6. What does the Enroll table contain?

A Only student_id as a foreign key

B Only course_id as a foreign key

C Columns student_id and course_id as foreign keys

D No foreign keys, just attributes

💡 Hint

Refer to execution_table row 6 describing the mapping of the many-to-many relationship.

At which step are primary keys added to the tables?

A Step 7

B Step 2

C Step 4

D Step 5

💡 Hint

Check execution_table row 7 about adding keys and constraints.

If the Enroll relationship was one-to-many instead of many-to-many, how would the mapping change?

A Create a join table with two foreign keys

B Add a foreign key to the 'many' side table referencing the 'one' side

C No tables needed for the relationship

D Add foreign keys to both tables

💡 Hint

Think about how one-to-many relationships are represented in relational tables, unlike many-to-many shown in execution_table step 6.

Concept Snapshot

ER Diagram to Table Mapping:
- Entities become tables
- Attributes become columns
- Primary keys uniquely identify rows
- Relationships map to foreign keys or join tables
- Many-to-many needs a separate join table
- Keys and constraints enforce data integrity

Full Transcript

This visual execution shows how to convert an ER diagram into database tables. First, identify entities and create tables for them. Then add attributes as columns. Next, identify relationships. For many-to-many relationships, create a join table with foreign keys referencing the related tables. Finally, add primary keys and foreign keys to enforce data integrity. This step-by-step mapping ensures the ER diagram is correctly represented in the database structure.