борей. Борей. Тема. Технология баз данных и личная информационная коллекция

• Название: Тема. Технология баз данных и личная информационная коллекция
• Анкор: борей
• Дата: 25.03.2022
• Размер: 2.33 Mb.
• Имя файла: Борей.doc
• Тип: Лекция; #416610
• страница: 5 из 6

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

Задание. Создать в среде 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. Сохраните изменения в структуре таблицы и попробуйте добавить новую запись в режиме таблицы. Текущая дата и время должны появляться автоматически, но при необходимости их значения можно отредактировать.

Подведение итогов

Вопросы для проверки

? Опишите связь «один-ко-многим». Что такое первичный ключ, внешний ключ, главная и подчиненная таблица?

? Почему базу данных из нескольких таблиц лучше сначала спланировать на бумаге?

? Зачем при разработке базы данных делить одну большую таблицу на несколько мелких?

? Для каких целей можно использовать Мастер подстановок?

? Какие параметры связей вы знаете?

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

Основные сведения о запросах

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

Запросы на выборку всегда возвращают результат в виде таблицы с данными и имеют некоторое сходство с фильтрами (см. урок 7.2), но в сравнении с ними запросы имеют целый ряд дополнительных возможностей:

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

? в запросах можно создавать вычисляемые поля и подсчитывать итоги;

? запросы можно сохранять как отдельные объекты базы данных.

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

ПРИМЕЧАНИЕ

Любой запрос представляет собой набор инструкций на языке SQL (Structured Query Language – язык структурированных запросов), и опытные разработчики могут создавать запросы любого типа с помощью этого языка. Access обеспечивает пользователей простыми средствами для создания запросов. Любой созданный запрос может быть представлен в виде инструкций SQL с помощью команды Режим SQL, которую можно найти в меню кнопки Режим.

Создание простых запросов на выборку

Для создания простых запросов на выборку лучше всего использовать Мастер запросов. Рассмотрим два вида простых запросов:

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

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

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

Пример 1. Простой подробный запрос.

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

1. Выберите на ленте вкладку Создание и нажмите кнопку Мастер запросов в группе Другие.

2. В первом окне Мастера выберите пункт Простой запрос и нажмите кнопку OK.

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


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

4. Нажмите кнопку Далее, в следующем окне оставьте переключатель в положении Подробныйи снова нажмите кнопку Далее.

5. В последнем окне введите имя создаваемого запроса, например Подробные сведения о заказах, и нажмите Готово. Результат запроса будет отображен в виде таблицы (рис. 7.23).



Рис. 7.22. Окно выбора полей для создания запросов



Рис. 7.23. Результат построения подробного запроса

Пример 2. Итоговый запрос.

Нужно создать запрос для отображения сумм заказов, которые ежедневно выполняет каждый водитель. Этот запрос можно построить следующим образом.

1. Запустите Мастер запросов с помощью команды Создание ? Другие ? Мастер запросов и выберите в первом окне Мастера пункт Простой запрос.

2. В следующем окне выберите для запроса следующие поля: Дата Заказа и Сумма Заказа из таблицы Заказы и поле Водитель из таблицы Автомобили. Нажмите кнопку Далее для перехода к следующему окну Мастера.

ВНИМАНИЕ

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

3. В следующем окне установите переключатель в положение итоговый и нажмите кнопку Итоги (рис. 7.24). Для подсчета итоговой суммы установите флажок Sum (Сумма). Другие флажки имеют следующие значения:

• Avg – среднее арифметическое;

• Min – минимальное значение;

• Max – максимальное значение.

Можно также включить подсчет числа записей в таблице Заказы с помощью одноименного флажка. Установив итоги, нажмите кнопку OK, затем кнопку Далее.



Рис. 7.24. Окно установки итогов в Мастере создания запросов

4. В следующем окне Мастера выберите интервал группировки дат по дням, нажмите кнопку Далее, введите имя запроса и нажмите Готово. Результат запроса показан на рис. 7.25.



Рис. 7.25. Результат построения итогового запроса

Работа с конструктором запросов

Широкие возможности для создания запросов имеет конструктор запросов, в котором можно задавать условия отбора по отдельным полям, определять способ сортировки результатов и создавать вычисляемые поля. Для знакомства с конструктором запросов откройте один из ранее созданных запросов с помощью конструктора (рис. 7.26).



Рис. 7.26. Конструктор запросов

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

Пример 3. Подробный запрос с условиями на значение.

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

1. В области переходов найдите и выделите ранее созданный запрос Подробные сведения о заказах и откройте его.

2. Для перевода запроса в режим конструктора нажмите кнопку Режим на вкладке Главная.

3. В окне конструктора запросов введите значение Активный как условие отбора для поля СостояниеЗаказа.

4. Для поля ДатаЗаказа из раскрывающегося списка выберите способ сортировки по возрастанию.

5. Сохраните измененный запрос под именем Активные заказы. Для этого нажмите Кнопку«Office» и выполните команду Сохранить как ? Сохранить объект как.

6. Снова нажмите кнопку Режим для перевода запроса в режим таблицы и просмотра результата работы запроса.

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