Концепция баз данных в Access

КОНЦЕПЦИЯ БАЗ ДАННЫХ В ACCESS

Модель данных Access

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

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

Основным интегрированным объектом Access является база данных.

База данных (БД) – это организованная на машинном носителе совокупность взаимосвязанных данных, которая представляет сведения об объектах определенной предметной области (ПО), их свойствах и связях между ними.

Используемым типом структуры данных является реляционная модель данных (РМД). Термин «реляционная» происходит из теории множеств теоретической основы баз данных, где обозначает специфический тип структуры данных, называемый отношением (отношение, по-английски relation, определяет название модели). Как тип структуры данных отношение допускает наглядное представление данных в виде двумерной таблицы.

Основным элементом структуры БД в Access является таблица, с помощью которой моделируются объекты и связи между ними. Следовательно, БД в Access представляет собой совокупность взаимосвязанных двумерных таблиц.

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

Содержание таблицы заключено в её строках. Каждая строка содержит данные об одном экземпляре объекта или связи и называется записью.

Для однозначного определения каждой записи (экземпляра объекта) таблица должна иметь уникальный ключ (первичный ключ). Ключ может состоять из одного или нескольких полей.

Таблицы в БД взаимосвязаны. Связь каждой пары таблиц, в которой одна является «отцом», а другая – «сыном», обеспечивается ключом связи (внешним ключом). Внешний ключ – это первичный ключ таблицы-отца, мигрировавший в таблицу-сына. Связи между таблицами могут быть двух типов: «один к одному» (1:1) или «один ко многим» (1:N).

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

Лабораторная работа №1. СОЗДАНИЕ БАЗЫ ДАННЫХ

Объекты базы данных

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

Формы и отчеты представляют собой средства разработки интерфейса конечного пользователя БД.

Страницы – средства доступа к данным через Internet.

Макросы и модули – это средства автоматизации работы с БД в эргономической среде пользователя.

Работа с БД может быть прервана в любой момент путем закрытия окна

БД или файла БД (по команде Файл | Закрыть) и продолжена путем открытия файла БД (по команде Файл | Открыть).

Продолжение работы с БД всегда начинается с окна БД (рис. 1). Для работы с тем или иным объектом БД нужно выбрать соответствующую закладку и нажать экранную кнопку:

– для просмотра результата проектирования объекта;

– для изменения структуры объекта;

– для создания объекта «с нуля».

 

Рис. 1. Окно базы данных Access

ПРОЕКТИРОВАНИЕ ТАБЛИЦ

Типы данных

 

Типы данных	Диапазон значений	Размер в байтах
Текстовый	0 ÷ 255
Числовой (байт)	0 ÷ 255
Числовой (целое)	-32768 ÷ 32767
Числовой (длинное целое)	-2147483468 ÷ 2147483467
Числовой (с плав. точкой)	-3.402823 1038 ÷ 3.402823 1038
Числовой (двойной точности)	-1.797693134862232 10308 ÷ 1.797693134862232 10308
Логический	Да / Нет
Дата/Время 100 ÷ 9999 гг.

Продолжение таблицы 1

Денежный	15,4
Счетчик
Поле MEMO
OLE-объект
Гиперссылка

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

Тип «Счетчик» может быть присвоен не более чем одному полю.

Данные этого типа являются уникальными номерами записей и не могут быть изменены или удалены.

Тип «Денежный» используется для описания данных, над которыми выполняются финансовые операции с высокой точностью вычисления. Точность представления данных этого типа составляет до 15 знаков в целой части и 4 – в дробной.

Тип «поле MEMO» используется для полей, содержащих пространную текстовую информацию.

Тип «поле объекта OLE» присваивается полям-объектам других приложений Windows (рисунок, фотография, звуковая запись, анимация и т. д.).

Тип «Гиперссылка» используется для полей, содержащих адрес удаленного источника данных.

В нижней части окна на закладке «Общие» могут быть заданы свойства полей, которые зависят от типа данных:

1) размер поля (для числовых полей возможна детализация);

2) формат поля (внешняя форма представления данных) при помощи специальных символов форматирования;

3) маска ввода в виде символов, управляющих вводом данных;

4) подпись – псевдоним поля;

5) значение по умолчанию;

6) условие на значение в виде ограничений на допустимые значения поля;

7) сообщение об ошибке, сопровождающее нарушение условия на значение;

8) обязательное поле – поле, которое не может принимать не определенное значение (т.н. Null-значение);

