RDBMS

Module :I
Topic : Data Models – E R Model
Faculty :Dr.S.Deepa Kanmani,IT

Database Management Systems 1

Database Management Systems 2
 TOPIC OBJECTIVE

• At the end of this presentation students should be

able to:

✔ Understand the data mode and different types of

Model used in DBMS.

Database Management Systems 3

DATABASE LIFECYCLE

Database Management Systems 4

REQUIREMENT PHASE

Involves in accessing the

informational needs of an
organisation so that a database
can be designed to meet those
needs.

Database Management Systems 5

Database Management Systems 6
Database Management Systems 7
TOP DOWN VS BOTTOM UP APPROACH

Database Management Systems 8

 DATA MODELS

✔ The data model is considered a logical structure

for creating a database.

✔ The data model includes entities, attributes,

constraints, relationships, etc.

✔ The data models are used to represent the data

and how it is stored in the database, how data is
accessible and updated in the database
management system.

Database Management Systems 9

Database Management Systems 10
 HIERARCHIAL MODEL

✔ The Hierarchical model is inspired from tree

based data structure format.

✔ Where in there is a single root node and other

data is linked to the same and expands like a
tree.
✔ A child node data may only have a single parent node
✔ For any parent node one to many relationship must
be maintained.

Database Management Systems 11

 EXAMPLE

Database Management Systems 12

 EXAMPLE

Database Management Systems 13

 EXAMPLE

Database Management Systems 14

 ADVANTAGES

✔ The model allows us easy addition and

deletion of new information.
• Data at the top of the Hierarchy is very fast
to access.
• It worked well with linear data storage
mediums such as tapes.
• It relates well to anything that works
EXAMPLE: There is a president with many managers
through a one below to many
them, and thoserelationships.
managers have many
employees below them, but each employee has only
one manager.

Database Management Systems 15

 DISADVANTAGES

✔ It requires data to be repetitively stored in many

different entities.
✔
• Searching for data requires the DBMS to run
through the entire model from top to bottom
until the required information is found, making
queries very slow.
✔
• This model support only one to many
relationships, many to many relationships are not
supported.

Database Management Systems 16

 NETWORK MODEL

✔ Network model was made to overcome the

drawbacks of hierarchical model.

✔ This works more like a graph rather than a tree.

✔ As the name suggests there is network based

looped relationship between various data linked
to one another.
✔ A child node may have more than one parent.
✔ There can be many to many relationships between
data.

Database Management Systems 17

 EXAMPLE

Database Management Systems 18

 EXAMPLE

Database Management Systems 19

 ADVANTAGES

✔ The network model is conceptually simple and easy to

design.
✔
• The network model can handle the one to many and
many to many relationships which is real help in modelling
the real-life situations.
✔
• The data access is easier and flexible than the
hierarchical model
✔ .
• The network model is better than the hierarchical model
in isolating the programs from the complex physical storage
details.

Database Management Systems 20

 DISADVANTAGES

✔ All the records are maintained using pointers and

hence the whole database structure becomes
very complex.
✔
• The insertion, deletion and updating
operations of any record require the large
number of pointers adjustments.
✔
• The structural changes to the database is very
difficult.

Database Management Systems 21

 RELATIONAL MODEL

✔ The relational model was first outlined by EF

Codd in 1970 and since then it was the most
widely used data model and in fact, the only used
database management system today in the
world .

✔ The basic idea between this relational data model

is simple two-dimensional tables, also called as
relations which consist of rows and columns

Database Management Systems 22

 RELATIONAL MODEL
• Relational Model (RM) represents the database
as a collection of relations.
•
• A relation is nothing but a table of values.

• Every row in the table represents a collection of

related data values.

• These rows in the table denote a real-world

entity or relationship.

Database Management Systems 23

 EXAMPLE

Database Management Systems 24

CONCEPTS OF RELATIONAL MODEL

Database Management Systems 25

 ADVANTAGES

✔ Data integrity for accuracy and consistency

✔ No data redundancy
✔ Access control and integrity in the form of constraints
which enables validation before entering and
accessing the data
✔ Provides high security
✔ Supports to store any types(numbers, characters,
date, images, audio, text files )
✔ Data can be managed and used by several users at a
time
✔ Data can be shared across several platforms

