Chapter 2: Create a computerized database

Creating a computerized database is a fundamental skill for ICT technicians tasked with managing data efficiently in diverse Kenyan workplaces. Whether supporting a county government office managing citizen records or a retail business tracking inventory, understanding how to structure data through database objects and fields ensures reliable, accessible, and secure information storage. This chapter addresses the essential components involved in creating a database, focusing on the creation of database objects and the detailed configuration of fields to meet organizational needs.

2.1 Creation of database objects

Database objects are the structural elements that a database management system uses to organise, store, present and automate work with data. Creating them correctly is the first practical step in building a computerised database, because every later task, from entering data to producing reports, happens through one of them. Fields are the smallest parts of a table, and they decide what kind of data can be stored and how well it is protected. The examples in this topic use Microsoft Access, which shows all of its objects in one Navigation Pane, because it makes the objects easy to see. Access runs on Windows and is sold within certain Microsoft 365 and Office editions, so check the licence before choosing it for a client. Free alternatives such as LibreOffice Base work in a similar way, and server products such as MySQL provide the same ideas with different tools.

2.1.1 Database objects

The main database objects are tables, forms, queries, reports and macros. Each has one job: tables store the data, queries ask questions about it, forms enter and edit it, reports present it and macros automate routine actions. They depend on each other. A form and a report usually draw their data from a table or a query, and a macro can open a form or run a query. Consider an equipment hire business that rents out chairs, tents and public address sets for events. A table holds the equipment and the hires, a query lists items currently out, a form lets the clerk record a new hire, a report prints the weekly hire summary and a macro opens the hire form when the database starts. In Access all the objects are saved together in one file with the extension .accdb.

2.1.1.1 Tables

A table is the object that stores the raw data, in rows and columns. Each table should be about one subject, such as equipment, customers or hires, so that each fact is stored in one place only. Each row is a record, which holds all the facts about one item, and each column is a field, which holds one kind of fact for every record.

Equipment
EquipmentID  Item                  DailyRate (Ksh)  QtyOwned
E01          Plastic chair                      15       200
E02          Tent 10 m by 10 m                4500         3
E03          Public address set               6000         2

Tables have several important characteristics.

• Role: a table is the core repository where all information is held. Every other object only displays, selects or changes what is in tables.
• Structure: a table is defined by its fields, each with a name, a data type and properties. The same table can be seen in Datasheet View, which shows the data, and in Design View, which shows the structure.
• Data integrity: the data types, field properties and keys of a table control what may be entered, so wrong values are rejected at the source.
• Relationships: tables are linked through keys so that, for example, each hire record points to the customer and the equipment it concerns.
• Naming: a table name should say what it holds, such as Equipment or Hire, and avoid spaces and special characters.

2.1.1.2 Forms

A form is the object that provides a friendly screen for entering, viewing and editing data. Users who are not technical can work through a form without ever seeing the tables behind it. A hire clerk, for instance, can choose a customer from a drop-down list and enter the dates, and the form stores the data in the correct tables.

• Function: a form shows the fields of one or more tables or a query in a layout designed for the user.
• Types: a single-item form shows one record at a time. A multiple-items form shows several records in rows. A split form shows a datasheet and a single record together. A datasheet form looks like a table. A navigation form holds buttons or tabs that open other forms.
• Design: forms are built from controls such as text boxes, labels, drop-down lists, check boxes and command buttons. A control tied to a field is called bound, and one that is not is unbound.
• Validation: a form can apply rules, such as refusing an empty customer or a return date earlier than the hire date, so mistakes are caught on entry.
• Navigation: buttons to move between records, search or close the form improve the user's experience and reduce training time.

2.1.1.3 Queries

A query is the object that retrieves or changes data according to criteria that the user sets. It does not store data of its own. It stores the question, and each time it runs it produces up-to-date results from the tables. A query can select certain fields, filter records, sort them, combine tables and calculate new values.

SELECT Item, DailyRate
FROM Equipment
WHERE DailyRate > 1000;

