Normalisierung

Kurz gesagt: Die Normalisierung zerlegt Daten in Tabellen, sodass jede Tatsache an genau einer Stelle steht. Das beseitigt Änderungs-, Einfüge- und Löschanomalien.

Problem

Excel ist in der Datenbank gelandet

Erinnerst du dich an die Bestelltabelle aus der ersten Lektion des Kurses? Jemand hat sie so, wie sie war, in die Datenbank übernommen: eine Zeile für jedes Produkt einer Bestellung, daneben das Datum, der Kunde, seine E-Mail und seine Stadt.

flat_orders

• order_idinteger *
• ordered_attext *
• customertext *
• emailtext *
• citytext
• producttext *
• pricereal *
• quantityinteger *

SQLleere Datenbank

SELECT * FROM flat_orders;

SELECT * FROM flat_orders;

▶ AusführenAbfrage zurücksetzenNeue Datenbank

11 Zeilen und nur drei Kunden. Marias Name und E-Mail stehen 6-mal da. Sieh dir auch ihre Stadt in Bestellung 6 an.

Die Idee

Anomalien

Steht eine Tatsache an vielen Stellen, ist jede Änderung ein Risiko. Diese Probleme heißen Anomalien. Es gibt drei Arten.

Beim Ändern. Maria ändert ihre E-Mail. Ein Kollege korrigiert sie nur in ihrer letzten Bestellung:

SQLleere Datenbank

UPDATE flat_orders SET email = 'maria.i@example.com'
WHERE order_id = 6;

SELECT DISTINCT customer, email FROM flat_orders
WHERE customer = 'Maria Ivanova';

UPDATE flat_orders SET email = 'maria.i@example.com' WHERE order_id = 6; SELECT DISTINCT customer, email FROM flat_orders WHERE customer = 'Maria Ivanova';

▶ AusführenAbfrage zurücksetzenNeue Datenbank

Jetzt sagt die Datenbank zwei Dinge gleichzeitig. Welche E-Mail stimmt?

Sieh es dir an

Einfügen und Löschen

Beim Einfügen. Der Shop nimmt Haferflocken ins Sortiment. Noch hat sie niemand bestellt. Wohin schreibst du den Preis?

SQLleere Datenbank

INSERT INTO flat_orders (product, price)
VALUES ('Haferflocken', 3.2);

INSERT INTO flat_orders (product, price) VALUES ('Haferflocken', 3.2);

▶ AusführenAbfrage zurücksetzenNeue Datenbank

Eine Zeile ohne Bestellung kann nicht hinein. Das Produkt existiert erst, wenn jemand es kauft.

Beim Löschen. Elena storniert Bestellung 4, und sie wird gelöscht. Es war die einzige Bestellung mit der Bratpfanne:

SQLleere Datenbank

DELETE FROM flat_orders WHERE order_id = 4;

SELECT product, price FROM flat_orders
WHERE product = 'Bratpfanne 28 cm';

DELETE FROM flat_orders WHERE order_id = 4; SELECT product, price FROM flat_orders WHERE product = 'Bratpfanne 28 cm';

▶ AusführenAbfrage zurücksetzenNeue Datenbank

Mit der Bestellung ist auch die Tatsache verschwunden, dass der Shop eine Bratpfanne für 24,90 € verkauft.

Die Idee

Normalformen

Tabellen so anzuordnen, dass jede Tatsache an einer Stelle steht, heißt Normalisierung. Die Regeln kommen in Stufen, den Normalformen. Jede schließt die vorige ein.

Erste (1NF): ein Wert pro Zelle. Keine Listen wie „vegan, bio“ und keine Spalten tag1, tag2, tag3.

SQLleere Datenbank

SELECT name, tags FROM products_sheet
WHERE tags = 'vegan';

SELECT name, tags FROM products_sheet WHERE tags = 'vegan';

▶ AusführenAbfrage zurücksetzenNeue Datenbank

Nur das Weißbrot. Das Vollkornbrot ist vegan, aber in seiner Zelle steht „vegan, bio“. Mit LIKE '%vegan%' findest du es, aber du kannst Produkte nicht nach Tag zählen, „bio“ nicht überall korrigieren und keinen Tippfehler fernhalten. Tags gehören in eine Verbindungstabelle, wie in der ersten Lektion dieses Moduls.

