CREATE DATABASE DBSDU;
USE DBSDU;

DROP TABLE IF EXISTS Person, Tourist, Provider, CoTraveler, Adress, Accomodation, Service, Cantoned, Reside, Institution, Headoffice, CategoryInstitution, EmailContact, FaxContact, PhoneContact, Event;

/*VYTVORENIE TABULIEK*/

CREATE TABLE Person (
  login    VARCHAR(20) PRIMARY KEY,
  password VARCHAR(12) NOT NULL
);

CREATE TABLE Tourist (
  login VARCHAR(20) REFERENCES Person (login),
  PRIMARY KEY(login)
);

CREATE TABLE Provider (
  login  VARCHAR(20) REFERENCES Person (login),
  rating FLOAT,
  PRIMARY KEY(login)
);

CREATE TABLE Telephone (
  login VARCHAR(20),
  phone VARCHAR(15),
  PRIMARY KEY (login, phone),
 CONSTRAINT fk_phone_owner FOREIGN KEY(login) REFERENCES Provider (login) ON DELETE CASCADE
);

CREATE TABLE CoTraveler (
  login1 VARCHAR(20) REFERENCES Tourist (login),
  login2 VARCHAR(20) UNIQUE NOT NULL REFERENCES Tourist (login),
  PRIMARY KEY(login1)
);

CREATE TABLE Adress (
  street      VARCHAR(20),
  housenumber VARCHAR(10),
  postcode    VARCHAR(5),
  PRIMARY KEY (street,housenumber,postcode)
);

CREATE TABLE Accomodation (
  name  VARCHAR(255) NOT NULL,
  ID INT UNIQUE NOT NULL,
  capacity    INT        NOT NULL,
  rating      INT        NOT NULL,
  street      VARCHAR(255) REFERENCES Adress (street),
  housenumber VARCHAR(255) REFERENCES Adress (housenumber),
  postcode    VARCHAR(255) REFERENCES Adress (postcode),
  PRIMARY KEY (street, housenumber,postcode)
);

CREATE TABLE Service (
  ID    INT REFERENCES Accomodation (ID),
  PROVIDER_NAME VARCHAR(255) REFERENCES Provider (login),
  PRIMARY KEY (ID, PROVIDER_NAME)
);

CREATE TABLE Cantoned (
  ID    INT REFERENCES Accomodation (ID),
  login VARCHAR(20) REFERENCES Tourist (login),
  PRIMARY KEY (ID, login)
);

CREATE TABLE Reside (
  PROVIDER_NAME      VARCHAR(20) REFERENCES Provider (login),
  street      VARCHAR(20) REFERENCES Adress (street),
  housenumber VARCHAR(10) REFERENCES Adress (housenumber),
  postcode    VARCHAR(5) REFERENCES Adress (postcode),
  PRIMARY KEY (PROVIDER_NAME, street, housenumber, postcode)
);

CREATE TABLE Institution (
  tradename VARCHAR(20) PRIMARY KEY,
  rating    FLOAT NOT NULL
);

CREATE TABLE Headoffice (
  tradename   VARCHAR(20) REFERENCES Institution (tradename),
  street      VARCHAR(20) REFERENCES Adress (street),
  housenumber VARCHAR(10) REFERENCES Adress (housenumber),
  postcode    VARCHAR(5) REFERENCES Adress (postcode),
  PRIMARY KEY (tradename, street,housenumber,postcode),
  CONSTRAINT fk_address_owner FOREIGN KEY(tradename) REFERENCES Institution (tradename)  ON DELETE CASCADE
);

CREATE TABLE CategoryInstitution (
  tradename VARCHAR(20) REFERENCES Institution (tradename),
  category  VARCHAR(15),
  PRIMARY KEY (tradename, category)
);