Database Management Systems 26

 DISADVANTAGES

• Mapping of objects in relational database is very difficult.

• Object oriented paradigm is missing in relation model.

• Data Integrity is difficult to ensure with Relational database.

• Relational Model is not suitable for huge database but suitable for
small database.

• Hardware overheads are incurred which make it costly.

• Ease of design can lead to bad design.

• Relational database system hides the implementation complexities

and the physical data storage details from the users.

Database Management Systems 27

 OBJCET ORIENTED MODEL

✔ The increasing complexity of the code in the

modern age, where we try to create real time
scenerios.
✔
✔ Specially in Artificial Intelligence, Machine
learning or Image processing.

✔ We need a database that can also represent the

real work scenerios very clearly and hence it is
solved by having an object oriented database
model.

Database Management Systems 28

 OBJCET ORIENTED MODEL

✔ An object database is a system in which information is

represented in the form of objects as used in
object-oriented programming.

✔ Object oriented databases are different from relational

databases which are table-oriented.

✔ The object-oriented data model is based on the

object-oriented- programming language concept, which is
now in wide use.

✔ The object-oriented data model also supports a rich type

system, including structured and collection types.

Database Management Systems 29

OO MODEL VS RELATIONAL MODEL

Database Management Systems 30

 EXAMPLE

Database Management Systems 31

 ADVANTAGES

✔ Object database can handle different types of data while relational data
base handles a single data.

✔ Unlike traditional databases like hierarchical, network or relational, the

object-oriented databases can handle the different types of data, for
example, pictures, voice video, including text, numbers and so on.
✔
• Object-oriented databases provide us code reusability, real world
modelling, and improved reliability and flexibility.
✔
• The object-oriented database is having low maintenance costs as
compared to other model because most of the tasks within the system are
encapsulated

Database Management Systems 32

 ENTITY RELATIONSHIP MODEL

• ER Model stands for Entity Relationship Model is a

high-level conceptual data model diagram.
• Entity relationship model is widely used data
model and it provides a convenient graphical
representation of data relationship and
constraints.
• ER model helps to systematically analyze data
requirements to produce a well-designed
The ER Model represents real-world entities
database. and the relationships between them.
•
Database Management Systems 33
ER NOTATIONS

Database Management Systems 34

STEPS TO DEVELOP AN ER MODEL

Database Management Systems 35

MAIN COMPONENTS OF DATABASE

Database Management Systems 36

 ENTITY

– Entity is a real object representation in an Entity

Relationship diagram.
– Example:
✔ It can be anything like –
✔ Teachers
✔ Courses
✔ Buildings
✔ Classrooms etc

For example if we want to create a database for a college in that

case students studying in the college will be considered as an entity.

Database Management Systems 37

 EXAMPLE

Database Management Systems 38

 ENTITY SET

• Entity Set is a collection or a group of

‘entities’ sharing exactly the ‘same set of
attributes’.

• All entities can be distinctly identified in an

entity set.

Database Management Systems 39

ENTITY SET-EXAMPLE

Database Management Systems 40

Database Management Systems 41
TYPES OF ENTITY

Database Management Systems 42

 STRONG ENTITY

• A strong entity set is an entity set that

contains sufficient attributes to uniquely
identify all its entities.

• In other words, a primary key exists for a

strong entity set.

• Primary key of a strong entity set is

represented by underlining it.
Database Management Systems 43
 REPRESNETATION

• The strong entity is represented by a single

rectangle.
•
• Relationship between two strong entities is
represented by a single diamond.

Database Management Systems 44

 EXAMPLE

Database Management Systems 45

 WEAK ENTITY

• A weak entity set is an entity set that does not contain sufficient
attributes to uniquely identify its entities.

• In other words, a primary key does not exist for a weak entity set.
• However, it contains a partial key called as a discriminator.

• Discriminator can identify a group of entities from the entity set.

• Discriminator is represented by underlining with a dashed line.

Database Management Systems 46

 REPRESNETATION

• A double rectangle is used for representing

a weak entity set

