modul:m290_guko:learningunits:lu08:aufgaben:a_fk_in_lu05

Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

Link zu dieser Vergleichsansicht

Beide Seiten der vorigen Revision Vorhergehende Überarbeitung
Nächste Überarbeitung		 Vorhergehende Überarbeitung
modul:m290_guko:learningunits:lu08:aufgaben:a_fk_in_lu05 [2025/10/19 23:44] – gkochmodul:m290_guko:learningunits:lu08:aufgaben:a_fk_in_lu05 [2025/10/27 11:02] (aktuell) – gkoch
Zeile 17: Zeile 17:
   * Prüft, dass **Primärschlüssel vorhanden** sind (wie in LU05), aber **keine** Fremdschlüssel.   * Prüft, dass **Primärschlüssel vorhanden** sind (wie in LU05), aber **keine** Fremdschlüssel.
   * Falls ihr neu starten wollt: erstellt die Tabellen nochmals wie in LU05 (PK ja, FK noch nicht) und füllt ein paar **Beispieldaten** ein (mind. 3 Zeilen pro Tabelle).   * Falls ihr neu starten wollt: erstellt die Tabellen nochmals wie in LU05 (PK ja, FK noch nicht) und füllt ein paar **Beispieldaten** ein (mind. 3 Zeilen pro Tabelle).
 +  * PDF mit Schema zu den einzelnen Cases: {{ :modul:m290_guko:learningunits:lu08:aufgaben:aufgaben_crowsfoot_sql-ddl.pdf |}}
  
  
Zeile 32: Zeile 33:
  
 **SQL-Muster (anpassen):** **SQL-Muster (anpassen):**
 +<WRAP center box round 80%>
 +1. Erstellen Sie eine neue Spalte für den Fremdschlüssel:
 <code sql> <code sql>
-ALTER TABLE <kind_tabelle> +ALTER TABLE TABLE_NAME 
-ADD CONSTRAINT fk_<kind>_<eltern> +ADD COLUMN neue_spalte DATENTYP [AFTER bestehende_spalte]; 
-FOREIGN KEY (<fk_spalte>) +</code> 
-REFERENCES <eltern_tabelle>(<pk_spalte>)+2. Setzen Sie die soeben erstellte Spalte als Fremdschlüssel (Foreign Key): 
 +<code sql> 
 +ALTER TABLE kind_tabelle 
 +ADD FOREIGN KEY (neue_spalte) 
 +REFERENCES eltern_tabelle(primaerschluessel_spalte)
 ON DELETE RESTRICT ON DELETE RESTRICT
 ON UPDATE RESTRICT; ON UPDATE RESTRICT;
 </code> </code>
 +</WRAP>
  
 <WRAP center box round 80%> <WRAP center box round 80%>
Zeile 50: Zeile 58:
  
  
-==== 3) DML testen – RESTRICT „live“ erleben ==== +==== 3) DML testen – RESTRICT erfahrbar machen ==== 
-**So gehst du vor (für euren gewählten Case):**+//DML = Data Manipulation Language -> Daten einfügen, ändern, löschen// 
 + 
 +**So gehen Sie vor (für euren gewählten Case):**
  
-1. **Mini-Daten anlegen** (2 Eltern + 1 Kind, das auf Eltern #1 zeigt).+1. **Daten anlegen**
 2. **Tests A–D** ausführen und das Verhalten beobachten (Kommentar sagt, was passieren soll). 2. **Tests A–D** ausführen und das Verhalten beobachten (Kommentar sagt, was passieren soll).
  
-> Hinweis: Ersetze nichts – die Beispiele sind bereits mit konkreten Tabellen-/Spaltennamen pro Case ausgearbeitet.+> Hinweis: Falls eure Tabellennamen und Spaltennamen abweichen, dann müsst ihr das entsprechend ändern. Also entweder die hier erwähnten Codesnippets anpassen oder die Spaltennamen bei euch ändern.
  
----+=== Case: Tierheim (Tierart → viele Tiere) ==
  
-### Case: Tierheim (»Tierart« → viele »Tier«)+**FK:** //tier.art_id// → //tierart.art_id//
  
-**FK:** `tier.art_id` → `tierart.art_id` +**Daten**
- +
-**Mini-Daten**+
 <WRAP center box 80% round><code sql> <WRAP center box 80% round><code sql>
 INSERT INTO tierart (art_id, bezeichnung) VALUES (1,'Hund'), (2,'Katze'); INSERT INTO tierart (art_id, bezeichnung) VALUES (1,'Hund'), (2,'Katze');
 INSERT INTO tier (tier_id, name, geburtsdatum, geschlecht, art_id) INSERT INTO tier (tier_id, name, geburtsdatum, geschlecht, art_id)
-VALUES (101,'Luna','2022-05-10','w',1);  -- Kind zeigt auf Hund (1) </code></WRAP>+VALUES (101,'Luna','2022-05-10','w',1); -- Kind zeigt auf Hund (1) </code> 
 +</WRAP>
  
 **Tests A–D** **Tests A–D**
Zeile 74: Zeile 83:
 -- A) INSERT: gültig vs. ungültig -- A) INSERT: gültig vs. ungültig
 INSERT INTO tier (tier_id, name, geburtsdatum, geschlecht, art_id) INSERT INTO tier (tier_id, name, geburtsdatum, geschlecht, art_id)
