ER to Relational Database Conversion: Complete Guide with SQL Examples

Conversion of ER relation to Relational Database and How to use it properly

There are basically three types of relation in a database:

1:1 Relation

In relational database term,

• 1:1 Relation means that one record in a table is related to one and only one record in another table.

In ER diagram term,

• 1:1 Relation means that one entity in an entity set is related to one and only one entity in another entity set.

Conversion of relation from ER to Relational Database: Process

• Consider we have two entities, A and B, with a 1:1 relationship.
• Create a table for each entity with their attributes.
• Add the primary key of either entity as a foreign key in the other entity.

Example:

1: N Relation

In relational database term,

• 1:N Relation means that one record in a table can be related to multiple records in another table.

In ER diagram term,

• 1:N Relation means that one entity in an entity set is related to multiple entities in another entity set.

Conversion of relation from ER to Relational Database:

• Process
• Consider we have two entities, A and B, with a 1:N relationship.
• Create a table for each entity with their attributes.
• Add the primary key of the entity on the “one” side (A) as a foreign key in the entity on the “many” side (B). Example:

N: M Relation

In relational database term,

• N:M Relation means that Consider we have two entities, A and B, Then one record in table A can be related to multiple records in table B and one record in table B can be related to multiple records in table A.

-Simply, N:M Relation means that one record in a table can be related to multiple records in another table and vice versa.

In ER diagram term,

• N:M Relation means that one entity in an entity set is related to multiple entities in another entity set and vice versa.

Conversion of relation from ER to Relational Database:

• Consider we have two entities, A and B, with a N:M relationship.
• Create a table for each entity with their attributes.
• Create a new table (junction table) to represent the relationship.
• The junction table will have two foreign keys, one referencing the primary key one one entity (A) and the other referencing the primary key of the other entity (B) to maintain the relationship.

Example:

Practical Example (The SQL Code)

One-to-One Relationship Example

1. Create the Person and Passport tables with a 1:1 relationship:

CREATE TABLE Person (
    person_id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE Passport (
    passport_id INT PRIMARY KEY,
    counry VARCHAR(50),
    person_id INT UNIQUE,
    issue_date DATE,
    FOREIGN KEY (person_id) REFERENCES Person(person_id)
);

2. Querying the 1:1 Relationship

1. Give the name of the person and their passport details:

SELECT name,Person.person_id,passport_id,counry,issue_date
FROM Person
INNER JOIN Passport ON Person.person_id = Passport.person_id;

2. Find a Person with a specific passwort ID:

SELECT name, passport_id, counry, issue_date
FROM Person
INNER JOIN Passport ON Person.person_id = Passport.person_id
WHERE Passport.passport_id = 1;

3. Find name of all person of a specific country:

SELECT name
FROM Person
INNER JOIN Passport ON Person.person_id = Passport.person_id
WHERE counry = 'USA';

4. Find all passport details of a specific person:

SELECT passport_id, counry, issue_date
FROM Passport INNER JOIN Person ON Passport.person_id = Person.person_id
WHERE Person.person_id = 1;

5. Find all passport details of a person named ‘John Doe’:

SELECT passport_id, counry, issue_date
FROM Passport INNER JOIN Person ON Passport.person_id = Person.person_id
WHERE Person.name = 'John Doe';

One-to-Many Relationship Example

1. Create the Author and Book tables with a 1:N relationship:

CREATE TABLE Author (
    author_id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE Book (
    book_id INT PRIMARY KEY,
    title VARCHAR(100),
    author_id INT,
    FOREIGN KEY (author_id) REFERENCES Author(author_id)
);

2. Querying the 1:N Relationship

1. List all books with their authors:

SELECT name,title FROM Author
INNER JOIN Book ON Author.author_id = Book.author_id;

2. Find authors who have written a specific book:

SELECT name FROM Author
INNER JOIN Book ON Author.author_id = Book.author_id
WHERE book_id = 1;

3. Find author of a specific book title:

SELECT name FROM Author
INNER JOIN Book ON Author.author_id = Book.author_id
WHERE Book.title = 'Book Title';

4. List all books by a specific author:

SELECT title
FROM Book INNER JOIN Author ON Book.author_id = Author.author_id
WHERE Author.author_id = 1;

5. Find all books written by authors with a specific name:

SELECT title
FROM Book INNER JOIN Author ON Book.author_id = Author.author_id
WHERE Author.name = 'Author Name';

Many-to-Many Relationship Example

1. Create the Student, Course, and Enrollment tables with a N:M relationship:

CREATE TABLE Student (
    student_id INT PRIMARY KEY,
    name VARCHAR(100)
);
CREATE TABLE Course (
    course_id INT PRIMARY KEY,
    title VARCHAR(100)
);
CREATE TABLE Enrollment (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES Student(student_id),
    FOREIGN KEY (course_id) REFERENCES Course(course_id)
);

2. Querying the N:M Relationship

1. List all students and their enrolled courses:

SELECT name, title
FROM Student
INNER JOIN Enrollment ON Student.student_id = Enrollment.student_id
INNER JOIN Course ON Enrollment.course_id = Course.course_id;

2. Find all courses a specific student is enrolled in:

SELECT title
FROM Course INNER JOIN Enrollment ON Course.course_id = Enrollment.course_id
INNER JOIN Student ON Enrollment.student_id = Student.student_id
WHERE Student.student_id = 1;

3. Find all students enrolled in a specific course:

SELECT name
FROM Student INNER JOIN Enrollment ON Student.student_id = Enrollment.student_id
INNER JOIN Course ON Enrollment.course_id = Course.course_id
WHERE Course.course_id = 1;

4. Find all courses for students with a specific name:

SELECT title
FROM Course INNER JOIN Enrollment ON Course.course_id = Enrollment.course_id
INNER JOIN Student ON Enrollment.student_id = Student.student_id
WHERE Student.name = 'Student Name';

5. Find all students enrolled in courses with a specific title:

SELECT name
FROM Student INNER JOIN Enrollment ON Student.student_id = Enrollment.student_id
INNER JOIN Course ON Enrollment.course_id = Course.course_id
WHERE Course.title = 'Course Title';