ER Model Asst. Prof. Dr. Kanda Runapongsa Saikaew - - PDF document

Electricite Du Laos (EDL)

• Dr. Kanda Runapongsa Saikaew, Computer Engineering, KKU

1

ER Model

• Asst. Prof. Dr. Kanda

Runapongsa Saikaew (krunapon@kku.ac.th) Dept of Computer Engineering Khon Kaen University

2

Objectives (1/2)

 Relational Data Model Terminology of relational data model How tables are used to represent data Properties of database relations How to identify candidate, primary, and

foreign keys

Meaning of entity integrity and referential

integrity

Objectives (2/2)

How to use ER modeling in database

design

The basic concepts of an ER model A diagrammatic technique for

displaying an ER model

How to identify and solve problems in

an ER model

3

Electricite Du Laos (EDL)

• Dr. Kanda Runapongsa Saikaew, Computer Engineering, KKU

2

Data Model

Integrated collection of concepts for

describing data, relationships between data, and constraints on the data

Has three components

A structural part A manipulative part A set of integrity rules

4

Relational Model Terminology

Relation: table with columns and rows Attribute: named column of a relation Domain: set of allowable values for

• ne or more attributes

Tuple: a record of a relation Relational database – collection of

normalized relations with distinct relation names

5

RM Terminology Samples

6

Electricite Du Laos (EDL)

• Dr. Kanda Runapongsa Saikaew, Computer Engineering, KKU

3

Attribute Domain Samples

7

Alternative Terminology

Relation, table Attribute, column, field Tuple, record, row

8

Properties of Relations (1/2)

Table name is distinct from all other

table names in the database

Each cell of table contains exactly

• ne atomic (single) value

Each column has a distinct name Values of a column are all from the

same domain

9

Electricite Du Laos (EDL)

• Dr. Kanda Runapongsa Saikaew, Computer Engineering, KKU

4

Properties of Relations (2/2)

Each record is distinct; there are no

duplicate records

Order of columns has no significance Order of records has no significance

10

Relational Keys (1/2)

Superkey

A column, or a set of columns, that

uniquely identifies a record within a table

Candidate Key

Superkey (K) such that no proper subset

is a superkey within the table

In each record, values of K uniquely

identify that record (uniqueness)

No proper subset of K has the

uniqueness property (irreduciability)

11

Relational Keys (2/2)

Primary Key

Candidate key selected to identify

records uniquely within table

Alternate Keys

Candidate keys that are not selected to

be primary key

Foreign Key

Column, or set of columns, within one

table that matches candidate key of some (possibly same) table

12

Electricite Du Laos (EDL)

• Dr. Kanda Runapongsa Saikaew, Computer Engineering, KKU

5

Relational Integrity (1/2)

Null

Represents value for a column that is

currently unknown or not applicable for record

Deals with incomplete or exceptional

data

Represents the absence of a value and

is not the same as zero or spaces, which are values

13

Relational Integrity (2/2)

Entity Integrity

Every table must have a primary key Column or columns chosen to be the

primary key should be unique and not null

Referential Integrity

If FK exists in a table, FK value must

match a candidate key value of some record in its home table

14

Referential Integrity Broken

15

• There is a foreign key (artist_

st_id id) value in the album table that references a non-existent artist

• This anomaly came about when the

record for an artist called "Aerosmith", with an artist_id t_id of "4", was deleted from the artist table, even though the album "Eat the rich" referred to this artist

Electricite Du Laos (EDL)

• Dr. Kanda Runapongsa Saikaew, Computer Engineering, KKU

6

Referential Integrity Enforced

If referential integrity had been

enforced

The deletion of the main record would

have been possible, but its associated record would have been deleted as well

Alternatively, the existence of an

associated record would not allow the delete operation of the referenced record, and instead return an error code.

16

Objectives (2/2)

How to use ER modeling in database

design

The basic concepts of an ER model A diagrammatic technique for

displaying an ER model

How to identify and solve problems in

an ER model

17

ER Modeling

Top-down approach to database

design

Start by identifying the important data

(called entities) and relationships between the data

Then add more details