-VALUES (102,'Milo','2023-01-03','m',2);         -- ok (Katze existiert)+VALUES (102,'Milo','2023-01-03','m',2); -- ok (Katze existiert)
 INSERT INTO tier (tier_id, name, geburtsdatum, geschlecht, art_id) INSERT INTO tier (tier_id, name, geburtsdatum, geschlecht, art_id)
-VALUES (103,'Nala','2023-03-01','w',9999);      -- sollte scheitern (FK!)+VALUES (103,'Nala','2023-03-01','w',9999); -- sollte scheitern (FK!)
  
 -- B) DELETE Eltern mit Kind -- B) DELETE Eltern mit Kind
-DELETE FROM tierart WHERE art_id = 1;           -- sollte scheitern (RESTRICT), weil Tier 101 darauf zeigt+DELETE FROM tierart WHERE art_id = 1; -- sollte scheitern (RESTRICT), weil Tier 101 darauf zeigt
  
 -- C) UPDATE FK im Kind (umhängen) -- C) UPDATE FK im Kind (umhängen)
Zeile 85: Zeile 94:
  
 -- D) UPDATE PK in Eltern -- D) UPDATE PK in Eltern
-UPDATE tierart SET art_id = 5 WHERE art_id = 2; -- sollte scheitern, solange Tiere auf 2 verweisen </code></WRAP>+UPDATE tierart SET art_id = 5 WHERE art_id = 2; -- sollte scheitern, solange Tiere auf 2 verweisen </code> 
 +</WRAP>
  
---- 
  
-### Case: Postzustellung (»Postgebiet« → viele »Briefkasten«)+=== Case: Postzustellung (Postgebiet → viele Briefkästen) ===
  
-**FK:** `briefkasten.gebiet_id` → `postgebiet.gebiet_id`+**FK:** //briefkasten.gebiet_id// → //postgebiet.gebiet_id//
  
-**Mini-Daten** +**Daten** 
-<WRAP center box 80% round><code sql>+<WRAP center box 80% round> 
 +<code sql>
 INSERT INTO postgebiet (gebiet_id, name, plz_bereich) VALUES (10,'Stadt Nord','8000-8099'), INSERT INTO postgebiet (gebiet_id, name, plz_bereich) VALUES (10,'Stadt Nord','8000-8099'),
 (20,'Stadt Süd','8100-8199'); (20,'Stadt Süd','8100-8199');
Zeile 104: Zeile 114:
 -- A) INSERT -- A) INSERT
 INSERT INTO briefkasten (briefkasten_id, standort, leerungszeit, gebiet_id) INSERT INTO briefkasten (briefkasten_id, standort, leerungszeit, gebiet_id)
-VALUES (502,'Zentrum','17:30',20);          -- ok+VALUES (502,'Zentrum','17:30',20); -- ok
 INSERT INTO briefkasten (briefkasten_id, standort, leerungszeit, gebiet_id) INSERT INTO briefkasten (briefkasten_id, standort, leerungszeit, gebiet_id)
-VALUES (503,'Park','17:00',9999);           -- FK-Fehler erwartet+VALUES (503,'Park','17:00',9999); -- FK-Fehler erwartet
  
 -- B) DELETE Eltern -- B) DELETE Eltern
