Comprehensive Guide to Relational Databases and SQL Operations

Architecture of Database Management Systems (DBMS)

A Database Management System (SGBD - Système de Gestion de Bases de Données) provides a software layer between applications/users and physical file storage. It handles data definition, storage, query processing, and transactional integrity.

Client-Server Architecture

• Clients Side:
  • Human Operators: Submit manual management requests and direct database queries.
  • Applications: Software written in languages such as Python, PHP, Java, or C# that issue programmatic database commands.
  • Communication: Requests for data management and query evaluation (demandes de gestion, interrogation des données) flow from clients to the DBMS server.
• Server Side (DBMS Core):
  • Query Evaluation & Optimization Module (Evaluation/optimisation des requêtes): Analyzes incoming queries, computes execution plans, and selects the most efficient execution path.
  • Transaction Management Module (Gestion des transactions): Ensures data consistency, atomicity, isolation, and durability during concurrent operations.
  • User Management Module (Gestion des utilisateurs): Controls authentication, authorization, access permissions, and roles.
  • Other Internal Engine Modules: Manage indexing, caching, locking, and recovery systems.
• Machine File Management System & Physical Storage:
  • Low-Level Data Transfer: The DBMS server executes file read, write, and manipulation operations (lectures, écritures, manipulations de fichiers) through the host operating system's file management system.
  • Storage Files (Stockage (fichiers)): Data resides physically on permanent disk storage structured as operating system files.

Categorization of DBMS Architectures

• Relational DBMS (SGBD Relationnel): Based on relational algebra and tabular structures (e.g., Oracle, MariaDB, MySQL, PostgreSQL).
• Graph-Oriented DBMS (Orienté Graphe): Designed to represent and store data as nodes, edges, and properties for direct graph traversal.
• NoSQL DBMS (Not Only SQL): Document stores, key-value engines, and wide-column databases designed for unstructured or distributed horizontal scaling (e.g., MongoDB).

Relational Data Model Fundamentals

A relational database constitutes a logical data model (modèle LOGIQUE des données) representing real-world entities and their relationships within a targeted application domain (e.g., robot management, library book loans, airline reservations).

Core Definitions

• Relation (or Table): A structured tabular representation consisting of columns (attributes) and rows (tuples).
• Schema of a Relation: The structural definition of a table, comprising:
  • The relation name.
  • The attribute names.
  • The domain of each attribute (e.g., integers, floating-point numbers, character strings representing proper nouns).
• Content of a Relation: The exact set of rows (tuples) present in the table at a specific point in time. Content is dynamic and subject to frequent updates (e.g., dynamic updates in train ticket bookings).
• Tuple (or Row): A single instance or entry within a relation containing discrete values for each attribute defined in the schema.
• Primary Key (Clé): An attribute or set of attributes of a relation $R$ that uniquely identifies each tuple in $R$. No two distinct tuples can share identical values for a primary key.
• Foreign Key (Clé étrangère): An attribute or set of attributes in a relation that matches the primary key of another relation. Foreign keys establish links and maintain referential integrity between separate relations.

Course Reference Database: The Robots Relational Database

The example database tracks robots, competitions, and performance results across three relations: Robots, Competitions, and Resultats.

Relation: Robots

• Schema: Robots(NoR: string, NomR: string, LaboR: string, Poids: integer)
• Primary Key: NoR (Robot registration number)

Relation: Competitions

• Schema: Competitions(NoC: string, NbJours: integer, Lieu: string)
• Primary Key: NoC (Competition identifier)

Relation: Resultats

• Schema: Resultats(NoR: string, NoC: string, Score: integer, FairPlay: integer)
• Composite Primary Key: (NoR, NoC)
• Foreign Keys:
  • NoR: References Robots(NoR)
  • NoC: References Competitions(NoC)

Note on NULL values: In Resultats, entry (B4546, WRC12) has a missing FairPlay score indicated by -, representing NULL (undefined or unknown value).

SQL Fundamentals: Data Manipulation & Querying

SQL (Structured Query Language) is a declarative language based on relational algebra used to define and manipulate relational databases.

Sublanguages within SQL

• Data Definition Language (DDL): Used to create, alter, and delete relation schemas and constraint definitions.
• Data Manipulation Language (DML): Expressions to query, insert, update, and delete tuples, specifying desired results declarative-style.

