UNIT II

2.1
Entity-Relationship (ER) Model, Entities and Entity Sets,
Attributes and Relationships, ER Diagrams, Key Constraints
and Weak Entity Sets
Introduction to the Relational Model and Relational
Schema
ENTITY RELATIONSHIP MODELLING
(ER Modeling)
Entities, Attributes and Entity Sets
 A data can be modeled as:
 a collection of entities,
 relationship among entities.
 Entity
 An entity is “an object or a thing in the real
world” that exists and is distinguishable from
other objects.
 An entity is described using a set of attributes.
 Example: specific person, book etc.
 Entity set
 An entity set is a set of entities of the same type that
share the same properties.
 It is represented by a rectangle and an attribute is
represented by an oval.
 Example: set of all persons, companies, trees,
holidays
Key
 For each entity set, we choose a key.

 A key is a minimal set of attributes whose

values uniquely identify an entity in the set.

 There could be more than one candidate key, we

designate one of them as the primary key.
Entity Sets customer and loan
customer-id customer- customer- customer- loan- no amount
name street city
 Attributes

 An entity is represented by a set of attributes, that is

descriptive properties possessed by all members of an
entity .

Example: customer = (customer-id, customer-name,

customer-street, customer-city)
loan = (loan-number, amount)
student=(id,snme,phno)
Attribute types:
 Simple and composite attributes.
 Eg. simple attributes: account-no
 Eg. composite attributes: Address(city, street, state)

 Single-valued and multi-valued attributes

 E.g. multivalued attribute: phone-number

 Derived attributes
 Can be computed from other attributes
 E.g. age, derived from date_of_birth

 Descriptive attribute
 It is used to record information about the relationship, rather
than any one of the participating entities.
Domain/ Value Set
 For each attribute associated with an entity set, we must
identify a domain of possible values

 Domain is the set of possible values that an

attribute can contain.

 Eg: If the company rates employees on a scale of 1 to 10

and stores ratings in a field called rating, the associated
domain consists of integers 1 through 10.
 Relationships and Relationship Sets
 Relationship: Association among two or more
entities.
 e.g., Sam works in Pharmacy department.

 Relationship Set: Collection of similar

relationships.
 A relationship can also have descriptive
attributes.
 Descriptive attributes are used to record
information about the relationship, rather than
about any one of the participating entities.
 For example, we may wish to record that an
employee works in the pharmacy department as of
January 1991
 This information is captured by adding an
attribute, since, to Works_In Relation
 Degree of a Relationship Set

 Refers to number of entity sets that

participate in a relationship set.

 Relationship sets that involve two entity

sets are binary (or degree two).

 Generally, most relationship sets in a

database system are binary.
Works_In Relation
 Ternary Relationships
 Ternary Relationship includes three entities
 Relationship is ternary because we must record an association
between an employee, a department, and a location
 Instance
 An instance can be thought of as a 'snapshot' of the
relationship set at some instant
Entity Relationship Model
 The entity-relationship (E-R) data model perceives the
real world as consisting of basic objects, called entities, and
relationships among these objects.
 An entity is a “thing” or “object” in the real world that is
distinguishable from all other objects. For example, each
person in an enterprise is an entity.
 An entity set is a set of entities of the same type that share
the same properties, or attributes. For example, Student
result is an entity set that is a collection of student entities
having the same set of attributes.
Entity Relationship Model
Entity-Relationship Diagram

 E-R diagram can express the overall logical structure of a

database graphically. E-R diagrams are simple and clear—
qualities that may well account in large part for the
widespread use of the E-R model. Such a diagram consists of
the following major components.(in the next slide)
 E/R Diagrams
 In an entity‐relationship relationship diagram, each
entity set is represented by a rectangle.

 Each attribute of an entity set is represented

by an oval, with a line to the rectangle
representing its entity set.
Entity Relationship Diagram
Notations
 Rectangle - Represent entity sets
 Ellipses - Represent attributes
 Diamonds - Representing Relationships.
 Lines - Links attributes to entity sets and entity sets
to relationships
 Primary Key - It will be underlined
 Multivalued Attribute - Double Ellipse
 Derived Attribute- Dotted Ellipse
 Double lines -which indicate total participation of an entity in
a relationship set
 Double rectangles -which represent weak entity sets
ER Diagram Notations

 ENTITY TYPE

 WEAK ENTITY TYPE

 RELATIONSHIP TYPE

 IDENTIFYING RELATIONSHIP TYPE

(relationship between the weak entity type
and strong entity type)
… Notation Guide

 ATTRIBUTE

_____  KEY ATTRIBUTE

 MULTIVALUED ATTRIBUTE

 DERIVED ATTRIBUTE

...  COMPOSITE ATTRIBUTE

