CREATE TABLE DonatorPsa (
  OIB CHAR(11) PRIMARY KEY,
  Ime VARCHAR(15),
  Prezime VARCHAR(15)
);

CREATE TABLE Kućica (
  IDKucice CHAR(5) PRIMARY KEY,
  Visina DECIMAL(3,1),
  Duljina DECIMAL(3,1),
  Širina DECIMAL(3,1),
  Zauzetost BIT
);

CREATE TABLE Volonter (
  IDVolontera CHAR(11) PRIMARY KEY,
  Ime VARCHAR(15),
  Prezime VARCHAR(15),
  DatPočetka DATETIME,
  DatKraj DATETIME
);

CREATE TABLE Djelatnik (
  OIB CHAR(11) PRIMARY KEY,
  Ime VARCHAR(15),
  Prezime VARCHAR(15),
  Telefon VARCHAR(15),
  Plaća DECIMAL(6,2)
  CONSTRAINT provjeri_placu CHECK (Plaća >= 0)
);

CREATE TABLE Cjepivo (
  Šifra CHAR(8) PRIMARY KEY,
  Naziv VARCHAR(20)
);

CREATE TABLE Udomitelj (
  OIB CHAR(11) PRIMARY KEY,
  BrojDjece INT,
  Email VARCHAR(50),
  PBR CHAR(5),
  Grad VARCHAR(15),
  Adresa VARCHAR(50),
  Ime VARCHAR(15),
  Prezime VARCHAR(15)
);


CREATE TABLE Pas (
  IDPsa CHAR(5)PRIMARY KEY,
  ImePsa VARCHAR(15),
  Kastriran BIT,
  Čip BIT,
  Spol CHAR(1) DEFAULT 'M',
  Dob INT,
  BojaDlake VARCHAR(20),
  Dlaka VARCHAR(10),
  Pasmina VARCHAR(20),
  Udomljen BIT,
  IDKućice CHAR(5),
  OIBDjelatnika CHAR(11),
  OIBUdomitelja CHAR(11),
  OIBDonatora CHAR(11),
  CONSTRAINT fkKucica FOREIGN KEY(IDKućice) REFERENCES kućica(IDKucice),
  CONSTRAINT fkDjelatnik FOREIGN KEY(OIBDjelatnika) REFERENCES djelatnik(OIB),
  CONSTRAINT fkUdomitelj FOREIGN KEY(OIBUdomitelja) REFERENCES udomitelj(OIB),
  CONSTRAINT fkDonator FOREIGN KEY(OIBDonatora) REFERENCES donatorpsa(OIB)
  CONSTRAINT provjeri_spol CHECK (Spol IN ('M','F'))
);

CREATE TABLE BrineO (
  IDVolontera CHAR(11),
  IDPsa CHAR(5),
  CONSTRAINT pk PRIMARY KEY(IDVolontera, IDPsa),
  CONSTRAINT fkbidpsa FOREIGN KEY(IDPsa) REFERENCES pas(IDPsa),
  CONSTRAINT fkbidvol FOREIGN KEY(IDVolontera) REFERENCES volonter(IDVolontera)
);

CREATE TABLE Imunizacija(
  IDPsa CHAR(5),
  Šifra CHAR(8),
  Datum DATETIME,
  CONSTRAINT pk PRIMARY KEY(IDPsa, Šifra),
  CONSTRAINT fkimidpsa FOREIGN KEY(IDPsa) REFERENCES pas(IDPsa),
  CONSTRAINT fkimsifra FOREIGN KEY(Šifra) REFERENCES cjepivo(Šifra)
);