DBMS — Flat File Concurrency Causes Two-Day Data Loss

-- ============================================================
-- DEMO: What a DBMS lets you do in just a few lines
-- Run this in any SQL environment (MySQL, PostgreSQL, SQLite)
-- ============================================================

-- STEP 1: Create a structured container for data (a table)
-- The DBMS enforces that every row must match this structure
CREATE TABLE students (
    student_id   INT PRIMARY KEY,       -- unique identifier, DBMS prevents duplicates
    full_name    VARCHAR(100) NOT NULL, -- cannot be left blank — DBMS enforces this
    age          INT,
    course       VARCHAR(50)
);

-- STEP 2: Insert some records — the DBMS stores these safely on disk
INSERT INTO students VALUES (1, 'Aisha Patel',    21, 'Computer Science');
INSERT INTO students VALUES (2, 'Liam O Brien',   19, 'Mathematics');
INSERT INTO students VALUES (3, 'Sofia Reyes',    22, 'Computer Science');
INSERT INTO students VALUES (4, 'James Okafor',   20, 'Physics');

-- STEP 3: Query — find only Computer Science students
-- The DBMS scans, filters, and returns exactly what you asked for
SELECT full_name, age
FROM   students
WHERE  course = 'Computer Science'
ORDER BY age ASC; -- youngest first

-- STEP 4: Update a record — DBMS ensures the change is atomic
-- Either the whole update succeeds or nothing changes. No half-updates.
UPDATE students
SET    age = 22
WHERE  student_id = 2; -- only Liam's row changes, nobody else's

-- STEP 5: Delete a record cleanly
DELETE FROM students
WHERE  student_id = 4; -- removes James, all other rows untouched

-- ============================================================
-- DEMO: Transactions — the Transaction Manager in action
-- This shows the 'all or nothing' guarantee a DBMS provides
-- Scenario: transferring £500 from Alice's account to Bob's
-- ============================================================

-- Setup: create a simple bank accounts table
CREATE TABLE bank_accounts (
    account_id   INT PRIMARY KEY,
    holder_name  VARCHAR(100),
    balance      DECIMAL(10, 2)  -- store money with 2 decimal places
);

INSERT INTO bank_accounts VALUES (101, 'Alice Nguyen', 1200.00);
INSERT INTO bank_accounts VALUES (102, 'Bob Martins',   300.00);

-- BEGIN TRANSACTION tells the DBMS: 'treat everything until COMMIT as one unit'
BEGIN TRANSACTION;

    -- Operation 1: deduct from Alice
    UPDATE bank_accounts
    SET    balance = balance - 500.00
    WHERE  account_id = 101;

    -- Operation 2: add to Bob
    UPDATE bank_accounts
    SET    balance = balance + 500.00
    WHERE  account_id = 102;

    -- Both operations succeeded — make the changes permanent
COMMIT;

-- Now check the result
SELECT holder_name, balance FROM bank_accounts;

-- ============================================================
-- WHAT HAPPENS IF SOMETHING GOES WRONG MID-TRANSFER?
-- ============================================================
BEGIN TRANSACTION;

    UPDATE bank_accounts
    SET    balance = balance - 500.00
    WHERE  account_id = 101; -- Alice's money is deducted...

    -- Imagine the server crashes HERE before Bob gets credited.
    -- Because we haven't COMMITted, the DBMS ROLLS BACK automatically.
    -- Alice keeps her £500. Bob gets nothing. No money disappears.

ROLLBACK; -- manually trigger rollback to demonstrate the concept

// ============================================================
// Comparing how RDBMS and NoSQL store the SAME data
// Scenario: storing a product in an e-commerce app
// ============================================================

// --- RDBMS approach: data split across two linked tables ---
// TABLE: products
// | product_id | name          | price  | category_id |
// |------------|---------------|-------|-------------|
// | 501        | Wireless Mouse|  29.99 | 3           |

// TABLE: categories
// | category_id | category_name |
// |-------------|---------------|
// | 3           | Electronics   |

// To get full info you JOIN the two tables:
// SELECT p.name, p.price, c.category_name
// FROM products p JOIN categories c ON p.category_id = c.category_id
// WHERE p.product_id = 501;

// --- NoSQL (MongoDB) approach: everything in ONE document ---
// No joining needed — the document is self-contained
{
  "_id": "501",
  "name": "Wireless Mouse",
  "price": 29.99,
  "category": "Electronics",

  // NoSQL shines here: attributes can vary per product
  // A book product would have 'author' and 'isbn' instead
  "specs": {
    "dpi": 1600,
    "connectivity": "Bluetooth",
    "battery_life_months": 12
  },

  // Arrays are first-class citizens in NoSQL
  "tags": ["wireless", "ergonomic", "office"]
}

-- ============================================================
-- DEMO: The major SQL sublanguages in action
-- ============================================================

-- DDL: Creating a table (structure)
CREATE TABLE employees (
    id       INT PRIMARY KEY,
    name     VARCHAR(100),
    dept_id  INT,
    salary   DECIMAL(10,2)
);

-- DML: Insert data
INSERT INTO employees VALUES (1, 'Maria Lopez', 10, 65000.00);
INSERT INTO employees VALUES (2, 'John Smith',  20, 72000.00);

-- DML: Query
SELECT name, salary FROM employees WHERE dept_id = 10;

-- DCL (conceptual — exact syntax varies):
-- GRANT SELECT ON employees TO analytics_app;

-- TCL: Transaction wrapping an update
BEGIN;
UPDATE employees SET salary = salary * 1.05 WHERE dept_id = 10;
COMMIT;

-- The DBMS ensures either all rows are updated or none are.

-- ============================================================
-- DEMO: MVCC snapshot isolation in PostgreSQL
-- Run two sessions to see how reads don't block writes
-- ============================================================

-- Session A (User 1):
BEGIN;
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 101;
-- At this point, Session A holds a write lock on that row.
-- Session B can still READ the old value because MVCC provides a snapshot.

-- Session B (User 2):
SELECT quantity FROM inventory WHERE product_id = 101;
-- Returns the old value before Session A's update, because Session B sees
-- the snapshot before Session A committed.

-- Session A:
COMMIT;  -- Now Session B's next SELECT will see the new value.

-- ============================================================
-- DEMO: Write-ahead log recovery concept
-- ============================================================
-- The following is conceptual; actual log internals are binary.
-- But the principle: before any data page is written, the log entry
-- is flushed to disk.

-- Log entry (logical):
-- LSN: 12345
-- Transaction ID: 789
-- Operation: UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 101
-- Old value: 100, New value: 99

-- On crash, the DBMS reads the log from last checkpoint.
-- If transaction 789 committed, it redoes the change.
-- If it didn't commit, it undoes (rolls back) the change.
-- No ambiguity. No corruption.