Structured Query Language (SQL): Relational Foundations, DDL, and DML Semantics

Relational Query Language Architecture and Core Principles

• Database Language Purpose and Learning Scope:

  • SQL (Structured Query Language) serves as the universal standard interface for interacting with relational database management systems (DBMS).

  • Mastery of SQL requires two distinct capabilities:

    • Reading SQL: Understanding the precise formal semantics, theoretical meaning, and logical evaluation of SQL statements.

    • Writing SQL: Constructing syntactically valid queries, data definition statements, and manipulation scripts to build and interact with database systems.

  • Focusing on core theoretical concepts provides a long shelf life, as core relational principles remain fundamental across changing technologies and automated AI tooling.

• Relational Query Sublanguages:

  • SQL is divided into two main sublanguages:

    1. Data Definition Language (DDL): Used to create, alter, and delete database structures (schemas, tables, indexes, constraints).

    2. Data Manipulation Language (DML): Used to insert, update, delete, and retrieve (query) data rows stored within database structures.

• Role of the DBMS and Query Optimization:

  • SQL is a declarative language: users specify what data to retrieve or manipulate rather than how to procedurally perform the operation.

  • The DBMS is responsible for determining the most efficient procedural execution strategy.

  • Decoupling logical query specifications from physical execution allows the internal Query Optimizer to re-order operations, select physical access paths, and utilize indexes without altering the logical result of the query.

  • Optimizer decisions are driven by an internal cost model evaluating I/O cost, CPU execution time, and data selectivity.

• SQL Standards and Dialects:

  • SQL was initially standardized by the American National Standards Institute (ANSI) in 1986 ($\text{SQL-86}$).

  • Subsequent major standards include $\text{SQL-89}$, $\text{SQL-92}$, $\text{SQL:1999}$, $\text{SQL:2003}$, up to $\text{SQL:2023}$.

  • Most commercial relational databases maintain compliance with the $\text{SQL-92}$ core specification while offering custom proprietary implementations and features known as dialects.

  • Common dialects include MySQL, Oracle PL/SQL, Oracle SQL*Plus, and Microsoft SQL Server (T-SQL).

  • Dialect Variation Examples:

    • Date Formats:

      • MySQL standard date format: YYYY-MM-DD.

      • Oracle standard date format: DD-MMM-YY or DD-MMM-YYYY.

    • Procedural Extensions:

      • Oracle PL/SQL extends SQL with procedural capabilities, block structures, variable declarations, and user-defined functions utilizing keywords like RETURN.

Formal Language Theory: Syntax vs. Semantics

• Definitions of Syntax and Semantics:

  • Every formal programming or query language is strictly defined by two distinct components:

    • Syntax: The formal grammatical rules and structural laws that dictate how valid code instructions must be written.

    • Semantics: The exact mathematical meaning, interpretation, and behavioral execution assigned to syntactically correct expressions and statements.

• Syntax vs. Representation (The Magritte Principle):

  • Syntax specifies symbolic expressions, whereas semantics maps those symbols to actual mathematical structures or real-world entities.

• Mathematical Representation of Semantics:

  • In formal language theory, the semantics of programming code is formally expressed using mathematical structures and mappings.

  • Imperative Program Example:

    • Syntax: text for i = 0 to 2 x = x + 1 y = 2 * x             

    • Semantics: A mathematical transformation function mapping initial state to final state:             $\text{prog} : \mathbb{R}^2 \rightarrow \mathbb{R}^2, \quad (x, y) \mapsto (x + 3, 2x + 4)$

  • SQL Semantics: The semantic meaning of SQL statements is defined formally using mathematical relations and set operations.

Mathematical Preliminaries: Set Theory and Relations

• Defining Sets with Set-Builder Notation:

  • The primary method for specifying a mathematical set is set-builder notation, which restricts a larger domain set using a logical predicate (condition).

  • Examples:

    • $\{ x \in \mathbb{N} \mid 5 < x \}$: Read as "the set of all natural numbers $x$ such that $x$ is greater than $5$."

    • $\{ x \in \mathbb{Z} \mid x \pmod 2 = 0 \}$: Read as "the set of all integers $x$ such that $x$ is divisible by $2$."

