Database 8

saikrupa123
Chapter9_DatabaseDesign.ppt
Home>Information Systems homework help>Database 8

Database Systems Design, Implementation, and Management

Coronel | Morris

11e

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

Chapter 9

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

Learning Objectives

  • In this chapter, you will learn:
  • That successful database design must reflect the information system of which the database is a part
  • That successful information systems are developed within a framework known as the Systems Development Life Cycle (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.

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

©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

*