Vytvorenie entít a ich naplnenie 

 

CREATE TABLE mesto

(id_mesta VARCHAR2(3) NOT NULL,

mesto VARCHAR2(30) NOT NULL,

PRIMARY KEY (id_mesta));

 

INSERT INTO mesto VALUES('1M','Humenne');

INSERT INTO mesto VALUES('2M','Nove Mesto nad Vahom');

INSERT INTO mesto VALUES('3M','Partizanske');

INSERT INTO mesto VALUES('4M','Kysucke Nove Mesto');

INSERT INTO mesto VALUES('5M','Vrutky');

INSERT INTO mesto VALUES('6M','Dolny Smokovec');

INSERT INTO mesto VALUES('7M','Dvorianky');

INSERT INTO mesto VALUES('8M','Kamenna Poruba');

INSERT INTO mesto VALUES('9M','Martin');

INSERT INTO mesto VALUES('10M','Zilina');

INSERT INTO mesto VALUES('11M','Wien');

INSERT INTO mesto VALUES('12M','Berlin');

INSERT INTO mesto VALUES('13M','Ostrava');

INSERT INTO mesto VALUES('14M','Krakow');

INSERT INTO mesto VALUES('15M','Rajecke Teplice');

 

 

CREATE TABLE stat

(id_statu VARCHAR2(3) NOT NULL,

stat VARCHAR2(15) NOT NULL,

PRIMARY KEY (id_statu));

 

INSERT INTO stat VALUES('1S','Slovensko');

INSERT INTO stat VALUES('2S','Nemecko');

INSERT INTO stat VALUES('3S','Rakusko');

INSERT INTO stat VALUES('4S','Polsko');

INSERT INTO stat VALUES('5S','Cesko');

 

CREATE TABLE klient

(id_klienta VARCHAR2(3) NOT NULL,

id_mesta VARCHAR2 (3) NOT NULL,

id_statu VARCHAR2 (3) NOT NULL,

meno VARCHAR2 (10) NOT NULL,

priezvisko VARCHAR2 (20) NOT NULL,

ulica VARCHAR2 (20) NOT NULL,

cislo_ulice VARCHAR2 (8) NOT NULL,

psc VARCHAR2 (6) NOT NULL,

tel_cislo VARCHAR2 (20) NOT NULL,

PRIMARY KEY (id_klienta),

FOREIGN KEY (id_mesta) REFERENCES mesto (id_mesta),

FOREIGN KEY (id_statu) REFERENCES stat (id_statu));

 

INSERT INTO klient

VALUES('1K','1M','1S','Roman','Kubo','Kosicka','2507/32','066 01','057/776 41 73');

INSERT INTO klient

VALUES('2K','2M','1S','Alica','Mudra','Beckovska','1603/20','915 01','032/771 74 02');

INSERT INTO klient

VALUES('3K','3M','1S','Jozefina','Baluchova','Skolska','190','958 01','038/749 32 24');

INSERT INTO klient

VALUES('4K','4M','1S','Jozef','Ochodnican','CSA','1301','024 04','041/421 42 73');

INSERT INTO klient

VALUES('5K','5M','1S','Jozef','Saga','Fatranska','5332/15','038 61','043/428 66 74');

INSERT INTO klient

VALUES('6K','6M','1S','Anna','Koporcova','Pod lesom','56','059 81','052/442 21 00');

INSERT INTO klient

VALUES('7K','7M','1S','Bartolomej','Kusnir','Agatova','46/3','076 62','056/679 73 43');

INSERT INTO klient

VALUES('8K','8M','1S','Ladislav','Hodas','Podlipie','344/24','013 14','041/549 80 75');

INSERT INTO klient

VALUES('9K','9M','1S','Bozena','Durickova','Zaborskeho','9','036 01','043/422 02 28');

INSERT INTO klient

VALUES('10K','10M','1S','Antonia','Sulovcova','Mladeznicka','3471/21','010 01','041/763 75 25');

INSERT INTO klient

VALUES('11K','11M','3S','Alois','Muller','Taborstrasse','8','1020','012 110 50');

INSERT INTO klient

VALUES('12K','12M','2S','Hilde','Schneider','Storkower Strasse','160','104 07','0304 172 400');

INSERT INTO klient

VALUES('13K','13M','5S','Jiri','Polert','Pavelcakova','22','701 38','599 099 717');

INSERT INTO klient

