Постановка задачи. Тема:Работа с многотабличной (реляционной) базой данных

MS Office ACCESS 2007

БД «Студент» Часть 4.

Тема:Работа с многотабличной (реляционной) базой данных. Запросы на обновление.

Создание отчета на основе сложного запроса.

Постановка задачи

Рассмотрим задачу, связанную с предоставлением студентам льгот по оплате обучения в коммерческом ВУЗе по результатам экзаменационной сессии. Допустим, что руководство института приняло решение материально поощрить студентов, добившихся высоких результатов в учёбе и успешно сдавших сессию, в форме предоставления им некоторых льгот по оплате за обучение. При этом было решено, что студентам, получившим в сессии:

► все отличныеоценки, предоставляется скидка 20% по оплате за обучение;

►получившим 1 оценку «хорошо», а остальные «отлично», - 10%;

►получившим 2 оценки «хорошо», а остальные «отлично», - 5%.

Контроль успеваемости студентов ведётся с помощью базы данных СТУДЕНТ. Используя эту базу данных, необходимо:

● определить по результатам сдачи экзаменационной сессии списки студентов, имеющих право на

указанные льготы по каждой категории;

● подготовить проект приказа ректора на предоставление льгот.

Данные организованы в три таблицы: СТУДЕНТ, СЕССИЯ, СКИДКА ОПЛАТЫ.

Таблице СТУДЕНТ (ранее созданная) - содержатся необходимые сведения о каждом студенте.

Таблица СЕССИЯ - содержит сведения о результатах сдачи студентами 5 экзаменов:

▪ номер студенческого билета (и зачётной книжки),

▪ оценки: по информатике, математике, экономике, философии, иностранному языку

▪ итоговый результат сдачи сессии.

Таблица СКИДКА ОПЛАТЫ - определяет условия предоставления студентам льгот по оплате за обучение

1. Теперь следует заполнить поле «Результат» таблицы СЕССИЯ значениями:

§ «отл» - для круглых отличников;

§ «хор1» - для студентов с одной оценкой «хорошо» и остальными «отлично»;

§ «хор2» - для студентов с двумя оценками «хорошо» и остальными «отлично».

Для остальных студентов данное поле остаётся незаполненным.

Сделать эту операцию вручную достаточно трудоёмко, особенно при большом числе записей. Для того, чтобы Access автоматически заполнил в соответствующих записях поле «Результат» необходимо воспользоваться запросами на обновление, которые представляют собой разновидность запросов-действий (по-английски: Action-Query). Запрос-действие создается сначала также, как уже известный Вам запрос-выборка (по-английски: Select-Query), а затем преобразуется в запрос-действие (в данном случае, в запрос на обновление данных).

В любом запросе необходимо правильно выбрать условие (критерий) отбора данных. В качестве условия отбора данных для определения круглых отличников, хорошистов с одной и с двумя четвёрками предлагается использовать произведение оценок (П) по всем предметам для каждого студента. Тогда эти произведения будут принимать значения:

для круглых отличников:	Результат = 5*5*5*5*5 = 3125
для хорошистов с одной четверкой:	Результат = 5*5*5*5*4 = 2500
для хорошистов с двумя четверками:	Результат = 5*5*5*4*4 = 2000

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

1.1.В окне базы данных перейдите на вкладку Запросы и выберите Создание запроса в режиме конструктора.

1.2.В диалоговом окне «Добавление таблицы» выберите таблицу СЕССИЯ, нажмите на кнопку Добавить, а затем закройте это окно, щёлкнув по кнопке Закрыть.

1.3.В первый столбец строки «Поле» перетащите из списка полей таблицы СЕССИЯ поле «Результат», а во втором столбце строки «Поле» создайте вычисляемое поле (которого нет ни в одной таблице базы данных), содержащее выражение, представляющее собой произведение всех оценок:

=[Сессия]![Информатика]*[Сессия]![Математика]*[Сессия]![Экономика]*[Сессия]![Философия]*[Сессия]![Ин.Яз]

Для облегчения набора этого выражения и исключения ошибок при наборе воспользуйтесь специальным средством - построителем выражений, для чего нажмите на панели инструментов кнопку Построить (с изображением «волшебной палочки» и трёх точек).

В нижнем левом окне построителя откройте двойным щелчком Таблицы, затем в списке таблиц двойным щелчком выберите таблицу Сессия. В нижнем среднем окне построителя появятся все поля таблицы Сессия. Выполните двойной щелчок по полю Информатика – оно появится в верхнем окне, затем щелкните по знаку умножения (звёздочке), расположенному между верхним и нижними окнами. Далее выполните двойной щелчок по полю Математика и снова по звёздочке, и так далее, пока не будет построено всё выражение. Наименования полей и таблицы, из которых они взяты, включаются в выражение в квадратных скобках. Для связи поля и таблицы используется восклицательный знак, который здесь означает принадлежность поля определённой таблице. После набора всего выражения нажмите на кнопку OK.

1.4.В столбце, где набрано это выражение, в строке «Условие отбора» наберите число 3125 для выбора всех круглых отличников.

