Transcript Practical Database design and tuning

CSBP430 – Database Systems
Chapter 16:
Practical Database Design and
Tuning
Elarbi Badidi
College of Information Technology
United Arab Emirates University
[email protected]
1
The Information System
Database
Carefully designed and constructed repository of facts
Part of an information system
Information System
Provides data collection, storage, and retrieval
Facilitates data transformation
Components include:
– People
– Hardware
– Software
Database(s)
– Application programs
– Procedures
–
2
Information System Life Cycle
System Development Life Cycle (Macro Life Cycle)
Feasibility analysis: This phase is concerned with analyzing potential application
areas, identifying the economics of information gathering and dissemination,
performing preliminary cost-benefit studies, and determining the complexity of data
and processes.
Requirements collection and analysis: Detailed requirements are collected by
interacting with potential users and user groups to identify their particular problems
and needs.
Design: This phase has two aspects: the design of the database system, and the
design of the application systems (programs) that use and process the database.
Implementation: The information system is implemented, the database is loaded,
and the database transactions are implemented and tested.
Validation and acceptance testing: The system is tested against performance
criteria and behavior specifications.
Deployment, operation and maintenance: The operational phase starts when all
system functions are operational and have been validated. Monitoring of system
performance and system maintenance are important activities during the operational
phase.
3
Database Lifecycle (DBLC) Micro Life Cycle
4
Overview of the Methodology
2b
1
Information
Flow
Diagram
3b
Tasks
2a
ER
Diagram
1
2
3
4
3a
Abstract
Code
w/SQL
Relational
Schema
4b
4a
3GL Code
w/SQL
Relational
Platform
Analysis
Specification
Design
Implementation
5
Phase 1:
Database Initial Study
(Requirements Collection and
Analysis)
6
Phase 1: Database Initial Study
Purposes
Analyze company situation
• Operating environment
• Organizational structure
Define problems and constraints
Analyze and study existing documentation concerning the
application (forms, reports, policy manual).
Analyze the expectations of the users
Determine system requirements
Define objectives
Define scope and boundaries
Analyze flow of information
7
Phase 1: Database Initial Study
Analysis
Input:
descriptions of documents and tasks; scenarios; usage statistics; plans for
the future system; relevant laws, constraints, and policies
Output:
Information Flow Diagram (IFD) modeling, external documents, internal
documents, tasks, and system boundary.
Techniques:
interviews with people at all levels of the enterprise
analysis of documents, scenarios, tasks
reviews of short and long-term plans, manuals, files, and forms
abstraction
Tools:
Information Flow Diagrams
8
Information Flow Diagram
D2
D3
D4
D1
T1
Database
T2
T3
T4
D6
D5
task
name
document
name
9
Example: Information Flow Diagram
Check-In
Ticket
Boarding
Pass
Reservation/
Cancellation
Make
Reservation/
Cancellation
Passenger
list
Inquiry
Process
Check-in
Enter Flight
Schedule
DB
Assign
Planes
Assign
Planes
Flight
Schedule
Answer
Inquiry
Create
Flight Inst
Create
Flight Inst
Enter
Airports
Enter
Planes
Airports
Airplanes
10
Phase 2:
Database Design
11
Phase 2: Database Design
Database Design
Most Critical DBLC phase
Makes sure final product meets requirements
Focus on data requirements
Subphases
Create conceptual database design
Create logical database design (Data Model Mapping)
Create physical database design
DBMS software selection
12
Phase 2: Database Design
Database Design (Con’t.)
Purpose:
create detailed design of normalized relational database schema
create detailed design of tasks using abstract code with embedded SQL
identify need for views
Input:
Analysis Report, IFD.
Output:
relational schema w/primary and foreign keys, constraint definitions in SQL,
abstract code w/SQL, view definitions
Techniques:
database normalization; abstract coding
Tools:
mapping: ER-Model  Relational Model
abstract code; SQL; views
13
Database Design (Con’t.)
14
Phase 2: Database Design
I. Conceptual Database Design
Conceptual schema design:
Data modeling creates abstract data structure to
represent real-world items
High level of abstraction
Three steps
• Data analysis and requirements
• Entity relationship modeling and normalization
• Data model verification
transaction design:
Design the database transaction
• Retrieval transaction, update transaction, mixed transaction
15
Phase 2: Database Design
I. Conceptual Database Design
Data analysis and Requirements
Focus on:
Information
Information
Information
Information
needs
users
sources
constitution
Data sources
Developing and gathering end-user data views
Direct observation of current system
Interfacing with systems design group
Business rules
16
Phase 2: Database Design
I. Conceptual Database Design
Entity Relationship Modeling and Normalization
17
Phase 2: Database Design
I. Conceptual Database Design
Example ER-Diagram
Airports
Airport Code Name
-
-
City
State
-
-
Name
Airport
Code
City
Airport
State
18
Example ER-Diagram
Dtime
Airline
Airport
Code
Name
City
From
Airport
1
n
1
n
State
Miles
Flt Schedule
Price
To
1
Plane
Type
Plane#
Airplane
Total
#Seats
First
Middle
Last
1
Assigned
Atime
n
Instance
Of
n
Flt Instance
n
ReserVation
#Avail
Seats
Weekday
Date
Ticket#
Seat#
Check-In
Status
n
Customer
Customer
Name
Flt#
Customer
Address
Street
City
State
Phone#
Cust#
Zip
19
E-R Modeling is Iterative
20
Phase 2: Database Design
I. Conceptual Database Design
DB Design Strategy Notes
Top-down (Fig 16.2 page 540)
1) Identify data sets
2) Define data elements
Bottom-up (Fig 16.3 page 541)
1) Identify data elements
2) Group them into data sets
21
Phase 2: Database Design
I. Conceptual Database Design
Top-Down vs. Bottom-Up
22
Phase 2: Database Design
II. DBMS Software Selection
DBMS software selection is critical
Advantages and disadvantages need study
Factors affecting purchasing decision
Cost (software, maintenance, hardware, training, etc)
DBMS features and tools
Underlying model
Portability
DBMS hardware requirements
23
Phase 2: Database Design
III. Logical Design (Data Model Mapping)
Translates conceptual design into internal model
Maps objects in model to specific DBMS
constructs
Design components
Tables
Indexes
Views
Transactions
Access authorities
Others
24
Phase 2: Database Design
IV. Physical Design
Choose specific storage structure and access
paths
Very technical
More important in older hierarchical and network
models
Indexing, clustering of related records on disk, linking
related records via pointers.
Designers favor software that hides physical
details
25
Phase 3:
Implementation and Loading
26
Phase3: Implementation and Loading
Implementation and Loading
Creation of special storage-related constructs to
house end-user tables
Data loaded into tables
Other issues
Performance
Security
Backup and recovery
Integrity
Company standards
Concurrency controls
27
Phase3: Implementation and Loading
Implementation
Input:
relational schema w/primary and foreign keys, data
representation, constraints in SQL, abstract code w/SQL, task
decompositions, view definitions
Output:
conceptual schema,
w/embedded SQL
internal
schema,
host-language
code
Tools:
SQL, host-language
relational database management system, pre-compiler
host-language compiler
28
Example Conceptual Schema Implementation
CREATE DOMAIN AIRPORT-CODE CHAR(3)
CREATE DOMAIN FLIGHTNUMBER CHAR(5);
CREATE DOMAIN WEEKDAY CHAR(2)
CONSTRAINT DAYS CHECK ( VALUE IN
(‘MO’,’TU’,’WE’,’TH’,’FR’,’SA’,’SU’));
CREATE TABLE FLT-SCHEDULE
(FLT#
FLIGHTNUMBER NOT NULL,
AIRLINE
VARCHAR(25),
DTIME
TIME,
FROM-AIRPORTCODE
AIRPORT-CODE,
ATIME
TIME,
TO-AIRPORTCODE
AIRPORT-CODE,
MILES
SMALLINT,
PRICE
DECIMAL(7,2),
PRIMARY KEY (FLT#),
FOREIGN KEY (FROM-AIRPORTCODE) REFERENCES
AIRPORT(AIRPORTCODE),
FOREIGN KEY (TO_AIRPORTCODE) REFERENCES
AIRPORT(AIRPORTCODE));
29
Example Conceptual Schema Implementation
CREATE TABLE FLT-WEEKDAY
(FLT#
FLIGHTNUMBER NOT NULL,
WEEKDAY
WEEKDAY,
UNIQUE(FLT#, WEEKDAY),
FOREIGN KEY (FLT#) REFERENCES
FLT-SCHEDULE(FLT#));
CREATE TABLE FLT-INSTANCE
(FLT#
FLIGHTNUMBER NOT NULL,
DATE
DATE NOT NULL,
PLANE#
INTEGER,
PRIMARY KEY(FLT#, DATE),
FOREIGN KEY FLT# REFERENCES
FLT-SCHEDULE(FLT#),
FOREIGN KEY PLANE# REFERENCES
AIRPLANE(PLANE#));
30
Example Task Implementation
some C code
Direct-Flights T1.1
/* read(Inquiry, :Departure-Airport, :Arrival-Airport,:Date); */
/* convert :Date to :Weekday;
*/
more C code
EXEC SQL WHENEVER NOT FOUND GOTO endloop;
more C code
EXEC SQL DECLARE DIRECT-FLIGHTS CURSOR FOR
SELECT FROM-AIRPORTCODE, TO-AIRPORTCODE,
FLT-SCHEDULE.FLT#, DTIME, ATIME
FROM FLT-SCHEDULE, FLT-WEEKDAY
WHERE FLT-SCHEDULE.FLT#=FLT-WEEKDAY.FLT#
AND FROM-AIRPORTCODE=:Departure-Airport
AND TO-AIRPORTCODE=:Arrival-Airport AND WEEKDAY=:Weekday
ORDER BY DTIME;
more C code
EXEC SQL OPEN DIRECT-FLIGHTS;
while
EXEC SQL FETCH DIRECT-FLIGHTS
INTO :From, :To, :Flt#, :Dtime, :Atime;
write(Inquiry, :From, :To, :Flt#, :Date, :Dtime, :Atime)
endwhile;
more C code
endloop:
Exec SQL CLOSE DIRECT-FLIGHTS;
31
Phase 4:
Testing and Evaluation
32
Testing and Evaluation
Database is tested and fine-tuned for
performance, integrity, concurrent access, and
security constraints
Done in parallel with application programming
Actions taken if tests fail
Fine-tuning based on the DBMS reference manuals
Modification of physical design
Modification of logical design
Upgrade or change DBMS software or hardware
33
Phase 5:
Operation
34
Operation
Database considered operational
Starts process of system evaluation
Unforeseen problems may surface
Demand for change is constant
35
Phase 6:
Maintenance and Evaluation
36
Maintenance and Evaluation
Preventative maintenance
Corrective maintenance
Adaptive maintenance
Assignment of access permissions
Generation of database access
monitor performance
Periodic security audits based
generated statistics
Periodic system usage-summaries
statistics
on
to
system-
37