MySql Airline Database System Projesi


                                                              

EGE UNIVERSITY
COMPUTER ENGINEERING
DATABASE MANAGEMENT PROJECT 2017


PROJECT NAME:                AIRLINE DATABASE SYSTEM

PROJETC DEVELOPER:     BURAK ÖZÇELİK
 





                            PART-1


1) GİRİŞ

      Projemiz, havalimanlarında uçuş yapan uçakların ve seyahat eden yolcuların kayıtlarını ve bunlar arasındaki ilişkiyi tutan bir veritabanı tasarımıdır.




   VERİTABANI TASARIMINDAKİ TABLOLAR 


1) AIRPLANE: Uçak ID ve uçak bilgilerini(total_number_of_seats gibi) tutar.





2) AIRPLANE_TYPE: Uçak tipini ve maksimum koltuk sayısını tutar. AIRPLANE_COMPANY tablosuyla bağlantılıdır.





3) AIRPORT: Havalimanı bilgilerini tutar.





4) CAN_LAND: AIRPORT ile AIRPLANE_TYPE arasında bağ kuran tablodur.






5) FARE: Uçak bileti fiyatlarını tutar.







6) FLIGHT: Uçuşun hangi Airline'a ait olduğunu ve hangi günlerde tutar.  







7) FLIGHT_LEG: Planlanan uçuş bilgilerini tutar.







8) LEG_INSTANCE: Gerçekleşen uçuş bilgilerini tutar.








9) SEAT_RESARVATION: Yolcuların hangi uçuş için bilet aldıkları bilgisini tutar.

















2) INSERT, UPDATE, DELETE

    2.1) CUSTOMER TABLOSU

          2.1.1) INSERT

INSERT INTO `customer` (`name`, `phone_number`, `e_mail`, `address`, `counrty`, `passport_no`) 
VALUES
('Ziya Atma', '05001147555', 'ywannettatt-7909@yopmail.com', 'Datca/Mugla', 'Turkey', '612875948'),
('Nurzhan Abdulin', '05001215487', 'ecajedditt-3717@yopmail.com', 'Gediz/Kutahya', 'Turkey', '607081374'),
('Bolat Gubashev', '05001235687', 'ossihixis-8890@yopmail.com', 'Besni/ADIYAMAN', 'Turkey', '320442421'),
('Collin Soldiew', '05001247198', 'beqappone-1677@yopmail.com', 'Torbali/Izmir', 'Turkey', '330151149'),
('Lale Belkiz', '05001251436', 'mettydatab-4497@yopmail.com', 'Alanya/Antalya', 'Turkey', '697836386'),
('Mahmut Karavana', '05001321321', 'gihorakapp-5915@yopmail.com', 'Demirci/Manisa', 'Turkey', '209698805')


           2.1.2) UPDATE
UPDATE `customer` SET `address` = 'Suruç/Urfa' WHERE `customer`.`phone_number` = '05001326598';


           2.1.3) DELETE
DELETE FROM `customer` WHERE `customer`.`phone_number` = '05001147555'







    2.2) FARE TABLOSU

           2.2.1) INSERT

INSERT INTO `fare` (`Flight_number`, `Fare_Code`, `Amount`, `Restrictions`) VALUES ('BR5275', '105', '190', NULL);

           2.2.2) UPDATE

UPDATE `fare` SET `Restrictions` = 'Promosyon biletidir' WHERE `fare`.`Flight_number` = 'PC4766' AND `fare`.`Fare_Code` = 388;

           2.2.3) DELETE

DELETE FROM `fare` WHERE `fare`.`Flight_number` = \'BR5275\' AND `fare`.`Fare_Code` = 105



    2.3) SEAT RESARVATION

           2.3.1) INSERT

INSERT INTO `seat_resarvation` (`Flight_number`, `Leg_Number`, `Date`, `Seat_number`, `Customer_phone`, `isAttendant`) VALUES ('AD8051', '1', '2018-01-08', '12', '05001506599', '1');

           2.3.2) UPDATE

UPDATE `seat_resarvation` SET `Seat_number` = '21' WHERE `seat_resarvation`.`Flight_number` = 'AD8051' AND `seat_resarvation`.`Leg_Number` = '1' AND `seat_resarvation`.`Date` = '2018-01-08' AND `seat_resarvation`.`Seat_number` = 16

           2.3.3) DELETE