1.5.Теперь преобразуйте запрос-выборку в запрос на обновление. Для этого в меню Запрос выполните команду Обновление. При этом в бланке запроса будут исключены строки «Вывод на экран» и «Сортировка», так как они для этого типа запроса не нужны. Вместо них в запрос включается новая строка «Обновление». В эту строку в поле «Результат» следует ввести новые выражение для заменяемых записей, в нашем случае выражение «отл» (кавычки допускается не ставить – в этом случае программа сама их поставит). В строке «Имя таблицы» должно быть указано «СЕССИЯ» (обычно это указывается автоматически).

1.6.Закройте запрос, сохраните его под именем отл. Обратите внимание, что вид значка этого запроса отличается от других на вкладке «Запросы», что говорит о том, что этот запрос другого типа – запрос на обновление.

1.7.Обязательно выполните созданный Вами запрос «отл» (по крайней мере один раз), дважды щёлкнув по его значку. Появится предупреждение о том, что выполнение запроса приведёт к изменению данных таблицы. Подтвердите выполнение этого запроса, ответив «Да». Затем появится сообщение: «Будет обновлено следующее число записей: 3» - подтвердите обновление записей, ответив «Да». Запрос выполнит необходимые действия. Однако никакой временной таблицы с результатами данного запроса выведено не будет, как это имело место раннее в случае выполнения запроса-выборки. Для того, чтобы увидеть результат работы запроса на обновление, откройте таблицу СЕССИЯ в режиме таблицы и посмотрите произведённые запросом изменения в поле «Результат». Против круглых отличников в этом поле должно стоять значение «отл», всего таких значений должно быть три.

В результате выполнения запроса в таблицеСессияпоявятся новые данные в полеРезультат

1.8.Аналогично пунктам 1.1–1.7 создайте запрос под именем хор1. Разница будет заключаться лишь в том, что в качестве условия отбора здесь следует указать число 2500, а в строке «Обновление» поля «Результат» таблицы СЕССИЯ надо ввести значение «хор1». Также сохраните и выполните этот запрос. Можете проверить изменения в таблице СЕССИЯ.

1.9.Аналогично пунктам 1.1–1.7 создайте запрос под именем хор2. Разница будет заключаться лишь в том, что в качестве условия отбора здесь следует указать число 2000, а в строке «Обновление» поля «Результат» таблицы СЕССИЯ надо ввести значение «хор2». Также сохраните и выполните этот запрос. Проверьте изменения в таблице СЕССИЯ. Поле «Результат» теперь должно быть заполнено в соответствии с требованиями задачи.

2. Установите связь между таблицами СКИДКА ОПЛАТЫ и СЕССИЯ.

2.1.Откройте окно «Схема данных». Для этого, находясь в окне базы данных, щёлкните по кнопке «Схема данных» на панели инструментов или выберите в меню Сервис команду Схема данных. На экране появится окно «Схема данных» с двумя таблицами.

