Задания к лабораторным работам по курсу "Безопасность систем баз данных". Вариант 2 (База данных "Автопредприятие по перевозке пассажиров")

Лабораторная работа №1

Исходной информацией для построения ER-диаграммы базы данных является следующий перечень атрибутов:

·  марка, номер, категория, вместимость транспортного средства;

·  дата поступления автомобиля на предприятие, дата списания;

·  название остановки автотранспорта; название района города, в котором расположена данная остановка;

·  номера маршрутов автотранспорта и конечные остановки для каждого маршрута;

·  дата и время перевоза пассажиров;

·  количество перевезенных пассажиров (учитывается каждая перевозка);

·  стоимость проезда.

Примечание. Каждый маршрут соотносится с определенной категорией автотранспорта.

Перечисленные атрибуты необходимо сгруппировать по сущностям и связям. Определение сущностей и структуры связей выполняется студентом самостоятельно. В процессе работы исходный список атрибутов может быть уточнён и дополнен.

Лабораторная работа №3

Реализовать следующие запросы к БД.

1.  Получить перечень автомобилей указанной категории.

2.  Получить перечень автомобилей, полученных предприятием в течение указанного периода.

3.  Получить перечень автомобилей, списанных в течение указанного периода.

4.  Получить данные о распределении пассажирского автотранспорта по маршрутам.

5.  Получить сведения о количестве пассажиров, перевезенных по заданному маршруту за указанный день, месяц, год.

6.  Получить сведения о среднем количестве пассажиров, перевезённых различными маршрутами автотранспорта различных категорий. В ответ на запрос должна выдаваться таблица следующего вида:

7.  См. запрос №6 со следующим дополнением: в результирующей таблице должны показываться только те маршруты, которым соответствует количество перевезённых пассажиров не менее заданного.

Запросы 4 и 6 реализовать в виде представлений.

Лабораторная работа №4

Реализовать в базе данных следующие функции и хранимые процедуры.

1. Функция, возвращающая перечень автомобилей указанной категории. Параметр – категория автотранспорта.

2. Функция, возвращающая перечень автомобилей, полученных предприятием в течение указанного периода. Параметры – дата начала и дата окончания периода.

3. Функция, возвращающая перечень автомобилей, списанных в течение указанного периода. Параметры – дата начала и дата окончания периода.

4. Процедура регистрации нового автомобиля.

Входные параметры: марка, номер, вместимость, категория автомобиля и дата его поступления на предприятие.

Дата поступления по умолчанию совпадает с текущей системной датой.

Выходной параметр: код завершения процедуры. Возможные значения:

·  0 – процедура завершена успешно;

·  1 – неверная категория автотранспорта.

5. Процедура списания автомобиля.

Входные параметры: номер автомобиля, дата списания.

Дата списания по умолчанию совпадает с текущей системной датой.

Выходной параметр – код завершения процедуры. Возможные значения:

·  0 – процедура завершена успешно;

·  1 – автомобиль не найден.

6. Процедура регистрации нового маршрута.

Входные параметры: номер маршрута, категория автотранспорта, две конечных остановки.

Выходной параметр: код завершения процедуры. Возможные значения:

·  0 – процедура завершена успешно;

·  1 – маршрут уже зарегистрирован;

·  2 – неверная категория автотранспорта.

7. Процедура приписки автомобиля к маршруту.

Входные параметры: номер автомобиля, номер маршрута.

Категория автотранспорта не указывается, т. к. процедура должна определить ее сама на основании того, к какой категории относится автомобиль.

Выходной параметр: код завершения процедуры. Возможные значения:

·  0 – процедура завершена успешно;

·  1 – неверный номер автомобиля;

·  2 – неверный маршрут.

8. Процедура регистрации факта перевозки пассажиров.

Входные параметры: номер автомобиля, дата перевозки, количество пассажиров и общая стоимость.

Номер маршрута не указывается, т. к. он определяется процедурой на основании текущей информации о приписке данного автомобиля к некоторому маршруту.

Выходной параметр – код завершения процедуры. Возможные значения:

·  0 – процедура завершена успешно;

·  1 – неверный номер автомобиля;

·  2 – указанный автомобиль не приписан ни к одному маршруту.

Лабораторная работа №6

Реализовать в базе данных следующие ограничения.

1. Любая дата перевозки пассажира автомобилем – более ранняя, чем дата списания автомобиля.

2. Автомобиль и любой его маршрут относятся к одной и той же категории транспорта.

3. Дата поступления автомобиля на предприятие – более ранняя, чем дата списания.

4. Количество перевозимых пассажиров неотрицательно.

5. Начальная и конечная остановки у маршрута не совпадают.

Лабораторная работа №7

Пользователями БД являются начальники, сотрудники автопредприятия и «гости». Необходимо предусмотреть в базе данных разграничение доступа разных категорий пользователей к различным сведениям.