entity relationship e r modeling mine ee (1)

7/23/2019 Entity Relationship e r Modeling Mine Ee (1)

1/64

ITS232

Introduction To Database Management Systems

Siti Nurbaya Ismail

Faculty of Computer Science & Mathematics!ni"ersiti Te#nologi M$%$ !iTM' (edah

) sitinurbaya*#edah+uitm+edu+my ) http,--...+sitinur/0/+.ordpress+com )) $213 3 ) e4t,205/ ) /21665 052 )

CHAPTER 4Entity Relationship (E-R) Modeling

(ERD)


2/64

4.0 Entity Relationship (E-R) Modeling4.1 The Entity Relationship (ER) Model4.2 Developing An E-R Diagram4. Data!ase Design "hallenges

"hapter 4# Entity Relationship (E-R)Modeling


3/64

"hapter 4# Entity Relationship (E-R)Modeling$asi% Modeling "on%ept

3

A model is description or analogy used to visualizesomething that cannot be directly observed.


4/64


4

Relatively simple representations of complex realworld data structures

A data model represents:

data structures and their characteristics relation and constraints

Can be physical or abstract: car, student = physical sub ect, register= abstract

!sed by database designer as: communications tools to communicate and

understanding between a client and the databasedesigner, which the database to be develop.


5/64


5

The importan%e o& data modeling#

Data" constitute the most basic information units employed by a

system Appli%ation " is created to manage data and to transform data to

information 'ie

" di#erent people views the same data di#erently based ontheir understanding Model

" helps di#erent user to have the holistic view of the same data


6/64


6

2+ Conceptual le"el

3+ Internal le"el

7hysical dataorgani8ation

/+ 94ternal le"el :ie. / :ie. 2 :ie. n

Conceptual Schema

Internal Schema

Database

!ser n!ser 2!ser /

;

Conceptual Model

94ternal Model

Internal Model

7hysical Model

1designer


7/64

"hapter 4# Entity Relationship (E-R)Modeling4.1 The Entity Relationship (E-R) Model

7

$ased on the set theory and the relational theory, it isused as tools to:" translate di#erent views of data among managers,

users and programmers to %t into a common wor&

" de%ne data processing and constraints to help meetthe di#erent views

" help implement the database" considered as a stage in a database design

preceding'before the relational database modeling" gives data structures representation of:( what information to be stored ( the relationships between informational

elements and constraint on the data structure( relationship


8/64


8

)R model forms the basis of an )R diagram *)R+ )R+ represents %on%ept al data!ase as viewed by

end user )R+s depict'shows database-s main components:

" Entities" Attri! tes" Relationships


9/64

"hapter 4# Entity Relationship (E-R)Modeling4.1 The Entity Relationship (E-R) Model# Entity

9

Entity Represents an object from the real world Collection of similar entities that made up entity sets Refers to entity set and not to single entity

occurrence Corresponds to table and not to row in relational

environment n both Chen and Crow-s /oot models, entity is

represented by rectangle containing entity-s name )ntity name, a noun , is usually written in capital

letters


10/64

"hapter 4# Entity Relationship (E-R)Modeling4.1 The Entity Relationship (E-R) Model#

Attri! te

10

Attri! te Characteristics of entities Property that explains about entity Correspondents to %elds of a table 0rimary &ey are underline with a straight line /oreign &ey are underline with dotted line or an ( Chen 1odel

attributes are represented by ovals and are connected

to entity rectangle with a line each oval contains the name of attribute it represents

Crow-s /oot 1odel attributes are written in attribute box below entity rectangle


11/64


11

2he Attributes of the 32!+)42 entity: Chen 5Crow-s /oot


12/64

"hapter 4# Entity Relationship (E-R)Modeling4.1 The Entity Relationship (E-R) Model#

Relationship

12

Relationship Associates between entities' connection between two or

more entities 6ogical interaction among the entities in a relational

database 7perate in !oth directions Chen 1odel

Crow-s /oot 1odel


13/64

"hapter 4# Entity Relationship (E-R)Modeling4.2 Developing An E-R Diagram

13

