Chapter 4: Test computerized database

Test computerized database systems are essential for ICT Technicians in Kenya who maintain and develop reliable data management solutions across industries such as banking, healthcare, and government services. Rigorous testing ensures databases perform accurately, securely, and efficiently to support critical operations like customer transactions, patient records, and financial reporting. This chapter focuses on developing effective test plans and preparing appropriate test data to validate database systems, which are foundational skills for ICT Technicians tasked with deploying and maintaining computerized databases.

4.1 Test plan development

A database test plan is the document that says what will be tested in a database, how, by whom, when, with what data and what result counts as a pass. It is written before testing starts so that the work is systematic and every important feature is covered. Without a plan, testers check only what comes to mind first and leave gaps. The examples in this topic use a small internet service provider (ISP) billing database with three tables: packages, subscribers and payments.

packages
package_id  package_name  monthly_fee_ksh
P1          Home Basic    2000
P2          Home Plus     3500
P3          Business      6000

subscribers
subscriber_id  full_name      package_id  status
7001           Anne Njeri     P1          Suspended
7002           Omar Salim     P2          Suspended
7003           Grace Wafula   P3          Active

payments
payment_id  subscriber_id  amount_ksh  paid_on
8001        7003           6000        2026-11-01

Components of a database test plan

A test plan is made of a fixed set of parts. Each part answers one question that the testers and the people approving the plan need answered.

• Scope: States which tables, queries, procedures and screens are tested and which are left out. Limiting the scope prevents the work from growing without end. For the ISP, the scope may be the payment recording and subscriber status functions, and not the monthly report layout.
• Test objectives: States what the testing must prove, such as that every payment is stored correctly or that the payment screen answers within an agreed time. Objectives decide which test types and test cases are chosen.
• Test types and approach: Lists the types of testing to be used, such as structural, functional, stored procedure and non-functional testing, and how each will be carried out, by hand or with a tool.
• Test environment: Describes the hardware, operating system, database system and version, network and tools used for testing. The environment should resemble the live system, and testing is done on a copy of the database and never on the live one.
• Roles and responsibilities: Names who writes test cases, who runs them, who fixes defects and who approves the results.
• Schedule and milestones: Gives the dates for preparing, running and reporting, and for retesting after defects are fixed.
• Entry and exit criteria: Entry criteria say what must be ready before testing starts, for example a finished and loaded test database. Exit criteria say when testing may stop, for example when all planned tests have run and no serious defect remains open.
• Risks: Lists what could delay or weaken the testing, such as an unavailable test server, and how each risk will be reduced.

Steps to develop a database test plan

A test plan is developed in a sequence of steps, so that nothing is missed and every test can be traced to a requirement.

1. Study the requirements and the database design, including the design documents, the table list and the business rules, and list the features that can be tested. For the ISP, one rule is that a subscriber becomes Active once the month's payments reach the package fee.
2. Define the test objectives that follow from those requirements, such as proving that the status rule works and that payments cannot be stored for a subscriber who does not exist.
3. Select the test types that match the objectives. Rules about data and structure need structural and functional tests, a payment routine stored in the database needs stored procedure tests, and speed needs load and stress tests.
4. Design the test cases. Each case states its input, the steps and the expected result, and covers normal cases, boundary values and invalid inputs, such as a negative payment amount.
5. Arrange the test environment and test data: build a copy of the database, load it with suitable records and set up the tools. Preparing the data in detail is dealt with under Prepare test data.
6. Assign roles and set the schedule with checkpoints at which progress is reviewed.
7. Submit the plan for review by the stakeholders, correct any gaps they find and record their approval before testing begins.

4.1.1 Importance of database testing

Database testing checks that the data a system stores is correct, safe and available when it is needed. The database sits underneath every screen and report, so a fault here spreads to everything above it. A technician who tests the database properly protects the trust that managers and customers place in the system.

Ensuring data integrity and accuracy

Testing verifies that stored data stays correct and consistent after inserts, updates and deletions. For example, after a payment of Ksh 2,000 is recorded for subscriber 7001, the payments table must hold exactly one new row with the right amount, and the subscriber's status must change correctly. A fault found now is cheaper to correct than wrong invoices discovered at month end.

Preventing security vulnerabilities

Testing exposes weaknesses that could reveal or damage sensitive data, such as weak access rights or an input field that accepts malicious SQL. Subscriber names and phone numbers are personal data, and the Data Protection Act, 2019 expects organisations that hold such data to protect it. Testing that each user can reach only the data allowed to that role supports that duty.

Validating database performance

Performance testing shows whether queries and transactions finish in an acceptable time, including when many users work at once. A billing database that answers in a fraction of a second with ten test records may become very slow when it holds many thousands of payments, and the slowness is found by testing and not by guessing.

Detecting logical and structural errors

