UNIT-II

Data Modeling Using the Entity-Relationship (ER) Model

Entity-Relationship modeling:
E – R Model
Concepts:
•Entity,
•Entity types,
•Entity sets,
• Attributes,
•Types of attributes,
• key attribute, and domain of an attribute.
•Relationships between the entities.
Relationship types, roles and structural constraints, degree and
cardinality ratio of a relationship.
Weak entity types, E -R diagram.
Chapter 3-2
 The first step shown is requirements collection and analysis.
During this step, the database designers interview prospective
database users to understand and document their data
requirements.
 functional requirements: These consist of the user-
defined operations (or transactions) that will be applied to the
database, including both retrievals and updates. and data flow
diagrams, sequence diagrams, scenarios are used to specify
functional requirements.
 The conceptual schema is a concise description of the data
requirements of the users and includes detailed descriptions of the
entity types, relationships, and constraints; these are expressed
using the concepts provided by the high-level data model. Because
these concepts do not include implementation details.

Chapter 3-3
The conceptual schema is transformed from the high-
level data model into the implementation data model.
This step is called logical design or data model
mapping; its result is a database schema in the
implementation data model of the DBMS.
 Physical design phase, during which the internal
storage structures, file organizations, indexes, access
paths, and physical design parameters for the database
files are specified.
Application programs are designed and implemented
as database transactions corresponding to the high-
level transaction specifications

Chapter 3-4
 Example COMPANY
Database

Requirements of the Company (oversimplified for

illustrative purposes)
– The company is organized into DEPARTMENTs.
Each department has a name, number and an
employee who manages the department. We keep
track of the start date of the department manager.
– Each department controls a number of PROJECTs.
Each project has a name, number and is located at a
single location.

Chapter 3-5
 Example COMPANY Database
(Cont.)

– We store each EMPLOYEE’s social security number,

address, salary, gender, and birthdate. Each employee
works for one department but may work on several
projects. We keep track of the number of hours per
week that an employee currently works on each
project. We also keep track of the direct supervisor of
each employee.
– Each employee may have a number of DEPENDENTs.
For each dependent, we keep track of their name, sex,
birthdate, and relationship to employee.

Chapter 3-6
Chapter 3-7
 ER model
ER model stands for an Entity-Relationship model. It is
a high-level data model. This model is used to define the
data elements and relationship for a specified system.
It develops a conceptual design for the database. It also
develops a very simple and easy to design view of data.
In ER modeling, the database structure is portrayed as a
diagram called an entity-relationship diagram.

Chapter 3-8
 ER Model Concepts
Entity :An entity can be a real-world object, either animate or
inanimate, that can be easily identifiable. For example, in a school
database, (students, teachers, classes, and courses) offered can be
considered as entities
 Entities are specific objects or things in the mini-world that are represented in
the database. For example the EMPLOYEE John Smith, the Research
DEPARTMENT, the ProductX PROJECT
• Attributes are properties used to describe an entity. For example
an EMPLOYEE entity may have a Name, SSN, Address, Gender,
BirthDate
– A specific entity will have a value for each of its attributes. For example a
specific employee entity may have Name='John Smith',
SSN='123456789', Address ='731, Fondren, Houston, TX', Gender='M',
BirthDate='09-JAN-55‘
– Each attribute has a value set (or data type) associated with it – e.g.
integer, string, subrange, enumerated type, …

Chapter 3-9
Chapter 3-10
 Types of Attributes
 Simple Attribute: A simple attribute is a single atomic value
that cannot be divided further. In ER diagrams, simple
attributes are represented within ovals connected to the
respective entity. For example, if we have an entity
"Employee" with a simple attribute "EmployeeID," the ER
notation would look like:
 Composite Attribute: A composite attribute is made up of
multiple simple attributes. In ER diagrams, composite
attributes are represented by a set of nested ovals within the
entity box. For instance, if we have an entity "Address" with
composite attributes like "Street," "City," and "Zip Code," the
ER notation would be:

Chapter 3-11
 Types of Attributes
 Derived Attribute: A derived attribute is one whose value
can be calculated or derived from other attributes in the
database. In ER diagrams, derived attributes are represented
using dashed lines. For example, if we have an entity
"Invoice" with a derived attribute "Total Amount" calculated
from "Unit Price" and "Quantity," the ER notation would be:
 Multi-valued Attribute: A multi-valued attribute can hold