Basic SQL Query Structure

The foundational retrieval block follows the form:

SELECT ... 
FROM ... 
WHERE ...;

Relational Operations: Projection & Selection

• Projection Operation (Restricting columns):
  • Defined in the SELECT clause.
  • To retain specific attributes, list attribute names separated by commas.
  • To retain all attributes defined in the target table schema, use the wildcard operator *.
• Selection Operation (Restricting tuples/rows):
  • Defined in the WHERE clause.
  • Specifies a boolean expression condition that each row must satisfy to be included in the output result set.

Examples of Projection

Extracting robot number, name, and laboratory from Robots:

SELECT NoR, NomR, LaboR 
FROM Robots;

Output table structure: 5 rows displaying columns NoR, NomR, LaboR.

Examples of Selection

Filtering all attributes for robots belonging to laboratory JRL:

SELECT * 
FROM Robots 
WHERE LaboR = 'JRL';

Output tuples:

• (G3425, Artok, JRL, 200)
• (A2131, Gneto, JRL, 66)

Combined Projection & Selection Examples

1. Select all attributes for JRL robots weighing strictly over 100 units:

SELECT * 
FROM Robots 
WHERE LaboR = 'JRL' 
  AND Poids > 100;

Result tuple: (G3425, Artok, JRL, 200)

2. Select NoR, NomR, and Poids for JRL robots weighing strictly over 100 units:

SELECT NoR, NomR, Poids 
FROM Robots 
WHERE LaboR = 'JRL' 
  AND Poids > 100;

Result tuple: (G3425, Artok, 200)

3. String pattern matching using LIKE (lab names starting with 'J'):

SELECT LaboR 
FROM Robots 
WHERE LaboR LIKE 'J%';

4. Computed mathematical expressions and column aliasing (AS): Calculate the robot identifier and weighted total score ($\text{Total} = \text{Score} + 2 \times \text{FairPlay}$) for competition WRC13:

SELECT NoR, Score + 2 * FairPlay AS Total 
FROM Resultats 
WHERE NoC = 'WRC13';

5. Queries handling NULL values and duplicate suppression (DISTINCT): Find distinct robot registration numbers that have an undefined (NULL) FairPlay entry in at least one competition:

SELECT DISTINCT NoR 
FROM Resultats 
WHERE FairPlay IS NULL;

Note: NULL denotes an unknown or undefined value.

Sorting Query Results (ORDER BY)

• Use ORDER BY attribute [ASC|DESC] to sort the output.
• Primary and secondary ordering criteria can be chained using commas.

1. Select NoR, NomR, and Poids of JRL robots weighing over 50, ordered by weight descending:

SELECT NoR, NomR, Poids 
FROM Robots 
WHERE LaboR = 'JRL' 
  AND Poids > 50 
ORDER BY Poids DESC;

2. Primary sort by descending weight, secondary sort alphabetically by robot name (NomR):

SELECT NoR, NomR, Poids 
FROM Robots 
WHERE LaboR = 'JRL' 
  AND Poids > 50 
ORDER BY Poids DESC, NomR;

Valid Expressions in the WHERE Clause

• Simple Boolean Expressions:
  • Poids >= 50
  • FairPlay IS NULL
  • ABS(Score - FairPlay) > 50
• Compound Logical Expressions using AND, OR, NOT:
  • Poids >= 50 AND (LaboR = 'MIT' OR LaboR = 'JRL')
  • Score < 50 OR (FairPlay = 0 OR FairPlay IS NULL)

Multi-Table Operations: Joins and Set Operations

When desired attributes reside across multiple tables, relational joins correlate rows from different tables based on matching key conditions.

Relational Join Mechanics

To find scores and names for MIT robots participating in a WRC competition:

1. Identify entries in Resultats for WRC competitions.
2. Identify registration numbers of MIT robots in Robots.
3. Match corresponding rows across both tables using Robots.NoR = Resultats.NoR.

Join Classifications

• Natural Join (Jointure naturelle): A join condition matching equal values across all common attributes sharing identical names between schemas $R_1$ and $R_2$.
• Equi-join (Équi-jointure): A join condition explicitly based on an equality comparison (=) between specified attributes.
• Self-Join (Auto-jointure): Joining a table with itself using distinct table aliases.
• Multi-Table Join: Joining three or more tables concurrently.

Explicit Join Syntax Examples

