DBMS How to Convert ER Diagram to Table Easily

To convert an ER diagram to tables, create one table for each entity with its attributes as columns, and for relationships, create tables with foreign keys referencing related entities using CREATE TABLE statements.

Examples

InputEntity: Student(Name, RollNo, Age)

OutputTable Student(Name VARCHAR, RollNo INT PRIMARY KEY, Age INT)

InputEntities: Student(RollNo, Name), Course(CourseID, Title), Relationship: Enroll(Student, Course)

OutputTables: Student(RollNo PRIMARY KEY, Name), Course(CourseID PRIMARY KEY, Title), Enroll(RollNo FOREIGN KEY, CourseID FOREIGN KEY)

InputEntity: Employee(EmpID, Name), Entity: Department(DeptID, DeptName), Relationship: WorksIn(Employee, Department)

OutputTables: Employee(EmpID PRIMARY KEY, Name), Department(DeptID PRIMARY KEY, DeptName), WorksIn(EmpID FOREIGN KEY, DeptID FOREIGN KEY)

How to Think About It

Start by identifying each entity in the ER diagram and create a table for it with its attributes as columns. For relationships, especially many-to-many, create separate tables that include foreign keys referencing the primary keys of the related entities. This way, the ER diagram's structure is mapped into relational tables.

Algorithm

1

Identify all entities in the ER diagram.

2

For each entity, create a table with attributes as columns and choose a primary key.

3

Identify relationships between entities.

4

For one-to-many relationships, add foreign key columns in the 'many' side table.

5

For many-to-many relationships, create a new table with foreign keys referencing both entities.

6

Define primary keys and foreign keys to maintain data integrity.

Code

sql

CREATE TABLE Student (
  RollNo INT PRIMARY KEY,
  Name VARCHAR(50),
  Age INT
);

CREATE TABLE Course (
  CourseID INT PRIMARY KEY,
  Title VARCHAR(100)
);

CREATE TABLE Enroll (
  RollNo INT,
  CourseID INT,
  PRIMARY KEY (RollNo, CourseID),
  FOREIGN KEY (RollNo) REFERENCES Student(RollNo),
  FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);

-- Tables created for Student, Course, and Enroll relationship

Output

Tables created for Student, Course, and Enroll relationship

Dry Run

Let's trace creating tables for Student, Course, and Enroll relationship from the ER diagram.

1

Create Student table

Student table with RollNo as primary key and columns Name, Age

2

Create Course table

Course table with CourseID as primary key and column Title

3

Create Enroll table

Enroll table with RollNo and CourseID as foreign keys and composite primary key

Table	Columns	Primary Key	Foreign Keys
Student	RollNo, Name, Age	RollNo	None
Course	CourseID, Title	CourseID	None
Enroll	RollNo, CourseID	RollNo, CourseID	RollNo->Student, CourseID->Course

Why This Works

Step 1: Entity to Table

Each entity in the ER diagram becomes a table with its attributes as columns to store data.

Step 2: Primary Key Selection

Choose a unique attribute as the primary key to identify each record in the table.

Step 3: Relationship Mapping

Relationships are represented by foreign keys or separate tables for many-to-many relationships to link entities.

Alternative Approaches

Use Nested Tables for Weak Entities

sql

CREATE TABLE Department (
  DeptID INT PRIMARY KEY,
  DeptName VARCHAR(50)
);

CREATE TABLE Employee (
  EmpID INT PRIMARY KEY,
  Name VARCHAR(50),
  DeptID INT,
  FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
);

This approach embeds weak entities as tables with foreign keys, simplifying queries but may increase joins.

Use Single Table with Nullable Columns

sql

CREATE TABLE Person (
  ID INT PRIMARY KEY,
  Name VARCHAR(50),
  CourseID INT NULL,
  DeptID INT NULL
);

Combines entities into one table with nullable columns, reducing tables but can cause sparse data and complexity.

Complexity: O(n) time, O(n) space

Time Complexity

Converting ER diagram to tables involves processing each entity and relationship once, so it is linear in the number of entities and relationships.

Space Complexity

Space depends on the number of tables and attributes created, proportional to the ER diagram size.

Which Approach is Fastest?

Direct mapping of entities and relationships to tables is fastest and simplest; alternatives may trade speed for design flexibility.

Approach	Time	Space	Best For
Direct Mapping	O(n)	O(n)	Clear ER diagrams with distinct entities
Nested Tables	O(n)	O(n)	Handling weak entities with dependencies
Single Table with Nullable Columns	O(n)	O(n)	Simple schemas with few relationships