Converting ER diagrams to relational schema in DBMS Theory - Mini Project: Build & Apply

Converting ER diagrams to relational schema

📖 Scenario: You are designing a database for a small library. You have an ER diagram that shows entities like Book, Author, and Publisher with their attributes and relationships.Your task is to convert this ER diagram into a relational schema step-by-step.

🎯 Goal: Build a relational schema by creating tables with primary keys and foreign keys based on the ER diagram.

📋 What You'll Learn

Create tables for entities with correct attributes

Define primary keys for each table

Add foreign keys to represent relationships

Use clear and consistent naming for tables and keys

💡 Why This Matters

🌍 Real World

Database designers use ER diagrams to plan databases and then convert them into relational schemas to create actual databases.

💼 Career

Understanding how to translate ER diagrams into relational schemas is essential for roles like database administrator, data analyst, and software developer.

1

Create tables for entities

Create three tables called Book, Author, and Publisher. Each table should have these columns exactly:

Book: BookID (primary key), Title, Year
Author: AuthorID (primary key), Name
Publisher: PublisherID (primary key), PublisherName

DBMS Theory

# Create tables Book, Author, and Publisher with specified columns
# Your code here

Hint

Define each table with the exact columns and mark the primary key using PRIMARY KEY.

2

Add a config variable for relationship type

Create a variable called relationship_type and set it to the string 'many-to-many' to represent the relationship between Book and Author.

DBMS Theory

CREATE TABLE Book (
    BookID INT PRIMARY KEY,
    Title VARCHAR(100),
    Year INT
);

CREATE TABLE Author (
    AuthorID INT PRIMARY KEY,
    Name VARCHAR(100)
);

CREATE TABLE Publisher (
    PublisherID INT PRIMARY KEY,
    PublisherName VARCHAR(100)
);

-- Define the relationship type between Book and Author
-- Your code here

Hint

Just assign the string 'many-to-many' to the variable relationship_type.

3

Create a junction table for many-to-many relationship

Create a table called BookAuthor to represent the many-to-many relationship between Book and Author. It should have two columns: BookID and AuthorID. Both columns together form the primary key. Also, add foreign keys referencing Book(BookID) and Author(AuthorID).

DBMS Theory

CREATE TABLE Book (
    BookID INT PRIMARY KEY,
    Title VARCHAR(100),
    Year INT
);

CREATE TABLE Author (
    AuthorID INT PRIMARY KEY,
    Name VARCHAR(100)
);

CREATE TABLE Publisher (
    PublisherID INT PRIMARY KEY,
    PublisherName VARCHAR(100)
);

-- Relationship type variable
relationship_type = 'many-to-many';

-- Create junction table BookAuthor
-- Your code here

Hint

Create a table with two columns and set both as a combined primary key. Add foreign keys to link to the original tables.

4

Add foreign key to Book for Publisher relationship

Add a column called PublisherID to the Book table to represent the one-to-many relationship from Publisher to Book. Make PublisherID a foreign key referencing Publisher(PublisherID).

DBMS Theory

CREATE TABLE Book (
    BookID INT PRIMARY KEY,
    Title VARCHAR(100),
    Year INT
);

CREATE TABLE Author (
    AuthorID INT PRIMARY KEY,
    Name VARCHAR(100)
);

CREATE TABLE Publisher (
    PublisherID INT PRIMARY KEY,
    PublisherName VARCHAR(100)
);

-- Relationship type variable
relationship_type = 'many-to-many';

CREATE TABLE BookAuthor (
    BookID INT,
    AuthorID INT,
    PRIMARY KEY (BookID, AuthorID),
    FOREIGN KEY (BookID) REFERENCES Book(BookID),
    FOREIGN KEY (AuthorID) REFERENCES Author(AuthorID)
);

-- Add PublisherID foreign key to Book
-- Your code here

Hint

Add the PublisherID column inside the Book table and define it as a foreign key.

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