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 3 : 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
Total and Partial Participation
• Total participation (indicated by double line): every entity in the entity set participates in at
least one relationship in the relationship set

• participation of student in advisor relation is total

• every student must have an associated instructor
• Partial participation: some entities may not participate in any relationship in the
relationship set
• Example: participation of instructor in advisor is partial
27/02/2025 Dept. of AIML, SCSE, Manipal University Jaipur 4
Notation for Expressing More
Complex Constraints
• A line may have an associated minimum and maximum cardinality, shown in the
form l..h, where l is the minimum and h the maximum cardinality
• A minimum value of 1 indicates total participation.
• A maximum value of 1 indicates that the entity participates in at most one relationship
• A maximum value of * indicates no limit.
• Example

• Instructor can advise 0 or more students. A student must have 1 advisor; cannot have
multiple advisors
27/02/2025 Dept. of AIML, SCSE, Manipal University Jaipur 5
Primary Key
• Primary keys provide a way to specify how entities and relations are
distinguished. We will consider:
• Entity sets
• Relationship sets.
• Weak entity sets

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

Primary key for Entity Sets
• By definition, individual entities are distinct.
• From database perspective, the differences among them must be
expressed in terms of their attributes.
• The values of the attribute values of an entity must be such that they
can uniquely identify the entity.
• No two entities in an entity set are allowed to have exactly the same value for
all attributes.
• A key for an entity is a set of attributes that suffice to distinguish
entities from each other

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

Choice of Primary key for Binary
Relationship
• Many-to-Many relationships. The preceding union of the primary
keys is a minimal superkey and is chosen as the primary key.
• One-to-Many relationships . The primary key of the “Many” side is a
minimal superkey and is used as the primary key.
• Many-to-one relationships. The primary key of the “Many” side is a
minimal superkey and is used as the primary key.
• One-to-one relationships. The primary key of either one of the
participating entity sets forms a minimal superkey, and either one can
be chosen as the primary key.

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

Weak Entity Sets
• Consider a section entity, which is uniquely identified by a course_id,
semester, year, and sec_id.
• Clearly, section entities are related to course entities. Suppose we create
a relationship set sec_course between entity sets section and course.
• Note that the information in sec_course is redundant, since section
already has an attribute course_id, which identifies the course with
which the section is related.
• One option to deal with this redundancy is to get rid of the relationship
sec_course; however, by doing so the relationship between section and
course becomes implicit in an attribute, which is not desirable.

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

Weak Entity Sets (Cont.)
• An alternative way to deal with this redundancy is to not store the attribute
course_id in the section entity and to only store the remaining attributes section_id,
year, and semester.
• However, the entity set section then does not have enough attributes to identify a particular
section entity uniquely
• To deal with this problem, we treat the relationship sec_course as a special
relationship that provides extra information, in this case, the course_id, required to
identify section entities uniquely.
• A weak entity set is one whose existence is dependent on another entity, called its
identifying entity
• Instead of associating a primary key with a weak entity, we use the identifying entity,
along with extra attributes called discriminator to uniquely identify a weak entity.

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

Weak Entity Sets (Cont.)
• An entity set that is not a weak entity set is termed a strong entity set.
• Every weak entity must be associated with an identifying entity; that is, the
weak entity set is said to be existence dependent on the identifying entity set.
• The identifying entity set is said to own the weak entity set that it identifies.
• The relationship associating the weak entity set with the identifying entity set is
called the identifying relationship.
• Note that the relational schema we eventually create from the entity set
section does have the attribute course_id, for reasons that will become clear
later, even though we have dropped the attribute course_id from the entity set
section.

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

Expressing Weak Entity Sets
• In E-R diagrams, a weak entity set is depicted via a double rectangle.
• We underline the discriminator of a weak entity set with a dashed
line.
• The relationship set connecting the weak entity set to the identifying
strong entity set is depicted by a double diamond.
• Primary key for section – (course_id, sec_id, semester, year)

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

Redundant Attributes
• Suppose we have entity sets:
• student, with attributes: ID, name, tot_cred, dept_name
• department, with attributes: dept_name, building, budget
• We model the fact that each student has an associated department
using a relationship set stud_dept
• The attribute dept_name in student below replicates information
present in the relationship and is therefore redundant
• and needs to be removed.
• BUT: when converting back to tables, in some cases the attribute gets
reintroduced, as we will see later.
27/02/2025 Dept. of AIML, SCSE, Manipal University Jaipur 13
Redundant Attributes

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

E-R Diagram for a
University Enterprise

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

Reduction to Relation Schemas
• Entity sets and relationship sets can be expressed uniformly as
relation schemas that represent the contents of the database.
• A collection of schemas can represent a database that conforms to an
E-R diagram.
• Each entity set and relationship set has a unique schema assigned the
name of the corresponding entity set or relationship set.
• Each schema has a number of columns (generally corresponding to
attributes), which have unique names.

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

Representing Entity Sets
• A strong entity set reduces to a schema with the same attributes
student(ID, name, tot_cred)
• A weak entity set becomes a table that includes a column for the
primary key of the identifying strong entity set
section ( course_id, sec_id, sem, year )
• Example

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

Representing Relationship Sets
• A many-to-many relationship set is represented as a schema with
attributes for the primary keys of the two participating entity sets,
and any descriptive attributes of the relationship set.
• Example: schema for relationship set advisor

advisor = (s_id, i_id)

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

Redundancy of Schemas
• Many-to-one and one-to-many relationship sets that are total on the
many-side can be represented by adding an extra attribute to the
“many” side, containing the primary key of the “one” side
• Example: Instead of creating a schema for relationship set inst_dept,
add an attribute dept_name to the schema arising from entity set
instructor
• Example

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

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