2.1 Creation of Database Objects

Creating a database involves defining various objects that structure and organize data logically and efficiently. In the Kenyan ICT sector, database objects underpin systems such as hospital patient records, SACCO member databases, and school management systems. Proper creation of these objects determines how data is stored, accessed, and maintained, directly affecting system performance and data integrity.

2.1.1 Database Objects

Database objects are the fundamental components within a database that hold or reference data and define its structure. They form the building blocks that enable an ICT Technician to organize data systematically. In practice, objects such as tables, queries, forms, and reports are used extensively in various sectors like banking (e.g., KCB’s customer transaction systems) and county government record-keeping.

Tables

Tables are the core database objects where data is stored in rows and columns. Each row represents a record, and each column represents a field or attribute. For example, a table in a hospital management system might include patient details such as name, ID number, and admission date. Tables must be carefully designed to avoid redundancy and ensure data normalization.

Queries

Queries enable users to retrieve and manipulate data from one or more tables by specifying criteria. They are essential for generating reports or extracting subsets of data for decision-making. At a retail business, queries might be used to find all customers who purchased a specific product within a given period.

Forms

Forms provide a user-friendly interface for entering, modifying, and viewing data stored in tables. They simplify data entry and reduce errors, which is critical in environments like schools or universities where staff may not be database experts.

Reports

Reports organize and present data in a structured format for analysis or presentation. They are often used for official documentation, such as financial reports in SACCOs or inspection summaries in NEMA.

Indexes

Indexes improve the speed of data retrieval operations by creating pointers to data in tables. They are particularly important in large databases, such as those used by county governments, to enhance performance and reduce query response times.

2.1.2 Fields

Fields are individual data categories within a table that define the type of data stored. Each field has specific properties that determine how data is entered, displayed, and validated. Effective field design is crucial for data accuracy and consistency in computerized databases used in Kenyan organizations.

Field Data Types

Field data types specify the nature of data a field can hold, such as text, number, date/time, or currency. Selecting appropriate data types prevents errors and optimizes storage. For instance, a date field in an insurance company database must be set to a date/time type to allow accurate sorting and calculations.

Field Size and Format

Field size limits the amount of data that can be stored in a field, which is important for controlling data length and ensuring uniformity. For example, a field for phone numbers in a county government database might be restricted to 10 digits to match the standard Kenyan format.

Field Properties and Validation Rules

Fields can have properties like default values, required status, and validation rules to enforce data integrity. Validation rules prevent entry of invalid data, such as negative quantities in an inventory system at a hotel.

Primary Key Fields

A primary key is a unique identifier for each record in a table, ensuring that no duplicate records exist. In a cooperative society database, the member ID field often serves as the primary key to uniquely identify each member.

Relationships Between Fields

Fields in different tables can be linked through relationships, such as one-to-many or many-to-many, enabling complex data models. For example, in a university database, a student’s ID field in the student table relates to the enrolment table to track courses taken.

Chapter Summary

This chapter explored the creation of computerized databases by first examining the various database objects such as tables, forms, queries, reports, and macros, detailing their roles in organizing and managing data. It then delved into the structure of fields and records, highlighting the importance of data types, field properties, and keys in ensuring data accuracy and accessibility. The development process was covered extensively, including the creation and linking of tables, as well as the formulation of queries, forms, and reports to facilitate data interaction and presentation. Essential integrity constraints like nulls, entity integrity, referential integrity, and general constraints were discussed to maintain data consistency and reliability. The chapter also addressed database indexing techniques, differentiating between primary and secondary indexing to optimize data retrieval. Finally, it examined various data relationships including one-to-one, one-to-many, many-to-one, and many-to-many, alongside fundamental data manipulation operations such as insert, select, update, and delete. An introduction to SQL was provided, focusing on writing SQL commands and creating SQL views to enable efficient database querying and management.