9) индексированное поле – поле, по которому предполагается упорядочение строк таблицы.

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

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

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

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

Таблица 2

Спецификация запросов

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

Запросы к БД можно разделить на две категории.

1. Запросы-выборки осуществляют только извлечение информации из одной или нескольких связанных таблиц и представление её в виде виртуальной (не существующей на самом деле) таблицы. Структура такого рода таблицы определяется локальным представлением о ПО конечного пользователя БД в контексте запроса.

2. Корректирующие запросы или запросы на добавление, удаление и изменение данных в БД. Это весьма значительные по своим последствиям запросы, так как их реализация приводит к изменению хранимой в БД информации. Такого рода запросы соответствуют функциям администрирования БД.

БД одной и той же ПО может быть представлена в виде одной таблицы («универсального отношения») или в виде совокупности связанных таблиц.

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

В Access (как и во многих других СУБД) используются языки запросов двух типов:

1. QBE (Query By Example) – запрос по образцу. Запрос формируется в режиме диалога путем заполнения таблицы специального вида. QBE- технология дает пользователю визуальное представление достаточно сложного запроса.

2. SQL (Structured Queries Language) – язык структурированных запросов. Запрос формируется в терминах команд этого языка (т. е. нужно знать язык SQL). В настоящее время SQL-технология запросов получила широкое распространение в системах БД на основе распределенных систем, в компьютерных сетях.

ЗАПРОСЫ ВЫБОРКИ

 

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

Вывод всех полей

Задание 4. Сформируйте запрос-выборку всех полей таблицы «Учёба» БД УЧЕБА:

1) перенесите мышью в строку «Поле» таблицы запроса имена всех полей из списка в верхней части таблицы;

2) включите щелчком мыши по квадратикам в строке «Вывод на экран» режим просмотра соответствующих полей – спецификация запроса примет вид таблицы, приведенной на рис. 6;

3) завершите результаты формирования запроса так. Результаты запроса представлены на рис. 7;

4) перейдите в SQL-режим по команде Вид | Режим SQL, чтобы получить представление о структуре SQL-запроса;

5) сохраните запрос под именем «Все поля».

Рис. 6. Структура QBE - запроса «Все поля»

 

Рис. 7. Результаты запроса «Все поля»

Выборка полей

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

Задание 5. Сформируйте запрос-выборку некоторых полей таблицы «Учёба» БД УЧЕБА.

1) сформируйте запрос, в котором присутствовали бы следующие поля: «номер студента», «фамилия», «специальность», «факультет» (технология построения запроса такая же, как в примере «Все поля», отличается только количеством полей);

2) просмотрите результаты запроса. Обратите внимание на присутствие в итоговой таблице дублирующих записей;

3) назовите запрос «Дубли».

Выборка по значению поля

Задание 7. Для выбора всех данных о студентах, обучающихся на факультете «экономический»:

1) выберите все поля в структуру запроса, как в примере «Все поля»;

2) в поле «факультет» в строке «Условие отбора» наберите вручную значение «экономический» (рис. 10);

3) завершите результаты формирования запроса;

4) назовите запрос «Селекция».

Рис. 10. Структура запроса-выборки по значению поля

Параметрический запрос

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

Задание 8. Сформируйте параметрический запрос всех данных о результатах сдачи экзаменов по предмету, заданному его названием:

1) выберите все поля в структуру запроса, как в примере «Все поля»;

2) в поле «предмет» в строке «Условие отбора» наберите в квадратных скобках текст подсказки к вводу конкретного значения (рис. 11);

Рис. 11. Структура параметрического запроса

 

3) завершите результаты формирования запроса;

4) в поле диалогового окна, которое появится при выполнении запроса, введите конкретное значение, например «математика» (рис. 12);

5) сохраните запрос под именем «Параметрический».

Рис. 12. Диалоговое окно параметрического запроса

 

Сортировка записей

 

Изначально записи-строки таблицы БД оказываются отсортированными по возрастанию ключа. Если по смыслу запроса требуется рассортировать записи по другому критерию, то формируют запрос на сортировку. Сортировка может выполняться по возрастанию или по убыванию значений поля. Сортировка может выполняться по нескольким полям. В этом случае порядок следования полей в структуре запроса определяет порядок сортировки (вложенная сортировка). Сначала выполняется сортировка по первому полю. В пределах внешней сортировки выполняется сортировка по второму полю и т. д.

