From 33613a85afc4b1481367fbe92a17ee59c240250b Mon Sep 17 00:00:00 2001 From: Sven Eisenhauer 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.2.3