Applied to the Equipment table above, this query returns the tent at Ksh 4,500 and the public address set at Ksh 6,000, and leaves out the chair.

• Purpose: to answer questions such as which items are out today or which customers have hired more than once.
• Select queries: retrieve data and display it.
• Parameter queries: ask the user to type a criterion, such as a date, each time they run.
• Crosstab queries: summarise data in a grid of rows and columns, such as hires per item per month.
• Action queries: change data. Make-table queries create a new table, append queries add records, update queries change values and delete queries remove records. Access warns before it runs one because it changes stored data.
• Efficiency and customisation: a query returns only the records that are needed, and it can join several tables to give a complete picture in one result.

2.1.1.4 Reports

A report is the object that formats and summarises data for viewing or printing. While a form is for entering data, a report is for showing finished information to a person who will read it, such as the owner. A report draws its data from a table or a query.

• Role: to present information clearly on screen, on paper or in a file.
• Sections: a report is divided into a report header, a page header, the detail section, group headers and footers, a page footer and a report footer. Titles go in the headers and totals in the footers.
• Features: reports support sorting, grouping and calculated fields, such as the total value of hires for each customer.
• Example: the hire business prints a weekly report of every hire, grouped by day and showing the total income for each day.
• Running reports: in Access a report is opened by the user or by a macro. A server system can run a reporting job on a schedule, using its job scheduler or another tool.
• Decision support: reports turn many records into a few figures that help the owner decide, for example which equipment earns the most.

2.1.1.5 Macros

A macro is an object that stores a list of actions so that Access performs them automatically, without the user writing a program. Macros save time on repeated tasks and make sure the steps are always done in the same order. They are built in the Macro Builder by choosing actions from the Action Catalog.

• Definition: a named set of actions, such as OpenForm, OpenReport, OpenQuery, GoToRecord and MessageBox, run in sequence.
• Capabilities: a macro can open forms, run queries, print reports, show messages and move to a record.
• Triggers: a macro runs when an event happens, such as clicking a button or opening a form. A macro named AutoExec runs when the database opens.
• Example: a macro attached to a Hire Today button opens the hire form, moves to a new record and shows a reminder to check the customer's identification.
• Programming: macros need a logical sequence of actions, and conditions can be added to decide when an action runs. Complex logic is usually written in Visual Basic for Applications (VBA) instead.
• Safety: macros can contain harmful actions, so Access blocks content from untrusted files and shows a security warning. Enable macros only in files from a source that is known and trusted.

2.1.2 Fields

A field is one column of a table. It holds one kind of fact for every record, and it has a name, a data type and a set of properties. Properly specified fields ensure data integrity, efficient storage and meaningful retrieval. The examples in this part come from a photo studio that records client bookings, with fields for the client name, phone number, session date, package, deposit and confirmation.

Field naming conventions

Field names make queries, forms and code easier to read and maintain, and they should follow a convention.

• Clarity: use descriptive names, such as SessionDate and not simply Date.
• Consistency: use one style throughout the database, such as capital letters at the start of each word with no spaces.
• Avoiding reserved words: names such as Date, Name or Order are keywords in some systems and can cause errors, so add detail, as in OrderDate.
• Length: keep names short enough to type but long enough to be clear.
• Key names: use the same name for a key and for the foreign key that refers to it, such as ClientID in both tables. A name such as BookingNo is clearer than the vague name ID.

2.1.2.1 Records

A record is the complete set of field values about one thing, shown as one row of a table. In the studio's Booking table one record describes one booking.

BookingNo  ClientName  Phone         SessionDate  Package    Deposit (Ksh)  Confirmed
1          Achieng     0700 000 001  2026-12-05   Wedding            5000  Yes
2          Baraka      0700 000 002  2026-12-06   Portrait            500  No

Here the table holds two records and seven fields. Records are what users add, change, find and delete.