DELETE FROM seat_resarvation WHERE seat_resarvation.Flight_number = "AD8051" AND seat_resarvation.Leg_Number = 1 AND `seat_resarvation`.`Date` = '2018-01-08' AND `seat_resarvation`.`Seat_number` = 13
                                                                             









3) QUERIES
  

1) İzmir’e inen uçakların Flight numaraları;

SELECT DISTINCT leg_instance.Flight_number
FROM leg_instance
WHERE (leg_instance.Dept_Airport_code)
IN ( SELECT leg_instance.Dept_Airport_code
                               FROM leg_instance
                               WHERE leg_instance.Dept_Airport_code="ADB" )


  2) Aralık ayındaki aktarmalı uçuşlar;

         SELECT flight.Flight_number, flight.Airline
         FROM flight
         WHERE EXISTS (SELECT *
                                       FROM leg_instance
                                       WHERE flight.Flight_number = leg_instance.Flight_number
                        AND leg_instance.Date BETWEEN "2017-12-01 "     AND "2017-12-31")
                     AND EXISTS (SELECT *
                                             FROM flight_leg
                                             WHERE flight.Flight_number = flight_leg.Flight_number
                                             AND flight_leg.Leg_Number >1)


3) Aktarmasız yani direk uçan uçakların yolcuları;

SELECT seat_resarvation.Customer_name
FROM seat_resarvation
WHERE NOT EXISTS (SELECT *
                             FROM leg_instance
                             WHERE leg_instance.Flight_number=seat_resarvation.Flight_number
                             AND leg_instance.Leg_Number>1
                             GROUP BY seat_resarvation.Customer_name)


4) Üç ve üçten daha fazla uçan Airplane Type modelleri;

      SELECT airplane.Airplane_type
                              FROM airplane
                              WHERE (SELECT
                                             COUNT(*)
                                              FROM leg_instance
                                              WHERE leg_instance.Airplane_id=airplane.Airplane_id) >= 3


5) Planlı kalkış zamanından farklı bir zamanda kalkan uçuşlar;

SELECT f.Flight_number,l.Leg_Number , f.Scheduled_dept_time                         as Planli_Kalkis_zamani ,l.Dept_Time as Kalkis_zamani
FROM flight_leg AS f ,leg_instance AS l
WHERE (f.Scheduled_dept_time!=l.Dept_Time)
AND f.Flight_number=l.Flight_number
AND f.Leg_Number=l.Leg_Number


6) Havaalanlarından yapılan kalkış sayıları;

SELECT airport.Name as Havaalani,COUNT(leg_instance.Dept_Airport_code)     AS Kalkis_Sayisi
FROM airport , leg_instance
WHERE airport.Airport_code=leg_instance.Dept_Airport_code
GROUP BY airport.Airport_code


7) Muş’a inebilecek uçak modelleri;

                                   SELECT can_land.Airplane_Type_name
FROM airport,can_land
WHERE can_land.Airport_code=airport.Airport_code
 AND airport.Name="MUS"


8) Trabzon’a zamanında iniş yapmayan uçuşlar;

                                                SELECT f.Flight_number,l.Leg_Number ,
f.Scheduled_arr_time as Planli_Inis_zamani ,
l.Arr_Time as Inis_zamani ,
a.Name AS Havalimanı_ismi
FROM flight_leg AS f ,leg_instance AS l , airport AS a
WHERE (f.Scheduled_arr_time!=l.Arr_Time)
AND a.Name="TRABZON"
AND f.Flight_number=l.Flight_number
AND f.Leg_Number=l.Leg_Number
AND a.Airport_code=l.Arr_Airport_code


9) Uçak tiplerinin uçuşlarda kullanım sayıları;

SELECT (aty.Airplane_Type_name) as Ucak_tipi ,
COUNT(aty.Airplane_Type_name) AS Kullanilan_ucus_sayisi
FROM airplane_type as aty, airplane as a , leg_instance as l
WHERE l.Airplane_id=a.Airplane_id
AND a.Airplane_type=aty.Airplane_Type_name
GROUP BY a.Airplane_type