• The double diamond symbol is used for

representing the relationship between a strong
entity and weak entity which is known as
identifying relationship

• Double lines are used for presenting

the connection with a weak entity set with
relationship

Database Management Systems 47

EXAMPLE 1

Database Management Systems 48

EXAMPLE 2

Database Management Systems 49

STRONG VS WEAK ENTITY

Database Management Systems 50

 ATTRIBUTES

• Attributes are the properties of any given

entity.
• EXAMPLE:
✔ ID
✔ email ID
✔ Age
✔ Address
✔ Phone Number etc

Database Management Systems 51

 EXAMPLE

Database Management Systems 52

TYPES OF ATTRIBUTES

Database Management Systems 53

 SIMPLE VALUED ATTRIBUTE

• Simple attributes are those attributes which

can not be divided further.

• Example
• All the attributes are simple attributes as they
can not be divided further.

Database Management Systems 54

 EXAMPLE

Database Management Systems 55

 COMPOSITE ATTRIBUTE

• Composite attributes are those attributes

which are composed of many other simple
attributes.

• Example
• The attributes “Name” and “Address” are
composite attributes as they are composed of
many other simple attributes.

Database Management Systems 56

 EXAMPLE

Database Management Systems 57

 EXAMPLE

Database Management Systems 58

 SINGLE VALUED ATTRIBUTE

• Single valued attributes are those attributes

which can take only one value for a given
entity from an entity set.

• Example
• All the attributes are single valued attributes
as they can take only one specific value for
each entity.

Database Management Systems 59

 EXAMPLE

Database Management Systems 60

 MULTI VALUED ATTRIBUTE

• Multi valued attributes are those attributes

which can take more than one value for a
given entity from an entity set.

• Example
• The attributes “Mob_no” and “Email_id” are
multi valued attributes as they can take more
than one values for a given entity.

Database Management Systems 61

 EXAMPLE

Database Management Systems 62

 DERIVED ATTRIBUTE

• Derived attributes are those attributes which

can be derived from other attribute(s).

• Example
• The attribute “Age” is a derived attribute as it
can be derived from the attribute “DOB”.

Database Management Systems 63

 EXAMPLE

Database Management Systems 64

 KEY ATTRIBUTE

• Key attributes are those attributes which can

identify an entity uniquely in an entity set.

• Example
• The attribute “Roll_no” is a key attribute as it
can identify any student uniquely.

Database Management Systems 65

 EXAMPLE

Database Management Systems 66

 RELATIONSHIP

• The association between different entities that are

existing in a database is depicted by relationship.
• Diamond box with entities connected to its edges as
depicted in the image.

• Example :
• In a university there may be thousands of students,
which have enrolled in a few subjects each year.

Database Management Systems 67

 EXAMPLE

Database Management Systems 68

REPRESENTATION

Database Management Systems 69

 RELATIONSHIP SET

• Relationship set is mapping of different

entity set to the others with the help of
connected lines are depicted in the image.

Database Management Systems 70

DEGREE OF A RELATIONSHIP

Database Management Systems 71

 UNARY RELATIONSHIP

• When there is only one entity set

participating in a relationship then such type
of relationship is called unary relationship

Database Management Systems 72

 EXAMPLE

Database Management Systems 73

 BINARY RELATIONSHIP

• When there are exactly two entity sets

participating in a relationship then such type of
relationship is called binary relationship

• EXAMPLE:
• A teacher teaches a subject here 2 entities are
teacher and subject for the relationship teacher
teaches subject

Database Management Systems 74

 EXAMPLE

Database Management Systems 75

 TERNARY RELATIONSHIP

• When there are exactly three entity sets

participating in a relationship then such type of
relationship is called ternary relationship

• EXAMPLE:
• A patient goes to a doctor and doctor prescribes the medicine to the
patient, three entities Doctor, patient and medicine are involved in the
relationship “prescribes”

Database Management Systems 76

 EXMAPLE

Database Management Systems 77

 N-ARY RELATIONSHIP

• When a large number of entity sets are

participating in a relationship, then such type
of relationship is called an n-ary relationship.

