From 33613a85afc4b1481367fbe92a17ee59c240250b Mon Sep 17 00:00:00 2001
From: Sven Eisenhauer <sven@sven-eisenhauer.net>
Date: Fri, 10 Nov 2023 15:11:48 +0100
Subject: add new repo

---
 Bachelor/Datenbanken/P5/init_p5.sql | 231 ++++++++++++++++++++++++++++++++++++
 1 file changed, 231 insertions(+)
 create mode 100644 Bachelor/Datenbanken/P5/init_p5.sql

(limited to 'Bachelor/Datenbanken/P5/init_p5.sql')

diff --git a/Bachelor/Datenbanken/P5/init_p5.sql b/Bachelor/Datenbanken/P5/init_p5.sql
new file mode 100644
index 0000000..84248fb
--- /dev/null
+++ b/Bachelor/Datenbanken/P5/init_p5.sql
@@ -0,0 +1,231 @@
+DROP DATABASE IF EXISTS busunternehmen;
+CREATE DATABASE busunternehmen
+  COLLATE latin1_german1_ci;
+USE busunternehmen;
+
+CREATE TABLE  Fahrplan (
+  fahrplannr    int NOT NULL,
+  gueltigvon    date,
+  gueltigbis    date,
+  PRIMARY KEY (fahrplannr)
+) ENGINE = InnoDB;
+
+CREATE TABLE Wochentage (
+  wochentag           varchar(10),
+  PRIMARY KEY (wochentag)
+) ENGINE = InnoDB;
+  
+
+CREATE TABLE Fahrt (
+  fahrtnr            int NOT NULL,
+  wochentage         varchar(10),
+  startzeit          TIME,
+  PRIMARY KEY (fahrtnr),
+  FOREIGN KEY (wochentage) REFERENCES Wochentage(wochentag)
+    ON UPDATE CASCADE
+    ON DELETE CASCADE
+) ENGINE = InnoDB;
+
+CREATE TABLE Bus (
+  busnr int NOT NULL,
+  kennzeichen varchar(12),
+  baujahr int,
+  sitzplaetze int,
+  PRIMARY KEY (busnr)
+) ENGINE = InnoDB;
+
+CREATE TABLE Farbe (
+  farbe           varchar(10),
+  PRIMARY KEY (farbe)
+) ENGINE = InnoDB;
+
+CREATE TABLE Route (
+  routenr int NOT NULL,
+  farbe varchar(10),
+  routeninfo varchar(255),
+  PRIMARY KEY (routenr),
+  FOREIGN KEY (farbe) REFERENCES Farbe(farbe)
+    ON UPDATE CASCADE
+    ON DELETE CASCADE
+) ENGINE = InnoDB;
+
+CREATE TABLE Routenabschnitt (
+  lfdnr int NOT NULL,
+  PRIMARY KEY (lfdnr)
+) ENGINE = InnoDB;
+
+CREATE TABLE Landkreis (
+  landkreis varchar(50),
+  PRIMARY KEY (landkreis)
+) ENGINE = InnoDB;
+
+CREATE TABLE Ort (
+  ortnr int NOT NULL,
+  ortname  varchar(50),
+  landkreis varchar(50),
+  PRIMARY KEY (ortnr)
+) ENGINE = InnoDB;
+
+CREATE TABLE Haltestelle (
+  haltestellenr int NOT NULL,
+  name varchar(50),
+  position varchar(50),
+  PRIMARY KEY (haltestellenr)
+) ENGINE = InnoDB;
+
+CREATE TABLE Strecke (
+  vonnr int,
+  nachnr int,
+  fahrzeit int,
+  entfernung float,
+  PRIMARY KEY (vonnr,nachnr)
+) ENGINE = InnoDB;
+
+CREATE TABLE Enthaelt (
+  fahrplannr int NOT NULL,
+  fahrtnr int NOT NULL,
+  PRIMARY KEY (fahrplannr,fahrtnr)
+) ENGINE = InnoDB;
+
+CREATE TABLE Setzt_ein (
+  fahrtnr int NOT NULL,
+  busnr int NOT NULL,
+  PRIMARY KEY (fahrtnr, busnr)
+) ENGINE = InnoDB;
+
+CREATE TABLE Deckt_ab (
+  fahrtnr int NOT NULL,
+  routenr int NOT NULL,
+  PRIMARY KEY (fahrtnr, routenr)
+) ENGINE = InnoDB;
+
+CREATE TABLE beinhaltet (
+  routenr int NOT NULL,
+  routenabschnittnr int NOT NULL,
+  PRIMARY KEY (routenr,routenabschnittnr)
+) ENGINE = InnoDB;
+
+CREATE TABLE verwendet_als (
+  strecke_von int NOT NULL,
+  strecke_nach int NOT NULL,
+  routenabschnittnr int NOT NULL,
+  PRIMARY KEY (strecke_von,strecke_nach,routenabschnittnr)
+) ENGINE = InnoDB;
+
+CREATE TABLE Von (
+  strecke_von int NOT NULL,
+  strecke_nach int NOT NULL,
+  haltestellenr int NOT NULL,
+  PRIMARY KEY (strecke_von,strecke_nach,haltestellenr)
+) ENGINE = InnoDB;
+
+CREATE TABLE Nach (
+  strecke_von int NOT NULL,
+  strecke_nach int NOT NULL,
+  haltestellenr int NOT NULL,
+  PRIMARY KEY (strecke_von,strecke_nach,haltestellenr)
+) ENGINE = InnoDB;
+
+CREATE TABLE liegt_in (
+  haltestellenr int NOT NULL,
+  ortnr int NOT NULL,
+  PRIMARY KEY (haltestellenr,ortnr)
+) ENGINE = InnoDB;
+    
+ALTER TABLE Ort ADD
+  FOREIGN KEY (landkreis) REFERENCES Landkreis(landkreis)
+    ON UPDATE CASCADE
+    ON DELETE CASCADE;
+
+ALTER TABLE Strecke ADD
+  FOREIGN KEY (vonnr) REFERENCES Haltestelle(haltestellenr)
+    ON UPDATE CASCADE
+    ON DELETE CASCADE;
+ALTER TABLE Strecke ADD
+  FOREIGN KEY (nachnr) REFERENCES Haltestelle(haltestellenr)
+    ON UPDATE CASCADE
+    ON DELETE CASCADE;
+
+ALTER TABLE Enthaelt ADD
+  FOREIGN KEY (fahrplannr) REFERENCES Fahrplan(fahrplannr)
+    ON UPDATE CASCADE
+    ON DELETE CASCADE;
+ALTER TABLE Enthaelt ADD
+  FOREIGN KEY (fahrtnr) REFERENCES Fahrt(fahrtnr)
+    ON UPDATE CASCADE
+    ON DELETE CASCADE;
+
+ALTER TABLE Setzt_ein ADD
+  FOREIGN KEY (busnr) REFERENCES Bus(busnr)
+    ON UPDATE CASCADE
+    ON DELETE CASCADE;
+ALTER TABLE Setzt_ein ADD
+  FOREIGN KEY (fahrtnr) REFERENCES Fahrt(fahrtnr)
+    ON UPDATE CASCADE
+    ON DELETE CASCADE;
+    
+ALTER TABLE Deckt_ab ADD
+  FOREIGN KEY (routenr) REFERENCES Route(routenr)
+    ON UPDATE CASCADE
+    ON DELETE CASCADE;
+ALTER TABLE Deckt_ab ADD
+  FOREIGN KEY (fahrtnr) REFERENCES Fahrt(fahrtnr)
+    ON UPDATE CASCADE
+    ON DELETE CASCADE;
+    
+ALTER TABLE beinhaltet ADD
+  FOREIGN KEY (routenr) REFERENCES Route(routenr)
+    ON UPDATE CASCADE
+    ON DELETE CASCADE;
+ALTER TABLE beinhaltet ADD
+  FOREIGN KEY (routenabschnittnr) REFERENCES Routenabschnitt(lfdnr)
+    ON UPDATE CASCADE
+    ON DELETE CASCADE;
+    
+ALTER TABLE verwendet_als ADD
+  FOREIGN KEY (strecke_nach) REFERENCES Strecke(nachnr)
+    ON UPDATE CASCADE
+    ON DELETE CASCADE;
+ALTER TABLE verwendet_als ADD
+  FOREIGN KEY (strecke_von) REFERENCES Strecke(vonnr)
+    ON UPDATE CASCADE
+    ON DELETE CASCADE;
+ALTER TABLE verwendet_als ADD
+  FOREIGN KEY (routenabschnittnr) REFERENCES Routenabschnitt(lfdnr)
+    ON UPDATE CASCADE
+    ON DELETE CASCADE;
+    
+ALTER TABLE Von ADD
+  FOREIGN KEY (strecke_nach) REFERENCES Strecke(nachnr)
+    ON UPDATE CASCADE
+    ON DELETE CASCADE;
+ALTER TABLE Von ADD
+  FOREIGN KEY (strecke_von) REFERENCES Strecke(vonnr)
+    ON UPDATE CASCADE
+    ON DELETE CASCADE;
+ALTER TABLE Von ADD
+  FOREIGN KEY (haltestellenr) REFERENCES Haltestelle(haltestellenr)
+    ON UPDATE CASCADE
+    ON DELETE CASCADE;
+
+ALTER TABLE Nach ADD
+  FOREIGN KEY (strecke_nach) REFERENCES Strecke(nachnr)
+    ON UPDATE CASCADE
+    ON DELETE CASCADE;
+ALTER TABLE Nach ADD
+  FOREIGN KEY (strecke_von) REFERENCES Strecke(vonnr)
+    ON UPDATE CASCADE
+    ON DELETE CASCADE;
+ALTER TABLE Nach ADD
+  FOREIGN KEY (haltestellenr) REFERENCES Haltestelle(haltestellenr)
+    ON UPDATE CASCADE
+    ON DELETE CASCADE;
+    
+ALTER TABLE liegt_in ADD
+  FOREIGN KEY (haltestellenr) REFERENCES Haltestelle(haltestellenr)
+    ON UPDATE CASCADE
+    ON DELETE CASCADE;
+ALTER TABLE liegt_in ADD
+  FOREIGN KEY (ortnr) REFERENCES Ort(ortnr)
+    ON UPDATE CASCADE
+    ON DELETE CASCADE;
-- 
cgit v1.3

