Datenbanken erstellen und Daten einfügen

Im Modul 162 hast du ein Datenmodell entworfen, hier wird es Realität. Du schreibst SQL-Skripte, die Datenbank, Tabellen, Datentypen und Regeln anlegen, und verknüpfst Tabellen über Fremdschlüssel. Danach füllst du die Datenbank mit INSERT-Befehlen und importierst bestehende Daten aus CSV-Dateien, wobei du sie vorher bereinigst. Du passt die Struktur bei neuen Anforderungen an, richtest Benutzer mit passenden Rechten ein und sorgst dafür, dass sich alles jederzeit per Skript neu aufbauen lässt.

Das sind die Fähigkeiten, die am Ende des Moduls sitzen sollten. Jedes Ziel mit einem Beispiel aus dem Lehrbetrieb.

1. 1

   Tabellen mit passenden Datentypen per SQL anlegen

   Kompetenz A

   Du übersetzt ein logisches Modell in CREATE-TABLE-Befehle und wählst für jede Spalte einen Typ, der den Inhalt genau abbildet: Geldbeträge als DECIMAL, Datumswerte als DATE, Texte mit sinnvoller Länge. Du kennst die Auswirkungen deiner Wahl auf Speicher und Genauigkeit.

   Situation

   Ein Hofladen im Emmental will Produkte, Lieferanten und Verkäufe digital erfassen. Das Datenmodell mit fünf Tabellen liegt vor.

   Deine Aufgabe

   Erstelle die Tabelle 'produkt' mit Name, Einheit, Preis, Bio-Kennzeichen und Erfassungsdatum.

   Gutes Ergebnis

   produkt_id INT AUTO_INCREMENT als Primärschlüssel, name VARCHAR(80), einheit VARCHAR(10) für 'kg' oder 'Stk', preis DECIMAL(8,2) statt FLOAT, damit CHF 4.85 genau gespeichert wird, ist_bio BOOLEAN und erfasst_am DATE mit Standardwert CURRENT_DATE.

   CREATE DATABASE / TABLEDatentypen (INT, VARCHAR, DECIMAL, DATE)AUTO_INCREMENTZeichensatz utf8mb4DDL
2. 2

   Regeln mit Constraints direkt in der Datenbank durchsetzen

   Kompetenz B

   Du legst fest, welche Werte Pflicht sind, welche eindeutig sein müssen und welche Bereiche erlaubt sind. So kann auch eine fehlerhafte Applikation keine ungültigen Daten speichern.

   Situation

   In der Mitarbeiterdatenbank eines Reinigungsunternehmens mit 120 Angestellten stehen doppelte Personalnummern und negative Pensen.

   Deine Aufgabe

   Ergänze die Tabelle 'mitarbeiter' mit Constraints, die solche Fehler verhindern.

   Gutes Ergebnis

   personalnr mit UNIQUE, nachname und eintritt mit NOT NULL, pensum mit CHECK (pensum BETWEEN 10 AND 100) und land mit DEFAULT 'CH'. Ein Testeintrag mit Pensum 120 wird mit einer Fehlermeldung abgelehnt.

   PRIMARY KEYNOT NULLUNIQUECHECKDEFAULTDatenintegrität
3. 3

   Tabellen mit Fremdschlüsseln verknüpfen

   Kompetenz B

   Du verbindest Tabellen über Fremdschlüssel, damit nur Verweise auf existierende Datensätze möglich sind. Du entscheidest bewusst, was beim Löschen oder Ändern eines referenzierten Datensatzes passieren soll.

   Situation

   Im Hofladen wurden Verkäufe gespeichert, die auf ein bereits gelöschtes Produkt zeigen. Die Monatsabrechnung stimmt nicht mehr.

   Deine Aufgabe

   Verknüpfe 'verkauf' und 'produkt' so, dass solche Waisen nicht mehr entstehen.

   Gutes Ergebnis

   verkauf.produkt_id ist ein FOREIGN KEY auf produkt.produkt_id mit ON DELETE RESTRICT. Ein Produkt mit Verkäufen lässt sich nicht mehr löschen, stattdessen wird es mit 'aktiv = FALSE' ausgeblendet. Die Positionen einer Rechnung werden dagegen mit ON DELETE CASCADE zusammen mit der Rechnung entfernt.

   FOREIGN KEYreferentielle IntegritätON DELETE RESTRICT / CASCADE / SET NULLWaisen-DatensätzeZwischentabelle