• EXAMPLE:
• A patient goes to a doctor and doctor prescribes the medicine and
diagnosis to the patient, four entities Doctor, patient and medicine,
diagnostics are involved in the relationship “prescribes”

Database Management Systems 78

 EXAMPLE

Database Management Systems 79

 RECURSIVE RELATIONSHIP

• A Recursive relationship is nothing but, simply

an entity is having a relationship with self.

• EXAMPLE:
• Student can be a class monitor and handle other students but a person
who is working as a class leader is itself a student of the class and hence
a class monitor has a recursive relationship of entity student

Database Management Systems 80

 EXAMPLE

Database Management Systems 81

 EXAMPLE

Database Management Systems 82

CONSTRAINTS

Database Management Systems 83

 MAPPING CONSTRAINTS

• Mapping constraints defines how many

entities can be related to another entity to a
relationship.

Database Management Systems 84

MAPPING CONSTRAINTS

Database Management Systems 85

 ONE TO ONE CARDINALITY

• When a single instance of an entity is

associated with a single instance of another
entity, then it is called as one to one
cardinality

Database Management Systems 86

 EXAMPLE

Database Management Systems 87

 EXAMPLE

Database Management Systems 88

 ONE TO MANY

• When is a single instance of an entity is associated

with more than one instance of another entity then
this type of relationship is called one to many
relationships.

• EXAMPLE:
• Many students can study in a single college but the
student cannot apply to more than one college at the
same time

Database Management Systems 89

 EXAMPLE

Database Management Systems 90

 EXAMPLE

Database Management Systems 91

 MANY TO ONE

• When there are multiple entities in a given

entity set in relationship with only one entity
of another or same entity set.

• EXAMPLE:
• One course can be taken by any number of
students but only one course can be allotted
to one student
Database Management Systems 92
 EXAMPLE

Database Management Systems 93

 EXAMPLE

Database Management Systems 94

 MANY TO MANY

• When more than one instance of an entity is

associated with more than one instance of
another entity then it is called many to many
relationships

• EXAMPLE:
• A student can take more than one course and the
single course can be taken by any number of
students this relationship will be many to many
relationship
Database Management Systems 95
 EXAMPLE

Database Management Systems 96

 EXAMPLE

Database Management Systems 97

RELATIONSHIP NOTATIONS

Database Management Systems 98

 PARTICIPATION

• In a Relationship, Participation
constraint specifies the presence of an entity
when it is related to another entity in a
relationship type.

Database Management Systems 99

 TYPES

Database Management Systems 100

 TOTAL PARTICIPATION

• All the entities in the entity set must participate in the

relationship.
• It is represented by a double line joining entity and
relationship.

• EXAMPLE
• The university forces, you to necessarily enrol in
atleast 1 course, in that case it will be called total
participation that student end.

Database Management Systems 101

 PARTIAL PARTICIPATION

• It is when there maybe cases when some entities may and

some entities may not participate in a relationship.

• This is represented by single line joining entity and

relationship.

• EXAMPLE
• In university there might be some courses where
enrollments are not made

Database Management Systems 102

 EXAMPLE

Database Management Systems 103

Database Management Systems 104
LIBARARY MANAGEMENT SYSTEM

Database Management Systems 105

OREDRING PRODUCTS_ ER DIAGRAM

Database Management Systems 106

 EXTENDED ER MODEL

• With large databases it was realised that the

ER model was become a little more complex
and inconvenient to use.
• Thus by programmers there were some
additions in the ER model were suggested to
reduce down on the complexity of the
program, some new concepts were added

Database Management Systems 107

 TYPES

Database Management Systems 108

 GENERALISATION

• In generalisation we combine lower level

entities to form a higher level entity. Thus its
clear that it follows a bottom up approach.

• EXAMPLE:
• In a bank there are two different types of accounts – Current and
Savings, combine to form a super entity Account.
• It may also be possible that the higher level entity may also combine
with further entity to form a one more higher level entity.

Database Management Systems 109

 DESIGN STEPS FOR GENERALISATION

✔ Define some entities with attributes

✔ Identify common attributes between these

entities and create a super-entity, so that all
the remaining entities in some way related to
the super-entity

