RBLrgSHf

1-- Лабораторная работа №3, дисциплина "Базы данных", СПбГУАП, весенний семестр 2019
2
3-- Включение форсирования ограничений ссылочной целостности
4PRAGMA FOREIGN_KEYS=ON;
5DROP TABLE IF EXISTS Билет;
6DROP TABLE IF EXISTS Пассажир;
7DROP TABLE IF EXISTS Место;
8DROP TABLE IF EXISTS Вагон;
9DROP TABLE IF EXISTS ТипВагона;
10DROP TABLE IF EXISTS ПроездСтанций;
11DROP TABLE IF EXISTS Поезд;
12DROP TABLE IF EXISTS МаршрутПоРасписанию;
13DROP TABLE IF EXISTS Станция;
14DROP TABLE IF EXISTS Маршрут;
15
16CREATE TABLE Маршрут (
17НомерПоезда INTEGER PRIMARY KEY,
18Начальная VARCHAR(30) NOT NULL,
19Конечная VARCHAR(30) NOT NULL,
20CHECK (Начальная<>Конечная));
21
22CREATE TABLE МаршрутПоРасписанию (
23Время Time,
24НомерПоезда);
25
26CREATE TABLE Поезд (
27Дата Date,
28Время Time,
29НомерПоезда INTEGER);
30
31CREATE TABLE Вагон (
32НомерВагона INTEGER,
33НомерПоезда INTEGER,
34Дата Date,
35Время Time,
36НазваниеТипа VARCHAR(30));
37
38CREATE TABLE Станция (
39Название VARCHAR(30) PRIMARY KEY);
40
41CREATE TABLE ПроездСтанций (
42Название VARCHAR(30) REFERENCES Станция(Название),
43Время Time,
44НомерПоезда INTEGER,
45КолвоМинутОтСтарта INTEGER NOT NULL,
46КолвоМинутСтоянки INTEGER NOT NULL CHECK(КолвоМинутСтоянки >3),
47Дата Date);
48
49CREATE TABLE ТипВагона (
50НазваниеТипа VARCHAR(30) PRIMARY KEY,
51Коэффицент INTEGER NOT NULL,
52КолМест INTEGER NOT NULL);
53
54CREATE TABLE Место (
55НомерМеста INTEGER,
56НомерВагона INTEGER,
57Дата Date,
58Время Time,
59НомерПоезда INTEGER);
60
61CREATE TABLE Билет (
62НомерБилета INTEGER PRIMARY KEY,
63НомерМеста INTEGER,
64НомерВагона INTEGER,
65Дата Date ,
66Время Time,
67НомерПоезда INTEGER,
68Цена MONEY NOT NULL,
69НомерПаспорта INTEGER,
70Начальная VARCHAR(30),
71Конечная VARCHAR(30));
72
73CREATE TABLE Пассажир (
74НомерПаспорта INTEGER PRIMARY KEY,
75ФИО VARCHAR(30) NOT NULL,
76НомерБилета INTEGER NOT NULL);
77
78INSERT INTO Маршрут VALUES (100, 'Санкт-Петербург', 'Ростов');
79INSERT INTO Маршрут VALUES (120, 'Москва', 'Санкт-Петербург');
80INSERT INTO Маршрут VALUES (370, 'Санкт-Петербург', 'Астрахань');
81INSERT INTO Маршрут VALUES (414, 'Москва', 'Екатеринбург');
82INSERT INTO Маршрут VALUES (568, 'Санкт-Петербург', 'Оренбург');
83
84INSERT INTO МаршрутПоРасписанию VALUES ('9-00', 100);
85INSERT INTO МаршрутПоРасписанию VALUES ('12-00', 414);
86INSERT INTO МаршрутПоРасписанию VALUES ('15-30', 568);
87INSERT INTO МаршрутПоРасписанию VALUES ('18-00', 370);
88INSERT INTO МаршрутПоРасписанию VALUES ('21-45', 120);
89
90INSERT INTO Поезд VALUES ('2019-03-20','21-45', 120);
91INSERT INTO Поезд VALUES ('2019-04-01','12-00', 414);
92INSERT INTO Поезд VALUES ('2019-04-10','15-30', 568);
93INSERT INTO Поезд VALUES ('2019-05-03','18-00', 370);
94INSERT INTO Поезд VALUES ('2019-04-19','9-00', 100);
95INSERT INTO Поезд VALUES ('2019-04-20','9-00', 100);
96--INSERT INTO Поезд VALUES ('2019-04-21','9-00', 100);
97INSERT INTO Поезд VALUES ('2019-04-22','9-00', 100);
98
99INSERT INTO ТипВагона VALUES ('Купе', 4, 28);
100INSERT INTO ТипВагона VALUES ('Плацкарт', 2, 30);
101
102INSERT INTO Вагон VALUES (1, 414, '2019-04-01','12-00', 'Купе');
103INSERT INTO Вагон VALUES (2, 370, '2019-05-03','18-00', 'Плацкарт');
104INSERT INTO Вагон VALUES (3, 100, '2019-04-19','9-00', 'Плацкарт');
105INSERT INTO Вагон VALUES (5, 568, '2019-04-10','15-30', 'Купе');
106INSERT INTO Вагон VALUES (7, 120, '2019-03-20','21-45', 'Купе');
107
108INSERT INTO Станция(Название) VALUES ('Астрахань');
109INSERT INTO Станция(Название) VALUES ('Вологда');
110INSERT INTO Станция(Название) VALUES ('Екатеринбург');
111INSERT INTO Станция(Название) VALUES ('Москва');
112INSERT INTO Станция(Название) VALUES ('Калининград');
113INSERT INTO Станция(Название) VALUES ('Новгород');
114INSERT INTO Станция(Название) VALUES ('Оренбург');
115INSERT INTO Станция(Название) VALUES ('Ростов');
116INSERT INTO Станция(Название) VALUES ('Санкт-Петербург');
117INSERT INTO Станция(Название) VALUES ('Саратов');
118INSERT INTO Станция(Название) VALUES ('Томск');
119
120INSERT INTO ПроездСтанций VALUES ('Вологда', '15-30', 568, 420, 25,'2019-04-10'); 
121INSERT INTO ПроездСтанций VALUES ('Калининград', '18-00', 370, 1750, 19,'2019-05-03'); 
122INSERT INTO ПроездСтанций VALUES ('Новгород', '21-45', 120, 60, 15,'2019-03-20'); 
123INSERT INTO ПроездСтанций VALUES ('Новгород', '21-45', 120, 60, 15,'2019-03-21');
124INSERT INTO ПроездСтанций VALUES ('Саратов', '12-00', 414, 3600, 20,'2019-04-01'); 
125INSERT INTO ПроездСтанций VALUES ('Саратов', '12-00', 120, 3600, 20,'2019-04-02');
126INSERT INTO ПроездСтанций VALUES ('Томск', '9-00', 100, 1440, 35,'2019-04-19'); 
127INSERT INTO ПроездСтанций VALUES ('Томск', '10-00', 100, 1440, 35,'2019-04-21'); 
128
129INSERT INTO Место VALUES (6, 5, '2019-04-10','15-30',568);
130INSERT INTO Место VALUES (10, 2, '2019-05-03','18-00',370);
131INSERT INTO Место VALUES (12, 1, '2019-04-01','12-00',414);
132INSERT INTO Место VALUES (12, 7, '2019-03-20','21-45',120);
133INSERT INTO Место VALUES (20, 3, '2019-04-19','9-00', 100);
134
135INSERT INTO Пассажир VALUES (1245678, 'Ромнов Сергей Федорович', 9);
136INSERT INTO Пассажир VALUES (3156800, 'Петрова Кристина Михайловна', 56);
137INSERT INTO Пассажир VALUES (4115890, 'Некрасов Виталий Юрьевич', 312);
138INSERT INTO Пассажир VALUES (4325678, 'Лев Мирослава Андреевна', 560);
139INSERT INTO Пассажир VALUES (9876543, 'Емильянова Аксиния Дмитриевна', 150);
140
141INSERT INTO Билет VALUES (9, 12, 7, '2019-03-20','21-45', 120, 2500, 1245678, 'Москва', 'Санкт-Петербург');
142INSERT INTO Билет VALUES (56, 20, 3, '2019-04-19','9-00', 100, 1290, 3156800, 'Санкт-Петербург', 'Ростов');
143INSERT INTO Билет VALUES (312, 6, 5, '2019-04-10','15-30', 568, 1900, 9876543, 'Санкт-Петербург', 'Оренбург');
144INSERT INTO Билет VALUES (560, 12, 1, '2019-04-01','12-00', 414, 3500, 4115890, 'Москва', 'Екатеринбург');
145INSERT INTO Билет VALUES (150, 10, 2,'2019-05-03','18-00', 370, 1647, 4325678, 'Санкт-Петербург', 'Астрахань'); 
146
147
148-- Удалить станции, на которых останавливается всего один поезд, и то не каждый день
149--select *, НомерПоезда as нп
150--from ПроездСтанций
151--group by НомерПоезда
152--having date(Дата) not in ( select date(Дата,'-1 day')
153--                           from ПроездСтанций
154--                          where нп = НомерПоезда )
155
156delete from Станция
157where Название in ( select *, НомерПоезда as н_п
158                    from ПроездСтанций 
159                    group by Название
160                    having count(distinct НомерПоезда)=1 and date(Дата) not in ( select date(Дата,'-1 day')
161                                                                                 from ПроездСтанций
162                                                                                 where н_п = НомерПоезда ) )