48387 (Проектирование реляционной базы данных в предметной области), страница 3

Прежде чем заносить данные в таблицы, нужно определить структуру этих таблиц. Под этим понимается не только описание наименований и типов полей, но и ряд других характеристик (например, формат, критерии проверки вводимых данных). Кроме описания структуры таблиц, обычно задаются связи между таблицами. Связи в реляционных базах данных определяются по совпадению значений полей в разных таблицах. Например, клиенты и заказы связаны отношением "один-ко-многим", т. к. одной записи в таблице, содержащей сведения о клиентах, может соответствовать несколько записей в таблице заказов этих клиентов. Если же рассмотреть отношение между преподавателями и курсами лекций, которые они читают, это будет отношение "многие-ко-многим", т. к. один преподаватель может читать несколько курсов, но и один курс может читаться несколькими преподавателями. И последний тип связей между таблицами — это отношение "один-к-одному". Такой тип отношений встречается гораздо реже. Как правило, это бывает в двух случаях: запись имеет большое количество полей, и тогда данные об одном типе объектов разносятся по двум связанным таблицам, или нужно определить дополнительные атрибуты для некоторого количества записей в таблице, тогда создается отдельная таблица для этих дополнительных атрибутов, которая связывается отношением "один-к-одному" с основной таблицей.

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

Рисунок 3.2 Создание описания структуры таблицы

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

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

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

После создания описания структуры таблицы можно перейти в режим непосредственного ввода в нее данных. Важным преимуществом интерфейса СУБД Access является продуманная гибкая система перехода от режима Конструктора к режиму ввода данных в таблицу (Режим таблицы). Такой переход можно осуществить, щелкнув мышью по пиктограмме Вид, расположенной на панели инструментов, либо выбрав функцию меню Вид > Режим таблицы.

Результат построения таблиц для проектируемой базы данных см. в Приложениях 5-8.

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

4. Формирование запросов к базе данных

4.1 Общее понятие запросов

Запрос - это требование на извлечение данных из таблиц базы, на выполнение вычислений над данными, на внесение изменений в базу данных.

Запрос может служить источником данных для форм, отчетов и страниц доступа к данным.

СУБД Access позволяет создавать запросы трех типов: запросы на выборку, перекрестные запросы, запросы на внесение изменений в базу данных.

Запрос на выборку является наиболее часто используемым типом запроса. Его результатом является динамическая таблица, которая может быть просмотрена, проанализирована. Запрос на выборку дает возможность:

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

- выбирать записи, удовлетворяющие условиям отбора;

- осуществлять вычисления над полями базы данных;

- группировать записи и находить для групп записей или для всех записей итоговые значения по некоторым полям с помощью статистических функций: Sum – сумма значений поля, Avg – среднее значений поля, Max – максимальное из значений поля, Min – минимальное из значений поля, Count – число значений поля и др.

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

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

Существует четыре типа запросов на внесение изменений в базу данных:

1) запрос на удаление - удаляет группу записей из одной таблицы или нескольких взаимосвязанных таблиц базы данных, для которых задано каскадное удаление связанных записей;

2) запрос на обновление - служит для изменения значений полей таблицы;

3) запрос на добавление - производит добавление записей из одной таблицы в другую таблицу;

4) запрос на создание таблицы - создает новую таблицу на основе всех или части данных из одной или нескольких таблиц базы данных.

СУБД Access позволяет создавать запросы с помощью Мастеров и с помощью Конструктора. Мастера используются для создания определенных запросов. С помощью Конструктора можно создать любой запрос.

4.2 Создание запроса выбора

Для создания запроса выбора с помощью Конструктора необходимо открыть окно Конструктора запроса, выполнив действия:

Окно БД - объект Запросы - [Создать] - окно Новый запрос - выбрать Конструктор - окно Добавление таблицы - выбрать таблицы-источники запроса - [Закрыть].

Окно Конструктора запроса имеет вид как на рис. 4.1.

Рис. 4.1 Окно Конструктора запроса

Оно разделено на две панели.

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

