Introduction

Relational database is a type of database management system that is designed to work with tables consisting of rows and columns. Each row represents a unique record and each column represents a specific attribute of that record. One of the key features of relational databases is the ability to establish relationships between tables using keys. Each table has a primary key column, which is a unique identifier for each record, and other tables can use these primary keys as foreign keys to establish relationships with the original tables.

Consider a database that tracks students’ enrolment in courses. The Students table contains the list of all students each identified by a unique id (Id column, primary key). The StudentCourses table could use its StudentId column as a foreign key to establish a relationship with Students tables’ Id column. We can now retrieve the details of a student and the courses he has enrolled in, by writing a join query.

ACID properties

ACID (Atomicity, Consistency, Isolation and Durability) is an abstraction that simplifies complex data interactions and hides anomalies such as dirty reads, dirty writes, read skew, write skew, lost updates and phantom reads behind a simple transaction abort. This ensures high level of data integrity and consistency.

1. Atomicity: A transaction is called atomic when either all the statements within a transaction are executed successfully or none of the statements will execute. If a statement within a transaction fails, it should be aborted and rolled back.
2. Consistency: This property ensures that the database is in a consistent state at all times and that it will remain consistent after each transaction. For example, if multiple users want to view a record, it should always provide the same result.
3. Isolation: This property ensures that each transaction is isolated from other transactions and they do not interfere with each other. That indicates that the transaction sees the database as it was before any other transaction started or after it has committed. This avoids problems like data corruption or inconsistencies caused by multiple transactions accessing and modifying the same data at the same time.
4. Durability: This property ensures that once the transaction is committed, the changes made to the database are permanent and will survive any subsequent failures or crashes. It means that the modifications made during the transaction are preserved in a durable storage medium, such as hard disk or SSD, and can be recovered even if the system fails due to power loss or other cause.

These properties, when combined, ensure that a relational database is reliable, consistent and robust, which is crucial for applications that require high levels of data integrity and availability.

Benefits

1. Flexibility: DDL (Data Definition Language) in SQL allows us to modify the tables, columns and other changes while still keeping the database servers running. This enables for more adaptability to changing business requirements.
2. Reduced redundancy: In relational databases, we can utilise a method called normalisation to eliminate data redundancy by maintaining information related to a specific entity in one table and relevant data to that entity in another table linked by foreign keys.
3. Concurrency: To manage concurrency, database management system must ensure that the transactions are isolated from one another and that they execute in such a way that the database integrity is maintained. For example, two users attempting to book the same seat of a bus would be handled in such a way that it is assigned to one user and fails for other.

Drawbacks

1. Unstructured data: Relational databases are not suitable for those applications that has large volume of semi-structured/unstructured data, low latency and flexible data models.