Les concepts relationnels

Ce que vous saurez faire

Trouvez les colonnes qui relient les trois tables et vérifiez si elles sont fiables. Une clé non unique, ou qui ne renvoie à rien, produit ensuite des comptes erronés sans erreur.

Carnet JupyterFaites cette leçon dans un carnet JupyterColab · Kaggle · GitHub · en anglais

Le carnet (notebook) Jupyter contient chaque requête, tâche et vérification de cette leçon, avec les instructions, les petites tables et les exemples de contraintes. Sa première cellule télécharge les données d’exercice et crée la base de données. Vous n’avez donc besoin de rien sur votre ordinateur. Lisez les explications ici et exécutez le code dans le carnet.

Ouvrir dans ColabOuvrir dans KaggleVoir sur GitHub

Le carnet est en anglais. Colab et Kaggle ouvrent votre propre copie. Il faut un compte Google ou Kaggle gratuit. Vous êtes bloqué ? Consultez le carnet de solution.

Avant de commencerCe qu’il faut savoir et avoir sous la mainEnviron 50 minutes

Ce que vous devez savoir

• Les lignes, les colonnes, les valeurs et NULL, vus dans Les éléments d’un jeu de données : lignes, colonnes et valeurs.
• La granularité de chaque table : un client, un billet de soutien, un événement, vue dans Que représente une ligne?.
• SELECT … FROM … LIMIT et WHERE, vus dans Exécuter votre première requête et À vous : les billets d’un client. COUNT(*), vu dans Quelle est la taille de chaque table?.

Ce qu’il vous faut sur votre ordinateur

• Rien. Tout le SQL de cette leçon s’exécute dans votre navigateur, et le carnet s’exécute dans Colab ou Kaggle. Vous créerez la base de données sur votre ordinateur dans la prochaine leçon.

Les zones d’exécution du navigateur utilisent SQLite 3.49.1. Le carnet a été testé dans Jupyter.

Trois petites tables

Les premières zones d’exécution de cette leçon n’utilisent pas la base de données d’exercice. Elles utilisent trois petites tables créées pour cette leçon, afin que vous puissiez voir chaque ligne. Elles contiennent 3 clients, 5 billets et 5 événements de résultat.

customers

customer_id	segment
C-0001	home
C-0002	trade
C-0003	business

tickets

ticket_id	customer_id	team
T-20001	C-0001	delivery
T-20002	C-0002	returns
T-20003	C-0002	payment
T-20004	C-0002	delivery
T-20005	C-0001	account

outcomes

outcome_id	ticket_id	status
O-90001	T-20001	resolved
O-90002	T-20002	escalated
O-90003	T-20002	resolved
O-90004	T-20003	resolved
O-90005	T-20005	resolved

Exécutez du SQL dans votre navigateur

Essayez : les petites tables

Exécutez la requête. Vous devriez voir les 5 billets. Remplacez ensuite tickets par outcomes, puis par customers, et exécutez chaque version.

Requête SQL (vous pouvez la modifier)SELECT * FROM tickets;

Les clés

Clé primaire. Une clé primaire est une colonne dont la valeur diffère dans chaque ligne et n’est jamais NULL. Elle identifie une ligne. Dans customers, customer_id est la clé primaire : C-0002 désigne exactement un client. Dans tickets, c’est ticket_id, et dans outcomes, c’est outcome_id.

Clé étrangère. Une clé étrangère est une colonne dont les valeurs renvoient à la clé primaire d’une autre table. Dans tickets, la colonne customer_id est une clé étrangère. La valeur C-0002 signifie « cherchez C-0002 dans customers ». Dans outcomes, ticket_id est une clé étrangère qui renvoie à tickets.

Les deux types de clés suivent des règles différentes :

	Clé primaire	Clé étrangère
Exemple	customers.customer_id	tickets.customer_id
Une valeur peut-elle apparaître deux fois?	Non. Chaque valeur désigne une ligne.	Oui. C-0002 apparaît dans trois billets.
Peut-elle être NULL?	Non	Oui, si l’absence de correspondance est permise. Un billet d’invité n’a pas de client.
Quelle condition faut-il respecter?	Unique et non NULL	Chaque valeur non NULL existe dans l’autre table

La notation customers.customer_id signifie « la colonne customer_id de la table customers ». Elle est utile quand deux tables ont une colonne du même nom.

Une relation un-à-plusieurs

Regardez C-0002 dans les petites tables. Un client a trois billets : T-20002, T-20003 et T-20004. Chacun de ces billets a exactement un client. C’est une relation un-à-plusieurs : une ligne d’un côté, zéro ligne ou plus de l’autre.

