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.

Practice

1. In an ER diagram, what does an entity typically become when converting to a database schema?

easy

A. A table with columns for each attribute

B. A single column in a table

C. A database index

D. A stored procedure

Solution

1. Step 1: Understand what an entity represents

   An entity in an ER diagram represents a real-world object or concept with attributes.

2. Step 2: Map entity to database structure

   Each entity is converted into a table, where each attribute becomes a column in that table.

3. Final Answer:

   A table with columns for each attribute -> Option A

4. Quick Check:

   Entity = Table [OK]

Hint: Entities become tables with columns for attributes [OK]

Common Mistakes:

• Confusing entities with indexes
• Thinking entities become single columns
• Assuming entities become procedures

2. Which of the following is the correct way to represent a one-to-many relationship in tables derived from an ER diagram?

easy

A. Create a new table with only primary keys from both tables

B. Add a foreign key column in the 'many' side table referencing the 'one' side

C. Add a foreign key column in the 'one' side table referencing the 'many' side

D. Use a trigger to link the two tables

Solution

1. Step 1: Understand one-to-many relationship

   One-to-many means one record in the first table relates to many records in the second table.

2. Step 2: Map relationship to tables

   The 'many' side table gets a foreign key column referencing the 'one' side table's primary key.

3. Final Answer:

   Add a foreign key column in the 'many' side table referencing the 'one' side -> Option B

4. Quick Check:

   Foreign key on 'many' side = Add a foreign key column in the 'many' side table referencing the 'one' side [OK]

Hint: Foreign key goes in the 'many' side table [OK]

Common Mistakes:

• Placing foreign key on the 'one' side
• Creating unnecessary tables for one-to-many
• Using triggers instead of foreign keys

3. Given two entities Author(id, name) and Book(id, title, author_id) with a one-to-many relationship from Author to Book, what will be the result of this SQL query?

SELECT a.name, b.title FROM Author a JOIN Book b ON a.id = b.author_id WHERE a.name = 'Alice';

medium

A. Syntax error due to join condition

B. List of all authors and their books

C. List of books with no authors

D. List of all books written by Alice

Solution

1. Step 1: Analyze the JOIN condition

   The query joins Author and Book on matching author IDs, linking books to their authors.

2. Step 2: Apply the WHERE filter

   It filters authors with name 'Alice', so only books by Alice are selected.

3. Final Answer:

   List of all books written by Alice -> Option D

4. Quick Check:

   Join + filter by author name = books by Alice [OK]

Hint: JOIN on foreign key filters books by author [OK]

Common Mistakes:

• Confusing join condition causing no results
• Ignoring WHERE clause filtering
• Thinking it lists all authors

4. You have two tables from an ER diagram: Student(id, name) and Enrollment(student_id, course_id). You want to add a foreign key constraint to Enrollment.student_id. Which SQL statement is correct?

medium

A. ALTER TABLE Enrollment ADD FOREIGN KEY (student_id) REFERENCES Student(id);

B. ALTER TABLE Student ADD FOREIGN KEY (id) REFERENCES Enrollment(student_id);

C. CREATE FOREIGN KEY Enrollment.student_id REFERENCES Student.id;

D. ALTER Enrollment ADD CONSTRAINT FOREIGN KEY student_id Student(id);

Solution

1. Step 1: Identify the correct syntax for adding foreign key

   The standard syntax is ALTER TABLE [table] ADD FOREIGN KEY (column) REFERENCES [other_table](column).

2. Step 2: Apply to given tables

   Enrollment.student_id references Student.id, so the statement must alter Enrollment table.

3. Final Answer:

   ALTER TABLE Enrollment ADD FOREIGN KEY (student_id) REFERENCES Student(id); -> Option A

4. Quick Check:

   ALTER TABLE + ADD FOREIGN KEY + REFERENCES [OK]

Hint: Foreign key added on referencing table with ALTER TABLE [OK]

Common Mistakes:

• Adding foreign key on referenced table
• Wrong ALTER TABLE syntax
• Using CREATE FOREIGN KEY instead of ALTER TABLE

5. Consider an ER diagram with entities Employee(emp_id, name), Project(proj_id, title), and a many-to-many relationship WorksOn between them. How should you map this relationship into tables?

hard

A. Add proj_id as a foreign key column in Employee table

B. Add emp_id as a foreign key column in Project table

C. Create a new table WorksOn with columns emp_id and proj_id as foreign keys

D. Merge Employee and Project tables into one

Solution

1. Step 1: Understand many-to-many relationships

   Many-to-many means multiple employees can work on multiple projects and vice versa.

2. Step 2: Map many-to-many to tables

   This requires a new table (junction table) that holds foreign keys from both Employee and Project tables.

3. Final Answer:

   Create a new table WorksOn with columns emp_id and proj_id as foreign keys -> Option C

4. Quick Check:

   Many-to-many = junction table with two foreign keys [OK]

Hint: Many-to-many needs a new table with two foreign keys [OK]

Common Mistakes:

• Adding foreign key to only one table
• Merging unrelated tables
• Ignoring the need for a junction table