4. 4

   Daten mit INSERT korrekt und in der richtigen Reihenfolge einfügen

   Kompetenz C

   Du fügst Daten mit INSERT ein, auch mehrere Zeilen auf einmal, und beachtest die Abhängigkeiten: zuerst die Tabellen ohne Fremdschlüssel, dann die abhängigen. Mit einem einfachen SELECT kontrollierst du jedes Mal das Ergebnis.

   Situation

   Für die Testumgebung eines Kursverwaltungssystems der Volkshochschule Zürich braucht das Entwicklerteam realistische Testdaten.

   Deine Aufgabe

   Erstelle ein Skript, das 5 Kursleitende, 12 Kurse und 40 Anmeldungen einfügt.

   Gutes Ergebnis

   Das Skript fügt zuerst kursleitung, dann kurs, dann teilnehmer und zuletzt anmeldung ein. Mehrere Zeilen werden pro INSERT gebündelt. Am Ende zeigt ein 'SELECT COUNT(*)' pro Tabelle die erwarteten Mengen, und ein Kurs mit 0 Anmeldungen ist bewusst enthalten.

   INSERT INTO ... VALUESMehrzeiliges INSERTEinfügereihenfolgeTestdatenDML
5. 5

   Bestehende Daten aus CSV-Dateien bereinigen und importieren

   Kompetenz D

   Daten kommen oft aus Excel oder Altsystemen. Du prüfst Trennzeichen, Zeichensatz, Datumsformate und Duplikate, bereinigst die Datei und importierst sie mit einem Werkzeug oder LOAD DATA. Fehlerhafte Zeilen erkennst du und behandelst sie gezielt.

   Situation

   Eine Hausarztpraxis wechselt die Software. Die Patientenliste wurde als CSV mit Semikolon exportiert, Umlaute erscheinen als 'ü' und Datumsangaben im Format 31.12.1985.

   Deine Aufgabe

   Importiere die 2300 Patientendatensätze sauber in die neue Datenbank.

   Gutes Ergebnis

   Die Datei wird als UTF-8 neu gespeichert, mit LOAD DATA in eine Hilfstabelle geladen, das Datum mit STR_TO_DATE umgewandelt und erst danach in die Zieltabelle übernommen. 14 Duplikate werden in einer Protokolltabelle festgehalten und mit der Praxis geklärt.

   CSVZeichensatz (UTF-8)LOAD DATA / Import-AssistentStaging-TabelleDatenbereinigung
6. 6

   Die Struktur mit Skripten anpassen und versionieren

   Kompetenz E

   Neue Anforderungen bedeuten neue Spalten oder Tabellen. Du änderst die Struktur mit ALTER TABLE, ohne vorhandene Daten zu verlieren, und hältst alle Schritte in nummerierten Skripten fest, sodass jede Person die Datenbank reproduzierbar aufbauen kann.

   Situation

   Der Hofladen will neu auch Online-Bestellungen mit Abholdatum anbieten. Die Datenbank ist bereits mit drei Monaten Verkaufsdaten in Betrieb.

   Deine Aufgabe

   Ergänze die Struktur, ohne bestehende Daten zu gefährden.

   Gutes Ergebnis

   Skript '003_bestellungen.sql' erstellt die Tabelle 'bestellung' und ergänzt 'verkauf' per ALTER TABLE um die Spalte 'kanal' mit DEFAULT 'Laden'. Vorher wurde ein Dump erstellt. Alle Skripte liegen im Git-Repository und laufen auf einer leeren Datenbank fehlerfrei durch.

   ALTER TABLEDROP vs. TRUNCATESQL-SkriptMigrationVersionsverwaltung