Client	Ses billets	Nombre
C-0001	T-20001, T-20005	2
C-0002	T-20002, T-20003, T-20004	3
C-0003	aucun	0

La même relation existe au niveau suivant. Un billet a zéro événement de résultat ou plus. T-20002 en a deux (transmis à un niveau supérieur, puis résolu). T-20004 n’en a pas encore, car il est toujours ouvert.

Exécutez du SQL dans votre navigateur

Essayez : le côté « plusieurs »

Prédisez le nombre de lignes, puis exécutez la requête. Vous devriez voir 3 lignes. Modifiez ensuite la requête pour afficher les événements du billet T-20002 dans outcomes. Vous devriez voir 2 lignes.

Requête SQL (vous pouvez la modifier)SELECT * FROM tickets WHERE customer_id = 'C-0002';

La clé étrangère se trouve toujours du côté « plusieurs ». tickets contient customer_id, et non l’inverse. Une ligne de client ne peut pas contenir une liste de billets. Par contre, chaque billet peut désigner son unique client.

Le diagramme entité-association

Un diagramme entité-association (ERD) représente les tables, leurs clés et les relations entre elles. Voici celui de la base de données d’exercice.

Le diagramme entité-association de la base de données d'exercice : customers un-à-plusieurs tickets, tickets un-à-plusieurs événements de résultat. Chaque rectangle indique la granularité de sa table et les colonnes clés.

Description textuelle du diagramme

Le diagramme contient trois rectangles. customers (une ligne représente un client) contient customer_id, la clé primaire (PK), ainsi que segment, region et joined_on. tickets (une ligne représente un billet) contient ticket_id (PK), created_at et closed_at. Une note indique que 14 identifiants apparaissent deux fois. Sa colonne customer_id est une clé étrangère (FK), avec la note « NULL pour les invités ». outcomes (une ligne représente un événement) contient outcome_id (PK), ticket_id (FK), recorded_at et status. Le diagramme montre seulement certaines colonnes de tickets et outcomes. Une ligne portant l’étiquette « a zéro ou plus » relie customers à tickets. Cela signifie qu’un billet a zéro ou un client, et qu’un client a zéro billet ou plus. Une deuxième ligne portant la même étiquette relie tickets à outcomes. Cela signifie qu’un événement a exactement un billet, et qu’un billet a zéro événement ou plus.

Lisez les extrémités de chaque ligne. La fourche (trois petits traits) signifie « plusieurs ». Le cercle signifie « zéro est permis ». Un petit trait signifie « un ». La ligne entre tickets et outcomes indique donc que chaque événement appartient à exactement un billet. Un billet a zéro événement ou plus. Entre customers et tickets, l’extrémité du côté client a aussi un cercle, car un billet d’invité n’a pas de client.

Le rôle d’une contrainte

Une base de données peut faire respecter les règles des clés pour vous. Une contrainte est une règle du schéma que la base de données vérifie à chaque modification. PRIMARY KEY (clé primaire) refuse une deuxième ligne avec la même valeur. FOREIGN KEY (clé étrangère) refuse une valeur qui n’existe pas dans l’autre table.

Exécutez le code de la prochaine zone. Il crée deux tables avec des contraintes, puis insère le même billet deux fois.

Exécutez du SQL dans votre navigateur

Essayez : les contraintes refusent les lignes incorrectes

Exécutez la requête. Vous devriez voir Error: UNIQUE constraint failed: tickets.ticket_id (erreur : la contrainte d’unicité de tickets.ticket_id n’est pas respectée). La base de données a refusé le deuxième T-20001. Faites ensuite ces modifications, une à la fois, et exécutez le code après chacune :

1. Supprimez la deuxième ligne INSERT INTO tickets. Vous voyez 1 ligne.
2. Ajoutez INSERT INTO tickets VALUES ('T-20002', 'C-0009', 'returns'); avant le SELECT. Vous voyez Error: FOREIGN KEY constraint failed (erreur : la contrainte de clé étrangère n’est pas respectée), car C-0009 n’est pas dans customers.
3. Remplacez 'C-0009' par NULL. La ligne est acceptée. Une clé étrangère permet NULL : c’est un billet d’invité.
4. Remplacez NULL par 'C-0009', puis supprimez la première ligne, PRAGMA foreign_keys = ON;. La ligne incorrecte est acceptée.

