Зуева Н.В., Заболотский В.П., Култышев Е.И. Базы данных Access. Методические указания

Подождите немного. Документ загружается.

(допустимые операции будут рассмотрены при практической работе с

базой данных).

Организация данных

Слово «реляционная» происходит от английского relation – отно-

шение. Отношение – математическое понятие, но в терминологии

моделей данных отношения удобно изображать в виде таблицы. При

этом строки таблицы соответствуют кортежам отношения, а столб-

цы – атрибутам. Атрибут, значения которого однозначно идентифи-

цируют кортежи, называется ключом (простым ключом), т.е. столбец

(поле), у которого данные в строках уникальны (не повторяются),

может быть определен как ключ. В некоторых отношениях кортежи

идентифицируются конкатенацией (объединением) нескольких атри-

бутов. Тогда говорят, что отношение имеет составной ключ. Это оз-

начает, что данные в строках каждого из столбцов составного ключа

могут повторяться, но комбинация данных каждой строки этих стол-

бцов является уникальной. Например, в таблице СТУДЕНТЫ есть

столбцы ФАМИЛИЯ и ГОД РОЖДЕНИЯ. В каждом из столбцов есть

некоторые повторяющиеся данные, т. е. одинаковые фамилии и оди-

наковые года рождения. Но если студенты, имеющие одинаковые

фамилии, имеют разные года рождения, то эти столбцы можно ис-

пользовать в качестве составного ключа.

В Access можно выделить три типа ключевых полей:

простой ключ;

составной ключ;

счетчик.

Поле счетчика содержит номера строк и Access обычно предлагает

его в качестве ключа, если не выбрано другое ключевое поле.

Совет. Понятие ключа имеет важное значение в реляционных базах

данных. Задание ключевых полей в таблицах не только позволит защи-

тить базу от ввода неправильных значений и сохранить ее целостность,

но и ускорит поиск информации в больших по объему базах. Поэтому,

когда Access предлагает задать ключевое поле, не отказывайтесь сразу

от этого, а подумайте, какое поле может содержать неповторяющиеся

данные и все-таки задайте ключ для таблицы. Конечно, можно всегда

отказываться от задания ключевых полей (так, казалось бы, проще и

понятней), но тем самым Вы лишите себя многих достоинств реляци-

онной базы данных.

Одно из важнейших достоинств реляционных баз данных состоит

в том, что вы можете хранить логически сгруппированные данные в

разных таблицах и задавать связи между ними, объединяя их в еди-

ную базу. Для задания связи таблицы должны иметь поля с одинако-

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

между таблицами устанавливает отношения между совпадающими

значениями в этих полях. Такая организация данных позволяет умень-

шить избыточность хранимых данных, упрощает их ввод и организа-

цию запросов и отчетов. Поясним это на примере. Допустим, что в

базе надо хранить данные о студентах (фамилия, изучаемая дисцип-

лина) и преподавателях (фамилия, номер кафедры, ученая степень,

преподаваемая дисциплина). Если хранить данные в одной таблице,

то в строке с фамилией каждого студента, изучающего конкретную

дисциплину, будут храниться все атрибуты преподавателя, читающе-

го эту дисциплину. Это же огромная избыточность данных. А если

хранить данные о студенте в одной таблице, о преподавателе – в

другой и установить связь между полями «читаемая дисциплина» –

«изучаемая дисциплина» (фактически это одинаковые поля), то из-

быточность хранимых данных многократно уменьшится без ущерба

для логической организации информации.

В Access можно задать три вида связей между таблицами: «один–ко–

многим», «многие–ко–многим» и «один–к–одному».

Связь «один–ко–многим» является наиболее часто используемым

типом связи между таблицами. В такой связи каждой записи в таблице

A могут соответствовать несколько записей в таблице B (эти записи

называют внешними ключами), а запись в таблице B не может иметь

более одной соответствующей ей записи в таблице A.

При связи «многие–ко–многим» одной записи в таблице A могут

соответствовать несколько записей в таблице B, а одной записи в таб-

