Projekt Ticket Booking Tento projekt je zamerany na vytvorenie databazy, pre letiska, letecke spolocnosti a ine spolocnosti, ktore poskytuju predaj a rezervacie leteniek. Databaza bude sluzit firme na spravu leteniek, letov, lietadiel, stalych zakaznikov, poskytovanych sluzieb, upravu cien. - vypis letov podla spolocnosti - vypis letov podla lietadiel pouzitych na dane lety - vypis letov, ktorymi letel vybrany zakaznik - vypis letov uskutocnenych resp. naplanovanych v urcitom casovom obdobi - prepocet leteniek podla triedy cestovania - vypis, ktore su kombinaciou predoslych - vypis letov smerovanych na urcite letisko resp. z urciteho letiska - vypis zakaznika, ktory letel najviackrat - vypis podla vyuzivanosti sluzieb - plosna uprava cien letov Databaza poskytuje prehlad o letoch t.j. kedy boli alebo budu uskutocnene akym lietadlom a pod., letovych spolocnostiach, letiskach, lietadlach, zakaznikoch, sluzbach. Ale i o sluzbach, ktore vyuzivaju zakaznici rovnako i o letenkach. Entitno-relacný model: Pri tvorbe projektu sú použité nasledovné atribúty: airport_id - ide letiska airport - nazov letiska city - mesto state - stat class_id - kod letovej triedy class - nazov letovej triedy koef - koeficient nasobenia ceny podla druhu letovej triedy company_id - kod spolocnosti name - nazov spolocnosti residence - rezidencia spolocnsoti count_plane - pocet lietadlej spolocnosti cstr_id - kod zakaznika name - meno zakaznika surname - priezvisko zakaznika home - domaca stanica dis_card - typ vernostnej karty flight_id - kod letu flight_type - typ letu (pravidelny, nepravidelny) price - zakladna cena letenky capacity - kapacita letu time_d - cas priletu date_d - datum odletu time_a - cas priletu date_a - datum priletu ticket_id - kod letenky plane_id - kod lietadla name - nazov lietadla type - typ lietadla capacity - kapacita lietadla Atribúty boli zoskupené do entít a boli vytvorené vlastné a cudzie klúce FLIGHTS (FLIGHT_ID,COMPANY_ID,FLIGHT_TYPE,PRICE,PLANE_ID,AIRPORT_D_ID,AIRPORT_A_ID,CAPACITY,TIME_D,DATE_D,TIME_A,DATE_A) AIRPORTS (AIRPORT_ID,AIRPORT,CITY,STATE) PLANES (PLANE_ID,NAME,TYPE,CAPACITY) CUSTOMERS (CSTR_ID,NAME,SURNAME,HOME,CLASS,DIS_CARD,SERVICE_ID primary key (CSTR_Id)) TICKET_BOOKING (TICKET_ID,COMPANY_ID,CSTR_ID, FLIGHT_ID,CLASS_ID) SERVICES (SERVICE_ID,NAME,COMPANY,HOME, TYPE_SERVICE) COMPANIES (COMPANY_ID,NAME,RESIDENCE,COUNT_PLANE) CLASS (CLASS_ID,CLASS,KOEF) CREATE TABLE FLIGHTS (FLIGHT_ID varchar(6) not null, COMPANY_ID varchar(10), FLIGHT_TYPE varchar(10), PRICE number(5), PLANE_ID varchar(5), AIRPORT_D_ID varchar(4) not null, AIRPORT_A_ID varchar(4) not null, CAPACITY number(3) not null, TIME_D varchar(5), DATE_D date, TIME_A varchar(5), DATE_A date, primary key (FLIGHT_ID), FOREIGN KEY (COMPANY_ID)REFERENCES COMPANIES(COMPANY_ID), FOREIGN KEY (PLANE_ID)REFERENCES PLANES(PLANE_ID), FOREIGN KEY (AIRPORT_D_ID)REFERENCES AIRPORTS(AIRPORT_ID), FOREIGN KEY (AIRPORT_A_ID)REFERENCES AIRPORTS(AIRPORT_ID)); VYTVORENIE TABULIEK: CREATE TABLE AIRPORTS (AIRPORT_ID VARCHAR(4) not null, AIRPORT VARCHAR(15), CITY VARCHAR(15), STATE VARCHAR(10), primary key (AIRPORT_ID)); CREATE TABLE PLANES (PLANE_ID VARCHAR(5), NAME VARCHAR(10), TYPE VARCHAR(13), CAPACITY NUMBER(3), primary key (PLANE_ID)); CREATE TABLE CUSTOMERS (CSTR_ID VARCHAR(7), NAME VARCHAR(15), SURNAME VARCHAR(10), HOME VARCHAR(20), DIS_CARD VARCHAR(8), SERVICE_ID NUMBER(1), primary key (CSTR_ID), FOREIGN KEY (SERVICE_ID)REFERENCES SERVICES(SERVICE_ID)); CREATE TABLE TICKET_BOOKING (TICKET_ID VARCHAR(6), COMPANY_ID VARCHAR(10), CSTR_ID VARCHAR(7), FLIGHT_ID VARCHAR(6) NOT NULL, CLASS_ID NUMBER(1) NOT NULL, primary key (TICKET_ID), FOREIGN KEY (COMPANY_ID)REFERENCES COMPANIES(COMPANY_ID), FOREIGN KEY (FLIGHT_ID)REFERENCES FLIGHTS(FLIGHT_ID), FOREIGN KEY (CSTR_ID)REFERENCES CUSTOMERS(CSTR_ID), FOREIGN KEY (CLASS_ID)REFERENCES CLASS(CLASS_ID)); CREATE TABLE COMPANIES (COMPANY_ID VARCHAR(10), NAME VARCHAR(10), RESIDENCE VARCHAR(10), COUNT_PLANE NUMBER(3), primary key (COMPANY_ID)); CREATE TABLE SERVICES (SERVICE_ID NUMBER(1), NAME VARCHAR(10), TYPE_SERVICE VARCHAR(10), PRICE NUMBER(5), primary key (SERVICE_ID)); CREATE TABLE CLASS ( CLASS_ID NUMBER(1) NOT NULL, CLASS VARCHAR(10), KOEF NUMBER(4), primary key (class_id)); NAPLNENIE DATAMI: INSERT INTO AIRPORTS VALUES ('BTS','M.R. STEFANIKA','BRATISLAVA','SVK'); INSERT INTO AIRPORTS VALUES ('PRG','RUZYNE','PRAHA','CZE'); INSERT INTO AIRPORTS VALUES ('LHT','HEATHROW','LONDON','GBR'); INSERT INTO AIRPORTS VALUES ('BOS','LOGAN AIRPORT','BOSTON','USA/MA'); INSERT INTO AIRPORTS VALUES ('NYC','JFK','NEW YORK CITY','USA/NY'); INSERT INTO AIRPORTS VALUES ('BRN','LETISKO BRNO','BRNO','CZE'); AIRP AIRPORT CITY STATE ---- --------------- --------------- ---------- BTS M.R. STEFANIKA BRATISLAVA SVK PRG RUZYNE PRAHA CZE LHT HEATHROW LONDON GBR BOS LOGAN AIRPORT BOSTON USA/MA NYC JFK NEW YORK CITY USA/NY BRN LETISKO BRNO BRNO CZE INSERT INTO PLANES VALUES ('737A3','BOEING','737-A300','120'); INSERT INTO PLANES VALUES ('450AB','AIRBUS','450','335'); INSERT INTO PLANES VALUES ('C700E','CONCORDE','7OOE','483'); INSERT INTO PLANES VALUES ('AN024','ANTONOV','AN24','113'); PLANE NAME TYPE CAPACITY ----- ---------- ------------- ---------- 737A3 BOEING 737-A300 120 450AB AIRBUS 450 335 C700E CONCORDE 7OOE 483 AN024 ANTONOV AN24 113 INSERT INTO COMPANIES VALUES ('0001','SKYEUROPE','SLOVAKIA',10); INSERT INTO COMPANIES VALUES ('0002','CSA','CZECH REP.',25); INSERT INTO COMPANIES VALUES ('0003','DELTA','USA',125); INSERT INTO COMPANIES VALUES ('0004','ROYAL','UK',72); INSERT INTO COMPANIES VALUES ('0005','LUFT-HANSA','GER',132); INSERT INTO COMPANIES VALUES ('0006','AIR-FRANCE','FRA',104); COMPANY_ID NAME RESIDENCE COUNT_PLANE ---------- ---------- ---------- ----------- 0001 SKYEUROPE SLOVAKIA 10 0002 CSA CZECH REP. 25 0003 DELTA USA 125 0004 ROYAL UK 72 0005 LUFT-HANSA GER 132 0006 AIR-FRANCE FRA 104 INSERT INTO SERVICES VALUES ('0','NONE','NONE',0); INSERT INTO SERVICES VALUES ('1','WELCOME','TAXI/HOTEL',2500); INSERT INTO SERVICES VALUES ('2','TRANSIT','TAXI',500); INSERT INTO SERVICES VALUES ('3','SPECIAL','ALL LUXURY',12000); INSERT INTO SERVICES VALUES ('4','EXTRA-T','LIMOUSINE',5500); SERVICE_ID NAME TYPE_SERVI PRICE ---------- ---------- ---------- ---------- 0 NONE NONE 0 1 WELCOME TAXI/HOTEL 2500 2 TRANSIT TAXI 500 3 SPECIAL ALL LUXURY 12000 4 EXTRA-T LIMOUSINE 5500 INSERT INTO CLASS VALUES (0,'ECONOMIC', 1); INSERT INTO CLASS VALUES (1,'BUSINESS', 2); CLASS_ID CLASS KOEF ---------- ---------- ---------- 0 ECONOMIC 1 1 BUSINESS 2 INSERT INTO CUSTOMERS VALUES ('01001','IVAN','CHOVANEC','SLOVAKIA/BTS','SKYE5%','1'); INSERT INTO CUSTOMERS VALUES ('02001','JAN','NOVAK','CZECH-REP/PRG','CSA','2'); INSERT INTO CUSTOMERS VALUES ('02002','PETR','DOLEZAL','CZECH-REP/BRN',' ','0'); INSERT INTO CUSTOMERS VALUES ('03001','JOHN','WALLER','GREAT BRITAIN/LHT','ROYAL','2'); INSERT INTO CUSTOMERS VALUES ('04001','MARK','TELLER','USA/MA/BOS','DELTA15%','3'); CSTR_ID NAME SURNAME HOME DIS_CARD SERVICE_ID ------- --------------- ---------- -------------------- -------- ---------- 01001 IVAN CHOVANEC SLOVAKIA/BTS SKYE5% 1 02001 JAN NOVAK CZECH-REP/PRG CSA 2 02002 PETR DOLEZAL CZECH-REP/BRN 0 03001 JOHN WALLER GREAT BRITAIN/LHT ROYAL 2 04001 MARK TELLER USA/MA/BOS DELTA15% 3 INSERT INTO FLIGHTS VALUES ('BR01PR','0001','REGULAR','3700','737A3','BTS','PRG','87','11:15','12/12/06','12:25','12/12/06'); INSERT INTO FLIGHTS VALUES ('PR01BR','0002','REGULAR','3550','737A3','PRG','BTS','87','14:35','12/12/06','15:45','12/12/06'); INSERT INTO FLIGHTS VALUES ('BR01LN','0001','REGULAR','5340','AN024','BTS','LHT','113','8:20','19/12/06','12:00','19/12/06'); INSERT INTO FLIGHTS VALUES ('BR02PR','0001','REGULAR','3700','737A3','BTS','PRG','87','11:15','5/1/07','12:25','5/1/07'); INSERT INTO FLIGHTS VALUES ('BR01NY','0003','UNREGULAR','23700','C700E','BTS','NYC','483','17:15','10/1/07','7:25','11/1/07'); INSERT INTO FLIGHTS VALUES ('NY01BR','0003','UNREGULAR','25700','C700E','NYC','BTS','483','11:00','13/1/07','23:25','13/1/07'); FLIGHT COMPANY_ID FLIGHT_TYP PRICE PLANE AIRP AIRP CAPACITY TIME_ DATE_D TIME_ DATE_A ------ ---------- ---------- ---------- ----- ---- ---- ---------- ----- -------- ----- -------- BR01PR 0001 REGULAR 3700 737A3 BTS PRG 87 11:15 12.12.06 12:25 12.12.06 PR01BR 0002 REGULAR 3550 737A3 PRG BTS 87 14:35 12.12.06 15:45 12.12.06 BR01LN 0001 REGULAR 5340 AN024 BTS LHT 113 8:20 19.12.06 12:00 19.12.06 BR02PR 0001 REGULAR 3700 737A3 BTS PRG 87 11:15 05.01.07 12:25 05.01.07 BR01NY 0003 UNREGULAR 23700 C700E BTS NYC 483 17:15 10.01.07 7:25 11.01.07 NY01BR 0003 UNREGULAR 25700 C700E NYC BTS 483 11:00 13.01.07 23:25 13.01.07 INSERT INTO TICKET_BOOKING VALUES ('000001','0001','01001','BR01PR','0'); INSERT INTO TICKET_BOOKING VALUES ('000002','0001','02002','BR01PR','0'); INSERT INTO TICKET_BOOKING VALUES ('000003','0001','03001','BR01PR','1'); INSERT INTO TICKET_BOOKING VALUES ('000004','0003','02001','NY01BR','1'); INSERT INTO TICKET_BOOKING VALUES ('000005','0003','01001','NY01BR','1'); INSERT INTO TICKET_BOOKING VALUES ('000006','0002','01001','PR01BR','1'); INSERT INTO TICKET_BOOKING VALUES ('000007','0003','04001','BR01NY','1'); TICKET COMPANY_ID CSTR_ID FLIGHT CLASS_ID ------ ---------- ------- ------ ---------- 000001 0001 01001 BR01PR 0 000002 0003 02002 BR01PR 0 000003 0003 03001 BR01PR 1 000004 0003 02001 NY01BR 1 000005 0003 01001 NY01BR 1 000006 0002 01001 PR01BR 1 000007 0003 04001 BR01NY 1 1. VYPIS VSETKY LETY SPOLOCNOSTI SKYEUROPE SELECT F.FLIGHT_ID, F.COMPANY_ID, F.FLIGHT_TYPE, CO.NAME, FROM FLIGHTS F, COMPANIES CO WHERE (F.COMPANY_ID=CO.COMPANY_ID) AND CO.COMPANY_ID='0001'; 2. VYPIS VSETKY LETY VSETKYCH SPOLOCNOSTI LIETADLOM BOEING 737 SELECT F.FLIGHT_ID, CO.NAME, F.AIRPORT_D_ID, F.AIRPORT_A_ID, P.NAME FROM FLIGHTS F, COMPANIES CO, PLANES P WHERE F.COMPANY_ID=CO.COMPANY_ID AND F.PLANE_ID=P.PLANE_ID AND F.PLANE_ID='737A3'; 3. VYPIS VSETKY LETY KTORYM LETEL KLIENT CISLO 02002 SPOLOCNOSTOU DELTA SELECT T.TICKET_ID, T.FLIGHT_ID, CU.NAME, CU.SURNAME, CO.NAME, F.AIRPORT_D_ID, F.AIRPORT_A_ID, P.NAME FROM TICKET_BOOKING T, CUSTOMERS CU, COMPANIES CO, PLANES P, FLIGHTS F WHERE T.FLIGHT_ID=F.FLIGHT_ID AND T.CSTR_ID=CU.CSTR_ID AND T.COMPANY_ID=CO.COMPANY_ID AND F.PLANE_ID=P.PLANE_ID AND T.CSTR_ID='02002' AND CO.COMPANY_ID='0003'; 4. VYPIS VSETKY LETY KTORE BOLI USKUTOCNENE V DECEMBRI SELECT * FROM FLIGHTS F WHERE F.DATE_D>'1.12.2006' AND F.DATE_D<'31.12.2006'; 5. PREPOCITAJ CENY VSETKYCH LETENIEK SELECT T.TICKET_ID, T.FLIGHT_ID, T.CSTR_ID, T.CLASS_ID,(C.KOEF*F.PRICE) AS PRICE FROM TICKET_BOOKING T, FLIGHTS F, CLASS C WHERE T.FLIGHT_ID=F.FLIGHT_ID AND T.CLASS_ID=C.CLASS_ID; 6. VYPIS POCET LETOV USKUTOCNENYCH POCAS NOVEMBRA SPOLOCNOSTOU CSA A LIETADLOM AIRBUS SELECT COUNT(F.FLIGHT_ID) AS COUNT_FLIGHTS FROM FLIGHTS F WHERE F.COMPANY_ID='0002' AND F.PLANE_ID='737A3'; 7. VYPIS VSETKY LETY NEPRAVIDELNE NAPLANOVANE NA BUDUCI TYZDEN SELECT F.FLIGHT_ID, CO.NAME, F.FLIGHT_TYPE, F.PRICE, F.DATE_D FROM FLIGHTS F, COMPANIES CO WHERE F.COMPANY_ID=CO.COMPANY_ID AND F.DATE_D>(SYSDATE) AND F.DATE_D'PRG' AND F.AIRPORT_A_ID='BTS' ORDER BY F.DATE_D; 10.VYHLADAJ CESTUJUCEHO KTORY NAJVIACKRAT LETEL CREATE VIEW A AS SELECT CU.NAME, CU.SURNAME, COUNT(T.CSTR_ID) AS COUNT_FLIGHTS FROM CUSTOMERS CU, TICKET_BOOKING T WHERE T.CSTR_ID=CU.CSTR_ID GROUP BY CU.NAME, CU.SURNAME; SELECT NAME, SURNAME, COUNT_FLIGHTS FROM A WHERE COUNT_FLIGHTS=(SELECT MAX(COUNT_FLIGHTS) FROM A); 11. URCI PORADIE NAJCASTEJSIE VYUZIVANYCH SLUZIEB SELECT S.NAME, S.TYPE_SERVICE, COUNT(CU.SERVICE_ID) AS COUNT_SERVICES FROM CUSTOMERS CU, SERVICES S WHERE CU.SERVICE_ID=S.SERVICE_ID GROUP BY S.NAME, S.TYPE_SERVICE ORDER BY COUNT_SERVICES DESC; 12. ZORAD SPOLOCNOSTI PODLA TRZIEB ZA LETENKY SELECT CO.NAME, SUM(F.PRICE*C.KOEF) AS REVENUES FROM TICKET_BOOKING T, FLIGHTS F, COMPANIES CO, CLASS C, CUSTOMERS CU WHERE T.FLIGHT_ID=F.FLIGHT_ID AND T.COMPANY_ID=CO.COMPANY_ID AND T.CSTR_ID=CU.CSTR_ID AND T.CLASS_ID=C.CLASS_ID GROUP BY CO.NAME ORDER BY REVENUES DESC; 13. ZORAD SPOLOCNOSTI PODLA POCTU PREPRAVENYCH LUDI SELECT CO.NAME, COUNT(T.COMPANY_ID) AS COUNT_PASSENGER FROM COMPANIES CO, TICKET_BOOKING T WHERE T.COMPANY_ID=CO.COMPANY_ID GROUP BY CO.NAME ORDER BY COUNT_PASSENGER DESC; 14. PLOSNE ZVYSENIE CIEN LETOV O 20% create table NEW_PRICE_SERVICES (SERVICE_ID NUMBER(6), PRICE number(5), NEW_PRICE number(6)); declare x NUMBER := 0; y NUMBER :=0; c NUMBER :=0; begin loop x:= x+1; SELECT PRICE into y from SERVICES where SERVICE_ID=x; c:= 1.2*y; insert into NEW_PRICE_SERVICES values (x,y,c); exit when x=4; end loop; end; . run; OVERENIE FUNKCIE DATABAZY FUNKCIA DATABAZY BOLA OVERENA NA ZAKLADNYCH DOTAZOCH, KTORE SU SUCASTOU TOHOTO PROJEKTU ZAVER VYUZITIE PROJEKTU A JEHO DOPLNENIE: SYSTEM BY MAL POMOCT PRI SPRAVE LETOV, REGISTROVANIA LETENIEK CESTUJUCICH A CELEJ AGENDE SUVISIACEJ S ORGAZNICACIOU LETOV, TEDA ZABEZPECENIE LIETADLA, ZADANIE LETU, CAS ODLETU A PRILETU A VSETKY VECI SUVISIACE S DANOU PROBLEMATIKOU V RAMCI MOZNOSTI DANEHO PROJEKTU. NA ZLEPSENIE TOHTO PROJEKTU / SYSTEMU JE POTREBNE VYTVORIT PRISTUPOVE ROZHRANIA A UDELIT PRISTUPOVE PRAVA. EFEKTIVNE BY BOLO CELY SYSTEM PREPOJIT S ROZHRANIM NA BAZE ONLINE, KTORA BY VELMI NAPOMOHLA RYCHLEMU SPRACOVANIU UDAJOV PRE REGISTRACIU ZAKAZNIKOV A REZERVACIU LETENIEK. IVAN CHOVANEC ikovan@gmail.com icq: 252-837-378