1. Join between Robots and Resultats (Required course syntax using table list in FROM and equality condition in WHERE):

SELECT NoR, NomR, Score 
FROM Robots r1, Resultats r2 
WHERE r1.NoR = r2.NoR 
  AND LaboR = 'MIT' 
  AND NoC LIKE 'WRC%';

2. Join between Competitions and Resultats to locate distinct competition locations where robot B5654 scored over 10:

SELECT DISTINCT Lieu 
FROM Competitions, Resultats 
WHERE Competitions.NoC = Resultats.NoC 
  AND NoR = 'B5654' 
  AND Score > 10;

3. Self-Join Example: Form all unique pairs of robots where the difference in weight is strictly greater than 100 units:

SELECT r1.NoR, r2.NoR 
FROM Robots r1, Robots r2 
WHERE ABS(r1.Poids - r2.Poids) > 100 
  AND r1.NoR > r2.NoR;

Note: The condition r1.NoR > r2.NoR prevents pairing a robot with itself and eliminates duplicate symmetric pairs $(\text{Robot}_A, \text{Robot}_B)$ vs $(\text{Robot}_B, \text{Robot}_A)$.

Alternative SQL Join Syntaxes

While implicit equality joins in WHERE are required for standard coursework, standard SQL supports:

• Implicit equality join: SELECT ... FROM R1, R2 WHERE R1.NoR = R2.NoR;
• Natural join: SELECT ... FROM R1 NATURAL JOIN R2;
• Explicit ON join: SELECT ... FROM R1 JOIN R2 ON R1.NoR = R2.NoR;
• Explicit USING join: SELECT ... FROM R1 JOIN R2 USING (NoR);

Cartesian Product & Computational Complexity

• Cartesian Product ($R_1 \times R_2$): Combines every single tuple of $R_1$ with every single tuple of $R_2$. A Cartesian product occurs whenever join conditions are accidentally omitted from the WHERE clause.
• Cardinality: The number of rows in $R_1 \times R_2$ equals $|R_1| \times |R_2|$.
  • Example: Robots (5 tuples) $\times$ Resultats (9 tuples) = $45$ resulting rows.
• Performance Cost:
  • A join logically acts as a Cartesian product followed by a selection filter.
  • In a database with $600{,}000$ customers having an average of $40$ orders each, a naive double-loop join traversal evaluates $600{,}000 \times 40 = 24{,}000{,}000$ candidate rows.
  • Optimization strategies include indexes, physical storage organization, and specialized DBMS join algorithms (hash joins, index nested loops).

Grouping Data and Aggregate Functions

Aggregate functions allow calculations across column vectors, aggregating multiple rows into single summary values.

SQL Aggregate Functions

• AVG(expr): Computes the arithmetic mean of numeric expressions.
• MIN(expr): Computes the minimum value.
• MAX(expr): Computes the maximum value.
• SUM(expr): Computes the total sum.
• COUNT(attribute): Counts the number of non-null values present in the specified attribute column.
• COUNT(*): Counts the total number of rows matching the query criteria regardless of nulls.
• COUNT(DISTINCT attribute): Counts unique non-null values.

Single Aggregate Computation Example

Compute the average score achieved by robot F4546 across all its competitions:

SELECT AVG(Score) 
FROM Resultats 
WHERE NoR = 'F4546';

Calculation: Scores for F4546 are 35, 50, and 70. Average = $\frac{35 + 50 + 70}{3} = 51.6667$.

The GROUP BY Clause

The GROUP BY A1, ..., Ak clause partitions the set of relation tuples into disjoint subsets sharing identical values for attributes $(A_1, \times, A_k)$.

Grouping Example

Display the average score achieved by each robot:

SELECT NoR, AVG(Score) 
FROM Resultats 
GROUP BY NoR;

Output table:

The HAVING Clause

While WHERE filters individual rows before grouping occurs, HAVING filters generated group rows after grouping and aggregate calculations are completed.

Filter groups to display average scores strictly greater than 45:

SELECT NoR, AVG(Score) 
FROM Resultats 
GROUP BY NoR 
HAVING AVG(Score) > 45;

Result: Retains 3 rows (excludes B5654 whose average score is 35).

COUNT() Usage Examples

1. Count total robots registered to laboratory MIT:

SELECT COUNT(NoR) /* or COUNT(*) */ 
FROM Robots 
WHERE LaboR = 'MIT';