лице B – несколько записей в таблице A. Такая схема реализуется толь-

ко с помощью третьей (связующей) таблицы, ключ которой состоит из,

по крайней мере, двух полей, одно из которых является общим с табли-

цей А, а другое – общим с таблицей В.

При связи «один–к–одному» запись в таблице A может иметь не

более одной связанной записи в таблице B и наоборот. Этот тип связи

используют не очень часто, поскольку такие данные могут быть поме-

щены в одну таблицу. Связь с отношением «один–к–одному» использу-

ют для разделения очень широких таблиц, для отделения части табли-

цы по соображениям защиты, а также для сохранения сведений, отно-

сящихся к подмножеству записей в главной таблице.

Целостность данных

Целостность данных означает систему правил, используемых в Access

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

обеспечивает защиту от случайного удаления или изменения связан-

ных данных. Установить целостность данных можно, если выполнены

следующие условия:

связанное поле главной таблицы является ключевым полем или име-

ет уникальный индекс;

связанные поля имеют один тип данных (здесь существует исклю-

чение – поле счетчика может быть связано с числовым полем), если

в последнем в свойстве Размер поля указано значение «Длинное

целое»;

обе таблицы принадлежат одной базе данных Access, если таблицы

являются связанными, то они должны быть таблицами Access; для уста-

новки целостности данных база данных, в которой находятся таблицы,

должна быть открыта; для связанных таблиц из баз данных других фор-

матов установить целостность данных невозможно.

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

Требуется разработать базу данных ДЕКАНАТ, содержащую четы-

ре таблицы: СТУДЕНТЫ, ПРЕПОДАВАТЕЛИ, ДИСЦИПЛИНЫ,

ОЦЕНКИ.

Таблица СТУДЕНТЫ должна содержать следующие поля:

Код студента;

Фамилия;

Имя;

Отчество;

Номер группы;

Адрес;

Телефон;

Дата рождения;

Медалист.

Таблица ПРЕПОДАВАТЕЛИ должна содержать следующие поля:

Код преподавателя;

Фамилия;

Имя;

Отчество;

Дата рождения;

Должность;

Стаж;

Телефон.

Таблица ДИСЦИПЛИНЫ должна содержать следующие поля:

Код дисциплины;

Название дисциплины;

Код преподавателя;

Номер семестра;

Экзамен.

Таблица ОЦЕНКИ должна содержать следующие поля:

Код студента;

Код дисциплины;

Номер семестра;

Оценка.

Необходимо разработать формы для ввода данных во все таблицы.

Необходимо создать следующие запросы:

Студенты заданной группы;

Оценки студентов заданной группы по заданной дисциплине;

Перекрестный запрос: средний балл групп по дисциплинам;

Увеличение на 10% зарплаты тех преподавателей, кто получает ме-

нее 2000 р.;

Удаление отчисленных студентов; (создание архива).

Все запросы должны быть оформлены в виде форм:

Разработать сложную форму;

Необходимо разработать итоговый отчет по дисциплинам, группам и

среднему баллу групп по данным дисциплинам;

Необходимо разработать кнопочную форму-меню для работы с базой

данных.

Лабораторная работа № 1

СОЗДАНИЕ СТРУКТУРЫ БАЗЫ ДАННЫХ

И УСТАНОВЛЕНИЕ СВЯЗЕЙ МЕЖДУ ТАБЛИЦАМИ

1. Создайте базу данных ДЕКАНАТ, выполнив следующие действия:

загрузите Access, в появившемся окне выберите пункт НОВАЯ БАЗА

ДАННЫХ, затем нажмите кнопку OK;

в окне ФАЙЛ НОВОЙ БАЗЫ ДАННЫХ задайте имя – вашу фами-

лию (пункт ИМЯ ФАЙЛА) и выберите папку с номером вашей группы

(пункт ПАПКА), где ваша база будет находиться (по умолчанию Access

предлагает вам имя базы db1., а тип файла – БАЗЫ ДАННЫХ ACCESS),

выбор диска для хранения БД, уточняется у преподавателя;

нажмите кнопку СОЗДАТЬ.

