bagian 2 pemodelan konseptual part 2

8/14/2019 Bagian 2 Pemodelan Konseptual part 2

1/36

Relationships

Associations between instances of one or more entity types that is of interest

Given a name that describes its function.

relationship name is an active or a passive verb.

Author Book

Relationship name:writes

An author writes one or more booksA book can be written by one or more authors.


2/36

Constraints on Relationship

Types Constraints limit the set of possible

combination of entities that can

participate in the relationship typeTwo main kinds of constraints: Cardinality constraints

Participation constraints


3/36

Cardinality Constrain

Relationships can be classified aseither one to one

one to many many to many

Cardinality: minimum and maximumnumber of instances of Entity B thatcan (or must be) associated with eachinstance of entity A.

Cardinality Constrain


4/36

Cardinality Constraints onRelationship Sets

Consider binary relationship set Rbetween entity sets A and B

One to one:an entity in A is associated

with at most one entity in B, and an entityin B is associated with atmost one entityin A.

an employeehas only one spousein a married-

torelationship.

Many to One: An entity in A is associatedwith at most one entity in B, an entity in B

is associated with many entities in A. an em lo ee works in a sin le de artmentbut


5/36

Cardinality Constraints onRelationship Sets (cont.)Many to Many:An entity in A is

associated with many entities in B,and an entity in B is associated withmany entities in A.

A customermay have many bankaccounts. Accountsmay be joint betweenmultiple customers.


6/36

Notation

Chen Model

1 to represent one.

M to represent many

Crows Foot

many

One

One or many

1

M

Mandatory one , means (1,1)

Partitipation constraint


7/36

Multiplicity ofRelationships

Many-to-one One-to-oneMany-to-many

multiplicity of relationship in ER diagram represented by an

arrow pointing to one


8/36

Many to ManyRelationship

Multiple customers can share an

account

customer custacct account

opening date

Customer Account Start Date

ohn 1001 Jan 20th

1999

Megan 1001 March 16th

1999

Me an 2001 Feb 18th

1994


ohn 1001 Jan 20th

1999

Me an 1001 March 16th

1999

legallegal

N N


9/36

Many to One Relationship

Multiple customers can share anaccount but one customer can haveonly one account.


John 1001 Jan 20th

1999


1999

Me an 2001 Feb 18th

1994

Illegal


ohn 1001 Jan 20th

1999


1999

legal


opening date

N 1


10/36

Relationship Attribute in aMany to One Relationship

In a Many-One relationship,relationship attributes can berepositioned to the entity set on the

many side.


opening date


opening date

1

1


11/36

One to One Relationship

1 customer canhave 1 account.

One account can be

owned by 1customer

relationship

attributes can beshifted to either of

ustomer Account Start Date


1999

Megan 2001 Feb 18th

1994

IllegalCustomer Account Start DateJohn 1001 Jan 20

th1999


1999

Illegal



1999

John 2001 Feb 18th 1994

Legal


opening date

1 1


12/36

car driver

car driver

car driver

car driver1:1 A car can only haveone driver; a driver canhave only one car.

1:M A car can havemore than one driver; adriver can only one car.

1:M A car can have onlyone driver; a driver canhave more than one car.

M:N A car can have more thanone driver; a driver can havemore than one car.


13/36

A typical company

Managing

DirectorCompany

Sales Staff Customer

employs

manages

take orders

Relationship

name


14/36

Participation Constraints

total participationis also calledexistential dependencymandatory

If an entity does not have a totalparticipationin a relationship, it issaid to have a partial participation

optional


15/36

Mandatory vs. Optional Cardinalities

Specifies whether an instance must exist or can beabsent in the relationship

Lecturer Classhandles

A Lecturer may handle zero or many classes.

A class is handled by one and only one Lecturer.

OptionalMandatory

(0,N)(1,1)

Lecturer Class

(0,N) (1,1)

handles1

M


16/36

Degree of Relationships

Degree: number of entity types that participate in arelationship

Three cases

Unary: between two instances of one entity type

Binary: between the instances of two entity types

Ternary: among the instances of three entity types

Th T f


17/36

Three Types ofRelationships


18/36

Unary

Th 1 1 R i


19/36

The 1:1 RecursiveRelationship EMPLOYEE is

Married to EMPLOYEE


20/36

M:N Recursive PARTContains PART

Relationship


21/36

EMPLOYEE ManagesEMPLOYEE Recursive

Relationship


22/36

Binary Relationships

1:M relationship Relational modeling ideal

Should be the norm in any relational database design

The 1: M relationship between PAINTER and PAINTING


23/36

The Implemented 1:M relationship between PAINTER and PAINTING


24/36


1:1 relationship Should be rare in any relational database design

A single entity instance in one entity class is related toa single entity instance in another entity class

Could indicate that two entities actually belong in the

same table


25/36

The 1:1 Relationship Between PROFESSOR and DEPARTMENT


26/36

The Implemented1:1 RelationshipBetweenPROFESSORand

DEPARTMENT


27/36


M:N relationships

Must be avoided because they lead to data

redundancies.

Can be implemented by breaking it up toproduce a set of 1:M relationships

Can avoid problems inherent to M:N relationship

by creating a composite entity or bridge

entity This will be used to link the tables that were

originally related in a M:N relationship

The composite entity structure includes-as

foreign keys-at least the primary keys of


28/36

The M:N Relationship Between STUDENT and CLASS

This CANNOT be implemented as shown next..

Bowser

Smithson

Accounting 1 (ACCT-211)

Intro to Microcomputing (CIS-220)

Intro to Statistics (QM-261)


29/36

The tables have many redundancies!!

+ CLASS_CODE

CLASS_CODE

+ STU_NUM


30/36

Changing the M:N relationship to TWO 1:M relationships


31/36

Converting the M:N relationship into TWO 1:M relationships

Foreign keys reference the primary keys in the

other tables of which it has a relationship with

The database designer has 2 main options todefine a composite tables primary key:either

use the combination of those foreignkeys or create a new primary key.


32/36

How to Evaluate a Data Model?

A good data model has the following:

Accuracy and completeness

Non redundancy

Enforcement of business rules Data Reusability

Stability and Flexibility

Communication Effectiveness

Simplicity


33/36

Next .

Enhanced/Extended ER model

PR A h it l i ti t


34/36

PR : A hospital in-patientsystemA hospitalis organised into a number of

wards.Each wardhas a ward number and a

name recorded, along with a number ofbeds in that ward.

Each wardis staffed by nurses.Nurseshave their staff number and name

recorded, and are assigned to a singleward.

Each patientin the hospital has a patient

identification number, and their name,address and date of birth are recorded.

Each patientis under the care of a singleconsultantand is assigned to a single

ward.Each consultantis res onsible for a


35/36

Exercise on In-patientsystem

1. State 4 entities for the systemgiving a suitable identifier for eachentity.

2. Draw an entity-relationshipdiagram to show the relationshipsbetween the entities.


36/36

Semoga Bermanfaat,

Sekian dan Terimakasih

bagian 2 pemodelan konseptual part 2

Documents