A Specimen ER Diagram
A Specimen ER Diagram
Simple ER Diagram
Entity
sname

Store Locations Relationship

manager

Attributes
qty Keeps

pname

Product price

descrip
E-R Diagram With Composite, Multivalued, and Derived
Attributes
Additional Features of the ER Model
Key Constraints/Mapping Cardinalities
Participation Constraints
Weak and Strong Entities
Key Constraints
 Consider Works_In relationship.
 An employee can work in several departments and a
department can have several employees.
 Employee 231-31-5368 has worked in Department 51
since 3/3/93 and in Department 56 since 2/2/92.
 Department 51 has two employees.
 since
name dname
ssn lot did budget

Employees Works_In Departments

 Consider another relationship set called Manages between
the Employees and Departments entity sets such that
each department has at most one manager, although a single
employee is allowed to manage more than one department.
 The restriction that each department has at most one
manager is an example of a key constraint.
 It implies that each Departments entity appears in at
most one Manages relationship in any allowable
instance of Manages.
 This restriction is indicated in the ER
diagram using an arrow from
Departments to Manages.
 An instance of the Manages relationship set is shown in
the following diagram.
 A relationship set like Manages is sometimes said to be one-to-
many, to indicate that one employee can be associated with many
departments(in the capacity of a manager) whereas each
department can be associated with at most one employee as its
manager.
 In contrast the Works-In relationship set, in which an employee is
allowed to work in several departments and a department is
allowed to have several employees, is said to be many-to-many.
 If we add the restriction that each employee can manage at most
one department to the Manages relationship set, which would be
indicated by adding an arrow from Employees to Manages is said
to be one-to-one relationship set.
Key Constraints For Ternary
Relationship
 If an entity set E has a key constraint in a relationship set R,
each entity in an instance of E appears in at most one
relationship in R.
 To indicate a key constraint on entity set E in relationship set
R, we draw an arrow from E to R.
 Consider the following diagram of a ternary relationship with
key constaints.
 Each employee works in at most one department and at a
single location.
 i.e each department can be associated with several employees
and locations and each location can be associated with several
departments and employees.
 However, each employee is associated with a single
department and location.
Each employee works in at most one department and at a
single location
 Mapping Cardinalities
 Express the number of entities to which another
entity can be associated via a relationship set.
 Most useful in describing binary relationship sets.
 For a binary relationship set the mapping cardinality
must be one of the following types:
 One to one
 One to many
 Many to one
 Many to many
 One to many
One to one
Many to one Many to many
Cardinality Constraints

 We express cardinality constraints by

drawing either a directed line (→),
signifying “one,” or an undirected line
(—), signifying “many,” between the
relationship set and the entity set.
E.g.: One-to-one relationship:
A customer is associated with at most one loan via the
relationship borrower
A loan is associated with at most one customer via borrower
One-To-Many Relationship
 In the one-to-many relationship a loan is associated
with at most one customer via borrower, a
customer is associated with several loans via
borrower.
Many-To-One Relationships
 In a many-to-one relationship a loan is associated with several
customers via borrower, a customer is associated with at most
one loan via borrower
Many-To-Many Relationship

 A customer is associated with several loans via borrower

 A loan is associated with several customers via borrower
Weak and strong entity set
 Weak Entity Sets
 An entity set that does not have a
primary key is referred to as a weak
entity set.
 A weak entity can be identified
uniquely only by considering some of
its attributes in conjunction with the
primary key of another entity, which is
called the identifying owner.
 The owner entity set and the weak entity
set must participate in a one to many
relationship set.
 This relationship set is called the
identifying relationship set of the weak
entity set.
 Dependents is an example of a weak entity set.
 The discriminator (or partial key) of a weak entity
set is the set of attributes of a weak entity set that
uniquely identify a weak entity for a given owner entity.
 Pname is a partial key for Dependents
 The primary key of a weak entity set is formed by the
primary key of the strong entity set on which the weak
entity set is existence dependent, plus the weak entity
set’s discriminator.
 The total participation of Dependents in policy is indicated
by linking them with a dark line.
 The arrow from Dependents to Policy indicates that each
Dependents entity appears in at most one Policy relationship.
 The Dependents is a weak entity and Policy is its identifying
relationship, we draw both with dark lines.
 To indicate that pname is a partial key for Dependents , we
underline it using a broken line.
Weak Entity Sets
 We depict a weak entity set by double rectangles.
 We underline the discriminator of a weak entity set with a
dashed line.
 payment-number – discriminator of the payment entity set
 Primary key for payment – (loan-number, payment-number)
E-R Diagram for a Banking Enterprise
ER Diagram of Library Management
System
ER diagram of university
Summary of Symbols Used in E-R Notation