ALTER SESSION SET NLS_DATE_FORMAT = 'DD.MM.YYYY';
SET SERVEROUTPUT ON;
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE ODDELENI CASCADE CONSTRAINTS';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE VEDOUCI CASCADE CONSTRAINTS';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE KNIHOVNIK CASCADE CONSTRAINTS';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE PERSONAL CASCADE CONSTRAINTS';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE VYPUJCKA CASCADE CONSTRAINTS';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE VYTISK CASCADE CONSTRAINTS';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE REZERVACE CASCADE CONSTRAINTS';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE TITUL CASCADE CONSTRAINTS';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE SPISOVATEL CASCADE CONSTRAINTS';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE ZANR CASCADE CONSTRAINTS';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE CTENAR CASCADE CONSTRAINTS';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/
BEGIN
EXECUTE IMMEDIATE 'DROP MATERIALIZED VIEW ' || KNIHOVNIK_XSHOR02;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -12003 THEN
RAISE;
END IF;
END;
/
CREATE TABLE CTENAR (
RODNE_CISLO VARCHAR(20) NOT NULL PRIMARY KEY, -- Cizinci mají jiná "čísla" než občani ČR a ani všichni ti nemají validní RČ
JMENO VARCHAR(20) NOT NULL,
PRIJMENI VARCHAR(20) NOT NULL,
DATUM_NAROZENI DATE NOT NULL, -- GENERATED ALWAYS AS (TO_DATE(SUBSTR(RODNE_CISLO,1,6), 'YYMMDD')), -- Chyba ve zjistovani roku pouze ze dvou cislic 1900/2000
ADRESA VARCHAR(50) NOT NULL, -- Není třeba rozdělovat, vždy bude použita celá
EMAIL VARCHAR(50) NOT NULL, -- kontrola jestli je '.' '@' na správném místě ve správném počtu
TELEFON NUMBER(9) NOT NULL, -- až 9 číslic, bez předvolby
CONSTRAINT CHECK_EMAIL CHECK(( EMAIL LIKE '_%@_%._' OR EMAIL LIKE '_%@_%.__' OR EMAIL LIKE '_%@_%.___' ) AND EMAIL NOT LIKE '_%@%.%.%.%' AND EMAIL NOT LIKE '%@%@%' )
);
CREATE TABLE PERSONAL (
ID_PERSONAL NUMBER(10) GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
PRIHLASOVACI_JMENO VARCHAR(20) NOT NULL,
HESLO VARCHAR(20) NOT NULL,
RODNE_CISLO VARCHAR(20) NOT NULL, -- Cizinci mají jiná "čísla" než občani ČR a ani všichni ti nemají validní RČ
JMENO VARCHAR(20) NOT NULL,
PRIJMENI VARCHAR(20) NOT NULL,
TITUL VARCHAR(10) -- ing., mgr., ...
);
CREATE TABLE ODDELENI ( --Dětská literatura, knihovna pro dospělé čtenáře, knihovna pro neslyšící a nevidomé, časopisy a denní tisk..
NAZEV_ODDELENI NVARCHAR2(256) PRIMARY KEY,
TELEFON_ODDELENI CHAR(13) NOT NULL
);
CREATE TABLE KNIHOVNIK (
ID_OSOBY NUMBER(10) NOT NULL UNIQUE,
ODDELENI NVARCHAR2(256) NOT NULL,
FOREIGN KEY (ID_OSOBY) REFERENCES PERSONAL(ID_PERSONAL),
FOREIGN KEY (ODDELENI) REFERENCES ODDELENI(NAZEV_ODDELENI)
);
CREATE TABLE VEDOUCI ( -- Ředitel, vedoucí jednotlících oddělení, vedoucí pobočky, zástupce ředitele
ID_OSOBY INTEGER NOT NULL,
POZICE NVARCHAR2(256) NOT NULL,
FOREIGN KEY (ID_OSOBY) REFERENCES PERSONAL(ID_PERSONAL)
);
CREATE TABLE ZANR (
ID_ZANRU NUMBER(3) GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
NAZEV VARCHAR(20) NOT NULL,
OBDOBI VARCHAR(9) NOT NULL, -- '1830-1920'
CONSTRAINT CHECK_OBDOBI CHECK( REGEXP_LIKE(OBDOBI, '[0-9]{4}[-][0-9]{4}') )
);
CREATE TABLE SPISOVATEL (
PORADOVE_CISLO NUMBER(10) GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
JMENO VARCHAR(20) NOT NULL,
PRIJMENI VARCHAR(20) NOT NULL,
DATUM_NAROZENI DATE NOT NULL,
DATUM_UMRTI DATE,
ZANR NUMBER(3) NOT NULL,
NARODNOST CHAR(3) NOT NULL, -- Country code podle ISO 3166-1 alpha-3
CONSTRAINT CHECK_ZIVOT CHECK( DATUM_NAROZENI < DATUM_UMRTI ),
FOREIGN KEY (ZANR) REFERENCES ZANR(ID_ZANRU),
CONSTRAINT CHECK_NARODNOST CHECK( LENGTH(NARODNOST)=3 AND REGEXP_LIKE(NARODNOST, '[A-Z]{3}') )
);
CREATE TABLE TITUL (
ISBN NUMBER(13) PRIMARY KEY,
NAZEV VARCHAR(50) NOT NULL,
AUTOR NUMBER(10) NOT NULL,
ZANR NUMBER(3) NOT NULL,
VYDANI NUMBER(2),
FOREIGN KEY (AUTOR) REFERENCES SPISOVATEL(PORADOVE_CISLO),
FOREIGN KEY (ZANR) REFERENCES ZANR(ID_ZANRU),
CONSTRAINT CHECK_ISBN CHECK( (LENGTH(ISBN)=10 AND REGEXP_LIKE(ISBN, '[0-9]{10}')) OR (LENGTH(ISBN)=13 AND REGEXP_LIKE(ISBN, '[0-9]{13}') ) )
);
CREATE TABLE REZERVACE (
CISLO_REZERVACE NUMBER(10) GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
DATUM_REZERVACE DATE NOT NULL,
TITUL NUMBER(13) NOT NULL,
CTENAR VARCHAR(20) NOT NULL,
FOREIGN KEY (TITUL) REFERENCES TITUL(ISBN),
FOREIGN KEY (CTENAR) REFERENCES CTENAR(RODNE_CISLO)
);
CREATE TABLE VYTISK (
SERIOVE_CISLO NUMBER(20) PRIMARY KEY,
TITUL NUMBER(13) NOT NULL,
FORMA_VYDANI VARCHAR(20) NOT NULL,
KVALITA_VYTISKU VARCHAR(20) NOT NULL,
FOREIGN KEY (TITUL) REFERENCES TITUL(ISBN)
);
CREATE TABLE VYPUJCKA (
CISLO_VYPUJCKY NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
CTENAR VARCHAR(20) NOT NULL,
VYTISK NUMBER(20) NOT NULL,
DATUM_ZAPUJCENI DATE DEFAULT SYSDATE NOT NULL,
DOBA_ZAPUJCENI DATE DEFAULT SYSDATE+30 NOT NULL,
DATUM_VRACENI DATE,
VYTVORIL NUMBER(10) NOT NULL,
UZAVREL NUMBER(10),
FOREIGN KEY (CTENAR) REFERENCES CTENAR(RODNE_CISLO),
FOREIGN KEY (VYTVORIL) REFERENCES KNIHOVNIK(ID_OSOBY),
FOREIGN KEY (UZAVREL) REFERENCES KNIHOVNIK(ID_OSOBY),
FOREIGN KEY (VYTISK) REFERENCES VYTISK(SERIOVE_CISLO)
);
CREATE OR REPLACE TRIGGER CHECK_DATE_REZERVACE
BEFORE INSERT OR UPDATE ON REZERVACE
FOR EACH ROW
BEGIN
IF( :NEW.DATUM_REZERVACE < SYSDATE )
THEN
raise_application_error(-20000, 'Rezervace musí být na datum v budoucnosti !');
END IF;
END;
/
CREATE OR REPLACE TRIGGER CHECK_DATE_CTENAR
BEFORE INSERT OR UPDATE ON CTENAR
FOR EACH ROW
BEGIN
IF( :NEW.DATUM_NAROZENI > SYSDATE )
THEN
raise_application_error(-20001, 'Ctenari narozeni v budoucnosti, necht se zaregistrují ve své casove linii !');
END IF;
END;
/
CREATE OR REPLACE TRIGGER prvni_vydani before insert on TITUL
for each row -- ... pro kazdou radku znovu ...
BEGIN
IF (:NEW.VYDANI IS NULL) THEN
select 1 into :NEW.VYDANI from dual;
END IF;
END prvni_vydani;
/
CREATE OR REPLACE PROCEDURE najdi_knihu_podle_isbn (hledane_isbn NUMBER) AS
radek TITUL%ROWTYPE;
CURSOR my_cursor
IS
SELECT *
INTO radek
FROM TITUL
WHERE ISBN = hledane_isbn;
BEGIN
OPEN my_cursor;
fetch my_cursor into radek;
IF my_cursor%notfound THEN
DBMS_OUTPUT.PUT_LINE(' Kniha s pozadovanym ISBN v databazi nenalezena');
ELSE
DBMS_OUTPUT.put_line (' Nazev hledane knihy :' || radek.NAZEV);
END IF;
CLOSE my_cursor;
END;
/
INSERT INTO CTENAR (RODNE_CISLO, JMENO, PRIJMENI, DATUM_NAROZENI, ADRESA, EMAIL, TELEFON) VALUES ('8508051111', 'Milos', 'Okoun', TO_DATE('1.1.1951', 'DD.MM.YYYY'), 'Brno', 'mujmail@mujweb.muj', 111111111);
INSERT INTO CTENAR (RODNE_CISLO, JMENO, PRIJMENI, DATUM_NAROZENI, ADRESA, EMAIL, TELEFON) VALUES ('6402282222', 'Petr', 'Dobrak', TO_DATE('2.2.1932', 'DD.MM.YYYY'), 'Znojmo', 'mujmail@jehoweb.muj', 222222222);
INSERT INTO CTENAR (RODNE_CISLO, JMENO, PRIJMENI, DATUM_NAROZENI, ADRESA, EMAIL, TELEFON) VALUES ('7212123333', 'Karel', 'Mikin', TO_DATE('3.3.1997', 'DD.MM.YYYY'), 'Melbourne', 'mujmail@jejichweb.muj', 333333333);
INSERT INTO CTENAR (RODNE_CISLO, JMENO, PRIJMENI, DATUM_NAROZENI, ADRESA, EMAIL, TELEFON) VALUES ('721212D33', 'Ondra', 'Zelenka' ,TO_DATE('9.9.2006', 'DD.MM.YYYY'), 'Melbourne', 'jehomail@jejichweb.muj', 999999999);
--select * from CTENAR;
INSERT INTO PERSONAL (PRIHLASOVACI_JMENO, HESLO, RODNE_CISLO, JMENO, PRIJMENI, TITUL) VALUES ('xokou86', 'gdslkg5', '912123333', 'Ondra', 'Okoun', 'ing');
INSERT INTO PERSONAL (PRIHLASOVACI_JMENO, HESLO, RODNE_CISLO, JMENO, PRIJMENI, TITUL) VALUES ('xdobr12', 'nkokfl6', '9012123333', 'Karel', 'Dobrak', 'mgr');
INSERT INTO PERSONAL (PRIHLASOVACI_JMENO, HESLO, RODNE_CISLO, JMENO, PRIJMENI, TITUL) VALUES ('xmiki78', 'dg6gds2', '8412123333', 'Petr', 'Mikin', 'bc');
INSERT INTO PERSONAL (PRIHLASOVACI_JMENO, HESLO, RODNE_CISLO, JMENO, PRIJMENI, TITUL) VALUES ('xzele14', 'sdh56d5' ,'8812123333', 'Milos', 'Zelenka', 'bc');
--select * from PERSONAL;
INSERT INTO ODDELENI (NAZEV_ODDELENI, TELEFON_ODDELENI) VALUES ('detska_literatura', '736284579');
INSERT INTO ODDELENI (NAZEV_ODDELENI, TELEFON_ODDELENI) VALUES ('dospeli_ctenari', '605987326');
INSERT INTO ODDELENI (NAZEV_ODDELENI, TELEFON_ODDELENI) VALUES ('nevidomi_slabozraky', '369800300');
--select * from ODDELENI;
INSERT INTO KNIHOVNIK (ID_OSOBY, ODDELENI) VALUES (1, 'detska_literatura');
INSERT INTO KNIHOVNIK (ID_OSOBY, ODDELENI) VALUES (2, 'dospeli_ctenari');
INSERT INTO KNIHOVNIK (ID_OSOBY, ODDELENI) VALUES (3, 'nevidomi_slabozraky');
--select * from KNIHOVNIK;
INSERT INTO VEDOUCI (ID_OSOBY, POZICE) VALUES (1, 'ředitel');
INSERT INTO VEDOUCI (ID_OSOBY, POZICE) VALUES (2, 'zástupce');
INSERT INTO VEDOUCI (ID_OSOBY, POZICE) VALUES (2, 'vedoucí_pobočky');
--select * from VEDOUCI;
INSERT INTO ZANR (NAZEV, OBDOBI) VALUES ('Sci-Fi', '1930-1960');
INSERT INTO ZANR (NAZEV, OBDOBI) VALUES ('Fantasy', '1960-1990');
INSERT INTO ZANR (NAZEV, OBDOBI) VALUES ('Horor', '1800-1900');
--INSERT INTO ZANR (NAZEV, OBDOBI) VALUES ('Horor', '180-1900');
--INSERT INTO ZANR (NAZEV, OBDOBI) VALUES ('Horor', '1800-190');
--INSERT INTO ZANR (NAZEV, OBDOBI) VALUES ('Horor', '180001900');
--select * from ZANR;
INSERT INTO SPISOVATEL (JMENO, PRIJMENI, DATUM_NAROZENI, DATUM_UMRTI, ZANR, NARODNOST) VALUES ('Edgar Allan', 'Poe', TO_DATE('11.11.1851', 'DD.MM.YYYY'), TO_DATE('11.11.1911', 'DD.MM.YYYY'), 3, 'USA');
INSERT INTO SPISOVATEL (JMENO, PRIJMENI, DATUM_NAROZENI, DATUM_UMRTI, ZANR, NARODNOST) VALUES ('George', 'Orwell', TO_DATE('11.11.1892', 'DD.MM.YYYY'), TO_DATE('11.11.1973', 'DD.MM.YYYY'), 1, 'GBR');
INSERT INTO SPISOVATEL (JMENO, PRIJMENI, DATUM_NAROZENI, DATUM_UMRTI, ZANR, NARODNOST) VALUES ('Patrick', 'Ryan', TO_DATE('11.11.1916', 'DD.MM.YYYY'), TO_DATE('11.11.1989', 'DD.MM.YYYY'), 2, 'GBR');
--SELECT * FROM SPISOVATEL;
INSERT INTO TITUL (ISBN, NAZEV, AUTOR, ZANR) VALUES (1813848446, 'Jak neudelat IDS', 1, 3);
INSERT INTO TITUL VALUES (5841848435, 'Jak udelat IDS', 1, 3, 2);
INSERT INTO TITUL VALUES (7987564515333, 'Jak udelat IDS', 1, 3, 3);
--INSERT INTO TITUL VALUES (798756451, 'Jak udelat IDS', 1, 3, 3);
--select * from TITUL;
--INSERT INTO REZERVACE (DATUM_REZERVACE, TITUL, CTENAR) VALUES ( TO_DATE('12.12.2015', 'DD.MM.YYYY'), 584184843, 8508051111);
--INSERT INTO REZERVACE (DATUM_REZERVACE, TITUL, CTENAR) VALUES ( TO_DATE('15.2.2000', 'DD.MM.YYYY'), 181384844, 8508051111);
INSERT INTO REZERVACE (DATUM_REZERVACE, TITUL, CTENAR) VALUES ( TO_DATE('12.12.2016', 'DD.MM.YYYY'), 5841848435, 8508051111);
INSERT INTO REZERVACE (DATUM_REZERVACE, TITUL, CTENAR) VALUES ( TO_DATE('15.2.2020', 'DD.MM.YYYY'), 5841848435, 8508051111);
INSERT INTO REZERVACE (DATUM_REZERVACE, TITUL, CTENAR) VALUES ( TO_DATE('17.9.2016', 'DD.MM.YYYY'), 7987564515333, 7212123333);
--select * from REZERVACE;
INSERT INTO VYTISK VALUES ( 184848384848, 7987564515333, 'pevna vazba', 'vyborna');
INSERT INTO VYTISK VALUES ( 784518841974, 5841848435, 'brožované', 'spatna');
INSERT INTO VYTISK VALUES ( 131187124545, 5841848435, 'pevna vazba', 'dobra');
--select * from VYTISK;
INSERT INTO VYPUJCKA (CTENAR, VYTISK, VYTVORIL, UZAVREL) VALUES ('8508051111', 784518841974, 2, 2);
INSERT INTO VYPUJCKA (CTENAR, VYTISK, VYTVORIL) VALUES ('6402282222', 784518841974, 1);
INSERT INTO VYPUJCKA (CTENAR, VYTISK, VYTVORIL) VALUES ('6402282222', 131187124545, 2);
--SELECT * FROM VYPUJCKA;
--DOTAZY SELECT--
--1.spojení dvou tabulek--
--Vypíše názvy všech titulů, které mají výtisky špatné kvality--
--SELECT NAZEV
--FROM TITUL, VYTISK
--WHERE TITUL.ISBN = VYTISK.TITUL AND VYTISK.KVALITA_VYTISKU='spatna';
--Vypíše všechny čtenáře, kteří mají nějakou rezervaci--
--SELECT CTENAR.JMENO, CTENAR.PRIJMENI, REZERVACE.DATUM_REZERVACE
--FROM CTENAR, REZERVACE
--WHERE CTENAR.RODNE_CISLO = REZERVACE.CTENAR;
--2.spojení 3 tabulek
--Vypíše všechny výpujčky, které mají čtenáři.
--SELECT VYPUJCKA.CISLO_VYPUJCKY, VYPUJCKA.VYTISK, CTENAR.JMENO, CTENAR.PRIJMENI, VYPUJCKA.DATUM_ZAPUJCENI, VYPUJCKA.DOBA_ZAPUJCENI, VYTISK.SERIOVE_CISLO, VYPUJCKA.VYTVORIL, VYPUJCKA.UZAVREL
--FROM (CTENAR INNER JOIN VYPUJCKA ON (VYPUJCKA.CTENAR=CTENAR) INNER JOIN VYTISK ON (VYTISK=VYTISK.SERIOVE_CISLO))
--WHERE VYTISK=SERIOVE_CISLO AND RODNE_CISLO=CTENAR ORDER BY CISLO_VYPUJCKY ASC;
--3.dotazy s klauzulí GROUP BY a agregační funkcí
--Vypíše sestupně řazený počet výpujček v jednotlivých dnech.
--SELECT COUNT(*)AS POCET_VYPUJCEK,DATUM_ZAPUJCENI
--FROM VYPUJCKA
--GROUP BY DATUM_ZAPUJCENI
--ORDER BY POCET_VYPUJCEK DESC;
--Vypíše čtenáře, kteří mají 2 a více výpújček
--SELECT CTENAR
--FROM VYPUJCKA
--GROUP BY CTENAR HAVING COUNT(*)>=2;
--4. Dotaz Exists--
--Vypíše knihovníka, který nezaložil žádnou výpůjčku--
--SELECT JMENO, PRIJMENI, ID_OSOBY
--FROM KNIHOVNIK, PERSONAL
--WHERE NOT EXISTS
--(
-- SELECT ID_OSOBY
-- FROM VYPUJCKA
-- WHERE VYTVORIL= ID_OSOBY
--);
--5. Prediktát IN--
--Vypíše informace o titulech, jež napsali spisovatelé žijící ve 20. století
--SELECT ISBN, NAZEV, JMENO, PRIJMENI
--FROM TITUL, SPISOVATEL
--WHERE PORADOVE_CISLO
--IN
--(
--SELECT PORADOVE_CISLO
--FROM TITUL, SPISOVATEL
--WHERE DATUM_NAROZENI BETWEEN '01-01-1901' AND '31-12-2000'
--AND DATUM_UMRTI BETWEEN '01-01-1901' AND '31-12-2000'
--);
GRANT ALL ON CTENAR TO xrenne00;
GRANT ALL ON KNIHOVNIK TO xrenne00;
GRANT ALL ON ODDELENI TO xrenne00;
GRANT ALL ON PERSONAL TO xrenne00;
GRANT ALL ON REZERVACE TO xrenne00;
GRANT ALL ON SPISOVATEL TO xrenne00;
GRANT ALL ON TITUL TO xrenne00;
GRANT ALL ON VEDOUCI TO xrenne00;
GRANT ALL ON VYPUJCKA TO xrenne00;
GRANT ALL ON VYTISK TO xrenne00;
GRANT ALL ON ZANR to xrenne00;
SELECT * FROM XSCHOR02.TITUL;
exec najdi_knihu_podle_isbn(7987564515332);
exec najdi_knihu_podle_isbn(7987564515333);
exec najdi_knihu_podle_isbn(7987564515334);
-------------Materializovany pohled----------------
-- spustit u 2. člen týmu
--BEGIN
-- EXECUTE IMMEDIATE 'DROP MATERIALIZED VIEW ' || KNIHOVNIK_XSHOR02;
--EXCEPTION
-- WHEN OTHERS THEN
-- IF SQLCODE != -12003 THEN
-- RAISE;
-- END IF;
--END;
--/
--CREATE MATERIALIZED VIEW KNIHOVNIK_XSHOR02
--CACHE --postupne optimalizuje cteni
--BUILD IMMEDIATE --po vytvoreni naplni pohled
--ENABLE QUERY REWRITE --bude pouzivany optimalizátorem
--AS SELECT * FROM XSCHOR02.KNIHOVNIK;
--GRANT ALL ON KNIHOVNIK_XSHOR02 TO xschor02;
---------------------------------------------------
-- spustit u 1. člena týmu
--select * from XRENNE00.KNIHOVNIK_XSHOR02;
---------------------------------------------------
--CREATE MATERIALIZED VIEW TESTVIEW_4 AS SELECT * FROM xrenne00.KNIHOVNIK;
--SELECT * FROM TESTVIEW_4;