Entity Relation Model

Introduction
● Conceptual modeling is a crucial phase in designing a successful database
application.
● Application program design and testing have been part of software
engineering rather than database design.
● Concentrates on database structures and constraints during conceptual
database design
● ER Model - A popular high-level conceptual data model used for the
conceptual design of database applications.
A Sample Database Application
● example database application called company-keeps track of the company's
employees projects and dependents
● Requirements collection and analysis
● The company is organized into departments. Each department has a unique
name, a unique number, and a particular employee who manages the
department. We keep track of the start date when that employee began
managing the department. A department may have several locations.
● A department controls a number of projects, each of which has a unique
name, a unique number, and a single location.
A Sample Database Application
● The database will store each employee’s name, Social Security number, address, salary, sex
(gender), and birth date.
● An employee is assigned to one department, but may work on several projects, which are not
necessarily controlled by the same department.
● It is required to keep track of the current number of hours per week that an employee works
on each project, as well as the direct supervisor of each employee (who is another employee).
● The database will keep track of the dependents of each employee for insurance purposes,
including each dependent’s first name, sex, birth date, and relationship to the employee
A Sample Database Application
Entity and Attributes
● Entity is a thing in the real world with an independent existence
● An object with physical existence (house, person) or an object with conceptual
existence (course, job).
● Each entity has attributes—the particular properties that describe it.
● For example, an EMPLOYEE entity may be described by the employee’s name, age,
address, salary, and job.
Entity and Attributes
Composite versus Simple (Atomic) Attributes.

● Composite attributes can be divided into smaller subparts

● Example: The Address attribute of an EMPLOYEE entity can be divided into:
○ Street Address
○ City
○ State
○ Zip Code
● Attributes that are not divisible are called simple or atomic attributes.
● The Age of an employee is a simple attribute.
Composite versus Simple (Atomic) Attributes.
Single-Valued versus Multivalued Attributes
Single-Valued Attribute: Holds one value (e.g., Age for a person, Social Security
Number).

Multivalued Attribute: Holds multiple values (e.g., Colors for a car, college degree ).
Stored versus Derived Attributes.
● Derived attributes whose values can be derived from other attributes.

● Stored Attributes are attributes whose values are physically stored in the database.

They are used as the basis for calculating derived attributes.

● example of a derived attribute is age

● date of birth or birth date of a person is a stored attribute

Complex Attributes
● multi-valued and composite Attribute

● multi-valued attribute is represented within curly brackets - { }

● composite attributes are represented within paranthesis - ()

● Ex: {College Degree: ( College, Year, Degree, Field)}

NULL Values
Which is not applicable or Unknown.

Not Applicable- Entity might not have an applicable value for an attribute.

Unknown: we don't know the value of an attribute.

Unknown NULL:

● Missing but exists:

● Existence unknown:
Entity Type and Entity Set
Entity Type: It is a collection of entities that have the same attributes.

Ex: Student.

Entity Set: collection of entities of a particular entity type at a point in time Student
Entity Type and Entity Set Example
Key Attribute and Value set of Attribute
Key Attribute: Attribute that is capable of identifying each entity uniquely. Ex: rollno

composite key: combination of multiple attributes that uniquely identifies each entity.

Ex: Vehicle_id and Registration

Value set of Attribute:set of values that can be assigned to a particular attribute.

Ex: Employee Age: 16-70.

Student
Mathematically, an attribute A of an entity set E

with a value set V is a function from E to the power set

P(V) of V

A : E → P(V)
Weak Entity Types
Weak Entity Types: entity types that do not have key attributes of their own

strong entity types: entity types that have their own key attributes

Owner Entity Type: An entity that provides identity to a weak entity.

Identifying Relationship: A relationship that links a weak entity to its owner entity,
enabling the weak entity's identification.
Symbols Used in ER Model
Symbols Used in ER Model
Conceptual Design of the COMPANY Database
Conceptual Design of the COMPANY Database
Relationship Types, Sets, and Instances

● A relationship type defines associations among entities

● Employee works for Department
● In ER diagrams relationship types are shown as diamonds.
● A relationship set is a collection of specific connections (or relationship instances) between
entities.
● Employee 1 works for Department 1
● Employee 2 works for Department 2
● Employee 3 works for Department 1
● Relationship instance is one particular connection between a single employee and a single
department.
● Mathematically relationship set R is a subset of the Cartesian product: E1 × E2 × ... × En
Relationship Types, Sets, and Instances

● Employees e1, e3, and e6 works for Department d1.

● Employees e2 and e4 works for Department d2.
● Employees e5 and e7 works for Department d3.
● Mathematically R⊆E×D
● R={(e1,d1),(e2,d2),(e3,d1),

(e4,d2),(e5,d3),(e6,d1),(e7,d3)}
Degree of a Relationship Type
Number of participating entity types involved in the relationship.
Binary Relationship (Degree 2):
● A relationship involving two entity types.
● Example: Employee works for Department
Ternary Relationship (Degree 3):
● A relationship involving three entity types.
● Example: The SUPPLY relationship between a SUPPLIER, a PART, and a PROJECT
Higher-Degree Relationships:
● Relationships involving more than three entity types.
Degree of a Relationship Type