Attributes of entities and relationships Constraints on entities, relationships,

and attributes

18

Electricite Du Laos (EDL)

• Dr. Kanda Runapongsa Saikaew, Computer Engineering, KKU

7

Entities

Entity

A set of objects with the same

properties, which are identified by a user

• r organization as having an

independent existence

Entity occurrence

Each uniquely identifiable object within a

set

19

Entities with Physical and Conceptual Existence

20

ER Diagram of Entity

Entity Diagram

Use a rectangle to represent an entity Insert the entity name inside the

rectangle

Example

21

Video

Electricite Du Laos (EDL)

• Dr. Kanda Runapongsa Saikaew, Computer Engineering, KKU

8

Attributes

Property of an entity or a relationship Hold values that describe each

• ccurrence of an entity or relationship

Represent the main source of data

stored in the database

22

ER Diagram of Attributes

Attribute Diagram

Use an oval to represent an attribute Insert the attribute name inside the oval

23

Video catalogNo title category dailyRental price

Classification of Attributes

Attributes can be classified as being

Simple or composite Single-valued or multi-valued Derived

24

Electricite Du Laos (EDL)

• Dr. Kanda Runapongsa Saikaew, Computer Engineering, KKU

9

Attributes (1/3)

Simple attribute

Attribute composed of a single

component

Example: salary

Composite attribute

Attribute composed of multiple

components

Example: name (firstname, lastname)

25

Attributes (2/3)

Single-valued attribute

Attribute that holds a single value for an

entity occurrence

Example: gender (M, F)

Multi-valued attribute

Attribute that holds multiple values for

an entity occurrence

Example: phone:area-code,number

(043-362-160)

26

Attributes (3/3)

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

Example: age which can be derived

from birthdate

27

Electricite Du Laos (EDL)

• Dr. Kanda Runapongsa Saikaew, Computer Engineering, KKU

10

28

Example: Entity

 ER diagram of an

entity “Employees”

 Entity set “Staff”

ssn name lot 111 A 12 222 B 24



Entity “111 A 12” is similar to Entity “222 B 24” since both of them are described using the same set

• f attributes

Staff ssn name lot

 The key of this entity

is “ssn” which is underlined

Relationships

Relationship

A set of meaningful associations among

entities

Relationship occurrence

Each uniquely identifiable association

within a set

29

ER Diagram of Relationships

30

Electricite Du Laos (EDL)

• Dr. Kanda Runapongsa Saikaew, Computer Engineering, KKU

11

Degree of a Relationship

Degree of a relationship is the

number of participating entities in relationship

Relationship of degree

Two is binary Three is ternary Four is quaternary

31

Example of ternary relationship

32

Recursive relationships

Relationship where same entity

participates more than once in different roles

Relationships may be given role

names to indicate purpose that each participating entity plays in a relationship

33

Electricite Du Laos (EDL)

• Dr. Kanda Runapongsa Saikaew, Computer Engineering, KKU

12

Example of a recursive relationship

34 35

Example1: Relationships

A relationship between “Employees”

and “Departments”

“since” is a descriptive attribute of this

relationship

A relationship must be uniquely

identified by the participating entities

lot dname budget did since name Works_In Departments Employees ssn

36

Example2: Relationships

Same entity set could participate

in different relationship sets, or in different “roles” in same set.

Reports_To lot name Employees super- visor ssn Works_In Departments Employees

Manages

super-visee

Electricite Du Laos (EDL)

• Dr. Kanda Runapongsa Saikaew, Computer Engineering, KKU

13

Multiplicity constraints on relationships

Represents the number of

• ccurrences of one entity that may

relate to a single occurrence of an associated entity

Represents policies (called business

rules) established by user or company

37

Multiplicity constraints

The most common degree for

relationships is binary

Binary relationships are generally

referred to as being

One-to-one (1:1) One-to-many (1:*) Many-to-many (*:*)

38

1:1 relationship – individual examples

39

Electricite Du Laos (EDL)

• Dr. Kanda Runapongsa Saikaew, Computer Engineering, KKU

14

1:1 relationship - multiplicity

40

1:* relationship – individual examples

