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 4 : ERD Relational Model
ER Diagram
Outline
Reducing ER Diagrams to Relational Schemas
Extended E-R Features

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

Reduction to Relational Schemas
• For each entity set and relationship set in the database design, there
is a unique relation schema to which we assign the name of the
corresponding entity set or relationship set.

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

Representation of Strong Entity Sets
with Simple Attributes.

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

Representation of Strong Entity Sets
with Complex Attributes.

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

Representation of Strong Entity Sets
with Multivalued Attributes.

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

27/02/2025 Dept. of AIML, SCSE, Manipal University Jaipur 8
27/02/2025 Dept. of AIML, SCSE, Manipal University Jaipur 9
 Extended E-R Features

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

Specialization (Top-Down)
• Top-down design process; we designate sub-groupings within an entity set
that are distinctive from other entities in the set.
• These sub-groupings become lower-level entity sets that have attributes or
participate in relationships that do not apply to the higher-level entity set.
• Depicted by a triangle component labeled ISA (e.g., instructor “is a”
person).

• Attribute inheritance – a lower-level entity set inherits all the attributes

and relationship participation of the higher-level entity set to which it is
linked.

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

 Overlapping – employee and student
Disjoint – instructor and secretary
Total and partial

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

Representing Specialization via
Schemas
 Method 1:
• Form a schema for the higher-level entity
• Form a schema for each lower-level entity set, include primary key of higher-level entity set and local
attributes

• Drawback: getting information about, an employee requires accessing two relations, the one corresponding
to the low-level schema and the one corresponding to the high-level schema.

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

Representing Specialization as Schemas (Cont.)

 Method 2:
• Form a schema for each entity set with all local and inherited attributes

• Drawback: name, street and city may be stored redundantly for people who are both students and
employees

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

Generalization (Bottom-Up)
• A bottom-up design process – combine a number of entity sets that
share the same features into a higher-level entity set.
• Specialization and generalization are simple inversions of each other;
they are represented in an E-R diagram in the same way.
• The terms specialization and generalization are used interchangeably.

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

27/02/2025 Dept. of AIML, SCSE, Manipal University Jaipur 16
27/02/2025 Dept. of AIML, SCSE, Manipal University Jaipur 17
27/02/2025 Dept. of AIML, SCSE, Manipal University Jaipur 18
27/02/2025 Dept. of AIML, SCSE, Manipal University Jaipur 19
27/02/2025 Dept. of AIML, SCSE, Manipal University Jaipur 20
Overlapping and Disjoint
Specialization
• The way we depict specialization in an E-R diagram depends on whether an
entity may belong to multiple specialized entity sets or if it must belong to at
most one specialized entity set.

• The former case (multiple sets permitted) is called overlapping specialization,

while the latter case (at most one permitted) is called disjoint specialization.

• For an overlapping specialization (as is the case for student and employee as
specializations of person), two separate arrows are used. For a disjoint
specialization (as is the case for instructor and secretary as specializations of
employees), a single arrow is used.
27/02/2025 Dept. of AIML, SCSE, Manipal University Jaipur 21
Aggregation
• Consider the ternary relationship proj_guide, which we saw earlier
• Suppose we want to record evaluations of a student by a guide on a
project

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

Aggregation
• Now, suppose that each instructor guiding a student on a project is
required to file a monthly evaluation report. We model the evaluation
report as an entity evaluation, with a primary key evaluation id.

• It appears that the relationship sets proj_guide and eval for can be
combined into one single relationship set. Nevertheless, we should
not combine them into a single relationship since some instructor,
student, and project combinations may not have an associated
evaluation.

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

Aggregation
• There is redundant information in the resultant figure; however, since
every instructor, student, and project combination in eval for must
also be in proj_guide. If the evaluation were a value rather than an
entity, we could instead make evaluation a multivalued composite
attribute of the relationship set proj_guide.
• However, this alternative may not be an option if an evaluation may
also be related to other entities; for example, each evaluation report
may be associated with a secretary responsible for further processing
the evaluation report to make scholarship payments.

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

Aggregation
• The best way to model a situation such as the one just described is to use
aggregation.
• Aggregation is an abstraction through which relationships are treated as
higher-level entities.
• For example, we regard the relationship set proj_guide (relating the entity
sets instructor, student, and project) as a higher-level entity set called
proj_guide.
• Such an entity set is treated in the same manner as any other entity set.
• We can then create a binary relationship eval_for between proj_guide and
evaluation to represent which (student, project, instructor) combination an
evaluation is for.
27/02/2025 Dept. of AIML, SCSE, Manipal University Jaipur 25
27/02/2025 Dept. of AIML, SCSE, Manipal University Jaipur 26
27/02/2025 Dept. of AIML, SCSE, Manipal University Jaipur 27
Symbols used in the E-R notation

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