Zeile 115: Zeile 125:
  
 -- D) UPDATE PK in Eltern -- D) UPDATE PK in Eltern
-UPDATE postgebiet SET gebiet_id = 11 WHERE gebiet_id = 20;         -- scheitert, solange Kinder auf 20 zeigen </code></WRAP>+UPDATE postgebiet SET gebiet_id = 11 WHERE gebiet_id = 20; -- scheitert, solange Kinder auf 20 zeigen </code></WRAP>
  
----+=== Case: Online-Kleidershop (Kategorie → viele Kleidungsstücke) ===
  
-### Case: Online-Kleidershop (»Kategorie« → viele »Kleidungsstück«)+**FK:** //kleidungsstueck.kategorie_id// → //kategorie.kategorie_id//
  
-**FK:** `kleidungsstueck.kategorie_id` → `kategorie.kategorie_id` +**Daten**
- +
-**Mini-Daten**+
 <WRAP center box 80% round><code sql> <WRAP center box 80% round><code sql>
 INSERT INTO kategorie (kategorie_id, name) VALUES (1,'Schuhe'), (2,'T-Shirts'); INSERT INTO kategorie (kategorie_id, name) VALUES (1,'Schuhe'), (2,'T-Shirts');
 INSERT INTO kleidungsstueck (artikel_id, name, preis, groesse, zielgruppe, kategorie_id) INSERT INTO kleidungsstueck (artikel_id, name, preis, groesse, zielgruppe, kategorie_id)
-VALUES (9001,'City Sneaker',89.90,'42','Herren',1); </code></WRAP>+VALUES (9001,'City Sneaker',89.90,'42','Herren',1); </code> 
 +</WRAP>
  
 **Tests A–D** **Tests A–D**
Zeile 138: Zeile 147:
  
 -- B) DELETE Eltern -- B) DELETE Eltern
-DELETE FROM kategorie WHERE kategorie_id = 1;     -- scheitert (RESTRICT), Schuh 9001 existiert+DELETE FROM kategorie WHERE kategorie_id = 1; -- scheitert (RESTRICT), Schuh 9001 existiert
  
 -- C) UPDATE FK im Kind -- C) UPDATE FK im Kind
Zeile 144: Zeile 153:
  
 -- D) UPDATE PK in Eltern -- D) UPDATE PK in Eltern
-UPDATE kategorie SET kategorie_id = 5 WHERE kategorie_id = 2;        -- scheitert bei referenzierten Zeilen </code></WRAP>+UPDATE kategorie SET kategorie_id = 5 WHERE kategorie_id = 2; -- scheitert bei referenzierten Zeilen </code> 
 +</WRAP>
  
----+=== Case: Eishockeyverein (Team → viele Blöcke) ===
  
-### Case: Eishockeyverein (»Team« → viele »Block«)+**FK:** //block.team_id// → //team.team_id//
  
-**FK:** `block.team_id` → `team.team_id` +**Daten**
- +
-**Mini-Daten**+
 <WRAP center box 80% round><code sql> <WRAP center box 80% round><code sql>
 INSERT INTO team (team_id, name, altersklasse) VALUES (100,'U18','U18'), (200,'Herren','Aktiv'); INSERT INTO team (team_id, name, altersklasse) VALUES (100,'U18','U18'), (200,'Herren','Aktiv');
-INSERT INTO block (block_id, bezeichnung, team_id) VALUES (301,'Erste Linie',100); </code></WRAP>+INSERT INTO block (block_id, bezeichnung, team_id) VALUES (301,'Erste Linie',100); </code> 
 +</WRAP>
  
 **Tests A–D** **Tests A–D**
-<WRAP center box 80% round><code sql>+<WRAP center box 80% round> 
 +<code sql>
 -- A) INSERT -- A) INSERT
 INSERT INTO block (block_id, bezeichnung, team_id) VALUES (302,'Powerplay',200);  -- ok INSERT INTO block (block_id, bezeichnung, team_id) VALUES (302,'Powerplay',200);  -- ok
Zeile 164: Zeile 174:
  
 -- B) DELETE Eltern -- B) DELETE Eltern
