Unterschiede

Hier werden die Unterschiede zwischen zwei Versionen angezeigt.

--- modul:m290_guko:learningunits:lu08:theorie:c_fk-create-table [2025/10/11 08:48] – angelegt gkoch
+++ modul:m290_guko:learningunits:lu08:theorie:c_fk-create-table [2025/10/11 09:30] (aktuell) – gkoch
@@ Zeile 1: / Zeile 1: @@
 ====== LU08c – Fremdschlüssel direkt beim Erstellen (CREATE TABLE) ======
-Wir definieren drei Tabellen passend zum ERD.
+Wir setzen das ERD in SQL um und füllen es mit europäischen Beispielen (DE/CH/AT/FR/IT).
-<code sql>
+<WRAP center box 80% round><code sql>
-DROP TABLE IF EXISTS film_cast;
+DROP TABLE IF EXISTS Trip;
-DROP TABLE IF EXISTS film;
+DROP TABLE IF EXISTS Country;
-DROP TABLE IF EXISTS person;
+DROP TABLE IF EXISTS Continent;
-CREATE TABLE person (
+CREATE TABLE Continent (
-  person_id INT AUTO_INCREMENT PRIMARY KEY,
+  ContinentID   INT AUTO_INCREMENT PRIMARY KEY,
-  name VARCHAR(100) NOT NULL,
+  ContinentName VARCHAR(50) NOT NULL UNIQUE
-  UNIQUE(name)
 );
-CREATE TABLE film (
+CREATE TABLE Country (
-  film_id INT AUTO_INCREMENT PRIMARY KEY,
+  CountryID     INT AUTO_INCREMENT PRIMARY KEY,
-  title VARCHAR(100) NOT NULL,
+  CountryName   VARCHAR(100) NOT NULL UNIQUE,
-  released_year YEAR,
+  ContinentID   INT NOT NULL,
-  director_id INT NULL,  -- optional: Film kann auch ohne gesetzte Regie existieren
+  CONSTRAINT fk_country_continent
-  CONSTRAINT fk_film_director
+    FOREIGN KEY (ContinentID)
-    FOREIGN KEY (director_id)
+    REFERENCES Continent(ContinentID)
-    REFERENCES person(person_id)
+    ON DELETE RESTRICT
-    ON DELETE SET NULL
     ON UPDATE RESTRICT
 );
-CREATE TABLE film_cast (
+CREATE TABLE Trip (
-  film_id INT NOT NULL,
+  TripID     INT AUTO_INCREMENT PRIMARY KEY,
-  person_id INT NOT NULL,
+  CountryID  INT NOT NULL,
-  role VARCHAR(50) DEFAULT 'Actor',
+  StartDate  DATE NOT NULL,
-  PRIMARY KEY (film_id, person_id),
+  EndDate    DATE NOT NULL,
-  CONSTRAINT fk_cast_film
+  Price      DECIMAL(10,2) NOT NULL,
-    FOREIGN KEY (film_id)
+  CONSTRAINT fk_trip_country
-    REFERENCES film(film_id)
+    FOREIGN KEY (CountryID)
-    ON DELETE CASCADE
+    REFERENCES Country(CountryID)
-    ON UPDATE CASCADE,
-  CONSTRAINT fk_cast_person
-    FOREIGN KEY (person_id)
-    REFERENCES person(person_id)
     ON DELETE RESTRICT
     ON UPDATE RESTRICT
 );
-</code>
+</code></WRAP>
+**Warum diese Referenzaktionen?**
+  * **RESTRICT** bei Country→Continent: Kontinente löscht man nicht „einfach so“.
+  * **RESTRICT** bei Trip→Country: Eine Reise muss zu einem existierenden Land gehören.
+===== Beispiel-Daten (Europa) =====
+<WRAP center box 80% round><code sql>
+-- Kontinent
+INSERT INTO Continent (ContinentName) VALUES ('Europa');
+-- Länder (DE/CH/AT/FR/IT)
+INSERT INTO Country (CountryName, ContinentID)
+SELECT x.CountryName, c.ContinentID
+FROM (SELECT 'Deutschland' CountryName UNION ALL
+      SELECT 'Schweiz'    UNION ALL
+      SELECT 'Österreich' UNION ALL
+      SELECT 'Frankreich' UNION ALL
+      SELECT 'Italien') x
+JOIN Continent c ON c.ContinentName='Europa';
+-- Reisen (Beispieldaten, Datum & Preis in EUR/CHF gemischt ist ok, wir speichern nur Zahl)
+INSERT INTO Trip (CountryID, StartDate, EndDate, Price)
+SELECT CountryID, '2025-07-12', '2025-07-20', 1250.00 FROM Country WHERE CountryName='Italien';
+INSERT INTO Trip (CountryID, StartDate, EndDate, Price)
+SELECT CountryID, '2025-10-05', '2025-10-11',  820.00 FROM Country WHERE CountryName='Deutschland';
+INSERT INTO Trip (CountryID, StartDate, EndDate, Price)
+SELECT CountryID, '2025-02-15', '2025-02-19',  590.00 FROM Country WHERE CountryName='Schweiz';
+</code></WRAP>
+===== Kontroll-Selects =====
+<WRAP center box 80% round><code sql>
+SELECT ContinentID, ContinentName FROM Continent;
+SELECT CountryID, CountryName, ContinentID FROM Country ORDER BY CountryName;
+SELECT TripID, CountryID, StartDate, EndDate, Price FROM Trip ORDER BY StartDate;
+</code></WRAP>
-**Begründung der Aktionen:**
+**Hinweis:** Die Reisedauer kann berechnet werden (z. B. `DATEDIFF(EndDate, StartDate)`), sie wird nicht gespeichert.
-  * **director_id → SET NULL**: Regie-Person darf verschwinden, Film bleibt erhalten (FK wird NULL).
-  * **film_cast.film_id → CASCADE**: Wird ein Film gelöscht/umbenannt, folgt die Besetzung automatisch.
-  * **film_cast.person_id → RESTRICT**: Eine besetzte Person kann nicht einfach gelöscht werden.