Vocabulaire

SQL est une implémentation spécifique d’une théorie qui s’appelle l’Algèbre relationnelle.

Nous utilisons donc un vocabulaire générique pour parler des différentes structures de notre base de données

Une relation

Une base de données se compose d’une collection de tables, ou avec le terme plus générique, de relations.

Une relation contient un set de tuples (« rows » ou « lignes »).

Chaque « ligne » est une collection d’attributs (ou colonnes).

Pourquoi le mot « relation » ? Puisqu'il s’agit d’une collection attributs qui sont tous liés

La relation étudiants dans une école contiendra des attributs comme « nom de famille », « prénom », « date de naissance », etc.

Chaque ligne « étudiant » est un tuple.

Un attribut

Un attribut représente une information atomique. Le mot atomique veut dire qu'on ne peut pas décomposer l'information en sous-information. Elle est indivisible.

• Un prénom est atomique, on ne peut pas le décomposer sans perdre du sens

• Une adresse n’est pas atomique : il y a des sous-informations utiles comme ville, code postal, etc.

Pour chaque attribut, nous précisons son domaine, qui est la gamme de valeurs valables.

• L'âge d'une personne a le domaine de toutes les valeurs numériques positives. Un numéro négatif n'est pas dans le domaine de l'âge. Ni un texte.

En SQL, nous spécifions le domaine par son type et ses contraintes.

Un attribut peut être vide ou null.

• normalement, cela veut dire que l’information est manquante, ou n'a pas encore été précisée.

• attention à ne pas associer des significations implicites aux valeurs nulles. C'est une bonne manière de créer des bugs difficiles à trouver !

Si possible, on essaye d’éviter des valeurs nulles : ils créent des problèmes dans les requêtes, où il faut faire attention à préciser si on veut des nulles ou pas.

Un schéma

Le schéma est le design logique de nos données, notamment :

• la liste de relations et leurs attributs

• les liens sémantiques entre les relations

• les contraintes sur les données

Un schéma peut être représenté en différentes formes selon le type d'utilisation :

• formes textuelles (pour brièveté)

• formes graphiques (E-R diagram, schéma, etc.)

Il est rare, mais assez pratique d'utiliser une forme textuelle.

Exemple : dans un service SaaS, on pourrait imaginer plusieurs relations (ou tables) :

• user : contenant les attributs de chaque utilisateur qui se connecte au service SaaS

• client : contenant les attributs de chaque client du service SaaS

On peut écrire des relations en format textuel :

user(userId, gender, familyName, givenName, email)
client(clientId, name, status)
...

Avec ce format, on commence par le nom de la relation, et entre parenthèse la liste d'attributs associés par cette relation.

Imaginez qu'on aura maintenant un set de données provenant de la table user, qui est définie par la relation ci-dessus. On dit que ce set est une instance de la relation user.

Clés

Une superkey est un attribut, ou groupe d’attributs, qui identifie(nt) un tuple unique.

Dans une relation user, par exemple, il y aura peut-être plusieurs lignes avec le prénom Kevin. L'attribut givenName n'est donc pas une superkey parce qu'il n'est pas capable d'identifier une ligne unique.

En revanche, probablement, il existe l'attribut numéro de sécurité sociale, qui est bien unique pour chaque individu. Cet attribut est donc capable d'identifier une ligne unique. Il est une superkey.

Il existe aussi un attribut userId, qui est une UUID généré pour chaque utilisateur à son inscription. Cet attribut est aussi une superkey.

On pourrait aussi dire que la combinaison de familyName et givenName est une superkey.

Chaque superkey est un candidat pour le choix de clé primaire ou primary key. Cette clé à la contrainte forte : elle doit obligatoirement identifier une ligne unique, et ne jamais être nulle.

Il faut choisir, parmi la liste de candidats, la superkey qui deviendra la clé primaire.

Dans notre exemple, on choisit l'attribut userId pour être le primary key de la relation.

Simple ! Je génère juste un UUID pour chaque ligne de ma relation, et je le désigne la clé primaire systématiquement.

Pourquoi pas, mais normalement, on génère des IDs uniques seulement pour manque de superkey parmi les vrais attributs de l'entité en question. Sinon, on crée de l'information inutile, ou bien, on obscurcit la relation sémantique entre les attributs.

Clés étrangères

Un attribut peut référencer un tuple dans une autre relation.

La clé primaire de la relation référencée et utilisée comme valeur d’un attribut d’une autre relation (clé étrangère ou foreign key).

Exemple, une base qui stocke des scores par joueur :

