Предметная область: «Междугородние пассажирские перевозки»
Рассмотрим автовокзал, который занимается обслуживанием и учетом пассажиров на междугородных автобусных маршрутах. На автовокзале имеется расписание движения автобусов, содержащее информацию о маршрутах и рейсах. Кроме того, на автовокзале имеется справочное бюро, в котором можно получить информацию о наличии мест на определенный рейс конкретной даты. И, наконец, на автовокзале есть кассы, в которых пассажир может приобрести билет. Кассы начинают предварительную продажу билетов за определенный промежуток времени до дня отправления автобуса (например, за 10 дней).
Необходимо построить такую базу данных, в которой хранится информация как о технических характеристиках маршрутов, содержащаяся в расписании, так и информация о наличии мест на рейсы, и информация о пассажирах, купивших билеты на определенный рейс.
Ниже предлагается вариант организации информации о рейсах и пассажирах (однако исполнитель задания может предложить собственный вариант организации данных).
Администратор БД к началу продажи билетов на рейс, т.е., например, за 10 дней до дня отправления, создает таблицу, соответствующую рейсу и дате отправления. Назовем эту таблицу схемой рейса, в момент создания схема рейса содержит только номера мест, а в процессе продажи билетов схема будет заполняться информацией о пассажире, которому продан билет на соответствующее место. Причем, в схеме может находиться собственно информация о пассажире (фамилия, имя, отчество) или ссылка на нее.
После того как рейс выполнен, таблица со схемой рейса удаляется, но перед этим список пассажиров этого рейса можно отправить в архив, который хранится в течение определенного срока, а информацию о рейсе - в таблицу выполненных рейсов. Если по какой-либо причине рейс отменяется, то информация об этом помещается в таблицу отмененных рейсов.
Кроме того, в рассматриваемой задаче представляют интерес запросы. Например, следующие:
- Расписание рейсов;
- Доход за каждый месяц;
- Вывести список автобусов;
- Наиболее популярные рейсы;
- Наиболее не популярные рейсы.
ER-модель
2) Описание модели данных и таблиц исходных и справочных данных:
Adress_shem
| № | Идентификатор поля | Ограничение целостности | Тип данных и длина | Ограничения | Ключи |
| N_reis | Целочисленный (int), 4 символа | Primary key | |||
| Dni_otp | NOT NULL | Символьный (char), 10 символов | |||
| Marshr | NOT NULL | Символьный (char), 10 символов | |||
| KM | NOT NULL | Целочисленный (int), 4 символа | |||
| Cena | NOT NULL | Целочисленный (int), 4 символа |
Kassa
| № | Идентификатор поля | Ограничение целостности | Тип данных и длина | Ограничения | Ключи |
| N_reis | Целочисленный (int), 4 символа | Primary key | |||
| N_mesta | NOT NULL | Целочисленный (int), 4 символа | |||
| Cena | NOT NULL | Целочисленный (int), 4 символа | Int check([Cena]>0) NOT NULL | ||
| Kod_Pass | Целочисленный (int), 4 символа | Primary key | |||
| Time_otp | NOT NULL | Datetime | |||
| Data_otp | NOT NULL | Datetime |
Otmena
| № | Идентификатор поля | Ограничение целостности | Тип данных и длина | Ограничения | Ключи |
| N_reis | Целочисленный (int), 4 символа | Primary key | |||
| Data | NOT NULL | Datetime | |||
| Prichina | NOT NULL | Символьный (char), 60 символов |
Passajir
| № | Идентификатор поля | Ограничение целостности | Тип данных и длина | Ограничения | Ключи |
| Kod_pass | Целочисленный (int), 4 символа | Primary key | |||
| FIO | NOT NULL | Символьный (char), 50 символов | |||
| Passport | NOT NULL unique | Символьный (varchar), 50 символов | unique |
Reis
| № | Идентификатор поля | Ограничение целостности | Тип данных и длина | Ограничения | Ключи |
| N_reis | Целочисленный (int), 4 символа | Primary key | |||
| Tip | NOT NULL | Символьный (char), 30 символов | |||
| Time_otp | NOT NULL | Datetime,8 символов | Primary key | ||
| Time_prib | NOT NULL | Datetime, 8 символов | |||
| Data_otp | NOT NULL | Datetime, 8 символов | Primary key |
Spravka
| № | Идентификатор поля | Ограничение целостности | Тип данных и длина | Ограничения | Ключи |
| N_reis | Целочисленный (int), 4 символа | Primary key | |||
| Svobod_mest | NULL | Целочисленный (int), 4 символа | [Svobod_mest]=>0 | ||
| Data_otp | NULL | Datetime, 8 символа |
Tip_avto
| № | Идентификатор поля | Ограничение целостности | Тип данных и длина | Ограничения | Ключи |
| N_reis | Целочисленный (int), 4 символа | Primary key | |||
| Kolvo_mest | NULL | Целочисленный (int), 4 символа | [kolvo_mest]=>0 | ||
| nomer | NULL | Varchar, 8 символа |
Vipolneno
| № | Идентификатор поля | Ограничение целостности | Тип данных и длина | Ограничения | Ключи |
| N_reis | Целочисленный (int), 4 символа | Primary key | |||
| Data_otp | NULL | Datetime,8 символов | |||
| Kolvo_prod_mest | NULL | Целочисленный (int), 4 символа | |||
| Summa | NULL | Decimal, 9 симолов | [summa]=>0 | ||
| Time_otp | NULL | Datetime,8 символов |
Скрипт БД
Reis:
Create table reis
(N_reis int not null,
Tip char(30) not null,
Time_otp datetime not null,
Time_prib datetime not null,
Primary key(N_reis,time_otp,data_otp))
Adress_shem:
Create table adress_shem
(N_reis int foreign key FK reis(N_reis) primary key,
dni_otp char(10) not null,
marshr char(10) not null,
KM int not null,
Cena int not null)
Passajir:
Create table passajir
(kod_pass int primary key,
FIO char(50) not null,
Passport varchar(50) unique not null)
Kassa:
Create table kassa
(N_reis int not null,
dni_otp char(10) not null,
marshr char(10) not null,
KM int not null,
Cena int not null check(cena>0),
Kod_pass int foreign key FK passajir(kod_pass)
Time_otp datetime not null,
Data_otp datetime not null,
Primary key(N_reis, kod_pass))
Otmena:
Create table otmena
(N_reis int primary key foreign key FK spravka(N_reis),
Data datetime not null,
Prichina char(60) not null)
Spravka:
Create table spravka
(N_reis int foreign key FK kassa(N_reis) primary key,
Svobod_mest int check(svobod_mest>=0) not null,
Data_otp datetime not null)
Tip_avto:
Create table tip_avto
(n_reis int primary key foreign key FK reis(N_reis),
Kolvo_mest int check(kolvo_mest>=0) not null,
Nomer varchar(8) not null)
Vipolneno:
Create table vipolneno
(N_reis int primary key foreign key FK reis(N_reis),
Data_otp datetime not null,
Kolvo_prod_mest int not null,
Summa decimal check(summa>=0) not null,
Time_otp datetime not null)
4) Диаграмма, демонстрирующая связи между таблицами:
Таблицы базы данных
Adressa_shem:
| N_reis | Dni_otp | Marshr | KM | Cena |
| вт-пт | Новосибирск-Бийск | |||
| вт-ср | Новосибирск-Бердск | |||
| пн-ср | Новосибирск-Горный | |||
| сб | Новосибирск-Кемерово | |||
| пн-ср | Новосибирск-Барнаул | |||
| сб | Новосибирск-Омск | |||
| чт | Новосибирск-Барнаул | |||
| пн-ср | Новосибирск-Кемерово | |||
| ср | Новосибирск-Бийск | |||
| вс | Новосибирск-Барнаул | |||
| пн-вс | Новосибирск-Юрга | |||
| чт | Новосибирск-Кемерово | |||
| вс | Новосибирск-Барнаул | |||
| вс | Новосибирск-Бийск |
Kassa:
| N_reis | N_mesta | Cena | Kod_Pass | Time_otp | Data_otp |
| 18:10:00 | 20.12.2012 | ||||
| 18:10:00 | 20.12.2012 | ||||
| 18:10:00 | 20.12.2012 | ||||
| 18:10:00 | 20.12.2012 | ||||
| 18:10:00 | 20.12.2012 | ||||
| 18:10:00 | 20.12.2012 | ||||
| 13:00:00 | 20.12.2012 | ||||
| 18:10:00 | 20.12.2012 | ||||
| 18:10:00 | 20.12.2012 | ||||
| 12:05:00 | 25.12.2012 | ||||
| 12:05:00 | 25.12.2012 | ||||
| 18:40:00 | 25.12.2012 | ||||
| 12:30:00 | 05.01.2013 | ||||
| 12:20:00 | 16.01.2013 | ||||
| 12:20:00 | 16.01.2013 | ||||
| 21:10:00 | 02.02.2013 | ||||
| 21:10:00 | 02.02.2013 | ||||
| 21:10:00 | 02.02.2013 | ||||
| 13:00:00 | 07.02.2013 | ||||
| 13:00:00 | 07.02.2013 | ||||
| 13:00:00 | 07.02.2013 | ||||
| 13:00:00 | 07.02.2013 | ||||
| 13:00:00 | 07.02.2013 | ||||
| 13:00:00 | 07.02.2013 | ||||
| 13:00:00 | 07.02.2013 | ||||
| 15:30:00 | 12.12.2012 | ||||
| 15:30:00 | 12.12.2012 | ||||
| 15:30:00 | 12.12.2012 | ||||
| 15:30:00 | 12.12.2012 | ||||
| 15:30:00 | 12.12.2012 | ||||
| 15:30:00 | 12.12.2012 | ||||
| 15:30:00 | 12.12.2012 | ||||
| 15:30:00 | 12.12.2012 | ||||
| 15:30:00 | 12.12.2012 | ||||
| 15:30:00 | 12.12.2012 | ||||
| 15:30:00 | 12.12.2012 | ||||
| 13:00:00 | 02.01.2013 | ||||
| 13:30:00 | 06.01.2013 | ||||
| 13:30:00 | 06.01.2013 | ||||
| 13:30:00 | 06.01.2013 | ||||
| 18:40:00 | 05.01.2013 | ||||
| 13:50:00 | 27.12.2012 | ||||
| 13:50:00 | 16.12.2012 | ||||
| 13:50:00 | 16.12.2012 | ||||
| 13:50:00 | 16.12.2012 | ||||
| 13:50:00 | 16.12.2012 | ||||
| 16:00:00 | 23.12.2012 | ||||
| 13:00:00 | 23.12.2012 | ||||
| 13:00:00 | 23.12.2012 |
Otmena
| N_reis | Data | Prichina |
Passajir
| Kod_pass | FIO | Passport |
| Петров И.И | ||
| Иванов С.С | ||
| Сидорова Ю.А | ||
| Степанчук Л.И | ||
| Прокопенко М.И | ||
| Ким Е.П | ||
| Белых В.С | ||
| Якоби В.А | ||
| Агапова С.А | ||
| Кот П.С | ||
| Ленин В.И | ||
| Мельникова А.А | ||
| Белый В.С | ||
| Мороз А.В | ||
| Голубина А.П | ||
| Винс А.П | ||
| Семченков Ю.Б | ||
| Дромашко Б.Б | ||
| Немец П.А | ||
| Наумова В.Е | ||
| Охлобыстин А.А | ||
| Стычкин В.С | ||
| Фандера А.П | ||
| Жижикин И.И | ||
| Бодюк И.Т | ||
| Стреженов А.Р | ||
| Прилучный Т.А | ||
| Сухинин И.Т | ||
| Светлоков А.П | ||
| Брежнева Ю.С |
Reis
| N_reis | Tip | Time_otp | Time_prib | Data_otp |
| Mersedes | 18:10:00 | 23:25:00 | 20.12.2012 | |
| ПАЗ | 12:05:00 | 12:44:00 | 25.12.2012 | |
| Ikarus | 18:40:00 | 20:20:00 | 25.12.2012 | |
| Nissan | 12:30:00 | 16:51:00 | 05.01.2013 | |
| ПАЗ | 14:20:00 | 18:50:00 | 16.01.2013 | |
| Ikarus | 12:20:00 | 16:50:00 | 16.01.2013 | |
| ПАЗ | 21:10:00 | 7:40:00 | 02.02.2013 | |
| Daewoo | 13:00:00 | 17:30:00 | 07.02.2013 | |
| Daewoo | 15:30:00 | 20:00:00 | 12.12.2012 | |
| ПАЗ | 13:00:00 | 6:20:00 | 02.01.2013 | |
| BMW | 13:30:00 | 18:00:00 | 06.01.2013 | |
| ПАЗ | 18:40:00 | 21:55:00 | 05.01.2013 | |
| МАЗ | 13:50:00 | 18:20:00 | 27.12.2012 | |
| Ikarus | 13:50:00 | 18:19:00 | 16.12.2012 | |
| Ikarus | 16:00:00 | 9:20:00 | 23.12.2012 | |
| ПАЗ | 13:00:00 | 6:20:00 | 23.12.2012 |
Spravka
| N_reis | svobod_mest | Data_otp |
| 20.12.2012 | ||
| 25.12.2012 | ||
| 25.12.2012 | ||
| 05.01.2013 | ||
| 16.01.2013 | ||
| 02.02.2013 | ||
| 07.02.2013 | ||
| 12.12.2012 | ||
| 02.01.2013 | ||
| 06.01.2013 | ||
| 05.01.2013 |
Tip_avto
| N_reis | Kolvo_mest | nomer |
| Е231КА | ||
| А675ЕН | ||
| А453КА | ||
| О278КА | ||
| Х444АМ | ||
| В647АО | ||
| В446ВО | ||
| О333ОО | ||
| Т455АМ | ||
| О001ОО | ||
| А331ТМ | ||
| В432КО | ||
| Т433ТО | ||
| А123ЕН |
Vipolneno
| N_reis | Data_otp | Kolvo_prod_mest | Summa | Time_otp |
| 20.12.2012 | 18:10:00 | |||
| 25.12.2012 | 12:05:00 | |||
| 25.12.2012 | 18:40:00 | |||
| 05.01.2013 | 12:30:00 | |||
| 16.01.2013 | 12:20:00 | |||
| 02.02.2013 | 21:10:00 | |||
| 07.02.2013 | 13:00:00 | |||
| 12.12.2012 | 15:30:00 | |||
| 02.01.2013 | 13:00:00 | |||
| 06.01.2013 | 13:30:00 | |||
| 05.01.2013 | 18:40:00 | |||
| 27.12.2012 | 13:50:00 | |||
| 16.12.2012 | 13:50:00 | |||
| 23.12.2012 | 13:00:00 |
Запросы БД
1) Вывести самые популярные рейсы
select top 3 with ties v.N_reis, marshr, kolvo_prod_mest as 'Кол-во пассажиров'
from vipolneno v join adress_shem a on a.n_reis=v.n_reis
order by 3 desc
| Номер рейса | Маршрут | Количество проданных мест |
| Новосибирск-Кемерово | ||
| Новосибирск-Бийск | ||
| Новосибирск-Барнаул |
2) Вывести самые не популярные рейсы
select top 3 with ties v.N_reis,marshr, kolvo_prod_mest
from vipolneno v join adress_shem a on a.n_reis=v.n_reis
order by 3
| Номер рейса | Маршрут | Количество проданных мест |
| Новосибирск-Бердск | ||
| Новосибирск-Горный | ||
| Новосибирск-Кемерово | ||
| Новосибирск-Барнаул | ||
| Новосибирск-Бийск | ||
| Новосибирск-Юрга | ||
| Новосибирск-Кемерово | ||
| Новосибирск-Бийск | ||
| Новосибирск-Бердск | ||
| Новосибирск-Горный | ||
| Новосибирск-Кемерово |
3) Вывести общую сумму за каждый месяц
select case DATEPART(M,data_otp)
when 1 then 'Январь'
when 2 then 'Февраль'
when 3 then 'Март'
when 4 then 'Апрель'
when 5 then 'Май'
when 6 then 'Июнь'
when 7 then 'Июль'
when 8 then 'Август'
when 9 then 'Сентябрь'
when 10 then 'Октябрь'
when 11 then 'Ноябрь'
when 12 then 'Декабрь'
end AS месяц,
sum(summa) as 'сумма'
from vipolneno
group by DATEPART(M,data_otp)
| Месяц | Сумма за месяц |
| Январь | |
| Февраль | |
| Декабрь |
4) Вывести список автобусов, их количество и вместимость.
select tip,kolvo_mest, count(tip) as kol
from tip_avto t join reis r on r.N_reis=t.N_reis
group by tip,kolvo_mest
| Марка автобуса | Количество мест | Количество в автопарке |
| Nissan | ||
| Ikarus | ||
| ПАЗ | ||
| Ikarus | ||
| ПАЗ | ||
| Daewoo | ||
| Ikarus | ||
| BMW | ||
| Daewoo | ||
| Mersedes |
5) Вывести расписание рейсов
SELECT r.N_reis AS [Номер рейса], a.Marshr AS Маршрут, a.Dni_otp AS [Дни отправления], r.Time_otp AS Время
FROM dbo.Reis r INNER JOIN
dbo.Adress_shem a ON a.N_reis = r.N_reis
| Номер рейса | Маршрут | Дни отправления | Время |
| Новосибирск-Бийск | вт-пт | 18:10:00 | |
| Новосибирск-Бердск | вт-ср | 12:05:00 | |
| Новосибирск-Горный | пн-ср | 18:40:00 | |
| Новосибирск-Кемерово | сб | 12:30:00 | |
| Новосибирск-Барнаул | пн-ср | 12:20:00 | |
| Новосибирск-Барнаул | пн-ср | 14:20:00 | |
| Новосибирск-Омск | сб | 21:10:00 | |
| Новосибирск-Барнаул | чт | 13:00:00 | |
| Новосибирск-Кемерово | пн-ср | 15:30:00 | |
| Новосибирск-Бийск | ср | 13:00:00 | |
| Новосибирск-Барнаул | вс | 13:30:00 | |
| Новосибирск-Юрга | пн-вс | 18:40:00 | |
| Новосибирск-Кемерово | чт | 13:50:00 | |
| Новосибирск-Барнаул | вс | 13:50:00 | |
| Новосибирск-Бийск | вс | 13:00:00 | |
| Новосибирск-Бийск | вс | 16:00:00 |
Триггеры
Kassa:
1) If @@Error>0
Begin
Raiserror('Ошибка',16,1)
RollBack Tran
Return
End
alter trigger Insert_kassa on kassa for insert
as
IF @@RowCount=0
Return
If @@RowCount>1
Begin
Raiserror('Ошибка',16,1)
RollBack Tran
Return
End
declare @dat datetime
select @dat=data_otp from Inserted
declare @nom_reis int
select @nom_reis=N_reis from inserted
declare @svobodn int
select @svobodn=svobod_mest from spravka s
join kassa k on k.N_reis=s.N_reis
where s.N_reis=@nom_reis and s.data_otp=@dat
if @svobodn=0
Begin