Database design life cycle ppt

Database 9 Assignment

Learning Objectives

In this chapter, you will learn:
That within the information system, the most successful databases are subject to frequent evaluation and revision within a framework known as the Database Life Cycle (DBLC)
How to conduct evaluation and revision within the SDLC and DBLC frameworks
About database design strategies: top-down vs. bottom-up design and centralized vs. decentralized design
*

©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.

The Information System

Provides for data collection, storage, and retrieval
Composed of:
People, hardware, software
Database(s), application programs, procedures
Systems analysis: Process that establishes need for and extent of information system
Systems development: Process of creating information system
*

©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.

Performance Factors of an Information System

Database design and implementation
Application design and implementation
Administrative procedures
Database development: Process of database design and its implementation
*

©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.

Systems Development Life Cycle (SDLC)

Traces history of an information system
Provides a picture within which database design and application development are mapped out and evaluated
Iterative rather than sequential process
*

©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.

Figure 9.2 - The Systems Development Life Cycle (SDLC)

Cengage Learning © 2015

*

©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.

Computer-Aided Systems Engineering (CASE)

Tool that produces:
Time and cost effective systems
Structured, documented, and standardized applications
*

©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.

Figure 9.3 - The Database Life Cycle (DBLC)

Cengage Learning © 2015

*

©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.

Purpose of Database Initial Study

*

©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.

Figure 9.4 - A Summary of Activities in the Database Initial Study

*

©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.

Database Design

Supports company’s operations and objectives
Checks the ultimate final product from all perspectives
Pointers for examining completion procedures
Data component is an element of whole system
System analysts/programmers design procedures to convert data into information
Database design is an iterative process
*

©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.

Figure 9.5 - Two Views of Data: Business Manager and Database Designer

*

©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.

Database Design Process

Conceptual

Design

DBMS

Selection

Logical

Design

Physical

Design

Data analysis and requirements
Entity Relationship modeling and normalization
Data model verification
Distributed database design
Map conceptual model to logical model components
Validate logical model using normalization
Validate logical model integrity constraints
Validate logical model against user requirements
Define data storage organization
Define integrity and security measures
Determine performance measures
Select the DBMS

©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.

Implementation and Loading

Install the DBMS
Virtualization: Creates logical representations of computing resources independent of underlying physical computing resources
Create the databases
Requires the creation of special storage-related constructs to house the end-user tables
Load or convert the data
Requires aggregating data from multiple sources
*

©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.

Testing Factors

Physical security
Password security
Access rights
Audit trails
Data encryption
Diskless workstations
Optimization
*

©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.

Levels of Database Backups

Full backup/dump: All database objects are backed up in their entirety
Differential backup: Only modified/updated objects since last full backup are backed up
Transaction log backup: Only the transaction log operations that are not reflected in a previous backup are backed up
Backups are provided with high security
*

©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.

Sources of Database Failure

*

©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.

Periodic Maintenance Activities

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
*

©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.

Figure 9.8 - Parallel Activities in the DBLC and the SDLC

*

©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.

Conceptual Design

Designs a database independent of database software and physical details
Conceptual data model - Describes main data entities, attributes, relationships, and constrains
Designed as software and hardware independent
Minimum data rule: All that is needed is there, and all that is there is needed
*

©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.

Table 9.2 - Conceptual Design Steps

Cengage Learning © 2015

*

©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.

Description of Operations

*

©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.

Table 9.3 - Developing the Conceptual Model Using ER Diagrams

Cengage Learning © 2015

*

©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.

Figure 9.10 - ER Modeling is an Iterative Process Based on Many Activities
*

©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.

Figure 9.11 - Conceptual Design Tools and Information Sources

*

©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.

Data Model Verification

Verified against proposed system processes
Revision of original design
Careful reevaluation of entities
Detailed examination of attributes describing entities
Module: Information system component that handles specific business function
*

©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.

Table 9.5 - The ER Model Verification Process

Cengage Learning © 2015

*

©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.

Figure 9.12 - Iterative ER Model Verification Process

*

©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.

Cohesivity and Module Coupling

Cohesivity: Strength of the relationships among the module’s entities
Module coupling: Extent to which modules are independent to one another
Low coupling decreases unnecessary intermodule dependencies
*

©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.

Distributed Database Design

Portions of database may reside in different physical locations
Database fragment: Subset of a database stored at a given location
Ensures database integrity, security, and performance
*

©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.

Factors Affecting Software Purchasing Decision

Cost
DBMS features and tools
Underlying model
Portability
DBMS hardware requirements
*

©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.

Logical and Physical Design

Logical design: Designs an enterprise-wide database that is based on a specific data model but independent of physical-level details
Validates logical model:
Using normalization
Integrity constraints
Against user requirements
Physical design: Process of data storage organization and data access characteristics of the database
*

©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.

Table 9.6 - Logical Design Steps

Cengage Learning © 2015

*

©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.

Table 9.7 - Mapping the Conceptual Model to the Relational Model

Cengage Learning © 2015

*

©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.

Table 9.8 - Physical Design Steps

Cengage Learning © 2015

*

©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.

Clustered Tables

*

©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.

Database Role

*

©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.

Figure 9.14 - Top-down vs. Bottom-up Design Sequencing

*

©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.

Figure 9.15 - Centralized Design

*

©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.

Figure 9.16 - Decentralized Design

*

©2015 Cengage Learning. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or in part.

Figure 9.17 - Summary of Aggregation Problems