+evelopment of )R model is an Iterative Process thatinvolved:

*tep1#8eneral narrative of organizational operationsdeveloped

*tep2# $asic )9R 1odel graphically depicted and reviewed

*tep #

1odi%cations made to incorporate newly discovered )Rcomponents

Repeat pro%ess# !ntil designers and users agree on complete )9R

+iagram


14/64

"hapter 4# Entity Relationship (E-R)Modeling4.2 Developing An E-R Diagram# Model

"omponents

14

)42 2 ;ariations of entity:i.


15/64

"hapter 4# Entity Relationship (E-R)Modeling4.2 Developing An E-R Diagram# "hen Model#

Entity

15

Entity Corresponds to table and not to row in relational

environment Represented by rectangle containing entity-s name

)ntity name, a noun , is usually written in capitalletters )xamlpe: )ntity STUDENT with attributes


16/64


Entity

16

'ariations o& Entity#i. +ea, Entityii. Re% rsive Entityiii. "omposite Entityiv. Entity * pertype and * !type


17/64


Entity

17

'ariations o& Entity#i. +ea, Entity

. )xistence9dependent )ntity $ depends on )ntity A to exist. f A does not exist

then $ cannot exist too . 0rimary &ey partially or totally derived from parent

entity in relationship . +atabase designer determines whether an entity is

wea& based on ! siness r les


18/64


Entity

18


19/64


Entity

19


20/64


Entity

20

'ariations o& Entity#ii. Re% rsive Entity

)ntity set that have relationship with the sameentity set

)xample: EMPLOYEE entity

employeeNO employeeNAME employeeSPOUSE111 Ali 444

222 Ah Chong

333 Bazil444 She iz 111

employeeNO employeeNAME employeeMANA!E" 111 Ali 333222 Ah Chong 333333 Bazil 333

444 She iz

EMPLOYEEemployeeNO

employeeNAME

ma ie#

11

employeeSPOUSE

EMPLOYEEemployeeNO

employeeNAME

manage

11

employeeMANA!E"


21/64


Entity'ariations o& Entity#iii. "omposite Entity

7riginally a relationship between > entities thatinvolved in 1:4 relationship

Composite entity ta&es its primary &ey from bothentities that it bridges

)xample: en oll

$%en%'D $%en%NAME

2(()22222

2(()33333

2(()44444

+o& $e'D +o& $eNAME

'TS232 Da%a,a$e

CSC31- 'P

CSC2(3 OS

STUDENT$%en%'D

$%en%NAME

en oll M

g a#e

COU"SE +o& $e'D

+o& $eNAME

N

$%en%'D +o& $e'D g a#e2(()22222 'TS232 A.2(()22222 CSC31- B.

2(()33333 CSC2(3 B2(()***** 'TS232 A/

STUDENT STUDENT0COU"SE EN"OLL COU"SE


22/64


Entity

22

'ariations o& Entity#iv. Entity * pertype * !type

0arent9Child relationship

3upertype contains the shared attributesan entity type that include distinct

subclasses that re?uired to be presented indata model

parent

3ubtype contains the uni?ue attributesan entity type that has a distinct role and

also a member of supertypechild


23/64


Entity

23


. )xample: 3uperype * EMPLOYEE3ubtype * Enginee &ll/Time


24/64


Entity

24


ave two types of relationship:Dis oint /verlapping

!ni?ue subtype4on9overlapping:subtypes can beone of the types

7verlapping:3ubtypes can beeither one or both

of the subtypesndicate with: ndicate with:

G Gs


25/64


Entity

25


. )xample: +is oint 5 7verlap

?"erlap

Dis@oint

!

!$


26/64


Entity

26


. )xample: +is oint


27/64


Entity

27


. )xample: +is oint 5 7verlap


28/64


Attri! tes

28

Attri! tes Represented by ovals that are connected to entity

with a line 7val contains of attribute *%eld it represents 0@ are underlined with straight line /@ are underlined with doted line or ( )xample: )ntity STUDENT with attributes

name +o& $e $%en%'D a## e$$ email

STUDENT

$%en%'D

name

+o& $eemail

a## e$$


29/64


Attri! tes

29

Attri! tes types#i. *imple Attri! tesii. "omposite Attri! tesiii. M ltival ed Attri! tesiv. Derived Attri! tes


30/64


Attri! tes

30

Attri! tes types#i. *imple Attri! tes

An attribute composed of single component with anindependent existence

Cannot be subdivided into smaller components )xample: gen#e ma %ial $%a%&e$

STUDENT $%en%'D phoneNO

age

gen#ename

a## e$$ a## e$$TO5N

a## e$$POSTCODEa## e$$NO

Simple $ttributes


31/64


Attri! tes

31

Attri! tes types#ii. "omposite Attri! tes

An attribute composed of multiple components,each with an independent existence

Can be further subdivide to additional attributes )xample: name 6i $% mi##le la$%

