Chapter 3: Manipulate computerized database

Database manipulation is a fundamental skill for ICT technicians working in Kenya’s diverse sectors, such as financial institutions, healthcare facilities, and county government offices. It involves populating databases with accurate data and retrieving information efficiently to support decision-making and service delivery. Mastery of database operations and querying languages empowers technicians to maintain data integrity and optimize system performance. This chapter explores the processes involved in database population and data retrieval, focusing on practical applications relevant to Kenyan ICT environments.

3.1 Database population

Database population is the work of loading data into the tables of a database and keeping that data correct afterwards. A new database holds only its structure: tables, fields and rules. It becomes useful when records are entered, read, corrected and removed in a controlled way. In a computer repair workshop, for example, the technician populates the customers and repair jobs tables when a device is booked in, reads them to see which jobs are still open, updates them as the repair progresses and removes records that were entered by mistake.

3.1.1 Database operations

Database operations are the four basic actions performed on stored data: insert, select, update and delete. Together they are often called CRUD, from Create, Read, Update and Delete. Each action can be carried out through a form, a datasheet view or a SQL statement. The SQL form is used here because it works in much the same way in MySQL, MariaDB, PostgreSQL, SQL Server and SQLite, and it shows exactly what the database is asked to do. The full rules for writing SQL are taught under Data retrieval.

The examples in this topic use two linked tables from a computer repair workshop. The customer_id field in repair_jobs links each job to one customer.

customers
customer_id  full_name        phone        town
101          Faith Achieng    0700000101   Kisumu
102          Hassan Mohamed   0700000102   Mombasa
103          Peter Kamau      0700000103   Nakuru

repair_jobs
job_id  customer_id  device    fault             status       charge_ksh
5001    101          Laptop    Cracked screen    Completed    6500
5002    102          Desktop   Will not boot     In progress  0
5003    101          Printer   Paper jam         In progress  0
5004    103          Laptop    Slow performance  Received     0

3.1.1.1 Insert

Insert adds a new record to a table. The database checks the new record against the table's rules before it is stored, so a record that breaks a rule is refused instead of being saved wrongly. Example: when a customer brings in a wireless router with no internet connection, the technician inserts a new row into repair_jobs.

INSERT INTO repair_jobs (job_id, customer_id, device, fault, status, charge_ksh)
VALUES (5005, 102, 'Router', 'No internet connection', 'Received', 0);

Text values are written in single quotes and numbers are written without quotes. The column list in brackets names the fields in the same order as the values, so the statement still works if the table later gains a new column.

• Single record: One INSERT with one set of values adds one record, as in the router example above.
• Several records: Most systems accept several sets of values in one statement, separated by commas. Microsoft Access accepts only one set of values per INSERT, so several statements are used there.
• Records from another table: INSERT with a SELECT copies records from a table or query into another table, for example when moving finished jobs into an archive table.
• Default and empty values: A field left out of the column list receives its default value, or stays empty (null) if the field allows it. A field that is required and has no default makes the statement fail.
• Refused records: Inserting a job with an existing job_id is refused because job_id is the primary key. Inserting a job for customer 999 is refused because no such customer exists, so the link between the tables would be broken.

To insert a record safely:

1. Confirm that the table and its fields exist and note the data type of each field.
2. Check that the value for the primary key is not already in use, or let the database generate it if the field is auto-numbered.
3. For a linked table, confirm that the related record exists first. The customer is inserted before the customer's first job.
4. Write the statement with the column list and the values in the same order, with text in single quotes.
5. Run the statement and read the message from the database. A message such as 1 row affected means the record was stored.
6. Read the new record back with a SELECT to confirm that every value is correct.

3.1.1.2 Select

Select reads data from one or more tables and shows it as a result set. The result set is a temporary view of the data, so the stored records stay exactly as they were. Selecting is used very often, because every report, search box and check of earlier work depends on it. Example: at the start of the day the technician lists the jobs that are still being repaired.

SELECT job_id, device, status
FROM repair_jobs
WHERE status = 'In progress';

With the sample data this returns two records: job 5002 (Desktop) and job 5003 (Printer).

• Chosen columns: The column list names the fields to show. Listing only the needed fields keeps the result short and is faster than asking for everything.
• All columns: An asterisk (SELECT *) returns every field. It is useful when exploring a table but should not be used in saved queries, because the result changes whenever the table gains a column.
• Filtering rows: The WHERE clause keeps only the records that meet a condition. Without it, every record in the table is returned.
• Sorting: ORDER BY puts the result in order, for example by job_id from newest to oldest.
• Read only: A SELECT never changes data, so it is the safe way to check which records an UPDATE or DELETE will affect before running it.

Combining tables, totals and grouped results are taught under Writing SQL commands in Data retrieval.

3.1.1.3 Update

Update changes values in records that already exist. It is used when information changes or when a mistake has to be corrected, and it keeps the same record instead of creating a second one. Example: when the desktop repair is finished, the technician records the new status and the charge.

UPDATE repair_jobs
SET status = 'Completed', charge_ksh = 2000
WHERE job_id = 5002;

After the update, the earlier query for jobs that are in progress returns only job 5003.

