RELATIONAL MODELS AND ENTITY

RELATIONSHIP(ER) MODEL
Basic E-R concepts
Conversion of E-R model into
Relations
Problems with E-R models
E-R Diagram Symbols
EER Model
SDLC
DDLC
Automated Design Tools

Structure of Relational Model

Relational Algebra
Relational Calculus
SQL
Embeded SQL
QBE

E-R modeling is a high level conceptual data

model developed to facilitate database
design
A conceptual model is a set of concepts that
describe the structure of a database and the
associated retrieval and update transaction
on the database.
E-R model is also defined as a logical
representation of data for an enterprise.

It is a top-down approach to the database

design.
It is one of the several semantic data
models.
It is very useful in mapping the meanings
and interactions of real-world enterprise
onto a conceptual schema.

E-R model provides the following

semantic concepts to the designers
1) Entities (distinct objects)
2) Relationships
( Meaningful interactions among the
objects)
3) Attributes (Which describes the
entities and relationships).
4)
Constraints (on the entities,
attributes and relationships).

An entity is an object or a thing in the real

world with an independent existence and that
is distinguished from other objects.

Each entity has attributes.

Person: STUDENT, PATIENT, EMPLOYEE
DOCTOR, ENGINEER
Place: CITY, COUNTRY, STATE.
Event: SEMINAR, SALE, RENEWAL,
COMPETITION.
Object: BUILDING, AUTOMOBILE, MACHINE,
FURNITURE, TOY.
Concept: COURSE, ACCOUNT, TRAINING
CENTRE, WORK CENTRE.

Entity Set (or Type):It is a set of entities of the same type that
share the same properties or attributes.
A group
properties.

of

objects

with

the

same

Entity Instance (or Entity Occurrence):It is a uniquely identifiable object.

It is also referred to as a specific table
row.

Classification of Entity Types:It can be classified as being strong

or weak.
A
strong
entity
type
has
a
characteristic
that
each
entity
occurrence is uniquely identifiable
using the primary key attribute(s) of
that entity type.
An entity type that is dependent on
some other entity type is called weak
entity type.

Weak entity type are sometimes

referred to as child, dependent or
subordinate entities.
Strong entity set are sometimes
referred to as parent , owner or
dominant entities.

Relationship:It is an association among two or

more entities.
A
particular
occurrence
of
a
relationship is called a relationship
instance or occurrence.
It
is
a
association.

uniquely

identifiable

In E-R modeling similar relationships

are grouped into relationship sets (also
called relationship type).
Relationships are described in the
following types:1) Degree of relationship
2) Connectivity of a relationship
3) Existence of a relationship
4) n-ary Relationship

Degree of Relationship:It is the number of entities associated

or participants in the relationships.
Following are the three degrees of
relationships.
1) Recursive or unary relationship
2) Binary relationship
3) Ternary relationship

Recursive or Unary Relationship:It is a relationship between the instances

of a single entity type.
It is a relationship type in which the same
entity type is associated more than once in
different roles.
They are also sometimes called Unary
relationship.

The following example shows a Recurssive

Relationship or Unary Relationship

PERSON
Role
Name
Manag
ed

Manage
s

Manag
er

Binary Relationship:The association between two entities is

called binary relationship.
It is the most common type of relationship
and its degree of relationship is two (2).
The following diagram shows the binary
relationship.

DEPT

IS SUBUNIT OF

1
DIVN
Binary

Ternary Relationship:It is an association among three

entities and its degree of relationship is
three (3).
The construct of ternary relationship is
a single diamond connected to three
entities.
The following diagram shows a ternary
relationship.

SKILL
M

Use
s

PROJECT

PERSON

Connectivity of a relationship:It describes a constraint on the mapping

of the associated entity occurrences in the
relationships.
Value for occurrences are either one or
many.
The actual count of elements associated
with the connectivity is called Cardinality of
the relationship connectivity.
The

following

diagrams

shows

the

DEPT

1
It is a one-to-one (1:1)

IS MANAGED BY

1
DIVN

Exactly
one
PERSON
manages the entity DEPT
and each person manages
exactly one DEPT
The
minimum
and
maximum connectivity are
exactly one for both the
entities.

DEPT

has

N
PERSON

It is a one-to-many (1:n)
The
Entity
DEPT
is
associated
to
Many
PERSON,
Whereas
each
PERSON work within exactly
one DEPT.
The
minimum
and
maximum connectivities to
the PERSON side are of
unknown value N and one
respectively.
Both
Maximum
and
minimum connectivities on
DEPT side are one only

