DATABASE SYSTEMS LIFE CYCLE

LEARNING OUTCOME
¡ Discussed the phases of database design
SYSTEMS DEVELOPMENT LIFE CYCLE
Phase Action(s)

Initial assessment
Planning
Feasibility study

User requirements
Analysis Existing system evaluation
Logical system design

Detailed Detailed system specification

systems design

Coding, testing, and debugging

Implementation
Installation, fine-tuning

Evaluation
Maintenance Maintenance
Enhancement

Source: Database Systems: Design, Implementation, & Management, 13th Edition,

Coronel C & Morris S, 2019)
DATABASE LIFECYCLE
Phase Action(s) Section

Analyze the company situation

Database initial Define problems and constraints
study Define objectives
Define scope and boundaries

Create the conceptual design

Database design DBMS software selection
Create the logical design
Create the physical design

Install the DBMS

Implementation
and loading Create the database(s)
Load or convert the data

Test the database

Testing and
evaluation Fine-tune the database
Evaluate the database and its application programs

Operation Produce the required information flow

Maintenance and Introduce changes

evolution Make enhancements

Source: Database Systems: Design, Implementation, & Management, 13th Edition,

Coronel C & Morris S, 2019)
PHASE 1: INITIAL STUDY ACTIVITIES
Analysis of the
company situation

Company objectives Company operations Company structure

Definition of
problems and constraints

Database system
specifications

Objectives Scope Boundaries

Source: Database Systems: Design, Implementation, & Management, 13th Edition,

Coronel C & Morris S, 2019)
PHASE 2: DATABASE DESIGN
¡ Most Critical of DBLC phase
¡ Makes sure final product meets requirements
¡ Focus on data requirements
¡ Sub-phases
¡ Create conceptual design

¡ DBMS software selection

¡ Create logical design

¡ Create physical design

TWO VIEWS OF DATA

Company

Engineering Purchasing Manufacturing

Manager’s view
Shared information What are the problems?
What are the solutions?
What information is needed to
implement the solutions?
What data is required to
generate the desired information?

Designer’s view
How must the data be structured?
How will the data be accessed?
C o m pa ny How is the data transformed
D a t ab a s e
into information?

Source: Database Systems: Design, Implementation, & Management, 13th Edition,

Coronel C & Morris S, 2019)
DATABASE DESIGN PROCESS

* See Chapter 12, Distributed Database Management Systems

Source: Database Systems: Design, Implementation, & Management, 13th Edition, Coronel C & Morris S, 2019)
DATA ANALYSIS AND REQUIREMENTS
¡ Focus on:
¡ Information needs

¡ Information users

¡ Information sources

¡ Information constitution
DATA ANALYSIS AND REQUIREMENTS
¡ Data sources
¡ Developing and gathering end-user data views

¡ Direct observation of current system

¡ Interfacing with systems design group

DATA ANALYSIS AND REQUIREMENTS
¡ Must take into account business rules
¡ Document that provides precise, detailed, up-to-date, and
thoroughly reviewed description of activities that define
organization’s operating environment -
ENTITY RELATIONSHIP (ER) MODELING
AND NORMALIZATION

¡ Designer must communicate and enforce

appropriate standards to be used in documentation
of design
¡ Use of diagrams and symbols
¡ Documentation writing style
¡ Layout
¡ Other conventions to be followed during documentation
ENTITY RELATIONSHIP (ER) MODELING
AND NORMALIZATION

DEVELOPING THE CONCEPTUAL MODEL USING ER DIAGRAMS

STEP ACTIVITY

Source: Database Systems: Design, Implementation, & Management, 13th Edition, Coronel C & Morris S, 2019)
CONCEPT DESIGN: TOOLS AND SOURCES
Information sources Design tools Conceptual model

Business rules and

ER diagram
data constraints

ERD
Data flow diagrams
Normalization
(DFD)*

Process functional
descriptions (FD)* Data dictionary
(user views) Definition
and
validation

* Output generated by the systems analysis and design activities

Source: Database Systems: Design, Implementation, & Management, 13th Edition, Coronel C & Morris S, 2019)
DATA MODEL VERIFICATION
¡ Model must be verified against proposed system
processes to corroborate that intended processes can be
supported by database model
¡ Revision of original design starts with careful reevaluation
of entities, followed by detailed examination of attributes
that describe these entities
¡ Define design’s major components as modules:
¡ An information system component that handles specific
function
DATA MODEL VERIFICATION
THE ER MODEL VERIFICATION PROCESS
STEP ACTIVITY

Source: Database Systems: Design, Implementation, & Management, 13th Edition, Coronel C & Morris S, 2019)
DATA MODEL VERIFICATION
Identify central entity,
modules, and components

Define processes and

transaction steps

