Computerized databases are critical assets in modern workplaces across Kenya, supporting efficient data management in sectors such as healthcare, education, finance, and public administration. Maintaining a computerized database requires a thorough understanding of user needs and careful design of the database structure to ensure data integrity, accessibility, and security. This chapter explores the process of collecting user requirements and designing database schemas, enabling professionals across industries to manage data effectively and support organizational goals.

3.1 Computerised database user requirements collection

Collecting user requirements is the foundation of a successful computerised database system. It involves understanding the specific data needs, workflows and objectives of the people who will use the database before any table is designed. For example, a software developer asked to build a stock database for a hardware shop must first learn what the shopkeeper records daily, which reports are needed and who is allowed to change prices. The process relies on communication and analysis so that the database matches real tasks.

3.1.1 Introduction to database

A database is an organised collection of data stored electronically so that it can be accessed, managed and updated easily. It holds data in structured formats such as tables, which makes retrieval and manipulation efficient. A developer building a membership system for a gym, for example, keeps member details, payments and attendance in one database instead of in separate paper registers.

3.1.1.1 Key concepts

Every database project uses a small set of core terms. A developer must use them accurately when talking to users and when documenting requirements.

• Data: Raw facts that have not yet been processed, such as a product name, a quantity of 40 or a price of Ksh 350.
• Information: Data that has been processed or organised so that it has meaning, such as a report showing the total value of stock in Ksh.
• Database: A structured, shared collection of related data stored electronically for one or more applications.
• Database management system (DBMS): The software that creates, stores, protects and retrieves data in a database. Examples are MySQL, PostgreSQL and Microsoft Access.
• Metadata: Data about data, such as the name, type and size of each field in a table.
• Data integrity: The accuracy and consistency of data. For example, an order cannot refer to a customer who does not exist.
• Data redundancy: Unnecessary repetition of the same data in several places, which wastes space and causes inconsistencies when one copy is changed and another is not.

Characteristics of a computerised database

• Structured data storage: Data is organised in tables with rows and columns, allowing systematic storage and retrieval.
• Data independence: The database is separate from the application programs that use it, so either can be changed with limited effect on the other.
• Support for queries: Users can request specific information through query languages such as SQL.
• Multi-user access: Several users can access and modify data at the same time while the DBMS maintains data integrity.
• Security controls: Access rights and authentication protect data from unauthorised use.

3.1.1.2 Database organisation

Data in a database is organised in levels, from the smallest item to the whole collection. Understanding these levels helps a developer decide, during requirements collection, which details users need to store.

• Field: The smallest named item of data, such as Product_Name or Unit_Price.
• Record: A complete set of related fields describing one entity, such as all the details of one product.
• Table: A collection of records about the same kind of entity, arranged in rows (records) and columns (fields).
• Database: A collection of related tables, for example Products, Customers, Suppliers and Orders for one shop.

The table below shows two records in a Products table.

Types of databases

• Relational databases: Store data in tables with defined relationships, suitable for most business applications.
• NoSQL databases: Handle unstructured or rapidly changing data, suitable for big data and real-time web applications.
• Distributed databases: Data is stored across different physical locations, improving availability and fault tolerance.
• Cloud databases: Hosted on cloud platforms, offering scalability and remote access.
• Centralised databases: Stored in a single location, which simplifies management but may limit access speed for remote users.

3.1.1.3 Database relationships

A relationship describes how the data in one table is connected to the data in another. Relationships are usually created by storing the key of one table as a foreign key in another. Identifying them early, from what users say about their work, prevents repeated data and design errors later.

A many-to-many relationship cannot be stored directly in a relational database. It is resolved by adding a linking table, such as Order_Items, which holds the keys of both tables. Drawing these relationships as diagrams is covered under designing the database schema.

3.1.1.4 Database operations

Database operations are the basic actions users perform on stored data. During requirements collection, the developer asks which operations each user group needs, since this determines forms, queries and permissions.