Задание 17. Постройте запрос на сортировку результатов сдачи экзаменов в таблице «Учеба» по факультетам (по полю «факультет» по возрастанию), а в пределах факультета по специальностям (по полю «специальность» по убыванию):

1) перенесите все поля в структуру запроса, как в предыдущем примере;

2) в структуре запроса поменяйте местами поля «факультет» (первое поле) и «специальность» (второе поле);

3) в поле «факультет» в строке «Сортировка» мышью включите список способов сортировки и выберите «По возрастанию» (по алфавиту);

4) в поле «специальность» в строке «Сортировка» мышью включите список способов сортировки и выберите «По убыванию»;

5) завершите результаты формирования запроса;

6) сохраните запрос под именем «Сортировка».

Лабораторная работа №3

 

Тема: Закрепление изученного материала

 

Задания для самостоятельной работы

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

1. Создать базу данных в соответствии с вариантом.

2. Определить ключевые поля.

2. Заполнить таблицу данными. Количество записей должно быть не менее 20.

3. Продумать и выполнить следующие типы запросов-выборок:

1) проекция на подмножество однородных атрибутов;

2) параметрический запрос на примере селекции по условию на основе операций сравнения (равно, не равно, больше, меньше, больше или равно, меньше или равно);

3) запрос-выборка, селекция по сложному условию на основе оператора конъюнкции And;

4) запрос-выборка, селекция по сложному условию на основе оператора дизъюнкции Or;

5) запрос-выборка, селекция по условию на основе оператора Between…And;

6) запрос-выборка, селекция по условию на основе оператора Like;

7) запрос-выборка, селекция по условию на основе оператора In;

8) запрос-выборка, содержащий вычисляемое поле;

9) запрос-выборка с сортировкой по двум-трем полям;

10) запрос-выборка с группировкой данных и вычислением статистических функций Count, Sum, Avg, Min, Max и др.

 

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

По данной лабораторной работе сформировать отчет с подробным описанием всех действий.

 

Вариант I. База данных Поставки (номер поставщика, фамилия поставщика, город, код детали, город, код детали, количество за месяц).

 

Вариант II. База данных Больница (номер пациента, фамилия пациента, адрес, номер хирурга, фамилия хирурга, дата операции, тип операции).

 

Вариант III. База данных Библиотека (номер абонента, фамилия абонента, номер книги, автор, название, год издания, дата выдачи, дата возврата).

Вариант IV. База данных Аренда (номер клиента, фамилия клиента, номер объекта, плата, фамилия собственника, дата начала, дата конца).

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

Запрос на создание таблицы

Запрос на создание новой таблицы как проекции исходной рассмотрим в контексте проблемы декомпозиции «универсального» отношения. Выполним декомпозицию «универсального» отношения со схемой:

Учёба (номер студента, фамилия, специальность, факультет, предмет, семестр, оценка)

на ее проекции со схемами:

Кафедра (специальность, факультет)

Экзамен (номер студента, фамилия, факультет, предмет, семестр, оценка)

Рассмотрим, как в Access реализуется операция проекция–создание одной таблицы как проекции другой. Алгоритм выполнения этой процедуры состоит из трёх этапов:

1. Построение запроса-выборки столбцов-атрибутов без дублей строк (Задание 6 на стр. 21]. Тем самым создается проекция – подмножество данных исходной таблицы, которое определит структуру и наполнение новой таблицы.

2. Выполнение запроса на создание новой таблицы.

3. Определение ключа вновь созданной таблицы.

Задание 1. На основе исходной таблицы «Учёба» со схемой отношения:

Учёба (номер студента, фамилия, специальность, факультет, предмет, семестр, оценка )

создайте две новые таблицы со схемами:

Кафедра (специальность, факультет)

Экзамен (номер студента, фамилия, факультет, предмет, семестр, оценка )

Алгоритм построения и выполнения запроса на создание новой таблицы рассмотрим на примере отношения «Кафедра»:

1) в режиме Конструктора запросов в качестве таблицы-источника данных к запросу выберите таблицу «Учёба»;

2) сформируйте запрос-выборку, в котором присутствовали бы следующие поля таблицы «Кафедра»: специальность, факультет;

3) в режиме Конструктора выполните команду Вид | Свойства;

4) в диалоговом окне «Свойства запроса» переключите мышью свойство «Уникальные значения» в состояние «Да»;

5) завершите результаты формирования запроса (на этом первая часть алгоритма заканчивается);

6) в режиме Конструктора выполните команду Запрос | Создание таблицы;

