-- 1) Vytvorenie databazy

DROP DATABASE IF EXISTS MOZ;
CREATE DATABASE IF NOT EXISTS MOZ;

USE MOZ;

-- 2a) Vytvorenie tabuliek

CREATE TABLE Miestnost
( 
  idM		 int  NOT NULL PRIMARY KEY, 
  Kapacita   int  NOT NULL CHECK(Kapacita > 0) 
);

CREATE TABLE Oddelenie
(  
  Nazov_odd	    varchar(40) PRIMARY KEY,
  Miestnost 	int
);

CREATE TABLE Zamestnanci
( 
  idZam		        CHAR(3) PRIMARY KEY,
  Meno	            varchar(20) NOT NULL,
  Prac_na_odd		varchar(40) NOT NULL
);

-- 2b) Pridanie vazieb medzi Oddelenim a Miestnostnou, respektive medzi Oddelenim a Zamestnancami
-- ALTER TABLE Oddelenie ADD CONSTRAINT fk_Miest FOREIGN KEY (Miestnost)
-- REFERENCES Miestnost(idM)ON DELETE CASCADE ON UPDATE CASCADE;
-- 
-- ALTER TABLE Zamestnanci	ADD CONSTRAINT fk_Pnodd FOREIGN KEY (Prac_na_odd) 
-- REFERENCES Oddelenie(Nazov_odd) ON DELETE CASCADE ON UPDATE CASCADE;

-- ALTER TABLE Oddelenie ADD CONSTRAINT fk_Miest FOREIGN KEY (Miestnost) REFERENCES Miestnost(idM);

-- ALTER TABLE Zamestnanci	ADD CONSTRAINT fk_Pnodd FOREIGN KEY (Prac_na_odd) REFERENCES Oddelenie(Nazov_odd);

-- 2c) Naplnanie tabuliek udajmi
INSERT Miestnost VALUES (1, 5);
INSERT Miestnost VALUES (2, 20);
INSERT Miestnost VALUES (3, 11);
INSERT Miestnost VALUES (4, 6);
INSERT Miestnost VALUES (5, 6);
INSERT Miestnost VALUES (6, 10);
INSERT Miestnost VALUES (7, 3);
INSERT Miestnost VALUES (8, 4);
INSERT Miestnost VALUES (9, 11);

Insert Oddelenie values ('A1', 1);
Insert Oddelenie values ('A2', 2);
Insert Oddelenie values ('A3', 3);
Insert Oddelenie values ('B1', 4);
Insert Oddelenie values ('B2', 7);
Insert Oddelenie values ('B3', 8);
Insert Oddelenie values ('C1', 5);
Insert Oddelenie values ('C2', 6);


INSERT Zamestnanci(idZam, Meno, Prac_na_odd) VALUES( 'X', 'x', 'A2'); 
INSERT Zamestnanci(idZam, Meno, Prac_na_odd) VALUES( 'Y', 'y', 'B1');
INSERT Zamestnanci(idZam, Meno, Prac_na_odd) VALUES( 'W', 'w', 'C2');
INSERT Zamestnanci(idZam, Meno, Prac_na_odd) VALUES( 'Z', 'z', 'B3');

USE moz;

-- select

SELECT * FROM Miestnost;
SELECT * FROM Oddelenie;
SELECT * FROM Zamestnanci;

-- 3)

-- kontrola na kapacitu miestnosti
INSERT Miestnost VALUES (11, -5);

SELECT * FROM Miestnost;

-- update/delete bez cudzieho kluca

UPDATE Miestnost SET idM = 22 WHERE idM = 2;

UPDATE Miestnost SET idM = 2 WHERE idM = 22;

DELETE FROM Miestnost WHERE idM = 2;

INSERT Miestnost VALUES (2, 20);

-- Pridanie vazieb (cudzich klucov) medzi Oddelenim a Miestnostnou, respektive medzi Oddelenim a Zamestnancami

ALTER TABLE Oddelenie ADD CONSTRAINT fk_Miest FOREIGN KEY (Miestnost) REFERENCES Miestnost(idM);

ALTER TABLE Zamestnanci	ADD CONSTRAINT fk_Pnodd FOREIGN KEY (Prac_na_odd) REFERENCES Oddelenie(Nazov_odd);

-- delete

DELETE FROM Miestnost WHERE idM = 2;

DELETE FROM Oddelenie WHERE Nazov_odd = 'A2';

DELETE FROM Zamestnanci WHERE idZam= 'X';

-- select

SELECT * FROM Miestnost;
SELECT * FROM Oddelenie;
SELECT * FROM Zamestnanci;

-- insert
INSERT Miestnost VALUES (2, 20);
INSERT Oddelenie values ('A2', 2);
INSERT Zamestnanci(idZam, Meno, Prac_na_odd) VALUES( 'X', 'x', 'A2');

