Basics Of SQL In Terms Of Mathematical Relation

Author: Eiko

Tags: sql, relation, database

Time: 2024-12-31 11:09:45 - 2024-12-31 11:09:45 (UTC)

Relational Database

Mathematically, a relation is just a subset of certain product spaces, like $R \subset A \times B \times C$ . Its elements are of the form of tuples $(a, b, c)$ .

The format for creating a table like this in SQL is simple:

CREATE TABLE table_name (
    column_a datatype_a,
    column_b datatype_b,
    column_c datatype_c,
   ....
);

Query Language

There are three core operations:

Selection: This is similar to projection to coordinates

$\prod_{all} T_{i} \overset{select}{\to} \prod_{some} T_{i}$
Where: This is a filter operation

$\prod_{i} T_{i} \overset{conditions}{\to} \prod_{i} T_{i}$
Join: Join is similar to a fibred product in mathematics, but there are multiple different kinds of join operations. For example the inner join on $R_{A B} \subset A \times B$ and $R_{B C}$ is $R_{A B} \times_{B} R_{A C}$ , selecting all $(a, b, c)$ that satisfies $(a, b) \in R_{A B}$ and $(b, c) \in R_{B C}$ .

$\prod_{I} T_{i} \times \prod_{J} T_{j} \overset{join}{\to} \prod_{I} T_{i} \times_{\prod_{K} T_{k}} \prod_{J} T_{j}, K \subset I \cap J$
```
SELECT a, b, c
FROM table1
INNER JOIN table2
ON table1.b = table2.b;
```

More Join Operations

Left join: besides a normal join, it forces the left table to be included in the result, even if there is no match in the right table (by filling NULL values).

$\prod_{I} T_{i} \times \prod_{J} T_{j} \overset{left join}{\to} \prod_{I} T_{i} \times_{\prod_{K} T_{k}} (Either \prod_{J} T_{j} or NULL), K \subset I \cap J$
```
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;
```
Right join: similar to left join, but the right table is included in the result.
Full join: includes all rows from both tables, filling NULL values when necessary.

Cross join: returns the Cartesian product of the two tables.

SELECT column_name(s)
FROM table1
CROSS JOIN table2;

Type System And Data Integrity In SQL

Basic types like INTEGER, REAL, TEXT, BLOB, NULL, DATE, BOOLEAN, etc.
Constraints like NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT, etc.
Transactions: COMMIT, ROLLBACK, SAVEPOINT, BEGIN TRANSACTION, etc. (We can think of this as a kind of homotopy theory in mathematics.)
Domain Types: you can create your custom types that encapsulate certain constraints and properties.

Example:

CREATE TABLE Users
  ( user_id SERIAL PRIMARY KEY
  , username TEXT UNIQUE NOT NULL
  , email TEXT NOT NULL
  , age INTEGER CHECK (age >= 18)
  , created_on DATE DEFAULT CURRENT_DATE
  );

here

SERIAL is a shorthand for INTEGER PRIMARY KEY AUTOINCREMENT.
PRIMARY KEY is a constraint that uniquely identifies each record in a table, it must be injective.
UNIQUE constraint ensures that all values in this column are different, ensuring injectivity.
NOT NULL constraint enforces a column to not accept NULL values.
CHECK constraint ensures that all values in this column satisfy certain conditions.

Advanced Magics

Extensions and Custom Types

PostgreSQL has a extension system allowing users to augment the database with additional functionalities, this can be new types, new functions, new operators, index types, etc.