CREATE DATABASE skola DEFAULT CHARACTER SET utf8 COLLATE utf8_czech_ci;

USE skola;

CREATE TABLE Pacienti (
	id_pacienta int (10) auto_increment,
	Jmeno VARCHAR (60),
  Prijmeni VARCHAR (60),
  Adresa VARCHAR (60),
  Telefon int (13),
  doktor int (10),
  pojistovna int (10),
  nemoc int (10),
  PRIMARY KEY (id_pacienta),
  FOREIGN KEY (doktor) REFERENCES Doktori (id_doktor) ON DELETE CASCADE,
  FOREIGN KEY (pojistovna) REFERENCES Pojistovna (id_pojistovna) ON DELETE CASCADE,
  FOREIGN KEY (nemoc) REFERENCES Nemoci (id_nemoci) ON DELETE SET NULL
)
ENGINE=innoDB;

CREATE TABLE Doktori (
	id_doktor int (10) auto_increment,
	cislo_d_oddeleni int (10),
	jmeno VARCHAR (60),
	prijmeni VARCHAR (60),
	Adresa VARCHAR (60),
	rok_nast date,
	PRIMARY KEY (id_doktor),
	FOREIGN KEY (cislo_d_oddeleni) REFERENCES Oddeleni (cislo) ON DELETE CASCADE
)
ENGINE=innoDB;

CREATE TABLE Oddeleni (
	cislo int (10) auto_increment,
	o_doktor int (10),
	Nazev VARCHAR (60),
	PRIMARY KEY (cislo),
	FOREIGN KEY (o_doktor) REFERENCES Doktori (id_doktor) ON DELETE CASCADE
)
ENGINE=innoDB;

CREATE TABLE Pojistovna (
	id_pojistovna int (10),
	Nazev VARCHAR (60),
	Telefon int (13),
	Adresa VARCHAR(60),
	PRIMARY KEY (id_pojistovna)
)
ENGINE=innoDB;

CREATE TABLE Nemoci (
	id_nemoci int (10),
	Nazev VARCHAR (60),
	Priznaky VARCHAR (60),
  Lecba VARCHAR (60),
	PRIMARY KEY (id_nemoci)
)
ENGINE=innoDB;



CREATE TABLE Pacientnemoc (
	p_pacient int (10),
  p_nemoc int (10),
FOREIGN KEY (p_pacient) REFERENCES Pacienti (id_pacienta) ON DELETE CASCADE,
FOREIGN KEY (p_nemoc) REFERENCES Nemoci (id_nemoci) ON DELETE CASCADE
)
ENGINE=innoDB;