Data Modelling with Entity-Relationship - The Complete Academic Support Hub

13/10/2017
1

Lecture 3

Data Modelling with Entity-Relationship Model

AssignmentTutorOnline

13/10/2017
2

Part 4 – Objectives

 How to use Entity–Relationship (ER) modeling
in database design.
 Basic concepts associated with ER model.
 Diagrammatic technique for displaying ER
model using Unified Modeling Language (UML).
 How to build an ER model from a requirements
specification.

4
A data model is a plan, or blueprint, for a
database design.
A data model is more generalized and
abstract than a database design.
It is easier to change a data model than it
is to change a database design, so it is the
appropriate place to work through
conceptual database problems.

The Data Model

13/10/2017
3

Concepts of the ER Model

 Entity types
 Relationship types
 Attributes

 Entity-Relationship model is a set of concepts and graphical
symbols that can be used to create conceptual schemas.
 Versions
– Original E-R model — Peter Chen (1976).
– Extended E-R model — Extensions to the Chen model.
– Information Engineering (IE) — James Martin (1990); it uses “crow’s
foot” notation, is easier to understand and we will use it.
– Unified Modeling Language (UML) — The Object Management Group; it
supports object-oriented methodology

E-R Model

13/10/2017
4

 Something that can be identified and the users want to
track. It can be an object, a concept, or person that
users are interested in recording information about.
 Examples:-Person, Account, Product, etc.
– Entity type/class — a collection of entities of a given type or
Group of objects with same properties, identified by
enterprise as having an independent existence.
– Entity instance — the occurrence of a particular
entity/Uniquely identifiable object of an entity type.
 There are usually many instances of an entity in an
entity class.

Entities

Examples of Entity Types

13/10/2017
5

CUSTOMER- The Entity Class and Two Entity
Instances

10
 Attributes describe an entity’s characteristics.
 Examples of attributes on Customer entity class are :-
Cust_no, cust_name, Cust_Dob, etc.
 All entity instances of a given entity class have the
same attributes, but vary in the values of those
attributes.
 Originally shown in data models as ellipses.
 Data modeling products today commonly show
attributes in rectangular form.

Attributes

13/10/2017
6

EMPLOYEE: Attributes in Ellipses

EMPLOYEE: Attributes in Entity Rectangle

13/10/2017
7

13
 Identifiers are attributes that name, or identify, entity instances.
 The identifier of an entity instance consists of one or more of the
entity’s attributes.
 Composite identifiers: Identifiers that consist of two or more
attributes
 Identifiers in data models become keys in database designs:
– Entities have identifiers.
– Tables (or relations) have keys.

Identifiers

Entity Attribute Display in Data Models

13/10/2017
8

15
 Entities can be associated with one another in relationships:
– Relationship classes: associations among entity classes
– Relationship instances: associations among entity instances
 In the original E-R model, relationships could have attributes but
today this is no longer done.
 A relationship class can involve two or more entity classes.

Relationships

Relationship Types

 Relationship type
– Set of meaningful associations among entity
types.
 Relationship occurrence
– Uniquely identifiable association, which
includes one occurrence from each
participating entity type.

13/10/2017
9

17
 Entity: is modeled as a
square
 Relationship: is modeled
as a link between the two
entities.
 Attribute: is modeled as a
oval.

Entity Relationship Notation

18
ID Name
Address

Entity Relationship: Example

Student Course
Course
13/10/2017
10

19
•Define the relationship:
•has to be a verb
•Recommended to be one word
•has to be clear
This reads like this: A student enrolls in a course. A course
has students.

Entity Relationship:

Student Course
Enrolls
has

20
 The degree of a relationship is the number of entity classes
participating in the relationship:
– One entity class has a unary or recursive relationship of degree
one.
– Two entity classes have a binary relationship of degree two.
– Three entities have a ternary relationship of degree three.

Degree of a Relationship

13/10/2017
11

 A recursive relationship occurs when an entity has a
relationship to itself
 Relationship type where same entity type
participates more than once in different roles
Marries
Manages

Recursive Relationships

Person
Employee

Binary Relationship

13/10/2017
12

Ternary Relationship

Quaternary relationship called Arranges

13/10/2017
13

The principle difference between an entity and
a table (relation in a relational database) is that
you can express a relationship between entities
without using foreign keys.
This makes it easier to work with entities in the
early design process where the very existence
of entities and the relationships between them is
uncertain.

Entities and Tables

Attributes

 Attribute
– Property of an entity or a relationship type.
 Attribute Domain
– Set of allowable values for one or more attributes.
 Simple Attribute
– Attribute composed of a single component with an
independent existence.
 Composite Attribute
– Attribute composed of multiple components, each
with an independent existence.

13/10/2017
14

Attributes

 Single-valued Attribute
– Attribute that holds a single value for each occurrence
of an entity type.
 Multi-valued Attribute
– Attribute that holds multiple values for each occurrence
of an entity type.
 Derived Attribute
– Attribute that represents a value that is derivable from
value of a related attribute, or set of attributes, not
necessarily in the same entity type.

Keys

 Candidate Key
– Minimal set of attributes that uniquely
identifies each occurrence of an entity type.
 Primary Key
– Candidate key selected to uniquely identify
each occurrence of an entity type.
 Composite Key
– A candidate key that consists of two or more
attributes.

13/10/2017
15

Structural Constraints

Multiplicity is made up of two types of restrictions
on relationships: cardinality and participation.
Cardinality
– Describes maximum number of possible
relationship occurrences for an entity
participating in a given relationship type.
Participation
– Determines whether all or only some entity
occurrences participate in a relationship.

Multiplicity as cardinality and participation
constraints

13/10/2017
16

