Converting ER diagrams to relational schema in DBMS Theory - Step-by-Step Execution

Concept Flow - Converting ER diagrams to relational schema

Start with ER Diagram

↓

Identify Entities

↓

Create Tables for Entities

↓

Identify Attributes

↓

Add Attributes as Columns

↓

Identify Primary Keys

↓

Set Primary Keys in Tables

↓

Identify Relationships

↓

Map Relationships to Foreign Keys or Tables

↓

Finalize Relational Schema

This flow shows how to convert an ER diagram step-by-step into a relational schema by mapping entities, attributes, keys, and relationships.

Execution Sample

DBMS Theory

Entity: Student (ID, Name)
Entity: Course (Code, Title)
Relationship: Enrolls (Student, Course)

Convert to tables:

Convert entities and their relationship into relational tables with keys and foreign keys.

Analysis Table

Step	Action	Input	Output
1	Identify Entities	ER Diagram with Student, Course	Entities: Student, Course
2	Create Tables	Entities Student, Course	Tables: Student, Course
3	Identify Attributes	Student(ID, Name), Course(Code, Title)	Attributes identified
4	Add Attributes as Columns	Tables Student, Course	Student(ID PK, Name), Course(Code PK, Title)
5	Identify Primary Keys	ID for Student, Code for Course	Primary keys set
6	Identify Relationship	Enrolls between Student and Course	Relationship Enrolls identified
7	Map Relationship	Enrolls is many-to-many	Create Enrolls table with StudentID FK, CourseCode FK
8	Finalize Schema	Tables Student, Course, Enrolls	Relational schema ready

💡 All entities, attributes, keys, and relationships mapped to tables and keys

State Tracker

Variable	Start	After Step 2	After Step 4	After Step 7	Final
Entities	ER Diagram	Student, Course	Student, Course	Student, Course	Student, Course
Tables	None	Student, Course	Student(ID, Name), Course(Code, Title)	Student, Course, Enrolls	Student, Course, Enrolls
Primary Keys	None	None	Student(ID), Course(Code)	Student(ID), Course(Code)	Student(ID), Course(Code)
Relationships	Enrolls	Enrolls	Enrolls	Enrolls mapped	Enrolls mapped

Key Insights - 3 Insights

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

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

How do we decide which attribute becomes the primary key?

Primary keys are chosen from unique identifiers of entities, like Student ID or Course Code. This is shown in step 5 where ID and Code are set as primary keys.

What happens if an attribute is multi-valued in the ER diagram?

Multi-valued attributes are usually converted into separate tables to store multiple values linked to the main entity. This is part of mapping attributes but not shown in this simple example.

Visual Quiz - 3 Questions

Look at the execution_table at step 4. What columns does the Student table have?

A Only ID

B ID and Name

C ID, Name, and CourseCode

D Name and CourseCode

💡 Hint

Check the 'Output' column in step 4 of the execution_table.

At which step is the many-to-many relationship converted into a separate table?

A Step 5

B Step 3

C Step 7

D Step 2

💡 Hint

Look for the step mentioning creation of the Enrolls table in the execution_table.

If the Enrolls relationship was one-to-many instead of many-to-many, how would the schema change?

A Add foreign key in the 'many' side table only

B Create a separate Enrolls table

C Add foreign keys in both tables

D No changes needed

💡 Hint

Refer to the key_moments explanation about many-to-many vs one-to-many relationships.

Concept Snapshot

Converting ER diagrams to relational schema:
- Entities become tables
- Attributes become columns
- Primary keys uniquely identify rows
- Relationships map to foreign keys or new tables
- Many-to-many relationships need separate tables
- Result is a set of tables ready for a database

Full Transcript

This visual execution shows how to convert an ER diagram into a relational schema. We start by identifying entities and creating tables for them. Then, we add attributes as columns and set primary keys. Next, we identify relationships and map them to foreign keys or new tables, especially for many-to-many relationships. The example traces a Student and Course entity with an Enrolls relationship, showing step-by-step how tables and keys are formed. Key moments clarify why many-to-many relationships need separate tables and how primary keys are chosen. The quiz tests understanding of table columns, relationship mapping steps, and schema changes for different relationship types.

Practice

1. In converting an ER diagram to a relational schema, how is a one-to-many relationship typically represented?

easy

A. Add a foreign key in the table on the 'many' side referencing the 'one' side

B. Create a new table for the relationship

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

D. Merge both entities into a single table

Solution

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

   In a one-to-many relationship, one entity instance relates to many instances of another entity.

