Связи между таблицами
Современные базы данных обычно состоят из многих таблиц, связанных между собой. В реляционной теории баз данных выделяют несколько типов связей между таблицами, однако чаще всего используется тип связи «один-ко-многим». Например, в базе данных Борей каждая таблица связана с одной или несколькими другими с использованием отношения «один-ко-многим» (рис. 7.14).
Рис. 7.14. Схема данных учебной базы данных Борей
Рассмотрим связь между таблицами Заказы и Клиенты. В таблице Клиенты для каждого клиента имеется только одна запись, а один и тот же клиент может быть включен в таблицу Заказы много раз, поэтому линия связи на схеме данных обозначена единицей со стороны таблицы Клиенты и знаком бесконечности со стороны таблицы Заказы.
Таблица, участвующая в связи на стороне «один», далее будет называться главной, а таблица на стороне «много» – подчиненной, или подробной.
Для создания связи обычно выбирается пара полей – по одному из каждой таблицы. Поле, используемое для связи в главной таблице, должно быть ключевым и называется первичным ключом. В таблице Клиенты ключевым полем выбрано специальное поле ИД (сокращение от слова «идентификатор») с типом данных Счетчик.
Поле для связи в подчиненной таблице называется внешним ключом и должно иметь тот же тип данных, что и первичный ключ в главной таблице, за исключением случая, когда первичный ключ имеет тип данных Счетчик (тогда для внешнего ключа следует выбрать числовой тип данных).
Чтобы при редактировании подчиненной таблицы появилась возможность выбирать значение внешнего ключа из раскрывающегося списка с содержимым главной таблицы, кроме создания связи нужно выполнить подстановку полей. Порядок выполнения подстановки будет рассмотрен в следующей практической работе.
Этапы проектирования базы данных
Процесс создания базы данных следует тщательно продумать, поскольку допущенные ошибки исправлять намного сложнее, когда база данных наполнена информацией. Разработку базы данных лучше выполнять в несколько этапов.
Постановка задачи. В процессе разработки баз данных обычно присутствуют две стороны: заказчик и разработчик. Заказчик определяет цель и задачи создания базы данных, а разработчик реализует эту задачу. В диалоге между заказчиком и разработчиком нужно решить следующие вопросы:
□ для чего создается база данных;
□ какие данные будут храниться;
□ как нужно обрабатывать данные и какие результаты при этом нужно получить.
Если база данных небольшая, то заказчик и разработчик может быть одним и тем же лицом. Однако и в этом случае перед началом создания базы данных следует дать подробные ответы на приведенные выше вопросы.
Разработка структуры базы данных. На этом этапе следует определить, какие таблицы будут использоваться в базе данных и как они будут связаны между собой. Результат разработки структуры можно представить в виде схемы данных (см. рис. 7.14), которую можно начертить на листе бумаги. Это самый важный этап при создании базы данных, поскольку ошибки в структуре могут стать источником серьезных проблем при последующем выполнении обработки данных, а изменение структуры уже готовой базы данных – относительно сложная задача. Считается, что правильная структура базы данных – это половина ее успеха, поэтому созданную структуру нужно тщательно проанализировать и при необходимости внести в нее изменения.
Для разработки структуры небольшой базы данных из нескольких таблиц самыми подходящими инструментами могут оказаться лист бумаги и карандаш. Для проектирования объемных баз данных существуют специальные программные средства, для работы с которыми необходим соответствующий уровень подготовки.
Создание таблиц и связей между ними. После разработки структуры базы данных можно приступать к созданию самой базы данных, таблиц и связей между ними. Для создания таблицы нужно иметь следующую информацию:
□ как будут называться все поля таблицы и какой тип данных будет использоваться для каждого поля;
□ какие поля в таблице будут являться ключевыми (уникальными);
□ какие дополнительные свойства полей будут применяться.
Создание форм, запросов и отчетов. Эти объекты создаются для обработки данных, вывода результатов и удобства работы пользователя. Их создание будет подробно рассмотрено в следующих уроках.
ПРИМЕЧАНИЕ
В Access имеются шаблоны нескольких баз данных, с помощью которых можно быстро создать новую базу данных с готовыми таблицами, формами и отчетами, не углубляясь в параметры создаваемых объектов. Этот способ доступен пользователям с минимальным уровнем знаний, однако имеет существенный недостаток: такая база данных часто не удовлетворяет всем поставленным требованиям и ее приходится доделывать вручную. Чтобы лучше разобраться с принципами построения баз данных, далее будет рассмотрено создание базы данных «с нуля». Полученные знания позволят редактировать любые базы данных, в том числе и созданные на основе встроенных шаблонов.
Техническое задание на разработку учебной базы данных
Необходимо создать базу данных для диспетчера такси, которая должна отвечать следующим требованиям.
□ В эту базу диспетчер должен заносить поступающие вызовы клиентов и регистрировать их исполнение.
□ На связи с диспетчером находятся несколько автомобилей, каждый из них закреплен за определенным водителем и имеет бортовой номер. При поступлении вызова диспетчер назначает автомобиль для выполнения заказа и фиксирует информацию о заказе в базе данных.
□ В базе данных нужно вести учет заказов постоянных клиентов, каждый из которых имеет карточку с персональным номером. Кроме номера карточки база данных должна позволять заносить фамилии, адреса и телефоны клиентов.
□ Кроме учета оперативной информации база данных должна обеспечивать получение различных итоговых данных, например количество и сумма заказов постоянных клиентов, интенсивность использования автомобилей и др.
Поставив задачу, можно приступать к разработке структуры базы данных.
Разработка структуры базы данных
Начинающие пользователи для хранения всех данных обычно создают одну таблицу (по аналогии с Excel). Примерная структура такой таблицы для данной задачи может быть следующей (табл. 7.3).
Таблица 73. Предварительный проект таблицы учебной базы данных
Хранение всех данных в подобной таблице будет неудобным, поскольку в ней смешаны три разных понятия – Заказы, Клиенты и Автомобили, что приведет к повторению информации. Например, для регистрации вызова постоянного клиента придется каждый раз вводить его номер карточки, фамилию и другие данные. Если создать отдельную таблицу Клиенты, то сведения о клиентах будут заноситься только при первом вызове, а при последующих фамилия клиента будет выбираться из раскрывающегося списка. Исходя из аналогичных соображений, можно также создать отдельную таблицу Автомобили, и окончательная структура базы данных может быть такой (рис. 7.15).
Несколько замечаний по созданному проекту.
□ В каждой таблице нужно определить ключевое поле. В таблице Клиенты это будет поле НомерКарточки, а в таблице Автомобили – НомерАвтомобиля. В таблице Заказы нет подходящего поля для ключевого, поэтому было введено дополнительное поле КодЗаказа с типом данных Счетчик.
Рис. 7.15. Проект структуры базы данных диспетчерской службы такси
□ Для регистрации исполнения заказов введено дополнительное поле СостояниеЗаказа, в которое оператор сможет вводить следующие значения – Активный, Выполнен, Отменен.
□ Для упрощения задачи пусть каждый водитель работает только на своем автомобиле в одну смену. В случае посменной работы водителей нужно будет разделить таблицу Автомобили на несколько отдельных.
ПРИМЕЧАНИЕ
Кроме связей типа «один-ко-многим» существуют связи «один-к-одному» и «многие-ко-многим». Так, если нужно хранить расширенные сведения об автомобилях и водителях, то целесообразно создать отдельную таблицу Водители, которая была бы связана с таблицей Автомобили способом «один-к-одному».
Связь «многие-ко-многим» реализуется в Access с помощью дополнительной таблицы. В данном примере таблицы Автомобили и Клиенты связаны между собой отношением «многие-ко-многим» с помощью дополнительной таблицы Заказы.
После того как структура базы данных создана и проверена, нужно проработать более детально структуру каждой таблицы. Таблица Клиенты была создана в предыдущем уроке, поэтому рассмотрим проекты таблиц Заказы и Автомобили (табл. 7.4, 7.5).
Таблица 7.4. Проект таблицы Автомобили
Для полей Автомобиль и Клиент выбран числовой тип, поскольку они используются в связях (см. рис. 7.15), а связанные поля должны иметь одинаковый тип.
Порядок создания связей
В подавляющем большинстве случаев в базах данных используются связи типа «один-ко-многим». Для создания такой связи в Access можно использовать следующий алгоритм.
1. Сначала создайте таблицу, которая участвует в связи на стороне «один».
2. В процессе создания таблицы, участвующей в связи на стороне «много», выполните подстановку полей из ранее созданной таблицы.
3. В окне Схема данных при необходимости настройте дополнительные параметры связи.
Подробно эти действия будут рассмотрены в инструкции по выполнению практической работы.
Практическая работа 56. Создание связей между таблицами
Задание. Создать в среде Access 2007 базу данных из трех связанных таблиц согласно разработанному в этом уроке проекту.
Эта работа будет объемной, поэтому ее выполнение будет разделено на несколько этапов.
Создание таблицы Автомобили
1. Откройте базу данных Taxi2008, которая была создана в предыдущем уроке. В ней должна уже быть одна таблица – Клиенты.
2. Выполните команду Создание ► Таблицы ► Таблица.
3. Введите названия полей и их свойства согласно проекту таблицы (см. табл. 7.4). Процесс создания таблицы был подробно рассмотрен в предыдущем уроке.
4. Переключитесь в режим конструктора – отобразится окно сохранения таблицы, в котором следует указать имя Автомобили.
5. В режиме конструктора установите размеры полей, затем снова переключитесь в режим таблицы.
6. Введите несколько пробных записей в созданную таблицу.
Создание таблицы Заказы
В процессе создания таблицы Заказы выполним подстановку необходимых полей из таблиц Автомобили и Клиенты.
1. Выполните команду Создание ► Таблицы ► Таблица и введите названия первых двух полей – КодЗаказа и ДатаЗаказа. Название поля НомерАвто вводить не нужно, поскольку это поле будет создано с помощью подстановки.
2. На вкладке Работа с таблицами нажмите кнопку Добавить поля в группе Поля и столбцы, чтобы отобразить область задач Список полей.
3. Найдите поле НомерАвто в таблице Автомобили и дважды щелкните на нем кнопкой мыши – будет запущен мастер создания подстановки (рис. 7.16).
4. Поле НомерАвто будет выбрано для подстановки автоматически, но для большей наглядности можно добавить в столбец подстановки поле Водитель. Для этого выделите нужное поле и нажмите кнопку
5. Мастер предложит указать способ сортировки списка подстановки. Выберите сортировку по номеру автомобиля и нажмите кнопку Далее.
6. Следующий этап – выбор ширины столбцов для списка подстановки. По умолчанию Мастер предлагает скрыть поле первичного ключа, и с этим можно согласиться, когда его значение не имеет особого смысла (например, в базе данных Борей ключевые столбцы всегда скрыты). В данном случае нужно снять флажок Скрыть ключевой столбец (рекомендуется), чтобы поле НомерАвто также отображалось в списке подстановки (рис. 7.17), после чего нажать кнопку Далее.
Рис. 7.16. Выбор полей для подстановки
Рис. 7.17. Настройка вывода на экран столбцов подстановки
7. Поскольку вы разрешили отображение ключевого поля, в следующем окне Мастер еще раз спросит, какое поле использовать для связи. Выберите поле НомерАвтои нажмите кнопку Далее.
8. В последнем окне при необходимости измените предлагаемую подпись для столбца подстановки и нажмите Готово, после чего будет сохранена таблица и создана связь.
9. Аналогично добавьте в таблицу Заказы поле НомерКарточки из таблицы Клиенты иукажите параметры подстановки.
10. Поля СуммаЗаказа и СостояниеЗаказа создайте обычным способом, после чего сохраните структуру таблицы Заказы.
Настройка параметров связей
1. Закройте все ранее открытые вкладки таблиц.
2. На ленте откройте вкладку Работа с базами данных и нажмите кнопку Схема данных в группе Показать или скрыть. В появившемся окне вы должны увидеть таблицы базы данных и линии связи между ними. Если в окне Схема данных отсутствуют изображения таблиц, щелкните правой кнопкой мыши на свободном месте окна, выполните команду Добавить таблицу, в появившемся окне выберите нужные таблицы и нажмите кнопку Добавить.
3. Если в окне Схема данных связь между таблицами отсутствует или отображается неправильно, значит, при выполнении подстановки была допущена ошибка. Для ее исправления выполните следующие действия:
1) в окне Схема данных щелкните правой кнопкой мыши на ошибочной связи и выберите в контекстном меню команду Удалить;
2) откройте таблицу Заказы, удалите поле подстановки и попробуйте добавить его заново.
4. Дважды щелкните кнопкой мыши на линии связи, чтобы открыть окно ее параметров (рис. 7.18). Установите флажок Обеспечение целостности данных, чтобы программа Access контролировала правильность данных в связанных полях. Станут доступными следующие флажки:
• каскадное обновление связанных полей – при установленном флажке будет разрешено изменение первичного ключа, причем соответствующие значения внешнего ключа в связанной таблице будут также изменены, например, если для определенного клиента будет изменен номер карточки, то он также будет автоматически изменен в поле Клиент таблицы Заказы; при снятом флажке изменение первичного ключа будет запрещено при наличии связанных записей;
• каскадное удаление связанных записей – разрешает автоматическое удаление всех связанных записей из подчиненной таблицы при удалении записи в главной таблице, например, при удалении записи об определенном клиенте будут автоматически удалены все записи о его заказах; при снятом флажке вы не сможете удалить запись, пока не удалите все связанные с ней записи.
5. Для связей между таблицами можно разрешить каскадное обновление связанных полей, а флажок Каскадное удаление связанных записей устанавливать не стоит, поскольку при ошибочном удалении клиентов будут удалены данные об их заказах, и вы не сможете получить правильные значения сумм оказанных услуг за определенные периоды.
Рис. 7.18. Изменение параметров связей
6. После установки параметров связи и нажатия кнопки OK на линии связи должны появиться обозначения «один» и «бесконечность» (см. рис. 7.15); если это не так – значит, в выборе полей была допущена ошибка. Ошибочную связь нужно удалить, а затем создать снова.
7. После настройки обеих связей попробуйте ввести записи в таблицу Заказы. Если вы все сделали правильно, то при заполнении полей Клиент и Автомобиль вы сможете выбирать данные из раскрывающегося списка.
Создание подстановки из фиксированного набора значений
После ввода нескольких записей в таблицу Заказы становится ясно, что в поле СостояниеЗаказаприходится вводить одни и те же значения. Для упрощения ввода данных в это поле можно создать еще одну связанную таблицу, но это будет не самый рациональный способ. Для случаев, когда набор значений поля не будет изменяться в процессе использования базы данных, лучше использовать подстановку из фиксированного набора значений.
Для настройки подстановки фиксированных значений выполните следующие действия.
1. Откройте таблицу Заказы в режиме конструктора и выберите для поля СостояниеЗаказа тип данных Мастер подстановок.
2. В первом окне Мастера (рис. 7.19) установите переключатель в положение Будет введен фиксированный набор значений и нажмите кнопку Далее.
3. В следующем окне введите в таблицу список нужных значений (рис. 7.20), после чего нажмите кнопки Далее и Готово.
Рис. 7.19. Первое окно Мастера подстановок
Рис. 7.20. Для создания фиксированного списка подстановки следует ввести нужные значения вручную
4. Сохраните изменения в структуре таблицы, переключитесь в режим таблицы и попробуйте ввести несколько записей с использованием подстановки.
Автоматический ввод текущей даты и времени
При вводе данных в таблицу Заказы неудобно каждый раз вводить текущие дату и время в поле ДатаЗаказа. Для настройки автоматического ввода текущего времени выполните следующие действия.
1. Снова откройте таблицу Заказы в режиме конструктора, затем выберите поле ДатаЗаказа.
2. Щелкните кнопкой мыши в поле ввода свойства Значение по умолчанию и нажмите появившуюся кнопку с изображением троеточия
– будет запущен построитель выражений Access, с помощью которого можно создавать различные выражения с использованием множества встроенных функций.
3. В построителе выражений дважды щелкните кнопкой мыши на значке Функции, выберите категорию Встроенные функции, затем выберите в среднем списке пункт Дата/время, а в правом – функцию Now (Сейчас). Нажмите кнопку Вставить – выбранная функция будет добавлена в выражение (рис. 7.21), после чего нажмите кнопку OK.
Рис. 7.21. Выбор нужной функции с помощью построителя выражений
4. Сохраните изменения в структуре таблицы и попробуйте добавить новую запись в режиме таблицы. Текущая дата и время должны появляться автоматически, но при необходимости их значения можно отредактировать.
Подведение итогов
Вопросы для проверки
□ Опишите связь «один-ко-многим». Что такое первичный ключ, внешний ключ, главная и подчиненная таблица?
□ Почему базу данных из нескольких таблиц лучше сначала спланировать на бумаге?
□ Зачем при разработке базы данных делить одну большую таблицу на несколько мелких?
□ Для каких целей можно использовать Мастер подстановок?
□ Какие параметры связей вы знаете?
Вы создали базу данных из трех связанных таблиц и наполнили ее пробными данными. В следующем уроке будет показано, как выполнять различные операции с данными с использованием запросов.