A project for databases cource
“Синяя птица”
Ваша задача заключается в автоматизации работы бюро проката парусных яхт ”Синяя птица”. Данное бюро занимается сдачей в аренду на различные сроки (от нескольких дней до нескольких лет) парусных яхт. Каждя яхта имеет свой идентификационный номер, а также принадлежит к определенному классу, зависящему от размера и водоизмещения яхты. Арендная плата зависит от класса яхты и срока аренды. Арендная плата для всех яхт одного класса и на один и тот же срок одинакова. Чем выше класс яхты, тем больше арендная плата. Бюро хранит информацию о своих клиентах, а именно ФИО, адрес, телефон, номер документов, дающих право на управление яхтой, а также номер банковского счета в случае оплаты по безналичному расчету. Выдача яхт в прокат фиксируется в журнале. Оплата за аренду в случае краткосрочной аренды (до 1 недели) взимается при выдаче яхты. При аренде на срок до месяца возможна оплата по схеме 50% в момент выдачи яхты, 50% в момент возвращения яхты. При аренде яхты на более длительные сроки оплата аренды производится ежемесячно. Для каждой яхты ведется учет ее состояния. Проверки состояния яхт должны производится каждый раз после окончания срока аренды, а также перед сдачей в аренду, если с момента предыдущей проверки прошло более 1 месяца. Регулярно производится проверка просроченности аренды.
Заспросы:
Вывести список всех яхт отсортировав по алфавиту. Вывести все яхты одного класса. Вывести всех клиентов, бравших в прокат одну и ту же яхту, сроки проката. Вывести все яхты, последняя проверка состояния которых, производилась за последнюю неделю. Вывести всех клиентов, кто задерживал возвращение яхты. Вывести клиента с наибольшим суммарным сроком проката в «Синей птице». Вывсети клиента, заплатившего наибольшее количество денег «Синей птице». Вывсести список клиентов, которые плохо следили за состоянием яхты. После возвращения ими яхт , проверка состояния яхт неудовлетворительной. Вывести яхты в порядке убывания их популярности у прокатчиков. (Первой должна быть яхта, котрую брали в прокат чаще друших.) Вывести список клиентов и их любимых яхт. Для каждого клиента показать ту яхту, которую он брал в прокат чаще остальных. В случае, когда таких яхт несколько – сравнивать по сроку проката. Если и срок проката одинаков, вернуть обе яхты а разных строках.
A. Хранимые процедуры и функции
-
Написать процедуру, увеличивающую стоимость проката всех яхт на заданную в качестве параметра величину в процентном соотношении (например, если в качестве параметра передается 10, значит стоимость проката всех яхт надо увеличить на 10 %). Для тех клиентов, у кого яхты на даный момент находятся на руках и кто внес полностью оплату за прокат в соответсвии со старой ценой проката, стоимость проката не изменяется – им не надо ничего доплачивать. Но для тех клиентов, которые внесли стоимость проката не полностью, необходимо увеличить размер оставшихся платежей на то же число процентов, насколько увеличивается стоимость проката. -
Написать функцию, вычисляющую возможную скидку для данного клиента. В качестве входного параметра передается idклиента, на выходе функция возращает процент скидки. Скидка вычислется по следующим правилам: клиент может получить скидку, если он ни разу не просрочил платеж и всегда возвращал яхты в хорошем состоянии. В противном случае скидка невозможна. Если клиент удовлетворяет данным условиям, то размер скидки в процентах равен некоторому коэффициенту с1, умноженному на общую сумму денег, которую данный клиент заплатил по предыдущим договарам «Синей птице», но не может превышать 25%. Величина коэффициента с1 определена как константа в функции, ее значение меньше единицы (например, 0.01). -
Написать функцию, возвращающую список ожидаемых платежей по всем контрактам в срок до даты, переданной в качестве параметра. Передаваемая дата должна быть в будущем, возвращаем номер договора аренды, по которому должен пройти платеж, сумму платежа, ожидаемую дату платежа, ФИО клиента и номер его счета, если он указан в базе.
B. Триггеры
-
Создать триггер на добавление новой записи заказа. Проверить, действительно ли яхта, которую пытаются арендовать, на данный момент есть в наличии (нет других действительных договоров по этой яхте) и что ее техническое состояние позволяет сдавать ее в прокат (последняя проверка была сделана после окончания предыдущей аренды и не более месяца назад). Если хотя бы одно из этих условий не выполнено, не создавать запись о заказе, выбрасывать соответствующее исключение. -
Создать триггер на обновление информации о проверке состояния яхты. В случае, когда информация обновляется для яхты, находящеся в прокате изменения в базу не вносить, выбрасывать исключение.