Describe the features of a relational database that address the limitations of a file-based approach

8.1 Database Concepts

Objective

Describe the features of a relational database that address the limitations of a file‑based approach.

1. Limitations of a File‑Based System

• Data Redundancy and Inconsistency – The same piece of information is stored in multiple files, leading to wasted space and contradictory values.
• Lack of Data Independence – Any change to the file structure (e.g., adding a field) requires changes to every program that accesses the file.
• Limited Query Capability – Programs must contain procedural code to locate and combine data; ad‑hoc queries are difficult.
• Poor Data Integrity – No built‑in mechanisms to enforce rules such as uniqueness, referential integrity, or domain constraints.
• Concurrency Problems – Simultaneous access by multiple users can cause lost updates or corrupted files.
• Security and Access Control – File systems provide only coarse‑grained protection; fine‑grained permissions are not supported.

2. Relational Database Features that Overcome These Limitations

2.1 Data Independence

Relational DBMS separate the logical schema (tables, relationships) from the physical storage. Changes to the physical layout do not affect application programs, providing both logical and physical data independence.

2.2 Reduced Redundancy & Normalisation

Data is stored once in a table; related data is linked via keys. Normalisation (1NF, 2NF, 3NF, BCNF) systematically removes redundancy.

2.3 Integrity Constraints

• Domain constraints – enforce valid data types and ranges.
• Primary key constraints – guarantee each row is uniquely identifiable.
• Foreign key constraints – enforce referential integrity between tables.
• Unique, NOT NULL, CHECK constraints – provide additional rule enforcement.

2.4 Powerful Query Language (SQL)

SQL allows declarative, ad‑hoc queries without writing procedural code. Example of a relational algebra expression rendered in LaTeX:

\$\sigma{Dept = \text{'CS'}}(Employee) \;\bowtie\; \sigma{Location = \text{'London'}}(Department)\$

SQL equivalent:

SELECT e.Name, d.Location
FROM Employee e
JOIN Department d ON e.DeptID = d.DeptID
WHERE d.Location = 'London';

2.5 Concurrency Control & Transaction Management

• ACID properties (Atomicity, Consistency, Isolation, Durability) ensure reliable multi‑user access.
• Locking protocols (two‑phase locking) and timestamp ordering prevent lost updates and dirty reads.

2.6 Security and Access Control

• Granular privileges: SELECT, INSERT, UPDATE, DELETE can be granted per user or role.
• Views can expose only a subset of data, providing logical security.

2.7 Scalability and Performance Optimisation

• Indexes accelerate search on large tables.
• Query optimiser rewrites queries for efficient execution plans.
• Partitioning and clustering distribute data across multiple disks or servers.

3. Comparison Table: File‑Based vs Relational DBMS

4. Summary

A relational database addresses the core shortcomings of a file‑based approach by providing a structured, mathematically sound model that supports data independence, integrity, powerful querying, robust concurrency control, fine‑grained security, and scalability. These features enable reliable, efficient, and maintainable data management for complex applications.