script.sql

-- SQL script for creating basic schema objects
-- Theme:   Pozicovna kostymov
-- Authors: Tomas Homola
--          Daniel Zalezak
-- Subject: IDS
-- Date:    02.05.2022

-- drop tables --
drop table "ORDER_ACCESSORY";
drop table "ORDER_COSTUME";
drop table "ACCESSORY_CATEGORY";
drop table "COSTUME_CATEGORY";
drop table "FINE";
drop table "ORDER";
drop table "ACCESSORY";
drop table "COSTUME";
drop table "CATEGORY";
drop table "EMPLOYEE";
drop table "CLIENT";
drop table "USER";
drop sequence "user_id";
drop sequence "order_id";

--create tables --
create table "USER"
(
    "ID"            number not null primary key,
    "FIRST_NAME"    varchar(255) not null,
    "SURNAME"       varchar(255) not null,
    "ADDRESS"       varchar(255) not null,
    "EMAIL"         varchar(255) not null,
    "PHONE_CONTACT" varchar(13)  not null,
    check(regexp_like("EMAIL", '^[a-z]+[a-z0-9\.]*@[a-z0-9\.-]+\.[a-z]{2,}$', 'i'))
);

create table "CLIENT"
(
    "USER_ID"         int         not null primary key,
    "BANK_ACC_NUMBER" varchar(250) not null,

    constraint "CLIENT_USER_ID_FOREIGN"
        foreign key ("USER_ID")
            references "USER" ("ID")
            on delete set null,

    check (regexp_like("BANK_ACC_NUMBER",'[A-Z]{2}[0-9]{22}$'))
);

create table "EMPLOYEE"
(
    "USER_ID"    int     not null primary key,
    "START_DATE" date    not null,
    "SALARY"     int     not null,
    "ID_NUMBER" char(8) not null,

    constraint "EMPLOYEE_USER_ID_FOREIGN"
        foreign key ("USER_ID")
            references "USER" ("ID")
            on delete set null,

    check(REGEXP_LIKE("ID_NUMBER", '[a-zA-Z]{2}[0-9]{6}'))
);

create table "CATEGORY"
(
    "ID"  int generated always as identity (start with 1 increment by 1) primary key,
    "NAME"         varchar(32) not null,
    "EVENT_TYPE"   varchar(32) not null,
    "AGE_CATEGORY" varchar(32) not null
);

create table "COSTUME"
(
    "ID"         int generated always as identity (start with 1 increment by 1) primary key,
    "PRODUCER"           varchar(32)  not null,
    "MATERIAL"           varchar(32)  not null,
    "SIZE"               varchar(4)   not null,
    "DESCRIPTION"        varchar(100) not null,
    "DATE_OF_PRODUCTION" date         not null,

    check ("SIZE" in ('XXS', 'XS', 'S', 'M', 'L', 'XL', 'XXL', 'XXXL'))
);

create table "ACCESSORY"
(
    "ID" int generated always as identity (start with 1 increment by 1) primary key,
    "NAME"         varchar(32) not null,
    "USAGE"        varchar(32) not null,
    "WEAR"         varchar(20) not null,

    check ("WEAR" in ('nove', 'mierne opotrebenie', 'viditelne poskodenie'))
);

create table "ORDER"
(
    "ID"         number not null primary key,
    "DATE"             date not null,
    "LENGTH_OF_RENTAL" varchar(32) not null,
    "CLIENT_ID" int not null,
    "EMPLOYEE_ID" int not null,

    constraint "ORDER_EMPLOYEE_ID_FK"
        foreign key ("EMPLOYEE_ID") references "EMPLOYEE" ("USER_ID")
        on delete cascade,
    constraint "ORDER_CLIENT_ID_FK"
        foreign key ("CLIENT_ID") references "CLIENT" ("USER_ID")
        on delete cascade
);

create table "FINE"
(
    "ID"       int generated always as identity (start with 1 increment by 1) primary key,
    "DATE_OF_ISSUE" date not null,
    "AMOUNT"        int not null,
    "DUE_DATE"      date not null,
    "CLIENT_ID" int not null,
    "EMPLOYEE_ID" int not null,

    constraint "FINE_EMPLOYEE_ID_FK"
        foreign key ("EMPLOYEE_ID") references "EMPLOYEE" ("USER_ID")
        on delete cascade,
    constraint "FINE_CLIENT_ID_FK"
        foreign key ("CLIENT_ID") references "CLIENT" ("USER_ID")
        on delete cascade
);

