LECTURE 4 – ERD CONT.

• Advanced conventions and detailed definitions

• Classical Structures and Generic Patterns
• Quality and Completeness Checks

• Basic literature
• [Barker II] Barker R., CASE*Method: Entity Relationship
Modelling, Addison-Wesley Pub. Comp., 1991
• Additional literature
• [Date] Date C.J., An introduction to database systems,
Addison-Wesley Pub. Comp., 1986

GHJ-PWR 1
 DETAILED DEFINITION AND ADDITION TO
PRINCIPLES OF INFORMATION MODELING

• integrity constraints level

• cascade delete and update transaction
• business cohesion rules
• attribute, domain and relationship definition - summary

GHJ-PWR 2
 BUSINESS RULES ENFORCEMENT

• Business rules – any external, management or other

factor that restrict a business or system development in
terms of resource availability, dependencies, timescale
or some other factor
• Business rules enforcement
• database-level via integrity constraints
• through an application code

GHJ-PWR 3
DETAILED DEFINITION OF ATTRIBUTE SUMMARY

Do not use an entity name as a part of an attribute name. EMPLOYEE

An attribute name must be in the singular. # no
An attribute name can not be repeated for one entity.
* name
Attributes in the same domain are subject to a common
set of validation constraints.
* date
* profession
o address

Entity name: EMPLOYEE

Attribute Optional Max UID

# Abbreviation Type
name (Y/N) length (Y/N)

1 number no N num 3 Y
2 name and name N char 25 N
surname
3 date of birth date N date 8 N
4 profession N char 20 N
5 address Y char 50 N 4

GHJ-PWR
 DOMAIN DEFINITION

• A set of business validation rules, format constraints

and other properties that apply to a group of attributes
• For example:
• A list of values
• A range
• A qualified list: low, medium, high
• A qualified range: 0 - 10
• Any combination of these

GHJ-PWR 5
DETAILED RELATIONSHIP DEFINITION SUMMARY

belongs to
ACCOUNT PERSON
# code
(5)
has got
# number
ORGANIZATION
owner of # code
owned by

Entity name: ACCOUNT

# Participation Link phrase Cardinality Entity name Cascade Arc UID

delete (Y/N)
1 mandatory Belongs to Many to PERSON C 5 N
one
2 mandatory Owned by Many to ORGANIZATION C 5 N
one
 BASIC LEVELS OF INTEGRITY CONSTRAINTS

• On an attribute level – all attribute values of the same type

belong to the same domain (the check constraint)
• On an entity level - none of the ID components cannot
contain the value null (the primary key constraint)
• On a relationship level – each, non-empty value of the
foreign key must match the primary key value from the
another or the same table. There may be the value null.
• Referential integrity (RI) rules ensure that one-to-one, one-to-
many and many-to-many relationships are enforced within the
relational schema.
• RI may well be enforced automatically by the RDBMS: the
reference constraint, the primary key constraint, the foreign
key, …

GHJ-PWR 7
 REFERENCE INTEGRITY CONSTRAINTS

• they are a set of rules

• to ensure the accuracy of the relationship
between entities
• participation - ensuring that a value that indicates
an entity, actually is in the collection of
information
• cardinality - for example: a particular type of
entity can not benefit from an association of more
than 5 other entities
• to prevent an accidental deletion or
modification of related data
• cascade delete and update rules

GHJ-PWR 8
 CASCADE DELETE

• Cascade delete
• when we lose all knowledge of something

• An indicator to show the Cascade Delete status

C (Cascade) – deletes all children if a parent is deleted, for

example, if we delete all knowledge of a CUSTOMER we implicitly
delete all knowledge of its ORDERs, ORDERs only exist within the
context of a CUSTOMER
X (Restrict) – inhibits the deletion of a parent if any children still
exist, normally this rule applies only when the child must be
associated with the parent – the relationship is mandatory
N (Nullify) – the deletion of a parent and a child can be
performed separately, it applies when both ends of the
relationship are optional
[Barker II, pp. 7-15]
GHJ-PWR 9
 CASCADE DELETE - EXAMPLE