• SET clause: The SET clause lists each field and its new value. Several fields can be changed in one statement, as in the example.
• WHERE clause: The WHERE clause selects the records to change. If it is left out, every record in the table is changed. Setting every job to Completed by accident is a common beginner error.
• Calculated values: A new value can be worked out from the old one. SET charge_ksh = charge_ksh + 500 adds a Ksh 500 parts charge to the jobs selected by WHERE.
• Rules still apply: The new value must pass the same data type, check and key rules as an inserted value. Changing a job's customer_id to a customer that does not exist is refused.
• Record of changes: The old value is lost after an update. Where the history matters, a date-modified field or a separate log table is kept.

To update records safely:

1. Write a SELECT with the same WHERE condition and run it first.
2. Check that the records returned are exactly the ones that should change, and count them.
3. Take a backup or start a transaction if many records are affected.
4. Write the UPDATE with the same WHERE condition and the new values.
5. Run it and compare the number of rows reported as changed with the count from step 2.
6. Run the SELECT again to confirm the new values, and commit the transaction or roll it back if the numbers do not match.

3.1.1.4 Delete

Delete removes whole records from a table. It cannot be undone once it is committed, so it is used with more care than the other operations. Example: if a router job was booked in twice and the second copy was stored as job 5006, the technician removes the copy.

DELETE FROM repair_jobs
WHERE job_id = 5006;

• WHERE clause: As with UPDATE, a DELETE without WHERE removes every record in the table. The table itself remains, but it is empty.
• Linked records: A customer who still has jobs cannot normally be deleted, because the jobs would be left pointing at nothing. The database refuses the deletion, or it removes the related jobs as well when the link was created with a cascade rule.
• Archive before deleting: Records that may be needed later, such as completed jobs, are copied to an archive table or exported before they are removed.
• Marking instead of deleting: Many systems add a status field such as Active or Inactive and change it with an UPDATE. The record stays available for history and can be restored.
• DELETE and TRUNCATE: DELETE removes the selected records one by one and can be rolled back inside a transaction. TRUNCATE empties the whole table at once and is not meant for removing selected records.

To delete records safely:

1. Write a SELECT with the same WHERE condition and run it first.
2. Check that only the unwanted records are returned.
3. Confirm that no other table depends on these records, or that the cascade rule is intended.
4. Back up or archive the records, and start a transaction.
5. Run the DELETE and compare the number of rows removed with the number found in step 1.
6. Run the SELECT again to confirm that the records are gone, then commit the transaction.

Validation during population

Validation checks each value as it is entered, so that wrong data is stopped at the door instead of being cleaned up later. Invalid data such as an impossible date or a phone number with the wrong number of digits damages reports and searches long after it was typed in.

• Required fields: The device and fault fields are marked as required, so a job cannot be saved without them.
• Data type: A charge field accepts only numbers, and a date field accepts only valid dates.
• Range: A check rule such as charge_ksh >= 0 refuses a negative charge.
• Format: A phone number must have ten digits, which stops entries with a missing digit.
• List of allowed values: The status field accepts only Received, In progress, Completed or Collected, which stops spelling variants such as Complete and Done.
• Uniqueness: A unique rule on the email or national ID field refuses a second record with the same value, which stops the same customer being entered twice. A phone number is a weak choice because relatives may share one.

Data integrity during population

Data integrity means that the stored data stays accurate, consistent and trustworthy while it is being inserted, updated and deleted. The database enforces integrity through the rules below.

• Entity integrity: Every record has a unique, non-empty primary key. In repair_jobs the job_id identifies each job, so two jobs can never be confused.
• Referential integrity: A foreign key must match an existing primary key in the linked table. A job can only be stored for a customer who exists, and a customer with jobs cannot be deleted by accident.
• Domain integrity: Each field accepts only values of the right type and range, such as numbers for charges and the four allowed words for status.
• User access controls: Each user is given only the permissions needed for the work. A front desk assistant may insert and select but not delete, and customer phone numbers are personal data that the Data Protection Act, 2019 requires organisations to protect from unauthorised changes and disclosure.
• Transaction management: A transaction bundles several operations so that they all succeed or none is kept. When a technician fits a replacement part, the stock quantity must fall by one and the part must be added to the job. If the second step fails, the transaction is rolled back and the stock count is restored. A transaction starts with START TRANSACTION (BEGIN in some systems) and ends with COMMIT to keep the changes or ROLLBACK to cancel them.

Chapter Summary

This chapter explored the essential processes involved in manipulating computerized databases, beginning with database population where key operations such as inserting, updating, and deleting data were detailed. It then addressed data retrieval techniques, focusing particularly on the use of Structured Query Language (SQL) as the primary tool for querying databases to extract meaningful information efficiently. The discussion progressed to database report generation, covering the steps to create comprehensive reports that summarize or analyze data. Editing reports was highlighted as a crucial step to ensure accuracy and relevance, followed by formatting techniques that enhance the visual presentation and readability of reports. The application of headers and footers was explained as a method to provide consistent structure and important contextual information across report pages. Finally, the chapter concluded with the practical considerations involved in printing reports, ensuring that the output meets organizational standards and user requirements. Together, these topics provide a thorough understanding of how to effectively manage and utilize computerized database systems in various professional settings.