create table "COSTUME_CATEGORY"
(
    "COSTUME_ID" int not null,
    "CATEGORY_ID" int not null,
    constraint "COSTUME_CATEGORY_PK"
        primary key ("COSTUME_ID", "CATEGORY_ID"),
    constraint "COSTUME_CATEGORY_COSTUME_ID_FK"
        foreign key ("COSTUME_ID") references "COSTUME" ("ID")
        on delete cascade,
    constraint "COSTUME_CATEGORY_CATEGORY_ID_FK"
        foreign key ("CATEGORY_ID") references "CATEGORY" ("ID")
        on delete cascade
);

create table "ACCESSORY_CATEGORY"
(
    "ACCESSORY_ID" int not null,
    "CATEGORY_ID" int not null,
    constraint "ACCESSORY_CATEGORY_PK"
        primary key ("ACCESSORY_ID", "CATEGORY_ID"),
    constraint "ACCESSORY_CATEGORY_ACCESSORY_ID_FK"
        foreign key ("ACCESSORY_ID") references "ACCESSORY" ("ID")
        on delete cascade,
    constraint "ACCESSORY_CATEGORY_CATEGORY_ID_FK"
        foreign key ("CATEGORY_ID") references "CATEGORY" ("ID")
        on delete cascade
);

create table "ORDER_COSTUME"
(
    "ORDER_ID" int not null,
    "COSTUME_ID" int not null,
    constraint "ORDER_COSTUME_PK"
        primary key ("ORDER_ID", "COSTUME_ID"),
    constraint "ORDER_COSTUME_ORDER_ID_FK"
        foreign  key ("ORDER_ID") references "ORDER" ("ID")
        on delete cascade,
    constraint "ORDER_COSTUME_COSTUME_ID_FK"
        foreign key ("COSTUME_ID") references "COSTUME" ("ID")
        on delete cascade
);

create table "ORDER_ACCESSORY"
(
    "ORDER_ID" int not null,
    "ACCESSORY_ID" int not null,
    constraint "ORDER_ACCESSORY_PK"
        primary key ("ORDER_ID", "ACCESSORY_ID"),
    constraint "ORDER_ACCESSORY_ORDER_ID_FK"
        foreign key ("ORDER_ID") references "ORDER" ("ID")
        on delete cascade,
    constraint "ORDER_ACCESSORY_ACCESSORY_ID_FK"
        foreign key ("ACCESSORY_ID") references "ACCESSORY" ("ID")
        on delete cascade
);

--TRIGGER--
create sequence "user_id";
create or replace trigger "user_id"
	before insert on "USER"
	for each row
begin
	if :NEW."ID" is null then
		:NEW."ID" := "user_id".NEXTVAL;
	end if;
end;

create sequence "order_id";
create or replace trigger "order_id"
	before insert on "ORDER"
	for each row
begin
	if :NEW."ID" is null then
		:NEW."ID" := "order_id".NEXTVAL;
	end if;
end;


-- INSERT --
insert into "USER" ("FIRST_NAME", "SURNAME", "ADDRESS", "EMAIL", "PHONE_CONTACT")
                values ('John', 'Wick', 'Zerotinova 1, Ostrava','wickjohn@gmail.cz','09123456781');

insert into "USER" ("FIRST_NAME", "SURNAME", "ADDRESS", "EMAIL", "PHONE_CONTACT")
                values ('Jackie', 'Chan', 'Horalkova 1, Brno','cinskyherec@gmail.com','0969999666');

insert into "USER" ("FIRST_NAME", "SURNAME", "ADDRESS", "EMAIL", "PHONE_CONTACT")
                values ('Diana', 'Morova', 'Bičíkova 24, Bratislava','navzdymlada@gmail.com','0911222444');

insert into "USER" ("FIRST_NAME", "SURNAME", "ADDRESS", "EMAIL", "PHONE_CONTACT")
                values ('Zuzana', 'Nova', 'Slovenska 24, Kosice','zuza@gmail.com','0911233455');

insert into "USER" ("FIRST_NAME", "SURNAME", "ADDRESS", "EMAIL", "PHONE_CONTACT")
                values ('Jan', 'Stary', 'Ceska 4, Praha','janstary@gmail.com','0911112222');

insert into "CLIENT" ("USER_ID", "BANK_ACC_NUMBER") values (1, 'CZ5508000000001234567899');
insert into "CLIENT" ("USER_ID", "BANK_ACC_NUMBER") values (3, 'SK5508000000001234567444');
insert into "CLIENT" ("USER_ID", "BANK_ACC_NUMBER") values (4, 'SK5508000000001234567488');