Symbols used in the E-R notation

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

Symbols used in the E-R notation

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

Alternative E-R Notations

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

Alternative E-R Notations

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

 Week 4 : Relational Model
Relational Database Design Model

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

33
Outline
Structure of Relational Databases
Database Schema
Keys
Schema Diagrams

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

Structure of Relational Databases attributes
(or columns)
• A relational database
consists of a collection of tuples
tables, each assigned a (or rows)
unique name.

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

Structure of Relational Databases
• In general, a row in a table represents a relationship among a set of
values. Since a table is a collection of such relationships, there is a close
correspondence between the concept of a table and the mathematical
concept of relation, from which the relational data model takes its
name.
• A relationship between n values is represented mathematically by an n-
tuple of values, i.e., a tuple with n values, which corresponds to a row in
a table.
• Thus, in the relational model the term relation is used to refer to a
table, while the term tuple is used to refer to a row. Similarly, the term
attribute refers to a column of a table.
27/02/2025 Dept. of AIML, SCSE, Manipal University Jaipur 36
Relation Schema and Instance
• Database schema is the logical design of the database, and the database instance
is a snapshot of the data in the database at a given instant in time.
A1, A2, …, An are attributes
R = (A1, A2, …, An ) is a relation schema
Example:
instructor = (ID, name, dept_name, salary)
• A relation instance r defined over schema R is denoted by r (R).
• A table specifies the current values in a relation.
• An element t of relation r is called a tuple and is represented by a row in a table
27/02/2025 Dept. of AIML, SCSE, Manipal University Jaipur 37
Attributes
• The set of allowed values for each attribute is called the domain of the attribute

• Attribute values are (normally) required to be atomic; that is, indivisible

• An attribute that cannot be divided further into meaningful
subcomponents/sub-attributes is an Atomic attribute. Person name non atomi

• The special value null is a member of every domain. Indicated that the value is
“unknown”
• The null value causes complications in the definition of many operations

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

Relations are Unordered
• Order of tuples is irrelevant (tuples
may be stored in an arbitrary order)
• Example: instructor relation with
unordered tuples

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

Database Schema
Example Instance:
• Database schema -- is the logical
structure of the database.
• Database instance -- is a
snapshot of the data in the
database at a given time.
• Example: schema: instructor
(ID, name, dept_name, salary)

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

Keys
• We must have a way to specify how tuples within a given relation are
distinguished. This is expressed in terms of their attributes.
• A superkey is a set of one or more attributes that, taken collectively,
allow us to identify a tuple in the relation uniquely.
• For example, the ID attribute of the relation instructor is sufficient to
distinguish one instructor tuple from another. Thus, ID is a superkey.
On the other hand, the instructor's name attribute is not a superkey
because several instructors might have the same name.

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

Super Key
• Super key is an attribute set that
can uniquely identify a tuple. A
super key is a superset of a
candidate key.
• For example: In the above EMPLOYEE
table, for(EMPLOEE_ID,
EMPLOYEE_NAME), the name of two
employees can be the same, but their
EMPLYEE_ID can't be the same.
Hence, this combination can also be a
key.

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

Candidate key
• A candidate key is an attribute that
uniquely identifies a tuple.
• Except for the primary key, the
remaining attributes are considered a
candidate key. The candidate keys are
as strong as the primary key.
• For example, it is best suited for the
primary key in the EMPLOYEE table.
The other attributes, like SSN,
Passport_Number, License_Number,
etc., are considered candidate keys.

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

Primary key
• It is the first key used to identify one and only
one instance of an entity uniquely.
• An entity can contain multiple keys, as we saw
in the PERSON table.
• The key which is most suitable from those
lists becomes a primary key.
• In the EMPLOYEE table, ID can be the primary
key since it is unique for each employee.
• In the EMPLOYEE table, we can even select
License_Number and Passport_Number as
primary keys since they are also unique.
• For each entity, the primary key selection is
based on requirements and developers.

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

Rules for defining Primary key
• Two rows can’t have the same primary key value
• It must for every row to have a primary key value.
• The primary key field cannot be null.
• The value in a primary key column can never be modified or updated
if any foreign key refers to that primary key.

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

Foreign key
• Foreign keys are the column of the table used to point to the primary key
of another table.
• Every employee works in a specific department in a company, and the
employee and department are two different entities.
• So, we can't store the department's information in the employee table.
That's why we link these two tables through the primary key of one table.
• We add the primary key of the DEPARTMENT table, Department_Id, as a
new attribute in the EMPLOYEE table.
• In the EMPLOYEE table, Department_Id is the foreign key, and both the
tables are related.
27/02/2025 Dept. of AIML, SCSE, Manipal University Jaipur 46
Foreign key

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

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