RDBMS – AIM2102 (AY :

2024-25)
Dr. Yadvendra Pratap Singh
Assistant Professor(Senior Scale)
Department of AIML, School of Computer Science and Engineering
Manipal University Jaipur
Week 2 : E-R Model
Database Design Using the E-R Model
Outline
Overview of the Design Process
The Entity-Relationship Model
Complex Attributes
Mapping Cardinalities
Primary Key
Removing Redundant Attributes in Entity Sets
Reducing ER Diagrams to Relational Schemas
Extended E-R Features
Entity-Relationship Design Issues
Alternative Notations for Modeling Data
Other Aspects of Database Design
27/02/2025 Dept. of AIML, SCSE, Manipal University Jaipur 3
Design Phases
• Initial phase -- characterize fully the data needs of the prospective
database users.
• Second phase -- choosing a data model
• Applying the concepts of the chosen data model
• Translating these requirements into a conceptual schema of the database.
• A fully developed conceptual schema indicates the functional requirements of
the enterprise.
• Describe the kinds of operations (or transactions) performed on the data.

27/02/2025 Dept. of AIML, SCSE, Manipal University Jaipur 4

Design Phases (Cont.)
• Final Phase -- Moving from an abstract data model to the
implementation of the database
• Logical Design – Deciding on the database schema.
• Database design requires finding a “good” collection of relation
schemas.
• Business decision – What attributes should we record in the database?
• Computer Science decision – What relation schemas should we have,
and how should the attributes be distributed among the various
relation schemas?
• Physical Design – Deciding on the physical layout of the database.

27/02/2025 Dept. of AIML, SCSE, Manipal University Jaipur 5

Design Alternatives
• In designing a database schema, we must ensure that we
avoid two major pitfalls:
• Redundancy: a bad design may result in repeat information.
• Redundant representation of information may lead to data
inconsistency among the various copies of information
• Incompleteness: a bad design may make certain aspects of
the enterprise difficult or impossible to model.
• Avoiding bad designs is not enough. There may be a large
number of good designs from which we must choose.
27/02/2025 Dept. of AIML, SCSE, Manipal University Jaipur 6
Design Approaches
• Entity Relationship Model
• Models an enterprise as a collection of entities and relationships
• Entity: a “thing” or “object” in the enterprise that is distinguishable
from other objects
• Described by a set of attributes
• Relationship: an association among several entities
• Represented diagrammatically by an entity-relationship diagram.
• Normalization Theory
• Formalize what designs are bad and test them.

27/02/2025 Dept. of AIML, SCSE, Manipal University Jaipur 7

ER model -- Database Modeling
• The ER data mode was developed to facilitate database design by
allowing the specification of an enterprise schema that represents the
overall logical structure of a database.
• The ER data model employs three basic concepts:
• entity sets,
• relationship sets,
• attributes.
• The ER model also has an associated diagrammatic representation,
the ER diagram, which can express the overall logical structure of a
database graphically.
27/02/2025 Dept. of AIML, SCSE, Manipal University Jaipur 8
Entity Sets
• An entity is an object that exists and is distinguishable from other objects.
• Example: specific person, company, event, plant
• An entity set is a set of entities of the same type that share the same properties.
• Example: a set of all persons, companies, trees, holidays
• An entity is represented by a set of attributes, i.e., descriptive properties
possessed by all members of an entity set.
• Example:
instructor = (ID, name, salary )
course= (course_id, title, credits)
• A subset of the attributes form a primary key of the entity set, i.e., uniquely
identifying each set member.

27/02/2025 Dept. of AIML, SCSE, Manipal University Jaipur 9

Entity Sets -- instructor and student

27/02/2025 Dept. of AIML, SCSE, Manipal University Jaipur 10

Representing Entity sets in ER
Diagram
• Entity sets can be represented
graphically as follows:
• Rectangles represent entity
sets.
• Attributes listed inside entity
rectangle
• Underline indicates primary
key attributes

27/02/2025 Dept. of AIML, SCSE, Manipal University Jaipur 11

Relationship Sets (Cont.)
• Example: we define the
relationship set advisor to
denote the associations between
students and the instructors who
act as their advisors.
• Pictorially, we draw a line
between related entities.

27/02/2025 Dept. of AIML, SCSE, Manipal University Jaipur 13

