Bundesliga Schema

Bundesliga.sql

CREATE SCHEMA bundesliga;
USE bundesliga;

CREATE TABLE Schiedsrichter(
  ZulassungsNummer INTEGER PRIMARY KEY AUTO_INCREMENT,
  Vorname VARCHAR(150),
  Nachname VARCHAR(150)
);

CREATE TABLE Stadions(
  Name VARCHAR(150) PRIMARY KEY,
  Ort VARCHAR(100),
  Strasse VARCHAR(150),
  Plz VARCHAR(6),
  Hausnummer INTEGER,
  Plaetze INTEGER DEFAULT 1000
);

CREATE TABLE Spieler(
  SpielerPassNr INTEGER AUTO_INCREMENT PRIMARY KEY,
  Vorname VARCHAR(150),
  Nachname VARCHAR(150),
  Geburtsdatum DATE
);

CREATE TABLE Teams(
  Vereinsname VARCHAR(150) PRIMARY KEY,
  Ort VARCHAR(150),
  Plz VARCHAR(6),
  Strasse VARCHAR(150),
  Hausnummer INTEGER
);

CREATE TABLE SpieltFuer(
  SpielerPassNr INTEGER NOT NULL,
  Vereinsname VARCHAR(150) NOT NULL,
  VON DATE,
  BIS DATE,
  CONSTRAINT PK PRIMARY KEY (SpielerPassNr,Vereinsname)
);

ALTER TABLE SpieltFuer ADD FOREIGN KEY (SpielerPassNr) REFERENCES Spieler(SpielerPassNr);

CREATE TABLE Spiele(
  SpielID INTEGER PRIMARY KEY AUTO_INCREMENT,
  HeimMannschaft VARCHAR(150) NOT NULL,
  GastMannschaft VARCHAR(150) NOT NULL,
  toreHeimmannschaft INTEGER DEFAULT 0,
  toreGastmannschaft INTEGER DEFAULT 0,
  Schiedsrichter INTEGER NOT NULL,
  LinienrichterI INTEGER,
  LinienrichterII INTEGER,
  Stadion VARCHAR(150) NOT NULL,
  Datum DATE,
  CONSTRAINT fk_heim FOREIGN KEY (HeimMannschaft) REFERENCES Teams(Vereinsname),
  CONSTRAINT fk_gast FOREIGN KEY (GastMannschaft) REFERENCES Teams(Vereinsname),
  CONSTRAINT fk_schi FOREIGN KEY (Schiedsrichter) REFERENCES Schiedsrichter(ZulassungsNummer),
  CONSTRAINT fk_lin1 FOREIGN KEY (LinienrichterI) REFERENCES Schiedsrichter(ZulassungsNummer),
  CONSTRAINT fk_lin2 FOREIGN KEY (LinienrichterII) REFERENCES Schiedsrichter(ZulassungsNummer),
  CONSTRAINT fk_stad FOREIGN KEY (Stadion) REFERENCES Stadions(Name)
);

CREATE TABLE Tore(
  SpielerPassNr INTEGER NOT NULL,
  SpielId INTEGER NOT NULL,
  Zeitpunkt DATETIME NOT NULL,
  CONSTRAINT pk_tor PRIMARY KEY (SpielerPassNr,SpielId,Zeitpunkt),
  CONSTRAINT fk_spieler FOREIGN KEY (SpielerPassNr) REFERENCES Spieler(SpielerPassNr),
  CONSTRAINT fk_spiel FOREIGN KEY (SpielId) REFERENCES Spiele(SpielID)
);

CREATE TABLE Karten(
  SpielerPassNr INTEGER NOT NULL,
  SpielId INTEGER NOT NULL,
  Zeitpunkt DATETIME NOT NULL,
  Farbe ENUM('gelb','rot') NOT NULL,
  PRIMARY KEY (SpielerPassNr,SpielId,Zeitpunkt),
  FOREIGN KEY (SpielerPassNr) REFERENCES Spieler(SpielerPassNr),
  FOREIGN KEY (SpielId) REFERENCES Spiele(SpielID)
);

ALTER TABLE SpieltFuer ADD FOREIGN KEY (Vereinsname) REFERENCES Teams(Vereinsname);