Creating a database system is fundamental for software development professionals in Kenya, as it enables the systematic management of data that supports application functionality and business processes. In the Kenyan software development industry, databases are crucial for handling large volumes of data efficiently, ensuring data integrity, and enabling fast retrieval for decision-making, such as in banking applications, hospital management systems, or county government record keeping. This chapter focuses on the foundational steps involved in creating database objects and defining fields, which are key to structuring any database system.

2.1 Creation of Database Objects

The creation of database objects lays the groundwork for organizing and managing data within a database system. In software development, these objects form the components that store, manipulate, and control data, supporting the logic and operations of applications. In Kenya, whether developing a system for a SACCO or a university, understanding database objects ensures that developers can design scalable and maintainable databases that meet user requirements.

2.1.1 Database Objects

Database objects are the fundamental components within a database that define its structure and functionality. Each object serves a unique purpose, contributing to the overall data management and integrity of the system.

Types of Database Objects

• Tables: These are the core objects where data is stored in rows and columns. In a banking system like Equity Bank’s core banking software, tables hold customer information, transactions, and account details, allowing structured data storage.
• Views: Views are virtual tables created by querying one or more tables. They provide a customized way to look at data without altering the base tables, commonly used in reporting systems at institutions like the Kenya Revenue Authority (KRA).
• Indexes: Indexes improve the speed of data retrieval operations. For example, in a hospital management system, indexes on patient ID fields enable quick searches across large patient records.
• Stored Procedures: These are sets of SQL statements stored in the database that perform specific tasks. In insurance claim processing software, stored procedures automate validation and updates of claim statuses.
• Triggers: Triggers automatically execute predefined actions in response to certain events on a table, such as inserting or deleting data. For instance, in a retail business database, a trigger might update stock levels after a sale transaction.

Characteristics of Database Objects

• Persistence: Objects remain in the database until explicitly deleted, ensuring data and structure continuity.
• Schema-bound: Objects conform to the database schema, maintaining consistency and enforcing data types.
• Security-controlled: Access to objects can be restricted through permissions, crucial for sensitive data in systems like county government records.
• Interdependent: Many objects rely on others, such as views depending on tables, requiring careful planning during design.
• Modifiable: Objects can be altered to accommodate evolving application needs, allowing flexibility in software maintenance.

Importance of Database Objects in Software Development

• Data Organization: Objects provide a structured framework to organize data logically and efficiently.
• Performance Optimization: Proper use of indexes and views enhances query performance, critical for responsive applications.
• Data Integrity Enforcement: Constraints within objects ensure accuracy and consistency of data, reducing errors in applications like payroll systems.
• Security Management: Objects enable granular access control, protecting sensitive information in sectors like healthcare.
• Reusability: Stored procedures and views promote code reuse, simplifying maintenance and reducing development time.

Creating Database Objects: Tools and Techniques

Database management systems (DBMS) such as Microsoft SQL Server, MySQL, and PostgreSQL offer graphical and command-line tools for creating objects. Developers typically use SQL Data Definition Language (DDL) commands like CREATE TABLE, CREATE VIEW, and CREATE INDEX to define objects programmatically. In Nairobi's tech hubs, software teams often integrate these commands into automated deployment scripts to maintain consistency across environments.

Challenges in Managing Database Objects

• Dependency Complexity: Managing inter-object dependencies demands careful change management to avoid breaking applications.
• Performance Trade-offs: Excessive indexing can degrade write performance, requiring balanced optimization.
• Security Risks: Improper permission settings can expose sensitive data, necessitating thorough access audits.
• Scalability Constraints: Poorly designed objects may hinder database scalability as data volumes grow, affecting systems like large SACCOs.
• Migration Difficulties: Altering objects during system upgrades requires meticulous planning to prevent data loss.

2.1.2 Fields

Fields are the smallest units of data storage in a database table, representing individual data attributes. Each field corresponds to a column in a table and defines the type of data it can hold, such as text, numbers, or dates. In Kenyan software projects, precise field definitions ensure data accuracy and support the specific needs of applications, for example, capturing detailed customer profiles in a mobile money platform.

Definition and Role of Fields in Database Systems

Fields define the structure of data entries and determine how information is stored and retrieved. Each field has a name, data type, size, and constraints that guide data input and validation. In an educational institution’s student management system, fields like StudentID, Name, DateOfBirth, and EnrollmentDate are critical for uniquely identifying and managing student records.

Types of Fields and Their Usage

• Text Fields (VARCHAR, CHAR): Store alphanumeric characters, used for names, addresses, and descriptions.
• Numeric Fields (INT, FLOAT, DECIMAL): Hold numerical data, essential for financial calculations in banking applications.
• Date/Time Fields: Capture dates and times, vital for logging transactions and appointments in hospital systems.
• Boolean Fields: Represent true/false values, used in flags such as account activation status.
• Binary Fields: Store binary data like images or documents, useful in insurance claim systems for storing scanned documents.

Field Properties and Constraints

• Data Type: Determines the kind of data stored, affecting storage size and validation.
• Field Size: Specifies the maximum length or precision, controlling data input limits.
• Default Value: Provides a preset value when no input is given, ensuring data completeness.
• Nullability: Defines whether a field can accept NULL values, impacting how missing data is handled.
• Unique Constraint: Ensures field values are distinct, critical for identifiers like employee IDs in county offices.

Designing Fields for Data Integrity and Efficiency

Effective field design prevents data anomalies and optimizes performance. For example, in a retail business inventory system, setting appropriate field sizes reduces storage waste, while unique constraints on product codes prevent duplication. Using appropriate data types also ensures that calculations, such as tax computations, are accurate and efficient.

Field Naming Conventions and Best Practices

Consistent and descriptive field names improve database readability and maintenance. Kenyan software teams often adopt camelCase or underscore_separated naming styles to align with coding standards. Names should be concise yet meaningful, avoiding reserved keywords and special characters to prevent conflicts in SQL queries.

Chapter Summary

This chapter covered the creation of database systems with a focus on the essential database objects such as tables, forms, queries, reports, and macros, detailing their roles in structuring and managing data. It explored fields and records, emphasizing data types, field properties, and keys which are fundamental for organizing and securing data integrity. The development process was examined through steps including table creation, linking tables to establish relationships, and designing queries, forms, and reports to facilitate data entry and retrieval. Integrity constraints were highlighted as crucial for maintaining accuracy and consistency of data, while database indexing was discussed as a method to improve query performance. The chapter concluded by explaining different types of data relationships including one-to-one, one-to-many, many-to-one, and many-to-many, which define how data entities interact and relate within the database system. This comprehensive approach equips students with the foundational knowledge necessary to build and manage effective computerized database systems.