2. Step 2: Represent relationship in relational schema

   The foreign key is placed in the table representing the 'many' side, pointing to the 'one' side's primary key.

3. Final Answer:

   Add a foreign key in the table on the 'many' side referencing the 'one' side -> Option A

4. Quick Check:

   One-to-many = foreign key on many side [OK]

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

Common Mistakes:

• Placing foreign key on the 'one' side
• Creating unnecessary new tables for one-to-many
• Merging entities incorrectly

2. Which of the following is the correct way to represent a many-to-many relationship in a relational schema?

easy

A. Add foreign keys in both entity tables referencing each other

B. Merge both entities into a single table

C. Create a new table with foreign keys referencing both entities

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

Solution

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

   Many-to-many means multiple instances of one entity relate to multiple instances of another.

2. Step 2: Represent many-to-many in relational schema

   Create a new table (junction table) with foreign keys referencing the primary keys of both entities.

3. Final Answer:

   Create a new table with foreign keys referencing both entities -> Option C

4. Quick Check:

   Many-to-many = new junction table [OK]

Hint: Use a new table for many-to-many relationships [OK]

Common Mistakes:

• Adding foreign keys directly in entity tables
• Merging entities incorrectly
• Ignoring the need for a junction table

3. Given an ER diagram with entities Student(SID, Name) and Course(CID, Title) connected by a many-to-many relationship Enrolls, which relational schema correctly represents this?

medium

A. Student(SID, Name), Course(CID, Title, SID), Enrolls(SID, CID)

B. Student(SID, Name), Course(CID, Title), Enrolls(SID, CID)

C. Student(SID, Name, CID), Course(CID, Title)

D. Student(SID, Name), Course(CID, Title)

Solution

1. Step 1: Identify entities and relationship type

   Student and Course are entities; Enrolls is many-to-many between them.

2. Step 2: Convert entities and relationship to tables

   Student and Course become tables with their attributes. Enrolls becomes a new table with foreign keys SID and CID.

3. Final Answer:

   Student(SID, Name), Course(CID, Title), Enrolls(SID, CID) -> Option B

4. Quick Check:

   Many-to-many needs separate table [OK]

Hint: Many-to-many always needs a separate table [OK]

Common Mistakes:

• Adding foreign key in Course for Student
• Adding foreign key in Student for Course
• Omitting the Enrolls table

4. You converted an ER diagram with a one-to-one relationship between Person and Passport by adding a foreign key PersonID in Passport. However, the database allows multiple passports for one person. What is the likely mistake?

medium

A. Foreign key in Passport lacks a UNIQUE constraint

B. Foreign key should be in Person table, not Passport

C. Passport table should be merged with Person table

D. PersonID should be primary key in Passport table

Solution

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

   One-to-one means each person has at most one passport and vice versa.

2. Step 2: Check foreign key constraints

   Foreign key alone allows many passports per person unless a UNIQUE constraint is added to enforce one-to-one.

3. Final Answer:

   Foreign key in Passport lacks a UNIQUE constraint -> Option A

4. Quick Check:

   One-to-one needs UNIQUE foreign key [OK]

Hint: Add UNIQUE to foreign key for one-to-one [OK]

Common Mistakes:

• Assuming foreign key alone enforces one-to-one
• Placing foreign key in wrong table
• Merging tables unnecessarily

5. An ER diagram has entities Author(AID, Name), Book(BID, Title), and a many-to-many relationship Writes with an attribute RoyaltyPercentage. How should you convert this to a relational schema?

hard

A. Create Author(AID, Name), Book(BID, Title), Writes(AID, BID)

B. Create Author(AID, Name), Book(BID, Title, RoyaltyPercentage), Writes(AID, BID)

C. Create Author(AID, Name, RoyaltyPercentage), Book(BID, Title), Writes(AID, BID)

D. Create Author(AID, Name), Book(BID, Title), Writes(AID, BID, RoyaltyPercentage)

Solution

1. Step 1: Identify entities and relationship with attribute

   Author and Book are entities; Writes is many-to-many with attribute RoyaltyPercentage.

2. Step 2: Convert entities and relationship with attribute

   Entities become tables with their attributes. The many-to-many relationship becomes a new table including foreign keys and the attribute RoyaltyPercentage.

3. Final Answer:

   Create Author(AID, Name), Book(BID, Title), Writes(AID, BID, RoyaltyPercentage) -> Option D

4. Quick Check:

   Relationship attributes go in junction table [OK]

Hint: Put relationship attributes in the new junction table [OK]

Common Mistakes:

• Adding relationship attribute to entity tables
• Omitting the junction table
• Placing attribute in wrong table