10) Aralık ayında Ankara’ya giden Anadolu Jet yolcuları;

   SELECT s.Customer_name AS Aralik_Ayinda_Ankaraya_Giden_AnadoluJET_Yolculari
   FROM seat_resarvation AS s , leg_instance as l , flight as f
   WHERE l.Arr_Airport_code="ESB"
   AND s.Date BETWEEN "2017-12-01"            AND "2017-12-31"
   AND f.Airline="ANADOLU JET"                      AND s.Flight_number=l.Flight_number
   AND s.Leg_Number=l.Leg_Number             AND s.Date=l.Date                                     
   AND l.Flight_number=f.Flight_number


11) İstanbul’a gerçekleştirilen toplam uçuş sayısı;

                                    SELECT COUNT(flight.Flight_number)
FROM airport ,leg_instance,flight
WHERE airport.City="ISTANBUL"
AND flight.Flight_number=leg_instance.Flight_number
AND airport.Airport_code=leg_instance.dept_Airport_code


12) Planlanan havaalanından farklı bir havaalanına inen uçuşlar;

                         SELECT f.Flight_number,l.Leg_Number ,
                                       b.Name ASInmesi_Planlanan_Havalaanı ,    
                                      a.Name AS Inisin_Gerceklestigi_Havaalanı
FROM flight_leg AS f ,leg_instance AS l , airport AS a , airport AS b
WHERE (f.Dept_airport_code!=l.Dept_Airport_code
OR f.Arr_airport_code!=l.Arr_Airport_code)
AND f.Flight_number=l.Flight_number
AND f.Leg_Number=l.Leg_Number
AND l.Arr_Airport_code=a.Airport_code
AND b.Airport_code=f.Arr_airport_code


13) Anadolu Jet’in Airbus’tan aldığı uçak sayısı;

SELECT COUNT(*) AS ANADOLU_JETin_AIRBUStan_aldigi_ucak_sayisi
FROM flight, leg_instance , airplane , airplane_type
WHERE flight.Airline="ANADOLU JET"
AND airplane_type.Company="AIRBUS"
AND flight.Flight_number=leg_instance.Flight_number
AND leg_instance.Airplane_id=airplane.Airplane_id
AND airplane.Airplane_type=airplane_type.Airplane_Type_name


14) Bora Jet’in 300’den fazla koltuğu olan uçaklarının uçak tipi ve toplam koltuk sayısı;

SELECT f.Airline, aty.Airplane_Type_name , a.Total_number_of_seats
FROM airplane as a ,airplane_type as aty ,leg_instance as l , flight as f
WHERE f.Airline="BORAJET"         AND a.Total_number_of_seats > 300
AND l.Airplane_id=a.Airplane_id      AND l.Flight_number=f.Flight_number
AND a.Airplane_type=aty.Airplane_Type_name


15) Bora Jet’in gerçekleşen uçuşlarda kullandığı uçakların uçak tipi ve uçak üreticisi;

        SELECT flight.Flight_number,leg_instance.Leg_Number,
         flight.Airline,airplane_type.Airplane_Type_name,airplane_type.Company
        FROM flight,leg_instance,airplane,airplane_type
        WHERE flight.Flight_number=leg_instance.Flight_number
        AND leg_instance.Airplane_id=airplane.Airplane_id
        AND airplane.Airplane_type= airplane_type.Airplane_Type_name
        AND flight.Airline="BORAJET"













4)EXIST, NOT EXIST QUERIES


1) 150 TL ve üzeri fiyata sahip Pegasus uçuşları;

                  SELECT fare.Flight_number
      FROM fare
      WHERE EXISTS (SELECT  *
                                                FROM  flight
                                                WHERE fare.Flight_number=flight.Flight_number
                                                 AND fare.Amount >= 150
                                                AND flight.Airline="PGS")


2) Gerçekleşmeyen uçuşların listesi;

                                   SELECT flight_leg.Flight_number AS Gerceklesmeyen_Ucus ,
             flight_leg.Dept_airport_code , flight_leg.Arr_airport_code
          FROM flight_leg
                                  WHERE NOT EXISTS (SELECT *
                                                                      FROM leg_instance
                                                      WHERE flight_leg.Flight_number=leg_instance.Flight_number)










