add new repoHEADmaster

1 files changed, 231 insertions, 0 deletions

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;