insert into "EMPLOYEE" ("USER_ID", "START_DATE", "SALARY", "ID_NUMBER")
                values (2, TO_DATE('2009-08-01', 'yyyy/mm/dd'), '800', 'EX292929');
insert into "EMPLOYEE" ("USER_ID", "START_DATE", "SALARY", "ID_NUMBER")
                values (5, TO_DATE('2019-09-01', 'yyyy/mm/dd'), '850', 'EX292930');

insert into "CATEGORY" ("NAME", "EVENT_TYPE", "AGE_CATEGORY")
                values ('Superhrdinovia', 'Karneval', '7-15 rokov');
insert into "CATEGORY" ("NAME", "EVENT_TYPE", "AGE_CATEGORY")
                values ('Vojak', 'Karneval', '10-15 rokov');
insert into "CATEGORY" ("NAME", "EVENT_TYPE", "AGE_CATEGORY")
                values ('Spolocenske oblecenie', 'Ples', '15-18 rokov');

insert into "COSTUME" ("PRODUCER", "MATERIAL", "SIZE", "DESCRIPTION", "DATE_OF_PRODUCTION")
                values ('Nike', 'Latex', 'M', 'WonderWoman - kostym pre zeny', TO_DATE('2021-10-01', 'yyyy/mm/dd'));
insert into "COSTUME" ("PRODUCER", "MATERIAL", "SIZE", "DESCRIPTION", "DATE_OF_PRODUCTION")
                values ('Adidas', 'Bavlna', 'XS', 'Batman maska', TO_DATE('2020-08-01', 'yyyy/mm/dd'));
insert into "COSTUME" ("PRODUCER", "MATERIAL", "SIZE", "DESCRIPTION", "DATE_OF_PRODUCTION")
                values ('Adidas', 'Bavlna', 'L', 'Sako a nohavice', TO_DATE('2020-09-01', 'yyyy/mm/dd'));

insert into "ACCESSORY" ("NAME", "USAGE", "WEAR") values ('Batman rukavice', 'doplnok na ruky', 'nove');
insert into "ACCESSORY" ("NAME", "USAGE", "WEAR") values ('Korunka', 'doplnok do vlasov', 'mierne opotrebenie');


insert into "ORDER" ("DATE", "LENGTH_OF_RENTAL", "CLIENT_ID", "EMPLOYEE_ID") values (TO_DATE('2021-08-01', 'yyyy/mm/dd'), '10 dni', 1, 2);
insert into "ORDER" ("DATE", "LENGTH_OF_RENTAL", "CLIENT_ID", "EMPLOYEE_ID") values (TO_DATE('2021-09-11', 'yyyy/mm/dd'), '5 dni', 3, 2);
insert into "ORDER" ("DATE", "LENGTH_OF_RENTAL", "CLIENT_ID", "EMPLOYEE_ID") values (TO_DATE('2021-10-12', 'yyyy/mm/dd'), '2 dni', 4, 5);

insert into "FINE" ("DATE_OF_ISSUE", "AMOUNT", "DUE_DATE", "CLIENT_ID", "EMPLOYEE_ID") values (TO_DATE('2021-08-11', 'yyyy/mm/dd'), '5', TO_DATE('2021-08-18', 'yyyy/mm/dd'), 1, 2);
insert into "FINE" ("DATE_OF_ISSUE", "AMOUNT", "DUE_DATE", "CLIENT_ID", "EMPLOYEE_ID") values (TO_DATE('2021-09-15', 'yyyy/mm/dd'), '10', TO_DATE('2021-09-22', 'yyyy/mm/dd'), 1, 2);
insert into "FINE" ("DATE_OF_ISSUE", "AMOUNT", "DUE_DATE", "CLIENT_ID", "EMPLOYEE_ID") values (TO_DATE('2021-11-11', 'yyyy/mm/dd'), '10', TO_DATE('2021-11-18', 'yyyy/mm/dd'), 4, 5);

insert into "COSTUME_CATEGORY" ("COSTUME_ID", "CATEGORY_ID") values (1, 1);
insert into "COSTUME_CATEGORY" ("COSTUME_ID", "CATEGORY_ID") values (2, 1);
insert into "COSTUME_CATEGORY" ("COSTUME_ID", "CATEGORY_ID") values (3, 3);

