DROP DATABASE IF EXISTS OPPlanung;
CREATE DATABASE OPPlanung;
USE OPPlanung;

CREATE TABLE `OPPlanung`.`Chirurg` (
  `PersNr` CHAR(8)  NOT NULL DEFAULT '',
  `Name` VARCHAR(20)  NOT NULL DEFAULT '',
  PRIMARY KEY(`PersNr`)
)
ENGINE = InnoDB;

CREATE TABLE `OPPlanung`.`Operation` (
   `OPNr` CHAR(8)  NOT NULL DEFAULT '',
   `OPDatum` DATE  NOT NULL DEFAULT 0,
   `Anfang` TIME  NOT NULL DEFAULT 0,
   `PersNr` CHAR(8)  NOT NULL DEFAULT '',
   PRIMARY KEY(`OPNr`),
   CONSTRAINT `leiter` FOREIGN KEY `leiter` (`PersNr`)
     REFERENCES Chirurg (`PersNr`)
     ON DELETE RESTRICT
     ON UPDATE RESTRICT
 ) ENGINE = InnoDB;

CREATE TABLE `OPPlanung`.`Assistenz` (
  `PersNr` CHAR(8)  NOT NULL DEFAULT '',
  `OPNr` CHAR(8)  NOT NULL DEFAULT '',
  `Rolle` VARCHAR(20)  NOT NULL DEFAULT '',
  PRIMARY KEY(`PersNr`, `OPNr`),
  CONSTRAINT `assi-pers` FOREIGN KEY `assi-pers` (`PersNr`)
    REFERENCES `Chirurg` (`PersNr`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT,
  CONSTRAINT `assi-op` FOREIGN KEY `assi-op` (`OPNr`)
    REFERENCES `Operation`  (`OPNr`)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
)
ENGINE = InnoDB;