✔ Add Relationship between entities

Database Management Systems 110

 EXAMPLE

Database Management Systems 111

 SPECIALISATION

• While generalisation may follow a bottom up

approach.
• Specialisation is a top-down design approach
where one higher level entity can be broken into
two or more lower level entities so that subsets
of entities that share some distinguishing
characteristics can be identified

• EXAMPLE
• Employee may be decomposed to further as current employee entity and ex
employee entity.

Database Management Systems 112

 DESIGN STEPS FOR SPECIALISATION

✔ Generally, superclasses are defined first

✔ Define subclass and its related attributes

✔ IS-A Relationship is linked between these

entities

Database Management Systems 113

 EXAMPLE

Database Management Systems 114

 AGGREGATION

• Aggregation is a design process in which the

relationship between two entities is treated
as a single entity

• EXAMPLE
• University offering course can be considered a same entity, when viewed
from a student entity perspective.

Database Management Systems 115

 EXAMPLE

Database Management Systems 116

 EXAMPLE

• EXAMPLE
• If students visit a coaching institute then he
shows interest not only to inquire about the
course alone or not only just coaching centre,
he will definitely enquire the details about
both the coaching institute and the details of
the concerned course

Database Management Systems 117

 EXAMPLE

Database Management Systems 118

SPECIALIZATION VS GENERALISATION

Database Management Systems 119

Database Management Systems 120
 ER MODEL TO RELATIONAL MODEL

• Conversion of ER model to Relational model is

nothing but converting ER diagram to tabular
form.
• This is done because tables can be easily
implemented by RDBMS like MySQL,
Oracle etc.

Database Management Systems 121

CONVERSION GUIDELINES

Database Management Systems 122

 STRONG ENTITY SET WITH ONLY SIMPLE ATTRIBUTES

• In relational model only one table is

required to represent a strong entity set with
simple attributes.
• In this
– Attributes are taken as columns of the table
– Key attribute is declared as primary key of the
table

Database Management Systems 123

 EXAMPLE

Relational Schema
Student(student_id, stu_name, gender)

Database Management Systems 124

 EXAMPLE

Database Management Systems 125

 STRONG ENTITY SET WITH ONLY COMPOSITE ATTRIBUTES

• In relational model only one table is required to

represent a strong entity set with composite
attributes.
• In this
– Simple attributes are taken as columns of the table
and
– Simple attributes of the composite attributes are
considered as columns but not composite attributes
themselves
– Key attribute is declared as primary key of the table

Database Management Systems 126

 EXAMPLE

Relational Schema
Student(student_id, FirstName, LastName, Street,City)

Database Management Systems 127

 EXAMPLE

Database Management Systems 128

 STRONG ENTITY SET WITH MULTIVALUE ATTRIBUTES

• In relational model two tables are required to

represent a strong entity set with multi-valued
attributes.
✔ One table with columns as primary key and
multi-valued attributes and
✔ One table with columns as primary key and other
simple attributes

Database Management Systems 129

 EXAMPLE

Database Management Systems 130

 EXAMPLE

Database Management Systems 131

 STRONG ENTITY SET WITH DERIVED
ATTRIBUTES
• In relational model only one table is
required to represent a strong entity set with
composite attributes.
• In this,
– Derived attribute is simply ignored.

Database Management Systems 132

 EXAMPLE

Database Management Systems 133

WEAK ENTITY TO RELATIONAL SCHEMA

Database Management Systems 134

 DESCRIPTIVE ATTRIBUTE

• If relationship have attributes is called as

descriptive attributes.

Database Management Systems 135

 EXAMPLE

Database Management Systems 136

UNARY RELATIONSHIP WITH CARDINALITY RATIO 1:1
UNARY RELATIONSHIP WITH CARDINALITY RATIO 1:M
UNARY RELATIONSHIP WITH CARDINALITY RATIO M:N
BINARY RELATIONSHIP WITH CARDINALITY RATIO 1:1
BINARY RELATIONSHIP WITH CARDINALITY RATIO 1:M
BINARY RELATIONSHIP WITH CARDINALITY RATIO M:N
TERNARY RELATIONSHIP
Database Management Systems 144