Data Modeling Using the Entity-
Relationship (ER) Model
E-R Data Model
• An Entity–relationship model (ER model) describes the structure of a database with the
help of a diagram, which is known as Entity Relationship Diagram (ER Diagram). An ER
model is a design or blueprint of a database that can later be implemented as a
database.
• The E-R data model supports following major constructs:
• Entity
• Attribute
• Relationship
Entity Relationship Diagram (ER Diagram)
• An ER diagram shows the relationship among entity sets. An entity set is a
group of similar entities and these entities can have attributes. In terms of
DBMS, an entity is a table or attribute of a table in database, so by
showing relationship among tables and their attributes, ER diagram shows
the complete logical structure of a database
name dname
ssn lot did budget
Employees Works_In Departments
Entities and Attributes
• The most basic object that the ER model represents is an entity
• Entity maybe :
an object with a physical existence (a person, a car, house…) or
• Attributes:
The particular properties that describe the entity
name
ssn lot
Employees
Relationship
Relationship: Association among two or more entities.
– relationships can have their own attributes.
name dname
ssn lot did budget
Employees Works_In Departments
Symbols & Meanings
Entity symbols
Attributes
• Several types of attribute occur in the ER model
– Simple vs. Composite
– Single value vs. Multi-value
– Stored vs. Derived
Simple vs. Composite Attributes
Simple attributes can not be further divisible
– For example, first name, salary
Composite attributes can be divided into smaller subparts.
– For example: Address attribute of the EMPLOYEE entity can be further subdivided into
street_address, city, state, zip_code
– street_address can be further subdivided into Number, street, and apt#
Example of a composite attribute
Single value vs. Multi-value
Single-valued
• The attributes which have a single value for a particular entity are called single-
valued
Example : name, roll_number
Multivalued
• In some cases an attribute can have a set of value for the same entity Such
attributes are called Multivalued
Example:- degree(phd, mcs)
Stored vs. Derived
Stored attributes:
• The stored attribute are such attributes which are already stored in the database and
from which the value of another attribute is derived is called stored attribute.
Derived attributes
• Derived attributes are attributes whose values are generated from other attributes using
calculations, algorithms or procedures.
For example:
Age of a person can be calculated from person’s date of birth and present date.
Difference between these two dates gives the value of age. In this case, date of birth is a
stored attribute and age of the person is the derived attribute
Entity with a multivalued attribute (Skill)
&
derived attribute (Years_Employed)
Multivalued:
Derived an employee can have
from date employed and current date more than one skill
13
Identifying relationship
The relationship type that connects a weak entity type to a parent
or identifying entity type. This relationship is shown with a double line
diamond.
Chen Notation
One-to-one relationship (1:1):
One instance in an entity refers to one and only one instance in the
related entity
Chen Notation
One-to-many relationship(1:M):
One instance in an entity (parent) refers to one or more instances in the
related entity (child)
Chen Notation
Many-to-many relationship (M:N):
exists when one instance of the first entity (parent) can relate to many instances
of the second entity (child), and one instance of the second entity can relate to
many instances of the first entity.
17
CID
PID
Date
Customer Product
Name
Name
Purchase
Customer
CID Name
1 A Purchase
2 B PID CID
1 1
1 2
2 2
Product
PID Name
1 X
2 y
EID DID
Since
Employee Dept
Name
Name
Works
Age for
Employee Dept
EID Name Age DID Name
Work
EID DID Since
Date PID Name
CID
Product
Customer Purchase
Price
Name
Date
Qty
Supplies
Qty
SID
Supplier
Name
SUPPLIER
STOCK
SID Name
1 E SID PID Qty Date
2 R 1 1 100 9
1 2 5000 20
Product
PID Name Price
1 A 10
PURCHASE 2 B 20
CID PID QTY Date
001 1 5 10
001 2 2 3
COUSTOMER
002 1 1 6
CID Name
001 X
002 y
