baze

-- MySQL Script generated by MySQL Workbench
-- Thu Dec 14 19:20:01 2017
-- Model: New Model    Version: 1.0
-- 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 mydb
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`grupa`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`grupa` (
  `ID_grupa` INT UNSIGNED ZEROFILL NOT NULL,
  `naziv_grupa` VARCHAR(45) NULL,
  PRIMARY KEY (`ID_grupa`),
  UNIQUE INDEX `idtip_kuhinja_jelo_UNIQUE` (`ID_grupa` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`tip`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`tip` (
  `ID_tip` INT ZEROFILL UNSIGNED NOT NULL AUTO_INCREMENT,
  `naziv_tip` VARCHAR(45) NULL,
  PRIMARY KEY (`ID_tip`),
  UNIQUE INDEX `id_tip_UNIQUE` (`ID_tip` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`kuhar`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`kuhar` (
  `ID_kuhar` INT ZEROFILL UNSIGNED NOT NULL AUTO_INCREMENT,
  `ime_kuhar` VARCHAR(45) NULL,
  `prezime_kuhar` VARCHAR(45) NULL,
  `datum_rod` DATE NULL,
  `adresa_kuhar` VARCHAR(45) NULL,
  `grad_kuhar` VARCHAR(45) NULL,
  `postanski_broj_kuhar` VARCHAR(45) NULL,
  `placa_kuhar` FLOAT NULL,
  `tel_kuhar` VARCHAR(45) NULL,
  `mail_kuhar` VARCHAR(45) NULL,
  `lozinka_kuhar` VARCHAR(45) NULL,
  PRIMARY KEY (`ID_kuhar`),
  UNIQUE INDEX `id_kuhar_UNIQUE` (`ID_kuhar` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`jelo`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`jelo` (
  `ID_jelo` INT(100) UNSIGNED ZEROFILL NOT NULL,
  `tip_ID_tip` INT ZEROFILL UNSIGNED NOT NULL,
  `grupa_ID_grupa` INT UNSIGNED ZEROFILL NOT NULL,
  `kuhar_ID_kuhar` INT ZEROFILL UNSIGNED NOT NULL,
  `ime_jelo` VARCHAR(45) NULL,
  `cijena` INT(4) UNSIGNED NULL,
  `ocjena` FLOAT UNSIGNED NULL,
  `opis` VARCHAR(100) NULL,
  `preporuka` VARCHAR(50) NULL,
  PRIMARY KEY (`ID_jelo`, `tip_ID_tip`, `grupa_ID_grupa`, `kuhar_ID_kuhar`),
  UNIQUE INDEX `idjelo_UNIQUE` (`ID_jelo` ASC),
  INDEX `fk_jelo_tip_kuhinja_jelo1_idx` (`grupa_ID_grupa` ASC),
  INDEX `fk_jelo_tip1_idx` (`tip_ID_tip` ASC),
  INDEX `fk_jelo_kuhar1_idx` (`kuhar_ID_kuhar` ASC),
  CONSTRAINT `fk_jelo_tip_kuhinja_jelo1`
    FOREIGN KEY (`grupa_ID_grupa`)
    REFERENCES `mydb`.`grupa` (`ID_grupa`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_jelo_tip1`
    FOREIGN KEY (`tip_ID_tip`)
    REFERENCES `mydb`.`tip` (`ID_tip`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_jelo_kuhar1`
    FOREIGN KEY (`kuhar_ID_kuhar`)
    REFERENCES `mydb`.`kuhar` (`ID_kuhar`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`sastojak`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`sastojak` (
  `ID_sastojak` INT ZEROFILL UNSIGNED NOT NULL AUTO_INCREMENT,
  `naziv_sastojak` VARCHAR(45) NULL,
  `kolicina_sastojak` FLOAT(15) NULL,
  `cijena_sastojak` FLOAT(15) NULL,
  `kalorije_sastojak` INT NULL,
  PRIMARY KEY (`ID_sastojak`),
  UNIQUE INDEX `id_sastojci_UNIQUE` (`ID_sastojak` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`konobar`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`konobar` (
  `ID_konobar` INT UNSIGNED ZEROFILL NOT NULL,
  `ime_konobar` VARCHAR(45) NULL,
  `prezime_konobar` VARCHAR(45) NULL,
  `datum_rod_konobar` DATE NULL,
  `adresa_konobar` VARCHAR(45) NULL,
  `grad_konobar` VARCHAR(45) NULL,
  `postanski_broj_konobar` VARCHAR(45) NULL,
  `placa_konobar` FLOAT NULL,
  `tel_konobar` VARCHAR(45) NULL,
  `mail_konobar` VARCHAR(45) NULL,
  `lozinka_konobar` VARCHAR(45) NULL,
  PRIMARY KEY (`ID_konobar`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`gost`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`gost` (
  `ID_gost` INT ZEROFILL UNSIGNED NOT NULL AUTO_INCREMENT,
  `ime_gost` VARCHAR(45) NULL,
  `prezime_gost` VARCHAR(45) NULL,
  `adresa_gost` VARCHAR(45) NULL,
  `sifra_gost` VARCHAR(45) NULL,
  `postanski_broj_gost` VARCHAR(45) NULL,
  `bodovi_gost` INT NULL,
  `mail_gost` VARCHAR(45) NULL,
  `lozinka_gost` VARCHAR(15) NULL,
  PRIMARY KEY (`ID_gost`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`stol`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`stol` (
  `ID_stol` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tip_stol` VARCHAR(45) NULL,
  `br_mjesta_stol` INT UNSIGNED NULL,
  PRIMARY KEY (`ID_stol`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`račun`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`račun` (
  `ID_račun` INT ZEROFILL UNSIGNED NOT NULL AUTO_INCREMENT,
  `konobar_ID_konobar` INT UNSIGNED ZEROFILL NOT NULL,
  `stol_ID_stol` INT UNSIGNED NOT NULL,
  `gost_ID_gost` INT ZEROFILL UNSIGNED NOT NULL,
  `datum` DATE NULL,
  `iznost_racun` FLOAT(45) NULL,
  PRIMARY KEY (`ID_račun`, `konobar_ID_konobar`, `stol_ID_stol`, `gost_ID_gost`),
  INDEX `fk_račun_konobar1_idx` (`konobar_ID_konobar` ASC),
  INDEX `fk_račun_gost1_idx` (`gost_ID_gost` ASC),
  INDEX `fk_račun_stol1_idx` (`stol_ID_stol` ASC),
  CONSTRAINT `fk_račun_konobar1`
    FOREIGN KEY (`konobar_ID_konobar`)
    REFERENCES `mydb`.`konobar` (`ID_konobar`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_račun_gost1`
    FOREIGN KEY (`gost_ID_gost`)
    REFERENCES `mydb`.`gost` (`ID_gost`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_račun_stol1`
    FOREIGN KEY (`stol_ID_stol`)
    REFERENCES `mydb`.`stol` (`ID_stol`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`sastojci_jela`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`sastojci_jela` (
  `jelo_ID_jelo` INT UNSIGNED ZEROFILL NOT NULL,
  `sastojak_ID_sastojak` INT ZEROFILL UNSIGNED NOT NULL,
  PRIMARY KEY (`jelo_ID_jelo`, `sastojak_ID_sastojak`),
  INDEX `fk_jelo_has_sastojak_sastojak1_idx` (`sastojak_ID_sastojak` ASC),
  INDEX `fk_jelo_has_sastojak_jelo_idx` (`jelo_ID_jelo` ASC),
  CONSTRAINT `fk_jelo_has_sastojak_jelo`
    FOREIGN KEY (`jelo_ID_jelo`)
    REFERENCES `mydb`.`jelo` (`ID_jelo`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_jelo_has_sastojak_sastojak1`
    FOREIGN KEY (`sastojak_ID_sastojak`)
    REFERENCES `mydb`.`sastojak` (`ID_sastojak`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`jela_na_račun`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`jela_na_račun` (
  `jelo_ID_jelo` INT UNSIGNED ZEROFILL NOT NULL,
  `račun_ID_račun` INT ZEROFILL UNSIGNED NOT NULL,
  PRIMARY KEY (`jelo_ID_jelo`, `račun_ID_račun`),
  INDEX `fk_jelo_has_račun_račun1_idx` (`račun_ID_račun` ASC),
  INDEX `fk_jelo_has_račun_jelo1_idx` (`jelo_ID_jelo` ASC),
  CONSTRAINT `fk_jelo_has_račun_jelo1`
    FOREIGN KEY (`jelo_ID_jelo`)
    REFERENCES `mydb`.`jelo` (`ID_jelo`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_jelo_has_račun_račun1`
    FOREIGN KEY (`račun_ID_račun`)
    REFERENCES `mydb`.`račun` (`ID_račun`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
COMMENT = '	';


-- -----------------------------------------------------
-- Table `mydb`.`pice`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`pice` (
  `ID_pice` INT UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
  `naziv_pice` VARCHAR(45) NULL,
  `cijena_pice` INT UNSIGNED NULL,
  `kolicina_pice` FLOAT NULL,
  PRIMARY KEY (`ID_pice`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`pića_na_računu`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`pića_na_računu` (
  `pice_ID_pice` INT UNSIGNED ZEROFILL NOT NULL,
  `račun_ID_račun` INT ZEROFILL UNSIGNED NOT NULL,
  PRIMARY KEY (`pice_ID_pice`, `račun_ID_račun`),
  INDEX `fk_pice_has_račun_račun1_idx` (`račun_ID_račun` ASC),
  INDEX `fk_pice_has_račun_pice1_idx` (`pice_ID_pice` ASC),
  CONSTRAINT `fk_pice_has_račun_pice1`
    FOREIGN KEY (`pice_ID_pice`)
    REFERENCES `mydb`.`pice` (`ID_pice`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_pice_has_račun_račun1`
    FOREIGN KEY (`račun_ID_račun`)
    REFERENCES `mydb`.`račun` (`ID_račun`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`kuhar_priprema_jelo`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`kuhar_priprema_jelo` (
  `kuhar_ID_kuhar` INT ZEROFILL UNSIGNED NOT NULL,
  `jelo_ID_jelo` INT UNSIGNED ZEROFILL NOT NULL,
  PRIMARY KEY (`kuhar_ID_kuhar`, `jelo_ID_jelo`),
  INDEX `fk_kuhar_has_jelo_jelo1_idx` (`jelo_ID_jelo` ASC),
  INDEX `fk_kuhar_has_jelo_kuhar1_idx` (`kuhar_ID_kuhar` ASC),
  CONSTRAINT `fk_kuhar_has_jelo_kuhar1`
    FOREIGN KEY (`kuhar_ID_kuhar`)
    REFERENCES `mydb`.`kuhar` (`ID_kuhar`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_kuhar_has_jelo_jelo1`
    FOREIGN KEY (`jelo_ID_jelo`)
    REFERENCES `mydb`.`jelo` (`ID_jelo`)
    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;