multiple values for a single entity. In ER diagrams, multi-
valued attributes are represented using double ovals. For
instance, if we have an entity "Student" with a multi-valued
attribute "Phone Numbers," the ER notation would look like:

Chapter 3-12
 Types of Attributes (2)
 Single-value attribute in a database refers to an attribute
that holds only one value for a particular entity or tuple. It is
a fundamental concept in database design and plays a crucial
role in defining the structure and integrity of a database
schema.
 Attributes that can have only one value at a time. For
example, consider a "DateOfBirth" attribute for a "Person"
entity. This attribute should ideally store a single date value
representing the birth date of that person. It cannot have
multiple values assigned to it simultaneously.

Chapter 3-13
 Comparison of Attribute Types

Simple vs. Composite: The key difference lies in the

atomicity, where simple attributes are indivisible,
while composite attributes can be broken down into
simpler parts.
Single-Valued vs. Multi-Valued: Single-valued
attributes hold one value, whereas multi-valued
attributes can store multiple values.
Derived vs. Stored: Derived attributes are calculated
on-the-fly, while stored attributes are physically stored
in the database.

Chapter 3-14
 Entity Type
An entity type describes the schema or intension for a
set of entities that share the same structure.
 An entity type defines a collection (or set) of entities
that have the same attributes. Each entity type in the
database is described by its name
 An entity type defines a collection (or set) of entities
. that have the same attributes. Each entity type in the
database is described by its name and attributes.
Figure 7.6 shows two entity types: EMPLOYEE and
COMPANY, and a list of some of the attributes for and
attributes

Chapter 3-15
 Entity Types and Key Attributes
 Entities with the same basic attributes are grouped or typed
into an entity type. For example, the EMPLOYEE entity type
or the PROJECT entity type.
 An attribute of an entity type for which each entity must
have a unique value is called a key attribute of the entity
type. For example, SSN of EMPLOYEE.
 A key attribute may be composite. For example,
VehicleTagNumber is a key of the CAR entity type with
components (Number, State).
 An entity type may have more than one key. For example, the
CAR entity type may have two keys:
– VehicleIdentificationNumber (popularly called VIN) and
– VehicleTagNumber (Number, State), also known as license_plate
number.

Chapter 3-16
 entity set
The collection of all entities of a particular entity type
in the database at any point in time is called an entity
set; the entity set is usually referred to using the same
name as the entity type. For example, EMPLOYEE
refers to both a type of entity as well as the current set
of all employee entities in the database.
The collection of entities of a particular entity type is
grouped into an entity set, which is also called the
extension of the entity type.

Chapter 3-17
Chapter 3-18
 ENTITY SET corresponding to the
ENTITY TYPE CAR
CAR
Registration(RegistrationNumber, State), VehicleID, Make, Model, Year, (Color)

car1
((ABC 123, TEXAS), TK629, Ford Mustang, convertible, 1999, (red, black))
car2
((ABC 123, NEW YORK), WP9872, Nissan 300ZX, 2-door, 2002, (blue))
car3
((VSY 720, TEXAS), TD729, Buick LeSabre, 4-door, 2003, (white, blue))
.
.
.

Chapter 3-19
 SUMMARY OF ER-DIAGRAM
NOTATION FOR ER SCHEMAS
Symbol Meaning

ENTITY TYPE

WEAK ENTITY TYPE

RELATIONSHIP TYPE

IDENTIFYING RELATIONSHIP TYPE

ATTRIBUTE

KEY ATTRIBUTE

MULTIVALUED ATTRIBUTE

COMPOSITE ATTRIBUTE

DERIVED ATTRIBUTE

E1 R E2 TOTAL PARTICIPATION OF E2 IN R

E1 N
R E2
CARDINALITY RATIO 1:N FOR E1:E2 IN R
(min,max)
R E STRUCTURAL CONSTRAINT (min, max) ON PARTICIPATION
OF E IN R
Chapter 3-20
 ER DIAGRAM – Entity Types are:
EMPLOYEE, DEPARTMENT, PROJECT, DEPENDENT

Chapter 3-21
 domain of an attribute.