CREATE TABLE EmailContact (
  tradename VARCHAR(20),
  email     VARCHAR(20),
  CHECK (email LIKE '%@%'),
  PRIMARY KEY (tradename, email),
  CONSTRAINT fk_mail_owner FOREIGN KEY(tradename)  REFERENCES Institution (tradename) ON DELETE CASCADE
);

CREATE TABLE FaxContact (
  tradename VARCHAR(20) REFERENCES Institution (tradename),
  fax       INT,
  PRIMARY KEY (tradename, fax),
  CONSTRAINT fk_fax_owner FOREIGN KEY(tradename)  REFERENCES Institution (tradename) ON DELETE CASCADE
);

CREATE TABLE PhoneContact (
  tradename VARCHAR(20) REFERENCES Institution (tradename),
  phone     VARCHAR(15),
  PRIMARY KEY (phone, tradename)
);


CREATE TABLE Event (
  tradename VARCHAR(20) REFERENCES Institution (tradename),
  hour TIME,
  day       DATE,
  login     VARCHAR(20) REFERENCES Tourist (login),
  PRIMARY KEY (tradename,login)
);

/*NAPLNENIE TABULIEK*/

INSERT INTO Person VALUES ('peter.novak', 'uvhj1488');
INSERT INTO Person VALUES ('jozef.turek', '1bjbk5');
INSERT INTO Person VALUES ('karolina.hrava', 'hurtadd');
INSERT INTO Person VALUES ('roman.prvy', 'kugfujk');
INSERT INTO Person VALUES ('ondrej.kral', 'hju578');
INSERT INTO Person VALUES ('ondrej.dubek', 'hpl578');
INSERT INTO Person VALUES ('rodina.malych', 'jkn47bk');
INSERT INTO Person VALUES ('hotel.Adria', 'kbgkj458');
INSERT INTO Person VALUES ('penzion.Uno', 'jjll242');
INSERT INTO Person VALUES ('privat.More', 'hjvj48');
INSERT INTO Person VALUES ('hostel.Kuna', 'bjbj486');

INSERT INTO Tourist VALUES ('peter.novak');
INSERT INTO Tourist VALUES ('jozef.turek');
INSERT INTO Tourist VALUES ('karolina.hrava');
INSERT INTO Tourist VALUES ('roman.prvy');
INSERT INTO Tourist VALUES ('ondrej.kral');
INSERT INTO Tourist VALUES ('ondrej.dubek');

INSERT INTO Provider VALUES ('rodina.malych', 1.0);
INSERT INTO Provider VALUES ('hotel.Adria', 5.0);
INSERT INTO Provider VALUES ('penzion.Uno', 1.5);
INSERT INTO Provider VALUES ('privat.More', 2.4);
INSERT INTO Provider VALUES ('hostel.Kuna', 3.2);

INSERT INTO Telephone VALUES ('rodina.malych', '0944023456');
INSERT INTO Telephone VALUES ('hotel.Adria', '0903452874');
INSERT INTO Telephone VALUES ('penzion.Uno', '098845678');
INSERT INTO Telephone VALUES ('privat.More', '0945363987');
INSERT INTO Telephone VALUES ('hostel.Kuna', '090536397');

INSERT INTO CoTraveler VALUES ('peter.novak', 'jozef.turek');
INSERT INTO CoTraveler VALUES ('jozef.turek', 'karolina.hrava');
INSERT INTO CoTraveler VALUES ('karolina.hrava', 'peter.novak');
INSERT INTO CoTraveler VALUES ('roman.prvy', 'ondrej.kral');
INSERT INTO CoTraveler VALUES ('ondrej.kral', 'jan.druhy');

