Вариант 22

1. Спроектировать базу данных «Учет штрафов в ГАИ» методом нормализации и методом семантического моделирования. Данные, которые должны храниться в базе, представлены в виде одной таблицы со следующими столбцами:

Код района;

Название района;

Код владельца автомобиля;

Наименование организации или Фамилия, имя, отчество владельца (NO);

Код типа владельца (юридическое или физическое лицо);

Адрес владельца автомобиля;

Номер автомобиля;

Марка автомобиля;

Цвет автомобиля;

Код нарушения;

Наименование нарушения;

Код тяжести нарушения;

Наименование тяжести нарушения (среднее, тяжелое и т.д.);

Размер штрафа (зависит от тяжести нарушения);

Дата оформления штрафа.

2. Разработать базу данных, таблицы и заполнить их данными. В списке районов достаточно наименования. В списке владельцев автомобилей 3-4 владельца – организации, у которых зарегистрировано от 2-х до 5 автомобилей, а также не менее 7 владельцев - физические лица, у некоторых зарегистрировано 2 автомобиля. Не на все автомобили были выписаны штрафы, на некоторые по 2-3 штрафа. Задать максимальное значение поля Код района равным десяти. Добавить в таблицу, в которой содержатся поле Дата оформления штрафа, ограничение для проверки правильности ввода данного поля: Дата оформления штрафа не должна быть позднее текущей даты. Создать для каждой таблицы первичный ключ, а также внешние ключи для всех дочерних таблиц. Установить постоянные отношения между дочерними и родительскими таблицами (создаются при построении диаграммы базы данных). Задать ограничение NOT NULL для всех полей, входящих в первичные или внешние ключи.

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

4. В третьей главе курсового проекта «Основные команды SQL для извлечения, добавления и изменения данных» выполнить следующие действия и представить их результаты.

4.1. Показать список штрафов, выписанных за определенный период. Показать поля: Дата оформления штрафа, Наименование организации или Фамилия, имя, отчество владельца, Наименование нарушения, Размер штрафа.

4.2. Изменить предыдущий запрос таким образом, чтобы показывать данные только на определенный период.

4.3. Создать сгруппированный запрос для подсчета количества нарушений и общей суммы штрафов в зависимости от тяжести нарушения по каждому типу владельцев стоимости

4. Показать список автомобилей, на которых не было выписано ни одного штрафа. Показать поля: Номер автомобиля, Наименование организации или Фамилия, имя, отчество владельца, Марка автомобиля.

5. В новую таблицу скопировать сведения об автомобилях, на которых было выписано более одного штрафа. Показать поля: Номер автомобиля, Наименование организации или Фамилия, имя, отчество владельца, Марка автомобиля.

6. Увеличить штраф на 30% в случае, если выписано более одного штрафа.

7. Определить общее количество и общую сумму штрафов по каждому району на каждую дату.

5. В четвертой главе должны быть представлены разработанные процедуры и функции.

5.1. Создать функцию для определения размера штрафа в зависимости от тяжести нарушения. Проверить работу функции с помощью запроса.

5.2. Создать процедуру с параметрами Код тяжести нарушения и Размер штрафа. Процедура должна выполнять проверку, есть ли данный Код в списке Кодов тяжести нарушения. Если такая запись существует, следует изменить значение поля Размер штрафа на значение параметра. Если такого Кода тяжести нарушения еще нет в списке, следует добавить о нем новую запись.

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

6.1. Создать триггер, который бы при вводе новой записи о штрафе проверял бы, существует ли Номер автомобиля в списке автомобилей, а Код нарушения – в списке нарушений. В случае неправильного ввода того или другого параметра следует выдать сообщение и откатить транзакцию.

6.2. Создать триггер на удаление записи об автомобиле из списка автомобилей. В теле триггера осуществить проверку, существуют ли записи о штрафах, выписанных на владельца данного автомобиля и отменить удаление, если такие записи имеются.