Entity Relationship model

Chapter2: Entity Relationship model (T2: 42-65)

 Using High-Level Conceptual Data Models for Database Design
 An Example of database Application
 Entity types, Entity sets, Attributes and Keys
 Relationships, Relationship types
 Roles and Structural Constraints
 Weak Entity Types
 Refining the ER Design for the COMPANY Database
 ER Diagrams
 Naming Conventions and Design Issues
 Relationship types of degree higher than two

Using High-Level Conceptual Data Models for Database Design

 1st Step: Requirements analysis
 2nd Step: Conceptual design
 3rd Step: Logical design or data model mapping
 4th Step: Physical design

1st Step: Requirements analysis

 Database designers interview prospective database users to understand and document their
data requirements
 Two types of requirements
o Functional requirements
o Database requirements
Functional requirements
 User defined operations that are applied to the database
 Include updates and retrieval
 DFDs, Sequence diagram, scenarios can be used to define
Database requirements
 Data and data type to be stored
 Constraints applied on data

2nd Step: Conceptual design

 Create conceptual schema using high level conceptual data model
 Conceptual schema is a description of the data requirements of the users and includes entity
types, relationships, constraints.
 Conceptual schema do not include implementation details and can be used to communicate
with non technical users.
 It can be used to ensure that all users data requirements are met and no conflict exists

3rd Step: Logical design or data model mapping

 Actual implementation of the database using a commercial DBMS
 Conceptual schema is transformed from the high level data model into the implementation
data model.
4th Step: Physical design
 Internal storage structures, access paths, and file organizations for the database files are
specified
 In parallel with all steps, application programs are designed and implemented as database
transactions corresponding to the high level transaction specifications.

Example: Company database

The company is organized into DEPARTMENTs
 Each department has a unique name, unique number.
 An employee manages the department.
 We keep track of the start date of the department manager.
 A department may have several locations.

Each department controls a number of PROJECTs.

 Each project has a unique name, unique number.
 It is located at a single location.

Each EMPLOYEE's details are also stored

 Like social security number, address, salary, sex, and birth date.
 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, birth date, and relationship to
employee.
ER Model
 A database can be modeled as:
o a collection of entities having attributes
o relationship among entities
 Entity, Entity type, Entity set
 Attributes, Type of attributes
 Keys
 Relationships, Relationship type
 Roles
 Constraints

Entities
 An entity is an object that has existence and is distinguishable from other objects
Physical existence
 Person, car, employee etc.
Conceptual existence
 Company, job, university course
 An entity lies within the scope of the business world being modelled.
 Each entity has attributes – the particular properties that describe it.

Attributes
 Attributes are properties used to describe an entity.
 For example an EMPLOYEE entity may have a Name, SSN, Address, Sex, BirthDate
 A specific entity will have a value for each of its attributes.
 For example a specific employee entity may have Name=‘Ram', SSN='123456789',
Address ='731, RR Nagar, Bangalore, Karnataka', Sex='M', BirthDate='09-JAN-65‘
 Each attribute has a data type associated with it e.g. integer, string, date etc.
 Each attribute must have a unique name across the entity.

Types of Attributes
 Simple
o Each entity has a single atomic value for the attribute. For example, SSN or Sex.
 Composite
