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

Need a 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

Need a 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

Need a 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

Need a hint?

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