7. 7

   Datenbankbenutzer mit minimalen Rechten einrichten

   Kompetenz F

   Du arbeitest nicht mit root, sondern legst eigene Benutzer an und gibst ihnen genau die Rechte, die sie brauchen. Eine Applikation darf zum Beispiel lesen und schreiben, aber keine Tabellen löschen.

   Situation

   Die neue Hofladen-Website soll auf die Datenbank zugreifen. Bisher verwendet sie den root-Benutzer ohne Passwort.

   Deine Aufgabe

   Richte einen sicheren Zugang für die Website und einen nur lesenden Zugang für die Buchhaltung ein.

   Gutes Ergebnis

   Benutzer 'hofladen_web' mit SELECT, INSERT und UPDATE auf die Datenbank, nur von localhost erreichbar. Benutzer 'buchhaltung' mit SELECT auf die Tabellen verkauf und produkt. Beide haben lange Passwörter aus dem Passwortmanager, root ist nur noch lokal mit Passwort zugänglich.

   CREATE USERGRANT / REVOKELeast PrivilegeHost-EinschränkungPasswortrichtlinie

Die Kompetenzmatrix zerlegt das Modul in Themenstränge und drei Stufen. Für den Kompetenznachweis solltest du überall mindestens Stufe 2 erreichen. Dein Stand bleibt in diesem Browser gespeichert und färbt Lernweg und Übungen ein.

Elena ist im 1. Lehrjahr als Entwicklerin digitales Business bei der Zweirad Kälin AG in Einsiedeln, einem Velogeschäft mit Werkstatt, Onlineshop und 35 Mitarbeitenden. Werkstattaufträge werden bisher auf Papierzetteln und in einer Excel-Liste geführt. Für ein neues Online-Buchungstool für Veloservices soll Elena die MySQL-Datenbank aufbauen und mit den bestehenden Daten befüllen.

1. Kapitel 1

   Die ersten vier Tabellen

   Elena startet MySQL in einem Docker-Container und arbeitet mit DBeaver. Sie legt die Tabellen kunde, velo, auftrag und servicepaket an und speichert alles im Skript 01_schema.sql. Beim Preis wählt sie zuerst FLOAT, bis eine Summe über 200 Testaufträge um einige Rappen danebenliegt, und wechselt auf DECIMAL(8,2). Für die eigene Arbeit legt sie einen Entwicklungsbenutzer an, statt ständig als root zu arbeiten.

   Ziel 1Ziel 7

2. Kapitel 2

   Regeln, die niemand umgehen kann

   Jedes Velo gehört genau einem Kunden, und jeder Auftrag braucht ein Velo. Elena setzt Fremdschlüssel und verhindert mit ON DELETE RESTRICT, dass ein Kunde mit offenen Aufträgen gelöscht wird. Mit einem CHECK-Constraint stellt sie sicher, dass der Abholtermin nicht vor dem Bringtermin liegt, und mit UNIQUE, dass jede Rahmennummer nur einmal vorkommt.

   Ziel 2Ziel 3

3. Kapitel 3

   Testdaten in der richtigen Reihenfolge

   Für die Entwickler des Buchungstools schreibt Elena ein Skript mit 20 Kunden, 30 Velos und 50 Aufträgen. Beim ersten Lauf bricht es mit einem Fremdschlüsselfehler ab, weil die Aufträge vor den Velos eingefügt werden. Sie ordnet die INSERT-Blöcke nach den Abhängigkeiten und ergänzt am Anfang ein Löschen in umgekehrter Reihenfolge, damit das Skript beliebig oft laufen kann.

   Ziel 4

4. Kapitel 4

   1400 Kunden aus der alten Liste

   Die bestehende Kundenliste kommt als CSV mit Semikolon und Datumsangaben im Format 31.12.2023. Elena lädt sie zuerst in eine Staging-Tabelle, wandelt die Daten mit STR_TO_DATE um und entfernt 37 Duplikate anhand der E-Mail-Adresse. 12 Datensätze ohne Nachnamen schreibt sie in eine Fehlerliste für das Sekretariat. Eine Kontrollzählung zeigt, dass alle übrigen 1351 Kunden angekommen sind.

   Ziel 5

5. Kapitel 5

   Kurz vor dem Livegang

   Kurz vor dem Start will die Werkstatt zusätzlich ein Feld für Ersatzteilwünsche. Elena schreibt dafür das Migrationsskript 04_ersatzteile.sql mit ALTER TABLE und legt es im Git-Repository neben die anderen Skripte. Die Webanwendung erhält einen eigenen Benutzer buchung_app, der nur SELECT und INSERT auf die nötigen Tabellen darf. Ein Test zeigt, dass ein DELETE mit diesem Benutzer abgelehnt wird.

   Ziel 6Ziel 7