the domain of an attribute refers to the set of all possible values that
the attribute can hold. It defines the range of values that a particular
attribute can take within a table or relation.

For example, in a table storing information about employees, the

"Age" attribute may have a domain of integer values from 18 to 65.
This means that the "Age" attribute can only store whole numbers
between 18 and 65, and any other value would be considered invalid.
Defining the domain of an attribute is crucial for data integrity and
validation purposes. It helps enforce constraints on the data being
inserted into the database, ensuring that only valid values are
accepted. This is essential for maintaining the accuracy and
consistency of the data stored in the database.

Chapter 3-22
CREATE TABLE Employees ( EmployeeID
INT PRIMARY KEY, FirstName
VARCHAR(50), LastName VARCHAR(50),
Age INT CHECK (Age BETWEEN 18 AND
65), DepartmentID INT );

Chapter 3-23
 Relationships and Relationship
Types (1)
 A relationship relates two or more distinct entities with a
specific meaning. For example, EMPLOYEE John Smith
works on the ProductX PROJECT or EMPLOYEE Franklin
Wong manages the Research DEPARTMENT.
 Relationships of the same type are grouped or typed into a
relationship type. For example, the WORKS_ON relationship
type in which EMPLOYEEs and PROJECTs participate, or
the MANAGES relationship type in which EMPLOYEEs and
DEPARTMENTs participate.
 The degree of a relationship type is the number of
participating entity types. Both MANAGES and
WORKS_ON are binary relationships.

Chapter 3-24
In a DBMS, understanding entity relationships is important for designing a
robust and efficient database schema. Entities represent real-world objects
or concepts, and the relationships between them define how they interact or
associate with each other. There are several types of relationships that can
exist between entities:
One-to-One (1:1) Relationship: In this type of relationship, each record in
one entity is related to exactly one record in another entity. For example, a
person may have only one passport, and a passport is linked to only one
person.
One-to-Many (1:N) Relationship: Here, a single record in one entity can
be associated with multiple records in another entity. For instance, a
department can have many employees, but an employee belongs to only
one department.
Many-to-One (N:1) Relationship: This is the reverse of a one-to-many
relationship. Multiple records in one entity are related to a single record in
another entity. For example, multiple students can belong to the same class.
Many-to-Many (M:N) Relationship: In this type of relationship, multiple
records in one entity can be associated with multiple records in another
entity. For instance, students can enroll in multiple courses, and a course
can have multiple students.
Chapter 3-25
Chapter 3-26
Example relationship instances of the WORKS_FOR
relationship between EMPLOYEE and DEPARTMENT
EMPLOYEE WORKS_FOR DEPARTMENT

r1
e1   d1

e2  r2

e3  r3  d2

e4  r4
 d3
e5 
r5
e6 
r6
e7 
r7

Chapter 3-27
Example relationship instances of the WORKS_ON
relationship between EMPLOYEE and PROJECT

r9
r1
e1   p1

e2  r2

e3  r3  p2

e4  r4
 p3
e5 
r5
e6 
r6
e7 
r8 r 7

Chapter 3-28
 Relationships and Relationship
Types (2)
 More than one relationship type can exist with the same
participating entity types. For example, MANAGES and
WORKS_FOR are distinct relationships between
EMPLOYEE and DEPARTMENT, but with different
meanings and different relationship instances.

Chapter 3-29
 ER DIAGRAM – Relationship Types are:
WORKS_FOR, MANAGES, WORKS_ON, CONTROLS,
SUPERVISION, DEPENDENTS_OF

Chapter 3-30
 Weak Entity Types
 An entity that does not have a key attribute
 A weak entity must participate in an identifying relationship type
with an owner or identifying entity type
 Entities are identified by the combination of:
– A partial key of the weak entity type
The particular entity they are related to in the identifying entity
type
Example:
Suppose that a DEPENDENT entity is identified by the
dependent’s first name and birhtdate, and the specific
EMPLOYEE that the dependent is related to. DEPENDENT is a
weak entity type with EMPLOYEE as its identifying entity type
via the identifying relationship type DEPENDENT_OF

Chapter 3-31
 Entities are typically classified as either strong entities or weak entities. A weak
