Практическая работа № 16: Создание связей между таблицами в Access

ПРАКТИЧЕСКАЯ РАБОТА № 16
«Создание связей между таблицами»
Цель: сформировать умения и навыки по созданию связей между таблицами, сохранению
установленных связей и удалению созданных связей..
Общие теоретические сведения
Для создания хорошей структуры базы данных обязательно устранение избыточности
(повторения) данных. Для этого нужно распределить данные по нескольким отдельным
тематически организованным таблицам, связанным между собой. Связи между таблицами
реализуются связи с помощью добавления в связанные таблицы общих полей, называемых
ключом связи (ключевое поле).
При установлении связи между двумя таблица одна выступает главной а другая
подчиненной. В главной таблице поле, по которому связывают две таблицы является ключевым.
В подчиненной таблице это поле связи может быть также ключевым или не быть, и называться
внешним ключом.
Так, например между таблицами «Журнал» и «Список пациентов» (рисунок 1)
установлена связь по полю Номер карточки. Таблица «Список пациентов» является главной, а
таблица «Журнал» подчиненной и мы видим, что в ней поле связи Номер карточки не является
ключевым.
Для
создания
связей
между
таблицами
применяют
команду
Схема
данных,
расположенную на вкладке Работа с базами данных, после чего открывается окно создания
схемы данных (Рис.1).
Рис. 1. Создание схемы данных
1
Связь между таблицами устанавливает отношения между совпадающими значениями в
ключевых полях, обычно между полями, имеющими одинаковые имена в обеих таблицах.
Обязательным условием при установлении связи является совпадение связываемых полей
по типу и формату.
Тип связи в создаваемой Microsoft Access связи зависит от способа определения
связываемых полей. Существуют следующие типы связей между таблицами:
«один-ко-многим» - является наиболее часто используемым типом связи между
таблицами. В отношении «один-ко-многим» каждой записи в таблице A могут
соответствовать несколько записей в таблице B, но запись в таблице B не может иметь
более одной соответствующей ей записи в таблице A.
«один-к-одному» - при отношении «один-к-одному» запись в таблице A может иметь не
более одной связанной записи в таблице B и наоборот.
«многие-ко-многим» - при отношении «многие-ко-многим» одной записи в таблице A
могут соответствовать несколько записей в таблице B, а одной записи в таблице B
несколько записей в таблице A. Этот тип связи возможен только с помощью третьей
(связующей) таблицы, первичный ключ которой состоит из двух полей, которые являются
внешними ключами таблиц A и B. Отношение «многие-ко-многим» по сути дела
представляет собой два отношения «один-ко-многим» с третьей таблицей.
Задание: Создайте связи между таблицами.
Технология выполнения:
1. Запустите СУБД командой Пуск ↝ Все программы ↝Microsoft Office ↝ Microsoft Office
Access.
2. В открывшемся диалоговом окне, выполните единичный щелчок левой кнопкой мыши по
пиктограмме Пустая (Новая) база данных.
3. В окне Имя файла, расположенном в правой части текущего диалогового окна укажите
имя создаваемой базы данных «Продажи_Фамилия» (Выполните единичный щелчок
левой кнопкой мыши в окне имени файла ↝ Путём выделения, удалите имя,
установленное программой по умолчанию ↝ Введите: Продажи и вашу Фамилию)
2
4. Создайте 3 таблицы в режиме конструктора «Покупатель», «Счёт» и «Товар»,
выполнив следующую последовательность операций: Активизируйте вкладку Создание
↝ Таблицы ↝ Конструктор таблиц↝ В окне Конструктора Access предлагает в столбце
Имя поля перечислить все необходимые поля будущей таблицы (см.рисунок)
5. Для каждого поля, в столбце Тип данных посредством раскрывающегося списка, задайте
тип данных (см.рисунок)
6. Определите первичный ключ, как для таблицы, для этого выполните следующую
последовательность операций: Щелкните правой клавишей мыши по полю Покупатель
(кандидат для создания связей) ↝ В появившемся меню выберите пункт Ключевое поле.
▲ После выполнения данной последовательности операций слева от названия поля
появится значок «ключа».
7. Закройте и сохраните полученную таблицу с именем «Покупатель».
8. Аналогичным образом в режиме конструктора создайте таблицу «Счёт», выполнив
следующую последовательность операций: Активизируйте вкладку Создание ↝ Таблицы
↝ Конструктор таблиц↝ В окне Конструктора Access предлагает в столбце Имя поля
перечислить все необходимые поля будущей таблицы (см.рисунок)
3
9. Для каждого поля, в столбце Тип данных посредством раскрывающегося списка, задайте
тип данных (см.рисунок)
10. Определите первичный ключ, как для таблицы, для этого выполните следующую
последовательность операций: Щелкните правой клавишей мыши по полю Номер счетадоговора (кандидат для создания связей) ↝ В появившемся меню выберите пункт
Ключевое поле.
▲ После выполнения данной последовательности операций слева от названия поля
появится значок «ключа».
11. Закройте и сохраните полученную таблицу с именем «Счет».
12. Аналогичным образом опираясь на пункты 5,6, создайте в режиме конструктора таблицу
«Товар». В качестве ключевого поля установите поле № п/п.
13. В таблице «Товар» поле Единица измерения сделайте полем со списком, которое имеет
следующие
значения:
«уп.»
и
«шт.».
Для
этого
выполните
следующую
4
последовательность операций: задайте тип данных Мастер подстановок ↝
в
появившемся окне выберите пункт «Будет введен фиксированный набор значений» ↝ в
следующем окне введите значения, которые будет принимать данное поле – «уп.» и шт.»
14. Закройте и сохраните полученную таблицу с именем «Товар».
15. Таким образом в окне обозревателя объектов слева у вас должны отобразиться 3 таблицы
16. Создайте связи между таблицами, выполнив следующую последовательность операций:
Работа с базами данных↝ Схема данных.
17. Поскольку ранее никаких связей между таблицами базы данных не было определено, то
при открытии окна «Добавление таблицы», в котором следует выбрать все три таблицы
для включения в структуру межтабличных связей, последовательно выделяя каждую
таблицу и нажимая кнопку Добавить.
5
18. Введя в схему данных все три таблицы, которые необходимо связать, приступите к
созданию связи между полями, которая устанавливается путём перетаскивания имени
поля из главной таблицы в подчинённую на соответствующее ему связанное поле.
19. Свяжите таблицы «Покупатель» и «Счет» по полю Покупатель, перетащив поле
Покупатель из таблицы «Покупатель» к полю Покупатель таблицы «Счет».
ВАЖНО! ПРИ УСТАНОВЛЕНИИ СВЯЗЕЙ, ВСЕ ТАБЛИЦЫ ДОЛЖНЫ БЫТЬ
ЗАКРЫТЫ, ИНАЧЕ ПРОГРАММА БУДЕТ ВЫДАВАТЬ ОШИБКИ
20. После перетаскивания откроется диалоговое окно Изменение связей, в котором можно
задавать свойство образующейся связи:
a) включение флажка «Обеспечение условия целостности данных» позволит защититься
от случаев удаления записей из одной таблицы, при которых связанные с ними данные
других таблиц останутся без связи.
▲ Чтобы условие целостности могло существовать, поле таблицы обязательно должно
быть ключевым и оба поля должны иметь одинаковый тип.
6
b) Включение флажков «Каскадное обновление» и «Каскадное удаление» связанных
записей обеспечивают одновременное обновление или удаление данных во всех
подчинённых таблицах при их изменении в главной таблице.
▲ Так, если у покупателя сменили расчётный счёт в банке, то внести изменения надо будет
только в таблице «Покупатель», во всех связанных таблицах и запросах. Изменения произойдут
автоматически.
Установите все три флажка, как показано на рисунке.
21. После установки всех выше перечисленных свойств необходимо нажать на кнопку
Создать. В схеме данных появится связь.
22. Аналогичным образом свяжите таблицы «Счет» и «Товар» по полю Номер счетадоговора, перетащив поле Номер счета-договора из таблицы «Счет» в таблицу «Товар».
23. Аналогичным образом установите свойство образующейся связи, поставив три флажка
«Обеспечение условия целостности данных», «Каскадное обновление», «Каскадное
удаление» (см.рисунок)
7
24. После установки всех связей окно Схемы данных примет вид, как на рисунке ниже
25. Закройте окно Схемы данных. Access запросит согласия на сохранение макета Схема
данных. Кликните «Да», чтобы установленные связи были и сохранены.
26. Заполните соответствующие поля таблицы «Покупатель», двойным щелчком левой
кнопкой мыши устанавливая курсор в соответствующих полях и с помощью клавиатуры
введите необходимые данные.
27. Заполните соответствующие поля таблицы «Счет», двойным щелчком левой кнопкой
мыши устанавливая курсор в соответствующих полях и с помощью клавиатуры введите
необходимые данные.
28. Заполните соответствующие поля таблицы «Покупатель», двойным щелчком левой
кнопкой мыши устанавливая курсор в соответствующих полях и с помощью клавиатуры
введите необходимые данные.
8
29. Обратите внимание, что благодаря наличию связей между таблицами, можно
просматривать данные связанной таблицы, нажав на пиктограмму крестика слева от
записи таблицы. Так, на рисунке показано, что в таблице «Покупатель» для ИП Ахметова
нажав на пиктограмму крестика, отображается содержимое таблицы «Счет», а именно те
счета, которые оформлены на данного покупателя.
30. Закройте созданную Вами базу данных.
9
ЗАДАНИЕ НА ПРАКТИЧЕСКУЮ РАБОТУ:
1. Изучите методические указания к практической работе:
2. Выполните задание.
3. Самостоятельно, для своего варианта базы данных, созданной в предыдущей
практической работе, установите ключевые поля и установите связи между таблицами.
Заполните таблицы данными.
4. Ответьте письменно на контрольные вопросы
Контрольные вопросы:
1. Понятие и назначение ключевого поля
2. Что такое внешний ключ?
3. Назовите обязательное условие при установлении связи
4. Перечислите и охарактеризуйте типы связей
5. При каких условиях и для чего возможно задание для нее параметров целостности
данных?
6. В чем заключается работа механизмов каскадного обновления и удаления связанных
записей
Для получения зачета по практической работе студент должен выполнить задания
учебного примера и задание своего варианта, ответить письменно на контрольные
вопросы.
10
Вариант 1 База данных «Зоопарк»
1. Задайте ключевые поля в таблице «Животные»: название животного, таблице
«Рацион»: название животного, таблице «Сектора»: сектор
2. Свяжите таблицы «Животные» и «Рацион» по полю Название животного
3. Свяжите таблицы «Сектора» и «Животные» по полю Сектор
4. Заполните таблицу «Сектора» (не менее 3 записей)
5. Заполните таблицу «Животные» (не менее 10 записей)
6. Заполните таблицу «Рацион» (не менее 10 записей)
Вариант 2 База данных «Санаторий»
1. Задайте ключевые поля в таблице «Отдыхающие»: код отдыхающего, таблице
«Услуги»: название услуги, таблице «Назначения»: код записи
2. Свяжите таблицы «Отдыхающие» и «Назначения» по полю Код отдыхающего
3. Свяжите таблицы «Услуги» и «Назначения» по полю Название услуги
4. Заполните таблицу «Отдыхающие» (не менее 5 записей)
5. Заполните таблицу «Услуги» (не менее 5 записей)
6. Заполните таблицу «Назначения» (не менее 10 записей)
Вариант 3 База данных «Туристическая фирма»
1. Задайте ключевые поля в таблице «Клиент»: код клиента, таблице «Договор»: номер
договора, таблице «Путевка»: код тура
1. Свяжите таблицы «Клиент» и «Договор» по полю Код клиента
2. Свяжите таблицы «Путевка» и «Договор» по полю Код тура
3. Заполните таблицу «Клиент» (не менее 5 записей)
4. Заполните таблицу «Путевка» (не менее 5 записей)
5. Заполните таблицу «Договор» (не менее 10 записей)
Вариант 4 База данных «Продажа автомобилей»
1. Задайте ключевые поля в таблице «Клиенты»: код клиента, таблице «Автомобили»:
марка автомобиля, таблице «Продажа»: код записи
2. Свяжите таблицы «Клиенты» и «Продажа» по полю Код клиента
3. Свяжите таблицы «Автомобили» и «Продажа» по полю Марка автомобиля
4. Заполните таблицу «Клиенты» (не менее 5 записей)
11
5. Заполните таблицу «Автомобили» (не менее 5 записей)
6. Заполните таблицу «Продажа» (не менее 10 записей)
Вариант 5 База данных «Образовательные услуги»
1. Задайте ключевые поля в таблице «Студенты»: ФИО, таблице «Преподаватели»:
ФИО, таблице «Специальности»: название специальности.
2. Свяжите таблицы «Студенты» и «Специальности» по полю Название специальности
3. Свяжите таблицы «Специальности» и «Преподаватели» по полю название
специальности
4. Заполните таблицу «Специальности» (не менее 5 записей)
5. Заполните таблицу «Студенты» (не менее 10 записей)
6. Заполните таблицу «Преподаватели» (не менее 5 записей)
Вариант 6 База данных «Гастроли»
1. Задайте ключевые поля в таблице «Участники»: ФИО, таблице «Концерты»:
название концерта, таблице «График концертов»: код записи
2. Свяжите таблицы «Концерты» и «График концертов» по полю Название концерта
3. Свяжите таблицы «Концерты» и «Участники» по полю Название концерта
4. Заполните таблицу «Концерты» (не менее 5 записей)
5. Заполните таблицу «Участники» (не менее 10 записей)
6. Заполните таблицу «График концертов» (не менее 5 записей)
12