CREATE SCHEMA IF NOT EXISTS `managementtool` DEFAULT CHARACTER SET latin1 ;
USE `managementtool` ;

-- -----------------------------------------------------
-- Table `managementtool`.`kostenstelle`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `managementtool`.`kostenstelle` (
  `kostenstelle_id` INT NOT NULL AUTO_INCREMENT ,
  `kst_name` VARCHAR(45) NOT NULL ,
  `kst_nr` VARCHAR(10) NOT NULL ,
  PRIMARY KEY (`kostenstelle_id`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;


-- -----------------------------------------------------
-- Table `managementtool`.`projekt`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `managementtool`.`projekt` (
  `projekt_id` INT NOT NULL AUTO_INCREMENT ,
  `projekt_name` VARCHAR(45) NOT NULL ,
  `projekt_nr` INT NOT NULL ,
  `projekt_jahr` YEAR NOT NULL ,
  `projekt_budget` INT NOT NULL ,
  PRIMARY KEY (`projekt_id`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;


-- -----------------------------------------------------
-- Table `managementtool`.`beschaffung`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `managementtool`.`beschaffung` (
  `beschaffung_id` INT NOT NULL AUTO_INCREMENT ,
  `bereich` VARCHAR(45) NOT NULL ,
  `datum` DATE NOT NULL ,
  `laborleiter` VARCHAR(45) NOT NULL ,
  `laboringenieur` VARCHAR(45) NOT NULL ,
  `haus` VARCHAR(45) NOT NULL ,
  `raum` VARCHAR(45) NOT NULL ,
  `wertgrenzen` VARCHAR(45) NOT NULL ,
  `aufstellungsort` VARCHAR(45) NOT NULL ,
  `lieferfirma` VARCHAR(60) NOT NULL ,
  `einbauteil` TINYINT(1)  NOT NULL ,
  `inventar_nr1` VARCHAR(10) NOT NULL ,
  `ersatzteil` TINYINT(1)  NOT NULL ,
  `inventar_nr2` VARCHAR(10) NOT NULL ,
  `seriennummer` VARCHAR(10) NOT NULL ,
  `bezeichnung` VARCHAR(45) NOT NULL ,
  `notwendigkeit` VARCHAR(380) NOT NULL ,
  `folgekosten` VARCHAR(600) NOT NULL ,
  `sonstiges` VARCHAR(380) NOT NULL ,
  `kostenstelle_id` INT NOT NULL ,
  `projekt_id` INT NOT NULL ,
  PRIMARY KEY (`beschaffung_id`) ,
  INDEX `fk_beschaffung_kostenstelle` (`kostenstelle_id` ASC) ,
  INDEX `fk_beschaffung_projekt` (`projekt_id` ASC) ,
  CONSTRAINT `fk_beschaffung_kostenstelle`
    FOREIGN KEY (`kostenstelle_id` )
    REFERENCES `managementtool`.`kostenstelle` (`kostenstelle_id` )
    ON DELETE RESTRICT
    ON UPDATE RESTRICT,
  CONSTRAINT `fk_beschaffung_projekt`
    FOREIGN KEY (`projekt_id` )
    REFERENCES `managementtool`.`projekt` (`projekt_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;



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

SHOW INNODB STATUS

SHOW ENGINE INNODB STATUS

SHOW ENGINE INNODB STATUS