International Islamic University Malaysia

Department of Information Systems

Kulliyyah of Information & Communication Technology

Lecture 15
Database Planning
Planning, Design
Design, and
Administration

Dr. Mira Kartiwi

Objectives
 M i components
Main t off an information
i f ti system.
t

 Main
M i stages
t off database
d t b system
t development
d l t
lifecycle.

 Main phases of database design: conceptual,

logical,
g , and physical
p y design.
g

 Benefits of CASE tools.

2
Objectives

 H
How to evaluate
l and
d select
l a DBMS.
DBMS

 Distinction
i i i between data administration
i i i and
database administration.

 Purpose and tasks associated with data

administration
d i i t ti and d database
d t b administration.
d i i t ti

2
Software Depression

 Last few decades have seen proliferation of

software applications, many requiring constant
maintenance
i involving:
i l i
 correcting faults,
 implementing new user requirements,
requirements
 modifying software to run on new or upgraded platforms.
 Effort spent
p on maintenance began
g to absorb
resources at an alarming rate.
Software Depression

 As a result, many major software projects were

 late,
 over budget,
b d
 unreliable,
 difficult to maintain,
maintain
 performed poorly.

 In late 1960s, led to ‘software crisis’, now refer

to as the ‘software depression’.
Software Depression

 Major reasons for failure of software projects

includes:
- lack of a complete requirements specification;
- lack of appropriate development methodology;
- poor decomposition of design into manageable
components.
 Structured approach to development was
proposed called Information Systems Lifecycle
(ISLC).
Information System

Resources that enable collection, management,

control, and dissemination of information
throughout an organization.

 Database is fundamental component of IS,

IS and
its development/usage should be viewed from
perspective of the wider requirements of the
organization.
Database System Development Lifecycle

 Database planning

 System definition

 Requirements collection and analysis

 Database design

 DBMS selection (optional)

Database System Development Lifecycle

 Application design
 Prototyping
yp g (optional)
( p )
 Implementation
 Data conversion and loading
 Testing
g
 Operational maintenance
Stages of the Database System
Development Lifecycle
Database Planning

 Management activities that allow stages of

database system development lifecycle to be
realized as efficiently and effectively as
possible.

 Must be integrated with overall IS strategy of

the organization.
Database Planning – Mission Statement

 Mission statement for the database project

defines major aims of database application.

 Those driving database project normally define

the mission statement.
 Mission statement helps clarify purpose of the
database project and provides clearer path
towards the efficient and effective creation of
required
q database system.
y
Database Planning – Mission Objectives

 Once mission statement is defined, mission

objectives are defined.

 Each objective should identify a particular task

that the database must support.
pp
 May be accompanied by some additional
information that specifies the work to be done,
done
the resources with which to do it, and the
money y to pay
p y for it all.
Database Planning

 Database planning should also include

development of standards that govern:
 how data will be collected,
 how the format should be specified,
 what necessary documentation will be needed,
 how design and implementation should proceed.
System Definition

 Describes scope and boundaries of database

system
y and the major
j user views.

 User view defines what is required of a

d t b
database system
t from
f perspective
ti of:f
 a particular job role (such as Manager or
Supervisor) or
 enterprise application area (such as marketing,
personnel, or stock control).
System Definition

 Database application may have one or more

user views.
 Identifying user views helps ensure that no
major users of the database are forgotten
when developing requirements for new system.
 User views also help
p in development
p of
complex database system allowing
requirements to be broken down into
manageable
bl pieces.
i
Representation of a Database System with
Multiple User Views
Requirements Collection and Analysis

 Process of collecting and analyzing information

about the part of organization to be supported
by the database system, and using this
information to identify users’ requirements of
new system.
t
Requirements Collection and Analysis

 Information is gathered for each major user view

including:
 a description of data used or generated;
 details of how data is to be used/generated;
 any additional requirements for new database system.

 Information is analyzed to identify requirements

to be included in new database system. Described
in the requirements specification.
Requirements Collection and Analysis

 Another important activity is deciding

how to manage the requirements for
a database system with multiple user
views.
 Three main approaches:
 centralized approach;
 view integration approach;
 combination
bi ti off both
b th approaches.
h
Requirements Collection and Analysis

 Centralized approach
 Requirements for each user view are
merged into a single set of requirements.
 A data model is created representing all
user views during the database design
stage.
stage
Centralized Approach to Managing Multiple
User Views
Requirements Collection and Analysis

 View integration approach

 Requirements for each user view remain as
separate lists.
 Data models representing each user view are
created
t d andd then
th merged d later
l t during
d i the
th database
d t b
design stage.
Requirements Collection and Analysis

 Data model representing single user view (or a

subset of all user views)) is called a local data
model.

 Each model includes diagrams and

documentation describing g requirements
q for one
or more but not all user views of database.
Requirements Collection and Analysis

 Local data models are then merged at a later

stage
g duringg database design
g to produce
p a gglobal
data model, which represents all user views for
the database.
View Integration Approach to Managing
Multiple User Views
Database Design

 Process of creating a design for a database that

will support the enterprise’s mission statement
and mission objectives for the required
database system.
Database Design

 Main approaches include:

 Top-down
 Bottom-up
 Inside-out
 Mixed
Database Design

 Main purposes of data modeling include:

 to assist in understanding the meaning (semantics)