Zweite (2NF): jede Spalte hängt vom ganzen Schlüssel ab. Das zählt, wenn der Schlüssel aus mehreren Spalten besteht:

order_lines

• PKorder_idinteger
• PKproduct_idinteger
• product_nametext *
• quantityinteger *

Der Produktname hängt nur von product_id ab, nicht von der Bestellung. Er wiederholt sich in jeder Bestellung mit diesem Produkt. Er gehört in products.

Dritte (3NF): Spalten hängen nur vom Schlüssel ab, nicht von einer anderen Spalte:

products

• PKidinteger
• nametext *
• category_idinteger *
• category_nametext *
• pricereal *

category_name wird durch category_id bestimmt, nicht durch das Produkt. Wird die Kategorie umbenannt, musst du jedes Produkt darin korrigieren. Der Name gehört in categories.

Genauer, für Neugierige

Die kurze Formel für die drei Formen: Jede Spalte hängt ab vom Schlüssel, vom ganzen Schlüssel und von nichts außer dem Schlüssel.

Es gibt auch höhere Formen (Boyce-Codd, vierte, fünfte), aber in der Praxis hat eine Tabelle in dritter Normalform meist keine dieser Anomalien mehr.

flat_orders verletzt die zweite Normalform. Der Schlüssel einer Zeile ist die Bestellung und das Produkt, während das Datum und der Kunde nur von der Bestellung abhängen. Sie verletzt auch die dritte: Stadt und E-Mail hängen vom Kunden ab, nicht von der Bestellung.

Probier es aus

Welche Form?

1. In order_lines ist der Schlüssel (order_id, product_id), und product_name hängt nur von product_id ab. Welche Form ist verletzt?

Die dritteDie ersteDie zweite

2. products hat category_id und category_name. Welche Form ist verletzt?

Die zweiteDie dritteDie erste

Prüfen

Die Idee

Das Zerlegen

Du zerlegst flat_orders in die Tabellen des Shops: customers, products, orders und order_items. Du beginnst mit denen, die auf nichts zeigen.

INSERT kann seine Zeilen aus einem SELECT nehmen statt aus VALUES. Mit DISTINCT kommt jedes Produkt einmal hinein:

products

• PKidinteger
• nametext *
• pricereal *

SQLleere Datenbank

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE,
  price REAL NOT NULL
);
INSERT INTO products (name, price)
SELECT DISTINCT product, price FROM flat_orders;

SELECT * FROM products;

CREATE TABLE products ( id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE, price REAL NOT NULL ); INSERT INTO products (name, price) SELECT DISTINCT product, price FROM flat_orders; SELECT * FROM products;

▶ AusführenAbfrage zurücksetzenNeue Datenbank

Aus 11 Zeilen werden 6 Produkte, jedes mit einer id. Der Preis bleibt auch in den Bestellzeilen, denn das ist der Preis, zu dem gekauft wurde. Warum das keine Wiederholung ist, siehst du in der nächsten Lektion.

Sieh es dir an

Excel ist schmutzig

Dasselbe für die Kunden:

customers

• PKidinteger
• nametext *
• emailtext *
• citytext

SQLleere Datenbank

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  city TEXT
);
INSERT INTO customers (name, email, city)
SELECT DISTINCT customer, email, city FROM flat_orders;

CREATE TABLE customers ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL UNIQUE, city TEXT ); INSERT INTO customers (name, email, city) SELECT DISTINCT customer, email, city FROM flat_orders;

▶ AusführenAbfrage zurücksetzenNeue Datenbank

Ein Fehler. In Bestellung 6 steht bei Maria die Stadt „Sofiya“. DISTINCT vergleicht die ganze Zeile, sieht also zwei Kunden mit einer E-Mail, und UNIQUE lässt sie nicht hinein. Das ist gut: Die Regel hat den Fehler gefangen, statt ihn in die neue Datenbank mitzunehmen.

Zuerst korrigierst du die Daten, dann zerlegst du:

SQLleere Datenbank