7) в диалоговом окне «Свойства запроса» введите в поле «Таблица» имя новой таблицы «Кафедра» (рис. 21);

8) в режиме Конструктора выполните команду Запрос | Запуск. В новую таблицу будет скопировано соответствующее количество записей

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

Рис. 21. Окно наименования новой таблицы

 

10) в окне БД выберите закладку Таблицы и убедитесь, что новая таблица «Кафедра» представлена в списке таблиц БД; это является признаком успешного выполнения запроса на создание таблицы;

11) выберите новую таблицу «Кафедра» в списке и нажмите экранную кнопку;

12) в режиме Конструктора таблиц в окне структуры таблицы определите ключевые атрибуты (в таблице «Кафедра» это поле «специальность»);

13) сохраните результаты спецификации новой таблицы «Кафедра» по команде Файл | Сохранить. На этом заканчивается третья часть алгоритма;

14) просмотрите в Режиме таблицы наполнение новой таблицы «Кафедра» (рис. 22);

15) по рассмотренному алгоритму создайте вторую таблицу «Экзамен».

Рис. 22 Наполнение таблицы «Кафедра»

 

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

В результате декомпозиции «универсального» отношения «Учеба» мы получили представление той же самой предметной области в виде совокупности двух отношений – таблиц «Кафедра» и «Экзамен». Теперь установим связь между новыми таблицами.

Правила установления связей между таблицами в Access таковы, что разрешены только бинарные (между двумя таблицами) связи. Связи могут быть двух типов «один к одному» и «один ко многим». Причём одна таблица выступает в роли старшей (отцовской), например таблица «Кафедра» в отношении «Кафедра»: «Экзамен», а другая – в роли младшей (сыновьей), например таблица «Экзамен» в отношении «Кафедра»: «Экзамен». Однако одна и та же таблица в разных отношениях может выступать в роли старшей и младшей одновременно. Связь между таблицами устанавливается по общим полям-атрибутам (поле «факультет» в нашем случае). Связь между таблицами Access устанавливается при помощи инструмента, называемого «Схемой данных».

Задание 2. Для установления связи между таблицами «Кафедра» и «Экзамен»:

1) выполните команду Сервис | Схема данных – появится окно макета схемы данных БД;

2) в контекстном меню выполните команду Связи | Добавить таблицу;

3) в окне «Добавление таблицы» выберите в списке таблицу «Кафедра» и нажмите экранную кнопку ;

4) в окне «Добавление таблицы» выберите в списке таблицу «Экзамен» и нажмите экранную кнопку ;

5) для установления связи между таблицами «Кафедра» и «Экзамен» захватите мышью и перенесите поле «факультет» таблицы «Кафедра» на поле «факультет» таблицы «Экзамен» – раскроется окно «Изменение связей» (рис. 23);

6) не настраивая свойства связи, нажмите экранную кнопку.

Схема базы данных примет вид, аналогичный представленному на рис. 24.

Рис. 23. Окно спецификации связей в схеме данных

Рис. 24. Схема данных двух таблиц

Запрос на соединение таблиц

 

Естественное соединение – это реляционная операция, которая порождает итоговую таблицу как соединение исходных таблиц по условию равенства однотипных (одноименных) полей-атрибутов без дублей одноименных столбцов [1–5].

Задание 3. Создайте запрос на соединение двух независимых таблиц «Кафедра» и «Экзамен»:

1) в окне БД выберите закладку Запрос и нажмите экранную кнопку ;

2) в окне «Новый запрос» выберите вариант Конструктор;

3) в окне «Добавление таблицы» выберите по закладке источник данных к запросу: Таблицы | Запросы | Таблицы и Запросы; в данном случае это Таблицы;

4) выберите в списке таблицы «Кафедра» и «Экзамен» и нажмите экранную кнопку ;

5) нажмите экранную кнопку – появится основное окно конструктора запросов в стиле QBE (рис. 25). Обратите внимание на связи между одноименными атрибутами разных таблиц в верхней части бланка запроса;

6) перенесите в структуру запроса в нижней части из разных таблиц разноименные поля (рис. 25);

7) выполните запрос по команде Запрос | Запуск – результат запроса будет выглядеть, как на рис. 26;

8) завершите результаты формирования запроса так, как это обсуждалось ранее;

9) сохраните результат под именем «Естественное соединение».