of the data;
 to facilitate communication about the information
requirements.
i t

 Building
g data model requires
q answering g
questions about entities, relationships, and
attributes.
Database Design

 A data model ensures we understand:

- each user’s perspective of the data;
- nature of the data itself, independent of its physical
representations;
- use of data across user views.
Criteria to Produce an Optimal
p Data Model
Database Design

 Three phases of database design:

 Conceptual
C t l database
d t b design
d i
 Logical database design
 Ph i l database
Physical d t b design.
d i
Conceptual Database Design

 Process of constructing a model of the data

used in an enterprise,
p independent
p of all
physical considerations.

 Data
D t model
d l iis b
built
ilt using
i the
th information
i f ti ini
users’ requirements specification.

 Conceptual data model is source of

information for logical
g design
g phase.
p
Logical Database Design

 Process of constructing a model of the data

used in an enterprise based on a specific data
model (e.g. relational), but independent of a
particular DBMS and other physical
considerations.
id ti

 Conceptual data model is refined and mapped

on to a logical data model.
Physical Database Design

 Process of producing a description of the

database implementation
p on secondary y storage.
g

 Describes base relations, file organizations, and

i d
indexes usedd tto achieve
hi efficient
ffi i t access to
t data.
d t
Also describes any associated integrity
constraints and security measures.
measures

 Tailored to a specific
p DBMS system.
y
Three-Level ANSI-SPARC Architecture and
Phases of Database Design
DBMS Selection

 Selection of an appropriate DBMS to support

the database system.
 Undertaken at any time prior to logical design
provided sufficient information is available
regarding system requirements.
 Main steps to selecting a DBMS:
 define Terms of Reference of study;
 shortlist two or three products;
 evaluate products;
 recommend selection and produce report.
DBMS Evaluation Features
DBMS Evaluation Features
Example - Evaluation of DBMS Product
Application Design

 Design of user interface and application

programs that use and process the database.

 Database design and application design are

parallel activities.
activities

 Includes two important activities:

 transaction design;
 user interface design.
Application Design - Transactions

 An action, or series of actions, carried out by a

single user or application program, which
accesses or changes content of the database.

 Should define and document the high-level

high level
characteristics of the transactions required.
Application Design - Transactions

 I
Important characteristics
h i i off transactions:
i
 data to be used by the transaction;
 functional characteristics of the transaction;
 output of the transaction;
 importance to the users;
 expected rate of usage.

 Three main
Th i types
t off transactions:
t ti retrieval,
t i l
update, and mixed.
Prototyping

 Building working model of a database system.

 Purpose
 to identify features of a system that work well, or are
inadequate;
q
 to suggest improvements or even new features;
 to clarify the users’ requirements;
 to evaluate feasibility of a particular system design.
Implementation

 Physical
Ph i l realization
li i off the
h ddatabase
b and
d application
li i
designs.
 Use DDL to create database schemas and empty
database files.
 Use DDL to create any specified user views.
 Use 3GL or 4GL to create the application programs.This
will include the database transactions implemented
using the DML,
DML possibly embedded in a host
programming language.
Data Conversion and Loading

 Transferring
T f i any existing
i i data
d into
i new database
d b
and converting any existing applications to run on
new database.
database

 Only required when new database system is

replacing an old system.
 DBMS normally has utility that loads existing files into
new database.
database
 May be possible to convert and use application
programs from old system for use by new system.
Testing

 Process of running the database system with intent

of finding errors.

 Use carefully planned test strategies and realistic

data.
 Testing cannot show absence of faults; it can show
only that software faults are present.
 Demonstrates that database and application
programs appear to be working according to
requirements.
i t
Testing

 Should also test usability of system.

 Evaluation conducted against a usability
specification.
ifi i
 Examples
p of criteria include:
 Learnability;
 Performance;
 Robustness;
 Recoverability;
 Ad
Adaptability.
bili
Operational Maintenance

 Process of monitoring and maintaining

database system following installation.
 Monitoring performance of system.
 if p
performance falls, may
y require
q tuning
g or
reorganization of the database.
 Maintaining and upgrading database
application
li ti ((when
h required).
i d)
 Incorporating new requirements into database
application.
application
CASE Tools

 Support provided by CASE tools include:

- data dictionaryy to store information about
database system’s data;
- design tools to support data analysis;
- tools to permit development of corporate data
model, and conceptual and logical data models;
- tools
l to enable
bl prototypingi off applications.
li i
CASE Tools

 Provide following benefits:

 Standards;
 Integration;
 Support for standard methods;
 Consistency;
 Automation .
CASE Tools and Database System
Development Lifecycle
Data Administration and Database
Administration

 The Data Administrator (DA) and Database

Administrator (DBA) are responsible for
managing and controlling the corporate data
and corporate database, respectively.

 DA is more concerned with early stages of

database system development lifecycle and
DBA is more concerned with later stages.
Data Administration

 Management of data resource including:

 database planning,
p g,
 development and maintenance of standards,
policies and procedures, and conceptual and logical
database design.
Data Administration

 Management of data resource including:

 database planning,
p g,
 development and maintenance of standards,
policies and procedures, and conceptual and logical
database design.
Database Administration

 Management of physical realization of a

database system
y including:
g
 physical database design and implementation,
 setting security and integrity controls,
 monitoring system performance, and reorganizing
the database.