IDRASAcademic OS
CS307 • CANONICAL ACADEMIC TEXTBOOK4 Units • 4 Topics • Verified Multilingual Labs

Relational Database & SQL: Digital Knowledge & Laboratory Textbook

Relational calculus, schema normalization, multi-table joins, subqueries, indexing, and ACID transaction semantics.

Curriculum View Compiler Studio
Table of Contents1 of 4
Unit 1: Relational Algebra, Joins & Query Mechanics
Unit 2: Storage Engine Internals, 8KB Slotted Pages & Index Architectures
Unit 3: MVCC Concurrency, ACID Isolation Levels & Table Bloat
Unit 4: Query Optimization, Cost Models & EXPLAIN ANALYZE Tuning
Unit 1 • Chapter 1Estimated Study Effort: 30 minsBASIC

Relational Joins, Cartesian Products & Set Semantics in SQL

Rigorous exploration of relational joins: Cartesian product cardinality (m x n), INNER JOIN predicate filtering, LEFT/RIGHT OUTER JOIN tuple preservation, and Three-Valued Logic.

Learning Outcomes & Core Objectives:
["Calculate the cardinality of Cartesian products across relational tables.","Differentiate between INNER JOIN, LEFT OUTER JOIN, and FULL OUTER JOIN semantics.","Diagnose query bugs caused by three-valued logic and NULL equality comparisons.","Construct multi-table SQL queries joining normalized entity schemas."]
Conceptual Intuition and Real-World Mental Model (Hinglish)

What Problem Does This Architecture Solve?

Think of two spreadsheets: one of Students (ID, Name) and one of Library Books checked out (BookID, StudentID). An INNER JOIN gives you only students who currently hold books. A LEFT JOIN gives you all students, filling in empty cells (NULL) for students who haven't borrowed any books.
Formal Technical Definition and Notation

Rigorous Specification, Assumptions and Invariants

Under the hood, database optimizers (PostgreSQL, MySQL, SQLite) implement joins using three primary physical algorithms: 1. Nested Loop Join: O(M * N) - ideal when one table is tiny or indexed. 2. Hash Join: O(M + N) - builds in-memory hash table of smaller relation and probes with larger relation. 3. Merge Join: O(M log M + N log N) - sorts both relations on join key and walks them in linear time.
Step-by-Step State Transition and Mechanism

Execution Trace and State Mutation Sequence

LEFT JOIN Execution Flow: 1. Scan Left Table row by row. 2. Look up matching rows in Right Table where Left.id = Right.ref_id. 3. If 1 or more matches found, emit concatenated row(s). 4. If 0 matches found, emit Left row concatenated with NULLs for all Right table columns.
Worked Numerical and Dry-Run Walkthrough

Step-by-Step Numerical Example with Edge Cases

-- Table Students: [(1, 'Aarav'), (2, 'Priya'), (3, 'Kabir')] -- Table Enrollments: [(101, 1, 'COA'), (102, 1, 'DSA'), (103, 2, 'Python')] SELECT s.name, e.course FROM Students s LEFT JOIN Enrollments e ON s.id = e.student_id; -- Output: -- Aarav | COA -- Aarav | DSA -- Priya | Python -- Kabir | NULL <-- Kabir preserved with NULL because no enrollment!
Interactive Code Laboratory
main.sqlsql
-- Production-Grade SQL Join Schema & Query Demonstration
CREATE TABLE Departments (
    dept_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE Employees (
    emp_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    dept_id INTEGER,
    salary REAL,
    FOREIGN KEY (dept_id) REFERENCES Departments(dept_id)
);

-- Comprehensive Multi-Table Report with Safe NULL Coalescing
SELECT 
    e.name AS employee_name,
    COALESCE(d.name, 'Unassigned Department') AS department_name,
    e.salary
FROM Employees e
LEFT JOIN Departments d ON e.dept_id = d.dept_id
ORDER BY e.salary DESC;

Canonical Micro-Concepts

Concept #1Academic Micro-Unit

The Cartesian Product (CROSS JOIN)

The foundational relational operation R x S combines every row of table R with every row of table S. If table R has m rows and table S has n rows, the Cartesian product produces exactly m * n rows.

Core Takeaway: CROSS JOIN cardinality is m * n; joining without an ON clause causes massive Cartesian explosions.
Concept #2Academic Micro-Unit

INNER JOIN: Predicate Matching

An INNER JOIN filters the Cartesian product, returning only rows where the join predicate evaluates to TRUE. Rows from either table that have no matching counterpart are discarded.

Core Takeaway: INNER JOIN preserves only matching rows; unmatched rows are dropped.
Concept #3Academic Micro-Unit

LEFT OUTER JOIN: Preserving Unmatched Rows

A LEFT JOIN returns all rows from the left table, plus matched rows from the right table. If a row in the left table has no match in the right table, right-side columns are filled with NULL.

Core Takeaway: LEFT JOIN guarantees every row from the left table appears in the result set.
Concept #4Academic Micro-Unit

Three-Valued Logic & NULL Comparisons in SQL

In SQL, comparisons with NULL (e.g. 'val = NULL' or 'NULL = NULL') evaluate to UNKNOWN, not TRUE. To filter for NULL values, you must use 'IS NULL' or 'IS NOT NULL'.

Core Takeaway: NULL represents unknown data; never use '=' with NULL, always use 'IS NULL'.
Production Systems and Industrial Engineering Relevance

How This Concept Powers Real-World Tech Infrastructure

Essential for database administrators, backend web developers, analytics engineers, and data warehouse architects.

Topic 1 of 4