Une ville écrite de deux façons
Une société de vente garde ses commandes dans une seule grande table. La ville d'un client y est écrite sur chaque ligne.
Un jour, une ligne dit « Lyon » et une autre dit « Lyon 3 ». Les statistiques par ville comptent maintenant deux villes.
Une bonne conception évite d'écrire deux fois le même fait.
À la fin de cette leçon
Remarque
Je peux découper une table à plat en une conception qui évite la répétition.
Mots clés
| Mot | Ce que ça veut dire | Exemple |
|---|---|---|
| Redondance | le même fait écrit à plusieurs endroits | |
| Anomalie | une erreur causée par la redondance, à l'ajout, au changement ou au retrait | |
| Dépendance | une colonne dont la valeur est fixée par une autre | ville fixe pays |
| Normalisation | découper une table pour retirer la redondance | |
| Forme normale | un niveau de règles de conception, de la première à la troisième | |
| Table à plat | une grande table qui mélange plusieurs sortes de choses |
Les cellules que les étudiants remplissent restent vides dans cet aperçu.
Fabriquons une table à plat
La base de la boutique est déjà bien conçue : clients, commandes, articles et lignes de commande sont dans des tables séparées.
Pour sentir ce que cela évite, vous allez recoller ces tables en une seule, dans votre base student_plat, pour deux clients. Rien n'est modifié dans la boutique.
Accepter le certificat du serveur
printf '[client]\nssl-verify-server-cert=0\n' > ~/.my.cnfAffichée ici, non exécutée. Créez un compte gratuit pour appuyer sur Exécuter et la saisir dans le terminal de votre propre labo.
Un nom court pour la boutique
alias sql='mariadb -h db -u student -pstudent shop -t -e'Affichée ici, non exécutée. Créez un compte gratuit pour appuyer sur Exécuter et la saisir dans le terminal de votre propre labo.
Créer votre base
sql "CREATE DATABASE IF NOT EXISTS student_plat"Affichée ici, non exécutée. Créez un compte gratuit pour appuyer sur Exécuter et la saisir dans le terminal de votre propre labo.
Jeter une ancienne table
sql "DROP TABLE IF EXISTS student_plat.plat"Affichée ici, non exécutée. Créez un compte gratuit pour appuyer sur Exécuter et la saisir dans le terminal de votre propre labo.
Recoller quatre tables en une seule
sql "CREATE TABLE student_plat.plat AS SELECT orders.id AS commande, customers.first_name AS client, customers.city AS ville, customers.country AS pays, products.name AS produit, order_items.quantity AS quantite FROM orders JOIN customers ON customers.id = orders.customer_id JOIN order_items ON order_items.order_id = orders.id JOIN products ON products.id = order_items.product_id WHERE customers.id IN (3, 16)"Affichée ici, non exécutée. Créez un compte gratuit pour appuyer sur Exécuter et la saisir dans le terminal de votre propre labo.
Regarder les premières lignes
sql "SELECT * FROM student_plat.plat ORDER BY commande, produit LIMIT 6"Affichée ici, non exécutée. Créez un compte gratuit pour appuyer sur Exécuter et la saisir dans le terminal de votre propre labo.
Des lignes, mais peu de clients
sql "SELECT COUNT(*) AS lignes, COUNT(DISTINCT client) AS clients FROM student_plat.plat"Affichée ici, non exécutée. Créez un compte gratuit pour appuyer sur Exécuter et la saisir dans le terminal de votre propre labo.
La répétition produit trois anomalies
Quand un fait est écrit plusieurs fois, trois choses tournent mal.
Mise à jour : on change la ville sur une ligne et pas sur les autres. Insertion : on ne peut pas noter un client qui n'a encore rien commandé. Suppression : en retirant sa dernière commande, on efface aussi le client.
Provoquons la première, sur votre copie.
Changer la ville sur une seule commande
sql "UPDATE student_plat.plat SET ville = 'Lyon' WHERE commande = 14"Affichée ici, non exécutée. Créez un compte gratuit pour appuyer sur Exécuter et la saisir dans le terminal de votre propre labo.
Combien de villes pour chaque client ?
sql "SELECT client, COUNT(DISTINCT ville) AS villes FROM student_plat.plat GROUP BY client ORDER BY client"Affichée ici, non exécutée. Créez un compte gratuit pour appuyer sur Exécuter et la saisir dans le terminal de votre propre labo.
Quelles villes, dans quels pays ?
sql "SELECT DISTINCT ville, pays FROM student_plat.plat ORDER BY ville"Affichée ici, non exécutée. Créez un compte gratuit pour appuyer sur Exécuter et la saisir dans le terminal de votre propre labo.
Trois formes normales, trois questions
Première forme : chaque case contient une seule valeur, et il n'y a pas de colonnes répétées comme produit1, produit2.
Deuxième forme : chaque colonne dépend de toute la clé, pas d'une moitié.
Troisième forme : chaque colonne dépend de la clé et de rien d'autre. Si ville fixe pays, le pays n'a pas sa place à côté du client.
Exemple : la table à plat découpée
| Colonne | Dépend de | Problème dans la table à plat | Table finale |
|---|---|---|---|
| client | commande | Seulement une moitié de la clé (commande, produit) | commandes |
| prix | produit | Seulement une moitié de la clé | produits |
| pays | ville | Dépend d'une colonne qui n'est pas la clé | villes |
| quantite | commande et produit | Aucun : dépend de toute la clé | lignes |
Les cellules que les étudiants remplissent restent vides dans cet aperçu.
Une ville a-t-elle jamais deux pays ?
sql "SELECT COUNT(*) AS villes_ambigues FROM (SELECT city FROM customers GROUP BY city HAVING COUNT(DISTINCT country) > 1) AS x"Affichée ici, non exécutée. Créez un compte gratuit pour appuyer sur Exécuter et la saisir dans le terminal de votre propre labo.
Un pays a-t-il plusieurs villes ?
sql "SELECT country, COUNT(DISTINCT city) AS villes FROM customers GROUP BY country ORDER BY country"Affichée ici, non exécutée. Créez un compte gratuit pour appuyer sur Exécuter et la saisir dans le terminal de votre propre labo.
Une erreur fréquente
Attention
Beaucoup pensent que plus il y a de tables, meilleure est la conception. Chaque table de plus coûte une jointure à chaque lecture. On découpe pour retirer un fait écrit deux fois, pas pour découper.
À vous : compter la répétition
-- Combien de fois la ville de chaque client est-elle écrite ?
SELECT client, ville, COUNT(*) AS lignes
FROM ventes
GROUP BY client, ville
ORDER BY client;
-- Chaque ligne en plus est une chance d'écrire la ville autrement.
À vous : extraire une table
-- La table des villes, obtenue en retirant les doublons.
SELECT DISTINCT ville, pays
FROM ventes
ORDER BY ville;
-- Faites la même chose pour les produits : SELECT DISTINCT produit, prix ...
Pour aller plus loin
Relancez les Runs depuis la suppression de la table, pour retrouver une copie sans anomalie. Puis découpez-la vous-même dans student_plat.
Créez villes, clients, commandes et lignes avec CREATE TABLE ... AS SELECT DISTINCT. Comptez les lignes de chaque table. Recollez enfin les quatre tables par des jointures : vous devez retrouver autant de lignes que dans plat.