Разработка структуры базы данных "Оплата обучения"

Размещено на http://www.allbest.ru/

1. Задание на контрольную работу

Вариант 8. Имеются следующие данные об оплате студентами обучения (табл.1):

1. В MS Access создать базу данных «Оплата обучения», состоящую из двух таблиц («Студенты» и «Оплата»). Состав полей каждой таблицы, типы данных, ключевые поля назначить самостоятельно. Учесть, что каждый студент имеют свой номер зачетки, не должно быть дублирующих записей, один и тот же студент в течение одного дня может оплатить только один раз.

2. Заполнить созданную базу имеющимися данными. Для выбора студента в таблице «Оплата» предусмотреть подстановку из таблицы «Студенты».

Таблица 1 Исходные данные

3. Отсортировать таблицу «Оплата» одновременно по студенту, дате оплаты и сумме.

4. Настроить и сохранить в виде запросов следующие фильтры: a) Данные только с оплатой более 5000; b) Данные только об оплате студентами первого курса. access студент фильтр запрос

5. Создать и сохранить следующие запросы: a) Все данные из обеих таблиц (как в исходной таблице в начале задания); b) Данные об оплате Бондарем с указанием даты оплаты и суммы; c) Данные об оплате только за 2009г.; d) Данные о возрасте студентов (вычисляемое поле - разность между сегодняшней датой и датой рождения, деленная на 365,25) с указанием ФИО, пола, курса и даты рождения; e) Суммарная оплата, внесенная каждым студентом (с групповой операцией).

6. Создать форму для ввода, просмотра и редактирования данных таблиц.

7. Создать отчет на основании запроса 5с.

2. Разработка структуры базы данных

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

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

Для описания каждого факта оплаты следует записывать дату оплаты, сумму оплаты. Для связи с таблицей СТУДЕНТЫ необходимо добавить в таблицу ОПЛАТА поле Студент. Для назначения ключа в таблице ОПЛАТА учитываем требование задания: «один и тот же студент в течение одного дня может оплатить только один раз». Исходя из этого, ключ будет состоять из двух полей: студент и дата оплаты.

Типы данных назначаем, исходя из данных табл.1 и с учетом возможного дальнейшего наращивания данных. Имена полей назначаем с учетом «правил назначения имен полей в MS Access».

Номер зачетки задан и состоит из четырех знаков. Тип данного поля назначим текстовым длиной 4 байта. Для поля ФИО назначаем текстовое поле длиной 25 символов. Хотя максимальная длина имеющихся данных в этом поле составляет 12 символов (с учетом пробелов и кавычек), даем длину с запасом на перспективу.

Курс и Пол - текстовые поля. Для поля Курс сделана подстановка.

Для полей Дата оплаты и Дата рождения назначаем тип дата/время.

Поле Сумма - денежный тип.

Результаты разработки структуры таблиц приведены в табл.1.2 и табл.1.3.

Таблица 1.2 Структура таблицы СТУДЕНТЫ

Таблица 1.3 Структура таблицы ОПЛАТА

В режиме конструктора таблиц в MS Access создаем сначала таблицу СТУДЕНТЫ и заполняем ее данными из табл.1. При этом о каждом студенте вводим только одну запись. На рис.1 показана таблица в режиме конструктора, а на рис.2 - в режиме таблицы.

Рис. 1 Таблица СТУДЕНТЫ в режиме конструктора

Рис. 2 Таблица СТУДЕНТЫ в режиме таблицы

Далее в режиме конструктора создаем таблицу ОПЛАТА. При этом для более удобного заполнения поля Студент настраиваем подстановку данных в это поле из таблицы Студенты. На рис.3 приведено окно выбора данных для подстановки.

Рис. 3 Настройка подстановки кода получателя в таблице ОПЛАТА

Нужно обратить внимание, что в качестве источника данных указана таблица СТУДЕНТЫ, первым выбрано подставляемое поле (Номер зачетки), кроме него указаны справочные поля ФИО, Дата рождения, Курс и Дата приказа.

