Вариант 16

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

Код факультета;

Наименование факультета;

Код группы;

Наименование группы;

Порядковый номер;

Фамилия, имя и отчество студента;

Код стипендии;

Наименование стипендии;

Сумма стипендии;

Признак проживания в общежитии;

Код общежития;

Адрес общежития;

Сумма оплаты за общежитие;

Количество мест в общежитии;

Количество студентов, проживающих в общежитии (заполняется программным путем);

Сумма к выдаче (расчетное поле, равно Сумма стипендии - Сумма оплаты за общежитие).

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

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

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

4.1. Показать список студентов, проживающих в общежитии и не получающих стипендию по определенному факультету. Показать поля: Код факультета, Наименование факультета, Код группы, Фамилия, имя и отчество студента, Адрес общежития, Сумма стипендии, Признак проживания в общежитии.

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

3. Рассчитать значение поля Сумма к выдаче для всех студентов.

4. Показать список студентов, проживающих в общежитии и получающих стипендию. Показать поля: Код факультета, Наименование факультета, Код группы, Фамилия, имя и отчество студента, Адрес общежития, Сумма стипендии, Сумма оплаты за общежитие,

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

6. Создать запрос, выводящий список факультетов, в которых средняя стипендия по факультету превышает среднюю стипендию в университете.

7. В новую таблицу скопировать сведения по факультетам, показать поля: Код факультета, Наименование факультета, общая сумма стипендии, общие суммы оплаты за общежитие и сумму к выдаче по каждому факультету.

8. Удалить запись о студенте с заданными фамилией, именем и отчеством.

9. Рассчитать и заполнить поле Количество студентов, проживающих в общежитии.

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

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

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

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

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

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