• Fundamental Set Operations:

  1. Intersection ($A \cap B$): Contains elements that belong to both set $A$ and set B$.\n * { \text{a}, \text{b}, \text{aa}, \text{bb}, \text{ab}, \text{ba} } \cap { \text{a}, \text{c}, \text{aa}, \text{cc}, \text{ac}, \text{ca} } = { \text{a}, \text{aa} }\n 2. **Union (A \cup B$)**: Contains elements that belong to set$A$, set$B, or both.\n * { \text{a}, \text{b}, \text{aa}, \text{bb}, \text{ab}, \text{ba} } \cup { \text{a}, \text{c}, \text{aa}, \text{cc}, \text{ac}, \text{ca} } = { \text{a}, \text{b}, \text{c}, \text{aa}, \text{bb}, \text{cc}, \text{ab}, \text{ba}, \text{ac}, \text{ca} }\n 3. **Cartesian Product (A \times B$)**: Contains all ordered pairs$(a, b)$such that$a \in A$and$b \in B$.

     • $\{ \text{true}, \text{false} \} \times \{ 0, 1 \} = \{ (\text{true}, 0), (\text{true}, 1), (\text{false}, 0), (\text{false}, 1) \}$

• Set Membership and Subset Inclusion:

  • Membership Symbol ($\in$): Denotes that an element belongs to a set.

    • $\text{true} \in \{ \text{true}, \text{false} \}$

  • Non-membership Symbol ($ otin$): Denotes that an element does not belong to a set.

    • $7 \notin \{ \text{true}, \text{false} \}$

  • Subset Inclusion ($\subseteq$): A set $A$ is included in $B$ ($A \subseteq B$) if every element of $A$ is also an element of $B$:         $\forall x \, (x \in A \implies x \in B)$

• Mathematical Definition of Relations:

  • A relation is a mathematical abstraction representing connections between sets.

  • A binary relation $R$ between two sets $X$ and $Y$ is a set of ordered pairs:         $R = \{ (x_1, y_1), (x_2, y_2), (x_3, y_3) \}$

  • Equivalently, a binary relation is a subset of the Cartesian product:         $R \subseteq X \times Y$

  • If $X = Y$, $R$ is called a relation on a set. For instance, the ordering relation $\le$ on natural numbers $\mathbb{N}$ is:         $\le \, = \{ (0,0), (0,1), \dots, (1,1), (1,2), (2,2), \dots \} \subseteq \mathbb{N} \times \mathbb{N}$

  • An $n$-ary relation $R$ across $n$ sets $X_1, X_2, \dots, X_n$ is a set of $n$-tuples:         $R = \{ (x_1, \dots, x_n), (x_1', \dots, x_n') \} \subseteq X_1 \times X_2 \times \dots \times X_n$

Relational Model: Tables vs. Mathematical Relations

• Equivalence of Relational Database Concepts:

  • In a relational database, all user data is stored strictly within relations. No alternative underlying data structures exist.

  • A database is defined formally as a collection of relations.

  • A relation is conceptually represented as a database table.

• Structural Components of a Table:

  • Name: Unique table identifier.

  • Columns: Fixed, unchanging set of attributes; each column is named and strongly typed.

  • Rows: Time-varying set of data records.

• Concrete Example (Student Relation):

  • Attributes: name (Strings), id (Integers), exam1 (Integers $\le 100$), exam2 (Integers $\le 100$).

  • Schema definition signature:         $\text{student} \subseteq \text{VARCHAR(255)} \times \text{INTEGER} \times \text{INTEGER} \times \text{INTEGER}$

  • Example tuples in student:

    • $\text{tuple}_1 = (\text{'Mounia'}, 891023, 12, 58)$

    • $\text{tuple}_2 = (\text{'Jane'}, 891024, 66, 90)$

    • $\text{tuple}_3 = (\text{'Thomas'}, 891025, 50, 65)$

• Conceptual Separation in Relational Systems:

  1. Schema / Shape: Defines the meta-information (number of attributes, domain datatypes, constraints, and inter-relation links). Defined via DDL.

  2. Content / Instance: The actual collection of $n$-tuples stored in the tables at any given point in time. Manipulated via DML.

Data Definition Language (DDL) and Schema Creation

• Purpose of DDL:

  • To define, alter, and delete table structures and establish database schema rules.

  • Core DDL commands: CREATE, ALTER, DROP.

• Pragmatic Schema Design Rules:

  1. Avoid rushing into SQL implementation without initial conceptual planning.

  2. Consider both present operational requirements and future expansion needs.

  3. Design a complete pen-and-paper or ER diagram schema prior to writing DDL.

  4. Establish clear, consistent, and descriptive naming conventions for tables and columns.

  5. Review schema designs with database administrators (DBAs) and end-users.

  6. Fix design flaws early, before application dependencies and data deployments occur.