EMPLOYEE assigned to
DEPARTMENT
# symbol # number
* name responsible for * name
* address * telephone number
C – deletes all children if a parent is deleted
EMPLOYEEs only exist within the context of a DEPARTMENT

If we delete all knowledge of a Dept 50

we implicitly delete all knowledge of its EMPLOYEEs

Lynne
Dept 50 Robert
Harriet
before delete after delete
 SEPARATE DELETION - EXAMPLE

EMPLOYEE assigned to
DEPARTMENT
# symbol # number
* name responsible for * name
* address * telephone number

N (Nullify) – the deletion of a parent and a child can be performed separately,

it applies when both ends of the relationship are optional

EMPLOYEEs exist separately of a DEPARTMENT

If we delete all knowledge of a Dept 50
we replace the value 50 to null
Lynne ???
Dept 50 Robert ??? value null
Harriet ???
before delete after delete
 RESCTRICT DELETION - EXAMPLE

EMPLOYEE assigned to
DEPARTMENT
# symbol # number
* name responsible for * name
* address * telephone number

X - inhibits the deletion of a parent if any children still exist

DEPARTMENT must be as long as an EMPLOYEE

If we try delete all knowledge of a Dept 50,
the associated Robert is not allowed to delete Dept 50

Dept 50 Robert Dept 50

before delete after delete

 CASCADE UPDATE INDICATOR

EMPLOYEE assigned to
DEPARTMENT
# symbol # number
* name responsible for * name
* address * telephone number

D (Default) – if the ID of a parent changes, a new value from a new

instance must be used to replace this ID value or the previously set
the default value

Lynne
Dept 50 Robert Dept 48
Harriet
before update after update
GHJ-PWR 13
 NON-TRANSFERABLE RELATIONSHIP

An entity instance typically may be connected to another via a

relationship, and then it may be disconnected or re-connected to
another instance of the same type.
If this is not allowed, then this relationship should be named non-
transferable [Barker II, pp. 7-12].
Non-transferable relationship on the appropriate end

the owner of
CUSTOMER ACCOUNT
owned by

Each ACCOUNT must be owned by one and only one CUSTOMER,

and it can never be transferred to another CUSTOMER.

GHJ-PWR 14
 Business cohesion rules - to enforce through
application code

PURCHASE ORDER
source at
# number # number
* date source of * from date
* value * total amount

Condition for Rule

Creating an instance entity type If the PURCHASE date is not earlier
PURCHASE than the date of ORDER
 CONCEPT OF GENERALIZATION – THE
HIERARCHY

• This may be seen as an abstraction process where a set of similar

types is regarded as a higher-level, generic type. [Tsichritzis,
Lochovsky, 1982]
• Generalization is most commonly applied to entities.
• The is – a relationship between an entity and a more generic
entity.
• Super-type – for higher–level entities
• Sub-type – for lower–level entities
• A set of subtypes together with their super-type and the ″is-a”
relationship between them assume a shape hierarchy.
• Generalization - specialization

GHJ-PWR 16
 INHERITANCE – THE HIERARCHY
PROPERTY

• The principle of property inheritance is that a sub-type possesses

or inherits the attributes and relationships from its super-type
• Inheritance is a useful concept in a generalization hierarchy as
attributes or relationships that are common to many sub-types are
specified only once on the super-type, and do not have to be
repeated on sub-types that also possess those properties
• Multiple inheritance – a sub-type may have more than one super-
type
• Multi levels (nest) inheritance – a sub-type may be both a sub-type
of another entity and a super-type for another sub-types

GHJ-PWR 17
Illustrate the generalization
VEHICLE

BOAT Super-types

MOTORCAR
Sub-types are represented
as divisions of the entity TRACK
box, where each division Sub-types
corresponds to a sub-type. CAR

AEROPLANE

AIRCRAFT

[CASE*Method]
GLIDER

GHJ-PWR 18
 SUB-TYPE RULES

• An entity sub-type is a type entity: must conform to all rules for

