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;