Вариант 15

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

Код района,

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

Базовая цена одного квадратного метра площади в районе,

Код дома,

Адрес дома,

Год постройки дома,

Код категории дома,

Наименование категории дома,

Коэффициент оплаты (зависит от категории дома),

Номер квартиры,

Количество жильцов,

Площадь квартиры,

Количество комнат,

Площадь комнаты

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

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

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

4.1. Создать запрос для выдачи данных по определенному дому: Код дома, Адрес дома, Год постройки, Количество квартир, Количество жильцов.

4.3. Создать запрос для выдачи следующих данных по каждому дому, построенному более тридцати лет тому назад: Код дома, Адрес дома, Год постройки, Количество квартир, Количество жильцов.

4.4. Создать запрос для выдачи списка квартир, в которых количество квадратных метров на человека менее семи.

4.4. Изменить Базовую цену одного квадратного метра на 20%.

4.5. Скопировать в новую таблицу следующие данные: Код района, Код дома, Адрес дома, Код категории дома, Номер квартиры, Площадь квартиры, Квартирная плата. Квартирная плата = Площадь квартиры * Базовая цена одного квадратного метра* Коэффициент оплаты.

4.5. Создать запрос для выдачи списка квартир, в которых количество квадратных метров на человека меньше, чем в среднем по району.

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

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

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

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

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

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