Chapter 1: Design computerized database

Designing a computerized database is a critical skill for an ICT Technician in Kenya. Effective database design enables organizations across sectors such as healthcare, education, finance, and public service to manage data efficiently, supporting decision-making and operational processes. This chapter introduces fundamentals of establishing computerized databases, focusing on understanding Database Management Systems (DBMS) and gathering accurate database requirements to ensure the system meets organizational needs.

1.1 Establishing computerized database requirements

A computerised database is only as useful as the match between what it stores and what its users need. Before choosing software or drawing a single table, an ICT technician must understand what a database management system (DBMS) is and does, and then find out in an organised way what the business needs the database to hold, produce and protect. For example, a hardware shop that moves its paper stock cards onto a computer must first know which items it sells, who records the sales, which reports the owner reads each month and who may change prices. Getting these answers right at the start avoids rebuilding the database later.

1.1.1 Introduction to DBMS

A DBMS is the software layer that sits between the people or applications that need data and the files where the data is physically stored. Without it, every application would open, search and update its own files, and the same fact would be stored again and again in different places. For example, a cyber café that keeps login times in one spreadsheet and payments in another will eventually see the two totals disagree. Placing both in one database managed by a DBMS gives a single, shared copy of each fact.

1.1.1.1 Definition of terms

The terms below are used throughout the unit, so each must be clear before design begins.

• Data: raw facts with no context on their own, such as "Ksh 250", "Terminal 3" or "45 minutes".
• Information: data that has been processed or placed in context so that it means something, such as "Terminal 3 earned Ksh 250 for 45 minutes of use today".
• Database: an organised collection of related data stored so that it can be shared, searched and updated. The café's customers, sessions and payments together form one database.
• Database management system (DBMS): an integrated software package designed to define, create, maintain and control access to databases. It acts as an intermediary between users and the physical database, so users never handle the stored files directly. MySQL, PostgreSQL, SQLite and Microsoft Access are examples.
• Database system: the database together with the DBMS and the applications that use it, for example the café's database, MySQL and the till screen that staff use.
• Table: a collection of data about one kind of thing, arranged in rows and columns. It is also called a relation.
• Record: one row of a table, holding all the facts about one occurrence, such as one session.
• Field: one column of a table, holding one kind of fact for every record, such as the session start time.
• Entity and attribute: an entity is a thing about which data is kept, such as a customer or a terminal. An attribute is a property of that thing, such as a customer's name.
• Primary key: a field, or a group of fields, whose value is different for every record and so identifies it. Types of key are studied under Keys in database design.
• Schema: the description of the structure of a database: its tables, fields, data types, relationships and constraints. The data held at one moment is called an instance of the schema.
• Metadata and data dictionary: metadata is data about data, such as a field's name, type and size. The DBMS keeps it in a data dictionary, also called the system catalogue.
• Query: a request made to the DBMS to retrieve or change data, usually written in Structured Query Language (SQL).
• Transaction: a unit of work that must be completed fully or not at all. Recording a sale and reducing the stock count are one transaction.
• Data redundancy: the unnecessary repetition of the same data in more than one place, which wastes space and allows copies to disagree.
• Data integrity: the accuracy and consistency of stored data, protected by rules such as validation checks and constraints.
• Data independence: the ability to change how data is stored, or how the database is organised, without having to rewrite the applications that use it.
• Database administrator (DBA): the person responsible for installing the DBMS, creating users, controlling access, taking backups and keeping the database healthy.

1.1.1.2 Functions of database systems

A DBMS does much more than store data. Its functions explain why organisations use one instead of ordinary files.

• Data definition: allows the structures, data types and constraints of the database to be specified, so that the schema reflects how the organisation really keeps its data.
• Data storage, retrieval and update: provides tools such as SQL to insert, update, delete and retrieve data efficiently, without the user knowing where on the disk the data sits.
• Data dictionary management: keeps the metadata up to date whenever a table or field is created or changed, so the structure is always documented.
• Security and access control: checks who is signing in and limits what each user may see or change. A cashier can record sales, while only the owner can change prices.
• Integrity enforcement: applies constraints and validation rules so that invalid data, such as a negative quantity, is rejected before it is stored.
• Concurrency control: lets many users work at the same time without spoiling each other's work. If two cashiers sell the last tin of paint together, the DBMS makes sure only one sale succeeds.
• Transaction management and recovery: treats related changes as one unit and keeps a log, so that after a power cut the database returns to a consistent state with no half-finished sale.
• Backup and recovery: offers facilities to copy the data periodically and to restore it when data is lost or corrupted, which protects retail businesses, farms and every other organisation that depends on its records.
• Data sharing and communication: lets many users and applications reach the same data over a network, whether from the till, a manager's laptop or a website.
• Performance and administration utilities: supply query optimisation, indexes, monitoring and import or export tools so that the database stays fast and manageable as it grows.

