SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

DROP SCHEMA IF EXISTS `musikakademie` ;
CREATE SCHEMA IF NOT EXISTS `musikakademie` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `musikakademie` ;

-- -----------------------------------------------------
-- Table `musikakademie`.`schueler`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `musikakademie`.`schueler` ;

CREATE TABLE IF NOT EXISTS `musikakademie`.`schueler` (
  `schuelerNr` INT NOT NULL,
  `vorname` VARCHAR(45) NULL,
  `nachname` VARCHAR(45) NULL,
  `geburtsdatum` DATE NULL,
  PRIMARY KEY (`schuelerNr`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `musikakademie`.`lehrer`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `musikakademie`.`lehrer` ;

CREATE TABLE IF NOT EXISTS `musikakademie`.`lehrer` (
  `lehrerNr` INT NOT NULL,
  `vorname` VARCHAR(45) NULL,
  `nachname` VARCHAR(45) NULL,
  `preis_pro_stunde` DOUBLE NULL,
  PRIMARY KEY (`lehrerNr`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `musikakademie`.`instrumente`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `musikakademie`.`instrumente` ;

CREATE TABLE IF NOT EXISTS `musikakademie`.`instrumente` (
  `instrumentNr` INT NOT NULL,
  `bezeichnung` VARCHAR(45) NULL,
  `zuschlagssatz` DOUBLE NULL,
  PRIMARY KEY (`instrumentNr`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `musikakademie`.`unterrichte`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `musikakademie`.`unterrichte` ;

CREATE TABLE IF NOT EXISTS `musikakademie`.`unterrichte` (
  `unterrichtNr` INT NOT NULL,
  `instrumentNr` INT NOT NULL,
  `lehrerNr` INT NOT NULL,
  PRIMARY KEY (`unterrichtNr`),
  INDEX `fk_unterrichte_instrumente_idx` (`instrumentNr` ASC),
  INDEX `fk_unterrichte_lehrer1_idx` (`lehrerNr` ASC),
  CONSTRAINT `fk_unterrichte_instrumente`
    FOREIGN KEY (`instrumentNr`)
    REFERENCES `musikakademie`.`instrumente` (`instrumentNr`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_unterrichte_lehrer1`
    FOREIGN KEY (`lehrerNr`)
    REFERENCES `musikakademie`.`lehrer` (`lehrerNr`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `musikakademie`.`unterrichtsstunden`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `musikakademie`.`unterrichtsstunden` ;

CREATE TABLE IF NOT EXISTS `musikakademie`.`unterrichtsstunden` (
  `stundenNr` INT NOT NULL,
  `datum` DATE NULL,
  `schuelerNr` INT NOT NULL,
  `unterrichtNr` INT NOT NULL,
  PRIMARY KEY (`stundenNr`),
  INDEX `fk_unterrichtsstunden_unterrichte1_idx` (`unterrichtNr` ASC),
  INDEX `fk_unterrichtsstunden_schueler1_idx` (`schuelerNr` ASC),
  CONSTRAINT `fk_unterrichtsstunden_unterrichte1`
    FOREIGN KEY (`unterrichtNr`)
    REFERENCES `musikakademie`.`unterrichte` (`unterrichtNr`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_unterrichtsstunden_schueler1`
    FOREIGN KEY (`schuelerNr`)
    REFERENCES `musikakademie`.`schueler` (`schuelerNr`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