o The attribute may be composed of several components.
o The value of a composite attribute is the concatenation of the values of its simple
attributes.
o Composition may form a hierarchy where some components are themselves
composite.
o If components of the composite attributes have to be referred, it is necessary to store
the components separately.
o If composite attribute is referred only as a whole, there is no need to subdivide it into
component attributes e.g. if address has to be referred as a whole only, there is no
need to divide it.
Composite attributes (Example)
For example, Address (Apt#, House#, Street, City, State, ZipCode, Country)
Name (FirstName, MiddleName, LastName).
Single-valued
 Only single value for a particular entity e.g. age
Multi-valued
 An entity may have multiple values for that attribute.
 Multiple value may have lower and upper bounds on the number of values.
 For example,
Color of a CAR: {Color}
phone_numbers: {phone number}

Stored attributes
 Stored in the database
Derived attributes
 Can be computed from other related attributes
E.g. Birthdate ---stored
Age ----derived

Joining date ------stored

Year of Experience ---derived
Types of Attributes: Example

Null values
 A particular entity may not have the value for a particular attribute
o Value is not applicable
o Value is unknown
 A special value NULL is written
 e.g. Every employee may not have Fax no.

Complex attributes
o Composite attributes can be nested arbitrarily.
o Components of a composite attribute can be shown in () and multivalued attribute can be
shown in {}.
For example:
{Address_phone ({phone (Area_code, phone_number)}, Address (Street_Address (Number,
Street, Apartment_No), City, State, Zip))}

Entity Type
 An entity type defines a collection of entities that have the same attributes
 Each entity if defined in database by its name and attributes.
 Each entity type must have a name that is unique across the entire model and has a
consistent meaning across the modelling team and the end users.
For example,
 EMPLOYEE is an entity type
 PROJECT is an entity type.

Entity set
 The collection of all entities of a particular entity type in the database at any point of 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
o ‘type of entity’
o ‘set of entity (Employee type)’
Notations
 Entity type by rectangular box enclosing the entity type name
 Attributes by ovals attached to entity type by straight line
 Composite attributes shown by attaching components to it
 Multivalued by double oval
Example

Extension & Intension

 An entity type describes the schema or intension for a set of entities that share structure.
 The collection of all entities of a particular entity type are grouped into an entity set, is
also called the extension of the entity type.
Key attributes
 An attribute of an entity type for which each entity must have a unique value is called a
key attribute of the entity type.
o For example, SSN of EMPLOYEE.
 (Key attribute defines the each entity of an entity type uniquely)
 Uniqueness of key attribute must hold for every extension of the entity type.
 It is not the property of a particular extension of the entity type, it is a constraint on all
extension of entity type.
 A key attribute may be composite.
o For example,
Registration is a composite key of the CAR entity type with components
(Number, State).
 Some entity types have more than one key attributes, those attributes can behave as keys
on their own separately, they are called candidate keys.
 All key attributes should be underlined in ER diagram
 Selected key will work as Primary key.
 Other potential keys will be Alternate keys.
 An entity type may have no key, it is called a weak entity type.

Value Sets (Domains) of attributes

 Domain specifies the set of permitted values for each attribute for each individual entity.
For example,
o Birth Date: Range of all valid dates
o Employee Name: All possible range of names
o DeptCode: Set of all department codes
 Value sets are specified using the basic data types, size and other constraints.
 Value set provides all possible values

Initial Conceptual Design of COMPANY Database

1. An entity type DEPARTMENT with attributes Name, Number, Location, Manager,
Manager_Start_Date.
Location is multivalued.
Name & Number are (separate) key attributes.

2. An entity type PROJECT with attributes Name, Number, Location, Controlling_Department.

Name & Number are (separate) key attributes.
3. An entity type EMPLOYEE with attributes SSN, Name, Sex, Address, Salary, Birth_date,
Department and Supervisor.
Name & Address are composite,SSN is key attribute.
Projects and Number_Of_Hours for which Employee is working.

4. An entity type DEPENDENT with attributes Employee_Name, Dependent_Name, Sex,

Birth_date, Relationship (to Employee)

Relationships
 A relationship is an association among two or more entities.
 Whenever an attribute of one entity type refers to the attribute of another entity type, some
relationship exists.
 Specifically a relationship relates two or more distinct entities with a specific meaning
o For example, manager of the DEPARTMENT refers to an employee who manages
the department.
o Department of EMPLOYEE refers to the department for which the employee works.

Relationships Type
 Relationships of the same type are grouped or typed into a relationship type.
o For example, the WORKS_FOR relationship type in which EMPLOYEEs and
DEPARTMENTs entities participate,
o or The MANAGES relationship type in which EMPLOYEEs and DEPARTMENTs
entities participate.
 More than one relationship type can exist with the same participating entity types.
o For example, MANAGES and WORKS_FOR are distinct relationships between
EMPLOYEE and DEPARTMENT, but with different meanings and different
relationship instances.
Relationships Set
 A relationships set is a set of relationships of the same type.
 A relationship set and a relation type are referred to by the same name.
 The relationship set R is a set of relationship instances r i, where each ri associates n
individual entities (e1, e2, ….., en), and each entity ej in ri is a member of entity type Ej,
1≤ j ≤ n
 E.g. each employee and department participates in the relationship of works_for.

Some instances in the WORKS_FOR relationship set, which represents a relationship type
WORKS_FOR between Employee and department

Degree of Relationships Type

 The degree of a relationship type is the number of participating entity types.
Binary Relationship
 When two entities participate in relation.
 WORKS_FOR is binary relationship and participating entities are EMPLOYEE and
DEPARTMENT
Ternary Relationship
 When three entities participate in relation.
 SUPPLY is ternary relationship and participating entities are SUPPLIER, PROJECT and
PARTS.
Example

Some relationship instances in the ‘SUPPLY’ ternary relationship set (Example)

Role names
 Each entity type that participates in a relationship type plays a particular role in the
relationship.
 Role name signifies the role that a participating entity plays in each relationship instance.
For example,
 In the WORKS_FOR relationship type, EMPLOYEE plays the role of employee or worker
and DEPARTMENT plays a role of department or employer.
 Each participating entity type name can be used as role name.

Recursive relationship
 In some cases, same entity participates more than once in a relationship type and plays
different roles.
 In such cases, role names become necessary for distinguishing the meaning of each
participation
 Such relationship types are called Recursive relationship.
Example

Example: A recursive relationship SUPERVISION between EMPLOYEE in the supervisor role

(1) and EMPLOYEE in the subordinate role (2)

Constraints on Relationships
 Structural Constraints on Relationship Types (Also known as ratio constraints) are
determined from the mini-world situation.
o Maximum Cardinality (or cardinality ratio)
o Minimum Cardinality (also called participation constraint or existence dependency
constraints)

Maximum Cardinality (or cardinality ratio) for Binary relationship

 It refers to the number of entities to which another entity can be associated via a relationship
set
 Cardinality must be one of the following types:
o One to one (1:1)
o One to many (1:N)
o Many to one (N:1)
o Many to many (M:N)
 Shown by placing appropriate number on the link.

One-to-one (1:1) Relationships

An entity in A is associated with at most one entity in B, and an entity in B is associated
with at most one entity in A

One-to-Many (1:N) Relationships

An entity in A is associated with any number of entities in B, and an entity in B however
can be associated with at most one entity in A,

1:1 & 1:N Relationships: Example

Many-to-one (N:1) Relationships

An entity in A is associated with at most one entity in B, and an entity in B however can
be associated with any number of entities in A

Many-to-many (N:M) Relationships

An entity in A is associated with any number of entities in B, and an entity in B however
can be associated with any number of entities in A,
N:1 & N:M Relationships: Example

Participation constraint or existence dependency constraints

o The partition constraint specifies whether the existence of an entity depends on its being
related to another entity via the relationship type.
o Total Participation
o Partial participation

Total Participation
 Total Participation is a constraint when every entity in the entity set participates in at least
one relationship in the relationship set.
 Total Participation is also called existence dependency.
 Shown by double lining the link
 For example
o Every employee must work in some department
o Every employee must work on some project

Partial Participation
 Partial participation is the constraint when some entities may not participate in any
relationship in the relationship set.
 Represented by single line link.
 Example: participation of ‘EMPLOYEE’ as manager in relationship type ‘MANAGES’ is
partial

Attributes of Relationship types

 Relationship types can also have attributes similar to attributes of entity types.
 E.g. To record ‘Number of Hours per Week’ that an employee works on a particular project,
No_of_hours can be included as an attribute with relationship type ‘Works_on’.
 Attribute ‘Manager_start_date’ can be included with relationship type ‘Manages’.

Weak Entity Types

 An entity that does not have a key attribute is called weak entity type
 A weak entity can not be identified without its owner.
 A weak entity type must participate in an identifying relationship type with an owner or
identifying entity type.
 A weak entity type always has a total participation constraint (existence dependency) with
respect to its identifying relationship.
 Weak entities are identified by the combination of:
o A partial key of the weak entity type
o The particular entity they are related to in the identifying entity type
For example:
 Weak Entity Type: DEPENDENT
 Identifying Relationship: DEPENDENTS_OF
 Identifying Entity Type: EMPLOYEE
 Partial key of DEPENDENT: Dependent_name
 Primary key of DEPENDENT: (Employee_name, Dependent_name)

Refining the ER Design for the Company Database

The cardinality ratio and participation constraint of each relationship type in company database
can be determined by the following requirements:

1. ‘manages’ a 1:1 relationship type between employee and department

 employee’s participation is partial but department participation is mandatory i.e. total
 the attribute StartDate is assigned to the relationship type

2. ‘works_for’, a N:1 relationship type between employee and department

 Both participations are total.

3. ‘controls’, a 1:N relationship type between department and project

 Participation of project is total but Participation of department is partial

4. ‘supervision’, a 1:N relationship type between employee and employee

 Both participation is partial

5. ‘works_on’, a M:N relationship type between employee and project

 Both participations are total.
 Attribute for this can be No-of-hours.

6. ‘dependents_of’, a 1:N relationship type between employee and dependent

 Participation of dependent is total but Participation of employee is partial
 Relationship is identifying for weak entity dependent.
ER diagrams, naming conventions and design issues
 In ER diagram, emphasis is on representing the schema rather than the instances.
 Schema is much smaller than extensions
 Schema is relatively much more stable than extensions
 Schema changes rarely and Extensions changes frequently.
Notations for ER Diagrams
Naming conventions of Schema constructs
 Singular names for entity type and relationship type
 Entity type & Relationship Type:
o Uppercase
 Attribute names:
o Capitalized
 Role names:
o Lowercase
 Nouns: Entity Type name
 Verbs: Relationship Type
 Additional Nouns that describe the nouns corresponding to Entity types: Attributes
 Binary relationship should be readable
o from left to right
o top to bottom
Design Choices for ER Conceptual Design
 Schema design is an iterative process, it continues until the most suitable design is
reached.

Alternative Notations for ER Diagrams

For specifying structural constraints on relationships
 This involves associating a pair of integer numbers (min, max) with each participation of
an entity type E in a relationship type R,
where 0<=min <=max>=1
 min=0 partial participation
 min>0 total participation
Use either cardinality ratio, single/double line
 Or (min, max)

Degree of Relationships Type

 The degree of a relationship type is the number of participating entity types.
 Binary Relationship
o When two entities participate in relation.
o WORKS_FOR is binary relationship and participating entities are EMPLOYEE and
DEPARTMENT
 Ternary Relationship
o When three entities participate in relation.
o SUPPLY is ternary relationship and participating entities are SUPPLIER, PROJECT
and PARTS.
Degree of Relationships Type: Example

Ternary Relationship: SUPPLY

 Relationship set of SUPPLY is a set of relationship instances (s, j, p), where s is a supplier
who currently supplying a part p to a project j.
Three Binary relationships
 Three binary relationship sets:
 SUPPLIES: relationship instances (s, j),
 CAN_SUPPLY: relationship instances (s, p),
 USES: relationship instances (j, p),

SUPPLY as Weak entity

 Some variations of ER model does not allow ternary relationships.
 A ternary relationship must be represented by weak entity without any partial key, and with
three identifying relationship.
 Three participating entity types SUPPLIER, PROJECT, PART are owner entities.

 Three binary 1:N relationships relate SUPPLY to the three participating entity types.
 A surrogate key “Supply_id” can also be introduced to convert weak entity as regular entity
 Choosing between Binary and Ternary (or higher-degree) Relationships


End of chapter 2