1.1 Establishing Database Requirements

Establishing database requirements is the initial and arguably most crucial phase in database system design. In Kenya’s dynamic software development landscape, gathering precise requirements from diverse sectors such as banking, healthcare, and government agencies ensures the database aligns with operational realities and compliance standards. This process involves comprehending the data to be managed, user interactions, and performance expectations, which collectively inform the database schema and architecture.

1.1.1 Introduction to DBMS

The Database Management System (DBMS) is the software that facilitates the creation, management, and use of databases. It acts as an intermediary between users and the physical data, providing tools to define, manipulate, and secure data. In Kenya, organizations ranging from microfinance institutions to county government offices rely on DBMS solutions to maintain data integrity and support decision-making.

Components of a DBMS

• Data Definition Language (DDL): Enables defining database schema, including tables, fields, and relationships, essential for structuring data according to application needs.
• Data Manipulation Language (DML): Provides commands to insert, update, delete, and retrieve data, allowing dynamic interaction with stored information.
• Data Control Language (DCL): Governs user permissions and access control, ensuring data security and compliance with policies such as Kenya’s Data Protection Act.
• Transaction Management: Ensures database consistency through mechanisms like commit and rollback, critical in multi-user environments such as banking systems.
• Query Processor: Interprets and executes queries efficiently, optimizing response times for user requests in applications like hospital patient records.

Types of DBMS Models

• Relational DBMS (RDBMS): Uses tables with rows and columns; widely adopted due to its flexibility and support for SQL. Examples include MySQL, Oracle, and Microsoft SQL Server, common in Kenyan financial institutions.
• Hierarchical DBMS: Organizes data in a tree-like structure, suitable for applications with a strict parent-child data relationship, such as organizational charts in county governments.
• Network DBMS: Allows more complex many-to-many relationships, used in telecommunications databases.
• Object-oriented DBMS: Integrates object-oriented programming concepts, beneficial for software applications that handle complex data types, such as multimedia.

Functions of a DBMS

• Data Storage Management: Efficiently stores data on physical media while abstracting complexities from users.
• Data Retrieval: Provides mechanisms to quickly locate and fetch requested data.
• Concurrency Control: Manages simultaneous data access by multiple users, preventing conflicts and ensuring consistency.
• Backup and Recovery: Safeguards data against loss or corruption through backup routines and recovery procedures.
• Data Integrity Enforcement: Maintains accuracy and consistency via constraints and validation rules embedded in the database.

1.1.2 Database Requirements Gathering Techniques

Gathering database requirements is a collaborative process that involves stakeholders to ensure the database system reflects real-world needs. In Kenya's software development projects, especially those serving sectors like education and healthcare, thorough requirements gathering prevents costly redesigns and enhances user satisfaction.

Stakeholder Interviews

Conducting structured interviews with end-users, managers, and IT staff uncovers detailed insights into data usage patterns, reporting needs, and pain points. For example, interviewing hospital administrators at a county referral hospital reveals specific patient data tracking requirements that inform the database schema.

Questionnaires and Surveys

Distributing questionnaires to a broad user base captures quantitative data on user expectations and frequency of database interactions. SACCOs use surveys to understand member data management needs, guiding the design of member databases.

Observation and Job Shadowing

Direct observation of daily workflows and data handling practices helps identify implicit requirements that users might not articulate. Shadowing clerks at a retail business exposes how sales data is recorded, influencing real-time inventory tracking features.

Document Analysis

Reviewing existing documentation such as business process manuals, legacy system reports, and regulatory guidelines provides context and constraints. For instance, analyzing Kenya Revenue Authority’s data policies ensures the database design complies with taxation data standards.

Prototyping

Developing a preliminary database model and obtaining feedback from users enables iterative refinement of requirements. At a university, prototyping student registration data structures helps align the system with academic administrative needs.

Chapter Summary

This chapter provides a comprehensive overview of designing a database system, beginning with the establishment of database requirements through understanding the fundamental concepts of database management systems. It explains key terms, outlines the core functions of database systems, explores various applications, and distinguishes between different types of database management systems while detailing the major components and architectures involved. The process of gathering accurate database requirements is emphasized using appropriate techniques. The chapter then examines various database models including relational, hierarchical, network, object-oriented, and dimensional models, highlighting their distinct structures and uses. In the design phase, it covers conceptual design with a focus on entity-relationship diagrams, followed by the application of the relational model. Logical design is presented as the step where the conceptual model is translated into a logical structure, and physical design is discussed as the final phase that optimizes performance and storage. Together, these topics form a detailed framework for creating effective and efficient computerized database systems.