Bagian 2 Pemodelan Konseptual

Pemodelan Konseptual Basisdata Kuliah 2 Basisdata Ilmu Komputer IPB Referensi: ◦ Rob and Coronel. Database Systems: Design, Implementation, and Management, Sixth Edition, ◦ Jeffrey A. Hoffer. Modern Systems Analysis and Design, thisd edition. ◦ dll

Topik Bahasan  Gambaran

Umum Peracangan Basisdata

Konseptual  Fase perancangan Basisdata  Konsep-konsep dalam pemodelan data  ER-Model

The Design Process Good decisions require good  information derived from raw  facts known as data.

Importance of good database  design Well designed database facilitates 

data management and becomes a  good information generator. It will grow well. It is easy to use.

NoteNo Packer

Comp

Comp    ItemNo Add

Qty

PartNo

Descrip

300

JW

Bloggs

York

1

200

1234

Nuts

300

JW

Bloggs

York

2

200

2234

Bolts

300

JW

Bloggs

York

3

200

3334

Nails

What is wrong with this  organisation’s database? Update anomaly Delete anomaly Insert anomaly

Info about Packer, Comp and  CompAdd has been repeated  in more than one row If Bloggs was the only company  to have purchased Nuts and  then decided they did not want  them so were deleted from  packing note, contents of  ParNo  and Descrip fields would be lost

PartNo and Descrip fields cannot be  established until a packing note has  been raised.

Architect Plan Builder House

Database Designer Database Design Database Builder Database System

Systems Development Life Cycle Project Identification  and Selection Project Initiation  and Planning

Database  Development Process  Enterprise modeling

Conceptual data modeling

Analysis Logical Design Physical Design Implementation Maintenance

Logical database design Physical database design  and definition Database implementation Database maintenance

Enterprise Modelling Purpose

of modelling: not only IS design Models not only “what” but also “why” Integrates conceptual and process models of the business with objectives, actors, business rules and information system requirements Makes information system solutions traceable to objectives Makes conceptual modelling a “participatory” activity

Iterative development of knowledge and models IS requirements

Actors

Conceptual Objectives Models

Information Processes Concepts Business Rules

Participation in modelling

Modelling during four+ decades Database models Pioneering work ­ concepts 60­ties

Information System models

Refinement, models and extensions 70­ties

Business rule modelling

The search for a common framework 80­ties

Temporal aspects

Modelling of ”why”,  Enterprise models

Participation and understanding 90­ties

­ Extended  scope ­Standardisation efforts 2005 Domain Specific  ”Ontological Models” and languages

User education and participation Formality

vs informality

A simplified database design process Database Requirements UoD

Requirements Collection and Analysis

Functional Requirements

A simplified database design process Database Requirements

Functional Requirements

Conceptual Design

Conceptual Schema

Functional Analysis

High­level Transaction Specs DBMS INDEPENDENT

A simplified database design process Conceptual Schema

High­level Transaction Specs

Physical Design

Application program Design

Internal Schema Application software Transaction Implementation

DBMS DEPENDENT

Conceptual Schema 





 

Outcome of the high-level conceptual design Concise description of data requirements of the users Includes description of entity types, relationships and constraints No implementation details Ease of understanding. Used to communicate with non-technical users

The Database Design  Process 1 Define the current process. 4, 5, & 6 are   conceptual  database design

2 Define the components of the  organisation. 3 Define rules (how organisation  is run). 4 Model the database. 5 Define the relationships. 6 Review. 7 Create the database.

What is Conceptual Database  Design? Representation