Обратите внимание на важный результат: итоговая таблица (рис. 26) по запросу на соединение двух таблиц не совпадает с исходной таблицей «Учеба» по числу строк. В ней присутствуют лишние строки, которые содержат семантически некорректные данные, например о том, что студенты учатся по двум специальностям. Из полученного результата следует важный вывод: декомпозиция таблицы-отношения «Учёба» выполнена некорректно.

Рис. 25. Структура запроса на соединение таблиц «Кафедра» и «Экзамен»

 

Рис. 6. Результат запроса на соединение таблиц «Кафедра» и «Экзамен»

Корректная декомпозиция

 

Декомпозиция исходного отношения (таблицы) на его проекции считается корректной, если выполняются два условия:

1. Сохранение исходных данных в результате естественного соединения компонентов декомпозиции.

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

Из теории БД, занимающейся проблемой оптимизации структуры данных, следует, что для реализации корректирующих запросов на добавление, удаление и изменение данных предпочтительнее представление БД в виде не одной таблицы («универсального» отношения), а виде совокупности так называемых нормализованных таблиц-отношений. Проблемы нормализации отношений здесь не обсуждаются. Достаточно понять, что в основе нормализации таблиц-отношений лежит процедура декомпозиции ненормализованного «универсального» отношения на её нормализованные компоненты, которые объединяют семантически однородные данные.

Задание 4. Выполните декомпозицию «универсального» отношения со схемой:

Учёба (номер студента, фамилия, специальность, факультет,предмет, семестр, оценка)

на ее проекции со схемами:

Кафедра (специальность, факультет)

Студент (номер студента, фамилия, специальность)

Сессия (номер студента, предмет, семестр, оценка)

Таблица «Кафедра» уже создана, поэтому:

1) создайте остальные таблицы по алгоритму, описанному на стр. 26;

2) установите связи между таблицами так, как обсуждалось на стр. 28;

3) проверьте корректность декомпозиции по новой схеме как результат операции естественного соединения трех таблиц (см. стр. 29).

Итоговые таблицы «Студент» и «Сессия» должны выглядеть так, как показано на рис. 27 и 28.

Схема данных нового варианта БД на основе трех таблиц должна выглядеть, как на рис. 29.

Структура запроса на естественное соединение таблиц «Кафедра», «Студент» и «Сессия» представлена на рис. 30.

Результат запроса на естественное соединение таблиц «Кафедра», «Студент» и «Сессия» приведен на рис. 31.

Рис. 27. Наполнение таблицы «Студент»

 

Рис. 28. Наполнение таблицы «Сессия»

Рис. 29. Схема данных трех таблиц

 

Рис. 30. Структура запроса на соединение трех таблиц

Рис. 31. Результат запроса на естественное соединение трех таблиц

 

Обратите внимание на важнейший результат: результирующая таблица (рис. 11) по запросу на соединение трех таблиц с точностью до порядка следования строк и столбцов (что не существенно) совпадает с исходной таблицей «Учёба» в [6, стр. 18], из которой новые таблицы были получены как её проекции.

Из полученного результата следуют важные выводы:

1. Декомпозиция таблицы-отношения «Учёба» на её компоненты- проекции «Кафедра», «Студент» и «Сессия» выполнена корректно, т. е. Без потери данных.

2. Представления предметной области (ПО) «Учёба» в виде одноименного «универсального» отношения и в виде комбинации нормализованных отношений- таблиц «Кафедра», «Студент» и «Сессия» эквивалентны. Отныне наша база данных УЧЕБА будет представлена не одной таблицей «Учеба», а комбинацией нормализованных связанных таблиц «Кафедра», «Студент» и «Сессия». Все простые запросы-выборки, которые были выполнены ранее на примере одной таблицы «Учеба», теперь с равным успехом могут быть воспроизведены на основе связанных таблиц. Рассмотрим в качестве примера запрос на группировку данных.

 

Задание 5. Допустим, что для каждого студента из БД УЧЕБА, представленной теперь тремя таблицами, требуется подсчитать количество сданных экзаменов и средний балл по результатам сдачи всех экзаменов.

Сформируйте запрос на выборку с группированием данных из связанных таблиц «Студент» и «Сессия» и вычислением групповых характеристик:

1) в окне БД на закладке Запрос приступите к созданию нового запроса (нажмите экранную кнопку);

2) в окне «Новый запрос» выберите вариант Конструктор;

3) в окне «Добавление таблицы» по закладке Таблицы выделите в списке таблицы «Студент» и «Сессия» и нажмите экранные кнопки и;