-DELETE FROM team WHERE team_id = 100;  -- scheitert (RESTRICT), Block 301 hängt dran+DELETE FROM team WHERE team_id = 100; -- scheitert (RESTRICT), Block 301 hängt dran
  
 -- C) UPDATE FK im Kind -- C) UPDATE FK im Kind
Zeile 170: Zeile 180:
  
 -- D) UPDATE PK in Eltern -- D) UPDATE PK in Eltern
-UPDATE team SET team_id = 250 WHERE team_id = 200;   -- scheitert, wenn Blocks auf 200 zeigen </code></WRAP>+UPDATE team SET team_id = 250 WHERE team_id = 200; -- scheitert, wenn Blocks auf 200 zeigen </code> 
 +</WRAP>
  
---- 
  
-### Case: Öffentlicher Verkehr (»Linie« → viele »Fahrt«)+=== Case: Öffentlicher Verkehr (Linie → viele Fahrten) ===
  
-**FK:** `fahrt.linien_id` → `linie.linien_id`+**FK:** //fahrt.linien_id// → //linie.linien_id//
  
-**Mini-Daten** +**Daten** 
-<WRAP center box 80% round><code sql>+<WRAP center box 80% round> 
 +<code sql>
 INSERT INTO linie (linien_id, name, betreiber) VALUES (7,'Tram 7','VBZ'), (9,'Bus 9','VBZ'); INSERT INTO linie (linien_id, name, betreiber) VALUES (7,'Tram 7','VBZ'), (9,'Bus 9','VBZ');
 INSERT INTO fahrt (fahrt_id, datum, abfahrtszeit, preis, linien_id) INSERT INTO fahrt (fahrt_id, datum, abfahrtszeit, preis, linien_id)
-VALUES (7001,'2025-11-05','07:45',3.80,7); </code></WRAP>+VALUES (7001,'2025-11-05','07:45',3.80,7);</code> 
 +</WRAP>
  
 **Tests A–D** **Tests A–D**
-<WRAP center box 80% round><code sql>+<WRAP center box 80% round> 
 +<code sql>
 -- A) INSERT -- A) INSERT
 INSERT INTO fahrt (fahrt_id, datum, abfahrtszeit, preis, linien_id) INSERT INTO fahrt (fahrt_id, datum, abfahrtszeit, preis, linien_id)
-VALUES (7002,'2025-11-05','08:15',3.80,9);   -- ok+VALUES (7002,'2025-11-05','08:15',3.80,9);  -- ok
 INSERT INTO fahrt (fahrt_id, datum, abfahrtszeit, preis, linien_id) INSERT INTO fahrt (fahrt_id, datum, abfahrtszeit, preis, linien_id)
 VALUES (7003,'2025-11-05','09:00',3.80,99);  -- FK-Fehler VALUES (7003,'2025-11-05','09:00',3.80,99);  -- FK-Fehler
  
 -- B) DELETE Eltern -- B) DELETE Eltern
-DELETE FROM linie WHERE linien_id = 7;       -- scheitert (RESTRICT)+DELETE FROM linie WHERE linien_id = 7; -- scheitert (RESTRICT)
  
 -- C) UPDATE FK im Kind -- C) UPDATE FK im Kind
Zeile 199: Zeile 212:
  
 -- D) UPDATE PK in Eltern -- D) UPDATE PK in Eltern
-UPDATE linie SET linien_id = 10 WHERE linien_id = 9;  -- scheitert bei referenzierten Zeilen </code></WRAP>+UPDATE linie SET linien_id = 10 WHERE linien_id = 9; -- scheitert bei referenzierten Zeilen 
 +</code> 
 +</WRAP>
  
---- 
  
-### Case: Schweizer Regionen (»Kanton« → viele »Gemeinde«)+=== Case: Schweizer Regionen (Kanton → viele Gemeinden) ===
  
-**FK:** `gemeinde.kanton_id` → `kanton.kanton_id`+**FK:** //gemeinde.kanton_id// → //kanton.kanton_id//
  
-**Mini-Daten** +**Daten** 
-<WRAP center box 80% round><code sql>+<WRAP center box 80% round> 
 +<code sql>
 INSERT INTO kanton (kanton_id, name, einwohnerzahl, flaeche) VALUES (1,'ZH',1550000,1729), INSERT INTO kanton (kanton_id, name, einwohnerzahl, flaeche) VALUES (1,'ZH',1550000,1729),
 (2,'GR',200000,7105); (2,'GR',200000,7105);
 INSERT INTO gemeinde (gemeinde_id, name, plz, kanton_id) INSERT INTO gemeinde (gemeinde_id, name, plz, kanton_id)