-- select

SELECT * FROM Miestnost;
SELECT * FROM Oddelenie;
SELECT * FROM Zamestnanci;

-- ON DELETE CASCADE ON UPDATE CASCADE

ALTER TABLE Oddelenie DROP FOREIGN KEY fk_Miest;
ALTER TABLE Zamestnanci DROP FOREIGN KEY fk_Pnodd;
    
ALTER TABLE Oddelenie ADD CONSTRAINT fk_Miest FOREIGN KEY (Miestnost) REFERENCES Miestnost(idM) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE Zamestnanci	ADD CONSTRAINT fk_Pnodd FOREIGN KEY (Prac_na_odd) REFERENCES Oddelenie(Nazov_odd) ON DELETE CASCADE ON UPDATE CASCADE;

-- select

SELECT * FROM Miestnost;
SELECT * FROM Oddelenie;
SELECT * FROM Zamestnanci;

-- delete

DELETE FROM Miestnost WHERE idM = 2;

DELETE FROM Oddelenie WHERE Nazov_odd = 'A2';

DELETE FROM Zamestnanci WHERE idZam= 'X';

-- select

SELECT * FROM Miestnost;
SELECT * FROM Oddelenie;
SELECT * FROM Zamestnanci;

-- insert

INSERT Miestnost VALUES (2, 20);
INSERT Oddelenie values ('A2', 2);
INSERT Zamestnanci(idZam, Meno, Prac_na_odd) VALUES( 'X', 'x', 'A2');

-- select

SELECT * FROM Miestnost;
SELECT * FROM Oddelenie;
SELECT * FROM Zamestnanci;

-- update

UPDATE Miestnost SET idM = 22 WHERE idM = 2;
UPDATE Oddelenie SET Nazov_odd = 'A22' WHERE Nazov_odd = 'A2';
UPDATE Zamestnanci SET idZam = 'X22' WHERE idZam = 'X';

-- select

SELECT * FROM Miestnost;
SELECT * FROM Oddelenie;
SELECT * FROM Zamestnanci;

-- delete

DELETE FROM Miestnost WHERE idM = 22;
DELETE FROM Oddelenie WHERE Nazov_odd = 'A22';
DELETE FROM Zamestnanci WHERE idZam = 'X22';

-- select

SELECT * FROM Miestnost;
SELECT * FROM Oddelenie;
SELECT * FROM Zamestnanci;

-- insert

INSERT Miestnost VALUES (2, 20);
INSERT Oddelenie values ('A2', 2);
INSERT Zamestnanci(idZam, Meno, Prac_na_odd) VALUES( 'X', 'x', 'A2');

-- ON DELETE SET NULL ON UPDATE CASCADE
-- ON DELETE NO ACTION ON UPDATE CASCADE

ALTER TABLE Oddelenie DROP FOREIGN KEY fk_Miest;
ALTER TABLE Zamestnanci DROP FOREIGN KEY fk_Pnodd;

ALTER TABLE Oddelenie 
ADD CONSTRAINT fk_Miest 
FOREIGN KEY (Miestnost) 
REFERENCES Miestnost(idM) 
ON DELETE SET NULL 
ON UPDATE CASCADE;
-- RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

ALTER TABLE Zamestnanci	
ADD CONSTRAINT fk_PNaOdd FOREIGN KEY (Prac_na_odd) 
REFERENCES Oddelenie(Nazov_odd) 
ON DELETE NO ACTION
ON UPDATE CASCADE;
-- RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

-- select

SELECT * FROM Miestnost;
SELECT * FROM Oddelenie;
SELECT * FROM Zamestnanci;

-- update

UPDATE Miestnost SET idM = 22 WHERE idM = 2;
UPDATE Oddelenie SET Nazov_odd = 'A22' WHERE Nazov_odd = 'A2';
UPDATE Zamestnanci SET idZam = 'X22' WHERE idZam = 'X';

-- select

SELECT * FROM Miestnost;
SELECT * FROM Oddelenie;
SELECT * FROM Zamestnanci;

-- delete

DELETE FROM Miestnost WHERE idM = 22;
DELETE FROM Oddelenie WHERE Nazov_odd = 'A22';
DELETE FROM Zamestnanci WHERE idZam = 'X22';

-- select

SELECT * FROM Miestnost;
SELECT * FROM Oddelenie;
SELECT * FROM Zamestnanci;

-- drop

DROP TABLE Miestnost;
DROP TABLE Oddelenie;
DROP TABLE Zamestnanci;


SELECT * FROM Miestnost;
SELECT * FROM Oddelenie;
SELECT * FROM Zamestnanci;