Fundamentals of Database Management

Systems

Chapter Three : The ER Model

 Outlines
 The high-level conceptual model
 Entities, Attributes, and Keys
 Relationships, Associations, and Constraints
 The ER Diagram
 Enhanced ER Diagram
 Mapping ER-models to Relational Tables

2
 The E-R Model: over view
 An entity-relationship model (or E-R model) is a
detailed, logical representation of the data for an
organization or for a business area.
 The E-R model is expressed in terms of entities in the
business environment, the relationships (or
associations) among those entities, and the attributes
(or of both the entities and their
properties)
relationships. is normally expressed as an entity-
 An E-R model
relationship diagram (or E-R diagram, or simply ERD),
which is a graphical representation of an E-R model.

3
 The E-R Model: over view….
It provides useful concepts that allow us to move from an
informal description of what users want from their database
to a more detailed and precise, description that can be
implemented in a DBMS.

4
The E-R Model

5
 The E-R Model

Entity
 PRODUCT

 ORDER

 ITEM

 SUPPLIER

 SHIPMENT T

6
 Drawing tools
 Microsoft Visio
 Oracle Designer
 All Fusion ERWin
 Power Designer

7
 Building Blocks/ Component of the E-R model

The building blocks of the E-R model are:

 Entities: real world physical or logical object

 Attributes: properties used to describe each Entity or real

world object.
 Relationship: the association between Entities

 Constraints: rules that should be obeyed while

manipulating the data.

8
 Building Blocks of the Relational Data
Model
Entity: A person, place, object, event, or concept in the
user environment about which the organization wishes to
maintain data.

 Thus, an entity has a noun name. Some examples of each of

these hinds of entities follow:
Person: EMPLOYEE, STUDENT, PATIENT
Place: STORE, WAREHOUSE, STATE
Object: MACHINE, BUILDING, AUTOMOBILE
Event: SALE, REGISTRATION, RENEWAL
Concept: ACCOUNT, COURSE, WORK CENTER
9
 Building Blocks/Component of E-R Model…
Each entity type shown in the conceptual model represents the
entire class for that entity
Corresponds to entire table, not row
The name given to an entity type should always be singular
noun and descriptive
An entity is one individual/ member/ instance within an entity
type: For example, within the entity type STUDENT, Abebe
might be one entity
 Entity type: A collection of entities that share
common properties or characteristics. (STUDENT)
 Entity instance: A single occurrence of an entity
type. (Abebe) 10
 Types of entity
 Strong entity type: An entity set that has a primary key is
termed a strong entity set.
Weak entity type: An entity set that does not have sufficient
attributes to form a primary key is termed a weak entity set.

Total and partial participation

The participation of an entity set E in a relationship set R is
said to be total if every entity in E participates in at least
one relationship in R.
If only some entities in E participate in relationships in R, the
participation of entity set E in relationship R is said to be
partial.
11
 Attribute
 Attribute: A property or characteristic of an entity or
relationship type that is of interest to the organization.

 Each entity within the entity type will have the same set
and number of attributes, but in general different attribute
values.

 We store the same type of facts (attributes) about every

entity within the entity type.

12
 Types of Attributes
1. Simple (atomic) Vs Composite attributes
2. Single-valued Vs multi-valued attributes
3. Stored vs. Derived Attribute
4. Null Values

13
 Types of Attributes…
(1) Simple (atomic) Vs Composite attributes
 Simple : contains a single value (not divided into sub

parts)
 E.g. Age, gender
 Composite: Divided into sub parts (composed of other
attributes)
 E.g. Name, address

14
 Types of Attributes …
(2) Single-valued Vs multi-valued attributes

Single-valued : have only single value(the value
may change but has only one value at one time)
 E.g. Name, Sex, Id. No. color_of_eyes
 Multi-Valued: have more than one value
 E.g. Address, dependent-name
 Person may have several college degrees

15
 Types of Attributes…
Stored vs. Derived Attribute
 Stored : not possible to derive or compute

 E.g. Name, Address

 Derived: The value may be derived (computed) from
the values of other attributes.
Example
 Age (current year – year of birth)

 Length of employment (current date- start date)

 Profit (earning-cost)

 G.P.A (grade point/credit hours)

16
 Types of Attributes…

Null Values
 NULL applies to attributes which are not applicable or

which do not have values.

 You may enter the value NA (meaning not applicable)

 Value of a key attribute can not be null.

17
 Relationships
 Relationships are the glue that holds together the
various components of an E-R model.
 Intuitively, a relationship is an association
representing an interaction among the instances of one or
more entity types that is of interest to the
organization.
 Thus, a relationship has a verb phrase name.

18
 Relationship type and instances
Relationship type: A meaningful association between
(or among) entity types.
Relationship instance: An association between
(or
among) entity instances where each relationship
instance includes exactly one entity from each
participating entity type.

19
 Relationship type and instances
(a) Relationship type (Completes)

20
 Relationship type and instances

(b) Relationship instances

21
 Degree of a Relationship
Degree: The number of entity types that participate in a
relationship.
Unary relationship: A relationship between the
instances of a single entity type.
Binary relationship: A relationship between the
instances of two entity types.
Ternary relationship: A simultaneous relationship
among the instances of three entity types.

