[PDF] - The Entity-Relationship (ER) Model The best known tools for PDF Document

SLIDE 1

The Entity-Relationship (ER) Model

The best known tools for database design. It aids

design by helping to analyse the structure of the data, the inter-relationships between the components, and, in general the semantics of the data.

Introduced in 1976 by Chen and extended later.
Based on the view that the real world consists of a

set of basic objects called entities and relationships amongst them.

One of the main reasons for the popularity
f the e-r technique is its graphic

representation.

Customer Account Acc No. Balance Name Tel No.

Cust. Acc

Department

Employee Dependent Project Supplier Part

Department

Employee Dependent Project Supplier Part Emp#

DName Dep#

DepName

EName F M L

Salary

Department

Employee Dependent Project Supplier Part Emp#

DName Dep#

DepName

EName F M L

Salary

SLIDE 2

Department

Employee Dependent Project Supplier Part Emp#

DName Dep#

DepName

EName F M L

Salary

1 N 1 N M N M N N M N Department

Employee Dependent Project Supplier Part Emp#

DName Dep#

DepName

EName F M L

Salary

1 N 1 N M N M N N M N component include Qty Department

Employee Dependent Project Supplier Part Emp#

DName Dep#

DepName

EName F M L

Salary

1 N 1 N M N M N N M N component include Qty

Entities

Objects that have to be represented in the database distinguishable from each other. Examples: Concrete entities: Person, Student, Mature student, Computers. Abstract entities: Holiday, Job satisfaction. Note: Entity types vs. Entity instances e.g. Person vs. John Smith

Attributes (Properties)

Properties or features of entities which we are interested in representing. Each entity has a set of attributes. Some properties may be designated as primary key.

Relationships

Associations among entities. Relationships may also have attributes. Note: Relationship types vs. Relationship instances e.g. CustAcc

vs. John Smith having account

567110.

SLIDE 3

The entities involved in a given relationship

are the participants in that relationship.

The number of participants in a relationship

is called the degree of that relationship.

Mapping Constraints

An e-r scheme may define certain constraints to

which the contents of a db must conform.

One important constraint is mapping

cardinalities which specify the number of entity instances to which an instance of another entity can be associated via a relationship.

Mapping Cardinalities For binary relationships:

One-to-One (1:1): An entity instance in A is associated with at most one entity instance in B, and vice versa.

a1 a2 a3 a4 b1 b2 b3 b4

One-to-Many (1:N): An entity instance in A is associated with any number of entity instances in B. An entity instance in B is associated with at most one in A.

a1 a2 a3 a4 b1 b2 b3 b4

Many-to-One: An entity instance in A associated with at most one in B. An entity instance in B associated with any number of entities in A.

b1 b2 b3 b4 a1 a2 a3 a4 a5

Many-to-Many (M:N): An entity instance in A is associated with any number in B, and vice versa.

a1 a2 a3 a4 b1 b2 b3 b4

SLIDE 4

Variations in Notational Conventions

Many-to-one relationships

E1 E2 R

Many-to-many relationships

E1 E2 R

One-to-One Relationships

E1 E2 R

Leaving out the Relationship

1 N

E1 E2

Existence Constraints

For every instance of entity E1 there is at least an instance of entity E2, related to it by R.

E1 E2 R

Example Every employee is a member of at least one department.

Employee Department EMP DEP

SLIDE 5

Every employee is a member of exactly one department. N 1

Employee Department EMP DEP

Operationally, the existence constraint means that whenever a department is deleted, then the employees working in it should either be deleted or be given new departments.

Roles

The function that an entity plays in a relationship is called its role. Roles are normally implicit and are not usually

specified. They are useful, however, when

the meaning of a relationship needs clarification, e.g. in a binary relation whose participants are the same entity. Example 1 manager N worker

Employee works for

Weak and Strong Entities
Attributes versus Relationship+entity

Type hierarchies

Also called:

Isa hierarchies
Subtypes
Specialisation hierarchies
Generalisation hierarchies

SLIDE 6

Employee

Programmer Application Programmer Systems Programmer

isa isa isa Employee

Programmer Application Programmer Systems Programmer

isa isa isa

Name Salary E# Language Domain Op System

Employee

Programmer Application Programmer Systems Programmer

isa isa isa

Name Salary E# Language Domain Op System

Pension Scheme Type

Provider

N 1

An entity type E1 is a subtype of an entity type

E2 if every instance of E1 is also an instance of E2.