Representing Relationship Sets via
ER Diagrams
• Diamonds represent relationship sets.

27/02/2025 Dept. of AIML, SCSE, Manipal University Jaipur 14

Relationship Sets (Cont.)
• An attribute can also be
associated with a relationship
set.
• For instance, the advisor
relationship set between entity
sets instructor and student may
have the attribute date which
tracks when the student started
being associated with the
advisor

27/02/2025 Dept. of AIML, SCSE, Manipal University Jaipur 15

Relationship Sets with Attributes

27/02/2025 Dept. of AIML, SCSE, Manipal University Jaipur 16

Roles
• Entity sets of a relationship need not be distinct
• Each occurrence of an entity set plays a “role” in the relationship
• The labels “course_id” and “prereq_id” are called roles.

27/02/2025 Dept. of AIML, SCSE, Manipal University Jaipur 17

Degree of a Relationship Set
• Binary relationship
• involve two entity sets (or degree two).
• most relationship sets in a database system are binary.
• Relationships between more than two entity sets are rare. Most
relationships are binary.
• Example: students work on research projects under the guidance of an
instructor.
• relationship proj_guide is a ternary relationship between instructor, student,
and project

27/02/2025 Dept. of AIML, SCSE, Manipal University Jaipur 18

Non-binary Relationship Sets
• Most relationship sets are binary
• There are occasions when it is
more convenient to represent
relationships as non-binary.
• E-R Diagram with a Ternary
Relationship

27/02/2025 Dept. of AIML, SCSE, Manipal University Jaipur 19

Complex Attributes
• Attribute types:
• Simple and composite attributes.
• Single-valued and multivalued attributes
• Example: multivalued attribute: phone_numbers
• Derived attributes
• Can be computed from other attributes
• Example: age, given date_of_birth
• Domain – the set of permitted values for each attribute

27/02/2025 Dept. of AIML, SCSE, Manipal University Jaipur 20

Composite Attributes
• Composite attributes allow us to divide attributes into subparts (other
attributes).

27/02/2025 Dept. of AIML, SCSE, Manipal University Jaipur 21

Representing Complex Attributes in
ER Diagram

27/02/2025 Dept. of AIML, SCSE, Manipal University Jaipur 22

Mapping Cardinality Constraints
• Express the number of entities to which another entity can be
associated via a relationship set.
• Most helpful in describing binary relationship sets.
• For a binary relationship set, the mapping cardinality must be one of
the following types:
• One to one
• One to many
• Many to one
• Many to many

27/02/2025 Dept. of AIML, SCSE, Manipal University Jaipur 23

Mapping Cardinalities

One to one One to many

Note: Some elements in A and B may not be mapped to any elements in the other set

27/02/2025 Dept. of AIML, SCSE, Manipal University Jaipur 24

Mapping Cardinalities

Many to one Many to many

Note: Some elements in A and B may not be mapped to any elements in the other set

27/02/2025 Dept. of AIML, SCSE, Manipal University Jaipur 25

Representing Cardinality Constraints
in ER Diagram
• We express cardinality constraints by drawing either a directed line
(), signifying “one,” or an undirected line (—), signifying “many,”
between the relationship set and the entity set.
• One-to-one relationship between an instructor and a student :
• A student is associated with at most one instructor via the
relationship advisor, and a student is associated with at most one
department via stud_dept

27/02/2025 Dept. of AIML, SCSE, Manipal University Jaipur 26

One-to-Many Relationship
• one-to-many relationship between an instructor and a student
• an instructor is associated with several (including 0) students via an
advisor
• A student is associated with at most one instructor via an advisor,

27/02/2025 Dept. of AIML, SCSE, Manipal University Jaipur 27

Many-to-One Relationships
• In a many-to-one relationship between an instructor and a student,
• an instructor is associated with at most one student via advisor,
• and a student is associated with several (including 0) instructors via
advisor

27/02/2025 Dept. of AIML, SCSE, Manipal University Jaipur 28

Many-to-Many Relationship
• An instructor is associated with several (possibly 0) students via
advisor
• A student is associated with several (possibly 0) instructors via advisor

27/02/2025 Dept. of AIML, SCSE, Manipal University Jaipur 29

27/02/2025 Dept. of AIML, SCSE, Manipal University Jaipur 30