insert into "ACCESSORY_CATEGORY" ("ACCESSORY_ID", "CATEGORY_ID") values (1, 1);
insert into "ACCESSORY_CATEGORY" ("ACCESSORY_ID", "CATEGORY_ID") values (2, 3);

insert into "ORDER_COSTUME" ("ORDER_ID", "COSTUME_ID") values (1, 1);
insert into "ORDER_COSTUME" ("ORDER_ID", "COSTUME_ID") values (2, 2);
insert into "ORDER_COSTUME" ("ORDER_ID", "COSTUME_ID") values (3, 3);

insert into "ORDER_ACCESSORY" ("ORDER_ID", "ACCESSORY_ID") values (1, 1);
insert into "ORDER_ACCESSORY" ("ORDER_ID", "ACCESSORY_ID") values (3, 2);

--TRIGGER 1 demonstracia --
select "ID", "FIRST_NAME", "SURNAME"
from "USER"
    order by "ID";

-- TRIGGER 2 demonstracia --
select "ID", "DATE", "CLIENT_ID"
from "ORDER"
    order by "ID";

-- Procedura 1 --
create or replace procedure "get_employee_age"
    ("name" in varchar2)
    is
        start_date date;
        currentYear number;
        start_year number;
        cursor cursor_1 is
        select e."START_DATE"
            from "EMPLOYEE" e, "USER" u
            where "name" = u."SURNAME";
    begin
        open cursor_1;
        fetch cursor_1 into start_date;

        if cursor_1%notfound then
            start_date := CURRENT_DATE;
        end if;
        currentYear := extract(year from CURRENT_DATE);
        start_year := extract(year from start_date);
        dbms_output.put_line('Zamestnanec ('||"name"||') je zamestnany' || TO_CHAR(currentYear - start_year));
        close cursor_1;

        exception
            when others then
                raise_application_error(-20001,'Nastala chyba v procedúre `get_employee_age`- '||SQLCODE||' -ERROR- '||SQLERRM);
    end;

-- Procedura 2 s kurzorom--
create or replace procedure "count_all_orders"
    ("before_date" in date)
    is
        orders_count number;
        order_date date;
        cursor cursor_1 is
            select "DATE" from "ORDER";
    begin
        orders_count := 0;
        open cursor_1;
        loop
            fetch cursor_1 into order_date;
            exit when cursor_1%notfound;
            if order_date <= "before_date" then
                orders_count := orders_count + 1;
            end if;
        end loop;
        close cursor_1;
    dbms_output.put_line('Počet všetkých stretnutí pred ' || TO_DATE("before_date", 'yyyy/mm/dd') || TO_CHAR(orders_count));
    exception
        when others then
            raise_application_error(-20001,'Nastala chyba v procedúre `count_all_orders`- '||SQLCODE||' -ERROR- '||SQLERRM);
    end;

begin "get_employee_age"('Stary'); end;
begin "count_all_orders"(TO_DATE('2021-09-11', 'yyyy/mm/dd')); end;

-- Prvy dotaz SELECT, spojenie 2 tabuliek ( Hlada Meno, Priezvisko a cislo uctu klienta )
SELECT u.FIRST_NAME, u.SURNAME, c.BANK_ACC_NUMBER FROM "USER" u, "CLIENT" c WHERE u.ID = c.USER_ID;

-- Druhy dotaz SELECT, spojenie 2 tabuliek ( Hlada Meno, Priezvisko a ID zamestnanca )
SELECT u.FIRST_NAME, u.SURNAME, e.ID_NUMBER FROM "USER" u, "EMPLOYEE" e WHERE u.ID = e.USER_ID;

-- Jeden dotaz SELECT, spojenie 3 tabuliek ( Hlada Popis, Velkost a Vekovu kategoriu kostymu )
SELECT c.DESCRIPTION, c."SIZE", ca.AGE_CATEGORY FROM COSTUME c, CATEGORY ca, COSTUME_CATEGORY cc WHERE c.ID = cc.COSTUME_ID AND ca.ID = cc.CATEGORY_ID;

-- Prvy dotaz s GROUP BY a agregacnou funkciou ( Hlada kolko dostal klient pokut )
SELECT COUNT(f.ID) as NUMBER_OF_FINES, u.FIRST_NAME, u.SURNAME FROM "USER" u,  CLIENT c, FINE f WHERE  u.ID = c.USER_ID AND c.USER_ID = f.CLIENT_ID GROUP BY u.FIRST_NAME, u.SURNAME ;