Advantages of using a DBMS

Compared with keeping data in separate files, a DBMS gives these advantages.

• Improved data sharing: many users in an organisation can reach the same data at the same time without conflicts.
• Data integrity enforcement: constraints and validation rules keep data accurate and consistent.
• Reduced data redundancy: each fact is stored once, which lowers storage use and prevents copies from disagreeing.
• Enhanced security: user authentication and authorisation decide who may see or change each part of the data.
• Efficient data access: query processing and indexes return answers quickly even when the tables are large.
• Data independence: structures can change without rewriting every application.

A DBMS also has costs. It needs skilled staff, suitable hardware and regular maintenance, and because many users depend on one system, a failure without a tested backup can stop the whole organisation.

1.1.1.3 Database applications

A database application is any system whose main job is to store data and let people use it. Databases are found wherever an organisation must keep many related records. The areas below show the range of work an ICT technician may meet.

• Retail and wholesale: point-of-sale tills, stock control and supplier records in supermarkets, shops and distributors.
• Hospitality and tourism: room reservations, guest records, restaurant orders and tour bookings in hotels, lodges and travel offices.
• Finance and payments: customer accounts, transaction records and mobile money platforms, where accurate and secure transaction processing is essential.
• Transport and logistics: parcel tracking for courier firms, ticketing, fleet maintenance and delivery schedules.
• Healthcare: patient registers, appointment lists and pharmacy stock, where confidentiality and accuracy are especially important.
• Education and training: admission records, timetables, examination results and fee records for learning institutions.
• Public services: licensing, revenue collection and civil registers kept by government bodies.
• Telecommunications: subscriber records, call and data usage records and billing.
• Agriculture: farm records, produce collection and delivery records and input stock, which help trace a product from the farm to the buyer.
• Websites and mobile applications: many dynamic websites, such as online shops, keep product lists, user accounts and orders in a database behind the pages. Phones use small embedded databases, and SQLite is built into both Android and iOS.
• Human resources: employee details, payroll and leave records.

1.1.1.4 Types of database management system

DBMS can be classified in several ways. A technician chooses by asking how many users there will be, where the data will live, what the organisation can afford and which kind of data must be stored.

• By number of users: a single-user DBMS supports one user at a time and is often installed on one computer, as in a small desktop database. A multi-user DBMS lets many people work at once and applies concurrency control to protect their work.
• By location of the data: a centralised DBMS keeps all data at one site. A distributed DBMS spreads the data over several sites linked by a network, as when a shop chain keeps a database at each branch and still reports across all of them.
• By deployment: a desktop or file-based DBMS keeps the whole database in one file on the user's computer; Microsoft Access (.accdb file) and SQLite work this way. A client-server DBMS runs on a server that many client applications connect to; MySQL, MariaDB, PostgreSQL, Oracle Database and Microsoft SQL Server are examples. A cloud DBMS is hosted by a provider, which looks after the hardware and often the backups.
• By licence: open-source DBMS such as MySQL, MariaDB, PostgreSQL and SQLite can be installed without a licence fee, which suits small organisations in Kenya. Commercial DBMS such as Oracle Database and Microsoft SQL Server are paid products, and Microsoft Access is sold within certain Microsoft 365 and Office plans for Windows. Check the plan and edition before promising Access to a client.
• By data model: the model decides how the DBMS arranges data, for example in related tables (relational), in a tree (hierarchical) or as objects (object-oriented). A NoSQL DBMS keeps data in forms other than related tables. Each model is explained in full under Database models.

1.1.1.5 Major components of the DBMS environment

The DBMS environment is everything needed for a database to work in daily use. It has five components, and a weakness in any one of them limits the whole system.