Requête SQL (vous pouvez la modifier)PRAGMA foreign_keys = ON; CREATE TABLE customers ( customer_id TEXT NOT NULL PRIMARY KEY, segment TEXT ); CREATE TABLE tickets ( ticket_id TEXT NOT NULL PRIMARY KEY, customer_id TEXT, team TEXT, FOREIGN KEY (customer_id) REFERENCES customers (customer_id) ); INSERT INTO customers VALUES ('C-0001', 'home'), ('C-0002', 'trade'); INSERT INTO tickets VALUES ('T-20001', 'C-0001', 'delivery'); INSERT INTO tickets VALUES ('T-20001', 'C-0001', 'delivery'); SELECT * FROM tickets;

L’étape 4 montre une règle de SQLite. SQLite vérifie les clés étrangères seulement après PRAGMA foreign_keys = ON;. Vous devez exécuter cette instruction à chaque nouvelle connexion. Sans elle, FOREIGN KEY est seulement une note dans le schéma. PostgreSQL les vérifie toujours.

Écrivez NOT NULL à côté de PRIMARY KEY dans SQLite

À cause d’une ancienne décision de conception, SQLite accepte NULL dans une colonne PRIMARY KEY de type TEXT. La plupart des autres bases de données ne le permettent pas. Le code ci-dessus utilise NOT NULL PRIMARY KEY pour éviter ce problème.

Pourquoi l’exportation n’a pas de contraintes

Regardez maintenant la vraie base de données. Ses tables ont des types, mais aucune contrainte PRIMARY KEY et aucune contrainte FOREIGN KEY. C’est volontaire. L’exportation est une copie des données d’un autre système, et elle ne garantit pas les clés. Si Mei ajoutait des contraintes, la première ligne incorrecte arrêterait le chargement. Elle n’aurait alors aucune donnée à examiner. Elle charge donc chaque ligne telle quelle, puis vérifie elle-même les clés avec des requêtes. La règle pour ce cours : l’exportation ne garantit pas les clés, alors vous les vérifiez vous-même.

Il y a deux vérifications pour chaque clé :

1. La clé primaire est-elle unique? Comparez le nombre de lignes au nombre de valeurs différentes.
2. Chaque clé étrangère renvoie-t-elle à une ligne? Cherchez les valeurs absentes de l’autre table. Une ligne dont la clé étrangère ne renvoie à rien est une ligne orpheline.

Exemple détaillé : vérifier ticket_id

Mei commence par tickets. Si ticket_id est une vraie clé primaire, le nombre de lignes égale le nombre d’identifiants différents. COUNT(DISTINCT ticket_id) compte les valeurs différentes.

Exécutez du SQL dans votre navigateur

Essayez : ticket_id est-il unique?

Prédisez : les deux nombres sont-ils égaux? Exécutez ensuite la requête. Vous devriez voir 1 417 et 1 403.

Requête SQL (vous pouvez la modifier)SELECT COUNT(*) AS ticket_rows, COUNT(DISTINCT ticket_id) AS different_ids FROM tickets;

Les deux nombres ont un écart de 14. Certains identifiants apparaissent plus d’une fois. Mei en fait la liste. Vous apprendrez GROUP BY et HAVING dans le module 2. Pour l’instant, lisez la requête ainsi : « pour chaque identifiant de billet, comptez ses lignes et gardez les identifiants ayant plus d’une ligne ».

Exécutez du SQL dans votre navigateur

Essayez : quels identifiants apparaissent deux fois?

Exécutez la requête. Vous devriez voir 14 lignes, de T-10031 à T-11291, chacune avec un row_count (nombre de lignes) de 2. Remplacez ensuite la requête par SELECT * FROM tickets WHERE ticket_id = 'T-10031'; et comparez les deux lignes.

Requête SQL (vous pouvez la modifier)SELECT ticket_id, COUNT(*) AS row_count FROM tickets GROUP BY ticket_id HAVING COUNT(*) > 1;

Les deux lignes de T-10031 sont identiques dans les 11 colonnes. Ce sont des lignes exactement identiques. Mei pose la question à Grace. L’outil d’exportation écrit les données par pages, et deux pages se chevauchaient. Ces lignes représentent donc un billet écrit deux fois, et non deux billets différents avec le même identifiant. SELECT DISTINCT * garde une copie de chaque ligne différente. SELECT COUNT(*) FROM (SELECT DISTINCT * FROM tickets) renvoie 1 403, soit le nombre d’identifiants différents. Cela confirme que les 14 doublons sont des copies exactes.

Mei écrit dans ses notes :

Vérification	Résultat	Signification
Lignes dans tickets	1 417	Lignes de billets, pas billets
Valeurs différentes de ticket_id	1 403	Billets
Identifiants ayant plus d’une ligne	14, chacun deux fois	Doublons exacts causés par un chevauchement de pages d’exportation
Lignes différentes (SELECT DISTINCT *)	1 403	Chaque doublon est une copie exacte