-- Druhy dotaz s GROUP BY a agregacnou funkciou ( Hlada celkovu sumu , ktoru klient zaplatil za pokuty )
SELECT SUM(f.AMOUNT) as TOTAL_AMOUNT , u.FIRST_NAME, u.SURNAME FROM "USER" u,  CLIENT c, FINE f WHERE  u.ID = c.USER_ID AND c.USER_ID = f.CLIENT_ID GROUP BY u.FIRST_NAME, u.SURNAME ;

-- Jeden dotaz s EXISTS ( Hlada objednavky v 7. a 8. mesiaci roku 2021 )
SELECT c.DESCRIPTION, o."DATE", o.ID FROM "ORDER" o , ORDER_COSTUME oc, COSTUME c
        WHERE  EXISTS (SELECT * FROM "ORDER" WHERE "DATE" = o."DATE" AND "DATE" BETWEEN TO_DATE('2021-07-01', 'YYYY-MM-DD') AND TO_DATE('2021-08-31', 'YYYY-MM-DD'))
        AND o.ID = oc.ORDER_ID AND c.ID = oc.COSTUME_ID;

-- Jeden dotaz s IN ( Hlada klientov, ktori uz boli pokutovani)
SELECT u.FIRST_NAME, u.SURNAME FROM CLIENT c, "USER" u, FINE f WHERE c.USER_ID = u.ID AND f.CLIENT_ID = c.USER_ID AND c.USER_ID IN (SELECT f.CLIENT_ID FROM FINE ) GROUP BY u.FIRST_NAME, u.SURNAME;


-- Explain plan bez indexu (Hlada id zamestnanca a pocet objdednavok daneho zamestnanca) ( cost 4 ) (TABLE ACCESS FULL)

EXPLAIN PLAN FOR
    SELECT  COUNT(o.ID) AS NUMBER_OF_ORDERS, o.EMPLOYEE_ID
    FROM EMPLOYEE e, "ORDER" o WHERE e.USER_ID = o.EMPLOYEE_ID
    GROUP BY o.EMPLOYEE_ID;
SELECT * FROM table (DBMS_XPLAN.DISPLAY);

-- -- Explain plan s indexom (Hlada id zamestnanca a pocet objdednavok daneho zamestnanca) ( cost 1 ) (INDEX FULL SCAN)
DROP INDEX ENPLOYEE_index;
DROP INDEX  ORDER_index;

CREATE INDEX ENPLOYEE_index ON EMPLOYEE(USER_ID);
CREATE INDEX ORDER_index ON "ORDER"(EMPLOYEE_ID);


EXPLAIN PLAN FOR
    SELECT  COUNT(o.ID) AS NUMBER_OF_ORDERS, o.EMPLOYEE_ID
    FROM EMPLOYEE e, "ORDER" o
    WHERE e.USER_ID = o.EMPLOYEE_ID
    GROUP BY o.EMPLOYEE_ID;
SELECT * FROM table (DBMS_XPLAN.DISPLAY);

--Prava pre druheho clena timu
GRANT ALL ON ORDER_ACCESSORY TO XZALEZ01;
GRANT ALL ON ORDER_COSTUME TO XZALEZ01;
GRANT ALL ON ACCESSORY_CATEGORY TO XZALEZ01;
GRANT ALL ON COSTUME_CATEGORY TO XZALEZ01;
GRANT ALL ON FINE TO XZALEZ01;
GRANT ALL ON "ORDER" TO XZALEZ01;
GRANT ALL ON ACCESSORY TO XZALEZ01;
GRANT ALL ON COSTUME TO XZALEZ01;
GRANT ALL ON CATEGORY TO XZALEZ01;
GRANT ALL ON EMPLOYEE TO XZALEZ01;
GRANT ALL ON CLIENT TO XZALEZ01;
GRANT ALL ON "USER" TO XZALEZ01;

-- MATERIALIZED VIEW

DROP MATERIALIZED VIEW MAT_VIEW;

CREATE MATERIALIZED VIEW MAT_VIEW
CACHE
BUILD IMMEDIATE AS
    SELECT XHOMOL28.u.FIRST_NAME, XHOMOL28.u.SURNAME, XHOMOL28.c.BANK_ACC_NUMBER FROM XHOMOL28."USER" u, XHOMOL28.CLIENT c WHERE u.ID = c.USER_ID;

GRANT ALL ON MAT_VIEW TO XZALEZ01;

SELECT * FROM MAT_VIEW;