INSERT INTO Adress VALUES ('Okruzna', '222/2', '02001');
INSERT INTO Adress VALUES ('Budvarska', '145', '02001');
INSERT INTO Adress VALUES ('Kratka', '14', '02597');
INSERT INTO Adress VALUES ('Dlha', '258', '02145');
INSERT INTO Adress VALUES ('Mierova', '3', '01258');
INSERT INTO Adress VALUES ('Lichardova', '24/2', '02002');
INSERT INTO Adress VALUES ('Plzenska', '140', '02489');
INSERT INTO Adress VALUES ('Kratka', '24', '02597');
INSERT INTO Adress VALUES ('Dlha', '28', '02145');
INSERT INTO Adress VALUES ('Mierova', '36', '01258');

INSERT INTO Accomodation VALUES ('Privat u rodiny Malych',1,5, 4.0, 'Okruzna','222/2','02001');
INSERT INTO Accomodation VALUES ('Hotel Adria',2,100, 1.0, 'Budvarska','145','02001');
INSERT INTO Accomodation VALUES ('Penzion Uno',3,25, 4.1, 'Kratka','14','02597');
INSERT INTO Accomodation VALUES ('Privat More',4,45, 1.5, 'Dlha','258','02145');
INSERT INTO Accomodation VALUES ('Hostel Kuna',5 ,250, 3.1, 'Mierova', '3','01258');

INSERT INTO Reside VALUES ('rodina.malych', 'Okruzna', '222/2', '02001');
INSERT INTO Reside VALUES ('hotel.Adria', 'Budvarska', '145', '02001');
INSERT INTO Reside VALUES ('penzion.Uno', 'Kratka', '14', '02597');
INSERT INTO Reside VALUES ('privat.More', 'Dlha', '258', '02145');
INSERT INTO Reside VALUES ('hostel.Kuna', 'Mierova', '3', '01258');

INSERT INTO Service VALUES (1, 'rodina.malych');
INSERT INTO Service VALUES (2, 'hotel.Adria');
INSERT INTO Service VALUES (3, 'penzion.Uno');
INSERT INTO Service VALUES (4, 'privat.More');
INSERT INTO Service VALUES (5, 'hostel.Kuna');

INSERT INTO Cantoned VALUES (1, 'peter.novak');
INSERT INTO Cantoned VALUES (2, 'jozef.turek');
INSERT INTO Cantoned VALUES (3, 'karolina.hrava');
INSERT INTO Cantoned VALUES (4, 'roman.prvy');
INSERT INTO Cantoned VALUES (5, 'ondrej.kral');
INSERT INTO Cantoned VALUES (5, 'ondrej.dubek');

INSERT INTO Institution VALUES ('kulturFabrik', 1.0);
INSERT INTO Institution VALUES ('TabackaCooltura', 4.0);
INSERT INTO Institution VALUES ('BK Zupny dom', 1.5);
INSERT INTO Institution VALUES ('Stanica', 5.0);
INSERT INTO Institution VALUES ('Klub Luc', 1.0);

INSERT INTO Headoffice VALUES ('kulturFabrik', 'Lichardova', '24/2', '02002');
INSERT INTO Headoffice VALUES ('TabackaCooltura', 'Plzenska', '140', '02001');
INSERT INTO Headoffice VALUES ('BK Zupny dom', 'Kratka', '24', '02001');
INSERT INTO Headoffice VALUES ('Stanica', 'Dlha', '28', '02147');
INSERT INTO Headoffice VALUES ('Klub Luc', 'Mierova', '36', '01258');

INSERT INTO CategoryInstitution VALUES ('kulturFabrik', 'divadlo');
INSERT INTO CategoryInstitution VALUES ('TabackaCooltura', 'umenie');
INSERT INTO CategoryInstitution VALUES ('BK Zupny dom', 'hudba');
INSERT INTO CategoryInstitution VALUES ('Stanica', 'umenie');
INSERT INTO CategoryInstitution VALUES ('Klub Luc', 'zabava');

