Databases

The two primary paradigms for storing data are Relational (SQL) and Non-Relational (NoSQL). Choosing between them depends entirely on the structure of your data and how it needs to scale.

1.1 Relational Databases (SQL)

Data is stored in highly structured Tables (Relations) consisting of rows (records) and columns (attributes). Tables are strictly linked together using Primary and Foreign Keys to form complex relationships. Examples: MySQL, PostgreSQL, Oracle, Microsoft SQL Server.

• Rigid Schema: You must define exactly what data type (integer, string, date) goes into each column before inserting any data. If you want to add a new attribute (like "MiddleName") later, you must alter the entire table structure.

• ACID Compliance: Guarantees strong data integrity and absolute reliability for complex, multi-step transactions (like financial bank transfers).

• Vertical Scaling (Scale-Up): Primarily scales by increasing the horsepower of a single server (buying a bigger, more expensive server with more RAM/CPU). Distributing a relational database across many servers is complex.

1.2 Non-Relational Databases (NoSQL)

Data is stored in flexible formats, often as JSON-like documents, key-value pairs, wide-columns, or graph nodes, rather than strict tables. Examples: MongoDB (Document), Redis (Key-Value), Cassandra (Wide-Column), Neo4j (Graph).

• Dynamic Schema: Schema-less design. You can insert varied data structures into the same collection without altering the database. One user document might have a "phone_number" field, while another does not.

• Horizontal Scaling (Scale-Out): Designed from the ground up to scale horizontally by adding cheaper, commodity servers to distribute the load across a massive cluster. Perfect for massive, unstructured Big Data and real-time web apps.

• BASE Properties: Often trades strict ACID consistency for higher availability and performance (Basically Available, Soft state, Eventual consistency).

Relational databases rely on specific keys and constraints to mathematically organize and link data across multiple tables.

Key Types:

• Primary Key (PK): A column (or combination of columns) that uniquely identifies every single row in a table. It must be unique and cannot be NULL. (e.g., A StudentID, a SocialSecurityNumber, or an auto-incrementing integer ID).

• Foreign Key (FK): A column in one table that specifically references the Primary Key of another table. This creates the relational link between the tables, enforcing referential integrity. (e.g., An Orders table has a CustomerID column linking back to the Customers table).

• Composite Key: A Primary Key that consists of two or more columns combined to create a unique identifier (often used in linking tables for Many-to-Many relationships).

Common Database Constraints:

• UNIQUE: Ensures all values in a column are different.
• NOT NULL: Ensures a column cannot have a NULL value.
• CHECK: Ensures values in a column satisfy a specific condition.
• DEFAULT: Provides a default value for a column when none is specified.

Relational DBMS systems guarantee reliability even during power failures, system crashes, or massive concurrent access by enforcing four strict rules known as the ACID properties for every database transaction.

• Atomicity ("All or Nothing"): A transaction must either complete entirely, or not happen at all. If a server crashes halfway through a bank transfer, the database entirely "rolls back" the partial changes to the exact starting state.

• Consistency: A transaction can only bring the database from one mathematically valid state to another valid state. It must obey all predefined rules, constraints (like NOT NULL), and cascading triggers.

• Isolation: Concurrent executions of transactions by hundreds of users must leave the database in the exact same state as if they were executed sequentially (one after the other). This prevents "dirty reads" where two users withdraw from the same account simultaneously, corrupting the final balance.

• Durability: Once a transaction has been successfully committed, the changes are permanently stored on non-volatile disk. They will survive a catastrophic system crash or power outage immediately afterward. This is heavily reliant on Transaction Logs, where the database records changes before writing them to the main tables, allowing recovery upon a crash.

SQL is the standard programming language used to communicate with, manage, and query Relational Databases.

• SELECT (Read): Retrieves data from a database. (e.g., SELECT FirstName, LastName FROM Users WHERE Age > 18 ORDER BY LastName;)

• INSERT (Create): Adds new rows (records) into a table.

• UPDATE (Update): Modifies existing data within specific rows based on a WHERE condition.

• DELETE (Delete): Removes specific rows from a table based on a WHERE condition.

• JOIN: The most powerful feature of SQL. It dynamically combines rows from two or more separate tables based on a related column (usually Primary/Foreign keys) to return a unified dataset.

An Index is a specialized data structure (often a B-Tree) created on a database column that drastically improves the speed of data retrieval operations (SELECTs), at the cost of slower writes (INSERTs/UPDATEs) and increased storage space.