Relational Databases

A relational database organises data into tables (rows and columns) with relationships between them.

Key Components:

• Rows (Records): Each row represents a single entry

• Columns (Fields): Each column represents a specific attribute

• Primary Key: Uniquely identifies each row

• Foreign Key: Links two tables together

ACID Properties

A - Atomicity (All or Nothing)

A transaction either completes fully or doesn't happen at all. There's no partial completion.

C - Consistency (Data Stays Valid)

Database remains in a valid state before and after a transaction. All rules and constraints are maintained.

I - Isolation (Transactions Don't Interfere)

Multiple transactions can occur concurrently, but they should behave as if they're running one after another. Each transaction is isolated from others until it completes.

Isolation Levels

Choosing the Right Isolation Level:

• Higher isolation = Better consistency but slower performance

• Lower isolation = Faster performance but potential data inconsistencies

• Most databases default to Read Committed as a good balance

• Use Serializable for critical financial transactions

D - Durability (Data Persists)

Once a transaction is committed, the data is permanently saved and will survive system crashes, power failures, or any other system failures. The changes are written to non-volatile storage.

How Durability is Achieved:

Database Indexing

An index is a data structure that improves the speed of data retrieval operations on a database table. Think of it like an index in a book - instead of reading every page to find a topic, you check the index and jump directly to the right page.

B-Trees vs B+ Trees

Both are self-balancing tree data structures commonly used for database indexing. Let's understand each:

B-Tree (Balanced Tree)

A B-tree stores data (or pointers to data) in both internal nodes and leaf nodes. All nodes can contain actual records

B+ Tree (Enhanced B-Tree)

A B+ tree stores data ONLY in leaf nodes. Internal nodes contain only keys for navigation. Additionally, all leaf nodes are linked together, forming a linked list.

Key Differences

Drawbacks of Relational Databases

• Rigid Schema: Structure is fixed. Changing it requires migrating all existing data, which can be time-consuming and risky.

• Horizontal Scaling Challenges: Difficult to distribute data across multiple servers. Vertical scaling (bigger hardware) has limits.

• Complex JOIN Performance: Multiple table joins can be slow on large datasets, especially with poor indexing.

• Not Ideal for Unstructured Data: JSON documents, images, videos, and variable schema data are awkward to store.

• ACID Overhead: Maintaining ACID properties can impact performance, especially in distributed systems.