UPDATE flat_orders SET city = 'Sofia' WHERE city = 'Sofiya';

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  city TEXT
);
INSERT INTO customers (name, email, city)
SELECT DISTINCT customer, email, city FROM flat_orders;

SELECT * FROM customers;

UPDATE flat_orders SET city = 'Sofia' WHERE city = 'Sofiya'; CREATE TABLE customers ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL UNIQUE, city TEXT ); INSERT INTO customers (name, email, city) SELECT DISTINCT customer, email, city FROM flat_orders; SELECT * FROM customers;

▶ AusführenAbfrage zurücksetzenNeue Datenbank

Fast jeder Umzug aus Excel beginnt mit einer solchen Bereinigung.

Probier es aus

Die Bestellungen

orders

• PKidinteger
• FKcustomer_idinteger *→ customers
• ordered_attext *

Produkte und Kunden sind schon zerlegt. Fülle orders (id, customer_id, ordered_at) aus flat_orders. Die Bestellnummer ist order_id, und den Kunden findest du über die E-Mail.

SQLleere Datenbank

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  ordered_at TEXT NOT NULL
);

CREATE TABLE orders ( id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customers(id), ordered_at TEXT NOT NULL );

Prüfen▶ Ausführen

Übung

Zwei Aufgaben

1. Übrig bleibt die Verbindungstabelle zwischen Bestellungen und Produkten.

order_items

• PKorder_idinteger→ orders
• PKproduct_idinteger→ products
• quantityinteger *
• unit_pricereal *

Fülle order_items aus flat_orders: die Bestellnummer, die Produkt-ID, die Menge und den Preis.

SQLleere Datenbank

CREATE TABLE order_items (
  order_id INTEGER NOT NULL REFERENCES orders(id),
  product_id INTEGER NOT NULL REFERENCES products(id),
  quantity INTEGER NOT NULL,
  unit_price REAL NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

CREATE TABLE order_items ( order_id INTEGER NOT NULL REFERENCES orders(id), product_id INTEGER NOT NULL REFERENCES products(id), quantity INTEGER NOT NULL, unit_price REAL NOT NULL, PRIMARY KEY (order_id, product_id) );

Prüfen▶ Ausführen

2. Jetzt braucht die Auswertung aus der ersten Lektion des Kurses nur noch eine Abfrage. Wie viel hat jeder Kunde ausgegeben?

Zeige den Namen jedes Kunden und die Summe seiner Einkäufe (Menge mal Preis):

SQLleere Datenbank

Prüfen▶ Ausführen

Test

Teste dich

1. Maria hat ihre E-Mail geändert, und sie steht in 40 Zeilen der Tabelle. Ein Kollege hat 38 korrigiert. Wie heißt das?

Eine EinfügeanomalieEine ÄnderungsanomalieEine Löschanomalie

2. Was verletzt die erste Normalform?

Eine Spalte city, die manchmal NULL istEine Spalte tags mit dem Text „vegan, bio“Eine Tabelle ohne Spalte id

3. Was macht INSERT INTO products (name, price) SELECT DISTINCT product, price FROM flat_orders?

Fügt für jede Bestellung eine Zeile in products einFügt eine Zeile für jedes verschiedene Paar aus Produkt und Preis einNichts, INSERT kann kein SELECT verwenden

4. Warum brach das Zerlegen von Marias Bestellungen mit einem UNIQUE-Fehler ab?

Weil eine E-Mail nicht UNIQUE sein kannWeil Maria viele Bestellungen hatWeil ihre Stadt in zwei Zeilen unterschiedlich geschrieben ist und DISTINCT sie als zwei Kunden sah

Prüfen

Lektion von vorn beginnen

Verwandte Lektionen

• Baut auf: Constraints: Regeln für die Daten · Fremdschlüssel: Verbindungen zwischen Tabellen · Beziehungstypen: 1:1, 1:N, M:N · ER-Diagramm: von der Beschreibung zum Schema
• Geht weiter in: Natürliche Schlüssel und Denormalisierung · Dokumentendatenbanken · Die Bäckerei, Etappe 1: Datenbank und Tabellen
• Aufgaben zum Thema: Eine Datenbank entwerfen