Some relationship instances in the Some relationship instances in the SUPPLY

WORKS_FOR binary relationship set ternary relationship set
Role Names
● Distinguish roles of participating entities in a relationship.

● Necessary when same entity type participates multiple times.

● Clarify purpose/function of each entity.

● In the WORKS_FOR relationship, the role names could be worker (EMPLOYEE)

and employer (DEPARTMENT).

Recursive Relationships
● Same Entity Type participates multiple times in a relationship

● In the SUPERVISION relationship, Entity type EMPLOYEE plays two

roles—supervisor (boss) and supervisee (subordinate).

● Role names are crucial to identify the different roles in a recursive relationship.
Recursive Relationships

‘1’ (supervisor)

‘2’ (supervisee).

e1 supervises e2 and e3,

e4 supervises e6 and e7, and

e5 supervises e1 and e4
Relationships as Attributes
● Sometimes convenient to think of a binary relationship type in terms of attributes.
● WORKS_FOR relationship can be represented by making Department an attribute
of EMPLOYEE.
● Employee can be a multi-valued attribute of DEPARTMENT. Ex: d1 would have
the set {e1, e3, e6}
● These two options are inverses of each other and must always stay consistent.
● Department for EMPLOYEE or Employee for DEPARTMENT, and they should
always match.
DESIGN AN ER-DIAGRAM
The department offers various courses in each semester.
● Each course has a unique course code, title, number of credits, and semester
in
which it is offered.
● Each course is taught by a faculty member and enrolled by multiple students.
● Each faculty member has a unique faculty ID, name, designation, and area of
specialization.
● Students enroll in multiple courses each semester
● Each student has a unique USN, name, semester, and contact details.
● The department has a set of classrooms where courses are conducted.
● Each classroom has a unique room number, capacity, and building location.
constraints on binary relationship types
● Limit the combinations of entities that can participate in a relationship set.

● Two common constraints are cardinality ratio and participation.

● Cardinality Ratios:Cardinality ratio defines the maximum number of relationship instances that an entity

can participate in.

● There are different types of relationship cardinalities:

➢ One to one

➢ One to many or Many to one

➢ Many to many
constraints on binary relationship types
Cardinality Ratios

A 1:1 relationship, An M:N relationship,

MANAGES WORKS_ON.
 constraints on binary relationship types
Cardinality Ratios

A 1:n or n:1
relationship,
WORKS_FOR
Constraints on Binary Relationship Types
Constraints on Binary Relationship Types
Participation Constraints:

Specify the minimum number of relationship instances that an entity can participate in
(also called minimum cardinality).

There are two types:

● Total Participation: Every entity must participate in at least one relationship instance
(e.g., every department must have manager).
● Partial Participation: Only some entities participate in the relationship (e.g., not
every employee manages a department).

Total participation is shown with a double line in ER diagrams, and partial

participation with a single line.
Constraints on Binary Relationship Types
Participation Constraints:
 Attributes of Relationship Types
Attributes capture additional information specific to the relationship between participating
entities.

● Example: The Hours attribute in the WORKS_ON relationship records how many hours an
employee works on a particular project.
Relationship Types:

Attributes of 1:1 or 1:N relationship types can be migrated to one of the participating entity
types.

● In 1:1 relationship type attributes can be migrated to either of the entity types or any one
of the entity types
● In 1:N or N:1 relationship type attributes are migrated only to the entity type Nth side of
the relationship
● M:N relationship type some attributes can be determined by a combination of the
participating entities
Attributes of Relationship Types
Attributes of Relationship Types
DESIGN AN ER-DIAGRAM
The department offers various courses in each semester.
● Each course has a unique course code, title, number of credits, and semester in
which it is offered.
● Each course is taught by a faculty member and enrolled by multiple students.
● Each faculty member has a unique faculty ID, name, designation, and area of
specialization.
● Students enroll in multiple courses each semester
● Each student has a unique USN, name, semester, and contact details.
● The department has a set of classrooms where courses are conducted.
● Each classroom has a unique room number, capacity, and building location.

● Each faculty member teaches one or more courses.

● Students enroll in multiple courses each semester.

● Each course is assigned to one classroom for the semester.

Alternative Notations for ER Diagrams
● The (min, max) notation for specifying structural constraints on relationships in
ER diagrams provides alternative to the traditional cardinality ratio and
participation constraints, like single/double line notation.
● The (min, max) notation specifies how many times an entity in an entity set can
participate in a relationship.
Min and Max Values:

● Min: Represents the minimum number of relationships an entity must participate

in.(Partial Participation min =0 and Total Participation min=1 )
● Max: Represents the maximum number of relationships an entity can participate
in.(Max=1 the entity can participate in at most one relationship max = N it means
the entity can participate in up to N relationships)
Alternative Notations for ER Diagrams
Relationship Types of Degree Higher than two

Three binary relationships not equivalent to

SUPPLY
Relationship Types of Degree Higher than two

Ternary relationship type - The SUPPLY relationship.