🗄️ Data Collection & DBMS — Complete Revision Notes

Before databases existed, every application stored its data in its own flat files (think .txt or .csv files stored on disk). Each program read and wrote its own files, with no coordination between programs.

A Database Management System (DBMS) is software that manages a structured collection of data. It acts as an interface between the user/application and the physical data stored on disk. The DBMS takes care of storage, security, concurrency, recovery, and integrity — so you don't have to.

In an RDBMS (Relational Database Management System), all data lives in tables — rows (tuples) and columns (attributes). Tables relate to each other through keys. You query data using SQL. E.F. Codd invented this model in 1970 at IBM.

E.F. Codd published 12 rules in 1985 to define what a "truly relational" database must do. Think of them as the constitution of RDBMS. Most commercial databases satisfy most but not necessarily all 12 rules.

When you install Oracle (or any enterprise RDBMS), data is stored in a hierarchy of logical and physical structures. Understanding this helps you as a DBA (Database Administrator) tune performance and plan storage.

The world generates different types of data. Understanding the difference determines which storage system you should use for each type.

Before any analysis or storage, data must be collected. As a data professional, you need to know how data enters your pipeline from various sources.

SQL (Structured Query Language) is the standard language used to interact with relational databases. It was developed in the 1970s by IBM and standardized by ANSI/ISO. All major RDBMS (MySQL, Oracle, PostgreSQL, SQL Server) support SQL with minor dialect differences.

DDL commands define the structure of the database — they create, modify, or delete database objects like tables, indexes, views. DDL commands are auto-committed (cannot be rolled back in most databases).

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  CREATE TABLE — Define a new table
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
CREATE TABLE Books (
  book_id   INT           NOT NULL,    -- no null allowed
  name      VARCHAR(100) NOT NULL,
  author    VARCHAR(60),               -- nullable
  price     DECIMAL(8,2) DEFAULT 0.00, -- default value
  genre     VARCHAR(30),
  added_on  DATE          DEFAULT CURDATE(),
  PRIMARY KEY (book_id),               -- uniquely identifies row
  CHECK (price >= 0)                   -- price can't be negative
);

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  ALTER TABLE — Modify existing table structure
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
ALTER TABLE Books ADD COLUMN    publisher  VARCHAR(80);   -- add column
ALTER TABLE Books MODIFY COLUMN name       VARCHAR(150); -- change type
ALTER TABLE Books DROP COLUMN   publisher;                -- remove column
ALTER TABLE Books RENAME TO    Library_Books;            -- rename table

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  DROP vs TRUNCATE — both delete all data
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
TRUNCATE TABLE Books;  -- removes all rows, keeps table structure (fast)
DROP TABLE    Books;   -- removes ENTIRE table + structure (gone forever)

DML commands work on the data inside the tables. They can be rolled back (unlike DDL). These are the commands you'll use most often day-to-day.

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  INSERT — Add new rows
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-- Method 1: Specify all columns in order
INSERT INTO Books VALUES
  (1, 'Clean Code', 'Robert Martin', 499.00, 'Programming', '2024-01-15');

-- Method 2: Specify column names (recommended — safe against schema changes)
INSERT INTO Books (book_id, name, author, price)
VALUES (2, 'The Pragmatic Programmer', 'Hunt', 549.00);

-- Method 3: Multiple rows at once (faster than individual inserts)
INSERT INTO Books (book_id, name, price) VALUES
  (3, 'Design Patterns', 699.00),
  (4, 'Refactoring',      599.00),
  (5, 'SICP',             350.00);

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  SELECT — Query / Read data
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-- Basic select
SELECT * FROM Books;                        -- all columns, all rows
SELECT name, price FROM Books;             -- specific columns only
SELECT DISTINCT genre FROM Books;          -- unique genres only

-- Filtering with WHERE
SELECT * FROM Books WHERE price > 400;
SELECT * FROM Books WHERE genre = 'Programming' AND price < 600;
SELECT * FROM Books WHERE name LIKE '%Code%'; -- contains 'Code'
SELECT * FROM Books WHERE price BETWEEN 300 AND 600;
SELECT * FROM Books WHERE author IN ('Martin', 'Hunt');
SELECT * FROM Books WHERE publisher IS NULL;  -- find NULL values

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  UPDATE — Modify existing rows
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
UPDATE Books SET price = 449.00 WHERE book_id = 1;  -- update one row
UPDATE Books SET price = price * 0.9               -- 10% discount to all
WHERE genre = 'Programming';

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  DELETE — Remove rows (rollbackable unlike TRUNCATE)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
DELETE FROM Books WHERE book_id = 5;       -- delete one specific row
DELETE FROM Books WHERE price < 100;       -- delete rows matching condition