5)OUTTER JOINS


1) LEFT OUTTER JOIN

     Planlanan ama gerçekleşmeyen uçuşlara NULL yazdırdık;

                               SELECT *
                               FROM flight_leg
                               LEFT JOIN leg_instance
                               ON flight_leg.Flight_number=leg_instance.Flight_number
                               AND flight_leg.Leg_Number=leg_instance.Leg_Number


2) RIGHT OUTTER JOIN

     Kullanılmayan Airplane Type modelinin NULL yazılması;

                      SELECT *
                      FROM airplane
                      RIGHT JOIN airplane_type
                      ON airplane_type.Airplane_Type_name=airplane.Airplane_type


3) FULL OUTTER JOIN
    
                                 (SELECT *
                                 FROM airplane
                                 LEFT JOIN can_land
           ON airplane.Airplane_type=can_land.Airplane_Type_name     
                                 AND can_land.Airport_code="MLX")
                    UNION
                                (SELECT *
                                 FROM airplane
                                 RIGHT JOIN can_land
                                 ON airplane.Airplane_type=can_land.Airplane_Type_name      
                                 AND can_land.Airport_code="MLX")









6) CREATE VIEWS

1)İzmir’e iniş yapan uçaklar;

CREATE VIEW IZMIR AS
 SELECT DISTINCT leg_instance.Flight_number
  FROM leg_instance
WHERE (leg_instance.Dept_Airport_code)
IN ( SELECT leg_instance.Dept_Airport_code
        FROM leg_instance
        WHERE leg_instance.Dept_Airport_code="ADB" )
       

            2) Aktarmasız uçuş yapan uçakların yolcuları;

 CREATE VIEW Direct AS
 SELECT seat_resarvation.Customer_name
 FROM seat_resarvation
 WHERE NOT EXISTS  (SELECT *
                                       FROM leg_instance
                             WHERE leg_instance.Flight_number=seat_resarvation.Flight_number
                                       AND leg_instance.Leg_Number>1
                                       GROUP BY seat_resarvation.Customer_name)


              3) Uçakların yaptığı uçuş sayıları;

CREATE VIEW ucakların_kullanıldığı_ucus_sayilari AS
SELECT (aty.Airplane_Type_name) AS Ucak_tipi,
COUNT(aty.Airplane_Type_name) AS Kullanilan_ucus_sayisi
FROM airplane_type as aty, airplane as a , leg_instance as l
WHERE l.Airplane_id=a.Airplane_id
AND a.Airplane_type=aty.Airplane_Type_name
GROUP BY a.Airplane_type











7) TRIGGERS


    1) Müsait koltuk sayısının, uçağın toplam koltuk sayısından fazla girilmemesini sağlar;

CREATE TRIGGER AS T1 BEFORE UPDATE
BEGIN
DECLARE deger INT;
SET deger =(SELECT airplane.Total_number_of_seats
                                               FROM airplane WHERE
                                               NEW.Airplane_id=airplane.Airplane_id);
IF
NEW.Number_of_avaliable_seats>deger THEN
SET NEW.Number_of_avaliable_seats=deger;
END IF;
END


        2) Her koltuk rezarvasyonunda, müsait koltuk sayısını eksilterek güncelleyen Trigger;

CREATE TRIGGER AS T2
BEFORE INSERT
UPDATE leg_instance
SET leg_instance.Number_of_avaliable_seats=(leg_instance.Number_of_avaliable_seats-1)
WHERE leg_instance.Flight_number=seat_resarvation.Flight_number AND leg_instance.Leg_Number=seat_resarvation.Leg_Number
AND leg_instance.Date=seat_resarvation.Date


3) 750 TL üzeri fiyat koyulmamasını kontrol eden Trigger;

DELIMITER //
CREATE TRIGGER fıyat BEFORE
UPDATE ON fare
FOR EACH ROW
BEGIN
DECLARE deger INT;
SET deger(SELECT fare.Amount
WHERE fare.Amount=750 );
IF new.Amount>deger THEN SET new.Amount=deger
END IF;
END;//
DELIMITER;









