-- vytvorime relace a naplnime daty

CREATE TABLE human 
	(name varchar(20) NOT NULL,
	 surname varchar(20) PRIMARY KEY,  -- primarni klic
	 city varchar(20) NOT NULL)
;



-- constrainty se daji pridavat i primo pri create table
CREATE TABLE animal
       (species varchar(20) NOT NULL,
       name varchar(20) NOT NULL,
       owner varchar(20) NOT NULL,
       FOREIGN KEY (owner) REFERENCES human (surname) ON UPDATE CASCADE ON DELETE CASCADE,  -- cizi klic
       UNIQUE (name, owner))  -- unikatni kombinace  atributu 
;

INSERT INTO human VALUES
       ('Pepa', 'Pako', 'Praha'),
       ('Tom', 'Mot', 'Brno'),
       ('Pepa',  'Novak', 'Brno'),
       ('Jana', 'Mala', 'Olomouc')
;

INSERT INTO animal VALUES
       ('vepr', 'Pepr', 'Pako'),
       ('vepr', 'Lojza', 'Mala'),
       ('pes', 'Balik', 'Mala'),
       ('ryba', 'Lekla',  'Mot'),
       ('vepr', 'Pecinka', 'Mala'),
       ('pes', 'Balik', 'Novak');

table human;

table animal;


 -- vytvareni pohledu
DROP VIEW IF EXISTS animal_name;

CREATE VIEW animal_name AS
SELECT DISTINCT name FROM animal;

table animal_name;

-- co je to pohled?


-- lze pres nej modifikovat?

UPDATE animal_name SET name = 'Alik' WHERE name = 'Balik';

--nekdy ne - DISTINCT

DROP VIEW IF EXISTS pig_owner;

CREATE VIEW pig_owner AS
SELECT human.surname AS owner_surname, animal.name AS animal_name FROM human, animal WHERE human.surname = animal.owner AND animal.species = 'vepr';

table pig_owner;

UPDATE pig_owner SET owner_surname = 'Velka' WHERE owner_surname = 'Mala';

-- nekdy ne -- moc relaci ve FROM

DROP VIEW IF EXISTS all_animal_name;

CREATE VIEW all_animal_name AS
SELECT name FROM animal;

table all_animal_name;   --POZOR DUPLICITY, zatim je nepripoustite; jen pro ukazku


UPDATE all_animal_name SET name = 'Alik' WHERE name = 'Balik';

--nekdy ano, presna pravidla v PostgreSQL jsou v dokumentaci

table all_animal_name;

table animal;


-- pohled nad pohledem?

DROP VIEW IF EXISTS human_name_surname;

CREATE VIEW human_name_surname AS
SELECT name, surname FROM human;


table human_name_surname;


DROP VIEW IF EXISTS human_name;

CREATE VIEW human_name AS
SELECT name FROM human_name_surname;


-- modifikovatlensot dat?

table human_name;

UPDATE human_name SET name = 'Tomas' WHERE name = 'Tom';

table human_name;

table human_name_surname;

table human;

--je-li pohled modifikovatelny, tak se zmena propaguje do dat


-- poheld s restirkci

DROP VIEW IF EXISTS animal_mala;

CREATE VIEW animal_mala AS
SELECT species, name, owner FROM animal WHERE owner = 'Mala';

table animal_mala;


 -- nedava smysl, pridavame do relace zvirat pani Male zvire nekoho jineho
 
INSERT INTO animal_mala VALUES
       ('pes', 'Joza', 'Mot');
-- ale ono to jde...

table animal_mala;

-- pridani proslo, ale v pohledu pres, ktery jsme pridali to neni - nedava smysl

table animal; -- pridani skutence proslo

DELETE FROM animal WHERE name = 'Joza'; -- opravime data

-- dodrzovani predikatu pohledu

DROP VIEW IF EXISTS animal_mala;

CREATE VIEW animal_mala AS
SELECT species, name, owner FROM animal WHERE owner = 'Mala' WITH CHECK OPTION;

-- WITH CHECK OPTION zamezi problemu vyse - tj s daty v pohledu lze manipulovat jen tak, aby stale platila podminka z vytvareni pohledu


table animal_mala;


INSERT INTO animal_mala VALUES -- uz nejde
       ('pes', 'Joza', 'Mot');



table animal;
table animal_mala;

INSERT INTO animal VALUES   --pohledy reaguji na zmeny v podkladove zakladni relaci
('ryba', 'Zlata', 'Mala');

table animal;
table animal_mala;