PERSON
StartDate

Task
Assignme
nt

WORKS ON

N
PROJECT

It is a many-to-many (m:n)
The Entity PERSON may
work on many PROJECTS
and each Project may be
handled by many persons.
The maximum connectivity
for PERSON and PROJECT
are M and N respectively,
and minimum connectivities
are each defined as one.

N-ary Relationship:It is a single relationship diamond with n

connections, one to each entity, represents
some association among n entities.
Admittedto

PATIENT

WARD

DAY

HOSPITAL

TIME

Existence of a Relationship:The existence of entities depends on the

existence of another entity.
Existence of an entity in a relationship is
defined either mandatory or optional.
In a mandatory existence, an occurrence
of either one or many side entity must
always exist for the entity to be included in
the relationship.
In a optional existence, the occurrence
of that entity need not exist.

Attributes:An attribute is a property of an entity or

a relationship type.
An entity is described using a set of
attributes.
Each attribute is associated with a set
of values called a domain.
Attributes may share a domain and is
called the attribute domain.

Attributes can be classified into the

following types
1) Simple attribute
2) Composite attribute
3) Single-valued attribute
4) Multi-valued attribute
5) Derived attribute
6) Identifier attribute

Simple Attribute:It is an attribute composed of single

component, with an independent existence.
A simple attribute cannot be subdivided or
broken down into smaller components.
They are
attributes.
e.g.
DOB

sometimes

called

atomic

EMP-ID, EMP-NAME, SALARY, EMP-

Composite Attribute:It is an attribute composed of multiple

components, each with an independent
existence.
Some attributes can be broken down or
divided into smaller components with an
independent existence.
The following diagrams shows
examples of composite attribute.

the

EMPNAME

FIRSTNAME

LASTNAME

Composite attributes can form a hierarchy.

The following figure shows an example of
an hierarchy.

ADDRESS

CITY

STREET

STREETADDRESS

STREETNAME

HIERARC

ZIP
CODE

APARTME
NT-NO

STATE

Single-Valued Attributes:It is an attribute that hold a single

value for each occurrence of an entity
type.
E.g. Employee entity has a single
value for the employee identification
number(EMP-ID) attribute.
Majority of the attributes are singlevalued.

Multi-valued Attributes:It is an attribute that holds multiple

values for each occurrence of an entity
type.
Multi-valued attributes can take more
than one value.
An attribute skill can take up many
values,
like
modeling,
programming,
designing.

Derived
Attribute

Multi-valued
attribute

skill

Years-of
experien
ce

Pername
person

Per-id

Identifier
attribute

Dateemploye
d

Peraddres
s

Derived Attributes:It is attribute that represents a value that

is derivable from the value held by some
attribute or set of attributes.
Identifier Attribute:Each entity is required to be identified
uniquelly in a particular entity set. Using
one ore more entity attributes as an entity
identifier this identification is done. These
attributes are known as the identifier
attributes.

Constraints:Relationship types usually have certain

constraints
that
limit
the
possible
combinations
of
entities
that
may
participate
in
the
corresponding
relationship set.
The main types of constraints on
relationships are multiplicity, cardinality,
participation and exclusion and uniqueness
constraints.

Multiplicity Constraints:It is the number of possible occurrences

of an entity type that may relate to a single
occurrence of an associated entity type
through a particular relationship.
Cardinality Constraints:It specifies the number of instances of
one entity that can (or must) be associated
with each instance of entity.

There are
constraints:

two

types

of

cardinality

1) minimum cardinality constraints.

2) maximum cardinality constraints.
Minimum
cardinality
constraint
of
a
relationship is the minimum number of
instances of an entity that may be
associated with each instance of another
entity.
Maximum cardinality constraint of a
relationship is the maximum number of

Participation constraints:It specifies whether the existence of an

entity depends on its being related to
another entity via the relationship type.
There are two types of participation
constraints.
1) Total Participation
2) Partial Participation

Total Participation means that every entity

in the total set of an entity must be
related to another entity via relationship.
It is also called existence dependency.
Partial Participation constraints means that
some or the part of the set of an entity
are related to another entity via a
relationship, but not necessarily all.
The cardinality constraint and participation
constraint are together known as the
structural constraints
of a relationship
type.

Conversion of E-R model into Relations

An E-R model can be converted into
relations, in which each entity set and each
relationship set is converted to a relation.
The following diagram shows a conversion
of E-R
Diagram into a set of relations.

