-- MySQL Workbench Forward Engineering

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 jaagup_2016
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema jaagup_2016
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `jaagup_2016` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `jaagup_2016` ;

-- -----------------------------------------------------
-- Table `jaagup_2016`.`omanikud`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `jaagup_2016`.`omanikud` ;

CREATE TABLE IF NOT EXISTS `jaagup_2016`.`omanikud` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `eesnimi` VARCHAR(45) NULL,
  `perekonnanimi` VARCHAR(45) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `jaagup_2016`.`loomad`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `jaagup_2016`.`loomad` ;

CREATE TABLE IF NOT EXISTS `jaagup_2016`.`loomad` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `omaniku_id` INT NOT NULL,
  `loomanimi` VARCHAR(45) NULL,
  `liik` VARCHAR(45) NULL,
  `synniaeg` DATE NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_loomad_omanikud_idx` (`omaniku_id` ASC),
  CONSTRAINT `fk_loomad_omanikud`
    FOREIGN KEY (`omaniku_id`)
    REFERENCES `jaagup_2016`.`omanikud` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `jaagup_2016`.`arstid`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `jaagup_2016`.`arstid` ;

CREATE TABLE IF NOT EXISTS `jaagup_2016`.`arstid` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `eesnimi` VARCHAR(45) NOT NULL,
  `perekonnanimi` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `jaagup_2016`.`kylastused`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `jaagup_2016`.`kylastused` ;

CREATE TABLE IF NOT EXISTS `jaagup_2016`.`kylastused` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `looma_id` INT NOT NULL,
  `arsti_id` INT NOT NULL,
  `aeg` DATETIME NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_kylastused_loomad1_idx` (`looma_id` ASC),
  INDEX `fk_kylastused_arstid1_idx` (`arsti_id` ASC),
  CONSTRAINT `fk_kylastused_loomad1`
    FOREIGN KEY (`looma_id`)
    REFERENCES `jaagup_2016`.`loomad` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_kylastused_arstid1`
    FOREIGN KEY (`arsti_id`)
    REFERENCES `jaagup_2016`.`arstid` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `jaagup_2016`.`rohud`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `jaagup_2016`.`rohud` ;

CREATE TABLE IF NOT EXISTS `jaagup_2016`.`rohud` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `nimetus` VARCHAR(45) NOT NULL,
  `yhik` VARCHAR(45) NOT NULL,
  `yhikuhind` DECIMAL(8,2) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `jaagup_2016`.`valjakirjutatud_rohud`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `jaagup_2016`.`valjakirjutatud_rohud` ;

CREATE TABLE IF NOT EXISTS `jaagup_2016`.`valjakirjutatud_rohud` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `rohu_id` INT NOT NULL,
  `kylastuse_id` INT NOT NULL,
  `kogus` FLOAT NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  INDEX `fk_valjakirjutatud_rohud_rohud1_idx` (`rohu_id` ASC),
  INDEX `fk_valjakirjutatud_rohud_kylastused1_idx` (`kylastuse_id` ASC),
  CONSTRAINT `fk_valjakirjutatud_rohud_rohud1`
    FOREIGN KEY (`rohu_id`)
    REFERENCES `jaagup_2016`.`rohud` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_valjakirjutatud_rohud_kylastused1`
    FOREIGN KEY (`kylastuse_id`)
    REFERENCES `jaagup_2016`.`kylastused` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `jaagup_2016`.`protseduurid`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `jaagup_2016`.`protseduurid` ;

CREATE TABLE IF NOT EXISTS `jaagup_2016`.`protseduurid` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `nimetus` VARCHAR(45) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `jaagup_2016`.`protseduurid_loomadel`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `jaagup_2016`.`protseduurid_loomadel` ;

CREATE TABLE IF NOT EXISTS `jaagup_2016`.`protseduurid_loomadel` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `kylastuse_id` INT NOT NULL,
  `protseduuri_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_protseduurid_loomadel_kylastused1_idx` (`kylastuse_id` ASC),
  INDEX `fk_protseduurid_loomadel_protseduurid1_idx` (`protseduuri_id` ASC),
  CONSTRAINT `fk_protseduurid_loomadel_kylastused1`
    FOREIGN KEY (`kylastuse_id`)
    REFERENCES `jaagup_2016`.`kylastused` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_protseduurid_loomadel_protseduurid1`
    FOREIGN KEY (`protseduuri_id`)
    REFERENCES `jaagup_2016`.`protseduurid` (`id`)
    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;