RDBMS – AIM2102 (AY :

2024-25)
Surabhi Agrawal
Assistant Professor
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

30/10/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.

30/10/2025 Dept. of AIML, SCSE, Manipal University Jaipur 4

Representation of Strong Entity Sets
with Simple Attributes.

30/10/2025 Dept. of AIML, SCSE, Manipal University Jaipur 5

Representation of Strong Entity Sets
with Complex Attributes.

30/10/2025 Dept. of AIML, SCSE, Manipal University Jaipur 6

Representation of Strong Entity Sets
with Multivalued Attributes.

30/10/2025 Dept. of AIML, SCSE, Manipal University Jaipur 7

30/10/2025 Dept. of AIML, SCSE, Manipal University Jaipur 8
30/10/2025 Dept. of AIML, SCSE, Manipal University Jaipur 9
Specialization (Top-Down)
• For instance, a subset of entities within an entity set may have
attributes that are not shared by all the entities in the entity set.
• As an example, the entity set person may be further classified as one
of the following:
• employee.
• student.
• Employee entities may be described further by the attribute salary,
whereas student entities may be described further by the attribute
tot cred. The process of designating subgroupings within an entity set
is called specialization.
30/10/2025 Dept. of AIML, SCSE, Manipal University Jaipur 10
Generalization (Bottom-Up)
• There are similarities between the instructor entity set and the
secretary entity set in the sense that they have several attributes that
are conceptually the same across the two entity sets: namely, the
identifier, name, and salary attributes. This commonality can be
expressed by generalization.

30/10/2025 Dept. of AIML, SCSE, Manipal University Jaipur 11

30/10/2025 Dept. of AIML, SCSE, Manipal University Jaipur 12
30/10/2025 Dept. of AIML, SCSE, Manipal University Jaipur 13
30/10/2025 Dept. of AIML, SCSE, Manipal University Jaipur 14
30/10/2025 Dept. of AIML, SCSE, Manipal University Jaipur 15
30/10/2025 Dept. of AIML, SCSE, Manipal University Jaipur 16
30/10/2025 Dept. of AIML, SCSE, Manipal University Jaipur 17
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.

30/10/2025 Dept. of AIML, SCSE, Manipal University Jaipur 18

30/10/2025 Dept. of AIML, SCSE, Manipal University Jaipur 19
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.

30/10/2025 Dept. of AIML, SCSE, Manipal University Jaipur 20

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.

30/10/2025 Dept. of AIML, SCSE, Manipal University Jaipur 21

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.
30/10/2025 Dept. of AIML, SCSE, Manipal University Jaipur 22
30/10/2025 Dept. of AIML, SCSE, Manipal University Jaipur 23
30/10/2025 Dept. of AIML, SCSE, Manipal University Jaipur 24
Symbols used in the E-R notation

30/10/2025 Dept. of AIML, SCSE, Manipal University Jaipur 25

Symbols used in the E-R notation

30/10/2025 Dept. of AIML, SCSE, Manipal University Jaipur 26

Symbols used in the E-R notation

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

Alternative E-R Notations

30/10/2025 Dept. of AIML, SCSE, Manipal University Jaipur 28

Alternative E-R Notations

30/10/2025 Dept. of AIML, SCSE, Manipal University Jaipur 29

 Week 4 : Relational Model
Relational Database Design Model

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

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

30/10/2025 Dept. of AIML, SCSE, Manipal University Jaipur 31

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

30/10/2025 Dept. of AIML, SCSE, Manipal University Jaipur 32

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.
30/10/2025 Dept. of AIML, SCSE, Manipal University Jaipur 33
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
30/10/2025 Dept. of AIML, SCSE, Manipal University Jaipur 34
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
30/10/2025 Dept. of AIML, SCSE, Manipal University Jaipur 35
Relations are Unordered
• Order of tuples is irrelevant (tuples
may be stored in an arbitrary order)
• Example: instructor relation with
unordered tuples

30/10/2025 Dept. of AIML, SCSE, Manipal University Jaipur 36

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)

30/10/2025 Dept. of AIML, SCSE, Manipal University Jaipur 37

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.

30/10/2025 Dept. of AIML, SCSE, Manipal University Jaipur 38

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.

30/10/2025 Dept. of AIML, SCSE, Manipal University Jaipur 39

Candidate key
• A candidate key is an attribute or
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, id 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.
30/10/2025 Dept. of AIML, SCSE, Manipal University Jaipur 40
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.

30/10/2025 Dept. of AIML, SCSE, Manipal University Jaipur 41

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.

30/10/2025 Dept. of AIML, SCSE, Manipal University Jaipur 42

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.
30/10/2025 Dept. of AIML, SCSE, Manipal University Jaipur 43
Foreign key

30/10/2025 Dept. of AIML, SCSE, Manipal University Jaipur 44

30/10/2025 Dept. of AIML, SCSE, Manipal University Jaipur 45