Elle ne supprime pas les doublons maintenant. Le module 4 les retire dans une étape de nettoyage qui consigne les éléments retirés. Pour l’instant, elle sait qu’il y a 1 417 lignes et 1 403 billets.

Exemple détaillé : les billets orphelins

Ensuite, elle vérifie la clé étrangère tickets.customer_id. Mei cherche les lignes de billets dont le client n’est pas dans customers. NOT IN (SELECT customer_id FROM customers) signifie « absent de la liste des identifiants de clients ».

Exécutez du SQL dans votre navigateur

Essayez : les billets dont le client manque

Exécutez la requête. Vous devriez voir 9 lignes. Les identifiants de clients vont de C-0241 à C-0244. Remplacez ensuite la ligne WHERE par WHERE customer_id IS NULL, puis exécutez à nouveau la requête. Vous devriez voir 12 lignes.

Requête SQL (vous pouvez la modifier)SELECT ticket_id, customer_id FROM tickets WHERE customer_id NOT IN (SELECT customer_id FROM customers);

Il y a deux groupes différents, et la première requête n’en trouve qu’un :

Groupe	Lignes	Valeur de customer_id	Signification (selon Grace)
Comptes supprimés	9	C-0241, C-0242, C-0243, C-0244	Le client a fermé son compte. La ligne du client a disparu, mais les billets restent.
Invités	12	NULL	Le client a acheté sans compte.

Pourquoi NOT IN ne trouve-t-il pas les 12 invités? Une comparaison avec NULL n’est jamais vraie : SQL ne peut pas déterminer si « aucune valeur » figure dans la liste. Ainsi, NULL NOT IN (...) ne garde pas la ligne. Il faut utiliser IS NULL pour ces lignes. Le module 2 explique cette règle. Ici, retenez ceci : une vérification des lignes orphelines doit aussi compter les NULL.

Les 9 lignes orphelines sont de vrais billets. Plus tard, une requête qui relie les billets aux clients peut les perdre sans erreur. Vous verrez cela dans le module 2.

Exercice guidé : vérifier customer_id dans customers

1. Dans la zone ci-dessous, exécutez la requête. Elle compare le nombre de lignes au nombre d’identifiants de clients différents.
2. Vérifiez le résultat : les deux nombres sont 240.
3. Ajoutez une troisième colonne au SELECT : COUNT(customer_id) AS not_null. COUNT(column) compte seulement les valeurs non NULL.
4. Exécutez à nouveau la requête. Vous devriez voir 240, 240 et 240. Donc, customer_id est unique et jamais NULL. Cette colonne peut servir de clé primaire.

Exécutez du SQL dans votre navigateur

Essayez : customer_id est-il une clé primaire?

Suivez les étapes ci-dessus.

Requête SQL (vous pouvez la modifier)SELECT COUNT(*) AS customer_rows, COUNT(DISTINCT customer_id) AS different_ids FROM customers;

À votre tour : les événements de résultat orphelins

Chaque événement de résultat doit appartenir à un billet. Écrivez une requête qui affiche toutes les colonnes des événements dont le ticket_id est absent de tickets. Faites d’abord une prédiction : le résultat sera-t-il vide?

Exécutez du SQL dans votre navigateur

À votre tour : les événements sans billet

Utilisez la requête sur les billets orphelins ci-dessus comme modèle. La vérification compare votre résultat au nôtre.

Requête SQL (vous pouvez la modifier)-- Write your query here

Afficher l’exemple de réponse

SELECT *
FROM outcomes
WHERE ticket_id NOT IN (SELECT ticket_id FROM tickets);

Le résultat contient 4 lignes, pour les billets T-9990, T-9991, T-9992 et T-9993. Aucun de ces identifiants n’est dans tickets. Il n’y a aucun NULL dans outcomes.ticket_id (vous pouvez vérifier avec IS NULL : 0 ligne). Ces 4 lignes sont donc toutes les lignes orphelines. Ce sont des événements de billets que l’exportation n’a pas inclus.

Exercice autonome : outcome_id est-il unique?

Vérifiez la clé primaire de outcomes comme vous l’avez fait pour ticket_id. Écrivez une requête qui renvoie deux nombres : le nombre de lignes et le nombre de valeurs différentes de outcome_id.

Exécutez du SQL dans votre navigateur

À vous de jouer : vérifier outcome_id

Le résultat contient une ligne et deux colonnes. Le nom des colonnes n’a pas d’importance pour cette vérification.