DCL manages WHO can do WHAT in the database. In organizations, the DBA creates user accounts and assigns only the necessary permissions (principle of least privilege).

-- Step 1: Create a new DB user
CREATE USER 'dbda_student'@'localhost' IDENTIFIED BY 'secure_pass_2024';

-- Step 2: Grant specific permissions
GRANT SELECT, INSERT ON college_db.* TO 'dbda_student'@'localhost';
GRANT ALL PRIVILEGES ON test_db.* TO 'dbda_student'@'localhost';

-- Step 3: Make permissions take effect
FLUSH PRIVILEGES;

-- Step 4: Revoke permissions when no longer needed
REVOKE DELETE ON college_db.Books FROM 'dbda_student'@'localhost';

-- Check what permissions a user has
SHOW GRANTS FOR 'dbda_student'@'localhost';

Constraints are rules applied on table columns to ensure data quality. They are enforced automatically by the DBMS — no need to write application logic for them.

-- Transactions ensure ACID compliance
-- BEGIN starts a transaction block
START TRANSACTION;
  UPDATE accounts SET balance = balance - 5000 WHERE acc_no = 'A001';
  UPDATE accounts SET balance = balance + 5000 WHERE acc_no = 'B002';
COMMIT;    -- both updates succeed → save permanently

-- If something goes wrong between the two updates:
ROLLBACK;  -- undo everything → no money lost, no money gained

SAVEPOINT sp1;   -- create a checkpoint mid-transaction
ROLLBACK TO sp1; -- undo back to checkpoint, not all the way

