Transcript dbms_design - Google Project Hosting

Database Design
Intro to Database Design
Database Lifecycle
Designer’s View
Based on the notes by Kiduk Yang
1
Outline


Database Design: Intro
Database Lifecycle





Planning & Analysis
Database Design
Implementation
Maintenance
Designer’s View
2
Turning Data

Simple dumping of data on the storage medium provides little value.
CUSTOMER
id
name
address
country
pay due
100
523
800
12 High Rd., Leeds
52 Ln. Muncie, IN
Box. 9, Miami, FL
UK
USA
USA
33.75
0
12.50
J. Rodney
E. Hoover
M. Old
PRODUCT
product_id
title
cost
sale price
123-19-20
169-15-34
354-90-33
Joy of Living
Learning Judo
Your Dream Home
12.50
20.00
18.25
19.25
25.00
24.25
SALE
id
100
523
100
800
zone
product_id
quantity
total price
UK
USMW
UK
USSE
123-19-20
354-90-33
169-15-34
123-19-20
2
1
1
1
38.50
24.25
25.00
19.25
3
...into information
The goal is not just storage of data, but ultimately the
extraction of information to support decision making by key
people and groups in the organization.
Data  Information (e.g. reports, tabulations, graphs)  Decisions

*
Sales of Titles in the UK Zone
- Summarized fact or information 50
Total Sales ($)

* In the UK ZONE, Joy of Living had a sale of $38.50,
and Learning Judo had a sale of $25.
** UK ZONE had the total sale of $63.50, USMW had
$24.25, and USSE had $19.25.
40
30
20
10
0
Joy of Living
Learning Judo
Titles
SALE by Zone**
Title
Joy of Living
Learning Judo
Your Dream Home
UK
38.50
25.00
63.50
USMW
24.25
24.25
USSE
19.25
19.25
total
57.75
25.00
24.25
107.00
4
Database Design: Intro

Database



Carefully designed and constructed repository of facts (or hearsay)
Part of an information system
Information System



Provides data collection, storage, and retrieval
Facilitates data transformation
Components include:
•
•
•
People
Hardware
Software
•
•

e.g. DBMS, Applications
Procedures
Successful database design must reflect the information system of
which the database is a part.
5
System Construction

System Analysis


establish the need and extent of an information
system
System Development

design & implement the information system
6
Database Construction


Planning & Analysis
Database Development


Design
 create complete, normalized, and integrated database
models
 establish data management processes
Implementation
 create storage structure
 load data into database
 provide for data management activities
 data entry/update, report generation, search
7
System vs. Database Lifecycle
System
Database
Database Systems: Design, Implementation, & Management: Rob & Coronel
8
Database Lifecycle: Phase 1

Planning & Analysis

Discover
•
•
company objectives
operations & structure




Define
•

what they are
how they function
how they interact
problems & constraints
Define Database System
specifications
•
•
•
database objectives
database scope
database boundaries
Database Systems: Design, Implementation, & Management: Rob & Coronel
9
Database Lifecycle: Planning &
Analysis
1.
Analyze company situation

•
•

•
•
what is organization’s general operating environment & its
mission?
what are operational components, how do they function &
interact?
design must satisfy the operational demands created by the
organization’s mission
what is organization’s structure?
who controls what and who reports to whom?
design involves defining information flows, queries, reports,
etc.
10
Database Lifecycle: Planning &
Analysis
Define problems & constraints
2.


•
what are the problems to be solved?
what are the constraints that limit the database design?
e.g. time, budget, personnel, etc.
Define objectives
3.
database should be designed to help solve the major problems.

•
examine the problem space for possible database solutions.
Define scope and boundaries
4.
consider factors that force the design into a specific mold
scope defines the extent of design.


•
data structures, type & number of entities, size of database
boundaries

•
imposed by constraint, existing hardware/software
11
Database Lifecycle: Phase 2

Database Design

Create a database model




to support company
operations and objectives
to meet system requirements
to meet user requirements
Focus on data requirements




Two Views of Data
data structure
data access
data to information
transformation
Sub-phases



Create conceptual design
Create logical design
Create physical design
Database Systems: Design, Implementation, & Management: Rob & Coronel
12
DB Design: Conceptual Design



Create a conceptual model

i.e. abstract data structure that represent real-world items.

need to understand how business works and what role data
plays

software and hardware independent
Minimal Data Rule:

“All that is needed is there, and all that is there is needed.”

make sure that all data needed are in the model, and that all
data in the model are needed.
Conceptual Design Steps

Data analysis and requirements

E-R modeling and normalization

Data model verification
13
Conceptual Design: Data Analysis &
Requirements

Discover the data that can be transformed into desired
information

information need



what kind of information is needed?
what output (queries & reports) must be generated by the
system?
user characteristics


who will use the information?
how will information be used?
14
Conceptual Design: Data Analysis &
Requirements

Discover the data that can be transformed into desired
information
...

information source






where is the information to be found?
how is the information to be extracted?
information creation
what data elements are needed to produce the information?
what are the data attributes and relationships?
what data transformations are to be used to produce the
information?
15
Conceptual Design: Data Analysis &
Requirements

Develop a thorough understanding of the
company’s data


flow, uses, characteristics
Data sources