2. Count distinct competitions participated in by MIT robots:

SELECT COUNT(DISTINCT NoC) 
FROM Robots, Resultats 
WHERE Robots.NoR = Resultats.NoR 
  AND LaboR = 'MIT';

3. Display the total competition count per individual robot:

SELECT NoR, COUNT(NoC) 
FROM Resultats 
GROUP BY NoR;

Multi-Table Grouping & Aggregation Example

For each robot and laboratory, find the maximum total score ($\text{Score} + 2 \times \text{FairPlay}$) achieved in competitions lasting strictly over 2 days, considering only robots weighing over 100 units, returning only groups whose maximum points exceed 60, ordered by lab and robot name:

SELECT NomR, LaboR, MAX(Score + 2 * FairPlay) 
FROM Robots ro, Resultats re, Competitions co 
WHERE ro.NoR = re.NoR 
  AND re.NoC = co.NoC 
  AND Poids > 100 
  AND NbJours > 2 
GROUP BY NoR, NomR, LaboR 
HAVING MAX(Score + 2 * FairPlay) > 60 
ORDER BY LaboR, NomR;

Design Note: NoR is included in GROUP BY alongside NomR and LaboR because two distinct laboratories may have robots sharing identical names (e.g., Draco exists in both MIT and Sony). Grouping by NoR preserves entity uniqueness.

Nested Queries (Subqueries)

A nested query (or subquery) is an inner SELECT query embedded within the WHERE or HAVING clause of an outer parent query.

Types of Subqueries and Operators

1. Scalar Subqueries (Single-Value Output)

When an inner subquery evaluates to a single value ($1 \times 1$ singleton), standard scalar comparison operators can be evaluated: =, >, >=, <, <=, <>.

Example: Retrieve the identifier and name of the heaviest robot(s) in the database:

SELECT NoR, NomR 
FROM Robots 
WHERE Poids = (
    SELECT MAX(Poids) 
    FROM Robots
);

Evaluation: Inner subquery calculates single value 200. Outer query filters rows where Poids = 200.

2. Set Subqueries (Multi-Value Output)

When an inner subquery evaluates to a set containing multiple values ($n \times 1$), membership testing operators IN or NOT IN must be used.

Example 1 (IN): Subquery replacing explicit join to output names and laboratories of robots scoring strictly above 50 in any competition:

SELECT NomR, LaboR 
FROM Robots 
WHERE NoR IN (
    SELECT DISTINCT NoR 
    FROM Resultats 
    WHERE Score > 50
);

Evaluation: Inner subquery returns set {'G3425', 'B4546'}. Outer query tests NoR IN {'G3425', 'B4546'}.

Example 2 (NOT IN): Find registration numbers and names of robots that have participated in zero competitions (i.e., have no entries in Resultats):

SELECT NoR, NomR 
FROM Robots 
WHERE NoR NOT IN (
    SELECT DISTINCT NoR 
    FROM Resultats
);

Evaluation: Inner subquery yields the set of all robot identifiers present in Resultats. NOT IN isolates robots with no performance records.

Subquery Consistency Rule

Strict semantic consistency must be maintained between the subquery return cardinality and the selected logical operator. Using scalar operators (such as =) against a subquery yielding 2 or more tuples causes a execution failure.

Comprehensive SQL SELECT Syntax Reference

SELECT [DISTINCT] {col_name | expr} [,{col_name | expr} ...]
FROM table_references
[WHERE where_condition]
[GROUP BY {col_name}]
[HAVING where_condition]
[ORDER BY {col_name | expr | position} [ASC | DESC], ...] ;

Clause Hierarchy & Strict Syntax Execution Order

1. SELECT: Specifies projections, scalar expressions, and aggregate output vectors.
2. FROM: Identifies target relations and table aliases.
3. WHERE: Applies pre-grouping tuple selection filters.
4. GROUP BY: Partitions tuples into disjoint mathematical subsets.
5. HAVING: Applies post-grouping aggregate evaluation filters.
6. ORDER BY: Sorts the final result rows in ascending (ASC) or descending (DESC) order.

Syntax Rule: Clauses must strictly follow the defined hierarchy order. ORDER BY cannot precede GROUP BY, GROUP BY cannot precede WHERE, and HAVING cannot precede GROUP BY.