an entity
• An entity may be split into two or more exclusive sub-types
• A sub-type entity implicitly inherits all attributes, relationships
and a business function of an entity a higher level, known as a
super-type
• A sub-type may have attributes and/or relationships in their
own right
• A sub-type may be further sub-typed to lower levels
[Barker II, pp.6-4]

GHJ-PWR 19
 INSTANCES OF AN ENTITY IN A SUPER-
TYPE

An instance of a super-type may

• be only one of sub-types – exclusion
• be one from many sub-types – not exclusion (overlapping sub-
types)
• not be an instance of one of the defined sub-types – not exhaustion
[Barker II, pp. 7-2 to 7-5]

GHJ-PWR 20
 EXCLUSION AND EXHAUSTION IN
GENERALIZATION HIERARCHY

• exclusion – where an instance of one sub-type cannot be

an instance of another sub-type
• exhaustion – where sub-type instances consist of all
super-type instances
• In case not exhaustion – adding an extra sub-type for
completeness
• In case not exclusion – choose a name to each set of
overlapped sub-types

GHJ-PWR 21
Not exhaustion case
VEHICLE

BOAT

MOTORCAR
A non exhaustive hierarchy
is one where TRACK added sub-type
a super-type instance may exist
that is not an instance CAR
of one of the defined subtypes
OTHER VEHICLE

AEROPLANE
AIRCRAFT

GLIDER
CASE*Method
GHJ-PWR 22
 Exclusive relationship and it’s representation

From the same entity type two or more relationship may be mutually exclusive.

An exclusive arc
belongs to
ACCOUNT PERSON
# code
has got
# number owned by
ORGANIZATION
# code
owner of

This means that each ACCOUNT must either belong to one and only one
PERSON or be owned by one and only one ORGANIZATION.
But never a combination [Barker II, pp. 4-3].
GHJ-PWR 23
 RULES OF AN EXCLUSIVE ARC

• Exclusivity is represented by an arc across relevant

relationship ends, with a small dot/circle where they join
• Relationship ends must be all mandatory or all optional
• An arc must span
• at least two relationship ends
• only a relationship from the same entity type, nor from a
sub-type and their super-type
• A relationship ends can only be in one arc
[Barker II, pp.7-9]

GHJ-PWR 24
 REDUNDANT RELATIONSHIP

• An Entity Relationship Diagram should not have any relationship that can, in all
circumstances, be derived from other relationship [Barker II, pp. 7-14]
• The relationship between a customer and a bank is derived from two relationships:
every customer must be the owner of one or more accounts and every account is
operated by one and only one bank.

the owner of
CUSTOMER ACCOUNT
owned by
operated by

the operator for

referenced by

BANK
the referenced for
GHJ-PWR 25
 CLASSICAL STRUCTURES

• Changes over time/history

• Bill of materials
• Classification and categories
• Hierarchies/organization units
• Classical structure for INVOICE, ORDER

GHJ-PWR 26
 CHANGES OVER TIME: ATTRIBUTE CHANGES
[BARKER II, P.8.9]

a) before b) after

CONTRACT STATUS
# * date # * from date
* status * value
o to date
The attribute status
becomes an entity type of

Business questions:
shown in
What was the previous status of the contract?
CONTRACT
Can a contract ever be in various state on the same
# * number
day?
# * date
GHJ-PWR 27
 CHANGES OVER TIME : RELATIONSHIP CHANGES
[BARKER II, P.8.10]

a) before b) after
for
COMPANY CAR CONTRACT COMPANY CAR
# * date # * symbol
# * symbol
o termination
* date the subject of
date

assigned to with
Adding a new entity to cater
for a relationship change – this
the driver of a party to
entity is the many to many
EMPLOYEE relationship solution through
EMPLOYEE
# code the intersection entity
# code

Business question:
Who was the previous driver of the company car?
GHJ-PWR 28
 BILL OF MATERIALS: IMPLOSION/EXPLOSION
[BARKER II, P.8.10-8.11]

b) An intersection entity
a) Many to Many
STANDARD
CONSTITUENT
PART/PRODUCT made up of o quantity
# * code o fitting instruction
* name
* description on the parts
for use of
list for