Testing finds mistakes in the design itself, such as a missing relationship between tables, a field with the wrong data type or a constraint that was never created. If subscribers could be saved with a package that does not exist, invoices would be wrong without any visible error message.

Supporting compliance with rules and agreements

Regular, recorded testing shows that the system follows the rules it was built for, such as an organisation's own data policy or the terms in a service agreement with a customer. The test records become evidence that the rules were checked and not only assumed.

4.1.2 Types of database testing

Database testing is divided into types because each type looks at a different side of the database. Some check the structure, some check what the database does, and some check how well it does it. A test plan normally combines several of them.

4.1.2.1 Structural DB testing

Structural testing checks the objects that make up the database and the rules attached to them, without looking at what the application does with them. It confirms that the database was built as designed. Testers compare the database with the design documents, and they often read the structure with queries on the system tables and on the information schema, so that the checking can be repeated.

4.1.2.1.1 Schema

A schema is the organised collection of the tables, views, indexes, keys and other objects of a database, with their definitions. Schema testing confirms that the objects named in the design exist, that they are named correctly and that the relationships between them are in place. Example: after the billing database is installed on a test server, the tester lists the tables and compares the list with the design.

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'isp_billing';

The expected result is three rows: packages, payments and subscribers. In MySQL the table_schema value is the database name, while in PostgreSQL it is the schema name, often public. A fourth, unplanned table would show that the installation differs from the design.

• Existence of objects: Every table, view, index and routine in the design is present, and nothing extra has been added.
• Names and conventions: Names follow the agreed style, so the application finds the objects it expects.
• Relationships: Each foreign key links the right pair of tables, for example subscribers.package_id to packages.package_id.
• Comparison between environments: The schema of the test server is compared with the schema of the live server before a change is released, so that the two stay identical.
• Version control: The scripts that build the schema are stored and numbered, so that the database can be rebuilt exactly.

4.1.2.1.2 Table and column

Table and column testing checks the detail inside each table: the data type, size, default value, whether the field may be empty, and the keys and constraints on each column. The tester reads the definition and then tries to break the rules with wrong values. The column definitions can be listed with a query and compared with the design.

SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'payments';

The next table shows structural test cases for the billing database.

• Data types: A date column accepts only valid dates, and an amount column accepts only numbers, with the right number of decimal places for money.
• Size and length: Each column is long enough for real values, such as the longest expected name, and not so long that storage is wasted.
• Empty values: Required columns refuse empty values, and optional columns accept them.
• Default values: A new record that omits a column receives the planned default, for example Suspended for the status of a new subscriber.
• Keys and constraints: Primary keys, foreign keys, unique rules and check rules exist on the right columns and are enforced by the database system in use. Older versions of some systems accepted check rules but did not enforce them, so the test must prove they are active.

4.1.2.2 Functional testing

Functional testing checks that the operations of the database give the correct result according to the business rules. It tests what the database does: inserting, reading, updating and deleting data, running queries, and applying rules through constraints, triggers and views. Example: in the billing database, the rule is that a subscriber becomes Active when the payments in the month reach the monthly fee of the package.

• Insert, update and delete operations: Each operation is run and the stored data is read back to confirm the change.
• Business rules: A payment of Ksh 2,000 for subscriber 7001, whose package costs Ksh 2,000, must change the status to Active.
• Query results: A query that lists suspended subscribers must return exactly those records and no others.
• Triggers and views: A trigger that records every change in an audit table must fire, and a view must show the same figures as the tables it reads.
• Failure cases: An operation that must fail, such as paying for a subscriber who does not exist, must be refused with a clear message.

4.1.2.2.1 Whitebox

Whitebox testing is carried out with knowledge of the internal structure and code of the database. The tester reads the table definitions, triggers, views and stored routines, and designs tests that exercise every part of them. It is usually done by the developer or a technician with access to the database code. Example: a trigger writes a row to an audit table whenever a payment is changed. The tester updates payment 8001 and checks that the audit table holds the old and the new amount.

• Code paths: If a routine has an IF branch for part payments and another for full payments, test cases are written so that each branch runs at least once.
• Constraints and triggers: The tester confirms that each rule written in the code actually fires, and that it fires only when intended.
• Cascade rules: If deleting a subscriber should also delete the related payments, the tester checks that exactly those rows disappear.
• Execution plans: The tester looks at how the database system runs a query, for example with the EXPLAIN command, to see whether an index is used.
• Hidden effects: The tester checks that a routine changes only the tables it is supposed to change.

4.1.2.2.2 Blackbox

Blackbox testing treats the database as a closed box: the tester supplies inputs through a form, a screen or a query and checks the outputs, without looking at the code or the structure inside. It is done by testers and by users, because it shows what a user would experience. Common techniques are equivalence partitioning, boundary value analysis and error guessing. Example: assume the billing rules accept a payment of Ksh 1 to Ksh 100,000. Input values are grouped into three partitions, below the range, inside it and above it, and the edges of the range are tested.