4) в окне конструктора запросов выберите в структуру запроса поля

«номер студента», «фамилия» (из таблицы «Студент»), «оценка», «оценка» (из таблицы «Сессия»);

5) выполните команду Вид | Групповые операции; в структуре запроса в строке «Групповая операция» для полей «номер студента» и «фамилия» включите настройку «Группировка»;

6) для первого поля «оценка» в строке «Групповая операция» выберите из списка функцию подсчета количества Count, для второго поля –функцию вычисления среднего значения Avg (рис. 32);

7) в таблице результата запроса полям, по которым вычисляются групповые характеристики, присвойте содержательные имена «количество экзаменов» и «средний балл»;

8) результаты запроса будут иметь вид, как на рис. 33;

9) сохраните запрос под именем «Группировка2»

Рис. 32. Структура запроса на группирование данных из связанных таблиц

 

Рис. 33. Результаты запроса на группирование данных из связанных таблиц

 

Сравните полученные результаты с результатами аналогичного запроса к одной таблице. Совпадение очевидно.

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

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

 

 

КОРРЕКТИРУЮЩИЕ ЗАПРОСЫ

 

Выполнение корректирующих запросов

В результате корректной декомпозиции отношения «Учеба» мы развели семантически однородные данные по разным таблицам. Тем самым мы решили проблему аномалий манипулирования данными и создали предпосылки для корректного выполнения корректирующих запросов. Корректирующие запросы бывают трех типов:

1) запросы на добавление данных;

2) запросы на удаление данных;

3) запросы на обновление данных.

Алгоритм выполнения этих запросов состоит из двух этапов [7–10]:

1. Построение запроса-выборки строк-записей, подлежащих добавлению, удалению или обновлению. Тем самым создается подмножество корректируемых данных.

2. Выполнение корректирующего запроса.

Продемонстрируем эти типы запросов на примере таблицы «Студент».

 

Запрос на удаление

Задание 6. Создайте запрос на удаление из таблицы «Студент» данных о студенте, заданном его фамилией.

1) в качестве таблицы-источника данных к запросу выберите таблицу «Студент»;

2) сформируйте запрос-выборку всех полей по условию, задаваемому параметрически; в поле «фамилия» в строке «Условие отбора» наберите в квадратных скобках текст подсказки к вводу конкретного значения [Фамилия студента];

3) выполните запрос по команде Запрос | Запуск; в диалоговом окне, которое появится при реализации запроса, введите в кавычках конкретное значение, например «Андреев А.А.»;

4) просмотрите результаты запроса в Режиме таблицы и примите решение, удовлетворяют ли вас результаты выборки или нет;

5) вернитесь в режим Конструктора. На этом первая часть алгоритма заканчивается;

выборка данных на удаление подготовлена;

6) в режиме Конструктора выполните команду Запрос | Удаление – в бланке структуры запроса появится строка «Удаление» (рис. 34);

Рис. 34. Структура запроса на удаление данных из таблицы

7) выполните запрос по команде Запрос | Запуск;

8) завершите результаты формирования запроса. На этом заканчивается вторая часть алгоритма;

9) в окне БД на закладке Таблицы выберите таблицу «Студент» и посмотрите её наполнение. Убедитесь, что удаление записи действительно произошло.

Запрос на добавление

Задание 7. Речь идет о добавлении данных в одну таблицу из другой. Создайте запрос на добавление данных о студенте, заданном его фамилией из таблицы «Учёба» в таблицу «Студент»:

1) в качестве таблицы-источника данных выберите таблицу «Учёба»;

2) сформируйте запрос-выборку полей, которые должны быть добавлены

(«номер студента», «фамилия», «специальность») по условию, задаваемому параметрически; в поле «фамилия» в строке «Условие отбора» наберите в квадратных скобках текст подсказки к вводу конкретного значения [ Фамилия студента ];

3) чтобы избавиться от дублей строк в выборке для добавления в таблицу «Студент» дополните условие выборки требованием «без дублей записей». Для этого в режиме Конструктора выполните команду Вид | Свойства;

4) в диалоговом окне «Свойства запроса» переключите мышью свойство

«Уникальные значения» в состояние «Да»;

выполните запрос по команде Запрос | Запуск; в диалоговом окне, которое появится при реализации запроса, введите в кавычках конкретное значение, например «Андреев А.А.»;

просмотрите результаты запроса в Режиме таблицы и примите решение, удовлетворяют ли вас результаты выборки или нет;