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 60ties
Information System models
Refinement, models and extensions 70ties
Business rule modelling
The search for a common framework 80ties
Temporal aspects
Modelling of ”why”, Enterprise models
Participation and understanding 90ties
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
Highlevel Transaction Specs DBMS INDEPENDENT
A simplified database design process Conceptual Schema
Highlevel 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 TOPDOWN
Identify Entities
Identify Relationships
Identify Attributes
BOTTOMUP
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
EntityRelationship (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
(999213415, jsmith@, John Smith, 18, 3.5)
Students in ITCS3160 999213415, jsmith@, John Smith, 18, 3.5 999312356, jzhang@, Jie Zhang, 20, 3.0 999321234, 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