Лабораторная работа №4.

Создание связей между таблицами

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

1. Задание на разработку БД "Такси"

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

     Поставив задачу, можно приступать к разработке структуры базы данных.

2. Разработка структуры БД

     Начинающие пользователи для хранения всех данных обычно создают одну таблицу (по аналогии с Excel). Примерная структура такой таблицы для данной задачи может быть следующей (табл. 7.3).

     Хранение всех данных в подобной таблице будет неудобным, поскольку в ней смешаны три разных понятия – Автомобили, Заказы и Клиенты, что приведет к повторению данных. Например, для регистрации вызова постоянного клиента придется каждый раз вводить его номер карточки, фамилию и другие данные. Если создать отдельную таблицу Клиенты, то сведения о клиентах будут заноситься только при первом вызове, а при последующих фамилия клиента будет выбираться из раскрывающегося списка. Исходя из аналогичных соображений, можно также создать отдельную таблицу Автомобили.

     Итак, создадим новую БД с именем Такси.accdb. и добавим в нее три таблицы – Автомобили, Заказы и Клиенты.

    В каждой таблице нужно определить ключевое поле. В таблице Клиенты это будет поле Карточка с типом данных Счетчик, а в таблице Автомобили – Номер - текстовое поле из 6 символов. В таблице Заказы нет подходящего поля для ключевого, поэтому введем дополнительное поле Заказ с типом данных Счетчик. Представление о полях, их типах и размерах дает нижеследующая таблица (рис. 1):

Рис. 1. Типы и размеры полей таблиц БД "Такси"

     В таблице Заказы введено два поля - Nавто и Карточка для того, чтобы с их помощью можно было связать эту таблицу с таблицами Автомобили и Клиенты. Такие специальные поля называют внешними ключами или вторичными ключами. В противовес им ключевое поле Номер таблицы Автомобили и ключевое поле Карточка таблицы Клиенты будут первичными ключами. 

3. Установления связей между таблицами

      Теперь установим между таблицами связи. Для этого мы обеспечим поля Nавто и Карточка таблицы Заказы списками выбора их возможных значений. Значения поля Nавто будем выбирать из списка значений Номер таблицы Автомобили, а значения поля Карточка из списка значений поля Карточка таблицы Клиенты.

     С этой целью откроем таблицу Заказы и для поля Nавто выберем тип данных Мастер подстановок. Далее выберем опцию Объект "столбец подстановка", затем выберем таблицу Автомобили и в следующем окне передвинем поле Номер в выбранные поля. Затем в окне создание подстановки выберем Номер и нажмем кнопку По возрастанию и, наконец, в следующем окне нажмем кнопку Готово.

     Аналогично свяжем поле Карточка таблицы Заказы с полем Карточка таблицы Клиенты.

     Закроем таблицу и на закладке Работа с базами данных щелкнем на кнопке Схема данных. При этом появится окно со схематическим изображением таблиц, их полей и установленных нами связей между полями таблиц (рис. 2).

Рис. 2. Схема таблиц БД Такси

     В нашем случае одному автомобилю может соответствовать сколько угодно заказов, поэтому мы свяжем соответствующие таблицы отношением "один-ко-многим". Для этого мышью щелкнем на линии связи Номер и Nавто. При этом появится окно, которое показано на рис. 3. В нем нужно установить галочку, указывающие на то, что будет обеспечиваться целостность данных, и нажать кнопку ОК.

Рис.3. Установление связей между таблицами

     Аналогично, одному клиенту может соответствовать множество заказов. Поэтому установим связь "один-ко-многим" между соответствующими полями таблиц Клиенты и Заказы. Окончательная структура БД получится такой (рис. 4):

Рис. 4. Таблицы БД Такси и схема связей между ними

     При этом образовалось две пары взаимосвязанных таблиц: а) Автомобили и Заказы и б) Клиенты и Заказы. Первая таблица пары является главной поскольку одной записи это таблицы может соответствовать сколько угодно записей второй таблицы, которая в этой паре будет подчиненной.  

     Обеспечение целостности данных необходимо для того чтобы программа Access контролировала правильность данных в связанных полях. Также доступными являются следующие опции:

- каскадное обновление связанных полей означает, что при установленном флажке будет разрешено изменение первичного ключа (например, для таблицы Автомобили разрешено изменение значения ключевого поля Номер), и автоматическое изменение значения внешнего ключа в связанной таблице (таблица Заказы, внешний ключ - поле Nавто, который предназначен для связывания с таблицей Автомобили);

- каскадное удаление связанных записей – разрешает автоматическое удаление всех связанных записей из подчиненной таблицы при удалении записи в главной таблице, например, при удалении записи об определенном клиенте будут автоматически удалены все записи о его заказах; при снятом флажке вы не сможете удалить запись, пока не удалите все связанные с ней записи.

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

4. Создание подстановки из фиксированного набора значений

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

     Для настройки подстановки фиксированных значений выполните следующие действия.

1. Откройте таблицу Заказы в режиме конструктора и выберите для поля Состояние тип данных Мастер подстановок.

2. В первом окне Мастера (рис. 5) установите переключатель в положение Будет введен фиксированный набор значений и нажмите кнопку Далее.

3. В следующем окне введите в таблицу список нужных значений (рис. 6), после чего нажмите кнопки Далее и Готово.

Рис. 5. Первое окно Мастера подстановок

Рис. 6. Для создания фиксированного списка подстановки следует ввести нужные значения вручную

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

5. Автоматический ввод текущей даты и времени

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

1. Снова откройте таблицу Заказы в режиме конструктора, затем выберите поле Дата.

2. Щелкните кнопкой мыши в поле ввода свойства Значение по умолчанию и нажмите появившуюся кнопку с изображением троеточия

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

3. В построителе выражений дважды щелкните кнопкой мыши на значке Функции, выберите категорию Встроенные функции, затем выберите в среднем списке пункт Дата/время, а в правом – функцию Now (Сейчас). Нажмите кнопку Вставить – выбранная функция будет добавлена в выражение (рис. 7), после чего нажмите кнопку OK.

Рис. 7. Выбор нужной функции с помощью построителя выражений

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

     Заполните таблицы данными примерно такими, как показано на рис 8 - 10.

Рис. 8. Таблица БД Автомобили

Рис. 9. Таблица БД Клиенты

Рис. 10. Таблица БД Заказы