of organizational data Purpose is to show rules about the meaning and interrelationships among data Process of describing the data, relationships between the data, relationships between the data, and the constraints on the data. The focus is on the data, rather than on the processes. The output of the conceptual database design is a Conceptual Data Model usually ER Diagram ( + Data

What is Conceptual Database  Design Main

goal of conceptual data modeling is to create accurate E-R diagrams Consistency must be maintained between process flow, decision logic and data modeling descriptions 10. 18

A Common Mistake Modeling the business processes or functions  instead of the data.

What data we want to keep?? We are interested in modeling the data,  NOT the processes or functions that use  or generate those data.

Example:

M Member

N Searches

Books

Is this part of the data requirement? Are we interested to know the books searched by the members? If answer is NO, then DO NOT include that as a relationship. Use other appropriate diagramming techniques to capture the business  processes such as Data Flow Diagram.  Do not mix up the use of ER Modeling with DFD.

Gathering Information for Conceptual Data Modeling Two

perspectives

◦ Top-down ` Data model is derived from an intimate understanding of the business

◦ Bottom-up  ` Data model is derived by reviewing specifications and business documents

10.21

Conceptual Design Phase TOP­DOWN

Identify Entities

Identify Relationships

Identify Attributes

BOTTOM­UP

Identify Relationships

Identify Dependencies

DATA Collect Data

The Entity Relationship (ER) Model Modeling is a top-down approach to database design.  Entity Relationship (ER) Diagram ◦ A detailed, logical representation of the entities, associations and data elements for an organization or business  ER

 ERD

represents the conceptual database as viewed by end user

 ERDs

depict the ER model’s three main components: ◦ Entities ◦ Attributes

Entity­Relationship (ER) Modeling.  Modeling is a top-down approach to database design. Entity Relationship (ER) Diagram ER

◦ A detailed, logical representation of the entities, associations and data elements for an organization or business Notation

uses three main constructs

◦ Data entities ◦ Relationships ◦ Attributes

Chen Model &  Crow’s Foot  Model

Chen Notation

Association  between the  instances of one or  more entity types  EntityName

Person, place, object, event  or concept about which data  is to be maintained Represents a set or collection of  objects in the real world that  share the same properties

Verb Phrase

AttributeName

named property or  characteristic of an  entity 

Entities to the entity set and not to a single entity occurrence

Refers

Corresponds

to a table and not to a row in the relational environment

In

both the Chen and Crow’s Foot models, an entity is represented by a rectangle containing the entity’s name

Entity

name, a noun, is usually

Entity  vs. Entity Set (Entities) Entity Set (Entities) ­­­ Student  John Smith

entity

(999­21­3415, jsmith@, John Smith, 18,  3.5)

Students in ITCS3160 999­21­3415, jsmith@, John Smith, 18,  3.5 999­31­2356, jzhang@, Jie Zhang,   20,  3.0 999­32­1234, ajain@,     Anil Jain,    21,  3.8

Is it an entity? Rules: 2. An entity must be important to the  organisation. 3. An entity must have at least one attribute. 4. An entity must occur more than once   (there must be more than one customer) 5. Each entity occurrence (record) must be  uniquely identifiable (customer id)

Entities Examples ◦ ◦ ◦ ◦ ◦

of entities:

Person: EMPLOYEE, STUDENT, PATIENT Place: STORE, WAREHOUSE Object: MACHINE, PRODUCT, CAR Event: SALE,REGISTRATION, RENEWAL Concept: ACCOUNT, COURSE

Guidelines

for naming and defining entity

types: ◦ ◦ ◦ ◦

An entity type name is a singular noun An entity type should be descriptive and specific An entity name should be concise Event entity types should be named for the result of the event, not the activity or process of the event.

Attributes Characteristics

of entities

In

Chen model, attributes are represented by ovals and are connected to the entity rectangle with a line

Each

oval contains the name of the attribute it represents

In

the Crow’s Foot model, the attributes are simply written in the

Attributes single-valued

vrs multi-valued:

◦ color of car could be multi-valued ◦ salary of employee is single-valued atomic

vrs composite:

◦ age of a person is atomic ◦ address of a person could be composite stored

vrs derived:

◦ derived attributes are those that can be derived from other attributes or entities, e.g., age can be derived from date of   birth.

Attributes  Example

of entity types and associated attributes:

STUDENT: Student_ID, Student_Name, Home_Address, Phone_Number, Major  Guidelines

for naming attributes:

◦ An attribute name is a noun. ◦ An attribute name should be unique ◦ To make an attribute name unique and clear, each attribute name should follow a standard format ◦ Similar attributes of different entity types should use similar but distinguishing names.

The Attributes of the STUDENT Entity

Domains Attributes

have a domain:

◦ The attribute’s set of possible values Attributes

may share a domain

Identifier Attributes Candidate

key

◦ Attribute (or combination of attributes) that uniquely identifies each instance of an entity type ◦ Some entities may have more than one candidate key ` Ex: A candidate key for EMPLOYEE is Employee_ID, a second is the combination of Employee_Name and Address.  ` If there is more than one candidate key, need to make a choice.

Identifier

◦ A candidate key that has been selected as the unique identifying characteristic for an entity type

Referential Attributes 

Make Reference to another instance in another table Referential attribute: Ties the lecturer entity to  another entity that is department.

Instance of Lecturer.

Name 

          IdNum                 DeptID

        Email

Ali

          105                  LG

        [email protected]

Mary

          106               

         [email protected]

John

          107                 ENG

         [email protected]

Lim

          108                  IT

         [email protected]

 IT

Example Name

Gender

StaffID

IC

Staff

Staff PK StaffID

Name Gender IC

Attributes Multivalued Derived

attributes

Attribute

A Multivalued Attribute in an Entity

Resolving Multivalued Attribute Problems Although

the conceptual model can handle multivalued attributes, you should not implement them in the relational DBMS ◦ Within original entity, create several new attributes, one for each of the original multivalued attribute’s components ` Can lead to major structural problems in the table

Splitting the Multivalued Attribute into New Attributes

A New Entity Set Composed of a Multivalued Attribute’s Components

Derived Attributes Attribute

whose value may be calculated (derived) from other attributes

Need

not be physically stored within the database

Can

be derived by using an algorithm

Depiction of a Derived Attribute

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 th Jan 20  1999 th March 16   1999 th Feb 18  1994

Customer Account Start Date th John 1001 Jan 20  1999 th Megan 1001 March 16   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 th Jan 20  1999 th March 16   1999 th Feb 18  1994

Customer John Megan

Account 1001 1001

Start Date th Jan 20  1999 th March 16   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 th John 1001 Jan 20  1999 th Megan 1001 March 16   1999

Illegal

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

Legal Customer Megan John

Account 1001 2001

Start Date th March 16   1999 th Feb 18  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