• Equivalence partitioning: Inputs that the system should treat alike are grouped, and one value from each group is tested instead of every value.
• Boundary value analysis: The values at and just beyond the edges of each range are tested, because mistakes are often found there.
• Error guessing: The tester tries inputs that experience suggests will cause trouble, such as empty text, very long text or special characters.
• Expected outputs: For each input, the correct output is written down before the test is run.
• No code needed: The test can be repeated by anyone, even if the internal design of the database changes.

4.1.2.3 Stored procedure

A stored procedure is a set of SQL statements saved inside the database and run by name, usually with input values called parameters. Applications use procedures to carry out important tasks in a consistent way. Stored procedure testing checks that each procedure returns the right results, handles wrong input safely and leaves the data consistent. Example: the procedure record_payment takes a subscriber number and an amount, stores the payment and sets the status to Active when the month's payments reach the package fee. It is called with CALL record_payment(7001, 2000); in MySQL and PostgreSQL, while SQL Server uses EXEC.

• Parameters: The procedure is tested with valid values, invalid values, empty values and boundary values, as in the table.
• Returned results: The values the procedure returns, such as a status code or message, are compared with the expected ones.
• Data changes: The tables are read before and after the call to confirm that exactly the planned rows changed.
• Error handling and rollback: When the procedure fails halfway, the earlier changes are undone, so no half-finished payment is left behind.
• Permissions: Only users with the right to run the procedure can run it, and users who only have read access are refused.
• Repeat calls: Calling the procedure twice with the same input produces the expected result, whether a second row or a refusal.

4.1.2.4 Non-functional testing

Non-functional testing checks how well the database performs its work, rather than what work it does. The questions are about speed, capacity, safety and recovery. A payment screen that gives the correct answer after two minutes passes a functional test but fails a non-functional one. Besides load and stress testing, non-functional testing also covers the following.

• Security checks: The tester tries to enter harmful SQL in input fields, a method called SQL injection, and confirms that the application refuses it. The tester also confirms that users cannot read or change data outside their role.
• Recovery checks: A backup is restored on a test server, and the restored data is compared with the original to prove that the backup can be relied on. The same comparison is made after data is moved from one database system or version to another.

4.1.2.4.1 Load testing

Load testing measures how the database behaves under the amount of work it is expected to carry, including the busiest period. The test simulates many users or many transactions at the same time and records the response time, the number of transactions completed per second and the use of processor, memory and disk. Example: on the last day of the month many ISP subscribers pay at once. The plan sets the normal load and the expected peak, and states a target, for example that 95 percent of payments are recorded in under 2 seconds.

1. Decide the expected load from the number of users and the busiest hour, using figures from the owner of the system and not guesses.
2. Prepare a test database that holds a realistic number of records, because a database with a few records answers quickly whatever its design.
3. Write the test script that records a payment, then repeat it for each simulated user.
4. Run the test in steps, for example 50, 100 and 200 users at the same time, each step for a fixed time such as 15 minutes.
5. Record response times, errors and the use of processor, memory and disk for each step.
6. Compare the results with the targets in the plan and report any step that fails.

Tools for generating the load include Apache JMeter, pgbench for PostgreSQL and mysqlslap for MySQL. The tool should be matched to the database system and its version.

4.1.2.4.2 Stress testing

Stress testing pushes the database beyond its expected limits, until it slows badly or fails, to find the breaking point and to see how it fails and recovers. Where load testing asks whether the system copes with the expected peak, stress testing asks what happens when the peak is exceeded. Example: after the 200-user step in the billing test, the load is raised in further steps until payments begin to time out.

• Breaking point: The number of users or transactions at which errors or time-outs begin is recorded as the limit of the system.
• Resource shortage: Disk space or memory on the test server is reduced on purpose, to see how the database reacts when resources run out.
• Failure behaviour: The tester checks that the database refuses new work with a clear message and does not return wrong answers.
• Data safety after failure: After the overload or a forced restart, the data is checked for lost or half-saved payments.
• Recovery: The time the database takes to return to normal once the load is removed is measured.

The difference between the two tests is summarised below.

Chapter Summary

This chapter focused on the systematic process of testing computerized database systems, beginning with the development of a comprehensive test plan that outlines the scope and objectives of database testing. The importance of database testing was emphasized as a crucial step to ensure data integrity, performance, and security. Various types of database testing were explored, including structural testing which examines the database schema, functional testing that verifies database operations, stored procedure testing to validate embedded logic, and non-functional testing addressing performance and scalability. The preparation of test data was highlighted as essential for simulating real-world scenarios and ensuring thorough evaluation. The chapter then covered how to perform database testing using the prepared test data, including the critical task of validating database results to confirm accuracy and reliability. Finally, the generation of test reports was discussed, underscoring their role in documenting findings and supporting decision-making, along with guidelines on how to prepare clear and informative test reports.