Для полей Дата рождения и Дата приказа в свойствах поля выбираем в качестве формата поля краткий формат даты. Для поля Сумма оплаты указываем число десятичных знаков 0 (чтобы суммы были без копеек). Указываем ключ (Номер зачетки и Дата оплаты) и сохраняем таблицу, после чего заполняем ее данными из табл.1.

На рис.4 показана таблица в режиме конструктора, а на рис. 5 - в режиме таблицы.

Рис. 4 Таблица ОПЛАТА в режиме конструктора

Рис. 5 Таблица ОПЛАТА в режиме таблицы

Схема данных приведена на рис.6.

Рис. 6 Схема данных базы данных «Оплата обучения»

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

3. Настройка сортировки и фильтров

В соответствии с п.3 задания таблицу ОПЛАТА нужно отсортировать по студенту, дате оплаты и сумме. Для этого, открыв таблицу ОПЛАТА, расположим поля Дата оплаты и Студент в указанном порядке, перетащив поле Дата оплаты влево от поля Студент. После чего выделим оба поля и щелкнем по кнопке «Отсортировать от старых к новым». Результат приведен на рис.7.

Рис. 7 Сортировка таблицы ОПЛАТА по студенту, дате оплаты и сумме

Для получения данных только с оплатой более 5000 используем фильтр. Для этого щелкаем правой кнопкой в любой строке суммы и в контекстном меню выбираем «Числовые фильтры» - «Больше» и в появившемся окне вводим сумму больше или равную 5001, жмем ОК. Результат приведен на рис.8.

Рис. 8 Данные об оплате более 5000

Чтобы сохранить результат в виде запроса, щелкаем по кнопке «Изменить фильтр», затем в режиме изменения фильтра по кнопке «Сохранить как запрос», вводим имя запроса и подтверждаем клавишей Enter.

Для настройки второго фильтра (данные только об оплате студентами первого курса) переходим в режим изменения фильтра кнопкой и вводим условие для поля Курс (рис.9). При этом следим за тем, чтобы в других столбцах не было каких-либо данных от предыдущего фильтра. Если такие данные есть, удаляем их.

Рис. 9 Настройка фильтра

Щелкаем по кнопке «Применить фильтр» и убеждаемся, что данные отобраны верно. Результат приведен на рис.10.

Рис. 10 Данные об оплате студентами первого курса

Далее, как и предыдущий фильтр, сохраняем данный в виде запроса, например, под именем Оплата первокурсников.

4. Создание запросов

Для создания запроса 5а для объекта Запросы выбираем «Создание нового запроса в режиме конструктора», добавляем в него обе таблицы, из таблицы СТУДЕНТЫ выбираем все поля, а из таблицы ОПЛАТА - все, кроме поля Студент (рис.11).

Рис. 11 Запрос со всеми данными обеих таблиц в режиме конструктора

Выполняем запрос, щелкнув по кнопке «Запуск». Результат выполнения запроса показан на рис.12. Сохраняем запрос под именем Все данные.

Рис. 12 Результат выполнения запроса со всеми данными обеих таблиц

Для выполнения задания 5b (запрос с данными об оплате Бондарем с указанием даты оплаты и суммы) откроем ранее созданный запрос Все данные, вызовем Файл - Сохранить как, и сохраним его под именем Оплаты Бондаря. Переведем запрос в режим конструктора кнопкой и введем условие отбора в столбце Получатель: Like “*Бондарь*” (рис.13). Столбцы Дата рождения, Пол, Курс и Дата приказа исключаем из запроса, для чего выделяем их и удаляем клавишей Delete, после чего выполним запрос.

Рис. 13 Настройка запроса об оплатах Бондаря

Символы * в шаблоне для оператора Like означают, что до и после слова Бондарь в имени студента могут быть и другие символы.

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

Рис. 14 Результат выполнения запроса с данными об оплатах Бондаря