• Table Creation Syntax (CREATE TABLE):

  • General Syntax: sql CREATE TABLE table_name ( column_name1 datatype1, column_name2 datatype2, column_name3 datatype3, ... column_nameN datatypeN );         

  • Statement Termination: SQL statements end with a semi-colon (;), indicating sequential statement execution.

• Supported Datatypes across Relational DBMS:

  • Numeric Types:

    • Integer: INT, INTEGER.

    • Fixed / Floating point Real numbers: DECIMAL, NUMERIC, FLOAT, DOUBLE.

  • String Types:

    • Fixed-length string: CHAR(n) (padded with spaces to length $n$).

    • Varying-length string: VARCHAR(n) (stores up to $n$ characters).

  • Temporal Types:

    • DATE, DATETIME.

    • Format formatting varies by dialect (YYYY-MM-DD in MySQL vs DD-MMM-YYYY in Oracle).

• Integrity Constraints:

  • Constraints enforce rules on acceptable attribute values and relations between tables.

  • Column Constraints: Applied directly to individual attribute definitions.

    • NOT NULL: Prevents insertion of NULL (missing/unknown) values.

    • CHECK (condition): Restricts attribute values to those satisfying a logical condition (e.g., AskPrice DOUBLE CHECK (AskPrice >= 0)).

    • DEFAULT <value>: Assigns a default fallback value if no value is explicitly provided during insertion.

*   **Table Constraints**: Applied across one or multiple columns.
    *   Multi-column `CHECK` (e.g., `CHECK (AskPrice >= BidPrice)`).
    *   `PRIMARY KEY`: Specifies unique tuple identifiers.
    *   `FOREIGN KEY`: Enforces referential integrity between tables.