Verify ER model

Yes
Does ER Make changes
require changes? to ER model

No

ER model verified

Source: Database Systems: Design, Implementation, & Management, 13th Edition, Coronel C & Morris S, 2019)
DATA MODEL VERIFICATION
¡ Verification process
¡ Select central (most important) entity
¡ Defined in terms of its participation in most of model’s
relationships
¡ Identify module or subsystem to which central entity
belongs and define boundaries and scope
¡ Place central entity within module’s framework -
DISTRIBUTED DATABASE DESIGN
¡ Portions of database may reside in different physical
locations
¡ Designer must also develop data distribution and
allocation strategies -
II. DBMS SOFTWARE SELECTION
¡ DBMS software selection is critical
¡ Advantages and disadvantages need study
¡ Factors affecting purchasing decision
¡ Cost
¡ DBMS features and tools
¡ Underlying model
¡ Portability
¡ DBMS hardware requirements -
III. LOGICAL DESIGN
¡ Used to translate conceptual design into internal
model for selected database management system
¡ Logical design is software-dependent

¡ Requires that all objects in model be mapped to

specific constructs used by selected database
software
DESIGN COMPONENT
¡ Tables
¡ Indexes
¡ Views
¡ Transactions
¡ Access authorities
¡ Others
 LOGICAL DESIGN STEPS
LOGICAL DESIGN STEPS
STEP ACTIVITY

Source: Database Systems: Design, Implementation, & Management, 13th Edition, Coronel C & Morris S, 2019)
IV. PHYSICAL DESIGN
¡ Process of selecting data storage and data access
characteristics of database
¡ Storage characteristics are function of device types
supported by hardware, type of data access methods
supported by system, and DBMS
¡ Particularly important in older hierarchical and network
models
¡ Becomes more complex when data are distributed at
different locations
 PHYSICAL DESIGN STEPS
PHYSICAL DESIGN STEPS
STEP ACTIVITY

Source: Database Systems: Design, Implementation, & Management, 13th Edition, Coronel C & Morris S, 2019)
PHASE 3: 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
PHASE 4: 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 reference manuals
¡ Modification of physical design
¡ Modification of logical design
¡ Upgrade or change DBMS software or hardware
PHASE 5: OPERATION
¡ Database considered operational
¡ Starts process of system evaluation
¡ Unforeseen problems may surface
¡ Demand for change is constant
PHASE 6: MAINTENANCE AND
EVALUATION

¡ Preventive maintenance (backup)

¡ Corrective maintenance (recovery)
¡ Adaptive maintenance
¡ Assignment of access permissions and their
maintenance for new and old users
¡ Generation of database access statistics
¡ Periodic security audits
¡ Periodic system-usage summaries
PARALLEL ACTIVITIES IN THE DBLC AND THE SDLC

DBLC SDLC

System Database initial Analysis

design study

Conceptual Screens
Database design Logical Detailed design Reports
Physical Procedures

Implementation
Coding Prototyping
and loading
System Creation
implementation Loading
Fine-tuning
Testing and Testing and Debugging
evaluation evaluation

Operation

Database maintenance Application program

and evolution maintenance

Source: Database Systems: Design, Implementation, & Management, 13th Edition, Coronel C & Morris S, 2019)
DATABASE DESIGN STRATEGIES
¡ Two classical approaches to database design:
¡ Top-down design
¡ Identifies data sets
¡ Defines data elements for each of those sets

¡ Bottom-up design
¡ Identifies data elements (items)
¡ Groups them together in data sets
DATABASE DESIGN STRATEGIES

¡ Top-down vs Bottom-up Design Sequencing

Source: Database Systems: Design, Implementation, & Management, 13th Edition, Coronel C & Morris S, 2019)
CENTRALIZED VS. DECENTRALIZED DESIGN

¡ Database design may be based on two very different

design philosophies:
¡ Centralized design
¡ Productive when data component is composed of relatively
small number of objects and procedures
¡ Decentralized design
¡ Used when data component of system has considerable
number of entities and complex relations on which very
complex operations are performed
CENTRALIZED VS. DECENTRALIZED DESIGN

¡ Centralized Design
Conceptual model

Conceptual model verification

User views System processes Data constraints

Data dictionary

Source: Database Systems: Design, Implementation, & Management, 13th Edition, Coronel C & Morris S, 2019)
 CENTRALIZED VS. DECENTRALIZED DESIGN
¡ Decentralized Design

Submodule criteria
Data component

Conceptual Engineering Purchasing Manufacturing

models

Views Views Views

Verification Processes Processes Processes
Constraints Constraints Constraints

Aggregation

Conceptual model

Data dictionary

Source: Database Systems: Design, Implementation, & Management, 13th Edition,

Coronel C & Morris S, 2019)