person

Works-on

projects

PER-ID
DESIGN
LASTNAME
DOB

PER-ID
PROJT-ID
HRSSPENT
STATUS

PROJ-ID
STARTDATE
ENDDATE

PERSON
PERDESIG
ID

LASTNAME

DOB

10651
9

Manag
er

Thoma
s

10028
2

12281
6

Progra
mmer

Abhish
ek

22018
7

WORKSO
13125 Analyst Jose
N6PROJ- PER- HRSID50 ID
SPEN
705
T
P4021

27108
4
STAT
US

10651 45
9

MAJO
R

P12281 15
4042 6
PROJECTS
P13125 21
PROJ-ID START4021 6
DATE

MINO
R
MINO
ENDR
DATE

P-4021

100205

250705

P-4042

010104

300305

P-4021

300804

010805

PROBLEMS WITH E-R MODELS:Some problems, called connection traps,

may arise when creating an E-R model.
The connection traps normally occur due
to misinterpretation of the meaning of
certain relationships.
There are mainly two types of connection
traps
1) Fan traps
2) Chasm traps

In a fan trap, a model represents a

relationship between entity types, but the
pathway between certain entity occurrences
is ambiguous.
A fan trap may exist where two or more
one-to-many (1:N) relationships fan out
from the same entity.
The following example shows a fan trap.

PERSON

FAN
TRAP

HAS

BANK

operat
es

COUNTER

In a chasm trap, a model suggests the

existence of a relationship between entity
types, but the pathway does not exist
between certain entity occurrences.
A chasm trap may occur where there are
one or more relationships with a minimum
multiplicity of zero forming part of the
pathway between related entities.
The following diagram shows a chasm
trap.

CHASM
TRAP

HAS

counter

overse
as

loan

ENHANCED
RELATIONSHIP(EER):-

ENTITY

Enhanced E-R model consists of all the

concepts of E-R model together with the
following additional concepts:
1) Specialisation/Generalisation
2) Categorisation

An Entity type is a set of entities of the

same type that share the same
properties or characteristics.
Subclasses
(or
subtypes)
and
Superclasses (or super types) are the
special types of entities.

Subclasses or subtypes are subgrouping of occurrences of entities in

an entity type that is meaningful to the
organisation and that shares common
attributes or relationships distinct from
other sub-groupings.
Super class or Super type is a generic
entity type that has relationship with
one or more subtypes.

A super class/subclass is simply called

class/subclass or super type/subtype.
A super class/subclass is often called an
IS-A (or IS-AN) relationship because the way
we refer to the concept.
a SECRETARY IS-A PERSON
an ENGINEER IS-A PERSON
a MANAGER IS-A PERSON

The following diagram illustrates the

semantic diagram of the classes both at
the enterprise level and occurrence level.
PERSO
NS
ISA

ENTERPRISE
LEVEL

ISA

MANAGE
RS

ENGINE
ERS

ISA
ELECTRI
CAL
ENGINE

ISA
COMPUT
ER
ENGINE

ISA
ELECTRO
NIC
ENGINEE
RS

Thom
as
Alk
a

Avina
sh
Mathe
w

ISA
Thom
as

ISA

ISA

ISA

Mathe
w

OCCURRE
NCE
LEVEL

Alk
a
ELECTRI
CAL

Mathe
w

Avina
sh

COMPUT
ER

ELECTRO
NIC

EMP-NAME

EMP-ID

DATE-OFBIRTH

ADDRESS
DATE-OFJOINING

EMPLOYEE

FULLTIME
EMPLOY
EE
ALLO
WLAN
CES

SALA
RY

PART-TIME
EMPLOYEE
HOUR
LYRATE

CONSULTA
NT
CONT
RACTNO

BILLI
NG
RATE

ADVANTAGES OF USING SUPERCLASSES AND

SUBCLASSES
1) It avoids the need to describe similar
concepts more than once, thus saving time for
the data modelling person.
2) It results in more readable and betterlooking E-R diagrams.
3) Super class and subclass relationships
add more semantic content and information to
the design in a concise form.

Specialization is the process of identifying

subsets of an entity set(the super class or
super type) that share some distinguishing
characteristic.
Generalization is the process of identifying
some common characteristics of a
collection of entity sets and creating a
new entity set that contains entities
processing these characteristics.

Categorization is a process of modeling a

single subtype (or subclass) with a
relationship that involves more than one
distinct super type (or super class).
EMPLOYEE

DEPARTMENT

OWNER

COMPANY