DROP TABLE IF EXISTS Nakup, Dodavatel, Zbozi;

CREATE TABLE Dodavatel (
  idDodavatele INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  nazev VARCHAR(255) NOT NULL,
  adresa VARCHAR(255) NOT NULL,
  obec VARCHAR(255) NOT NULL,
  psc VARCHAR(6) NOT NULL,
  telefon VARCHAR(10) NOT NULL,
  PRIMARY KEY(idDodavatele)
);

CREATE TABLE Zbozi (
  kod INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  idDodavatele INTEGER UNSIGNED NOT NULL,
  druh VARCHAR(255) NULL,
  typ VARCHAR(255) NULL,
  ksNaSklade INTEGER UNSIGNED NULL,
  cenaProdej DECIMAL NULL,
  trvanlivost INTEGER UNSIGNED NULL,
  PRIMARY KEY(kod),
  INDEX Zbozi_FKIndex1(idDodavatele),
  FOREIGN KEY(idDodavatele)
    REFERENCES Dodavatel(idDodavatele)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION
);

CREATE TABLE Nakup (
  idNakupu INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  kod INTEGER UNSIGNED NOT NULL,
  pocetKs INTEGER UNSIGNED NOT NULL,
  datumVyroby DATE NOT NULL,
  datumNakupu DATE NOT NULL,
  cenaNakup DECIMAL NULL,
  PRIMARY KEY(idNakupu),
  INDEX Nakup_FKIndex1(kod),
  FOREIGN KEY(kod)
    REFERENCES Zbozi(kod)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION
);