automated entry
manual entry
interviews, direct observation
business rules
 narrative description of policy & procedures
16
Conceptual Design: E-R Modeling

E-R Modeling steps





Identify, analyze, and refine the business rule.
Identify entities, attributes, and relationships
Construct E-R diagram
 create the initial E-R diagram
 verify the model
 modify the E-R diagram
Normalize
 process for evaluating & designing good table structures
 reduce data redundancies
 help eliminate data anomalies
Repeat the last 2 steps as necessary
17
Conceptual Design:

E-R model is verified against proposed system processes.




E-R Model Verification
confirm that intended processes can be supported by the database
model
careful reevaluation of the entities and detailed examination of attributes
May reveal additional entity and attribute details.
Verification process is iterative.
Database Systems: Design, Implementation, & Management: Rob & Coronel
18
DB Design: Logical & Physical Design

Logical Design

specify system processes and I/O.
•

translate conceptual design into internal model that maps objects
in model to specific DBMS constructs
•

detailed & functional descriptions of system components
from software independent to software dependent
Physical Design

select the data storage and data access characteristics of the
database
•

can affect the database performance
•

more important in older hierarchical and network models
e.g. storage media, buffer size, etc.
designers favor software that hides physical details
19
DB Design: DBMS Selection

What are the advantages & disadvantages?





Cost
• purchase, maintenance, operational, training, etc.
Features & Tools
• ease-of-use, performance, DB administration, etc.
• application development tools
DB model
• hierarchical, RDB, Object-oriented, etc.
Portability
• platform, O/S, SQL
Hardware requirement
• processor, RAM
20
Database Lifecycle: Phase 3

Implementation

Create the database
•
•

tables, forms, queries, reports
programming: SQL, VBA
Fine-tune
•
repeated testing, debugging and evaluation
21
Database Lifecycle: Phase 4

Maintenance

Preventative maintenance
•
•

Corrective maintenance
•
•

to prevent and prepare for problem situations
e.g. backup
to address database system error
e.g. recovery
Adaptive maintenance
•
•
to adapt to the external changes
e.g. database update, enhancement
22
DB Lifecycle: Designer’s
Perspective
Planning
Maintenanc
e
Analysis
Design is a never-ending process...
Implementation
conceptual
design
23
DB Design: Step 1. Planning
Business Plan
or Org. Goals

Information
Needs
Database Plan
Database
Development
Projects
Strategic Planning
Objectives:

Understand the business model



Organizational goal
Product and information flow
Assess the information need
Put together an initial database plan


Activities:

Take stock of what is in place
Interacts with users at all levels



end-user, managers, support staff
24
Product & Info Flow in
Organization

DB designer must establish the product & information flow in the
organization.
Business Office:
Book Purchasing
& Inventory
Management
Store
Warehouse
Customers
Publishers
Barney & Nimble Inc.
25
Product & Info Flow in
Organization

Product & Information flow varies from organization to organization.



Warehouse and inventory can use information about orders going out to publishers.
Online department needs to have information on inventory.
Online department can also use information about pending orders.
Book Purchasing
& Collection
Management
Warehouse
Maintenance
& Inventory
Online Presence
&
Sales
Publishers
& Distribution
Centers
Fulfillment
Customers
Amazing.com (distributed approach)
26
Centralized Information Flow
Periodic
Management
Report
Amazing
Sales & Acquisition
Group
invoice &
other data
queries
electronic
payments
Publishers
shipping
info
record
screens
inventory
update
Consumers
Warehouse
27
DB Design: Step 2. Analysis

Requirement Analysis

Examination of existing database environment
•
•
•

From Information Need to Database Requirement
•
•
•

What hardware and software are available?
What files/records are in use?
What is under development?
What type of information is desired?
What can database system do to satisfy the information need?
Why design it? What would it improve?
Feasibility Analysis
•
•
•
Technological
Operational
Economical
28
DB Design: Feasibility Analysis

Technological Feasibility



What hardware, software, and additional resources would be
needed?
What is available in-house? What has to be purchased?
How will the new system be integrated?
29
DB Design: Feasibility Analysis

Operational Feasibility




Who will design the system?
Who will maintain the system?
Who will do training or help-desk support?
Can the available personnel provide the time? New personnel
necessary?
30
DB Design: Feasibility Analysis

Economic Feasibility

Expected cost of the overall project?
•
•

Other costs
•
•

Milestones, staff-time, software, and hardware
Hidden cost (unforeseen)
What is the competition/comparable unit doing?
Would data sharing among departments lead to additional
expenses?
Benefits
•
How soon expected?
31
DB Design: Step 3. Design

Conceptual Design


Map organizational & user needs to a conceptual model
Data Modeling
•

Event Table
•

list of events that will occur within the database system
Use Cases
•

Relational tables, attributes, & constraints
description of how users will interact with the system
User interfaces
Conceptual
Data Model
E-R modeling
Relational
Data Model
Relational
DBMS
Schema & Normalization
32
Data Modeling

What is Data Modeling?



Why Model?



A model is a representation of reality that retains only carefully
selected essential details.
Logical organization of data for optimum information extraction
and data manipulation
To understand and identify essential data elements
To produce a representation that can be transformed into a
schema
How?

Data modeling involves
•
identifying entities, attributes, and relationships
33