DBMS 3 | ER Diagram to Relational Schema

This slide explains the conversion procedure from ER Diagram to Relational Schema.
1. Entity set to Relation
2. Relationship set to Relation
3. Attributes to Columns, Primary key, Foreign Keys

1. Database Management System
ER Diagram →Relational Schema
Mohammad Imam Hossain, Lecturer, Dept. of CSE, UIU

2. Design Phases
2
Mohammad Imam Hossain, Lecturer, Dept. of CSE, UIU
Physical-design
Phase
(Relational Database)
Logical-design Phase
(Relational Schema)
Conceptual-design
Phase
(ER Diagram)
User
Requirements
Specification

3. Database Schema &Data Model
Database Schema
Describes the overall design of the database at different levels(physical, logical, view)
Data Model
Describes a way to design database at physical, logical and view level. It is a collection of conceptual tools and
languages for describing data, data relationships, data semantics, and consistency constraints.
High-level conceptual data model →ER Model
Record based logical data model →Relational Model
3
Mohammad Imam Hossain, Lecturer, Dept. of CSE, UIU

4. E-R Diagram vsRelational Schema
4
Mohammad Imam Hossain, Lecturer, Dept. of CSE, UIU
ER Diagram RelationalSchema
The main components of ER Model are:
•Entity Set –setof similar entities
•Relationship Set –set of similar relationships
•Attributes –including different types of keys
The maincomponents of Relational Model are:
•Relations (tables)
•Tuple (row, unordered)
•Attributes (columns, unordered)
•Domain (column type)
•Primary Key
•Foreign Key
ER Model describes Mapping Cardinality. RelationalModel does not describe Mapping Cardinality.

5. E-R Diagram vsRelational Schema
5
Mohammad Imam Hossain, Lecturer, Dept. of CSE, UIU
ER Diagram :
Relational Schema :

6. Relational Schema >>Strong Entity Set
6
Mohammad Imam Hossain, Lecturer, Dept. of CSE, UIU

7. Relational Schema >>Weak Entity Set
7
Mohammad Imam Hossain, Lecturer, Dept. of CSE, UIU

8. Relational Schema >>Composite Attribute
8
Mohammad Imam Hossain, Lecturer, Dept. of CSE, UIU

9. Relational Schema >>Multivalued Attribute
9
Mohammad Imam Hossain, Lecturer, Dept. of CSE, UIU

10. Relational Schema >>Derived Attribute
10
Mohammad Imam Hossain, Lecturer, Dept. of CSE, UIU

11. Relational Schema >>One to One Relationship Set
11
Mohammad Imam Hossain, Lecturer, Dept. of CSE, UIU
Nullable

12. Relational Schema >>One to Many Relationship Set
12
Mohammad Imam Hossain, Lecturer, Dept. of CSE, UIU
Nullable

13. Relational Schema >>Many to Many Relationship Set
13
Mohammad Imam Hossain, Lecturer, Dept. of CSE, UIU

14. Relational Schema >>Recursive Relationship Set
14
Mohammad Imam Hossain, Lecturer, Dept. of CSE, UIU

15. Relational Schema >>Identifying Relationship Set
15
Mohammad Imam Hossain, Lecturer, Dept. of CSE, UIU

16. E-R Diagram →Relational Schema
16
Mohammad Imam Hossain, Lecturer, Dept. of CSE, UIU

17. 17
THANKS!
Any questions?
Email : [email protected]
References:
▪Database System Concepts by S. Sudarshan, Henry F. Korth, Abraham Silberschatz
▪Database Systems The Complete Book by Ullman, Widomand Hector
▪https://www.lucidchart.com/pages/er-diagrams
▪https://www.vertabelo.com/blog/chen-erd-notation/