CREATE TABLE Kocka (
	rodneCislo INTEGER NOT NULL,
	jmeno varchar(20) NOT NULL,
	vzorekKuzi varchar(20),
	barvaSrsti varchar(20),
	mnozstviZivotu INTEGER NOT NULL,
	PRIMARY KEY (rodneCislo) ON DELETE CASCADE,
	FOREIGN KEY (kodVlastnosti) REFERENCES Rasa(kodVlastnosti) ON DELETE CASCADE
);

CREATE TABLE Zivot (
	idZivotu INTEGER NOT NULL,
	cisloZivotu INTEGER NOT NULL,
	datumNarozeni DATE NOT NULL,
	mistoNarozeni INTEGER NOT NULL FOREIGN KEY REFERENCES Teritoria(idTeritorii),
	PRIMARY KEY (idZivotu) ON DELETE CASCADE
);

CREATE TABLE AktualniZivot (
	idZivotu INTEGER NOT NULL REFERENCES Zivot(idZivotu) ON DELETE CASCADE,
	FOREIGN KEY (rodneCislo) REFERENCES Kocka(rodneCislo) ON DELETE CASCADE,
);

CREATE TABLE MinulyZivot (
	idZivotu INTEGER NOT NULL REFERENCES Zivot(idZivotu) ON DELETE CASCADE,
	datumSmrti DATE NOT NULL,
	mistoSmrti INTEGER NOT NULL FOREIGN KEY REFERENCES Teritoria(idTeritorii),
	zpusobSmrti varchar(160),
	FOREIGN KEY (rodneCislo) REFERENCES Kocka(rodneCislo) ON DELETE CASCADE,
);

CREATE TABLE KockaZivot (
	rodneCislo INTEGER NOT NULL FOREIGN KEY REFERENCES Kocka(rodneCislo) ON DELETE CASCADE,
	idZivotu INTEGER NOT NULL FOREIGN KEY REFERENCES Zivot(idZivotu) ON DELECTE CASCASE,
	jeAktualni BOOLEAN NOT NULL
);

ALTER TABLE KockaZivot ADD CONSTRAINT PK_KockaZivot PRIMARY KEY (rodneCislo, idZivotu)

CREATE TABLE Teritoria (
	idTeritorii INTEGER NOT NULL,
	typTeritorii varchar(20) NOT NULL,
	kapacita INTEGER NOT NULL,
	PRIMARY KEY (idTeritorii) ON DELETE CASCADE
);

CREATE TABLE KockaTeritoria (
	rodneCislo INTEGER NOT NULL FOREIGN KEY REFERENCES Kocka(rodneCislo) ON DELETE CASCADE,
	idTeritorii INTEGER NOT NULL FOREIGN KEY REFERENCES Teritoria(idTeritorii) ON DELETE CASCADE,
	PeriodaOd DATE NOT NULL,
	PeriodaDo DATE

);

ALTER TABLE KockaTeritoria ADD CONSTRAINT PK_KockaTeritoria PRIMARY KEY (rodneCislo, idTeritorii)

CREATE TABLE Vec (
	idVec INTEGER NOT NULL,
	typVlastnictvi varchar(20) NOT NULL,
	kvantita INTEGER NOT NULL,
	PRIMARY KEY (idVec) ON DELETE CASCADE,
	FOREIGN KEY (idTeritorii) REFERENCES Teritoria(idTeritorii) ON DELETE CASCADE,
	FOREIGN KEY (idVec) REFERENCES Vec(idVec) ON DELETE CASCADE
);

CREATE TABLE KockaVec (
    rodneCislo INTEGER NOT NULL FOREIGN KEY REFERENCES Kocka(rodneCislo) ON DELETE CASCADE,
    idVec INTEGER NOT NULL FOREIGN KEY REFERENCES Vec(idVec) ON DELETE CASCADE,
    PeriodaOd DATE NOT NULL,
    PeriodaDo DATE
);

ALTER TABLE KockaVec ADD CONSTRAINT PK_KockaVec PRIMARY KEY (rodneCislo, idVec)

CREATE TABLE Hostitel (
	rodneCislo INTEGER NOT NULL,
	jmeno varchar(30) NOT NULL,
	vek INTEGER NOT NULL,
	pohlavi char(1) NOT NULL,
	mistoBydleni varchar(50) NOT NULL,
	PRIMARY KEY (rodneCislo) ON DELETE CASCADE,
	oblibenaRasa INTEGER NOT NULL FOREIGN KEY (kodVlastnosti) REFERENCES Rasa(kodVlastnosti)
);

CREATE TABLE KockaHostitel (
    rodneCislo INTEGER NOT NULL FOREIGN KEY REFERENCES Kocka(rodneCislo) ON DELETE CASCADE,
    rodneCisloHost INTEGER NOT NULL FOREIGN KEY REFERENCES Hostitel(rodneCislo) ON DELETE CASCADE,
    JmenoPodleHostitele varchar(20) NOT NULL
);

ALTER TABLE KockaHostitel ADD CONSTRAINT PK_KockaHostitel PRIMARY KEY (rodneCislo, rodneCisloHost)

CREATE TABLE Rasa (
	kodVlastnosti INTEGER NOT NULL
	barvaOci varchar(20) NOT NULL,
	puvod varchar(20) NOT NULL,
	maxDelkaTesaku REAL NOT NULL,
	tvarUcha varchar(20) NOT NULL,
	PRIMARY KEY (kodVlastnosti) ON DELETE CASCADE
);