player(player_id, family_name, given_name, age)
score(id, player_id, game_id, points)

player_id est la clé primaire de la relation player.

Dans la relation score, l’attribut player_id est une clé étrangère qui référence la relation player.

Une clé étrangère impose une contrainte clé étrangère (foreign key constraint) :

Nous ne pouvons pas préciser une valeur dans l’attribut player_id de score qui n’existe pas déjà dans la relation player.

Opérations

L'algèbre relationnelle précise donc un nombre d'opérations possible sur des relations.

Operation	Explication
Select	Sélectionner un sous-set de tuples d’une relation selon certains critères
Project	Choisir seulement certains attributs
Union	Fusionner 2 sets de tuples en ne dupliquant pas ceux qui sont identiques
Intersection	Fusionner 2 sets de tuples en prenant uniquement les tuples qui se trouvent dans les 2 sets
Difference	Fusionner 2 sets de tuples en gardant les tuples qui sont dans le premier set, mais pas dans le deuxième
Cartesian product	Fusionner 2 sets, en démultipliant chaque tuple dans le premier set avec chaque tuple du deuxième
Rename	Renommer les attributs sortants

Toutes ses opérations sont théoriques dans le domaine d'algèbre relationnelle. Ensuite, un SGBDR (avec un langage comme SQL) implémente ces principes théoriques.

Le Produit Cartésien

Imaginons un scénario suivant :

Pour un service SaaS, il peut y avoir plusieurs utilisateurs. Chaque utilisateur peut se connecter à la plateforme.

Le service SaaS a plusieurs clients.

Un utilisateur peut appartenir à plusieurs clients.

Par exemple, un intervenant indépendant peux intervenir pour un client1 et client2. Cet intervenant aura un seul identifiant unique, mais peut consulter ses interventions pour les deux clients différents.

Imaginons un schéma suivant :

client(clientId, name, status)
user(userId, gender, familyName, givenName, email)
animator(clientId, userId, status)

Le userId est une clé primaire pour la relation user, mais aussi une clé étrangère pour la relation animator.

Supposons, on a les instances suivantes (ou tables):

La relation user :

userId	gender	familyName	givenName
1	male	Glass	Kevin
2	female	Delacroix	Sabine
3	female	Descamps	Natalie

La relation animator :

clientId	userId	status
3	1	active
3	2	active
1	2	active
2	3	active

Souvent, on aimera lister le nom des intervenants. Il faudrait fusionner les deux relations.

Comment cela se fait ?

On commence par le produit cartésien, ou autrement exprimé :

user X animator

Il s'agit simplement de l'énumération exhaustive de toutes les combinaisons des lignes entre les deux relations. En pseudo-code :

for each user
  for each animator
     set+= { user.userId, user.gender, user.familyName, user.givenName, animator.clientId, animator.userId, animator.status }

Le résultat est le suivant :

userId	gender	familyName	givenName	clientId	userId	status
1	male	Glass	Kevin	3	1	active
1	male	Glass	Kevin	3	2	active
1	male	Glass	Kevin	1	2	active
1	male	Glass	Kevin	2	3	active
2	female	Delacroix	Sabine	3	1	active
2	female	Delacroix	Sabine	3	2	active
2	female	Delacroix	Sabine	1	2	active
2	female	Delacroix	Sabine	2	3	active
3	female	Descamps	Natalie	3	1	active
3	female	Descamps	Natalie	3	2	active
3	female	Descamps	Natalie	1	2	active
3	female	Descamps	Natalie	2	3	active

C'est le croisement naïf des données ! Mais ce n'est pas très utile.

Normalement, on va limiter les résultats in filtrant uniquement des lignes qui adhèrent à une contrainte, par exemple, via la clé étrangère :

user.userId = animator.userId

Le résultat est plus cohérent :

userId	gender	familyName	givenName	clientId	userId	status
1	male	Glass	Kevin	3	1	active
2	female	Delacroix	Sabine	3	2	active
2	female	Delacroix	Sabine	1	2	active
3	female	Descamps	Natalie	2	3	active

Ce dernier s'appelle une opération join.

Nous ne ferions jamais un Produit Cartésien dans nos requêtes.

En revanche, toutes les jointures de table (que ce soit via inner join ou bien juste via un select de plusieurs tables) commencent par un Produit Cartésien implicite.

Pour extraire des bonnes lignes, le SGBDR est obligé de traverser toutes les combinaisons possibles et ne retourner uniquement celles qui passent les contraintes.

Si on garde cette information en tête, on pourrait optimiser nos requêtes seulement en limitant le résultat du Produit Cartésien initial.