2. Создайте структуру таблицы ПРЕПОДАВАТЕЛИ. Для этого:

в окне базы данных выберите вкладку ТАБЛИЦЫ, а затем нажмите

кнопку СОЗДАТЬ;

в окне НОВАЯ ТАБЛИЦА выберите пункт КОНСТРУКТОР и на-

жмите кнопку OK;

в результате проделанных операций открывается окно таблицы в ре-

жиме конструктора, в котором следует определить поля таблицы;

определите поля таблицы в соответствии с табл 1.

Таблица 1

ялопямИхыннадпиТялопремзаР

ялетавадоперпдоКйоволсиЧеолеЦ

яилимаФйывотскеТ51

ямИйывотскеТ31

овтсечтОйывотскеТ51

яинеджоратаДямерв/атаД)аксам(

ьтсонжлоДковонатсдопретсаМмоксипсоселоп52

жатСйоволсиЧ

анеиволсу(еолеЦ

довыв,0>еинечанз

огещюувтстевтоос

)екбишобояинещбоос

ардефаКйывотскеТ11

нофелеТйывотскеТ)аксам(9

аталпраЗйынженеД

При определении поля ДАТА РОЖДЕНИЯ используем маску для

удобного ввода даты (т. е. в датах точки будут вводится автоматичес-

ки). Для этого в Свойства полей на вкладке Общие установите кур-

сор на поле маска, справа появится кнопка с тремя точками – на-

жмите на нее. В появившемся окне создания масок выбирайте КРАТ-

КИЙ ФОРМАТ ДАТЫ.

В поле ДОЛЖНОСТЬ используем мастер подстановок для того,

чтобы не вводить, а выбирать из списка нужную должность с ис-

пользованием ввода должности, которой нет в списке. В режиме

СОЗДАНИЯ ПОДСТАНОВОК выбираем ФИКСИРОВАННЫЙ НАБОР

ЗНАЧЕНИЙ, далее создаем 1-й столбец с должностями:

профессор;

доцент;

старший преподаватель;

ассистент.

Закончив создание списка в режиме конструктора на вкладке ПОД-

СТАНОВКА, посмотрите появившиеся изменения после работы мас-

тера. Проверьте строку ОГРАНИЧИТЬСЯ СПИСКОМ, в котором дол-

жно стоять слово НЕТ.

В поле СТАЖ в общих свойствах поля установите УСЛОВИЕ НА

ЗНАЧЕНИЕ > 0, СООБЩЕНИЕ ОБ ОШИБКЕ введите – стаж должен

быть больше 0.

В поле ТЕЛЕФОН наберите маску для ввода 999-99-99, которая

позволит не набирать тире в номере телефона при вводе в поле (под-

робнее о маске см. help).

В качестве ключевого задайте поле КОД ПРЕПОДАВАТЕЛЯ.

Закройте таблицу ПРЕПОДАВАТЕЛИ в режиме конструктора.

Откройте таблицу ПРЕПОДАВАТЕЛИ и введите первую строку

из табл. 5. При вводе стажа преподавателя введите отрицатель-

ный стаж – 17.

Остальная часть этой таблицы также будет заполняться в режиме

формы.

3. Таблица СТУДЕНТ (табл. 2) будет импортирована из таблицы

EXCEL ( файл студент.xls местонахождение таблицы узнать у препо-

давателя).

Меню ФАЙЛ/ВНЕШНИЕ ДАННЫЕ/ИМПОРТ.

Отредактируйте созданную таблицу в режиме конструктора.

Таблица 2

ялопямИхыннадпиТялопремзаР

атнедутсдоКйоволсиЧеолеЦ

яилимаФйывотскеТ51

ямИйывотскеТ21

овтсечтОйывотскеТ51

ыппургремоНйоволсиЧеолеЦ

сердАйывотскеТ03

нофелеТйывотскеТ)аксам(9

тсиладеМйывотскеТ

/аД(моксипсоселоП4

теНюиначломуоп)теН

В качестве ключевого задайте поле КОД СТУДЕНТА. Для этого щел-

кните по полю КОД СТУДЕНТА и выполните команду ПРАВКА⇒ КЛЮ-

ЧЕВОЕ ПОЛЕ.

Для удобства ввода телефона задайте маску (см. поле ТЕЛЕФОН

таблицы ПРЕПОДАВАТЕЛИ).

В поле МЕДАЛИСТ создайте ПОЛЕ СО СПИСКОМ без ввода новых

значений, а также задайте ЗНАЧЕНИЕ ПО УМОЛЧАНИЮ “Нет” ( ка-

вычки обязательны).

4. Создайте структуру таблицы ДИСЦИПЛИНЫ аналогично п.2 в

соответствии с табл. 3.

Таблица 3

ялопямИхыннадпиТялопремзаР

ынилпицсиддоКйоволсиЧеолеЦ

ынилпицсидеинавзаНйывотскеТ03

артсемесремоНйоволсиЧеолеЦ

ялетавадоперпдоКйоволсиЧ

ретсам(еолеЦ

)ковонатсдоп

немазкЭйывотскеТ

моксипсоселоп(4

).чаЗ/.зкЭ

В качестве ключевого задайте поле КОД ДИСЦИПЛИНЫ.

Поле КОД ПРЕПОДАВАТЕЛЯ будет заполнятся при помощи мас-

тера подстановок из таблицы ПРЕПОДАВАТЕЛИ. Из доступных по-

лей таблицы ПРЕПОДАВАТЕЛИ выберите: КОД ПРЕПОДАВАТЕЛЯ,

ФАМИЛИЯ, ИМЯ, ОТЧЕСТВО, скройте ключевое поле. После ра-

боты мастера при заполнении поля КОД ПРЕПОДАВАТЕЛЯ табли-

цы будут отображатся ФИО преподавателя для выбора, но в таблице

ДИСЦИПЛИНЫ поле КОД ПРЕПОДАВАТЕЛЯ будет оставаться чис-

ловым целым.

Поле ЭКЗАМЕН заполняйте при помощи поля со списком двух зна-

чений Экз. или Зач.

Закройте таблицу ДИСЦИПЛИНЫ. Откройте таблицу ДИСЦИПЛИ-

НЫ и введите первую строку из табл. 7. При вводе кода преподавателя

выбирите фамилию Истомина Ремира Евгеньевича.

Оставшаяся часть таблицы будет заполняться в режиме формы.

5. Таблица ОЦЕНКИ будет импортирована из базы данных ОЦЕН-

КИ (файл оценки.mdb). Местонахождение файла узнать у препода-

вателя.

Меню ФАЙЛ/ВНЕШНИЕ ДАННЫЕ/ИМПОРТ.

6. Отредактируйте структуру таблицы ОЦЕНКИ аналогично в соот-

ветствии с табл. 4.

Таблица 4

ялопямИхыннадпиТялопремзаР

атнедутсдоКйоволсиЧеолеЦ

ынилпицсиддоКйоволсиЧеолеЦ

артсемесремоНйоволсиЧеолеЦ

икнецОйоволсиЧтйаБ

Ключ будет составной: КОД СТУДЕНТА, КОД ДИСЦИПЛИНЫ,

НОМЕР СЕМЕСТРА

(в режиме конструктора выделите три поля и задайте ключ).

Эта таблица уже заполнена.

Разработайте схему данных, т.е. создайте связи между таблицами.

Для этого:

Выполните команду СЕРВИС⇒ СХЕМА ДАННЫХ. На экране по-

явится окно СХЕМА ДАННЫХ.

Выполните команду СВЯЗИ⇒ДОБАВИТЬ ТАБЛИЦУ.

В появившемся окне будет выделено название одной таблицы. На-

жмите кнопку ДОБАВИТЬ.

Переведите выделение на имя следующей таблицы и нажмите кноп-

ку ДОБАВИТЬ. Аналогично добавьте оставшиеся две таблицы.

Закройте окно нажав кнопку ЗАКРЫТЬ.

Создайте связь между таблицами ДИСЦИПЛИНЫ и ОЦЕНКИ. Для

этого подведите курсор мыши к полю КОД ДИСЦИПЛИНЫ в табли-

це ДИСЦИПЛИНЫ, нажмите левую клавишу мыши и, не отпуская

ее, перетащите курсор на поле КОД ДИСЦИПЛИНЫ в таблице

ОЦЕНКИ, а затем отпустите левую клавишу мыши. На экране от-

кроется окно СВЯЗИ.

Щелкните по ячейке ОБЕСПЕЧЕНИЕ ЦЕЛОСТНОСТИ ДАННЫХ –

в ней должна появиться галочка.

Щелкните по ячейкам КАСКАДНОЕ ОБНОВЛЕНИЕ СВЯЗАННЫХ

ПОЛЕЙ и КАСКАДНОЕ УДАЛЕНИЕ СВЯЗАННЫХ ЗАПИСЕЙ.

Информация. Задание каскадного обновления связанных полей и кас-

кадного удаления связанных записей позволит редактировать записи

только в таблице ДИСЦИПЛИНЫ, а в таблице ОЦЕНКИ эти действия

будут со связанными записями выполняться автоматически. Например,

если вы удалите из таблицы ДИСЦИПЛИНЫ один предмет, то в табли-

це ОЦЕНКИ удалятся все строки, связанные с этим предметом.

Нажмите кнопку СОЗДАТЬ. Связь будет создана.

Аналогично создайте связи между полем КОД ПРЕПОДАВАТЕЛЯ в

таблице ПРЕПОДАВАТЕЛИ и полем КОД ПРЕПОДАВАТЕЛЯ в табли-

це ДИСЦИПЛИНЫ, а также между полем КОД СТУДЕНТА в таблице

СТУДЕНТЫ и полем КОД СТУДЕНТА в таблице ОЦЕНКИ.

Закройте окно схемы данных, ответив ДА на вопрос о сохранении

макета.

Результаты работы представьте преподавателю.

Лабораторная работа № 2

СОЗДАНИЕ ОДНОТАБЛИЧНЫХ ФОРМ

ДЛЯ ВВОДА ДАННЫХ В ТАБЛИЦЫ

Access предоставляет широкие возможности для создания форм –

графического диалогового интерфейса пользователя. Формы позволяют

добавлять и удалять записи в таблицах, изменять значения полей, полу-

чать расчетные данные.

Простейшая форма на основе одной таблицы может быть создана

при выборе одного из режимов АВТОФОРМЫ: В СТОЛБЕЦ, ЛЕН-

ТОЧНАЯ, ТАБЛИЧНАЯ.

АВТОФОРМА В СТОЛБЕЦ является одноразовой. В ней поля рас-

полагаются в столбец, а в окне формы одна запись и кнопки перехода к

другим записям (рекомендуется использовать автоформу в столбец, если

исходная таблица содержит много полей).

Остальные автоформы многоразовые, т. е. поля размещаются в од-

ной строке и несколько записей в окне.

Все автоформы не предоставляют возможности выбора стиля офор-

мления и выбирают все поля таблицы. Однако после создания автофор-

мы, войдя в режим конструктора, можно доработать ее в нужном на-

правлении, т. е. отредактировать.

Формы могут быть получены и при помощи Мастера форм, который

в режиме диалога с пользователем позволяет выбирать одну или не-

сколько исходных таблиц, отображать только необходимые поля, предо-

ставляет стили для их оформления.

Создайте формы для ввода данных в таблицы.

Для этого:

Откройте вкладку ФОРМЫ.

Нажмите кнопку СОЗДАТЬ.

В открывающемся списке выберите таблицу ДИСЦИПЛИНЫ.

Выберите пункт АВТОФОРМА: В СТОЛБЕЦ.

Нажмите кнопку OK. Форма для ввода данных создана (рис. 1).

Заполните форму данными. Аналогично создайте форму для ввода

данных в таблицу ПРЕПОДАВАТЕЛИ (рис. 2).

Заполните форму данными (пример данных для ввода в табл. 5).