What is a DBMS?

A Database Management System (DBMS) is software that provides an interface between users/applications and a database. Users never interact with the raw data files directly — all access goes through the DBMS. Examples: MySQL, PostgreSQL, Microsoft SQL Server, Oracle, SQLite.

A DBMS provides controlled, organised access to stored data while ensuring security, integrity, and efficiency. Without a DBMS, each application would need to manage its own files, leading to data inconsistency and duplication.

Functions of a DBMS

• Data storage management: organises how data is physically stored on disk; manages indexes for fast retrieval.
• Data retrieval: accepts SQL queries and returns the requested data efficiently.
• Access control / security: manages user accounts and permissions — different users can have different levels of access (read-only, write, admin). Protects sensitive data from unauthorised access.
• Data integrity enforcement: enforces constraints (primary key, foreign key, NOT NULL, UNIQUE) to ensure data remains valid and consistent. Prevents invalid data from being entered.
• Concurrency control: manages simultaneous access by multiple users to prevent data corruption. Uses locking mechanisms to ensure transactions do not interfere with each other.
• Backup and recovery: automatically backs up data and can restore the database to a consistent state after a crash or failure.
• Transaction management: ensures that groups of operations (transactions) are completed fully or not at all — preventing partial updates that would leave the database in an inconsistent state.

The ACID Properties of Transactions

A transaction is a logical unit of work — a group of one or more SQL operations that must be treated as a single, indivisible operation. For example, transferring money between two bank accounts: debit one, credit another — both must succeed or neither should.

The DBMS guarantees the ACID properties for every transaction:

Concurrency Control

When multiple users access a database simultaneously, problems can occur without concurrency control:

• Lost update: two users read the same value, both modify it, and the second write overwrites the first — one update is lost.
• Dirty read: one transaction reads data that another uncommitted transaction has written — if the second transaction is rolled back, the first read invalid data.
• Incorrect summary: one transaction reads data while another is in the middle of updating it — the summary includes some but not all updates.

The main mechanism used to prevent these is record locking: when a transaction accesses data, it places a lock on it so no other transaction can modify the same data simultaneously. Deadlock can occur if two transactions each hold a lock the other needs — the DBMS must detect and break deadlocks.

Database Schemas

A schema defines the structure of the database — the tables, their columns, data types, and constraints. The schema describes the database's logical structure independently of the data it contains.

In a three-level ANSI/SPARC architecture:

• External schema (view): the user's view of the data — only the subset they are permitted to see (e.g. a HR user sees employee salary but not performance reviews).
• Conceptual schema: the logical structure of the whole database — tables, columns, keys, relationships.
• Internal schema: how data is physically stored on disk — indexes, file structures.

This separation allows the physical storage to be changed without affecting the logical structure, and the logical structure to change without affecting users' views — known as data independence.

Advantages of Using a DBMS

• Reduces data redundancy through normalisation and centralised storage.
• Enforces data integrity via constraints.
• Provides access control and security — user roles and permissions.
• Supports multi-user concurrent access safely.
• Provides backup and recovery mechanisms.
• Data independence — applications are insulated from changes to the physical storage.

Disadvantages of a DBMS

• High cost of software licences (enterprise DBMS can be very expensive).
• Complex to set up, maintain, and administer — requires specialist knowledge.
• Can be slower than direct file access for simple tasks due to overhead.
• A single point of failure — if the DBMS fails, all applications lose access to data.