TOPIC 6

Chapter Overview

  • This chapter discusses the role and processes involved in database design, emphasizing its importance for the successful functioning of information systems.

Chapter Objectives

  • By the end of this chapter, you should be able to:
      - Describe the role of database design as the foundation of a successful information system.
      - Describe the five phases in the Systems Development Life Cycle (SDLC).
      - Design databases using the six phases in the Database Life Cycle (DBLC) framework.
      - Conduct evaluation and revision within the SDLC and DBLC frameworks.
      - Distinguish between top-down and bottom-up approaches in database design.
      - Distinguish between centralized and decentralized conceptual database design.

The Information System

  • A database is a component of a larger system called an Information System (IS), which supports the collection, storage, and retrieval of data.
      - A complete information system includes:
        - People
        - Hardware
        - Software
        - Databases
        - Application programs
        - Procedures
  • The process of creating an information system is known as systems development.

Factors Affecting Information System Performance

  • The performance of an information system hinges on:
      - Database design and implementation.
      - Application design and implementation.
      - Administrative procedures.
  • Database development refers to the process of database design and its implementation.

The Systems Development Life Cycle (SDLC)

  • The SDLC traces the history of an information system and provides a framework for database design and application development.
  • The traditional SDLC consists of five phases:
      - Planning: This phase yields a general overview of the company and its objectives.
      - Analysis: Problems defined during the planning phase are examined in greater detail. This process establishes the need for an information system and its extent.
      - Detailed Systems Design: The designer completes the design of the system’s processes.
      - Implementation: In this phase, hardware, DBMS software, and application programs are installed, with the database design implemented.
      - Maintenance: This phase is categorized into three types:
        - Corrective
        - Adaptive
        - Perfective

The Database Life Cycle (DBLC)

  • The DBLC traces the history of a database within an information system and consists of six phases:
      - Database Initial Study: Analyzes the company situation, defines problems and constraints, objectives, and scope and boundaries.
      - Database Design: Involves conceptualizing the structure of the database.
      - Implementation and Loading: Translating the database design into actual database structures.
      - Testing and Evaluation: Assessing various aspects of database security and function.
      - Operation: After evaluation, the database is considered operational.
      - Maintenance and Evolution: Engages in periodic maintenance activities.

Database Initial Study

  • Purpose:
      - Analyze the company situation.
      - Define problems and constraints.
      - Define objectives.
      - Define scope and boundaries.

Database Design Process

  • The database design process follows the DBLC and involves:
      - Translating the database design into actual structures.
      - Creating tables, attributes, domains, views, indexes, security constraints, and storage and performance guidelines.
  • Virtualization is a technique creating logical representations of computing resources independent of physical computing resources.

Testing and Evaluation

  • Testing should cover:
      - Physical security
      - Password security
      - Access rights
      - Audit trails
      - Application code and database security
      - Data encryption
      - Diskless workstations
  • Backup methods include:
      - Full Backup/Dump: Backing up all database objects entirely.
      - Differential Backup: Backing up only modified/updated objects since the last full backup.
      - Transaction Log Backup: Backing up only transaction log operations not previously backed up.

Operational Phase

  • Once the database has passed testing, it enters the operational phase, where:
      - Unforeseen issues may arise.
      - Compliance is a critical aspect.

Maintenance and Evolution

  • Periodic maintenance activities include:
      - Preventative maintenance (backups).
      - Corrective maintenance (recovery).
      - Adaptive maintenance.
      - Managing access permissions for users.
      - Generating usage statistics and conducting security audits.

Conceptual Design

  • Conceptual design is the first stage in the database design process, employing data-modeling techniques to create a model of the database that represents real-world objects effectively.
  • The goal is to design a database independent of software and physical details. The output is a conceptual data model comprising data entities, attributes, relationships, and constraints.
  • Minimal Data Rule: Ensure that all necessary information is included while avoiding extraneous data.

Data Analysis and Requirements

  • Focus on:
      - Information needs and sources.
      - Users and constitution of information.
  • Information can be gathered from:
      - User data views.
      - Observing current systems and outputs.
      - Interfacing with systems design groups.
  • Description of Operations Document: Provides details of organizational activities for system design.

Entity Relationship Modeling and Normalization

  • The ER modeling process involves:
      - Normalizing entities.
      - Defining all data elements in a data dictionary.
      - Applying standard naming conventions.
  • During modeling, designers must:
      - Define entities, attributes, primary and foreign keys.
      - Decide on treatment of composite and multivalued attributes.
      - Address placement of foreign keys in 1:1 relationships.
      - Aim to avoid unnecessary relationships and create corresponding ER diagrams.

Module Design

  • A module in an information system handles specific functions (like inventory or payroll) and can be implemented as an independent unit.
  • Within the module framework:
      - Ensure cohesiveness and analyze relationships with other modules to maintain low coupling.

Distributed Database Design

  • Involves distributing data and processes across various physical locations.
  • Database Fragment: A subset stored at a specific location; optimizing this allocation enhances integrity, security, and performance.

DBMS Software Selection

  • Crucial factors for selecting DBMS software:
      - Cost
      - Features and tools
      - Underlying model
      - Portability
      - Hardware requirements

Logical Design

  • The logical design phase aligns conceptual designs with the constructs of the selected DBMS, generally involving:
      - Mapping conceptual models to logical components.
      - Validating logical models to ensure normalization and integrity constraints.
      - Matching all requirements against end-user expectations.

Steps in Logical Design

  1. Map the Conceptual Model:
       - Map strong entities, supertype/subtype relationships, weak entities, and binary/higher-degree relationships.
  2. Validate Using Normalization:
       - Ensure tables are properly normalized and data anomalies are prevented.
  3. Validate Integrity Constraints:
       - Confirm that all constraints are supported by the logical data model.
  4. Validate Against User Requirements:
       - Ensure logical definitions meet all end-user data, transaction, and security needs.

Physical Design

  • The physical design stage determines data storage organization and access characteristics to maintain database integrity, security, and performance.
  • Consists of steps such as:
      1. Define Data Storage Organization:
         - Determine data volume and usage patterns.
      2. Define Integrity and Security Measures:
         - Set user roles and security controls.
      3. Determine Performance Measurements:
         - Refine and tune the DBMS and queries based on performance requirements.

Database Design Strategies: Top-Down vs Bottom-Up

  • Top-down design: Identifies data sets first, then details individual data elements.
  • Bottom-up design: Begins with data elements and then groups them into data sets.

Centralized vs Decentralized Design

  • Centralized design: Involves a small group making all design decisions, effective for small databases.
  • Decentralized design: Suitable for large and complex data systems where multiple operational sites exist, allowing for distributed decision-making.