• Hardware: the physical equipment, namely the server or computer that runs the DBMS, storage disks, network devices, and the client computers, tills and tablets that people use. A database server needs enough memory, fast storage, a stable power supply and a backup device.
• Software: the DBMS itself, the operating system, the network software, the application software that users meet, and utilities such as backup tools. Inside the DBMS, a query processor interprets requests, a storage manager reads and writes the disk, and a transaction manager keeps changes consistent.
• Data: the facts stored in the database, together with the metadata that describes them. Data is the hardest component to replace, because hardware and software can be bought again but lost data may not be recoverable.
• Procedures: the written instructions and rules for using and managing the database, such as how to log in, how often to back up, how to add a new user and what to do after a failure.
• People: everyone who works with the database. The database administrator manages it, database designers decide its structure, application developers write the programs that use it, and end users enter and read data.

1.1.1.6 Database architecture

Database architecture describes how a database system is organised, both inside the DBMS and across computers. Two views are used.

The three-level architecture separates what users see from how data is stored, so that each level can change without disturbing the others.

External level     User views: the till screen, the stock report
        |   external/conceptual mapping
Conceptual level   Whole logical structure: tables, fields, relationships, constraints
        |   conceptual/internal mapping
Internal level     Physical storage: files, indexes, disk blocks

• External level: the different views that groups of users have. A cashier's view shows prices and quantities, while the owner's view also shows supplier costs.
• Conceptual level: the single, complete description of the whole database, which is the schema the designer creates.
• Internal level: how the data is physically stored on disk, including file formats and indexes.
• Logical data independence: the conceptual structure can change, for example by adding a field, without rewriting every user view.
• Physical data independence: the storage can change, for example by moving to a faster disk or adding an index, without changing the conceptual schema.

Database systems are also described by the number of tiers between the user and the data.

• One-tier: the database, the DBMS and the application run on the same computer, as with a single desktop database file.
• Two-tier: a client application on the user's computer connects directly to a database server. A branch's stock-control program that talks straight to a server is an example.
• Three-tier: a client, often a web browser, talks to an application server that holds the business rules, and only the application server talks to the database server. This arrangement protects the database because users never reach it directly, and it is widely used for web systems.

1.1.2 Database requirements gathering techniques

Requirements gathering is the work of finding out what the database must do before it is designed. Gathering accurate requirements is essential for building a system that meets the needs of its users, because a mistake found after the tables are built is far more expensive to correct. Techniques should capture both the technical needs, such as the data and volumes, and the business needs, such as the reports that managers read. A good analyst uses several techniques together because each reveals something the others miss.

Interviews

An interview is a direct conversation with a stakeholder, such as an owner, a department head, a data entry clerk or the person who maintains the computers. It allows in-depth exploration of what users expect and often uncovers requirements they would never write down. For example, interviewing a hardware shop owner may reveal that some items are sold by the metre and others by the piece, which affects the data type of the quantity field. Prepare questions beforehand, ask open questions, and record the answers.

Questionnaires and surveys

A structured questionnaire is given to many users to collect the same information from each. It is efficient for dispersed teams, such as staff in several branches of a distributor. Well-designed questions identify the common data fields, how often each person uses the data and the reports they want. Questionnaires are weaker than interviews for exploring unexpected needs, so they work best after the main ideas are known.

Document analysis

Reviewing existing documents, such as order books, delivery notes, invoices, stock cards, registers and old reports, shows what data is already collected and how it flows. Every box on a paper form usually becomes a field, and every repeated list becomes a table. For example, an invoice that lists several items for one customer shows a one-to-many relationship between customers and items. This technique also shows gaps and inefficiencies in the current system.

Observation

Watching users do their work reveals details that do not come out in interviews. Observing a shop attendant at the till may show that the attendant writes some sales in a notebook when the system is slow, or that product names are typed differently each time. This practical understanding guides the design of friendly screens and validation rules.

Prototyping

A prototype is an early, simplified version of the database or its screens, built so that users can try it and comment. It is especially useful where requirements change quickly, such as a reservation system for a small lodge. A prototype built in a few hours with Microsoft Access or a spreadsheet, or sketched on paper, gives users something concrete to react to and reduces misunderstandings between developers and users.