VALUES('14K','14M','4S','Malgorzata','Czepczyk','Ul. Lubicz','5','31-034','423 25 10');

INSERT INTO klient

VALUES('15K','15M','1S','Frantisek','Person','Osloboditelov','214/42','013 13','041/549 33 91');

 

CREATE TABLE hotel

(id_hotela VARCHAR2(3) NOT NULL,

hotel VARCHAR2(20) NOT NULL,

PRIMARY KEY (id_hotela));

 

INSERT INTO hotel VALUES('1H','Aphrodite');

INSERT INTO hotel VALUES('2H','Velka Fatra');

INSERT INTO hotel VALUES('3H','Villa Flora');

INSERT INTO hotel VALUES('4H','Villa Margareta');

INSERT INTO hotel VALUES('5H','Mala Fatra');

INSERT INTO hotel VALUES('6H','penzion Antik');

INSERT INTO hotel VALUES('7H','Laura');

 

CREATE TABLE ubytovanie

(id_zariadenia VARCHAR2(4) NOT NULL,

id_hotela VARCHAR2(3) NOT NULL,

pocet_noci VARCHAR2(10) NOT NULL,

pocet_lozok VARCHAR2(20) NOT NULL,

cena_noc NUMBER(3) NOT NULL,

PRIMARY KEY (id_zariadenia),

FOREIGN KEY (id_hotela) REFERENCES hotel (id_hotela));

 

INSERT INTO ubytovanie VALUES('1U','1H','do 3 noci','1-lozkova','77');

INSERT INTO ubytovanie VALUES('2U','1H','do 3 noci','2-lozkova','133');

INSERT INTO ubytovanie VALUES('3U','1H','do 3 noci','2-lozkova standard','90');

INSERT INTO ubytovanie VALUES('4U','1H','do 3 noci','apartman','156');

INSERT INTO ubytovanie VALUES('5U','1H','do 3 noci','pristelka','77');

INSERT INTO ubytovanie VALUES('6U','1H','do 4 noci','1-lozkova','67');

INSERT INTO ubytovanie VALUES('7U','1H','od 4 noci','2-lozkova','123');

INSERT INTO ubytovanie VALUES('8U','1H','od 4 noci','2-lozkova standard','86');

INSERT INTO ubytovanie VALUES('9U','1H','od 4 noci','apartman','146');

INSERT INTO ubytovanie VALUES('10U','1H','od 4 noci','pristelka','21');

INSERT INTO ubytovanie VALUES('11U','2H','do 3 noci','1-lozkova','48');

INSERT INTO ubytovanie VALUES('12U','2H','do 3 noci','2-lozkova','66');

INSERT INTO ubytovanie VALUES('13U','2H','do 3 noci','2-lozkova standard','116');

INSERT INTO ubytovanie VALUES('14U','2H','do 3 noci','apartman','136');

INSERT INTO ubytovanie VALUES('15U','2H','do 3 noci','pristelka','20');

INSERT INTO ubytovanie VALUES('16U','2H','od 4 noci','1-lozkova','41');

INSERT INTO ubytovanie VALUES('17U','2H','od 4 noci','2-lozkova','59');

INSERT INTO ubytovanie VALUES('18U','2H','od 4 noci','2-lozkova standard','109');

INSERT INTO ubytovanie VALUES('19U','2H','od 4 noci','apartman','126');

INSERT INTO ubytovanie VALUES('20U','2H','od 4 noci','pristelka','20');

INSERT INTO ubytovanie VALUES('21U','3H','do 3 noci','1-lozkova','28');

INSERT INTO ubytovanie VALUES('22U','3H','do 3 noci','2-lozkova','53');

INSERT INTO ubytovanie VALUES('23U','3H','do 3 noci','apartman','69');

INSERT INTO ubytovanie VALUES('24U','3H','do 3 noci','pristelka','18');

INSERT INTO ubytovanie VALUES('25U','3H','od 4 noci','1-lozkova','26');

INSERT INTO ubytovanie VALUES('26U','3H','od 4 noci','2-lozkova','49');

INSERT INTO ubytovanie VALUES('27U','3H','od 4 noci','apartman','64');

INSERT INTO ubytovanie VALUES('28U','3H','od 4 noci','pristelka','18');

INSERT INTO ubytovanie VALUES('29U','4H','do 3 noci','2-lozkova','56');

INSERT INTO ubytovanie VALUES('30U','4H','do 3 noci','apartman','81');

