MariaDB-Kurs / Bestelldaten normalisieren und historische Fakten erhalten
Normalisierung untersucht, welche Schlüssel welche Fakten bestimmen, und trennt Tabellen, um wiederholte aktuelle Informationen und Änderungsanomalien zu verringern. Die Trennung aktueller Kunden- oder Produktattribute von Kaufdaten erlaubt eine Namensänderung an einer Stelle, während der historische Einzelpreis einer Bestellung erhalten bleibt.
Anomalien durch wiederholte Fakten erkennen
Wird der aktuelle Kundenname in jede Bestellposition kopiert, kann eine unvollständige Aktualisierung unterschiedliche Namen für denselben Kunden hinterlassen. Das Löschen der letzten Position könnte seine einzige gespeicherte Information entfernen. Eine Registrierung vor der ersten Bestellung könnte unmöglich sein, wenn alle Daten nur in Positionen liegen. Die Trennung nach dem Gegenstand der Fakten verringert solche Änderungs-, Lösch- und Einfügeanomalien.
| Fakt | Bestimmender Schlüssel | Ort |
|---|---|---|
| Aktueller Kundenname und Stadt | customer_id | customers |
| Aktueller Produktname und Verkaufspreis | product_id | products |
| Kunde, Zeitpunkt und Status einer Bestellung | order_id | orders |
| Menge und Einzelpreis beim Kauf | order_id + product_id | items |
Eine Abhängigkeit bedeutet hier, dass ein Schlüssel den Wert eines Fakts bestimmt. Dieselbe Kundenkennung hat in der zuständigen Tabelle einen aktuellen Namen. Was als unteilbarer Wert gilt, hängt von Fachgebiet und Verwendung ab. Eine kommaseparierte Produktliste erschwert einzelne Mengenabfragen. Daraus folgt aber nicht, dass jeder Text oder JSON-Wert grundsätzlich in weitere Spalten oder Tabellen zerlegt werden muss.
Getrennte Tabellen und eine flache Vergleichskopie erstellen
Verbinden Sie sich als course_user mit dem Lernserver und wählen Sie shopcourse. Die Anleitung zu Client und UTF-8-SQL-Dateien erklärt Speichern und Ausführen mit SOURCE C:/mariadb-lab/lesson.sql;. Ersetzen Sie den Pfad durch die tatsächliche Datei und führen Sie SQL im MariaDB-Client aus.
Prüfen Sie vor dem Erstellen von c37_customers, c37_products, c37_orders und c37_items mit SELECT VERSION(), DATABASE(); und SHOW TABLES; die Umgebung. Diese Namen gehören nur zur Übung. Untersuchen Sie vorhandene Definitionen und Daten vor einer Einstufung als entbehrlich und erhalten Sie Anwendungstabellen.
USE shopcourse;
CREATE TABLE c37_customers (
customer_id INT NOT NULL PRIMARY KEY,
customer_name VARCHAR(50) NOT NULL,
city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c37_products (
product_id INT NOT NULL PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
category VARCHAR(30) NOT NULL,
price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c37_orders (
order_id INT NOT NULL PRIMARY KEY,
customer_id INT NOT NULL,
ordered_at DATETIME NOT NULL,
status VARCHAR(20) NOT NULL,
shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
CONSTRAINT fk_c37_order_customer FOREIGN KEY (customer_id)
REFERENCES c37_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c37_items (
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL CHECK (quantity > 0),
unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
PRIMARY KEY (order_id, product_id),
CONSTRAINT fk_c37_item_order FOREIGN KEY (order_id)
REFERENCES c37_orders(order_id) ON DELETE RESTRICT,
CONSTRAINT fk_c37_item_product FOREIGN KEY (product_id)
REFERENCES c37_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c37_customers VALUES
(1,'김민수','서울'),(2,'이서연','부산'),
(3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c37_products VALUES
(1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
(3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c37_orders
(order_id,customer_id,ordered_at,status,shipping_fee) VALUES
(101,1,'2026-01-10 10:00:00','paid',1000),
(102,1,'2026-01-20 11:00:00','paid',1000),
(103,2,'2026-02-05 12:00:00','paid',1000),
(104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c37_items VALUES
(101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
(103,1,1,3500),(103,3,2,8000),(104,2,1,1500);
Das SQL erhält die koreanischen Originalnamen und Städte. Die Produkte 1 bis 4 sind Notizbuch, Kugelschreiber, Becher und Lampe. Die Kunden 1 und 3 wohnen in Seoul, Kunde 2 in Busan und Kunde 4 in Daejeon. Behalten Sie die UTF-8-Zeichenfolgen beim Nachvollziehen bei.
Prüfen Sie außerdem, dass c37_flat fehlt. CREATE TABLE AS SELECT erstellt hier eine flache Kopie zum Vergleich der Wiederholungen. Dabei werden nicht automatisch alle Schlüssel und Bedingungen der Ursprungstabellen übernommen. Dies ist keine vollständige Datensicherung. Die Tabelle wird vor START TRANSACTION für den Änderungsversuch erstellt.
CREATE TABLE c37_flat AS
SELECT o.order_id,i.product_id,c.customer_id,c.customer_name,
p.product_name,i.quantity,i.unit_price
FROM c37_orders o JOIN c37_customers c ON c.customer_id=o.customer_id
JOIN c37_items i ON i.order_id=o.order_id
JOIN c37_products p ON p.product_id=i.product_id;
SELECT customer_id,customer_name,COUNT(*) AS repeated_rows
FROM c37_flat GROUP BY customer_id,customer_name ORDER BY customer_id;
START TRANSACTION;
UPDATE c37_flat SET customer_name='김민수 변경' WHERE order_id=101;
SELECT DISTINCT customer_id,customer_name FROM c37_flat
WHERE customer_id=1 ORDER BY customer_name;
ROLLBACK;
Das erste Ergebnis zeigt drei Zeilen für Kunde 1, zwei für Kunde 2 und eine für Kunde 3. Kunde 4 fehlt wegen seiner fehlenden Bestellungen und der inneren Verknüpfungen der Kopie. Der Name von Kunde 1 steht in den zwei Positionen von Bestellung 101 und der Position von Bestellung 102.
UPDATE verändert nur die Kopien der Bestellung 101. SELECT DISTINCT zeigt deshalb für Kunde 1 den ursprünglichen Namen 김민수 und die geänderte Version 김민수 변경. ROLLBACK nimmt diese Änderung der Vergleichstabelle zurück; die normalisierte Kundentabelle blieb unverändert. Danach hat der Kunde auch in c37_flat wieder nur den ursprünglichen Namen.
Partielle und transitive Abhängigkeiten untersuchen
Der Positionsschlüssel dieses Modells verbindet order_id und product_id. Das Bestelldatum hängt nur von order_id ab, nicht von der ganzen Kombination. Es in jeder Position zu wiederholen, speichert einen nur von einem Schlüsselteil abhängigen Fakt. Seine Ablage in orders trennt den Bestellfakt von Menge und Positionspreis.
Eine Bestellung bestimmt ihren Kunden; der Kunde bestimmt seinen aktuellen Namen. Die Wiederholung dieses Namens in jeder Bestellung verteilt eine Änderung auf viele Zeilen. Das ist eine transitive Abhängigkeit über ein weiteres Attribut. Der Verweis auf customers mittels customer_id schafft einen festgelegten Pflegeort für den aktuellen Namen. Solche Fragen helfen, zweite und dritte Normalform praktisch zu untersuchen. Die offizielle Erklärung zur dritten Normalform erläutert transitive Abhängigkeiten.
Den historischen Preis als eigenen Fakt erhalten
Entfernen Sie items.unit_price nicht allein deshalb, weil es derzeit products.price entspricht. Der erste Wert hält den Kaufpreis fest, der zweite den aktuellen Verkaufspreis. Es sind Fakten verschiedener Zeitpunkte. Steigt der Katalogpreis des Notizbuchs von 3500 auf 4000, müssen die zwei zu 3500 gekauften Einheiten der Bestellung 101 weiterhin 7000 zum historischen Warenwert beitragen.
Auch Lieferadressen und auf einem Dokument gedruckte Namen können einen Kaufzeitpunkt-Snapshot erfordern. Definieren Sie Bedeutung, Erfassungszeit und Änderungsregeln für aktuelle Attribute und historische Belege getrennt. Benötigte Historie zu bewahren, unterscheidet sich vom undefinierten Wiederholen eines aktuellen Attributs.
Schlüssel und fachliche Bedingungen durchsetzen
Vier Tabellen zu erstellen, reicht zum Abweisen ungültiger Referenzen nicht aus. Definieren Sie PRIMARY KEY, UNIQUE, NOT NULL und Fremdschlüssel nach den Zeilenregeln. Testen Sie doppelte Schlüssel und fehlende übergeordnete Datensätze. Die Übung zu Beziehungen und Fremdschlüsseln zeigt diese Prüfungen. Die offizielle MariaDB-Fremdschlüsselreferenz erläutert die Integritätsregeln.
Dieses vereinfachte Modell fasst jedes Produkt in einer einzelnen Position pro Bestellung zusammen. Eine zweite Position derselben Kombination verletzt den Primärschlüssel, auch wenn eine Option oder Charge unterschieden werden sollte. Optionen, Chargen oder Teillieferungen können eigene Positionskennungen und zusätzliche Schlüssel verlangen. Definieren Sie Zeilen nach den Anforderungen, statt die Übung als allgemeine Bestelllösung zu behandeln.
Vor bewusster Denormalisierung messen
Normalisierung schafft eine Grundlage für konsistente Änderungen. Die Leistung hängt von Abfragen, Indizes und Datenmenge ab. Benötigen Sie eine Zusammenfassungstabelle für Berichte, legen Sie maßgebliche Quelle, Aktualisierung und Fehlerbehebung fest. Viele JOINs allein rechtfertigen keine Wiederholung aktueller Informationen ohne Pflegekonzept.
Prüfen Sie vor einer Wiederholung die ausgewählte Datenbank, Definitionen und Datensätze und sichern Sie benötigte Inhalte. Sind die fünf folgenden Tabellen ausschließlich entbehrliche Übungsobjekte, entfernen Sie zuerst die flache Kopie, danach die abhängigen und zuletzt die referenzierten Tabellen. DROP TABLE entfernt Struktur und Daten und wird durch ein gewöhnliches ROLLBACK nicht rückgängig gemacht. Halten Sie die Bereinigung außerhalb des Änderungsversuchs.
DROP TABLE shopcourse.c37_flat;
DROP TABLE shopcourse.c37_items;
DROP TABLE shopcourse.c37_orders;
DROP TABLE shopcourse.c37_products;
DROP TABLE shopcourse.c37_customers;