-VALUES (10001,'Zürich','8001',1); </code></WRAP>+VALUES (10001,'Zürich','8001',1); 
 +</code> 
 +</WRAP>
  
 **Tests A–D** **Tests A–D**
Zeile 218: Zeile 235:
 -- A) INSERT -- A) INSERT
 INSERT INTO gemeinde (gemeinde_id, name, plz, kanton_id) INSERT INTO gemeinde (gemeinde_id, name, plz, kanton_id)
-VALUES (10002,'Chur','7000',2);               -- ok+VALUES (10002,'Chur','7000',2); -- ok
 INSERT INTO gemeinde (gemeinde_id, name, plz, kanton_id) INSERT INTO gemeinde (gemeinde_id, name, plz, kanton_id)
-VALUES (10003,'Nowhere','9999',999);          -- FK-Fehler+VALUES (10003,'Nowhere','9999',999); -- FK-Fehler
  
 -- B) DELETE Eltern -- B) DELETE Eltern
-DELETE FROM kanton WHERE kanton_id = 1;       -- scheitert (RESTRICT), Zürich hängt dran+DELETE FROM kanton WHERE kanton_id = 1; -- scheitert (RESTRICT), Zürich hängt dran
  
 -- C) UPDATE FK im Kind -- C) UPDATE FK im Kind
Zeile 229: Zeile 246:
  
 -- D) UPDATE PK in Eltern -- D) UPDATE PK in Eltern
-UPDATE kanton SET kanton_id = 3 WHERE kanton_id = 2;          -- scheitert bei referenzierten Zeilen </code></WRAP> +UPDATE kanton SET kanton_id = 3 WHERE kanton_id = 2; -- scheitert bei referenzierten Zeilen 
- +</code> 
---- +</WRAP>
- +
-<WRAP tip round 80% center> +
-**Was ihr hier beobachtet:** +
- +
-* **RESTRICT schützt die Elternzeile**: Löschen/Primärschlüssel ändern ist blockiert, solange Kinddaten daran hängen. +
-* **INSERT/UPDATE in der Kindtabelle** ist nur dann erlaubt, wenn der **neue FK-Wert wirklich existiert**. +
-* **Umhängen** (FK im Kind ändern) ist ok, aber nur auf **existierende** Eltern. +
- +
-  </WRAP> +
  
 ==== 4) (Optional) SET NULL oder CASCADE bewusst einsetzen ==== ==== 4) (Optional) SET NULL oder CASCADE bewusst einsetzen ====
Zeile 255: Zeile 262:
 </code> </code>
  
-*Einsatzbeispiel:* ''team.trainer_id'' darf leer sein → beim Löschen eines Trainers bleibt das Team bestehen, ''trainer_id'' wird **NULL**.+//Einsatzbeispiel:// ''team.trainer_id'' darf leer sein → beim Löschen eines Trainers bleibt das Team bestehen, ''trainer_id'' wird **NULL**.
  
 **CASCADE** verwenden wir bevorzugt bei **Zwischentabellen** (N:M). **CASCADE** verwenden wir bevorzugt bei **Zwischentabellen** (N:M).
-Falls euer Case eine N:M-Erweiterung hat (z. B. im Kleidershop: *Artikel ↔ Größe*), legt ihr die Zwischen-Tabelle wie in **LU08e** an und setzt dort **CASCADE**.+Falls euer Case eine N:M-Erweiterung hat (z. B. im Kleidershop: //Artikel ↔ Grösse//), legt ihr die Zwischen-Tabelle wie in **LU08e** an und setzt dort **CASCADE**.
 Für reine 1:N-Beziehungen bleibt **RESTRICT** meist die beste Wahl. Für reine 1:N-Beziehungen bleibt **RESTRICT** meist die beste Wahl.
  
  • modul/m290_guko/learningunits/lu08/aufgaben/a_fk_in_lu05.1760910271.txt.gz
  • Zuletzt geändert: 2025/10/19 23:44
  • von gkoch