117
FOREIGN KEY (ID_tipa, ID_strany)
CREATE TABLE Strana_proizvoditel (
ID_strany INTEGER AUTO INCREMENT NOT NULL,
Naimenovanie char(18) NOT NULL)
ALTER TABLE Strana_proizvoditel ADD PRIMARY KEY (ID_strany),
CREATE TABLE Tip_samoleta (
ID_Tipa INTEGER AUTO INCREMENT NOT NULL,
Naimenovanie char(18) NOT NULL)
ALTER TABLE Tip_samoleta ADD PRIMARY KEY (ID_Tipa),
CREATE TABLE class_pereleta (
ID_classa INTEGER AUTO INCREMENT NOT NULL,
Naimenovanie char(18) NOT NULL)
ALTER TABLE class_pereleta ADD PRIMARY KEY (ID_classa),
CREATE TABLE Bilety (
N_bileta char(20) NOT NULL,
Stoimost value NOT NULL,
N_mesta char(18) NOT NULL,
Vremya_vileta datetime NOT NULL,
Vremya_pribitiya datetime NOT NULL,
ID_classa integer(18) NOT NULL,
N_reysa char(18)NOT NULL,
ID_aviacompanii integer(4) NOT NULL)
ALTER TABLE Bilety ADD PRIMARY KEY (N_bileta),
FOREIGN KEY (ID_classa, N_reysa, ID_aviacompanii)
CREATE TABLE pol (
118
ID_pola INTEGER AUTO INCREMENT NOT NULL,
Naimenovanie char(7) NULL)
ALTER TABLE pol ADD PRIMARY KEY (ID_pola)
CREATE TABLE Passazhiri (
N_pasporta char(10) NOT NULL,
Familia char(30) NOT NULL,
Imya char(30) NOT NULL,
Otchestvo char(30),
ID_pola integer(18) NOT NULL,
N_bileta char(20) NOT NULL)
ALTER TABLE Passazhiri ADD PRIMARY KEY (N_pasporta),
FOREIGN KEY (ID_pola, N_bileta)
CREATE TABLE Punkt_naznachenia (
ID_goroda INTEGER AUTO INCREMENT NOT NULL,
Naimenovanie char(40) NOT NULL)
ALTER TABLE Punkt_naznachenia ADD PRIMARY KEY (ID_goroda)
CREATE TABLE Reysi (
N_reysa char(18) NOT NULL,
Vremya_vileta datetime NOT NULL,
Vremya_pribitiya datetime NOT NULL,
N_samoleta char(18) NOT NULL,
ID_goroda integer(18) NOT NULL)
ALTER TABLE Reysi ADD PRIMARY KEY (ID_reysa),
FOREIGN KEY (N_samoleta, ID_goroda)
CREATE TABLE Raspisanie (
ID_raspisaniya INTEGER AUTO INCREMENT NOT NULL,
119
Vremya_vileta datetime NOT NULL,
Vremya_pribitiya datetime NOT NULL,
N_vihoda integer(2) NOT NULL,
N_reysa char(18) NOT NULL)
ALTER TABLE Raspisanie ADD PRIMARY KEY (ID_raspisaniya),
FOREIGN KEY (N_reysa)
CREATE TABLE Aviacompaniya (
ID_aviacompanii INTEGER AUTO INCREMENT NOT NULL,
Naimenovanie char(18) NOT NULL)
ALTER TABLE Aviacompaniya ADD PRIMARY KEY (ID_aviacompanii)
SELECT FIO, pol, stazh_raboty, vozrast, kolichestvo_detey, fio_nachalnika,
zarplata, rabotniki.otdel
FROM rabotniki, otdely, dolzhnost
WHERE rabotniki.Otdel=Otdely.ID_otdela AND
rabotniki.Dolzhnost=Dolzhnost.ID;
SELECT Count(rabotniki.FIO) AS Kolichestvo_rabotnikov
FROM rabotniki;
SELECT Count(FIO_nachalnika) AS Kolichestvo_nachalnikov
FROM otdely;
SELECT fio, brigady.naimenovanie
FROM rabotniki, brigady
WHERE (rabotniki.brigada=brigady.Num_brigady);
SELECT fio, otdely.naimenovanie
FROM rabotniki, otdely, brigady
120
WHERE (rabotniki.brigada=otdely.Num_otdela=brigady.Num_brigady);
SELECT fio, vozrast
FROM rabotniki;
SELECT Count(fio,) as kolichestvo
FROM rabotniki, otdely, brigady
WHERE (rabotniki.brigada=otdely.Num_otdela=brigady.Num_brigady);
SELECT fio, naimenovanie, pol, vozrast, zarplata
FROM rabotniki, dolzhnost
WHERE (rabotniki.dolzhnost=dolzhnost.ID_dolzhnost) AND
(rabotniki.dolzhnost=1);
SELECT Count(fio,) as kolichestvo_pilotov
FROM rabotniki, dolzhnost
WHERE (rabotniki.dolzhnost=dolzhnost.ID_dolzhnost) AND
(rabotniki.dolzhnost=1);
SELECT bilety.Num_reysa, NazvanieGoroda,reysi.Vremya_v_puti
FROM bilety, reysi, punktnaznacheniya
WHERE (bilety.num_reysa=reysi.num_reysa) AND
(reysi.PunktNaznacheniya=PunktNaznacheniya.ID_PunktNaznacheniya);
SELECT Bilety.Num_reysa, Reysi.PunktNaznacheniya, Reysi.Vremya_v_puti
FROM Bilety, Reysi, Punktnaznacheniya
WHERE (Bilety.Num_reysa=Reysi.Num_reysa) AND
(Reysi.PunktNaznacheniya=Punktnaznacheniya.ID_Punktnaznacheniya) AND
(Reysi.PunktNaznacheniya= 11);
121
SELECT Count(Bilety.Num_reysa) AS Kolichestvo_reysov
FROM Bilety, Reysi, Punktnaznacheniya
WHERE (Bilety.Num_reysa=Reysi.Num_reysa) AND
(Reysi.PunktNaznacheniya=Punktnaznacheniya.ID_Punktnaznacheniya) AND
(Reysi.PunktNaznacheniya= 11);
SELECT Num_reysa, sostoyanie, punktnaznacheniya
FROM reysi
WHERE (punktnaznacheniya=1) AND (sostoyanie="Отменен");
SELECT Count(Num_reysa) AS Kolichestvo_reysov
FROM reysi
WHERE (punktnaznacheniya=1) AND (sostoyanie="Отменен");
SELECT Num_reysa, sostoyanie, punktnaznacheniya, prichina
FROM reysi
WHERE (sostoyanie="Задержан") and (prichina="Погода");
SELECT Count(Num_reysa) AS Kolichestvo_reysov
FROM reysi
WHERE (sostoyanie="Задержан") and (prichina="Погода");
SELECT reysi.Num_reysa, naimenovanie, Stoimost, Data_vileta
FROM reysi, tip_samoleta, bilety
WHERE (id_tipa_samoleta=tip_samoleta.ID) AND (id_tipa_samoleta=1);
SELECT Count(Num_reysa) AS Kolichestvo_reysov
FROM reysi, tip_samoleta, bilety
WHERE (id_tipa_samoleta=tip_samoleta.ID) AND (id_tipa_samoleta=1);
122
SELECT Reysi.Num_Reysa, reysi.Kategoriya, Punktnaznacheniya,
id_tipa_samoleta
FROM Reysi, Kategoriya_reysa, Punktnaznacheniya, Tip_samoleta
WHERE (reysi.kategoriya=Kategoriya_reysa.ID_kategorii) AND
(Punktnaznacheniya=Punktnaznacheniya.ID_Punktnaznacheniya) AND
(id_tipa_samoleta=Tip_samoleta.ID_tip) AND (Punktnaznacheniya=1);
SELECT count(Reysi.Num_Reysa) AS Kolichestvo_reysov
FROM Reysi, Kategoriya_reysa, Punktnaznacheniya, Tip_samoleta
WHERE (reysi.kategoriya=Kategoriya_reysa.ID_kategorii) AND
(Punktnaznacheniya=Punktnaznacheniya.ID_Punktnaznacheniya) AND
(id_tipa_samoleta=Tip_samoleta.ID_tip) AND (Punktnaznacheniya=1);
SELECT FamiliaPassazhira,passazhiri.ImyaPasszhira,OtchestvoPassazhira,
passazhiri.Num_bileta, passazhiri.pol, Reysi.Num_reysa, reysi.data_vileta
FROM passazhiri, Bilety, reysi
WHERE ( Reysi.Num_reysa=9) AND
(passazhiri.Num_bileta=Bilety.Num_bileta);
SELECT Count(FamiliaPassazhira) AS Kolichestvo_passazhirov
FROM passazhiri, Bilety, reysi
WHERE ( Reysi.Num_reysa=9) AND
(passazhiri.Num_bileta=Bilety.Num_bileta)
DELIMITER |
CREATE TRIGGER `delete_rabotniki` BEFORE DELETE ON `Rabotniki`
FOR EACH ROW BEGIN
INSERT INTO backup SET row_id = OLD.ID_Rabotnika,
Status = 'Delete',
FIO = OLD.FIO;
123
END
DELIMITER |
CREATE TRIGGER `new_rabotniki` AFTER INSERT ON `Rabotniki`
FOR EACH ROW BEGIN
INSERT INTO backup SET row_id = NEW.FIO,
Status = 'Insert',
FIO = NEW.FIO;
END
DELIMITER |
CREATE TRIGGER `update_ rabotniki ` BEFORE UPDATE ON ` Rabotniki `
FOR EACH ROW BEGIN
INSERT INTO backup SET row_id = OLD.FIO,
Status = 'Update',
FIO = OLD.FIO;
END
124
Приложение 2
Рисунок 29. Корректное создание и редакции рейса («Тип
самолёта»)
Рисунок 30. Корректное создание и редакции рейса («Категория
рейса»)
125
Рисунок 31. Корректное создание и редакции рейса («Пункт
назначения»)
126
Рисунок 32 – Окно регистрации