N-NARY Relationship: Tuples from arbitrary number

of entity sets are participating in a relationship.

22
 Cardinality Constraints
Cardinality can be :-
 ONE-TO-ONE:one tuple is associated with only one other
tuple.
 e.g. Building - Location,

 ONE-TO-MANY:one tuple can be associated with many other

tuples
 e.g. hospital - patient,

MANY-TO-ONE:many tuples are associated with one tuple.

 e.g. Employee - Department

23
 Cardinality Constraints
Cardinality can be :-
 MANY-TO-MANY: one tuple is associated with many other
tuples and from the other side, with a different role name
one tuple will be associated with many tuples.
 e.g. Author - Book

24
 Cardinality Constraints
 Example

25
 Problem in ER Modeling

To be Continued…

26
 Problem in ER Modeling
 While designing the ER model one could face a problem on
the design which is called a connection traps.

 Connection traps are problems arising from misinterpreting

certain relationships .

 There are two types of connection traps;

Fan trap

Chasm Trap:

27
 Problem in ER Modeling
[Link] trap:
 Occurs where a model represents a relationship between

entity types, but the pathway between certain entity

occurrences is ambiguous.
 May exist where two or more one-to-many (1:M)

relationships fan out from an entity.

 The problem could be avoided by restructuring the model so

that there would be no 1:M relationships fanning out from a
singe entity and all the semantics of the relationship is
preserved.
28
 [Link] trap:
 Example

29
 [Link] trap …
 Problem: Which car (Car1 or Car3 or Car5) is used by
Employee 6 Emp6 working in Branch 1 (Bra1)?

 Thus from this ER Model one can not tell which car is used
by which staff since a branch can have more than one car
and also a branch is populated by more than one employee.

 Thus we need to restructure the model to avoid the

connection trap.

30
 [Link] trap…
 To avoid the Fan Trap problem we can go for restructuring of
the E-R Model. This will result in the following E-R Model.

31
 [Link] Trap
 Occurs where a model suggests the existence of a
relationship between entity types, but the path way does not
exist between certain entity occurrences.

 May exist when there are one or more relationships with a

minimum multiplicity on cardinality of zero forming part of
the pathway between related entities.

32
 [Link] Trap…
Example

If we have a set of projects that are not active currently

then we can not assign a project manager for these project.

So there are project with no project manager making the

participation to have a minimum value of zero.

33
 [Link] Trap…
Problem: How can we identify which BRANCH is
responsible for which PROJECT? We know that whether the
PROJECT is active or not there is a responsible BRANCH.

But which branch is a question to be answered, and since we

have a minimum participation of zero between employee and
PROJECT we can’t identify the BRANCH responsible for
each PROJECT.

34
 [Link] Trap…
 The solution for this Chasm Trap problem is to add
another relationship between the extreme entities
(BRANCH and PROJECT)

35
 Constraints
 Domain Integrity: No value of the attribute should be
beyond the allowable limits
 Entity Integrity: In a base relation, no attribute of a
primary key can be null
 Referential Integrity: If a foreign key exists in a relation,
either the foreign key value must match a candidate key in
its home relation or the foreign key value must be null
foreign key to primary key match- ups
 Enterprise Integrity: Additional rules specified by the
users or database administrators of a database are
incorporated
36
 Key constraints
Key constraints
 If tuples are need to be unique in the database, and then we need
to make each tuple distinct. To do this we need to have relational
keys that uniquely identify each relation.
 Super Key: an attribute or set of attributes that
uniquely identifies a tuple within a relation.
 Candidate Key: a super key such that no proper subset of that
collection is a Super Key within the relation. A candidate key
has two properties:
[Link]
[Link]
If a candidate key
consists of more
than one attribute 37
 Key constraints
 Primary Key: the candidate key that is selected to
identify tuples uniquely within the relation.

The entire set of attributes in a relation can be
considered as a primary case in a worst case.
 Foreign Key: an attribute, or set of attributes, within
one relation that matches the candidate key of
some relation.

A foreign key is a link between different relations to
create the view or the unnamed relation

38
 Relational languages and
views
 The languages in relational database management
systems are the DDL, VDL and the DML that are
used to define or create the database and perform
manipulation on the database.

 We have the two kinds of relation in relational

database.

 The difference is on how the relation is created, used

and updated:

39
 Relational languages and
views
1. Base Relation
 A Named Relation corresponding to an entity in the
conceptual schema, whose tuples are physically stored in
the database.

2. View
 Is the dynamic result of one or more relational operations

operating on the base relations to produce another virtual

relation.
 So a view virtually derived relation that does not
necessarily exist in the database but can be produced upon
request by a particular user at the time of request.
40
 Relational languages and
views
Purpose of a view
 Hides unnecessary information from users
 Provide powerful flexibility and security
 Provide customized view of the database for users
 A view of one base relation can be updated.
 Update on views derived from various relations is not allowed
 Update on view with aggregation and summary is not allowed

41
Symbols to draw ERD

42
Symbols to draw ERD…

43
ERD example

44
 Question?
45