a## e$$ $% ee% +i%y $%a%e zip

Composite $ttributes

STUDENT $%en%'D phoneNO

age

gen#e name

a## e$$ a## e$$TO5N

a## e$$POSTCODEa## e$$NO


32/64


Attri! tes

32

Attri! tes types#iii. M ltival ed Attri! tes

attribute that holds multiple values for eachoccurrence of an entity type

3hould not be implemented multivalued attributesin relational database

Can simpli%es multivalued attributes by:a. Create several attri! tes

b. Create ne entity of the original multivaluedattributes components

)xample: phone n&m,e han#$e% o66i+e home

7&ali6i+a%ion #iploma #eg ee ma$%e


33/64


Attri! tes

33


%onta%tid

rstname

lastname ho!!ies

B D 8eorge $arnes reading

EB>D 3usan 4oble hi&ing, movies

FF )rwin 3tar hoc&ey, s&iing

EGG> Alice $uc&D /ran& $orders photography, travel, art

HFHF anna +iedrich coo&ing, movies

Contact hobbies


34/64


Attri! tes

34


st dentid

gender age address %onta%tn m

B D 1 F @lang I > HEBG H, I G> H

EB>D 1 F 2emerloh I DDFG > H, IDHBI>G> ,IDHBI>G>

FF 1 D Jerantut I DD > HH, IDHBI > HEGG> / F ulu@lang I D HHFDII, I EEHHBG >

D / F $esut I DDEEB I ,

HFHF / F 1achang I HHEH , IDEG> DD>

Contact hobbies


35/64


Attri! tes

35


Can simpli%es multivalued attributes by:a. Create several attri! tes

b. Create ne entity of the original multivaluedattributes components

gen#e

STUDENT

$%en%'D

han#$e%NO

age

a## e$$

CONTACTha$

homephoneNO

$%en%'D8

STUDENTage

gen#e

a## e$$

han#$e%NO

homephoneNO$%en%'D


36/64


Attri! tes

36

Attri! tes types#iv. Derived Attri! tes

An attributes that represents a value that is derivedfrom the value of related attribute or set of

attributes, not necessarily in the same entity type. 4eed not be physically stored within database )xample: age


37/64

37


Attri! tes


38/64


Relationship

38

Relationship Associations /connection between entities 6ogical interaction among the entities in a relational database 7perates in both directions 4aming Relationships:

Relationship name is a verb phrase Avoid vague names

+e%ning Relationships: +e%nition explains what action is being ta&en and why it is important 8ive examples to clarify the action

7ptional participation should be explained )xplain reasons for any explicit maximum cardinality )xplain any restrictions on participation in the relationship )xplain extent of the history that is &ept in the relationship )xplain whether an entity instance involved in a relationship instance

can transfer participation to another relationship instance


39/64


Relationship

39

Relationship is des%ri!ed !y#i. Degree o& the relationshipii. "onne%tivity o& the relationshipiii. "ardinality o& the relationshipiv. arti%ipation


40/64


Relationship

40

Relationship is des%ri!ed !y#i. Degree o& the relationship

ndicates number of associated entities within therelationship

2here are three types:a. nary Relationship

Association is maintained within single entity

!.$inary Relationship. 2wo entities are associated * : , :1, 1:4

%. Ternary Relationship. 2hree entities are associated


