Bagian 2 Pemodelan Konseptual Part 2

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.

Relationship name:  writes Author

Book

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

Constraints on Relationship Types Constraints limit the set of possible combination of entities that can participate in the relationship type  Two main kinds of constraints: 

Cardinality constraints  Participation constraints 

Cardinality Constrain Relationships

can be classified as

either ` one – to – one ` one – to – many  ` many – to –many Cardinality

Cardinality Constrain

: minimum and maximum number of instances of Entity B that can (or must be) associated with each instance of entity A.

Cardinality Constraints on Relationship Sets Consider

binary relationship set R between entity sets A and B One to one: an entity in A is associated with at most one entity in B, and an entity in B is associated with atmost one entity in A. ◦ an employee has only one spouse in a marriedto relationship. Many

to One: An entity in A is associated with at most one entity in B, an entity in B is associated with many entities in A.   ◦ an employee works in a single department but

Cardinality Constraints on Relationship Sets (cont.) Many

to Many: An entity in A is associated with many entities in B, and an entity in B is associated with many entities in A.

◦ A customer may have many bank accounts. Accounts may be joint between multiple customers.

Notation Chen

Model

◦ 1 to represent one. ◦ M to represent many Crow’s

1 M

Foot

One many One or many

Mandatory one , means (1,1) Partitipation constraint

Multiplicity of Relationships

Many­to­many

Many­to­one

One­to­one

multiplicity of relationship in ER diagram represented by an  arrow pointing to “one”

Many to Many Relationship customer

N

N

account

custacct

opening date

Customer John Megan  Megan

Account 1001 1001 2001

legal 

Multiple

account

Start Date Jan 20th 1999 March 16th  1999 Feb 18th 1994

Customer Account Start Date John 1001 Jan 20th 1999 Megan  1001 March 16th  1999

legal 

customers can share an

Many to One Relationship customer

N

1

custacct

account

opening date

Customer John Megan  Megan

Account 1001 1001 2001

Illegal Multiple

Start Date Jan 20th 1999 March 16th  1999 Feb 18th 1994

Customer John Megan 

Account 1001 1001

Start Date Jan 20th 1999 March 16th  1999

legal

customers can share an account but one customer can have only one account.

Relationship Attribute in a Many to One Relationship customer

N

1 account

custacct

opening date

In

a Many-One relationship, relationship attributes can be repositioned to the entity set on the many side. N

customer

opening date

1

custacct

account

One to One Relationship customer

1

1

custacct

opening date

1

customer can have 1 account. One account can be owned by 1 customer relationship attributes can be shifted to either of

account

Illegal

Customer Account Start Date John 1001 Jan 20th 1999 Megan  1001 March 16th  1999

Illegal

Customer Account Start Date Megan  1001 March 16th  1999 Megan 2001 Feb 18th 1994

Legal

Customer Megan  John

Account 1001 2001

Start Date March 16th  1999 Feb 18th 1994

car

car

car

car

driver

1:1 A car can only have  one driver; a driver can  have only one car.

driver

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

driver

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

driver

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

Relationship  name

A typical company Company

manages

Managing  Director

employs Sales Staff

take orders

Customer

Participation Constraints total

participation is also called existential dependency  mandatory If an entity does not have a total participation in a relationship, it is said to have a partial participation  optional

 

Mandatory vs. Optional Cardinalities 

Specifies whether an instance must exist or can be absent in the relationship Mandatory

Optional

handles

Lecturer (1,1)

Lecturer

1 (0,N)

Class (0,N)

handles

M

Class

(1,1)

A Lecturer may handle zero or many classes. 

A class is handled by one and only one Lecturer.

Degree of Relationships  Degree:

number of entity types that participate in a relationship  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

Three Types of Relationships

Unary

The 1:1 Recursive Relationship “EMPLOYEE is Married to EMPLOYEE”

Implementation of the M:N Recursive “PART Contains PART” Relationship

Implementation of the 1:M “EMPLOYEE Manages EMPLOYEE” Recursive Relationship

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 

The Implemented 1:M relationship between PAINTER and PAINTING 

Binary Relationships 1:1 relationship



◦

Should be rare in any relational database design

◦

A single entity instance in one entity class is related to a single entity instance in another entity class

◦

Could indicate that two entities actually belong in the same table

The 1:1 Relationship Between PROFESSOR and DEPARTMENT

The Implemented  1:1 Relationship  Between  PROFESSOR  and  DEPARTMENT 

Binary Relationships M:N relationships



Must be avoided because they lead to data

◦

redundancies. Can be implemented by breaking it up to

◦

produce 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

The M:N Relationship Between STUDENT and CLASS  Bowser Smithson

Accounting 1 (ACCT­211) Intro to Microcomputing (CIS­220) Intro to Statistics (QM­261)

This CANNOT be implemented as shown next…..

The tables have many redundancies!!

+ CLASS_CODE CLASS_CODE

+ STU_NUM

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

The database designer has 2 main options to  define a composite table’s primary key:  either  use the combination of those foreign  keys or create a new primary key.

Foreign keys reference the primary keys in the  other tables of which it has a relationship with

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

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

Next …. Enhanced/Extended

ER model…

PR : A hospital in-patient system 

A hospital is organised into a number of wards.  Each ward has a ward number and a name recorded, along with a number of beds in that ward.  Each ward is staffed by nurses.  Nurses have their staff number and name recorded, and are assigned to a single ward.  Each patient in the hospital has a patient identification number, and their name, address and date of birth are recorded.  Each patient is under the care of a single consultant and is assigned to a single ward.  Each consultant is responsible for a

Exercise on In-patient system 1. State 4 entities for the system giving a suitable identifier for each entity. 2. Draw an entity-relationship diagram to show the relationships between the entities.

Semoga Bermanfaat, Sekian dan Terimakasih