INSERT INTO ubytovanie VALUES('31U','4H','do 3 noci','pristelka','18');

INSERT INTO ubytovanie VALUES('32U','4H','od 4 noci','2-lozkova','53');

INSERT INTO ubytovanie VALUES('33U','4H','od 4 noci','apartman','76');

INSERT INTO ubytovanie VALUES('34U','4H','od 4 noci','pristelka','18');

INSERT INTO ubytovanie VALUES('35U','5H','do 3 noci','1-lozkova','28');

INSERT INTO ubytovanie VALUES('36U','5H','do 3 noci','2-lozkova','48');

INSERT INTO ubytovanie VALUES('37U','5H','do 3 noci','pristelka','15');

INSERT INTO ubytovanie VALUES('38U','5H','od 4 noci','1-lozkova','24');

INSERT INTO ubytovanie VALUES('39U','5H','od 4 noci','2-lozkova','43');

INSERT INTO ubytovanie VALUES('40U','5H','od 4 noci','pristelka','15');

INSERT INTO ubytovanie VALUES('41U','6H','do 3 noci','1-lozkova','26');

INSERT INTO ubytovanie VALUES('42U','6H','do 3 noci','2-lozkova','43');

INSERT INTO ubytovanie VALUES('43U','6H','do 3 noci','apartman','56');

INSERT INTO ubytovanie VALUES('44U','6H','do 3 noci','pristelka','15');

INSERT INTO ubytovanie VALUES('45U','6H','od 4 noci','1-lozkova','23');

INSERT INTO ubytovanie VALUES('46U','6H','od 4 noci','2-lozkova','39');

INSERT INTO ubytovanie VALUES('47U','6H','od 4 noci','apartman','53');

INSERT INTO ubytovanie VALUES('48U','6H','od 4 noci','pristelka','15');

INSERT INTO ubytovanie VALUES('49U','7H','do 3 noci','1-lozkova','31');

INSERT INTO ubytovanie VALUES('50U','7H','do 3 noci','2-lozkova','46');

INSERT INTO ubytovanie VALUES('51U','7H','do 3 noci','apartman','63');

INSERT INTO ubytovanie VALUES('52U','7H','do 3 noci','pristelka','15');

INSERT INTO ubytovanie VALUES('53U','7H','od 4 noci','1-lozkova','28');

INSERT INTO ubytovanie VALUES('54U','7H','od 4 noci','2-lozkova','43');

INSERT INTO ubytovanie VALUES('55U','7H','od 4 noci','apartman','59');

INSERT INTO ubytovanie VALUES('56U','7H','od 4 noci','pristelka','15');

 

CREATE TABLE cennik_procedur

(id_procedury VARCHAR2(3) NOT NULL,

nazov_procedury VARCHAR2(30) NOT NULL,

cas_trvania VARCHAR2(10) NOT NULL,

cena_procedury NUMBER NOT NULL,

PRIMARY KEY (id_procedury));

 

INSERT INTO cennik_procedur VALUES('1P','Relax Spa Sensation','155 minut','45');

INSERT INTO cennik_procedur VALUES('2P','Relax Exclusive','290 minut','31');

INSERT INTO cennik_procedur VALUES('3P','Relax Classic','335 minut','27');

INSERT INTO cennik_procedur VALUES('4P','Liecebny balik','100 minut','40');

INSERT INTO cennik_procedur VALUES('5P','Liecebny balik MINI','70 minut','27');

INSERT INTO cennik_procedur VALUES('6P','Zdravy chrbat','300 minut','132');

INSERT INTO cennik_procedur VALUES('7P','Anti-age Exclusive','360 minut','335');

INSERT INTO cennik_procedur VALUES('8P','Aphrodite Love Exclusive','360 minut','335');

INSERT INTO cennik_procedur VALUES('9P','Balik Fit&Slim','450 minut','451');

INSERT INTO cennik_procedur VALUES('10P','Antistresovy balik','250 minut','219');