8) CHECK CONSTRAINT


   
     1) Dörtten fazla Leg’e izin vermez;

ALTER TABLE flight_leg ADD CONSTRAINT leg_limitation CHECK (Leg_Number<4)


     2) Ücretin negatif olmamasını sağlar;

ALTER TABLE fare ADD CONSTRAINT amountC CHECK (Amount>0)


     3) 99 koltuktan az uçakların sisteme kayıt olmasını engeller;

ALTER TABLE airplane_type ADD CONSTRAINT seatC CHECK (airplane_type.Max_seats>99)


 4) Rezerve edilen koltuk numarasının, toplam koltuk sayısını geçmemesini sağlar;

ALTER TABLE seat_resarvation
ADD CONSTRAINT Seat_no_constraint
CHECK       (NOT EXISTS
                                           (SELECT *
                                            FROM leg_instance AS l , airplane as a , seat_resarvation AS s
                                            WHERE s.Seat_number>a.Total_number_of_seats  
                                            AND l.Flight_number=s.Flight_number
                                            AND l.Leg_Number=s.Leg_Number
                                            AND l.Date=s.Date ))


















                                                         PART-2


1) GİRİŞ

      Projemiz, havalimanlarında uçuş yapan uçakların ve seyahat eden yolcuların kayıtlarını ve bunlar arasındaki ilişkiyi tutan ayrıntılı bir veritabanı tasarımıdır.



VERİTABANI TASARIMINDAKİ TABLOLAR 


1) AIRLINE_COMPANY: Havayolu şirketlerinin isimlerini, site ismini ve hangi ülkenin firması olduğu bilgisini tutar.








2) AIRPLANE: Uçak ID ve uçak bilgilerini(total_number_of_seats gibi) tutar.




3) AIRPLANE_COMPANY: Uçak tipi ve site ismi, hangi ülkeye ait olduğu bilgisini tutar.








4) AIRPLANE_TYPE: Uçak tipini ve maksimum koltuk sayısını tutar. AIRPLANE_COMPANY tablosuyla bağlantılıdır.




5) AIRPORT: Havalimanı bilgilerini tutar.



6) CAN_LAND: AIRPORT ile AIRPLANE_TYPE arasında bağ kuran tablodur.




7) CUSTOMER: Müşteri bilgilerini tutar.








8) FARE: Uçak bileti fiyatlarını tutar.




9) FFC: Yolcuların toplam aldığı yol mesafesini tutar. Müşteri tarafından seat resanvation tablosundaki uçuşa fiziksel olarak check in yapıldıysa (isAttendant=1), FFC tablosunun verileri güncellenir.




10) FLIGHT: Uçuşun hangi Airline'a ait olduğunu ve hangi günlerde tutar.  

11) FLIGHT_LEG: Planlanan uçuş bilgilerini tutar + Uçuşun mesafe bilgisini tutmak için Mileage attribute eklendi.

12) LEG_INSTANCE: Gerçekleşen uçuş bilgilerini tutar.

13)SEAT_RESARVATION: Yolcuların, uçuş yapıp yapmadıkları bilgisini(isAttendant) ve hangi uçuş için bilet aldıkları bilgisini tutar.




2) QUERIES


   1) İptal olan AD4874 2. Leg’inin yolcularının iletişim bilgileri;

                                   SELECT c.name, c.phone_number,  c.e_mail
FROM customer AS c, seat_resarvation AS s, leg_instance AS l
WHERE c.phone_number = s.Customer_phone
AND s.Flight_number = l.Flight_number
AND s.Leg_Number = l.Leg_Number        AND s.Date = l.Date
AND l.Leg_Number = 2                               AND l.Flight_number = "AD4874"
   

2) Uçuşa katılmayan yolcular;

                        SELECT customer.name
FROM customer , seat_resarvation
WHERE seat_resarvation.isAttendant=-1
AND seat_resarvation.Customer_phone=customer.phone_number
  

 3) Airbus uçaklarına sahip havayolu şirketleri;