Для выполнения задания 5с (запрос данные об оплатах только за 2009 г) создаем в режиме конструктора новый запрос, добавляем в него обе таблицы и выбираем из таблицы СТУДЕНТЫ поле Номер зачетки, а из таблицы ОПЛАТА все поля кроме Студент. Затем в строке Условие отбора столбца Дата_оплаты вводим выражение >=#01.01.09# And <=#31.12.09# и выполняем запрос. Запрос в режиме конструктора показан на рис.15, а результат его выполнения - на рис.16.

Рис. 15 Настройка запроса об оплатах за 2009 г

Рис. 16 Результат выполнения запроса с данными об оплатах за 2009 г

Сохраним запрос под именем оплата за 2009.

Для выполнения задания 5d (Данные о возрасте студентов (вычисляемое поле - разность между сегодняшней датой и датой рождения, деленная на 365,25)) создадим в конструкторе запросов новый запрос, включив в него перечисленные данные из обеих таблиц. Для вычисления возраста студентов в пустом столбце конструктора запросов вместо имени поля введем выражение:

Возраст: Round((Date()-[Дата рождения])/365,25)

Получившийся запрос в режиме конструктора показан на рис.17.

Рис. 17 Запрос о возрасте студентов в режиме конструктора

Запустим запрос на выполнение (рис.18) и сохраним его под именем Возраст студентов.

Рис. 18 Результат запроса о возрасте студентов

Чтобы получить данные о суммарных оплатах, внесенных каждым студентом, создаем новый запрос в режиме конструктора и включаем в него из таблицы СТУДЕНТЫ поле ФИО, а из таблицы ОПЛАТА поле Сумма. Кнопкой включаем групповые операции и в появившейся строке Групповая операция в столбце Сумма выбираем операцию Sum, чтобы просуммировать данные по группам строк с одними и теми же студентами (рис.19).

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

Все настроенные запросы отображаются в окне базы данных в разделе Объекты-Запросы (рис.21).

Рис. 19 Запрос с групповой операцией в режиме конструктора

Рис. 20 Суммарная оплата каждого студента

Рис. 21 Перечень настроенных запросов

5. Создание формы

Для создания формы в соответствии с п.6 задания выбираем Объекты-Формы и вызываем Создание формы с помощью мастера. Сначала в выпадающем списке выбираем таблицу СТУДЕНТЫ и выбираем из нее все поля (рис. 22). Затем в выпадающем списке выбираем таблицу ОПЛАТА и выбираем из нее все поля кроме поля Студент (рис.23).

Щелкаем по кнопке Далее (рис. 24), соглашаемся с предложенными настройками, щелкнув Далее.

Вид подчиненной формы оставляем табличный, щелкаем Далее, выбираем понравившийся стиль, щелкаем Далее, подтверждаем кнопкой Готово. Результат - на рис.25.

Рис. 22 Выбор полей для формы из таблицы Студенты

Рис. 23 Выбор полей для формы из таблицы Оплата

Рис. 24 Настройка вида представления данных в форме

Рис. 25 Готовая форма

6. Создание отчета

Чтобы создать отчет на основе запроса о данных об оплатах за 2009 г, переходим в раздел Объекты-Отчеты и выбираем Создание отчета с помощью мастера. В окне настроек выбираем из раскрывающегося списка запрос Оплата за 2009 г и выбираем все доступные поля (рис. 26) и щелкаем Далее.

Рис. 26 Выбор полей для отчета

Остальные настройки выбираем по своему усмотрению, при этом желательно настроить отчет таким образом, чтобы все данные показывались в читаемом, необрезанном виде. Для этого можно попробовать разные макеты и ориентацию листа. На рис. 27 приведен результат.

Таким образом, в процессе выполнения работы была спроектирована и заполнена исходными данными база данных «Оплата обучения», состоящая из двух таблиц: СТУДЕНТЫ и ОПЛАТА.

Были созданы необходимые запросы, форма и отчет в соответствии с заданием.

Рис. 27 Сформированный отчет

Размещено на Allbest.ru