GROUP BY collapses multiple rows with the same value in a column into a single summary row. It's used with aggregate functions like COUNT, SUM, AVG, MIN, MAX.

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  EMPLOYEES table (what we're working with)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  emp_id | name    | dept_id | salary | city
  -------|---------|---------|--------|-------
  1      | Priya   | 10      | 60000  | Mumbai
  2      | Rahul   | 10      | 55000  | Pune
  3      | Anita   | 20      | 70000  | Mumbai
  4      | Vikram  | 20      | 80000  | Delhi
  5      | Sneha   | 30      | 45000  | Pune
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

-- Q: How many employees in each department?
SELECT dept_id, COUNT(*) AS headcount
FROM employees
GROUP BY dept_id;
-- Result: dept 10→2, dept 20→2, dept 30→1

-- Q: Departments with more than 1 employee AND avg salary above 50000
SELECT dept_id,
       COUNT(*) AS headcount,
       AVG(salary) AS avg_salary,
       MAX(salary) AS highest_sal,
       MIN(salary) AS lowest_sal,
       SUM(salary) AS total_payroll
FROM employees
WHERE city != 'Delhi'    -- filter BEFORE grouping (Delhi excluded)
GROUP BY dept_id
HAVING COUNT(*) > 1      -- filter AFTER grouping (keep dept with 2+ people)
  AND AVG(salary) > 50000
ORDER BY avg_salary DESC; -- sort final output

A subquery is a complete SELECT statement nested inside another SQL statement. The inner query runs first, its result is passed to the outer query. Think of it as solving a maths problem step by step.

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  1. Simple Subquery (runs once, independent)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-- Find all employees earning above company average
SELECT name, salary
FROM employees
WHERE salary > (
  SELECT AVG(salary) FROM employees  ← runs once, returns 62000
);
-- The inner query returns 62000, outer becomes: WHERE salary > 62000

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  2. Correlated Subquery (runs once PER ROW of outer query)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-- Find employees earning MORE than their OWN department's average
-- This is correlated because it references e1.dept_id from outer query
SELECT e1.name, e1.salary, e1.dept_id
FROM  employees e1
WHERE e1.salary > (
  SELECT AVG(e2.salary)
  FROM  employees e2
  WHERE e2.dept_id = e1.dept_id  ← uses outer query's dept_id!
);
-- For Priya (dept 10): inner query calculates avg of dept 10 = 57500
-- For Anita (dept 20): inner query calculates avg of dept 20 = 75000
-- Each row gets its OWN calculation — hence "correlated"

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  3. Subquery with EXISTS (check if related rows exist)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-- Find all departments that HAVE at least one employee
SELECT d.dept_name
FROM departments d
WHERE EXISTS (
  SELECT 1 FROM employees e WHERE e.dept_id = d.id
);

In a normalised database, related data is split across multiple tables. JOINs bring them back together. Think of it as matching puzzle pieces — you need matching values on both sides.

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  Sample data we'll use for JOIN examples
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  EMPLOYEES:  emp_id, name, dept_id (1,2,3,4,5)
  DEPARTMENTS: id, dept_name        (10,20,30,99 ← 99 has no employees)
  SALESMAN:   id, name, customer_id, commission
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

-- INNER JOIN: Only matching rows from BOTH tables
SELECT e.name, d.dept_name, e.salary
FROM  employees e
INNER JOIN departments d ON e.dept_id = d.id;
-- Returns only employees whose dept_id exists in departments table
-- Employees with dept 99 would be excluded

-- LEFT OUTER JOIN: All employees + their dept (NULL if dept missing)
SELECT e.name, d.dept_name
FROM  employees e
LEFT JOIN departments d ON e.dept_id = d.id;
-- Returns ALL employees. If no matching dept, dept_name shows NULL

-- RIGHT OUTER JOIN: All departments + their employees (NULL if no emp)
SELECT e.name, d.dept_name
FROM  employees e
RIGHT JOIN departments d ON e.dept_id = d.id;
-- Returns ALL departments. Dept 99 appears with NULL employee name

-- FULL OUTER JOIN: Everything from both sides
SELECT e.name, d.dept_name
FROM  employees e
FULL OUTER JOIN departments d ON e.dept_id = d.id;

-- SELF JOIN: Employee-Manager hierarchy (same table joined to itself)
SELECT e.name AS employee, m.name AS manager
FROM  employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;

-- Salesperson + customer details (from syllabus lab problem)
SELECT c.cust_name, c.city, s.name AS salesman, s.commission
FROM  customers c
JOIN  salesman s ON c.salesman_id = s.id
ORDER BY s.commission DESC;

Without normalization, databases suffer from three types of anomalies that corrupt data integrity:

Rule: Every column must contain atomic (single, indivisible) values. No repeating groups. Every row must be unique.

Rule: Must be in 1NF PLUS no partial dependency. Every non-key attribute must depend on the entire primary key — not just part of it. This only matters when you have a composite primary key.

Rule: Must be in 2NF PLUS no transitive dependency. A non-key attribute should NOT depend on another non-key attribute. All non-key columns must depend DIRECTLY on the primary key.

Rule: Must be in 3NF PLUS every determinant must be a candidate key. It's a stricter version of 3NF that catches edge cases where 3NF still allows anomalies (usually involves multiple overlapping candidate keys). For most practical purposes, 3NF is sufficient.

An ER diagram is a blueprint of your database — drawn before you write a single line of SQL. It shows what entities (things) exist, what attributes they have, and how they relate to each other. Think of it as the architect's floor plan before construction begins.

A stored procedure is a named, pre-compiled block of SQL code stored in the database. Instead of sending a long SQL query from your application every time, you just call the procedure name. Think of it like a function in Python — you define it once and call it many times with different arguments.

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  Basic Stored Procedure
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
DELIMITER //
CREATE PROCEDURE GetDeptSummary(
  IN  p_dept_id   INT,           -- input parameter (caller provides)
  OUT p_emp_count INT,           -- output parameter (procedure fills)
  OUT p_avg_salary DECIMAL(10,2) -- another output
)
BEGIN
  -- Use IF-ELSE control structure
  IF p_dept_id IS NULL THEN
    SELECT COUNT(*), AVG(salary)
    INTO p_emp_count, p_avg_salary
    FROM employees;              -- all employees if dept not specified
  ELSE
    SELECT COUNT(*), AVG(salary)
    INTO p_emp_count, p_avg_salary
    FROM employees
    WHERE dept_id = p_dept_id;
  END IF;
END //
DELIMITER ;

-- Call the procedure
CALL GetDeptSummary(10, @count, @avg);
SELECT @count AS total_employees, @avg AS average_salary;

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  Procedure with CURSOR (process row by row)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
DELIMITER //
CREATE PROCEDURE GiveRaise()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE v_id INT;
  DECLARE v_salary DECIMAL(10,2);

  -- Declare cursor to loop over employees
  DECLARE emp_cursor CURSOR FOR
    SELECT emp_id, salary FROM employees;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN emp_cursor;
  read_loop: LOOP
    FETCH emp_cursor INTO v_id, v_salary;
    IF done THEN LEAVE read_loop; END IF;
    -- Give 10% raise to those earning below 50000
    IF v_salary < 50000 THEN
      UPDATE employees SET salary = v_salary * 1.10
      WHERE emp_id = v_id;
    END IF;
  END LOOP;
  CLOSE emp_cursor;
END //
DELIMITER ;

A VIEW is a saved SELECT query that looks and behaves like a table. It does NOT store data — it runs the underlying query every time you access it. Views are used to: simplify complex queries, restrict columns/rows users can see, and present data in a specific format.

-- Create a view: employees with dept name + only salary > 50000
CREATE VIEW HighEarnerDetails AS
  SELECT
    e.emp_id,
    e.name,
    e.salary,
    e.city,
    d.dept_name
  FROM employees e
  JOIN departments d ON e.dept_id = d.id
  WHERE e.salary > 50000;

-- Use the view exactly like a table
SELECT * FROM HighEarnerDetails WHERE city = 'Mumbai';
SELECT dept_name, COUNT(*) FROM HighEarnerDetails GROUP BY dept_name;

-- Update or drop a view
CREATE OR REPLACE VIEW HighEarnerDetails AS SELECT ...  -- modify
DROP VIEW HighEarnerDetails;                              -- remove

-- From syllabus: View to find employee with highest salary
CREATE VIEW TopEarnerView AS
  SELECT e.name, e.salary, e.dept_id, d.dept_name
  FROM  employees e
  JOIN  departments d ON e.dept_id = d.id
  WHERE e.salary = (SELECT MAX(salary) FROM employees);

A TRIGGER is a stored procedure that automatically executes when a specific event (INSERT, UPDATE, DELETE) occurs on a table. You don't call triggers — the database fires them. Used for: audit logging, enforcing complex business rules, maintaining derived data.

-- AFTER UPDATE trigger: audit log for salary changes
DELIMITER //
CREATE TRIGGER trg_SalaryAudit
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
  IF OLD.salary != NEW.salary THEN  -- only log if salary actually changed
    INSERT INTO salary_audit_log
      (emp_id, old_salary, new_salary, changed_by, changed_at)
    VALUES
      (NEW.emp_id, OLD.salary, NEW.salary, USER(), NOW());
  END IF;
END //
DELIMITER ;

-- BEFORE INSERT trigger: auto-uppercase the name
DELIMITER //
CREATE TRIGGER trg_FormatName
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
  SET NEW.name = UPPER(TRIM(NEW.name));  -- trim spaces + uppercase
END //
DELIMITER ;

Window functions perform calculations across a set of related rows without collapsing them. Unlike GROUP BY (which gives one row per group), window functions give you a result for EVERY row, with that row's "window" context calculated alongside. Extremely powerful for ranking, running totals, and comparisons.

-- The OVER() clause is what makes it a window function
-- PARTITION BY = which "window" each row belongs to
-- ORDER BY = how to sort within each window

SELECT
  name,
  dept_id,
  salary,
  -- Rank within department (ties get same rank, gap follows)
  RANK() OVER(PARTITION BY dept_id ORDER BY salary DESC)       AS dept_rank,
  -- Dense rank (ties get same rank, NO gap)
  DENSE_RANK() OVER(PARTITION BY dept_id ORDER BY salary DESC) AS dense_rank,
  -- Row number (unique sequential number)
  ROW_NUMBER() OVER(ORDER BY salary DESC)                       AS overall_rank,
  -- Running total of salary within dept
  SUM(salary) OVER(PARTITION BY dept_id ORDER BY salary)        AS running_total,
  -- Department total (same for all rows in that dept)
  SUM(salary) OVER(PARTITION BY dept_id)                         AS dept_total,
  -- Salary of next higher earner in same dept
  LEAD(salary, 1) OVER(PARTITION BY dept_id ORDER BY salary)   AS next_salary,
  -- Salary of previous row in same dept
  LAG(salary, 1)  OVER(PARTITION BY dept_id ORDER BY salary)   AS prev_salary
FROM employees;

-- Practical use: Get top earner per department (without subquery)
SELECT * FROM (
  SELECT name, dept_id, salary,
         RANK() OVER(PARTITION BY dept_id ORDER BY salary DESC) AS rnk
  FROM employees
) ranked
WHERE rnk = 1; -- only top earner per dept

-- Simple CASE: map specific values
SELECT name, salary,
  CASE dept_id
    WHEN 10 THEN 'Engineering'
    WHEN 20 THEN 'HR'
    WHEN 30 THEN 'Finance'
    ELSE       'Unknown'
  END AS dept_name,
  -- Searched CASE: conditions
  CASE
    WHEN salary >= 80000 THEN 'Senior'
    WHEN salary >= 50000 THEN 'Mid-level'
    ELSE                       'Junior'
  END AS level
FROM employees;

A Data Warehouse is a central repository that consolidates data from multiple OLTP systems (ERP, CRM, POS) into one place for analysis. Data is cleaned, transformed, and loaded through an ETL process.

Named because the diagram looks like a star — one central Fact table surrounded by Dimension tables. The Fact table contains measurable numbers (sales amount, quantity). Dimension tables provide context (who, what, when, where).

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  STAR SCHEMA for E-commerce Sales Analysis
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

  FACT_Sales (the star's centre)
  ├── sales_id (PK)
  ├── amount         ← measure
  ├── quantity       ← measure
  ├── discount       ← measure
  ├── date_id        → DIM_Date
  ├── product_id     → DIM_Product
  ├── customer_id    → DIM_Customer
  ├── store_id       → DIM_Store
  └── promotion_id   → DIM_Promotion

  DIM_Date          DIM_Product       DIM_Customer
  ├── date_id (PK)  ├── product_id    ├── customer_id
  ├── day           ├── name          ├── name
  ├── month         ├── category      ├── city
  ├── quarter       ├── brand         ├── segment
  └── year          └── price         └── age_group

-- Sample analytical query on Star Schema
SELECT
  d.year,
  d.quarter,
  p.category,
  c.city,
  SUM(f.amount) AS total_revenue,
  COUNT(f.sales_id) AS num_transactions,
  AVG(f.amount) AS avg_order_value
FROM  FACT_Sales f
JOIN  DIM_Date d     ON f.date_id = d.date_id
JOIN  DIM_Product p  ON f.product_id = p.product_id
JOIN  DIM_Customer c ON f.customer_id = c.customer_id
WHERE d.year IN (2023, 2024)
GROUP BY d.year, d.quarter, p.category, c.city
ORDER BY total_revenue DESC;

The CAP Theorem (Brewer's Theorem, 2000) states that in a distributed database system, you can guarantee at most 2 out of 3 properties simultaneously:

Embed related data directly inside the parent document. Best when data is: always accessed together, doesn't change often, has a 1-to-few relationship, or doesn't need independent queries.

-- Example: Blog post with embedded comments
{
  "_id": "post_001",
  "title": "Introduction to MongoDB",
  "author": "Priya Sharma",
  "published": "2024-03-15",
  "tags": ["NoSQL", "MongoDB", "Database"],
  "comments": [                    ← embedded array of sub-documents
    {
      "user": "Rahul",
      "text": "Great article!",
      "date": "2024-03-16",
      "likes": 5
    },
    {
      "user": "Anita",
      "text": "Very helpful for my exam prep",
      "date": "2024-03-17",
      "likes": 3
    }
  ]
}
-- ✅ One query fetches post + all comments
-- ❌ If comments grow to 10,000+, document becomes huge (16MB limit in MongoDB)

Store related data in separate collections and reference by ID. Best when: data is accessed independently, there's a 1-to-many or many-to-many relationship, or the related data is frequently updated.

-- User document
{ "_id": "user_101", "name": "Priya", "email": "priya@example.com" }

-- Order documents (separate collection, reference user_id)
{ "_id": "order_501", "user_id": "user_101", "item": "Laptop", "amount": 65000 }
{ "_id": "order_502", "user_id": "user_101", "item": "Mouse",  "amount": 800   }

-- Query with $lookup (like SQL JOIN)
db.orders.aggregate([
  { $lookup: {
      from:         "users",
      localField:   "user_id",
      foreignField: "_id",
      as:           "user_details"
  }}
])
-- ✅ Orders and users can be queried and updated independently
-- ❌ Requires multiple queries (or $lookup which is slower than embedded)

One of NoSQL's greatest advantages over SQL: you can add new fields to a collection without ALTER TABLE, without downtime, and without breaking existing applications. Old documents simply don't have the new field.

-- Scenario: You need to add 'loyalty_points' to all users
-- In SQL: ALTER TABLE users ADD loyalty_points INT DEFAULT 0; (might lock table)
-- In MongoDB: Just start inserting new docs with the field!

-- Backfill existing docs that don't have the field:
db.users.updateMany(
  { loyalty_points: { $exists: false } },   -- where field doesn't exist
  { $set: { loyalty_points: 0 } }             -- add it with default 0
);

-- Rename a field across all documents:
db.users.updateMany({}, { $rename: { "phone": "mobile_number" } });

-- Remove a field from all documents:
db.users.updateMany({}, { $unset: { "old_field": "" } });

-- MongoDB creates DB when you first use it (no CREATE DATABASE command)
use restaurant_db          -- switch to (or create) database

-- Insert a single document
db.restaurants.insertOne({
  name:     "The Spice Route",
  borough:  "Manhattan",
  cuisine:  "Indian",
  address: {
    street: "74 W 47th St",
    zipcode: "10036",
    coord: [-73.9894, 40.7594]   ← nested document
  },
  grades: [                           ← array of sub-documents
    { grade: "A", score: 12, date: new Date("2024-01-15") },
    { grade: "B", score: 27, date: new Date("2023-06-20") }
  ]
})

-- Insert multiple documents at once
db.restaurants.insertMany([
  { name: "Mumbai Magic", borough: "Brooklyn", cuisine: "Indian" },
  { name: "Beijing Bites", borough: "Queens",   cuisine: "Chinese" },
  { name: "Pasta Palace", borough: "Bronx",    cuisine: "Italian" }
])

-- Basic queries
db.restaurants.find({})                          -- all documents
db.restaurants.find({ borough: "Manhattan" })   -- exact match
db.restaurants.findOne({ cuisine: "Indian" })   -- only first match

-- Comparison operators
db.restaurants.find({ "grades.score": { $gt:  90 } }) -- score > 90
db.restaurants.find({ "grades.score": { $gte: 70, $lt: 100 } })

-- From syllabus lab: restaurants NOT American, score > 70, longitude < -65.75
db.restaurants.find({
  cuisine: { $ne: "American" },
  "grades.score": { $gt: 70 },
  "address.coord.0": { $lt: -65.754168 }
})

-- NOT American, grade=A, NOT Brooklyn, sort by cuisine desc
db.restaurants.find({
  cuisine: { $ne: "American" },
  "grades.grade": "A",
  borough: { $ne: "Brooklyn" }
}).sort({ cuisine: -1 })

-- Logical operators
db.restaurants.find({
  $or: [
    { cuisine: "Indian" },
    { cuisine: "Chinese" }
  ]
})
db.restaurants.find({
  $and: [
    { borough: "Manhattan" },
    { "grades.score": { $gt: 80 } }
  ]
})

-- Projection: specify which fields to return (1=include, 0=exclude)
db.restaurants.find(
  { borough: "Manhattan" },
  { name: 1, cuisine: 1, _id: 0 }   -- show name+cuisine, hide _id
)

-- Sort, skip, limit (for pagination)
db.restaurants.find({})
  .sort({ name: 1 })    -- ascending by name
  .skip(20)             -- skip first 20 (page 2)
  .limit(10)            -- return 10 results

-- $set: add or change specific fields
db.restaurants.updateOne(
  { name: "The Spice Route" },
  { $set: { is_open: true, rating: 4.5 } }
)

-- $inc: increment a number
db.restaurants.updateMany(
  { cuisine: "Indian" },
  { $inc: { review_count: 1 } }   -- add 1 to review_count
)

-- $push: add element to an array
db.restaurants.updateOne(
  { name: "The Spice Route" },
  { $push: { grades: { grade: "A", score: 15, date: new Date() } } }
)

-- Upsert: update if exists, insert if not
db.restaurants.updateOne(
  { name: "New Restaurant" },
  { $set: { cuisine: "Fusion", borough: "Staten Island" } },
  { upsert: true }            -- creates new doc if not found
)

Without indexes, every query performs a collection scan (COLLSCAN) — examining every single document. As your collection grows to millions of documents, this becomes unbearably slow. Indexes create a separate B-tree data structure that allows O(log n) lookup instead of O(n) scan.

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  Creating Different Types of Indexes
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

-- 1. Single field index (most common)
db.restaurants.createIndex({ borough: 1 })  -- ascending
db.restaurants.createIndex({ rating: -1 })  -- descending

-- 2. Compound index (multiple fields together)
db.restaurants.createIndex({ borough: 1, cuisine: 1, name: 1 })
-- This compound index helps queries on:
--   (borough)                ← yes, prefix
--   (borough, cuisine)       ← yes, prefix
--   (borough, cuisine, name) ← yes, full index
--   (cuisine)                ← NO! (no borough prefix)
--   (cuisine, name)          ← NO!

-- 3. Unique index — prevents duplicates
db.users.createIndex({ email: 1 }, { unique: true })

-- 4. Sparse index — only indexes documents WHERE field exists
db.users.createIndex({ phone: 1 }, { sparse: true })
-- Documents without 'phone' field are NOT in this index
-- Useful when field is optional (avoid indexing many nulls)

-- 5. Partial index — only index subset matching a condition
db.restaurants.createIndex(
  { borough: 1 },
  { partialFilterExpression: { rating: { $gt: 4.0 } } }
)
-- Only indexes restaurants with rating > 4.0
-- Much smaller index, much faster for queries on popular restaurants

-- 6. Text index — for full-text search
db.articles.createIndex({ content: "text", title: "text" })
db.articles.find({ $text: { $search: "MongoDB indexing performance" } })

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  Analyzing Query Performance with explain()
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
db.restaurants.find(
  { borough: "Manhattan", cuisine: "Indian" }
).explain("executionStats")

-- Key things to look for in explain() output:
-- winningPlan.stage: "IXSCAN" = ✅ using index, "COLLSCAN" = ❌ full scan
-- totalDocsExamined: should be close to nReturned (ideally equal)
-- executionTimeMillis: how long in milliseconds
-- nReturned: how many docs matched

-- Index management
db.restaurants.getIndexes()          -- list all indexes
db.restaurants.dropIndex("borough_1") -- drop specific index by name
db.restaurants.dropIndexes()          -- drop all except _id

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  Complete aggregation pipeline example
  From syllabus: restaurants with score > 90
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
db.restaurants.aggregate([
  // Stage 1: filter to Manhattan
  { $match: { borough: "Manhattan" } },

  // Stage 2: explode grades array — 1 doc per grade
  { $unwind: "$grades" },

  // Stage 3: only keep grades with score > 90
  { $match: { "grades.score": { $gt: 90 } } },

  // Stage 4: group by cuisine, compute stats
  { $group: {
      _id:       "$cuisine",
      avgScore:  { $avg:  "$grades.score" },
      maxScore:  { $max:  "$grades.score" },
      restCount: { $addToSet: "$name" },  // unique restaurant names
      total:     { $sum: 1 }
  }},

  // Stage 5: add a field counting unique restaurants
  { $addFields: {
      uniqueRestaurants: { $size: "$restCount" }
  }},

  // Stage 6: sort by average score descending
  { $sort: { avgScore: -1 } },

  // Stage 7: only top 5 cuisines
  { $limit: 5 },

  // Stage 8: clean up output
  { $project: {
      cuisine:           "$_id",
      avgScore:          { $round: ["$avgScore", 2] },
      uniqueRestaurants: 1,
      _id:               0
  }}
])

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  $lookup — Join two collections
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
db.orders.aggregate([
  { $lookup: {
      from:         "customers",  // collection to join
      localField:   "customer_id",// field in orders
      foreignField: "_id",         // field in customers
      as:           "customer_info"// name for joined array
  }},
  { $unwind: "$customer_info" },  // flatten the joined array
  { $project: {
      order_date:    1,
      amount:        1,
      "customer_info.name": 1,
      "customer_info.city": 1
  }}
])

XML (eXtensible Markup Language) is a text-based format for representing structured data. Every element has an opening and closing tag. It's self-describing (tags explain the data). Widely used for: configuration files, web services (SOAP), data interchange between systems, and document storage.

<?xml version="1.0" encoding="UTF-8"?>
<library>
  <book id="B001" inStock="true">
    <title>Clean Code</title>
    <author>Robert C. Martin</author>
    <price currency="INR">499</price>
    <categories>
      <category>Programming</category>
      <category>Software Engineering</category>
    </categories>
  </book>
  <book id="B002" inStock="false">
    <title>The Pragmatic Programmer</title>
    <author>David Thomas</author>
    <price currency="INR">549</price>
  </book>
</library>

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  XPath — Navigate the XML tree
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  /library              → root element
  /library/book         → all book elements
  /library/book[1]      → first book only
  /library/book/@id     → all id attributes
  /library/book[@id='B001']         → book with id B001
  /library/book[@id='B001']/price   → price of book B001
  //title               → all title elements anywhere in doc
  /library/book[price > 500]        → books costing over 500

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  XQuery — Query language for XML (like SQL for XML)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
for    $b in doc("library.xml")/library/book
where  $b/price > 400
  and $b/@inStock = "true"
order by $b/price descending
return
  <result>
    <bookTitle>{$b/title/text()}</bookTitle>
    <cost>{$b/price/text()}</cost>
  </result>

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  Getting Started with cqlsh
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-- Start the CQL shell
$ cqlsh
$ cqlsh 127.0.0.1 9042    -- with specific host and port

-- Create a keyspace (with replication settings)
CREATE KEYSPACE college_db
WITH REPLICATION = {
  'class': 'SimpleStrategy',     -- for single DC
  'replication_factor': 3         -- 3 copies of all data
};
-- For production (multi-datacenter):
CREATE KEYSPACE prod_db
WITH REPLICATION = {
  'class': 'NetworkTopologyStrategy',
  'datacenter1': 3,
  'datacenter2': 2
};

USE college_db;       -- switch to keyspace
DESCRIBE KEYSPACES;   -- list all keyspaces
DESCRIBE TABLES;      -- list tables in current keyspace
DESCRIBE TABLE employees; -- see table structure

Unlike SQL where you add indexes after design, Cassandra requires you to design your tables around your queries. The PRIMARY KEY determines: which node stores the data AND how data is sorted on each node. Get this wrong and your queries will either fail or be extremely slow.

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  Designing for query: "Get sensor readings for
  device X in descending time order"
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
CREATE TABLE sensor_readings (
  device_id   UUID,        -- partition key: groups all readings for one device
  read_time   TIMESTAMP,  -- clustering key: sorted newest-first
  temperature FLOAT,
  humidity    FLOAT,
  location    TEXT,
  PRIMARY KEY (device_id, read_time)
) WITH CLUSTERING ORDER BY (read_time DESC);

-- ✅ This query is FAST:
SELECT * FROM sensor_readings
WHERE device_id = 12345-uuid
AND   read_time > '2024-01-01'
LIMIT 100;

-- ❌ This query FAILS (no partition key):
SELECT * FROM sensor_readings
WHERE temperature > 30;  -- Cannot scan all partitions

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  Table Operations
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-- Create employees table
CREATE TABLE employees (
  emp_id      UUID PRIMARY KEY,
  first_name  TEXT,
  last_name   TEXT,
  department  TEXT,
  salary      DECIMAL,
  hire_date   DATE,
  is_active   BOOLEAN
);

-- Add a new column (non-breaking change)
ALTER TABLE employees ADD email TEXT;
ALTER TABLE employees ADD skills LIST<TEXT>;

-- Create secondary index (for non-partition key queries)
CREATE INDEX emp_dept_idx ON employees(department);
-- Now you can query: WHERE department = 'Engineering'
-- But use sparingly — secondary indexes are expensive at scale

DROP INDEX   emp_dept_idx;
TRUNCATE TABLE employees;  -- delete all rows, keep structure
DROP TABLE   employees;

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  Collections — LIST, SET, MAP in action
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
CREATE TABLE student_profile (
  student_id  UUID PRIMARY KEY,
  name        TEXT,
  -- LIST: ordered, allows duplicates (exam scores in order)
  scores      LIST<INT>,
  -- SET: unordered, NO duplicates (enrolled courses)
  courses     SET<TEXT>,
  -- MAP: key-value (certification → date earned)
  certs       MAP<TEXT, DATE>
);

-- Insert with collections
INSERT INTO student_profile (student_id, name, scores, courses, certs)
VALUES (
  uuid(),
  'Priya Sharma',
  [85, 90, 78, 92],                              -- LIST
  {'DBMS', 'OS', 'Networks', 'ML'},              -- SET
  {'AWS_Cloud': '2024-03-15', 'MongoDB': '2024-01-20'} -- MAP
);

-- Append to a LIST
UPDATE student_profile
SET scores = scores + [88]
WHERE student_id = some_uuid;

-- Add to a SET
UPDATE student_profile
SET courses = courses + {'Big Data'}
WHERE student_id = some_uuid;

-- Add to a MAP
UPDATE student_profile
SET certs['Cassandra'] = '2024-06-01'
WHERE student_id = some_uuid;

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
  UDT — User Defined Type (custom complex type)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
-- Define UDT (like a struct in C)
CREATE TYPE address_type (
  street  TEXT,
  city    TEXT,
  state   TEXT,
  pincode TEXT
);
-- Use in a table (must use FROZEN for non-collection UDT columns)
CREATE TABLE employees (
  emp_id     UUID PRIMARY KEY,
  name       TEXT,
  home_addr  FROZEN<address_type>,    -- single address
  past_addrs LIST<FROZEN<address_type>> -- list of addresses
);

-- Insert with UDT
INSERT INTO employees (emp_id, name, home_addr)
VALUES (
  uuid(),
  'Priya',
  { street: '101 Andheri East', city: 'Mumbai', state: 'MH', pincode: '400069' }
);

A BATCH groups multiple DML statements and executes them atomically within a single partition. In Cassandra, BATCH is NOT a full transaction — it guarantees atomicity only within the same partition. Cross-partition batches should be avoided as they are slow and add coordinator load.

-- Batch: all operations executed atomically
BEGIN BATCH
  -- Insert new employee
  INSERT INTO employees (emp_id, name, salary)
    VALUES(uuid(), 'Neha Patil', 45000);

  -- Update salary where commission = 0
  UPDATE employees
    SET salary = 20000
    WHERE emp_id = some_uuid;

  -- Change names starting with N to uppercase
  UPDATE employees
    SET name = toUpperCase(name)
    WHERE emp_id = another_uuid;
APPLY BATCH;

-- Logged BATCH (default): safe but slower — coordinator logs before applying
-- Unlogged BATCH: faster but not atomic
BEGIN UNLOGGED BATCH
  ...
APPLY BATCH;

According to IBM, bad data costs businesses $3.1 trillion annually in the USA alone. In data pipelines, dirty data causes incorrect analytics, wrong business decisions, failed ML models, and regulatory compliance failures.

Machine learning algorithms (KNN, SVM, Neural Networks) are sensitive to the scale of features. If "salary" ranges from 10,000 to 1,00,000 and "age" ranges from 22 to 65, the ML model will be dominated by salary. Scaling puts all features on similar ranges.

Data lineage tracks the journey of data from origin to destination — where did it come from, what transformations were applied, who accessed it. Critical for: debugging data quality issues ("why is this sales figure wrong?"), regulatory compliance (GDPR, HIPAA), and impact analysis ("if I change this ETL rule, what downstream reports are affected?").