text: -- MySQL Script generated by MySQL Workbench
  -- Model: New Model    Version: 1.0
  -- MySQL Workbench Forward Engineering
  -- INF2014.6G
  
  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';
  
  -- -----------------------------------------------------
  -- Schema Fahrradverleih
  -- -----------------------------------------------------
  
  -- -----------------------------------------------------
  -- Schema Fahrradverleih
  -- -----------------------------------------------------
  CREATE SCHEMA IF NOT EXISTS `Fahrradverleih` DEFAULT CHARACTER SET utf8 ;
  USE `Fahrradverleih` ;
  
  -- -----------------------------------------------------
  -- Table `Fahrradverleih`.`Adresse`
  -- -----------------------------------------------------
  CREATE TABLE IF NOT EXISTS `Fahrradverleih`.`Adresse` (
    `Id` INT NOT NULL AUTO_INCREMENT,
    `Strasse` NVARCHAR(255) NOT NULL,
    `Nr` NVARCHAR(80) NOT NULL,
    `PLZ` INT NOT NULL,
    `Ort` NVARCHAR(255) NOT NULL,
    PRIMARY KEY (`Id`),
    INDEX `PK_Adresse` (`Id` ASC),
    INDEX `IDX_PLZ` (`PLZ` ASC),
    INDEX `IDX_Ort` (`Ort` ASC));
  
  
  -- -----------------------------------------------------
  -- Table `Fahrradverleih`.`Person`
  -- -----------------------------------------------------
  CREATE TABLE IF NOT EXISTS `Fahrradverleih`.`Person` (
    `Id` INT NOT NULL AUTO_INCREMENT,
    `Anrede` NVARCHAR(256) NOT NULL,
    `Vorname` NVARCHAR(255) NOT NULL,
    `Name` NVARCHAR(255) NOT NULL,
    `Mobile` NVARCHAR(80) NOT NULL,
    `Adresse_Id` INT NOT NULL,
    PRIMARY KEY (`Id`),
    INDEX (`Adresse_Id` ASC),
    INDEX `PK_Kunde` (`Id` ASC),
    INDEX `IDX_Anrede` (`Anrede` ASC),
    INDEX `IDX_Adresse_FK` (`Adresse_Id` ASC),
    CONSTRAINT `FK_Kunde_Adresse`
      FOREIGN KEY (`Adresse_Id`)
      REFERENCES `Fahrradverleih`.`Adresse` (`Id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE);
  
  
  -- -----------------------------------------------------
  -- Table `Fahrradverleih`.`Marke`
  -- -----------------------------------------------------
  CREATE TABLE IF NOT EXISTS `Fahrradverleih`.`Marke` (
    `Id` INT NOT NULL AUTO_INCREMENT,
    `Name` NVARCHAR(255) NOT NULL,
    PRIMARY KEY (`Id`),
    INDEX `PK_Marke` (`Id` ASC));
  
  
  -- -----------------------------------------------------
  -- Table `Fahrradverleih`.`Typ`
  -- -----------------------------------------------------
  CREATE TABLE IF NOT EXISTS `Fahrradverleih`.`Typ` (
    `Id` INT NOT NULL AUTO_INCREMENT,
    `Name` NVARCHAR(255) NOT NULL,
    PRIMARY KEY (`Id`),
    INDEX `PK_Typ` (`Id` ASC));
  
  
  -- -----------------------------------------------------
  -- Table `Fahrradverleih`.`Modell`
  -- -----------------------------------------------------
  CREATE TABLE IF NOT EXISTS `Fahrradverleih`.`Modell` (
    `Id` INT NOT NULL AUTO_INCREMENT,
    `Name` NVARCHAR(255) NOT NULL,
    `Typ_Id` INT NOT NULL,
    `Marke_Id` INT NOT NULL,
    PRIMARY KEY (`Id`),
    INDEX (`Marke_Id` ASC),
    INDEX (`Typ_Id` ASC),
    INDEX `PK_Modell` (`Id` ASC),
    INDEX `IDX_Name` (`Name` ASC),
    CONSTRAINT `FK_Modell_Marke`
      FOREIGN KEY (`Marke_Id`)
      REFERENCES `Fahrradverleih`.`Marke` (`Id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
    CONSTRAINT `FK_Modell_Typ`
      FOREIGN KEY (`Typ_Id`)
      REFERENCES `Fahrradverleih`.`Typ` (`Id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE);
  
  
  -- -----------------------------------------------------
  -- Table `Fahrradverleih`.`Zustand`
  -- -----------------------------------------------------
  CREATE TABLE IF NOT EXISTS `Fahrradverleih`.`Zustand` (
    `Id` INT NOT NULL AUTO_INCREMENT,
    `Name` NVARCHAR(255) NOT NULL,
    PRIMARY KEY (`Id`),
    INDEX `PK_Zustand` (`Id` ASC));
  
  
  -- -----------------------------------------------------
  -- Table `Fahrradverleih`.`Velo`
  -- -----------------------------------------------------
  CREATE TABLE IF NOT EXISTS `Fahrradverleih`.`Velo` (
    `Id` INT NOT NULL AUTO_INCREMENT,
    `Kaufdatum` DATETIME NOT NULL,
    `Modell_Id` INT NOT NULL,
    `Zustand_Id` INT NOT NULL,
    PRIMARY KEY (`Id`),
    INDEX (`Modell_Id` ASC),
    INDEX (`Zustand_Id` ASC),
    INDEX `PK_Velo` (`Id` ASC),
    CONSTRAINT `FK_Velo_Modell`
      FOREIGN KEY (`Modell_Id`)
      REFERENCES `Fahrradverleih`.`Modell` (`Id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
    CONSTRAINT `FK_Velo_Zustand`
      FOREIGN KEY (`Zustand_Id`)
      REFERENCES `Fahrradverleih`.`Zustand` (`Id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE);
  
  
  -- -----------------------------------------------------
  -- Table `Fahrradverleih`.`Mitarbeiter`
  -- -----------------------------------------------------
  CREATE TABLE IF NOT EXISTS `Fahrradverleih`.`Mitarbeiter` (
    `Person_Id` INT NOT NULL,
    `Beruf` VARCHAR(256) NOT NULL,
    `Lohn` FLOAT NOT NULL,
    INDEX `Person_Id_idx` (`Person_Id` ASC),
    INDEX `IDX_Beruf` (`Beruf` ASC),
    CONSTRAINT `FK_Mitarbeiter_Person`
      FOREIGN KEY (`Person_Id`)
      REFERENCES `Fahrradverleih`.`Person` (`Id`)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION)
  ENGINE = InnoDB;
  
  
  -- -----------------------------------------------------
  -- Table `Fahrradverleih`.`Kunde`
  -- -----------------------------------------------------
  CREATE TABLE IF NOT EXISTS `Fahrradverleih`.`Kunde` (
    `Person_Id` INT NOT NULL,
    `Email` NVARCHAR(256) NOT NULL,
    `Newsletter` TINYINT NOT NULL,
    INDEX `fk_Kunde_Person1_idx` (`Person_Id` ASC),
    CONSTRAINT `fk_Kunde_Person1`
      FOREIGN KEY (`Person_Id`)
      REFERENCES `Fahrradverleih`.`Person` (`Adresse_Id`)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION)
  ENGINE = InnoDB;
  
  
  -- -----------------------------------------------------
  -- Table `Fahrradverleih`.`Verleih`
  -- -----------------------------------------------------
  CREATE TABLE IF NOT EXISTS `Fahrradverleih`.`Verleih` (
    `Id` INT NOT NULL AUTO_INCREMENT,
    `Preis` DOUBLE NOT NULL,
    `Start_Datum` DATETIME NOT NULL,
    `End_Datum` DATETIME NOT NULL,
    `Velo_Id` INT NOT NULL,
    `Kunde_Id` INT NOT NULL,
    `Mitarbeiter_Id` INT NOT NULL,
    PRIMARY KEY (`Id`),
    INDEX (`Velo_Id` ASC),
    INDEX `PK_Verleih` (`Id` ASC),
    INDEX `FK_Verleih_Mitarbeiter_idx` (`Mitarbeiter_Id` ASC),
    INDEX `FK_Verleih_Kunde_idx` (`Kunde_Id` ASC),
    INDEX `IDX_Preis` (`Preis` ASC),
    CONSTRAINT `FK_Verleih_Velo`
      FOREIGN KEY (`Velo_Id`)
      REFERENCES `Fahrradverleih`.`Velo` (`Id`)
      ON DELETE CASCADE
      ON UPDATE CASCADE,
    CONSTRAINT `FK_Verleih_Mitarbeiter`
      FOREIGN KEY (`Mitarbeiter_Id`)
      REFERENCES `Fahrradverleih`.`Mitarbeiter` (`Person_Id`)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
    CONSTRAINT `FK_Verleih_Kunde`
      FOREIGN KEY (`Kunde_Id`)
      REFERENCES `Fahrradverleih`.`Kunde` (`Person_Id`)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION);
  
  
  SET SQL_MODE=@OLD_SQL_MODE;
  SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
  SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
  