• Insert: Adding new records, such as entering a new product delivered to the shop.
• Retrieve: Searching for and displaying stored data, such as listing all products with fewer than 10 items in stock.
• Update: Changing existing data, such as correcting a unit price from Ksh 450 to Ksh 480.
• Delete: Removing records that are no longer needed, such as a discontinued product.
• Sort and filter: Arranging records in order, for example by price, or showing only those that meet a condition.
• Backup and recovery: Making copies of the database and restoring them after data loss.
• Security administration: Creating user accounts and granting or withdrawing permissions.

Importance of databases in the workplace

• Efficient data management: Quick access to large volumes of data, such as thousands of stock items in a retail business.
• Improved decision making: Accurate data supports decisions, such as which products to reorder before they run out.
• Enhanced data sharing: Different departments, such as sales and procurement, work from the same up-to-date data.
• Data security: Sensitive information such as customer contacts is protected by passwords and permissions.
• Automation of routine tasks: Calculations and reports are produced automatically, reducing manual errors and workload.

3.1.2 Collection of User requirements

Collection of user requirements means identifying what users expect from the database and translating these needs into technical specifications. It is vital because errors found after the database is built are costly to correct. For example, if a developer discovers only after delivery that a courier company needs to track parcels by several delivery attempts, the tables may have to be redesigned.

Techniques for gathering user requirements

• Interviews: One-on-one discussions with users to explore their tasks, challenges and expectations.
• Questionnaires: Structured forms given to many users to collect standardised answers.
• Observation: Watching users perform their tasks to identify needs they may not mention and the actual workflow.
• Workshops: Group sessions that encourage brainstorming and agreement among stakeholders.
• Document analysis: Reviewing existing forms, registers, reports and manuals to understand current data use and gaps.

Procedure for collecting user requirements

1. Identify the stakeholders, such as the owner, clerks, supervisors and customers who will use or depend on the database.
2. Define the purpose and scope of the database so that everyone agrees what the system will and will not cover.
3. Choose suitable techniques, for example interviews with managers and questionnaires for many clerks.
4. Collect the information by holding the sessions, observing work and gathering sample forms and reports.
5. Analyse the findings to list the data to be stored, the operations needed and the relationships between data.
6. Document the requirements in a clear written specification, using the users' own terms where possible.
7. Resolve conflicts and gaps by discussing them with stakeholders and agreeing priorities.
8. Present the specification to users for review and sign-off before database design begins.

Key elements to capture during requirements collection

• Data types and volume: What data will be stored, including formats, size and how often it changes.
• User roles and access levels: The different user categories and the permissions each needs.
• Reporting needs: The reports and summaries required for decision making.
• Performance expectations: Response time, number of simultaneous users and required availability.
• Integration requirements: Links with other systems such as accounting software or external databases.

Challenges in collecting requirements and mitigation strategies

• Ambiguous user needs: Users may not express clearly what they want. Use prototypes or sample screens to clarify requirements.
• Conflicting requirements: Different departments may want opposing things. Hold prioritisation workshops to reach agreement.
• Changing requirements: Business needs evolve. Use iterative feedback and a flexible database design.
• Limited user availability: Users may be busy. Schedule sessions in advance and use several collection methods.
• Technical knowledge gap: Users may not understand what a database can do. Provide simple explanations or short training.

Chapter Summary

This chapter provides a comprehensive overview of maintaining a computerized database, beginning with the collection of user requirements essential for effective database design. It introduces fundamental database concepts, including organisation, relationships, and operations, which form the foundation for understanding how data is structured and manipulated. The chapter then explores the design of database schemas through models such as Entity-Relationship Diagrams and relational models, enabling clear representation of data and its connections. Creation of database objects follows, detailing the roles of tables, records, fields, keys, forms, queries, and reports in building a functional database system. Data manipulation techniques are thoroughly covered, including how to insert, retrieve, delete, and update records to maintain data accuracy and relevance. Finally, the chapter addresses the printing of database objects, highlighting the methods for producing physical copies of tables, forms, queries, and reports for analysis and record-keeping. This structured approach equips learners with the knowledge to effectively design, create, and manage computerized databases.