1. 1

   Arbeitsumgebung einrichten

   ca. 2 Std.

   Du installierst einen Datenbankserver und ein Werkzeug, mit dem du SQL bequem ausführen kannst, und lernst die Unterschiede zwischen Server, Datenbank und Client kennen.

   • MariaDB über XAMPP oder MySQL per Docker starten
   • Mit DBeaver oder MySQL Workbench verbinden und eine erste Datenbank mit utf8mb4 anlegen
   • Einen eigenen Benutzer statt root für die Übungen anlegen

   Trainiert Kompetenz A1F1

   Lernziel17

2. 2

   Tabellen und Datentypen

   ca. 3 Std.

   Du schreibst die ersten CREATE-TABLE-Befehle und probierst aus, was bei falschen Datentypen passiert.

   • Ein Modell mit drei Tabellen aus Modul 162 in SQL umsetzen
   • FLOAT und DECIMAL mit dem Betrag 0.1 + 0.2 vergleichen
   • Den Befehl SHOW CREATE TABLE nutzen, um die erzeugte Struktur zu prüfen

   Trainiert Kompetenz A1A2

   Lernziel1

3. 3

   Constraints und Fremdschlüssel

   ca. 4 Std.

   Du sicherst die Tabellen mit Regeln ab und verknüpfst sie. Danach versuchst du gezielt, die Regeln zu brechen.

   • NOT NULL, UNIQUE, CHECK und DEFAULT ergänzen
   • Fremdschlüssel mit unterschiedlichen ON-DELETE-Optionen anlegen
   • Fünf fehlerhafte INSERTs schreiben und die Fehlermeldungen erklären

   Trainiert Kompetenz B1B2

   Lernziel23

4. 4

   Daten einfügen und importieren

   ca. 4 Std.

   Du füllst die Datenbank mit Testdaten und übernimmst eine echte CSV-Datei mit typischen Problemen.

   • Ein Testdatenskript mit über 50 Datensätzen in der richtigen Reihenfolge schreiben
   • Eine Excel-Liste mit Umlauten und Schweizer Datumsformat als CSV importieren
   • Über eine Staging-Tabelle Duplikate finden und entfernen

   Trainiert Kompetenz C1C2D1D2

   Lernziel45

5. 5

   Ändern, Rechte und Skripte

   ca. 3 Std.

   Du passt eine bestehende Datenbank an, ohne Daten zu verlieren, und organisierst alles in nachvollziehbaren Skripten.

   • Eine Spalte hinzufügen, umbenennen und einen Datentyp ändern
   • Benutzer für Applikation und Auswertung mit unterschiedlichen Rechten anlegen
   • Alle Skripte nummerieren und auf einer leeren Datenbank durchlaufen lassen

   Trainiert Kompetenz E1E2F2

   Lernziel67

6. 6

   Gesamtübung: Datenbank für einen Kleinbetrieb

   ca. 4 Std.

   Du baust anhand eines vorgegebenen Modells eine komplette Datenbank inklusive Import, Testdaten und Benutzern auf, wie im Kompetenznachweis.

   • Strukturskript, Datenskript und Rechteskript getrennt erstellen
   • Mit einem kurzen Prüfprotokoll zeigen, dass alle Constraints greifen

   Trainiert Kompetenz A3B2C3D3

   Lernziel1345

DDL

Data Definition Language: SQL-Befehle zum Anlegen und Ändern der Datenbankstruktur.

DML

Data Manipulation Language: SQL-Befehle zum Einfügen, Ändern und Löschen von Daten.

Constraint

Regel in der Datenbank, die ungültige Daten verhindert, z.B. NOT NULL oder UNIQUE.

Referentielle Integrität

Garantie, dass jeder Fremdschlüssel auf einen tatsächlich existierenden Datensatz zeigt.

AUTO_INCREMENT

Automatisch hochzählender Wert, meist für künstliche Primärschlüssel.

CSV

Textdatei, in der Werte durch ein Trennzeichen wie Komma oder Semikolon getrennt sind.

Staging-Tabelle

Zwischentabelle für Rohdaten, die vor der Übernahme geprüft und bereinigt werden.

Least Privilege

Prinzip, jedem Benutzer nur die minimal nötigen Rechte zu geben.

Passende Module