SELECT DISTINCT  airline_company.name , airplane_company.name
FROM airplane_type , airplane , leg_instance , flight , airline_company ,
airplane_company
WHERE airplane_type.Airplane_Type_name=airplane.Airplane_type
AND airplane.Airplane_id=leg_instance.Airplane_id
AND leg_instance.Flight_number=flight.Flight_number
AND flight.Airline=airline_company.name
AND airplane_type.Company=airplane_company.name
AND airplane_type.Company="AIRBUS"


     4) THY’nin en uzun uçuş mesafesi;

SELECT MAX(flight_leg.mileage)
FROM flight ,flight_leg, airline_company
WHERE flight.Flight_number=flight_leg.Flight_number
AND flight.Airline=airline_company.name
AND airline_company.name="TÜRK HAVA YOLLARI"









3) TRIGGERS


    1) Müsait koltuk sayısının, uçağın toplam koltuk sayısından fazla girilmemesini sağlar;

CREATE TRIGGER AS T1 BEFORE UPDATE
BEGIN
DECLARE deger INT;
SET deger =(SELECT airplane.Total_number_of_seats
                                               FROM airplane WHERE
                                               NEW.Airplane_id=airplane.Airplane_id);
IF
NEW.Number_of_avaliable_seats>deger THEN
SET NEW.Number_of_avaliable_seats=deger;
END IF;
END


       

2) Her koltuk rezarvasyonunda, müsait koltuk sayısını eksilterek güncelleyen Trigger;

CREATE TRIGGER AS T2
BEFORE INSERT
UPDATE leg_instance
SET leg_instance.Number_of_avaliable_seats=  
        (leg_instance.Number_of_avaliable_seats-1)
WHERE leg_instance.Flight_number=seat_resarvation.Flight_number AND leg_instance.Leg_Number=seat_resarvation.Leg_Number
AND leg_instance.Date=seat_resarvation.Date



3) Planlanan uçuşu gerçekleştirmiş yolcuların; airline şirketine göre, toplam kat ettikleri yolu miL cinsinden tutmasını sağlayan Trigger;

CREATE DEFINER = CURRENT_USER TRIGGER `mydb`.`seat_resarvation_AFTER_INSERT`
AFTER INSERT
ON `seat_resarvation` FOR EACH ROW
BEGIN
select mill=ffc.total_mileage, airlinee=ffc.airline, phone=ffc.customer_phone
from ffc, seat_rezervation, customer, flight_leg, flight
where (seat_resarvation.Flight_number=flight_leg.Flight_number
and seat_resarvation.Flight_number=flight.Flight_number
and seat_resarvation.Customer_phone=customer.phone_number)
DECLARE mill float
DECLARE airlinee varchar(20)
DECLARE phone varchar(13)
IF (seat_resarvation.isAttendant=1) then
INSERT INTO ffc (mill, airlinee, phone) VALUES
(flight_leg.mileage, flight.Airline, customer.phone_number)
end IF
END

Yorumlar

Yorum Gönder

Bu blogdaki popüler yayınlar

XAMPP NEDİR? JOOMLA NEDİR? JOOMLA VE XAMPP TANITIM.

Resim
XAMPP NEDİR? JOOMLA NEDİR? XAMPP  ( Extended Apache/MariaDB/PHP/Perl )      XAMPP, bilgisayarınızı PHP, MariaDB, Perl, Apache ve FileZilla, MercuryMail gibi sistemler için server olarak kullanmanızı sağlar. XAMPP ile phpMyAdmin de kurulu olarak gelir. Eğer JOOMLA ile site tasarlıyorsanız veya bir projede çalışıyorsanız, öncelikle o projenin dosyalarını XAMPP’a aktarmanız gerekiyor: C:\xampp\htdocs   (Joomla için buraya.)   Site linki yukarıdaki proje dosyasının adından geliyor.       Joomla günümüz internet sitelerini tasarlayıp etkin ve güzel bir şekilde kamuoyunun kullanımına açabileceğiniz bir geliştiricir. Yeni sürümlerini https://github.com/joomla/joomla-cms sitesinden indirebilirsiniz. Açık kaynaklı bir tooldur.      Joomla ile kendi sitenizi PhpStorm’da tasarlayıp buraya yükleyebilirsiniz. Component, Plug in ve modül kodlarını öğrenebileceğiniz linkler ektedir;   - https://docs.joomla.org/J3.x:Developing_an_MVC_Comp
Devamı