entity is an entity that does not have a primary key attribute of its own. Instead, it
relies on the existence of a related strong entity to ensure its own uniqueness
within the database.
 Characteristics of Weak Entities:
 Dependency: A weak entity is dependent on a strong entity for its existence. It
cannot be identified uniquely by its own attributes.
 Partial Key: Weak entities have a partial key that is a set of attributes that can
uniquely identify weak entities related to the same strong entity.
 Relationship: Weak entities are associated with identifying relationships with
strong entities. These relationships are crucial for maintaining referential
integrity.
 Example of a Weak Entity:
 Consider a scenario where we have entities like Order and OrderItem.
Here, OrderItem is a weak entity because it cannot exist without being related to
a specific Order. The primary key of OrderItem would consist of a combination
of its own attributes and the primary key of Order.

Chapter 3-32
 Creating a Weak Entity in a Database Schema:
 When designing a database schema that includes weak entities, it is essential to
represent this relationship correctly. In an Entity-Relationship Diagram (ERD),
weak entities are denoted by using a double rectangle. The identifying relationship
between the weak entity and the strong entity is represented by a diamond shape
connecting the two entities.
 CREATE TABLE Order ( OrderID INT PRIMARY KEY, OrderDate DATE );
CREATE TABLE OrderItem ( ItemID INT, ItemName VARCHAR(50), OrderID
INT, PRIMARY KEY (ItemID, OrderID), FOREIGN KEY (OrderID)
REFERENCES Order(OrderID) );
 In the above SQL example, OrderItem is a weak entity with a composite primary
key consisting of ItemID and OrderID, where OrderID is a foreign key referencing
the Order table.
 Understanding weak entities is crucial for database designers to ensure data
integrity and proper normalization in a relational database system. By correctly
identifying and modeling weak entities, database schemas can effectively
represent complex real-world relationships.


Chapter 3-33
 Weak Entity Type is: DEPENDENT
Identifying Relationship is: DEPENDENTS_OF

Chapter 3-34
In a DBMS, structural constraints play a crucial role in defining the rules and
dependencies between entities in a database. When it comes to relationship
types, there are several structural constraints that can be enforced to maintain
data integrity and consistency. some of the key structural constraints that can be
applied to relationship types:
1. Cardinality Constraints:
Cardinality constraints define the number of instances of one entity that can be
associated with the number of instances of another entity. These constraints
include:
One-to-One (1:1): Each entity instance in one entity is associated with exactly
one entity instance in another entity.
One-to-Many (1:N): Each entity instance in one entity can be associated with
multiple entity instances in another entity.
Many-to-One (N:1): Multiple entity instances in one entity can be associated
with exactly one entity instance in another entity.
Many-to-Many (M:N): Multiple entity instances in one entity can be associated
with multiple entity instances in another entity.

Chapter 3-35
2. Participation Constraints:
Participation constraints specify whether the existence of an entity depends on its
relationship with another entity. These constraints include:
Total Participation: Every entity instance in an entity set must participate in a
relationship.
Partial Participation: Not every entity instance in an entity set is required to participate in
a relationship.
3. Referential Integrity Constraints:
Referential integrity constraints ensure that relationships between entities remain valid.
These constraints include:
CASCADE: Automatically propagates changes in the parent entity to the child entity.
SET NULL: Sets the foreign key in the child entity to NULL when the corresponding
parent entity is deleted.
RESTRICT: Prevents the deletion of a parent entity if there are dependent child
entities.
NO ACTION: Rejects the deletion or update of a parent entity if there are dependent
child entities.
By applying these structural constraints to relationship types in a DBMS, database
designers can establish clear rules for how entities are related and interconnected, ensuring
data consistency and integrity throughout the database system.

Chapter 3-36
Chapter 3-37
 Constraints on Relationships
Constraints on Relationship Types
– ( Also known as ratio constraints )
– Maximum Cardinality
 One-to-one (1:1)
 One-to-many (1:N) or Many-to-one (N:1)
 Many-to-many
– Minimum Cardinality (also called participation constraint or existence dependency
constraints)
– minimum cardinality constraints can be enforced using foreign key constraints with
appropriate NOT NULL constraints.
– Let's consider a simple example to illustrate minimum cardinality. In a library database,
there is a relationship between the 'Book' entity and the 'Author' entity. If we define a
minimum cardinality of '1' on the 'Book' side, it means that every book must have at least
one author associated with it. This constraint ensures that no book can exist without an
author, maintaining data integrity.