2.2.Добавьте третью таблицу СКИДКА ОПЛАТЫ в окно «Схема данных». Для этого щёлкните правой кнопкой мыши на сером фоне в окне схемы данных и выберите в контекстном меню команду Добавить таблицу или выполните в меню Связи команду Добавить таблицу. В окне «Добавление таблицы» из списка таблиц выберите таблицу СКИДКА ОПЛАТЫ (установите курсор на имя таблицы и нажмите на кнопку Добавить, а затем Закрыть. В окне «Схема данных» расположатся три таблицы.

2.3.Протащите указатель мыши от ключевого поля «Результат» таблицы СКИДКА ОПЛАТЫ к неключевому полю «Результат» таблицы СЕССИЯ.

2.4.В появившемся диалоговом окне «Изменение связей» установите флажок «Обеспечение целостности данных» и убедитесь, что автоматически установлен тип отношения «Один-ко-многим», после чего нажмите кнопку Создать. На схеме данных появится соответствующая связь, как показано на рисунке. Теперь все таблицы базы данных связаны между собой.

2.5.Закройте схему данных, сохранив сделанные изменения.

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

3.1. Определите с помощью запроса фамилии студентов, получивших на экзамене по информатике отличную (5) оценку. В запрос должны быть добавлены две указанные таблицы. Из таблицы СТУДЕНТ включите в запрос поля: Номер, Фамилия, Имя, Отчество, Группа, а из таблицы СЕССИЯ поле Информатика. Условие отбора выберите самостоятельно. Выполните запрос, а затем сохраните его с именем «Сдали информатику на 5» (кавычки в имени не ставить!).

3.2.Определите с помощью аналогичного запроса фамилии студентов, которые сдали сессию только на «4» и «5». Из таблицы СТУДЕНТ включите в запрос те же поля, что и в предыдущем запросе. Сохраните запрос с именем «Учатся только на 4 и 5».

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

4.1.Находясь в окне базы данных, перейдите на вкладку Запросы и выберите Создание запроса в режиме конструктора.

4.2.В окне «Добавление таблицы» включите в запрос все три таблицы: сначала таблицу «СТУДЕНТ», затем таблицу «СЕССИЯ» и таблицу «СКИДКА ОПЛАТЫ», каждый раз нажимая кнопку Добавить, а после добавления всех таблиц нажмите на кнопку Закрыть. (Можно также выделить все таблицы и один раз нажать на кнопкиДобавить и Закрыть.) Все таблицы, связанные между собой, появятся в верхней части окна «Запрос на выборку».

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

4.4.Введите условие отбора. Для этого в строке «Условие отбора» под полем «Процент скидки» введите выражение «>0» (без кавычек!).

4.5.Упорядочите выводимые в запросе данные по полю «Фамилия» в алфавитном порядке. Для этого щёлкните в строке «Сортировка», в поле «Фамилия» и в появившемся списке выберите «По возрастанию».

4.6.Выполните запрос и проверьте правильность полученной информации, должно быть:

§ 3 отличника со скидкой - 20%;

§ 7хорошистов с одной четвёркой со скидкой - 10%;

§ 4 хорошиста с двумя четвёрками со скидкой - 5%.

4.7.Закройте запрос, сохранив его под именем «Скидки студентам».

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

5.1.В окне базы данных нажмите на кнопку Отчёты и выберите двойным щелчком Создание отчёта с помощью мастера.

5.2.На первом шаге мастера выберите в качестве источника данных в поле «Таблицы и запросы» последний созданный запрос «Скидки студентам». Из этого запроса выберите все доступные поля (из левого окна) для включения в отчёт, для чего нажмите на кнопку «>>», при этом все поля из левого окна будут перенесены в правое окно. Нажмите на кнопку Далее.

5.3.На втором шаге Мастер предлагает выбрать вид представления данных, который устанавливает по умолчанию – по таблице СТУДЕНТ (в левом окне). С этим следует согласиться и нажать кнопку Далее.

5.4.На третьем шаге мастер спрашивает, следует ли добавить уровни группировки. Для того, чтобы в отчёте фамилии студентов располагались не общим списком, а с разбивкой по учебным группам, необходимо добавить один уровень группировки по полю «Группа». Для этого в левом окне выделите поле Группа и, нажав на кнопку «>», перенесите его в правое окно. Нажмите на кнопку Далее.

5.5.На четвёртом шаге следует установить порядок сортировки записей в отчёте. Общепринято, что фамилии должны располагаться в пределах каждой группы в алфавитном порядке. Поэтому щёлкните в первом поле по кнопке раскрывающегося списка (с чёрным треугольником с вершиной вниз) и выберите в качестве поля сортировки поле Фамилия, на кнопке, расположенной справа от первого поля должно быть значение «По возрастанию», т.е. в алфавитном порядке (если это не так, щёлкните по ней). Аналогично в качестве второго поля сортировки выберите поле Имя, по которому записи также должны следовать также в порядке возрастания. Остальные поля сортировки можно не заполнять. Нажмите на кнопку Далее.

5.6.На пятом шаге мастера согласитесь с предложенным видом макета для отчёта: макет – ступенчатый; ориентация – книжная. Нажмите на кнопку Далее.

5.7.На шестом шаге выберите требуемый стиль отчёта – строгий, который обычно устанавливается по умолчанию, и нажмите на кнопку Далее.

5.8.На последнем, седьмом шаге задайте имя отчёта – Проект приказа, и нажмите на кнопку Готово. На экране появится вид отчёта в режиме просмотра.

5.9.Улучшите вид созданного отчёта. Для этого перейдите в режим Конструктора отчётов, нажав на кнопку с изображением чертёжного треугольника голубого цвета. В области Заголовка отчёта выделите заголовок и переместите его в центр по горизонтали. Для перемещения следует поместить курсор мыши на заголовок и найти место, где он принимает вид руки. Причём, необходимо иметь в виду, что курсор с изображением руки только с одним указательным пальцем перемещает один отдельно взятый объект, а курсор с изображением руки с пятью пальцами перемещает группу связанных объектов. В области Верхнего колонтитула, а затем и в области Заголовка группы уменьшите размер поля Группа, смести его правую границу влево (курсор при этом должен имеет вид горизонтальной двунаправленной стрелки). В области Верхнего колонтитула сместите влево поля Фамилия, Имя, Отчество, Процент скидки, для чего выделите сначала одно поле простым щелчком, а затем остальные с нажатой клавишей Shift, затем переместите всю группу объектов влево курсором с изображением руки с пятью пальцами. Переместите соответственно влево эти же поля и в Области данных. В области Верхнего колонтитула и в Области данных несколько уменьшите размер поля Процент скидки, сместив его правую границу влево. Можете по своему усмотрению сделать собственные изменения в макете отчёта. Чтобы посмотреть окончательный вид отчёта, перейдите в режим просмотра. Вид отчёта должен приблизительно соответствовать рисунку.

5.10.Закройте отчёт, сохранив все изменения.

6. Предъявите преподавателю результаты работы. После оценки вашей работы преподавателем закройте базу данных с сохранением всех данных.