used as made up of
used within
PART/PRODUCT
Business questions:
# * code
* name
Which parts are used in different products ?
* description
What are products and parts made of ?

GHJ-PWR 29
 CLASSIFICATION
[BARKER II, PP. 8.12-8.13]

CLOTHES
a) simple
# * code Classification entity
Classification attribute * name implements coded
* size classification
b) coded classification
in
CLOTHES SIZE
# * code # * value
the classification
* name * description
for

In a coded classification a value of each size requires description, e.g.

S: 150 – 160 cm
M: 160 -170 cm
Clothes may be in one size only at a time.
The classification entity eliminates typing errors.
GHJ-PWR 30
 HIERARCHY - SIMPLE MODEL
BARKER II, PP.8.2-8.4]

The simple mapping of hierarchical relationships

between different organizational structures: many to
TEAM
one mandatory relationships.
within
made up of
Business questions:
DEPARTMENT
What way may a department submit a report directly
to management? within
What happens if we introduce a new team within made up of
division?
What happens if we introduce another level – a new DIVISION
structure element?
within
made up of
MANAGEMENT
GHJ-PWR 31
 HIERARCHY - ALTERNATIVE 1

ORGANIZATION
UNIT
# code within
Classification attribute * name
* type

made up of

The entity type ORGANIZATION UNIT with a recursive relationship fulfills the
disadvantages of the previous model:
direct links are possible between different organizational structures
the addition of any organization unit type

Business question:
Which type of organizational units is at the top of the hierarchy?
GHJ-PWR 32
 HIERARCHY – ALTERNATIVE 2

ORGANIZATION UNIT
Sub-types of the # code
organization unit * name
within
STRUCTURE
ELEMENT

The top of the

hierarchy with no MANAGEMENT made up of
parent connection
Recursive many to one
of mandatory
relationship for other
Classification entity the classification for elements of
implements coded organizational
classification and allows ORGANIZATION UNIT
structure than the top
any number of structure TYPE
of the hierarchy
levels # name
GHJ-PWR 33
 SPECIFIC EXAMPLE – INVOICE/ORDER

PRODUCT
# * code
for * name
INVOICE ITEM
* vat
# * number shown on
* net price GOODS
o quantity * unit of measure Sub-types for
two possible
part of SERVICE kinds of items
o comment
Intersection
entity made up of
placed on COMPANY
INVOICE # * symbol This entity can be
a party to divided into two
# * number * name
placed by sub-types: SUPPLIER
* date * city
and RECIPIENT
* zip code
the source of
* street
Two relationships o phone
showing both participants
GHJ-PWR in one sale 34
 QUESTIONS TO HELP YOU L4 STUDY

1. What are integrity constraints on an entity attributes - level?

2. What are integrity constraints on an entity - level?
3. What are a reference integrity constraints ? - on the level of the relationship between
the entity types.
4. What are the cascading delete rules for related entities? (INVOICE, ITEM of INVOICE)
5. Can all business rules be presented using the data modeling language?
6. What is a data domain defined for?
7. What does an entities occur in a relationship „is – a”?
8. Can attributes or relationships be common to many sub-types in a generalization
concept?
9. Can a sub-type be both a sub-type and a super-type?
10. Provide an example of super-type of an entities
11. Provide an example of sub-type of an entities

GHJ - PWR 35
 QUESTIONS TO HELP YOU L4 STUDY (CONT.)
12. Explain by example, an exclusion between sub - types in generalization
concept
13. Explain by example, an exhaustion of a super -type in generalization
concept
14. What are rules of an exclusive arc?
15. When the relationship between entity types non-transferable is?
16. If exists a requirement to remember all changing values of a price, how to
transform an entity model: (PRODUCT, code, name, price)?
17. If exists a requirement to remember all changing users of every computer,
how to transform an entity relationship model: Every EMPLOYEE is a user of
one or more COMPUTER?
18. What are different ways of the presentation of a formal entity classification
on the ERD? (CAR, brand)
19. What are the ways to represent hierarchical relationships between entity
types on an ERD? (SOLDIER, military degree). What are the typical
advantages and disadvantages of each of them?
GHJ - PWR 36