Chapter 03 Data Modeling

Introduction to Databases
Bab 3:
Memodelkan Data
Desain Database
● Tiga bagian proses:
○ Conceptual design: Identifikasi kebutuhan
stakeholder dan persyaratan data,
○ Logical design:Mengidentifikasi jenis aplikasi
database yang diperlukan; pemodelan data (
mengidentifikasi entitas, atribut, hubungan)
○ Physical design: Implementasi; desain hardware
secara fisik dan platform; instalasi dan konfigurasi
server dan pembuatan database
Desain Database
Jenis Database
● Transactional
○ Mendukung transaksi bisnis
■ contoh, sales, Online Transaction
Processing (OLTP)
○ Lingkungan client/server sederhana
○ Concerns : Concurrency, throughput
Database Types
● Decision support system (DSS)
○ Mendukung Keputusan Bisnis
○ Types: Data warehouses, reporting databases, data
marts
○ Tujuan Utama:Pemanggilan dan Evaluasi Data
○ Bulk loading used to add data
○ Concerns: Access speeds, throughput
● Hybrid
○ Mixture of transactional and DSS types
Modeling Goals
● Design with understanding that:
○ All applications written for database are
dependent on database structure
○ If database is altered, applications may need
to be altered or rewritten
● Support business objectives
● Simple, easy-to-read, easy-tocomprehend structure
● Scalability
Business Rules
● Business rules:
○ Processes and flows used in organization’s daily
work, including:
■ Organizational policies
■ Calculations and formulas
■ Rules and regulations
● Database systems should support
business rules
○ Some rules may be better enforced by database
design
○ Others may be better handled through database
application
Entity-Relationship (E-R) Modeling
● Entities:
○ “Things” to track in database
■ Physical objects (people or products)
■ Conceptual entities (loan balances or
interest rates)
Entity-Relationship (E-R) Modeling
● Attributes:
○ Information tracked about entity
○ Each entity has attributes
○ For example:
■ Entity: Employees
■ Attributes: Hire Date, Pay Rate, Social Security ID,
Employee Number, Home Address
○ One attribute used as unique identifier or
primary key
Entity-Relationship (E-R) Modeling
● Relationships:
○ When one entity references information from
another entity
○ For example:
■ Time Card entity must reference an Employee
entity
■ Time Card entity is referencing entity
■ Employee entity is referenced entity
○ Primary keys ensure referential integrity
Entity-Relationship (E-R) Modeling
Basic Database Objects
● Tables: Store unique instances of entity
○ Columns (fields) represent attributes
○ Rows (records) represent individual occurrences of
that entity
● Views:
○ Customized representations of table information
Basic Database Objects
Basic Database Objects
● Index:
○ Organizes and sorts data
○ Provides pointer to specific physical location
of data on storage media
○ May be:
■ Primary or secondary
■ Clustered or nonclustered
○ Balanced-tree index (b-tree, or binary tree
index):
■ Most common type of index in modern databases
B-Tree Index
Relationship Types
● Three basic types:
○ Binary relationship:
■ Relationship between two types of entities
○ Unary relationship:
■ Relationship with another occurrence
within same entity
○ Ternary relationship:
■ Relationship directly involving three entity
types
Relationship Types
● Cardinality:
○ Sets maximum number of entities in
relationship
■ One-to-one
■ One-to-many
■ Many-to-many
● Modality:
○ Sets minimum number of entities in
relationship
Binary Relationships
● Simplest type of relationship
● Majority of relationships in most relational
databases are binary relationships
● Entity can have separate binary
relationships with any number of other
entities in database
Binary Relationships
Binary Relationships
Binary Relationships
Unary Relationships
● Occurrences of an entity type are
associated with other occurrences of
same entity type
○ Relate data in a table to itself
● For example:
○ Entity (table) Salesperson
○ One salesperson may act as a backup or
stand-in for another
Unary Relationships
Unary Relationships
Unary Relationships
Ternary Relationships
● Involve three different entities
● Can be considered sets of binary
relationships
Ternary Relationships
Many-to-Many Relationships
● Help minimize wasted space and optimize
performance
● Intersection data:
○ Data that is part of many-to-many relationship
and associated with specific, unique instance
of related entities
● Associative entities:
○ Entity designed to associate key values from
two entities in many-to-many relationship
Many-to-Many Relationships
Associative Entities
Creating Data Models
● To create data model:
○ Identify entities
○ Identify attributes for each entity
○ Identify associations between entities
● Modeling tools include:
○ Manual (pencil and paper)
○ Generic draw program (e.g. Visio)
○ Custom modeling program
Visio Database Diagrams
Example: General Hardware Company
● Wholesaler and distributor of various
manufacturers’ tools and other hardware
products
● Customers:
○ Hardware and home improvement stores,
which in turn sell products at retail to individual
consumers
● Acts as middleman:
○ Buys goods from manufacturers and sells to
retail stores
Example: General Hardware Company
Example: General Hardware Company
Contoh: Good Reading Bookstores
● Chain of bookstores
● Needs to track data for:
○ Books
○ Publishers
○ Authors
○ Customers
Contoh: Good Reading Bookstores
Ringkasan
● Proses Desain Database: Conceptual, logical
dan design
● Jenis-jenis database: Transactional, decision
support (DSS) dan hybrid
● Tujuan Modeling Data: membuat model
database yang strukturnya baik yang sederhana,
mudah untuk dibaca dan dipahami serta terukur
● Obyek Database: Tabel dan indeks
● Tabel: Mendeskripsikan entiti; yang terdiri dari
kolom (field) dan baris (record), dengan primary
key digunakan sebagai identitas unik dari suatu
record
Ringkasan
● Komponen yang menjadi kunci dari suatu
Database relasional adalah: Entiti dan
relationship
● Relationships mungkin berupa binary, unary,
atau ternary, dan memiliki kardinalitas dan
modalitas yang berbeda
● Relasionship Many-to-many mendukung
persimpangan data dan asosiasi entiti
● dalam membuat model data, tahapnya yaitu
1. mengidentifikasi entiti,
2. mengidentifikasi atributnya, selanjutnya
3. mengidentifikasi hubungan antar entiti
Kata Kunci
● Associative entity
● Balanced tree index
● Base object
● Binary relationship
● Binary tree index
● B-tree index
● Bulk loading
● Business rules
● Cardinality
● Clustered index
● Conceptual design
● Concurrency
● Database object
● Data diagram
● Data mart
● Data model
● Data warehouse
● Decision support
system (DSS)
Kata Kunci
● Entity-Relationship
diagram (ERD)
● Entity-Relationship
(E-R) modeling
● Field
● Hybrid database
● Identifier
● Index
● Intersection data
● Large object (LOB)
data
● Leaf node
● Logical design
● Many-to-many (M-M)
binary relationship
● Modality
● Node
● Nonclustered index
● Object-relation model
(ORM)
● One-to-many (1-M)
binary relationship
Kata Kunci
● One-to-one (1-1)
binary relationship
● Online transaction
processing (OLTP)
● Physical design
● Primary index
● Primary key Record
● Referenced entity
● Referencing entity
● Referential integrity
● Reporting database
● Scalable
● Secondary index
● Stakeholder
● Ternary relationship
● Transactional
database
● Throughput
● Unary relationship
Copyright Notice
Copyright 2008 John Wiley & Sons, Inc.
All rights reserved. Reproduction or translation of
this work beyond that permitted in section 117 of the
1976 United States Copyright Act without express
permission of the copyright owner is unlawful.
Requests for further information should be
addressed to the Permissions Department, John
Wiley & Sons, Inc. The purchaser may make backup copies for his/her own use only and not for
distribution or resale. The Publisher assumes no
responsibility for errors, omissions, or damages
caused by the use of these programs or from the
use of the information herein.