41

1:* relationship – multiplicity

42

Electricite Du Laos (EDL)

• Dr. Kanda Runapongsa Saikaew, Computer Engineering, KKU

15

*:* relationship – individual examples

43

*:* relationship - multiplicity

44

Complex relationships – individual examples

45

Electricite Du Laos (EDL)

• Dr. Kanda Runapongsa Saikaew, Computer Engineering, KKU

16

Complex relationship - multiplicity

46

Summary of multiplicity constraints

47

Cardinality and Participation Constraints

Cardinality

Describe the number of possible

relationships for each participating entity

Participation

Determine whether all or only some

entity occurrences participate in a relationship

48

Electricite Du Laos (EDL)

• Dr. Kanda Runapongsa Saikaew, Computer Engineering, KKU

17

Cardinality and Participation Constraints Examples

49

Relationship with attributes

50

Strong and weak entities

Strong entity

Entity that is not dependent on the

existence of another entity for its primary key

Weak entity

Entity that is partially or wholly

dependent on the existence of another entity, or entities, for its primary key

51

Electricite Du Laos (EDL)

• Dr. Kanda Runapongsa Saikaew, Computer Engineering, KKU

18

52

Weak Entities

A weak entity can be identified

uniquely only by considering the primary key of another (owner) entity

Owner entity set and weak entity set

must participate in a one-to-many relationship set (one owner, many weak entities)

Weak entity set must have total

participation in this identifying relationship set

53

Example: Weak Entities

“Employees” is an owner entity set

and “Dependents” is a weak entity set

“Dependents” have total participation

in the relationship

lot name age pname Dependents Employees ssn Policy cost

Problems with ER models

Problems may arise when designing

an ER model called connection traps

Often due to a misinterpretation of the

meaning of certain relationships

Two main types of connection traps

Fan traps Chasm traps

54

Electricite Du Laos (EDL)

• Dr. Kanda Runapongsa Saikaew, Computer Engineering, KKU

19

Fan trap

Two entities have a 1:* relationship

that fan out from a third entity

But the two entities should have a

direct relationship between them to provide the necessary information

55

An example of a fan trap

56

Fan trap –individual example

57

Cannot tell which member of staff uses car SH34

Electricite Du Laos (EDL)

• Dr. Kanda Runapongsa Saikaew, Computer Engineering, KKU

20

Resolving the fan trap

58

Fan trap resolved – individual example

59

Can now tell which car staff use

Chasm trap

A model suggests the existence of a

relationship between entities

But the pathway does not exist

between certain entity occurrences

60

Electricite Du Laos (EDL)

• Dr. Kanda Runapongsa Saikaew, Computer Engineering, KKU

21

An example of a chasm trap

61

Chasm trap – individual example

62

Cannot tell which branch staff S0003 works at

Resolving the chasm trap

63

Electricite Du Laos (EDL)

• Dr. Kanda Runapongsa Saikaew, Computer Engineering, KKU

22

Chasm trap resolved – individual example

64

Can now tell which branch each member of staff works at.

Summary (1/2)

ER model popular for conceptual

design

Constructs are expressive, close to the

way people think about their applications

Basic constructs of ER model

 Entities  Relationships  Attributes (of entities and relationships)

65

Summary (2/2)

 Superkey

 A column, or a set of columns, that uniquely

identifies a record within a table

 Candidate Key

 Superkey (K) such that no proper subset is a

superkey within the table

 Entity Integrity

 Every table must have a primary key

 Referential Integrity

 If FK exists in a table, FK value must match a

candidate key value of some record in its home table

66

Electricite Du Laos (EDL)

• Dr. Kanda Runapongsa Saikaew, Computer Engineering, KKU

23

References (1/2)

Connolly and Begg, “Database

Systems: A Practical Approach to Design, Implementation and Management”, Pearson, 2004

http://en.wikipedia.org/wiki/Entity_inte

grity

http://en.wikipedia.org/wiki/Referentia

l_integrity

67

References (2/2)

http://www.docstoc.com/docs/421235

1/E-R-Diagram-(Entity-Relationship- Diagram)

68