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

The creation of database objects forms the backbone of any computerized database system. Database objects are the structural elements that organize, store, and enable interaction with data. For ICT technicians in Kenya, such as those supporting SACCOs or healthcare facilities, mastering database objects ensures that data is logically arranged and easily retrievable, which improves operational efficiency and decision-making.

2.1.1 Database objects

Database objects are the fundamental building blocks within a database management system (DBMS). They include tables, queries, forms, reports, indexes, and macros, each serving a specific role in data handling and presentation. Understanding these objects enables ICT technicians to design databases that are both functional and user-friendly.

Tables

• Definition and Role: Tables store the raw data in rows and columns, representing entities such as customers, products, or transactions. They are the core repository where all information is held.
• Structure: Each table consists of records (rows) and fields (columns), with each field representing a data attribute.
• Example: In a county government office, a table might store citizen details including name, ID number, and address, enabling efficient record keeping.
• Data Integrity: Tables enforce data types and constraints to maintain accuracy.
• Relationships: Tables can be linked through keys to represent real-world associations, supporting complex queries.

Queries

• Purpose: Queries retrieve specific data by filtering, sorting, or calculating based on criteria.
• Types: Select queries extract data, action queries modify data, and parameter queries request user input.
• Use Case: A SACCO might use queries to generate reports on member loan balances.
• Efficiency: Queries optimize database performance by limiting data retrieval to relevant records.
• Customization: Queries can join multiple tables to produce comprehensive datasets.

Forms

• Function: Forms provide a user-friendly interface for data entry and editing.
• Design: They can include controls like text boxes, drop-down lists, and buttons.
• Application: In a hospital setting, forms simplify patient registration.
• Validation: Forms can enforce input rules to prevent errors.
• Navigation: Well-designed forms improve user experience and reduce training time.

Reports

• Role: Reports format and summarize data for presentation or printing.
• Features: They support grouping, sorting, and calculated fields.
• Example: A retail business uses reports to monitor monthly sales performance.
• Automation: Reports can be scheduled to run automatically for regular updates.
• Decision Support: Reports provide insights critical for management decisions.

Indexes

• Purpose: Indexes speed up data retrieval by creating pointers to data locations.
• Types: Unique indexes prevent duplicate entries, while non-unique indexes improve search speed.
• Impact: Proper indexing enhances query performance in large databases.
• Considerations: Excessive indexing can slow down data updates.
• Maintenance: Indexes require periodic rebuilding to maintain efficiency.

Macros

• Definition: Macros automate repetitive tasks within the database.
• Capabilities: They can open forms-run queries, or generate reports.
• Use Case: Automation of monthly report generation at a university database system.
• Programming: Macros require logical sequencing of actions.
• Limitations: Complex automation may require transitioning to scripting languages.

2.1.2 Fields

Fields define the structure and type of data stored within each table. Properly specifying fields ensures data integrity, efficient storage, and meaningful retrieval. ICT technicians must understand field properties and types to design databases that meet the precise needs of Kenyan organizations such as banks or educational institutions.

Field Types and Data Formats

• Text Fields: Store alphanumeric characters, suitable for names, addresses, and descriptions. Typically limited to a set number of characters to optimize storage.
• Number Fields: Store numeric data for calculations, such as quantities or financial figures. Different number types include integers, decimals, and currency.
• Date/Time Fields: Capture dates and times, essential for tracking transactions, appointments, or deadlines.
• Boolean Fields: Store binary data such as Yes/No or True/False, often used for status indicators.
• Memo/Long Text Fields: Accommodate large text entries like notes or comments, without a strict character limit.

Field Properties

• Field Size: Determines the maximum length or precision of data stored, impacting storage efficiency and data validation.
• Format: Controls the display of data, such as currency symbols or date formats, enhancing readability.
• Default Value: Specifies a preset value for new records to reduce data entry errors.
• Validation Rules: Define constraints on acceptable data, preventing invalid entries.
• Required Field Setting: Ensures critical fields are not left empty, maintaining database completeness.

Primary Keys and Unique Identifiers

• Purpose: A primary key uniquely identifies each record in a table, preventing duplicates.
• Composition: Can be a single field or a combination of fields.
• Example: A hospital patient database uses a unique patient ID as a primary key.
• Indexing: Primary keys automatically create an index to speed up access.
• Referential Integrity: Primary keys link to foreign keys in related tables to maintain data consistency.

Field Naming Conventions

• Clarity: Field names should be descriptive to convey their content clearly.
• Consistency: Use uniform naming styles across tables to simplify database management.
• Avoid Reserved Words: Prevent conflicts with DBMS keywords.
• Length: Keep names concise yet meaningful to facilitate coding and querying.
• Example: Using "EmployeeID" instead of ambiguous terms like "ID" improves clarity in a payroll system.

Handling Null Values and Defaults

• Null Values: Represent unknown or missing data, allowing flexibility in data entry.
• Impact: Nulls can affect query results and calculations if not handled properly.
• Default Values: Provide fallback data to reduce null occurrences.
• Use in Validation: Proper configuration ensures data accuracy and usability.
• Example: In an educational database, a null grade might indicate a course not yet completed.

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.