Нижняя панель есть бланк запроса по образцу (QBE-запроса). Он представлен в виде таблицы, предназначенной для определения структуры результирующей таблицы запроса и задания условий отбора данных из таблиц. Каждый столбец бланка относится к одному полю таблицы. Строки бланка имеют следующее назначение:

• Поле – указывает имена полей, участвующих в формировании запроса;

• Имя таблицы - указывает имена таблиц, которым принадлежат эти поля;

• Сортировка – дает возможность отсортировать записи в результирующей таблице запроса;

• Вывод на экран – позволяет управлять отображением полей в этой таблице;

• Условие отбора – служит для задания условий отбора записей;

• или – позволяет объединять условия отбора логической операцией ИЛИ. При этом условия отбора могут указываться в нескольких строках бланка запроса.

Включение отдельных полей в бланк запроса можно выполнить одним из следующих способов:

• перетащить поле из списка полей в крайнюю слева свободную клетку строки Поле;

• дважды щелкнуть по имени поля в списке полей;

• щелкнуть в клетке строки Поле и из раскрывающегося списка выбрать нужное поле.

Включение в бланк запроса всех полей таблицы можно выполнить, если:

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

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

Для очистки отдельных столбцов бланка запроса используется команда - Правка/Удалить столбцы.

Для очистки всего бланка запроса служит команда - Правка/Очистить бланк.

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

Формирование запроса с применением сортировки.

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

Формирование запроса с условиями отбора.

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

Если выражения вводятся в несколько клеток одной строки Условие отбора, то они автоматически объединяются с помощью логического оператора And. Если выражения вводятся в разные строки бланка запроса, то Access объединяет их логическим оператором Or. Пример создания запроса с условием для создаваемой базы данных см. в Приложении 11-12.

В условии отбора может быть использована конструкция Between (между). Например, Between 100 and 200. Результат построения запроса с использованием конструкции Between см. в Приложении 13-14.

Формирование запроса с вычисляемым полем.

Для создания вычисляемого поля в пустую клетку строки Поле вводится имя вычисляемого поля с двоеточием, после которого – выражение. Например,

СТОИМОСТЬ: [ЦЕНА]*[КОЛИЧЕСТВО]

Если выражение сложное, то для его создания целесообразно использовать построитель выражений.

Для проектируемой базы данных запрос с вычисляемым полем имеет вид – см. в Приложении 15-16.

Формирование запроса с группировкой.

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

Для создания запроса с группировкой выполняется следующее:

• перетаскивается в первую клетку строки Поле то поле, по которому производится группировка записей. Затем перетаскиваются в последующие клетки поля, по которым подводятся итоги;

• вводится команда Вид / Групповые операции. В бланке запроса появляется новая строка Групповая операция, в которой для всех полей указано Группировка;

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

Формирование запроса с параметрами

Если необходимо часто выполнять один и тот же запрос выбора, меняя только в условиях отбора значения полей, то целесообразно создать запрос с параметрами. В строках Условие отбора, или для полей, играющих роль параметров, вводится в квадратных скобках текст приглашения на ввод интересующих пользователя значений этих полей. Этот текст будет выводиться в диалоговом окне "Введите значение параметра при выполнении запроса". Результат построения запроса с параметрами приведен в Приложении 17-18.

4.3 Создание перекрестного запроса

Создание перекрестного запроса с помощью Конструктора начинается с открытия окна Конструктора запроса. В этом окне в бланк запроса последовательно перетаскиваются:

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

• поле, значения которого будут заголовками столбцов перекрестной таблицы;

• поле, по которому подводится итог с использованием статистической функции.

Затем выполняется команда Запрос / Перекрестный. В результате в бланке запроса появятся две новые строки: Групповая операция и Перекрестная таблица. В строке Перекрестная таблица для полей со значениями в роли заголовков строк выбирается из раскрывающегося списка значение Заголовки строк, а для поля со значениями в роли заголовков столбцов – Заголовки столбцов. В строке Групповая операция для поля, по которому подводится итог, из раскрывающегося списка выбирается необходимая статистическая функция.