Chapter 3-38
 zero (optional participation, not existence-dependent)
 one or more (mandatory, existence-dependent)

Chapter 3-39
 Many-to-one (N:1) RELATIONSHIP
EMPLOYEE WORKS_FOR DEPARTMENT

r1
e1   d1

e2  r2

e3  r3  d2

e4  r4
 d3
e5 
r5
e6 
r6
e7 
r7

Chapter 3-40
 Many-to-many (M:N) RELATIONSHIP

r9
r1
e1   p1

e2  r2

e3  r3  p2

e4  r4
 p3
e5 
r5
e6 
r6
e7 
r8 r 7

Chapter 3-41
 Relationships and Relationship
Types (3)
 We can also have a recursive relationship type.
 Both participations are same entity type in different roles.
 For example, SUPERVISION relationships between
EMPLOYEE (in role of supervisor or boss) and (another)
EMPLOYEE (in role of subordinate or worker).
 In following figure, first role participation labeled with 1 and
second role participation labeled with 2.
 In ER diagram, need to display role names to distinguish
participations.

Chapter 3-42
A RECURSIVE RELATIONSHIP
SUPERVISION
EMPLOYEE SUPERVISION

e1  2
1 r1
e2  2
1
r2
e3  2
1
e4  2 r3
1
e5  1
2 r4
e6  1

2 r5
e7 

r6
© The Benjamin/Cummings Publishing Company, Inc. 1994, Elmasri/Navathe, Fundamentals of Database Systems, Second Edition

Chapter 3-43
Recursive Relationship Type is: SUPERVISION
(participation role names are shown)

Chapter 3-44
 Attributes of Relationship types

A relationship type can have attributes; for

example, HoursPerWeek of WORKS_ON; its
value for each relationship instance describes
the number of hours per week that an
EMPLOYEE works on a PROJECT.

Chapter 3-45
Attribute of a Relationship Type is:
Hours of WORKS_ON

Chapter 3-46
 Structural Constraints –
one way to express semantics
of relationships
Structural constraints on relationships:
 Cardinality ratio (of a binary relationship): 1:1, 1:N, N:1,
or M:N
SHOWN BY PLACING APPROPRIATE NUMBER ON
THE LINK.
 Participation constraint (on each participating entity
type): total (called existence dependency) or partial.
SHOWN BY DOUBLE LINING THE LINK
NOTE: These are easy to specify for Binary Relationship
Types.

Chapter 3-47
 Alternative (min, max) notation for relationship
structural constraints:
 Specified on each participation of an entity type E in a relationship type R
 Specifies that each entity e in E participates in at least min and at most max

relationship instances in R
 Default(no constraint): min=0, max=n

 Must have minmax, min0, max 1

 Derived from the knowledge of mini-world constraints

Examples:
 A department has exactly one manager and an employee can manage at most

one department.
– Specify (0,1) for participation of EMPLOYEE in MANAGES

– Specify (1,1) for participation of DEPARTMENT in MANAGES

 An employee can work for exactly one department but a department can have

any number of employees.

– Specify (1,1) for participation of EMPLOYEE in WORKS_FOR

– Specify (0,n) for participation of DEPARTMENT in WORKS_FOR

Chapter 3-48
The (min,max) notation
relationship constraints

(0,1) (1,1)

(1,1) (1,N)

Chapter 3-49
COMPANY ER Schema Diagram
using (min, max) notation

Chapter 3-50
Entity-Relationship (E-R) diagrams are a
visual representation used in Database
Management Systems to model the
relationships between entities in a database.
In an E-R diagram, entities are represented as
rectangles, attributes as ovals, and
relationships as diamond shapes. Entities are
objects or concepts that are relevant to the
database, while attributes describe the
properties of these entities. Relationships
define how entities interact with each other.

Chapter 3-51
 Relationships of Higher Degree

 Relationship types of degree 2 are called binary

 Relationship types of degree 3 are called ternary and of
degree n are called n-ary
 In general, an n-ary relationship is not equivalent to n
binary relationships
 Higher-order relationships discussed further in Chapter 4

Chapter 3-52