• Adding: a new record is entered in the blank row at the end of the datasheet or through a form.
• Editing: values in a record are changed in place. The record is saved when the user moves to another record.
• Deleting: removing a record removes all its fields. Access asks for confirmation because the deletion cannot be undone.
• Finding and sorting: records can be searched, filtered and sorted without changing the data stored.
• Uniqueness: every record must be distinguishable from the others, which is the job of the key described below. Two clients with the same name need different booking numbers.

2.1.2.2 Data types

A data type decides what kind of values a field accepts and how much space it uses. Choosing the right type prevents wrong entries and makes sorting and calculation work properly. The guidelines for choosing a type are given under Selection of data types in database design. The commonly used types in Microsoft Access, in current Microsoft 365 versions, are shown below with a rough equivalent in SQL.

The Number type has sizes. Byte stores whole numbers from 0 to 255, Integer from −32,768 to 32,767 and Long Integer from −2,147,483,648 to 2,147,483,647, while Single and Double store decimal numbers with different precision. Phone numbers are stored as Short Text, because a leading zero would be lost in a Number field and nobody adds phone numbers together. Memo is the old name for Long Text.

2.1.2.3 Field properties

Field properties are settings that refine how a field behaves. They appear in the lower pane of Design View and differ a little for each data type. Careful settings control what is accepted, how it looks and how it is stored.

• Field Size: sets the maximum length of text or the size of a number. Short Text defaults to 255 characters, so reducing ClientName to 60 characters limits storage and rejects absurdly long entries.
• Format: controls how a value is displayed without changing what is stored, such as a Currency format or a date shown as 05-Dec-2026.
• Decimal Places: sets how many digits appear after the point in a Number or Currency field.
• Input Mask: forces a pattern during entry, for example ten digits for a phone number.
• Caption: gives a friendlier label to show in forms and reports, such as Client name for the field ClientName.
• Default Value: supplies a preset value for each new record, such as No for Confirmed. It reduces typing and mistakes.
• Validation Rule: is a condition the value must meet, such as >=0 for Deposit.
• Validation Text: is the message shown when the rule is broken, such as Deposit cannot be negative.
• Required: when set to Yes, the field cannot be left empty, so important facts are never missing.
• Allow Zero Length: decides whether an empty text string may be stored. An empty string is different from a missing value.
• Indexed: speeds up searching on the field. It can be set to allow duplicates or to refuse them.

2.1.2.4 Keys

A key is a field, or a group of fields, that identifies records or links tables. The kinds of key, such as candidate, alternate and surrogate keys, are explained under Keys in database design. This part shows how keys are set when objects are created.

• Primary key: uniquely identifies each record. In Design View the field is selected and the Primary Key button is clicked. For the studio, BookingNo is a good primary key. The DBMS refuses a duplicate or empty value and automatically indexes the field.
• AutoNumber as a key: an AutoNumber field generates a new number for each record, so the user never types or duplicates it.
• Composite key: two or more fields used together as the key. They are selected together in Design View before clicking the Primary Key button. A table linking sessions to photographers might use SessionNo and PhotographerID together.
• Foreign key: a field that holds the primary key value of a record in another table. If clients were kept in their own table, Booking would hold ClientID, with the same data type as the primary key it refers to, so each booking points to one client.
• Unique values on other fields: a field that must not repeat but is not the primary key, such as an email address, is set to Indexed with no duplicates.

Chapter Summary

This chapter explored the creation of computerized databases by first detailing various database objects such as tables, forms, queries, reports, and macros that serve as foundational elements for data management. It examined fields and records, highlighting the importance of data types, field properties, and keys in structuring information effectively. The process of database development was covered extensively, including the creation and linking of tables, as well as building queries, forms, and reports to facilitate data interaction and presentation. Integrity constraints were discussed to ensure data accuracy and consistency, focusing on null values, entity integrity, referential integrity, and general constraints. The chapter also introduced database indexing, differentiating between primary and secondary indexing to optimize data retrieval. Finally, it explained data relationships by describing one-to-one, one-to-many, many-to-one, and many-to-many associations, alongside fundamental database operations such as insert, select, update, and delete. An introduction to SQL was provided with guidance on writing commands and using SQL views to manage and manipulate database information efficiently.