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.
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
Map the Conceptual Model:
- Map strong entities, supertype/subtype relationships, weak entities, and binary/higher-degree relationships.
Validate Using Normalization:
- Ensure tables are properly normalized and data anomalies are prevented.
Validate Integrity Constraints:
- Confirm that all constraints are supported by the logical data model.
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.