CSBP430%20

1
CSBP430 Database SystemsChapter 16 Practical
Database Design and Tuning

• Elarbi Badidi
• College of Information Technology
• United Arab Emirates University
• ebadidi_at_uaeu.ac.ae

2
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

3
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.

4
Database Lifecycle (DBLC) Micro Life Cycle
5
Overview of the Methodology

• Analysis
• Specification
• Design
• Implementation

1
2
3
4
6
Phase 1 Database Initial Study (Requirements
Collection and Analysis)
7
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

8
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

9
Information Flow Diagram
10
Example Information Flow Diagram
Check-In
Process Check-in
11
Phase 2 Database Design
12
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

13
Database Design (Cont.)

• 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

14
Database Design (Cont.)
15
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

16
Data analysis and Requirements
I. Conceptual Database Design

• Focus on
• Information needs
• Information users
• Information sources
• Information constitution
• Data sources
• Developing and gathering end-user data views
• Direct observation of current system
• Interfacing with systems design group
• Business rules

17
Entity Relationship Modeling and Normalization
I. Conceptual Database Design
18
Example ER-Diagram
I. Conceptual Database Design
Airports Airport Code Name City State
- - -
- - - -
-
19
Example ER-Diagram
Atime
Dtime
Airline
Airport Code
From
Name
Miles
1
n
City
Airport
Flt Schedule
n
To
Price
1
State
Flt
Weekday
1
Instance Of
Date
Plane
Plane Type
n
Assigned
1
n
Ticket
Flt Instance
Airplane
n
Seat
Total Seats
Avail Seats
Reser- Vation
Street
Check-In Status
City
First
n
Customer Address
Customer
Customer Name
State
Middle
Cust
Phone
Zip
Last
20
E-R Modeling is Iterative
21
DB Design Strategy Notes
I. Conceptual Database Design

• 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

22
Top-Down vs. Bottom-Up
I. Conceptual Database Design
23
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

24
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

25
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

26
Phase 3 Implementation and Loading
27
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

28
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, internal schema, host-language
  code w/embedded SQL
• Tools
• SQL, host-language
• relational database management system,
  pre-compiler
• host-language compiler

29
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)) CREAT
E TABLE FLT-SCHEDULE (FLT FLIGHTNUMBER NOT
NULL, AIRLINE VARCHAR(25), DTIME TIME, FROM-AI
RPORTCODE AIRPORT-CODE, ATIME TIME, TO-AIRPORTCO
DE AIRPORT-CODE, MILES SMALLINT, PRICE DECIMA
L(7,2), PRIMARY KEY (FLT), FOREIGN KEY
(FROM-AIRPORTCODE) REFERENCES
AIRPORT(AIRPORTCODE), FOREIGN KEY
(TO_AIRPORTCODE) REFERENCES
AIRPORT(AIRPORTCODE))
30
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, PRIMAR
Y KEY(FLT, DATE), FOREIGN KEY FLT REFERENCES
FLT-SCHEDULE(FLT), FOREIGN KEY PLANE
REFERENCES AIRPLANE(PLANE))
31
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.FLTFLT-WEEKDAY.FLT AND
FROM-AIRPORTCODEDeparture-Airport AND
TO-AIRPORTCODEArrival-Airport AND
WEEKDAYWeekday 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
32
Phase 4 Testing and Evaluation
33
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

34
Phase 5 Operation
35
Operation

• Database considered operational
• Starts process of system evaluation
• Unforeseen problems may surface
• Demand for change is constant

36
Phase 6 Maintenance and Evaluation
37
Maintenance and Evaluation

• Preventative maintenance
• Corrective maintenance
• Adaptive maintenance
• Assignment of access permissions
• Generation of database access statistics to
  monitor performance
• Periodic security audits based on
  system-generated statistics
• Periodic system usage-summaries