Databases and Database Management
Data
Databases and Database Management
Syllabus tag: KASNEB CPA | Foundation Level | CA16 Information Communication Technology | Topic 4 Databases and Database Management
Lesson objectives
By the end of this topic, you will be able to:
- Explain the problems of file-based data storage
- Describe the relational model and its terminology
- Explain primary, foreign and composite keys
- Describe normalisation and why it matters
- State the functions of a database management system
Why this matters
Every accounting system sits on a database. Understanding how one is structured explains why some reports are easy to produce and others are not — and why data entered once in the right place appears correctly everywhere.
The problem with files
Before databases, each department kept its own files. Three problems followed:
Data redundancy. The same customer address stored in sales, credit control and despatch.
Data inconsistency. The address changes and only two of the three are updated. Which is right? Nothing in the system can say.
Data isolation. Answering "which customers bought product X and are overdue" requires joining files that were never designed to be joined.
A database solves all three by storing each fact once, in one place, with every application drawing on the same store.
The relational model
Data is held in tables (relations):
| Term | Meaning |
|---|---|
| Table / relation | A set of records about one kind of thing |
| Row / record / tuple | One instance — one customer, one invoice |
| Column / field / attribute | One property — name, credit limit |
| Primary key | A field uniquely identifying each row |
| Foreign key | A field referring to the primary key of another table |
Primary keys must be unique and never null. A customer number serves; a customer name does not, since two customers may share one.
Foreign keys create the relationships. An invoice table holds a customer number as a foreign key, linking each invoice to exactly one customer. That single field is what allows the two tables to be joined.
Referential integrity is the rule that a foreign key must match an existing primary key — so an invoice cannot exist for a customer who does not. It is the database enforcing what the business already knows.
A composite key uses two or more fields together where no single field is unique. An order-line table keyed on order number and line number is the standard case.
Normalisation
Organising data to eliminate redundancy and the anomalies it causes.
First normal form (1NF) — no repeating groups; each cell holds a single value. Second normal form (2NF) — in 1NF, and every non-key field depends on the whole key. Third normal form (3NF) — in 2NF, and no non-key field depends on another non-key field.
Why it matters is easier seen through what goes wrong without it:
- Update anomaly — a supplier's address stored on every invoice line must be changed in a hundred places, and one will be missed
- Insertion anomaly — a new product cannot be recorded until someone orders it, because product details live only in the order table
- Deletion anomaly — deleting the last order for a customer erases the customer entirely
Normalisation is not tidiness. Each of those anomalies is a way of losing or corrupting data, and the accountant meets the result as a figure that cannot be reconciled.
:::checkpoint A sales table stores customer name and address alongside every transaction. A customer relocates. Identify the anomaly this creates and explain what a normalised design would do instead. :::
The database management system
The DBMS is the software between users and the stored data. Its functions:
- Data definition — creating and altering the structure
- Data manipulation — inserting, updating, deleting, querying
- Access control — who may see and change what
- Concurrency control — managing simultaneous users so two updates do not conflict
- Backup and recovery
- Integrity enforcement — applying the rules automatically
Concurrency control is worth a moment. Where two users read a balance of 100, each adds 50, and both write back, the result is 150 rather than 200. The DBMS prevents this by locking — and the problem is invisible until it produces an unexplained difference in an account.
SQL is the standard language: SELECT to query, INSERT, UPDATE and DELETE to change, CREATE and ALTER for structure.
Advantages and drawbacks
Advantages: data stored once; consistency; shared access; central security; independence of data from the programs using it.
Drawbacks: cost and complexity; specialist staff; and a single point of failure — the concentration that produces the benefits also means everything stops if the database does. This is precisely why backup and recovery matter more in a database environment than in a file-based one.
:::checkpoint A finance director asks why the company cannot simply keep departmental spreadsheets, which are cheaper and need no specialists. Set out the two most serious risks, using the terminology of this topic. :::