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 !
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.
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.
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
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
client1etclient2. 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 :
1
male
Glass
Kevin
2
female
Delacroix
Sabine
3
female
Descamps
Natalie
La relation animator :
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 animatorIl 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 :
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.userIdLe résultat est plus cohérent :
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.
Dernière mise à jour