INSERT INTO cennik_procedur VALUES('11P',Beauty Intensive','350 minut','319');

INSERT INTO cennik_procedur VALUES('12P','Beauty Anticellu','320 minut','262');

INSERT INTO cennik_procedur VALUES('13P','Kozmeticke osetrenie','70 minut','83');

INSERT INTO cennik_procedur VALUES('14P','Aphrodite vikend','210 minut','58');

INSERT INTO cennik_procedur VALUES('15P','Beauty Vital','230 minut','64');

INSERT INTO cennik_procedur VALUES('16P','Romantik vikend','250 minut','106');

INSERT INTO cennik_procedur VALUES('17P','Anti-age vikend','200 minut','96');

INSERT INTO cennik_procedur VALUES('18P','Vodny svet','60 minut','10');

INSERT INTO cennik_procedur VALUES('19','Saunovy svet','60 minut','15');

 

CREATE TABLE rezervacie_procedur

(id_rezervacie VARCHAR2(4) NOT NULL,

id_procedury VARCHAR2(3) NOT NULL,

id_klienta VARCHAR2(3) NOT NULL,

tel_cislo VARCHAR2(20) NOT NULL,

rezervacie_od DATE,

rezervacie_do DATE,

PRIMARY KEY (id_rezervacie),

FOREIGN KEY (id_procedury) REFERENCES cennik_procedur (id_procedury),

FOREIGN KEY (id_klienta) REFERENCES klient (id_klienta));

 

INSERT INTO rezervacie_procedur

VALUES('1RP','6P','1K','057/776 41 73','26.10.2009','30.10.2009');

INSERT INTO rezervacie_procedur

VALUES('2RP','11P','2K','032/771 74 02','13.09.2010','17.09.2010');

INSERT INTO rezervacie_procedur

VALUES('3RP','17P','3K','038/749 32 24','27.12.09','03.01.2010');

INSERT INTO rezervacie_procedur

VALUES('4RP','5P','4K','041/421 42 73','21.06.2010','27.06.2010');

INSERT INTO rezervacie_procedur

VALUES('5RP','2P','5K','043/428 66 74','26.08.2010','29.08.2010');

INSERT INTO rezervacie_procedur

VALUES('6RP','1P','6K','052/442 21 00','20.11.2009','23.11.2010');

INSERT INTO rezervacie_procedur

VALUES('7RP','14P','7K','056/679 73 43','20.01.2010','24.01.2010');

INSERT INTO rezervacie_procedur

VALUES('8RP','3P','8K','041/549 80 75','17.05.2010','21.05.2010');

INSERT INTO rezervacie_procedur

VALUES('9RP','8P','9K','043/422 02 28','11.06.2010','13.06.2010');

INSERT INTO rezervacie_procedur

VALUES('10RP','12P','10K','041/763 75 25','07.12.2009','10.12.2010');

INSERT INTO rezervacie_procedur

VALUES('11RP','9P','11K','012 110 50','26.06.2010','05.07.2010');

INSERT INTO rezervacie_procedur

VALUES('12RP','9P','12K','0304 172 400','30.07.2010','07.08.2010');

INSERT INTO rezervacie_procedur

VALUES('13RP','8P','13K','599 099 717','26.08.2010','29.08.2010');

INSERT INTO rezervacie_procedur

VALUES('14RP','14P','14K','423 25 10','20.09.2010','25.09.2010');

INSERT INTO rezervacie_procedur

VALUES('15RP','10P','15K','041/549 33 91','23.11.2009','30.11.2009');

INSERT INTO rezervacie_procedur

VALUES('16RP','2P','1K','057/776 41 73','28.10.2009','30.10.2009');

INSERT INTO rezervacie_procedur

VALUES('17RP','9P','3K','038/749 32 24','28.12.09','30.12.2009');

INSERT INTO rezervacie_procedur

VALUES('18RP','19P','3K','038/749 32 24','31.12.09','01.01.2010');

INSERT INTO rezervacie_procedur

VALUES('19RP','5P','3K','038/749 32 24','02.01.09','03.01.2010');

INSERT INTO rezervacie_procedur

VALUES('20RP','9P','4K','041/421 42 73','23.06.2010','25.06.2010');

INSERT INTO rezervacie_procedur

VALUES('21RP','19P','4K','041/421 42 73','26.06.2010','27.06.2010');

INSERT INTO rezervacie_procedur

VALUES('22RP','12P','7K','056/679 73 43','22.01.2010','24.01.2010');

INSERT INTO rezervacie_procedur

VALUES('23RP','10P','8K','041/549 80 75','19.05.2010','21.05.2010');

INSERT INTO rezervacie_procedur

VALUES('24RP','18P','11K','012 110 50','27.06.2010','28.06.2010');

INSERT INTO rezervacie_procedur

VALUES('25RP','15P','11K','012 110 50','29.06.2010','01.07.2010');

INSERT INTO rezervacie_procedur

VALUES('26RP','10P','11K','012 110 50','02.07.2010','03.07.2010');

INSERT INTO rezervacie_procedur

VALUES('27RP','7P','11K','012 110 50','04.07.2010','05.07.2010');

INSERT INTO rezervacie_procedur

VALUES('28RP','4P','11K','012 110 50','04.07.2010','05.07.2010');

INSERT INTO rezervacie_procedur

VALUES('29RP','4P','12K','0304 172 400','31.07.2010','02.08.2010');

INSERT INTO rezervacie_procedur

VALUES('30RP','19P','12K','0304 172 400','03.08.2010','05.08.2010');

INSERT INTO rezervacie_procedur

VALUES('31RP','15P','12K','0304 172 400','06.08.2010','07.08.2010');

INSERT INTO rezervacie_procedur

VALUES('32RP','4P','14K','423 25 10','22.09.2010','23.09.2010');

INSERT INTO rezervacie_procedur

VALUES('33RP','18P','14K','423 25 10','24.09.2010','25.09.2010');

INSERT INTO rezervacie_procedur

VALUES('34RP','18P','15K','041/549 33 91','25.11.2009','27.11.2009');

INSERT INTO rezervacie_procedur

VALUES('35RP','1P','15K','041/549 33 91','28.11.2009','29.11.2009');

INSERT INTO rezervacie_procedur

VALUES('36RP','5P','15K','041/549 33 91','29.11.2009','30.11.2009');

 

CREATE TABLE rezervacie_ubytovania

(id_rezervacie VARCHAR2(4) NOT NULL,

id_zariadenia VARCHAR2(4) NOT NULL,

id_klienta VARCHAR2(3) NOT NULL,

tel_cislo VARCHAR2(20) NOT NULL,

rezervacie_od DATE,

rezervacie_do DATE,

pocet_noci_spolu NUMBER(2),

PRIMARY KEY (id_rezervacie),

FOREIGN KEY (id_zariadenia) REFERENCES ubytovanie (id_zariadenia),

FOREIGN KEY (id_klienta) REFERENCES klient (id_klienta));

 

INSERT INTO rezervacie_ubytovania

VALUES('1RU','25U','1K','057/776 41 73','26.10.2009','30.10.2009','4');

INSERT INTO rezervacie_ubytovania

VALUES('2RU','44U','2K','032/771 74 02','13.09.2010','17.09.2010','4');

INSERT INTO rezervacie_ubytovania

VALUES('3RU','7U','3K','038/749 32 24','27.12.09','03.01.2010','7');

INSERT INTO rezervacie_ubytovania

VALUES('4RU','8U','4K','041/421 42 73','21.06.2010','27.06.2010','6');

INSERT INTO rezervacie_ubytovania

VALUES('5RU','28U','5K','043/428 66 74','26.08.2010','29.08.2010','3');

INSERT INTO rezervacie_ubytovania

VALUES('6RU','34U','6K','052/442 21 00','20.11.2009','23.11.2010','3');

INSERT INTO rezervacie_ubytovania

VALUES('7RU','45U','7K','056/679 73 43','20.01.2010','24.01.2010','4');

INSERT INTO rezervacie_ubytovania

VALUES('8RU','52U','8K','041/549 80 75','17.05.2010','21.05.2010','4');

INSERT INTO rezervacie_ubytovania

VALUES('9RU','35U','9K','043/422 02 28','11.06.2010','13.06.2010','2');

INSERT INTO rezervacie_ubytovania

VALUES('10RU','2U','10K','041/763 75 25','07.12.2010','10.12.2010','3');

INSERT INTO rezervacie_ubytovania

VALUES('11RU','9U','11K','012 110 50','26.06.2010','05.07.2010','10');

INSERT INTO rezervacie_ubytovania

VALUES('12RU','16U','12K','0304 172 400','30.07.2010','07.08.2010','8');

INSERT INTO rezervacie_ubytovania

VALUES('13RU','4U','13K','599 099 717','26.08.2010','29.08.2010','3');

INSERT INTO rezervacie_ubytovania

VALUES('14RU','15U','14K','423 25 10','20.09.2010','25.09.2010','5');

INSERT INTO rezervacie_ubytovania

VALUES('15RU','37U','15K','041/549 33 91','23.10.2009','30.11.2009','7');