Isa: Special 1:1 relationship representing the

relationship between a subtype and its parent type.

Subtypes inherit the properties and relationships of

their parent type but not vice versa.

Converting Multiway Relationships to Binary Ones

It is possible to convert any relationship that connects more than two entities to a collection of binary, many-to-one relationships without losing any information. This is done by introducing a new entity (called the connecting entity), and connecting this entity by many-to-one relationships to each of the entities that participated in the original multiway relationship.

Translating ER Models to Relational Models

1. Entities Base Relations 2. Properties Attributes 3. M:N Relationships Base Relations 4. I:N Relationships Extra Attribute 5. I:1 Relationships Extra Attribute For 3,4,5 decide foreign keys and foreign key rules.

SLIDE 7

Department

Employee Dependent Project Supplier Part Emp#

DName Dep#

DepName

EName F M L

Salary

1 N 1 N M N M N N M N component include Qty

Entities Base Relations

Dept ( ) Emp ( ) Project ( ) Supplier ( ) Part ( ) Dependent( )

Properties Attributes

Dept ( Dep# # # #, DepName ) Emp ( Emp# # # #, , , , F, M, L, Salary ) Project ( ) Supplier ( ) Part ( ) Dependent( DName, Emp# # # # )

Entities Base Relations Properties Attributes

Dept ( Dep# # # #, DepName ) Emp ( Emp# # # #, , , , F, M, L, NatIns ) Project ( Proj# # # #, ProjName ) Supplier ( S# # # #, SName, Status, City ) Part ( Part# # # #, PartName Dependent ( DName, Emp# # # # )

M:N Relationships Base Relations

Emp-Proj ( ) SPP ( ) Structure ( )

M:N Relationships Base Relations

Emp-Proj ( Emp# # # #, Proj# # # # ) SPP ( S# # # #, Proj# # # #, Part# # # #, Qty) Structure ( Part# # # #, Subpart# # # # )

SLIDE 8

M:N Relationships Base Relations Designate Foreign keys Emp-Proj ( Emp# # # #, Proj# # # # ) EMP# and Proj# FKs SPP ( S# # # #, Proj# # # #, Part# # # #, Qty) S# # # #, Proj# # # #, Part# # # # FKs Structure ( Part# # # #, Subpart# # # # ) Part# # # #, Subpart# # # # FKs Emp-Proj ( Emp# # # #, Proj# # # # ) Emp ( Emp# # # #, , , , F, M, L, NatIns ) Project ( Proj# # # #, ProjName )

FK Rules Nulls Allowed Referring To In Relation FK

R1 NO - PK

Emp

Emp-Proj

Emp#

R1 NO - PK

Project

Emp-Proj

Proj#

R1 NO - PK

Supplier

SPP S# R1 NO - PK

Project

SPP Proj# R1 NO - PK

Part

SPP Part#

R1

NO - PK

Part Structure Subpart# R1

NO - PK

Part Structure Part#

R1 means Updates cascade & Deletions delete

I:N Relationships Extra Attribute

Emp ( Emp# # # #, , , , F, M, L, Salary, Dep# # # # ) Dep# FK Dependent ( DName, Emp# # # # ) EMP# FK Emp ( Emp# # # #, , , , F, M, L, Salary, Dep# # # # ) Dept ( Dep# # # #, DepName)

Can Dep# be NULL in EMP? What would be sensible FK rules?

SLIDE 9

Updates cascade, deletions delete

No- existance constraint and PK

Emp

Dependent

Emp# Updates cascade, deletions rejected

No- existance constraint

Dept EMP Dep# FK Rules NULLS Allowed ? Referring To In Relation FK

Finally, putting it all together

Dept ( Dep# # # #, DepName ) Emp ( Emp# # # #, , , , F, M, L, Salary, Dep# # # # ) Project ( Proj# # # #, ProjName ) Supplier ( S# # # #, SName, Status, City) Part ( Part# # # #, PartName ) Dependent ( DName, Emp# # # # ) Emp-Proj ( Emp# # # #, Proj# # # # ) SPP ( S# # # #, Proj# # # #, Part# # # #, Qty) Structure ( Part# # # #, Subpart# # # # )

Subtypes/Supertypes Translation

Base relations for each entity type. Suppose base relation for Employee is Employee(Emp#, Dept#, Salary, . . . ) Then base relation for Programmer can be Programmer(Emp#, Language, . . . ) where Emp# is a foreign key referencing Employee.