INSERT INTO EmailContact VALUES ('kulturFabrik', 'divadlo@kulturFabrik.sk');
INSERT INTO EmailContact VALUES ('TabackaCooltura', 'umenie@tabacka.sk');
INSERT INTO EmailContact VALUES ('BK Zupny dom', 'hudba@zupnydom.sk');
INSERT INTO EmailContact VALUES ('Stanica', 'sport@stanica.sk');
INSERT INTO EmailContact VALUES ('Klub Luc', 'zabava@klubluc.sk');

INSERT INTO FaxContact VALUES ('kulturFabrik', 467854678);
INSERT INTO FaxContact VALUES ('TabackaCooltura', 467854245);
INSERT INTO FaxContact VALUES ('BK Zupny dom', 467854457);
INSERT INTO FaxContact VALUES ('Stanica', 467125678);
INSERT INTO FaxContact VALUES ('Klub Luc', 467854678);

INSERT INTO PhoneContact VALUES ('kulturFabrik', '0945363987');
INSERT INTO PhoneContact VALUES ('TabackaCooltura', '0944563987');
INSERT INTO PhoneContact VALUES ('BK Zupny dom', '0905313987');
INSERT INTO PhoneContact VALUES ('Stanica', '0940363987');
INSERT INTO PhoneContact VALUES ('Klub Luc', '0945373987');

INSERT INTO Event VALUES ('kulturFabrik', '12:20:00','2011-11-02', 'peter.novak');
INSERT INTO Event VALUES ('TabackaCooltura', '10:00:00', '2011-11-26', 'jozef.turek');
INSERT INTO Event VALUES ('BK Zupny dom', '18:20:00', '2011-11-27', 'karolina.hrava');
INSERT INTO Event VALUES ('Stanica', '20:30:00', '2011-11-29', 'roman.prvy');
INSERT INTO Event VALUES ('Klub Luc', '08:30:00', '2011-11-30', 'ondrej.kral');




/*1.) vypise vsetky institucie v dvoch susednych mestach*/
SELECT tradename FROM headoffice WHERE(postcode =  '02001' )
UNION
SELECT tradename FROM headoffice WHERE(postcode = '02001')

/*2.) vypise mena turistov, ktory sa nenavstivia ziadnu zaujmovu instituciu*/
SELECT tourist.login FROM tourist LEFT OUTER JOIN event ON(tourist.login = event.login)
WHERE event.login IS NULL

/*3.) zoznam turistov, ktory v roku 2011 sa zaujimali o umenie*/
SELECT login FROM Event JOIN CategoryInstitution ON(Event.tradename=CategoryInstitution.tradename)
WHERE (category=  'umenie' AND (day>'2011-01-01' AND day<'2011-12-31'))
ORDER BY login

/*4.)zobrazi institucie s vacsim ako priemernym ratingom*/
SELECT * FROM institution
WHERE (rating >(SELECT AVG(rating) FROM institution))

/*5.) zobrazi ubytovacie zariadenia s poctami ubytovanych a kapacitou, kde je este volne miesto)*/
SELECT accomodation.name, accomodation.capacity, COUNT(cantoned.login) AS tourists
FROM accomodation NATURAL JOIN cantoned
GROUP BY accomodation.name HAVING(tourists < capacity)

/*pohlad na zobrazenie kompletnych informacii o ubytovacich zariadeniach*/
CREATE VIEW accomodation_information (name,cantoned_tourists ,capacity, street, housenumber, postcode) AS
SELECT accomodation.name,COUNT(cantoned.login) AS tourists, accomodation.capacity, street, housenumber,postcode
FROM accomodation NATURAL JOIN cantoned
GROUP BY accomodation.name

/*pohlad na zobrazenie tabulky s instituciami, ktore v danom tyzdni organizuju nejake podujatie*/
CREATE VIEW all_event_at_week AS
SELECT tradename, category, street, housenumber, postcode, day
FROM event NATURAL JOIN  institution NATURAL JOIN categoryinstitution NATURAL JOIN headoffice
GROUP BY tradename HAVING (day > '2011-11-01' AND day<'2011-11-07' )
ORDER BY category