31
Cardinality means “count,” and is expressed as
a number.
Maximum cardinality (Cardinality) is the
highest number of entity instances that can
participate in a relationship.
Minimum cardinality (Optionality) is the
smallest number of entity instances that must
participate in a relationship.

Cardinality & Optionality

32
 Maximum cardinality is the highest number of entity instances that
can participate in a relationship.
 There are three types of maximum cardinality:
– One-to-One [1:1]– One-to-Many [1:N]– Many-to-Many [N:M]

Maximum Cardinality

13/10/2017
17

The Three Types of
Maximum Cardinality

34
 In a one-to-many relationship:
– The entity on the one side of the relationship is called the parent entity
or just the parent.
– The entity on the many side of the relationship is called the child entity
or just the child.
 In the figure below, EMPLOYEE is the parent and COMPUTER is the
child:

Parent and Child Entities

13/10/2017
18

35
The relationships we have been discussing are
known as HAS-A relationships:
– Each entity instance has a relationship with
another entity instance:
»An EMPLOYEE has one or more
COMPUTERs.
»A COMPUTER has an assigned
EMPLOYEE.

HAS-A Relationships

36
 Minimum cardinality is the minimum number of
entity instances that must participate in a
relationship.
 Minimums are generally stated as either zero or one:
– IF zero [0] THEN participation in the relationship by the
entity is optional, and no entity instance must participate in
the relationship.
– IF one [1] THEN participation in the relationship by the
entity is mandatory, and at least one entity instance must
participate in the relationship.

Minimum Cardinality(Optionality)

13/10/2017
19

37
 As shown in the examples in a following slide:
– Minimum cardinality of zero [0] indicating optional
participation is indicated by placing an oval next to the optional
entity.
– Minimum cardinality of one [1] indicating mandatory
(required) participation is indicated by placing a vertical hash
mark next to the required entity.

Indicating Minimum Cardinality

38
 Look toward the entity in question:
– IF you see an oval THEN that entity is optional (minimum
cardinality of zero [0]).
– IF you see a vertical hash mark THEN that entity is mandatory
(required) (minimum cardinality of one [ 1]).

Reading Minimum Cardinality

13/10/2017
20

The Three Types of
Minimum Cardinality

Data Modeling Notation

13/10/2017
21

Data Modeling Notation:
ERwin

42
 A Student can enroll in one Course at a time.
 A Course can have one to many Students at a
time.
 A Lecturer can teach in zero to many Courses at
a time.
 A Course would have one or more Lectures
teaching in that course at anytime.

Lets look at some examples:

Entity
Constraint
13/10/2017
22

43
 one
 one to many
 zero to many
(Min One, Max One)
(Min One, Max Many)
(Min Zero, Max Many)

Constraints:

A
A
A

44
Optionality specifies the minimum number of
instances of the related entity.
Cardinality specifies the maximum number of
instances of the related entity.

E-R: Optionality and Cardinality

A B
Cardinatlity
Optionality
13/10/2017
23

Lets see how it is implemented:

 A Student can enroll in one Course at a time only.
 A Course can have one to many Students at a time.
 A Lecturer can teach in zero to many Courses at a time.
 A Course would have one or more Lectures teaching at it at
anytime.

Student Course
Lecturer

46
 Note that:
 (1) ERwin cannot indicate true
minimum cardinalities on N:M
relationships
 (2) Visio introduces the
intersection table instead of
using a true N:M model

Data Modeling Notation:
N:M and O-M

13/10/2017
24

47
 An ID-dependent entity is an entity (child) whose identifier
includes the identifier of another entity (parent).
 The ID-dependent entity is a logical extension or sub-unit of the
parent:
– BUILDING : APARTMENT
– PAINTING : PRINT
 The minimum cardinality from the ID-dependent entity to the parent
is always one.

ID-Dependent Entities

A solid line
indicates an
identifying
relationship

ID-Dependent Entities

13/10/2017
25

Entity Type

 Strong Entity Type
– Entity type that is not existence-dependent
on some other entity type.
 Weak Entity Type
– Entity type that is existence-dependent on
some other entity type.

Strong entity type called Client and weak entity type
called Preference

13/10/2017
26

51
 A weak entity is an entity whose exisitence depends upon another
entity.
 All ID-Dependent entities are considered weak.
 But there are also non-ID-dependent weak entities.
– The identifier of the parent does not appear in the identifier of the
weak child entity.

Weak Entities

Weak entities
must be
indicated by an
accompanying
text box in
Erwin – There is
no specific
notation for a
nonidentifying
but weak entity
relationship
A dashed line
indicates a
nonidentifying
relationship

Weak Entities (Continued)

13/10/2017
27

ID-Dependent and Weak Entities

A subtype entity is a special case of a
supertype entity:
– STUDENT :
UNDERGRADUATE or
GRADUATE
The supertype contains all common attributes,
while the subtypes contain specific attributes.
The supertype may have a discriminator
attribute that indicates the subtype.

Subtype Entities

13/10/2017
28

Subtypes with a Discriminator

56
 If subtypes are exclusive, one supertype relates to at most one
subtype.
 If subtypes are inclusive, one supertype can relate to one or more
subtypes.
 Other database texts refer to exclusive subtypes as being disjoint.
 Inclusive subtypes are also known as overlaps.

Subtypes: Exclusive (Disjoint)
or Inclusive (Overlap)

13/10/2017
29

Subtypes: Exclusive or
Inclusive (Continued)

 Relationships connecting supertypes and subtypes are called IS-A
relationships, because a subtype IS A supertype.
 The identifer of the supertype and all of its subtypes must be
identical, i.e., the identifier of the supertype becomes the identifier
of the related subtype(s).
 Subtypes are used to avoid value-inappropriate nulls.

Subtypes: IS-A relationships