1.1.2.1 Database requirement elicitation

Elicitation is the process of drawing out, recording and confirming what stakeholders need from the database. It uses the techniques above and ends with a clear list of requirements that everyone has agreed. Consider a hardware shop moving its stock records onto a computer.

1. Identify the stakeholders: the owner, the cashiers, the person who orders stock, and whoever will maintain the system.
2. Define the scope in one or two sentences, for example "record sales, stock and suppliers for one shop", and state what is left out, such as staff payroll.
3. Choose the techniques that suit each stakeholder, such as interviews with the owner, observation at the till and analysis of the stock cards.
4. Collect the data requirements: the things the shop keeps data about (items, suppliers, sales), the facts about each, and the rough volumes, for instance about 3,000 different items.
5. Collect the process requirements: what users do with the data, such as recording a sale, receiving stock, correcting a price and closing the day.
6. Collect the output requirements: the reports and screens needed, such as a daily sales summary and a list of items below their reorder level.
7. Collect the non-functional requirements: how many people use the system at once, how fast it must respond, who may see supplier prices, how often backups are taken and what hardware is available.
8. Write the requirements in short, testable statements and ask each stakeholder to confirm them. A statement such as "the system must refuse a sale of more items than are in stock" can later be tested.
9. Rank the requirements as must have, should have and could have, so that the most important ones are built first if time or money is short.

Requirements fall into two groups. Functional requirements say what the system must do, for example record sales and print receipts. Non-functional requirements say how well it must do it, for example respond within a few seconds, be available during opening hours and keep a daily backup.

1.1.2.2 Database assessment requirement report

The database assessment requirement report is the written record of the gathered requirements and the analyst's assessment of them. The client signs it to confirm that it describes the need correctly, and the designer uses it as the starting point for design. It should be short enough to be read and precise enough to be tested against.

A typical report contains the following parts.

• Title and purpose: the name of the project, the author, the date and why the database is needed.
• Background and current system: how work is done now and the problems with it, such as lost stock cards and slow month-end counting.
• Scope and stakeholders: what the database will and will not cover, and who the users and decision makers are.
• Data requirements: the entities, their attributes and the expected volumes, such as items, suppliers and sales.
• Functional requirements: the tasks the system must perform, written as numbered statements.
• Output requirements: the screens, reports and forms to be produced.
• Non-functional requirements: the number of users, response time, security, backup, availability and growth.
• Constraints and assumptions: limits such as budget, hardware, staff skills and deadlines, and the assumptions the analyst has made.
• Assessment and recommendation: the analyst's judgement of feasibility, risks and the suggested DBMS and approach, with reasons. For a small shop with one computer and no technician, a desktop database may suit better than a server.
• Approval: a space for the client's signature and date.

An extract shows the style of the requirement statements.

Data requirement D2:  The system shall store for each item a code, a name,
                      a unit of sale (piece or metre), a selling price and
                      the quantity in stock.
Function F4:          The system shall record each sale with its date, the
                      cashier, the items sold and their quantities.
Rule R1:              A sale shall be refused when the quantity requested
                      is more than the quantity in stock.
Output O1:            A daily sales summary shall be printed by the owner
                      at closing time.

To prepare the report, collect all notes from interviews, observation and document analysis, group them by entity and by task, write each requirement as one clear statement, remove duplicates, check them with the stakeholders, add the assessment and recommendation, and obtain the signature before design starts.

Chapter Summary

This chapter explored the establishment of computerized databases by first introducing the fundamental concepts of database management systems, including key definitions, functions, applications, types, components, and architecture. It then examined techniques for gathering database requirements, emphasizing elicitation methods and the preparation of assessment reports to ensure accurate data needs capture. Various database models were discussed, highlighting relational, hierarchical, network, object-oriented, and dimensional approaches, each with distinct structures and use cases. The chapter progressed into database design, covering conceptual design through entity-relationship diagrams that define entities, attributes, and relationships with appropriate notations. Logical design was addressed by focusing on the creation of tables, identification of keys, and the process of normalization to organize data efficiently. Finally, physical design considerations were presented, including the selection of data types, implementation of indexing for performance optimization, and the enforcement of constraints to maintain data integrity. Together, these topics provide a comprehensive foundation for designing effective computerized database systems.