Requête SQL (vous pouvez la modifier)-- Write your query here

Afficher l’exemple de réponse

SELECT COUNT(*) AS event_rows,
       COUNT(DISTINCT outcome_id) AS different_ids
FROM outcomes;

Les deux nombres sont 1 874, donc outcome_id est unique. Une clé unique ne signifie pas que les données ne contiennent aucune répétition. Le module 3 montre que certains événements ont été enregistrés deux fois avec un nouveau outcome_id. L’unicité est une vérification, pas une preuve que les données sont propres.

Erreur courante

Faire confiance à une clé sans la vérifier

Symptôme : Tomás compte les billets de soutien par équipe avec COUNT(*) sur tickets. Le total est de 1 417. L’écran du service de soutien de Grace indique 1 403 billets pour la même période. Personne n’a vu d’erreur.

Cause : Tomás a supposé que ticket_id était une clé primaire, parce que son nom se termine par _id. L’exportation n’a aucune contrainte, et 14 billets apparaissent deux fois. Chacun a été compté deux fois.

Solution :

1. Avant d’utiliser une colonne comme clé, comparez COUNT(*) à COUNT(DISTINCT key).
2. Si les résultats diffèrent, listez les valeurs répétées et examinez les lignes.
3. Décidez avec les personnes qui connaissent les données. Ici, les lignes sont des copies exactes, donc vous comptez chaque billet une seule fois. Notez cette décision.

Dans le travail en IA : des lignes en double peuvent se retrouver dans les données d’entraînement et de test en même temps. Le modèle est alors testé sur des lignes qu’il a déjà vues, et le score paraît meilleur qu’il ne l’est. Le module 5 présente ce type de problème.

Vérifiez votre compréhension

Dans les petites tables, pourquoi customer_id est-il une clé primaire dans customers, mais pas dans tickets?

Afficher la réponse

Dans customers, chaque customer_id désigne une seule ligne : il est unique et n’est jamais NULL. Dans tickets, customer_id est une clé étrangère. Il pointe vers un client, et un même client peut avoir plusieurs billets. La valeur peut donc se répéter (C-0002 apparaît trois fois). Elle peut aussi être NULL pour un invité.

Vérifiez votre compréhension

Vous exécutez WHERE customer_id NOT IN (SELECT customer_id FROM customers) et obtenez 9 lignes. Est-il correct de dire que 9 billets n'ont aucun client connu?

Afficher la réponse

Non. La requête trouve seulement les 9 lignes qui pointent vers un client supprimé. Une comparaison avec NULL n’est jamais vraie. Les 12 lignes d’invités contenant NULL ne figurent donc pas dans le résultat. Vous les trouvez avec IS NULL. Ainsi, 21 lignes de billets n’ont aucune ligne de client correspondante : 9 comptes supprimés et 12 invités.

Vérifiez votre compréhension

SQLite a accepté un billet pour le client C-0009, même si la table a une contrainte FOREIGN KEY et que C-0009 n'existe pas. Quelle est la raison la plus probable?

Afficher la réponse

Les vérifications des clés étrangères n’étaient pas activées. SQLite vérifie les clés étrangères seulement après PRAGMA foreign_keys = ON;, et ce réglage s’applique à une seule connexion. Sans ce réglage, la contrainte figure dans le schéma, mais SQLite ne la vérifie pas.

Récapitulatif

• Une clé primaire identifie une seule ligne : elle est unique et n’est jamais NULL. Une clé étrangère pointe vers la clé primaire d’une autre table. Elle peut se répéter et être NULL.
• Un-à-plusieurs : un client a zéro billet ou plus, et un billet a zéro événement ou plus. La clé étrangère se trouve du côté « plusieurs ».
• Une contrainte fait rejeter les lignes incorrectes par la base de données. Dans SQLite, les clés étrangères sont vérifiées seulement avec PRAGMA foreign_keys = ON;.
• L’exportation n’a aucune contrainte, donc vous vérifiez les clés vous-même : COUNT(*) comparé à COUNT(DISTINCT key), NOT IN pour les lignes orphelines et IS NULL pour les valeurs manquantes.
• Les données réelles : 1 417 lignes de billets, mais 1 403 billets (14 doublons exacts). 9 lignes de billets pointent vers des clients supprimés, et 12 n’ont aucun client. 4 événements pointent vers des billets absents de l’exportation.

Ensuite, vous créez la même base de données sur votre ordinateur et exécutez des requêtes enregistrées dans des fichiers.

Leçon suivante : Préparer une base de données d'exercice