Вариант 3

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

Код клиента,

ФИО клиента,

Адрес,

Телефон домашний,

Телефон сотовый,

Причина контакта (продажа или покупка),

Код риелтора,

ФИО риелтора,

дата приема на работу,

дата рождения,

телефон,

оклад,

код объекта продажи (покупки),

адрес,

район

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

общая площадь,

площадь кухни

запрашиваемая цена,

статус объекта (продан, оформление документов, выставлен на продажу),

дата начала контакта, дата продажи (покупки),

цена продажи (покупки)

Риелтор с каждой продажи получает комиссионные в размере 10% от цены продажи.

2. Разработать базу данных, таблицы базы данных и заполнить их данными. Список риелторов и список клиентов должен содержать не менее 5 фамилий каждый, список квартир не менее 20 строк.

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

• NOT NULL – для полей, которые будут являться первичными и внешними ключами,

• PRIMARY KEY – для полей, выбранных в качестве первичных ключей,

• FOREIGN KEY – для полей, являющихся внешними ключами,

• CHECK для полей «Дата начала контакта», «Дата продажи (покупки)», (их значения не должны быть позже системной даты, и вторая должна быть не меньше первой), а также для полей «Количество комнат» и «Общая площадь» (их значения должны быть положительными числами).

Построить диаграмму базы данных.

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

   1. Показать список квартир, проданных за определенный календарный период. Показать все сведения о квартире, код и ФИО риелтора, сумму комиссионных за продажу.

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

   3. Подсчитать общую сумму комиссионных каждого риелтора.

   4. В новую таблицу скопировать все сведения о квартирах, с которыми в данный момент работает определенный риелтор.

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

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

   7. Показать фамилии трех риелторов, которые получили наибольшую сумму комиссионных за весь период работы.

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

   1. Создать функцию, которая подсчитывала бы сумму комиссионных в зависимости от цены продажи по следующему алгоритму: если цена продажи меньше 20000, то комиссионные равны 6% от цены, если цена продажи от 20000 до 35000, то комиссионные равны 7% от цены продажи, если цена продажи выше 35000, по комиссионные равны 10% от цены продажи.

   2. Создать процедуру с входными параметрами (район, количество комнат, общая площадь), которая бы выдавала список выставленных на продажу квартир в заданном районе с заданным количеством комнат и общей площадью не меньше заданной. Если таких квартир нет в списке, процедура должна об этом выдавать сообщение.

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

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

   2. Создать триггер, который бы при изменении сведений о клиенте (ФИО, адрес клиента) проверял, существует ли такой клиент в базе, и если существует, то проводил бы изменения в заданном поле, а если нет, то добавлял бы новую запись о клиенте.