Optimising databases with indexing, maintaining data integrity, and securing databases with access rights, encryption and backup, including concurrency in multi-user systems.

What this dot point is asking

CCEA wants you to explain how a database is optimised (indexing), how data integrity is maintained, and how a database is secured (access rights, encryption, backup), including how concurrent access by many users is controlled. A real information system must be fast, accurate and secure for many users at once.

Optimising with indexing

Indexing a field that is frequently searched or sorted greatly speeds up those queries. The cost is extra storage and slower writes, because every insert, update or delete must also update the index. So you index the fields that queries use most, not every field.

Maintaining data integrity

Referential integrity is examined specifically: it prevents "orphan" records, for example a booking whose student has been deleted, by enforcing that foreign-key values always match an existing primary key.

Securing the database

A database holds valuable and often personal data, so it must be secured:

• Access rights (permissions). Each user is granted only the rights they need, for example read-only for some staff and read-write for others, limiting what a compromised or careless account can do.
• Encryption. Data is encrypted so that, if it is stolen or intercepted, it cannot be read without the key.
• Backup and recovery. Regular backups (full and incremental) let the database be restored after hardware failure, corruption, ransomware or accidental deletion.
• Authentication. Strong passwords and, increasingly, multi-factor authentication confirm who is accessing the data.

Concurrency in multi-user systems

Without control, two users editing the same record can cause the lost update problem, where one update overwrites the other. Locking serialises the conflicting updates; the trade-off is that users may have to wait, and badly managed locks can cause a deadlock.

Why this matters

A database that is slow, inaccurate or insecure fails its users and may breach data protection law. Indexing keeps it responsive, integrity rules keep it correct, and security and concurrency control keep it safe and reliable for many users, which is exactly what an operational information system requires.

Try this

Q1. State one benefit and one drawback of adding an index to a field. [2 marks]

• Cue. Benefit: faster searching and sorting on that field. Drawback: extra storage and slower inserts, updates and deletes because the index must be maintained.

Q2. Explain what referential integrity prevents. [2 marks]

• Cue. It ensures a foreign key always references an existing primary key, preventing orphan records such as an order linked to a customer that does not exist.

Q3. State one security measure that makes stolen data unreadable. [1 mark]

• Cue. Encryption.