DROP DATABASE IF EXISTS fakultet_novi;
CREATE DATABASE fakultet_novi;
DROP USER IF EXISTS 'fakultet_novi'@'localhost';
CREATE USER 'fakultet_novi'@'localhost' IDENTIFIED BY 'fakultet_novi';
GRANT ALL ON fakultet_novi.* TO 'fakultet_novi'@'localhost';
USE fakultet_novi;

DROP TABLE IF EXISTS `mjesto`;
CREATE TABLE IF NOT EXISTS `mjesto` (
  `id` int(11) NOT NULL,
  `pbr` int(11) NOT NULL,
  `nazMjesto` char(40) NOT NULL,
  `sifZupanija` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT ibfk_mjesto UNIQUE (`pbr`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `stud`;
CREATE TABLE IF NOT EXISTS `stud` (
  `id` int(11) NOT NULL,
  `mbrStud` int(11) NOT NULL,
  `imeStud` char(25) NOT NULL,
  `prezStud` char(25) NOT NULL,
  `pbrRod` int(11) DEFAULT NULL,
  `pbrStan` int(11) NOT NULL,
  `datRodStud` datetime DEFAULT NULL,
  `jmbgStud` char(13) DEFAULT NULL,
  FOREIGN KEY(`pbrRod`) REFERENCES `mjesto`(`id`),
  FOREIGN KEY(`pbrStan`) REFERENCES `mjesto`(`id`),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `ispit`;
CREATE TABLE IF NOT EXISTS `ispit` (
  `id` int(11) NOT NULL,
  `mbrStud` int(11) NOT NULL,
  `sifPred` int(11) NOT NULL,
  `sifNastavnik` int(11) NOT NULL,
  `datIspit` datetime NOT NULL,
  `ocjena` smallint(6) NOT NULL DEFAULT '1',
  FOREIGN KEY(`mbrStud`) REFERENCES `stud`(`id`),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;