41/64


Relationship

41

h l h ( )


42/64


Relationship

42

!nary %elationship Type R, we i enti!y the "e#ation $ "ep"esents theentity type in%o#%es in this "e#ationship. &nc#' e in this "e#ation one (o"eti(e as !o"ei)n *ey the p"i(a"y o! itse#!. The two *eys +p"i(a"y *ey an!o"ei)n *ey a"e the sa(e b't they "ep"esents two entities o! i!!e"ent"o#es "e#ate to this "e#ationship.

E3D ame * per5E3D

&hsan

>>> )izan

4awal

HHH /airuz >>>- /

- /

A

"h 4# E i R l i hi (E R)


43/64


Relationship

43

/1/ =inary %elationship Type R, i enti!y "e#ation $ an 0

that co""espon to the entity types pa"ticipatin) in R. The"e#ationship itse#! can be (appe 'sin) one o! the !o##owin)app"oaches !sing foreign #ey This is the (ost pop'#a" app"oach.

Choose one o! the pa"ticipatin) "e#ation, say $ +'s'a##yone with tota# pa"ticipation inc#' e in $ as the !o"ei)n*ey the p"i(a"y *ey o! 0. &! R has att"ib'tes then p't the(in the "e#ation $ as we##.

Merged relation &! both pa"ticipations o! two "e#ateentity type a"e tota# then we can (e")e two entity typesan the "e#ationship into a "e#ation.

Defining relationship relation e!ine a "e#ation R to"ep"esent the "e#ationship. $## att"ib'tes o! "e#ationship isinc#' e in R. &n a ition, p't the p"i(a"y *eys o! two"e#ations $ , 0 into R. The p"i(a"y *eys o! R is

co(bination o! p"i(a"y *eys o! $ an 0.

A

$

A$R



44/64


Relationship

44

/1N =inary %elationship Type /o" each 1 0ina"y Re#ationship TypeR, i enti!y two "e#ations $ an 0 co""espon to two entity typespa"ticipatin) in R. $ "ep"esents the entity type at 1 si e an 0"ep"esents the entity type at si e. Include the primary #ey of $ asthe foreign #ey in = . This !o"ei)n *ey "ep"esent the "e#ationship type R.

A$



45/64


Relationship

45

M1N =inary %elationship Type, /o" each 0ina"y Re#ationshipType R, i enti!y two "e#ation $, 0 "ep"esent two entity type pa"ticipatin)in R. C"eate a new "e#ation to "ep"esent R. &nc#' e in as !o"ei)n*eys the p"i(a"y *eys o! $ an 0 an a## the si(p#e att"ib'tes o! R. Thecombination of primary #eys of $ and = .ill ma#e the primary #eyof S .

A$

3



46/64


Relationship

46

Ternary %elationship Type /o" eachn a"y + 2 Re#ationships c"eate anew "e#ation to "ep"esent the

"e#ationship. The p"i(a"y *ey o! thenew "e#ation is the co(bination o! thep"i(a"y *eys o! the pa"ticipatin)entities that ho# the +(any si e. &n(ost cases o! an n a"y "e#ationship a##the pa"ticipatin) entities ho# a (any

si e

"h t 4# E tit R l ti hi (E R)


47/64


Relationship

47

Relationship is des%ri!ed !y#ii. "onne%tivity o& the relationship

6ogical interaction among entities in a relationaldatabase

2here are three types:a.1#1

!.1#M

%.M#

STUDENT &n#e P"O!"AM

UiTMB"ANC9 ha:e "ECTO"

STUDENT egi$%e COU"SE

1 1

1 M

N1 M



48/64


Relationship

48

Relationship is des%ri!ed !y#iii. "ardinality o& the relationship

)xpress the speci%c *minimum and maximumnumber of entity occurrences associated with one

occurrence of the related entity /unction of organizational policy business rules



49/64


Relationship

49

Relationship is des%ri!ed !y#iii. "ardinality o& the relationship

)xample :7ne student can register to D courses7ne course maximum can have E student

;1 )< ;( 3*

entity relationship e r modeling mine ee (1)

Documents