Overview

Relational databases (RDBMS) store data in structured tables with predefined schemas. They are based on the relational model of data and use Structured Query Language (SQL) for data manipulation and retrieval.

Core Concepts

• Tables, Rows, and Columns:
  • Table: A collection of related data (e.g., Users).
  • Row (Tuple): A single record in a table.
  • Column (Attribute): A specific field in a record.
• ACID Properties: Ensuring reliability in database transactions.
  • Atomicity: All or nothing. If one part of a transaction fails, the whole thing is rolled back.
  • Consistency: A transaction transforms the database from one valid state to another.
  • Isolation: Concurrent transactions do not interfere with each other.
  • Durability: Once committed, the data survives system failures.
• Normalization: The process of organizing data to reduce redundancy and improve data integrity (1NF, 2NF, 3NF).
• Relationships:
  • One-to-One: One user has one profile.
  • One-to-Many: One user has many posts.
  • Many-to-Many: Many students enroll in many courses (requires a join table).

Code Examples

-- Create a table
CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    Username VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE
);

-- Query with a Join
SELECT Users.Username, Posts.Title 
FROM Users 
JOIN Posts ON Users.UserID = Posts.UserID 
WHERE Users.UserID = 1;

Use Cases

• Financial Systems: Where ACID compliance and data integrity are non-negotiable.
• Structured Data: Applications where the data model is stable and well-defined.
• Enterprise Applications: Systems requiring complex queries across multiple entities.

Gotchas

• Performance Degradation: As tables grow to millions of rows, complex joins can become very slow.
• Schema Rigidity: Changing the schema of a large table in production can be risky and slow (migration pain).

Related Notes

• Indexing
• Transactions & Concurrency Control
• NoSQL Databases