• Primary Keys and Mathematical Functions:

  • A mathematical function $f : X \rightarrow Y$ maps every input $x \in X$ to exactly one output y \in Y$.\n * A primary key enforces functional behavior on a relation:\n * Single primary key X_1$models a function$f : X_1 \rightarrow X_2 \times X_3 \times \dots \times X_n, ensuring every key value uniquely maps to a single output tuple.\n * Composite primary key (X_1, X_2)$models a function$f : X_1 \times X_2 \rightarrow X_3 \times \dots \times X_n.\n * SQL syntax: `PRIMARY KEY (Ticker, Date)` enforces ( \text{Ticker}, \text{Date} ) \mapsto ( \text{AskPrice}, \text{BidPrice}, \text{Mid60RollAvg} ).\n\n* **Referential Integrity (`FOREIGN KEY`)**:\n * A foreign key in a child table references the primary key of a parent table.\n * Ensures values in child columns must pre-exist in the referenced parent primary key column.\n * *Example*:\n ```sql\n CREATE TABLE customers (\n customer_id INT PRIMARY KEY,\n name VARCHAR(100)\n );\n\n CREATE TABLE orders (\n order_id INT PRIMARY KEY,\n customer_id INT,\n FOREIGN KEY (customer_id) REFERENCES customers(customer_id)\n );\n        ```\n * *Insertion Validation*:\n * Inserting `INSERT INTO orders VALUES (101, 1)` succeeds if `customer_id = 1` exists in `customers`.\n * Inserting `INSERT INTO orders VALUES (102, 999)` triggers a constraint violation failure if `customer_id = 999` does not exist in `customers`.\n\n* **Referential Triggered Actions**:\n * Triggered actions handle parent table modifications (`UPDATE` or `DELETE`) that affect referenced keys:\n * `CASCADE`: Automatically propagates updates or deletions in parent records to corresponding child records.\n * `RESTRICT`: Rejects parent updates or deletions if any child row references that parent key.\n * `SET NULL` / `SET DEFAULT`: Sets referencing child key columns to `NULL` or default values when parent key changes or drops.\n * `NO ACTION`: Rejects modifications similarly to `RESTRICT` (evaluated at transaction completion).\n\n\n\n* **Circular Schema Dependencies (The Chicken-and-Egg Problem)**:\n * When Table A references Table B as a foreign key, but Table B also references Table A as a foreign key, neither table can be created first without producing an error.\n * *Resolution Procedure*:\n 1. Create Table A (`Branch`) without foreign key constraints.\n 2. Create Table B (`Staff`) referencing Table A.\n 3. Use `ALTER TABLE` to append the foreign key constraint to Table A referencing Table B.\n\n ```sql\n CREATE TABLE Branch (\n branchNo VARCHAR(5) PRIMARY KEY,\n street VARCHAR(40),\n city VARCHAR(15),\n state VARCHAR(15),\n zipcode INTEGER,\n mgrStaffNo VARCHAR(5)\n );\n\n CREATE TABLE Staff (\n staffNo VARCHAR(5) PRIMARY KEY,\n name VARCHAR(40),\n position VARCHAR(15),\n salary INTEGER,\n branchNo VARCHAR(5),\n FOREIGN KEY (branchNo) REFERENCES Branch(branchNo) ON DELETE NO ACTION\n );\n\n ALTER TABLE Branch \n ADD CONSTRAINT fk_Branch \n FOREIGN KEY (mgrStaffNo) REFERENCES Staff(staffNo) ON DELETE NO ACTION;\n        ```\n\n\n\n* **Modifying and Dropping Tables**:\n * Delete table from database: `DROP TABLE stock_price;` \n * Add a column: `ALTER TABLE stock_price ADD ImpliedVol DOUBLE CHECK(ImpliedVol >= 0);` \n * Remove a column: `ALTER TABLE stock_price DROP COLUMN Mid60RollAvg;` \n * Modify datatype: `ALTER TABLE stock_price MODIFY COLUMN Mid60RollAvg FLOAT;` \n\n# Data Manipulation Language (DML) Operations\n\n* **Purpose of DML**:\n * To insert, modify, remove, and query data tuples within established relations.\n * Core commands: `INSERT INTO`, `UPDATE`, `DELETE`, `SELECT`.\n\n* **Inserting Data Records (`INSERT INTO`)**:\n * Adds new tuples to a relation.\n * Full tuple insertion: `INSERT INTO table_name VALUES (val1, val2, ..., valN);` \n * Explicit column selection: `INSERT INTO table_name (col1, col2) VALUES (val1, val2);` \n\n* **Formal Relation Implementation Example**:\n * Target mathematical relation:\n        \text{delim} = { (m, n) \in \mathbb{N} \times \mathbb{N} \mid 1 < m, n < 10, n \pmod m = 0 }\n * This relation contains pairs of natural numbers (m, n)$between$2$and$9$where$m$divides$n.\n * Elements belonging to \text{delim}:\n        { (2,2), (2,4), (2,6), (2,8), (3,3), (3,6), (3,9), (4,4), (4,8), (5,5), (6,6), (7,7), (8,8), (9,9) }\n\n\n\n* **Updating and Deleting Records**:\n * **Updating records**:\n ```sql\n UPDATE table_name \n SET col1 = val1, col2 = val2 \n WHERE condition;\n        ```\n * **Deleting records**:\n ```sql\n DELETE FROM table_name \n WHERE condition;\n        ```\n * *Operational Warning*: Omitting the `WHERE` clause in an `UPDATE` or `DELETE` statement applies the action globally to **all** rows in the table.\n\n# Relational Query Processing and Logical Semantics\n\n* **Anatomy of a Standard SQL Query**:\n ```sql\n SELECT [DISTINCT] target-list\n FROM source-list\n WHERE condition\n ORDER BY column-list ASC|DESC;\n    ```\n * `target-list`: Attributes or aggregate functions to retrieve.\n * `source-list`: List of source relations (tables), with optional table aliases.\n * `condition`: Logical boolean expression combining attribute comparisons (`=`, `<>`, `<`, `>`, `<=`, `>=`) using `AND`, `OR`, `NOT`, `LIKE`, or `IN`.\n * `column-list`: Attributes determining output row ordering.\n\n* **Data Structure Formalisms: Sets, Multisets, and Sequences**:\n * **Sets**: Unordered collections of distinct elements. Order does not matter, duplicates are not permitted ({1, 2, 3} = {2, 3, 1}$, and${1, 1, 2} = {1, 2}).\n * **Multisets (Bags)**: Unordered collections allowing duplicate elements. Order does not matter, but duplicate counts are maintained (\mathbb{\{1, 1, 2\}} \neq \mathbb{\{1, 2\}}$; formally represented as$\{1:2, 2:1\}).\n * **Sequences (Tuples)**: Ordered lists where both position and duplicate counts matter ((1, 2, 3) \neq (2, 1, 3)).\n\n* **Logically vs. Practically Returned Structures**:\n * *Standard Relational Theory*: Operates strictly on pure mathematical sets.\n * *SQL Real-World Execution*:\n * Default `SELECT * FROM Table` returns a **multiset** (duplicates preserved):\n            \text{SELECT} \, * \, \text{FROM} \, T = { r_1 : n_1, r_2 : n_2, \dots, r_k : n_k }\n * `SELECT DISTINCT * FROM Table` returns a pure **set** (duplicates eliminated):\n            \text{SELECT DISTINCT} \, * \, \text{FROM} \, T = { r_1 : 1, r_2 : 1, \dots, r_k : 1 }\n * `SELECT * FROM Table ORDER BY Col ASC` returns a **sequence / tuple of tuples**:\n            \text{SELECT} \, * \, \text{FROM} \, T \, \text{ORDER BY} \dots = (r_{\pi(1)}, r_{\pi(2)}, \dots, r_{\pi(N)})\n\n\n\n* **Conceptual Query Evaluation Strategy**:\n    The logical evaluation of a SQL query follows a rigorous 3-step pipeline:\n 1. **`FROM` Clause (Cartesian Product)**: Computes the cross-product (R_1 \times R_2 \times \dots \times R_m) of all source tables specified in `source-list`.\n 2. **`WHERE` Clause (Selection \sigma_P$)**: Evaluates the predicate condition$P$against each tuple generated in Step 1, discarding any tuple where$P(\text{tuple}) eq \text{true}.\n 3. **`SELECT` Clause (Projection \pi)**: Projects desired attributes from `target-list`, removing unselected columns. If `DISTINCT` is specified, duplicate tuples are eliminated. If `ORDER BY` is specified, tuples are sorted.\n\n* **Complete Worked Example of Query Evaluation**:\n * *Target SQL Query*:\n ```sql\n SELECT S.name, E.cid\n FROM Students S, Enrolled E\n WHERE S.sid = E.sid AND E.grade = 'B';\n        ```\n * *Formal Mathematical Specification*:\n        \pi_{S.\text{name}, E.\text{cid}} \left( { t \in \text{Students} \times \text{Enrolled} \mid t[S.\text{sid}] = t[E.\text{sid}] \land t[E.\text{grade}] = \text{'B'} } \right)\n\n * *Base Source Data*:\n\n        *Table `Students` (S)*:\n\n| sid | name | login | age | gpa |\n| :--- | :--- | :--- | :--- | :--- |\n| 53666 | Jones | jones@cs | 18 | 3.4 |\n| 53688 | Smith | smith@ee | 18 | 3.2 |\n\n        *Table `Enrolled` (E)*:\n\n| sid | cid | grade |\n| :--- | :--- | :--- |\n| 53831 | Carnatic101 | C |\n| 53831 | Reggae203 | B |\n| 53650 | Topology112 | A |\n| 53666 | History105 | B |\n\n * **Step 1: Compute Cross Product (`Students` \times `Enrolled`)**:\n        Combines 2 student rows with 4 enrolled rows to produce 2 \times 4 = 8 combined tuples.\n\n| S.sid | S.name | S.login | S.age | S.gpa | E.sid | E.cid | E.grade |\n| :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- |\n| 53666 | Jones | jones@cs | 18 | 3.4 | 53831 | Carnatic101 | C |\n| 53666 | Jones | jones@cs | 18 | 3.4 | 53831 | Reggae203 | B |\n| 53666 | Jones | jones@cs | 18 | 3.4 | 53650 | Topology112 | A |\n| **53666** | **Jones** | **jones@cs** | **18** | **3.4** | **53666** | **History105** | **B** |\n| 53688 | Smith | smith@ee | 18 | 3.2 | 53831 | Carnatic101 | C |\n| 53688 | Smith | smith@ee | 18 | 3.2 | 53831 | Reggae203 | B |\n| 53688 | Smith | smith@ee | 18 | 3.2 | 53650 | Topology112 | A |\n| 53688 | Smith | smith@ee | 18 | 3.2 | 53666 | History105 | B |\n\n * **Step 2: Apply Predicate Selection (`WHERE S.sid = E.sid AND E.grade = 'B'`)**:\n * Tuple 4 evaluates to `true`: 53666 = 53666$and$\text{grade} = \text{'B'}$$.

    • All other 7 tuples evaluate to false and are discarded.

*   **Step 3: Project Target Attributes (`SELECT S.name, E.cid`)**:
    *   Discards unselected columns (`S.sid`, `S.login`, `S.age`, `S.gpa`, `E.sid`, `E.grade`).

*   *Final Result Relation*: A relation containing the single tuple `("Jones", "History105")`.