Entity Relationship (ER) Model
Data Model
• Data model
– A collection of concepts that can be used to describe the
structure of a database (data types, relationships and the
constraints that should hold for data)
• Conceptual (high-level, semantic) data models:
– Describes what data are stored in the database, and what
relationships exist among those data
– Provide concepts that are close to the way many users
perceive data. (Also called entity-based or object-based
data models)
Overview of Database Design
• Conceptual design: ER Model is used at this stage to
describe the data to be stored in the database.
– What are the entities and relationships in the enterprise?
– What information about these entities and relationships
should we store in the database?
• What are the integrity constraints or business rules
that we need to describe?
• A database ‘schema’ in the ER Model can be
represented pictorially (ER diagrams).
• We can map an ER diagram into a relational schema.
ER Model Basics
• Entity: Real-world object distinguishable from other
objects. An entity is described (in DB) using a set of
attributes.
• Entity Set: A collection of similar entities. E.g., all
employees.
• All entities in an entity set have the same set of attributes.
• Each entity set has a key. (A key is a minimal set of
attributes whose values uniquely indentify an entity in the
set).
• Each attribute has a domain.
ER Model Basics
continued
• Relationship: Association among two or more
entities.
– Ram works in Pharmacy department.
• Relationship Set: Collection of similar relationships.
since: it is a descriptive attribute –records information
about the relationship
ER Model Basics
continued
• Same entity set could participate in different
relationship sets.
Ternary Relationship
• Suppose each department has offices in several locations and
we want to record the locations at which each employee works.
• We have to represent this using ternary relationship set
involving Employees, Departments and Locations.
• Entity sets that participate in a relationship set need not
be distinct; some times a relationship might involve two
entities in the same set.
Here, role indicators (subordinate, supervisor) have to be used.
Additional Features of the ER Model
Key Constraints
• Consider Works_In relationship set.
– An employee can work in many departments; a dept can
have many employees.
– But, each dept has at most one Manager (Key constraint)
– Key constraint is indicated by an arrow.
An instance of Manages Relationship Set
Key constraints on Ternary Relationship
•Each employee works in at most one department
and in a single location.
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.
One to Many Relationship
One –to-many relationship – one employee can be associated with many
departments;
Many to Many Relationship
Many-to-many: In Works_In relationship test – an employee is
allowed to work in several departments and a department is allowed
to have several employees.
Participation Constraint
• Does every department need to have a manager?
• If so, this is a participation constraint: the participation of
Departments in Manages is said to be total
– A participation that is not total is said to be partial.
• Every did value in Departments table must appear in a row
of the Manages table (with a non-null ssn value!)- total
participation
– indicated in the ER diagram by using a thick line
• The participation of the entity set Employees in Manages
is partial , since not every employee gets to manage a
department.
Each employee works in at least one department and that each department has at
least one employee. So, participation of both Employees and Departments in
Works_In3 is total.
Manages and Works_in Relationships
Weak Entity
• A weak entity is an entity without the key
– can be identified uniquely only by considering the primary key
of another (owner) entity.
• Owner entity set and weak entity set must participate in a
one-to- many relationship set (one owner, many weak
entities).
• Weak entity set must have total participation in this
identifying relationship set.
• The set of attributes of a weak entity set that uniquely
indentify a weak entity for a given owner entity is called
partial key of the weak entity set.
• pname is the partial key in Dependents (underlined with broken lines)
• A Dependent participates exactly once in the Relationship ..
Key Constraint
• Thick Rectangle .. Shows .. Dependent is the weak entity.
• Policy .. Identifying relationship set … thick line is used.
(Total participation)
Enhanced ER Concepts
Class Hierarchies
• Attributes of one entity set can be inherited by
another entity set
• Employees entity set can be classified into (i)
Hourly based employees (ii) Contract employees
– We can have one more sub class also : Senior
Employees
– Sub classes inherit the properties of parent class
Class Hierarchy Example
Specialization & Generalization
• Specialization: is the process of defining a set of
subclasses of an entity type.
– Employees entity set is divided into (i) Hourly based
employees (ii) Contract employees (iii) Senior employees
• We suppress the differences among several entity
types, identify their common features and generalize
them into a single super class of which the original
entity types are special subclasses.
– CAR, TRUCK , AUTO, BUS (SubClasses) -> VEHICLE
(Generalized Class)
Aggregation
• Used when we have to model a relationship involving
another relationship set (collection of entities)
• Projects should be sponsored by Departments and
Department assigns employees to monitor
sponsorship.
• Sponsors, Monitors have different Descriptive
attributes (so we cannot combine as one relationship
set and use ternary relationship set )
• The use of aggregation versus ternary relationship
may also be guided by certain integrity constraints.