Ein Kunde, vierzig Adressen
Ein kleiner Betrieb führt alle Bestellungen auf einem großen Blatt. Jede Zeile wiederholt Namen und Adresse des Kunden.
Ein Kunde zieht um. Jemand ändert die Adresse in achtunddreißig von vierzig Zeilen und übersieht zwei.
Nun hat der Betrieb zwei Adressen für eine Person und weiß nicht, welche stimmt.
Am Ende dieser Lektion
Notiz
Ich kann eine flache Tabelle in einen Entwurf ohne Wiederholung aufteilen.
Schlüsselwörter
| Begriff | Was er bedeutet |
|---|---|
| Redundanz | dieselbe Angabe, an mehr als einer Stelle gespeichert |
| Anomalie | ein Fehler durch Redundanz, wenn sich Daten ändern |
| Abhängigkeit | der Wert einer Spalte legt den Wert einer anderen fest |
| Atomar | nur ein Wert, nie eine Liste |
| Normalform | eine benannte Stufe sauberen Entwurfs, von der ersten bis zur dritten |
| Normalisierung | Tabellen aufteilen, bis die Redundanz weg ist |
Die Zellen, die Lernende ausfüllen, bleiben in dieser Vorschau leer.
Wiederholung verursacht drei Fehler
In einer flachen Tabelle steht dieselbe Angabe oft. Das verursacht drei Fehler, die Anomalien heißen.
Eine Änderungsanomalie: Sie ändern eine Kopie und übersehen die anderen.
Eine Einfügeanomalie: Sie können einen Kunden erst eintragen, wenn er etwas bestellt.
Eine Löschanomalie: Mit der letzten Bestellung verschwindet auch der einzige Eintrag des Kunden.
Ein Beispiel: eine flache Tabelle
| bestell_nr | kunde | stadt | land | artikel | menge |
|---|---|---|---|---|---|
| 1 | Anna | Hamburg | Deutschland | Stift | 2 |
| 1 | Anna | Hamburg | Deutschland | Lineal | 1 |
| 2 | Ben | Wien | Österreich | Stift | 5 |
| 3 | Anna | Hamburg | Deutschland | Block | 1 |
Die Zellen, die Lernende ausfüllen, bleiben in dieser Vorschau leer.
Sehen Sie, was ein Entwurf spart
Die Shop-Datenbank ist schon in Tabellen aufgeteilt. Sie bauen eine flache Ansicht eines Kunden und zählen, was sie wiederholen würde.
Starten Sie zuerst die zwei Einrichtungszeilen.
Das Zertifikat des Servers annehmen
printf '[client]\nssl-verify-server-cert=0\n' > ~/.my.cnfHier angezeigt, nicht ausgeführt. Erstellen Sie ein kostenloses Konto, um „Ausführen“ zu drücken und ihn im Terminal Ihres eigenen Labs einzugeben.
Eine Abkürzung für den Shop
alias sql='mariadb -h db -u student -pstudent shop -t -e'Hier angezeigt, nicht ausgeführt. Erstellen Sie ein kostenloses Konto, um „Ausführen“ zu drücken und ihn im Terminal Ihres eigenen Labs einzugeben.
Ein Kunde als flache Tabelle
sql "SELECT customers.first_name, customers.city, orders.id AS order_no, products.name AS product FROM customers JOIN orders ON orders.customer_id = customers.id JOIN order_items ON order_items.order_id = orders.id JOIN products ON products.id = order_items.product_id WHERE customers.id = 33 ORDER BY orders.id, products.name LIMIT 5"Hier angezeigt, nicht ausgeführt. Erstellen Sie ein kostenloses Konto, um „Ausführen“ zu drücken und ihn im Terminal Ihres eigenen Labs einzugeben.
Zeilen gegen Kunden
sql "SELECT COUNT(*) AS flat_rows, COUNT(DISTINCT orders.customer_id) AS customers FROM orders JOIN order_items ON order_items.order_id = orders.id"Hier angezeigt, nicht ausgeführt. Erstellen Sie ein kostenloses Konto, um „Ausführen“ zu drücken und ihn im Terminal Ihres eigenen Labs einzugeben.
Die erste und die zweite Normalform
Erste Normalform (1NF): Jede Zelle enthält einen Wert. Keine Liste wie „Stift, Lineal“ in einer Zelle und keine Spalten artikel1, artikel2, artikel3.
Zweite Normalform (2NF): Jede Spalte hängt vom ganzen Schlüssel ab. Besteht der Schlüssel aus bestell_nr und artikel, hängt eine Angabe zum Kunden nur von bestell_nr ab. Lagern Sie sie aus.
Ein Beispiel: die flache Tabelle teilen
| Neue Tabelle | Spalten | Schlüssel | Enthält |
|---|---|---|---|
| bestellungen | bestell_nr, kunden_id | bestell_nr | Eine Zeile je Bestellung |
| positionen | bestell_nr, artikel_id, menge | bestell_nr und artikel_id | Eine Zeile je Position |
| kunden | kunden_id, name, stadt_id | kunden_id | Eine Zeile je Person |
| staedte | stadt_id, stadt, land | stadt_id | Eine Zeile je Stadt |
Die Zellen, die Lernende ausfüllen, bleiben in dieser Vorschau leer.
Die dritte Normalform entfernt eine versteckte Abhängigkeit
Dritte Normalform (3NF): Keine Spalte hängt von einer anderen Spalte ab, die nicht der Schlüssel ist.
Die id eines Kunden legt seine stadt fest. Die stadt legt das land fest. Das Land hängt also nur über die Stadt vom Schlüssel ab. Das ist eine versteckte Abhängigkeit.
Lagern Sie stadt und land in eine eigene Tabelle aus, und der Kunde hält nur noch eine Städtenummer.
Hat eine Stadt je zwei Länder?
sql "SELECT COUNT(*) AS cities_with_two_countries FROM (SELECT city FROM customers GROUP BY city HAVING COUNT(DISTINCT country) > 1) AS x"Hier angezeigt, nicht ausgeführt. Erstellen Sie ein kostenloses Konto, um „Ausführen“ zu drücken und ihn im Terminal Ihres eigenen Labs einzugeben.
Städte gegen Länder
sql "SELECT COUNT(DISTINCT city) AS cities, COUNT(DISTINCT country) AS countries FROM customers"Hier angezeigt, nicht ausgeführt. Erstellen Sie ein kostenloses Konto, um „Ausführen“ zu drücken und ihn im Terminal Ihres eigenen Labs einzugeben.
Die Städte, die am häufigsten stehen
sql "SELECT city, country, COUNT(*) AS customers FROM customers GROUP BY city, country ORDER BY customers DESC, city LIMIT 3"Hier angezeigt, nicht ausgeführt. Erstellen Sie ein kostenloses Konto, um „Ausführen“ zu drücken und ihn im Terminal Ihres eigenen Labs einzugeben.
Ein häufiger Fehler
Warnung
Viele glauben, mehr Tabellen seien immer besser. Das Aufteilen beseitigt Wiederholung, aber jede Teilung braucht einen Join, um die Daten wieder zu lesen. Hören Sie bei der dritten Normalform auf, außer Sie haben einen gemessenen Grund weiterzumachen.
Probieren Sie es: jeden Kunden einmal
-- Die Kunden, jeder nur einmal.
SELECT DISTINCT kunde, stadt
FROM bestellzeilen
ORDER BY kunde;
-- Ändern Sie die Abfrage: jede Stadt mit ihrem Land, nur einmal.
Weiterführend: den Entwurf wirklich bauen
Bauen Sie die Tabellen aus dem Beispiel in Ihrer Datenbank student_depot: staedte, kunden, artikel, bestellungen und positionen, jede mit Primärschlüssel und Fremdschlüsseln.
Tragen Sie die sechs Zeilen der flachen Tabelle aus dem SQL-Block ein, verteilt auf die neuen Tabellen.
Setzen Sie die flache Ansicht dann mit Joins wieder zusammen. Kommen genau die sechs Zeilen heraus?