Фильтрация и выборка данных

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

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

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

В MS Access поиск данных осуществляется в трех режимах: Фильтр по выделенному, Изменить фильтр и Расширенный фильтр.

Выбор режима Фильтр по выделенному или Изменить фильтр осуществляется с помощью кнопок панели управления Таблица в режиме таблицы соответственно или же с помощью команды Записи (рис.27). Использование Расширенного фильтра возможно только по команде Записи. Для активизации фильтрации или отмены ее результатов используется кнопка (Применение фильтра) панели управления Таблица в режиме таблицы.

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

В режиме фильтрации Фильтр по выделенному можно выполнить поиск данных по одному полю. Поэтому, если условие отбора информации более сложное, т.е. предполагает поиск данных одновременно по двум и более полям, следует применить механизм столько раз, сколько полей задействовано в критерии. Причем источником информации второй фильтрации является результат предыдущей и т.д. Такое последовательное применение Фильтра по выделенному позволяет реализовать сложный критерий отбора, объединяющий простые условия соответствия конкретному значению данных разных полей с помощью логической функции И (And).

 

Режим фильтрации Изменить фильтр позволяет найти данные в таблице по более сложному условию отбора. Результатом выбора этого режима является появление на экране бланка фильтра. Он содержит все заголовки полей активной таблицы. На рис.28 представлен чистый бланк фильтра для таблицы Студенты.

Если бланк содержит какие-либо записи, его можно очистить с помощью кнопки (Очистить бланк) панели инструментов Фильтр.

С помощью бланка может формироваться сложное логическое выражение для поиска данных активной таблицы. Условия отбора записываются в одной или разных строках, объединение условий осуществляется с помощью логической функции И (And) или ИЛИ (Or) соответственно.

Условия поиска записываются на вкладке Найти окна бланка фильтра. Последующие условия, объединяемые с предыдущими с помощью логической функции ИЛИ (Or), записываются на вкладке ИЛИ.

На рис. 29 представлен бланк фильтра для поиска данных в таблице Студенты о студентах первого курса двух факультетов: ГЭФ и МТФ.

 

В результате выполнения фильтрации рис.30 в таблице Платежи будут отобраны данные о платежах меньше 20 000 руб. за все годы и о платежах 2008 года в размере от 30 000 до 1 000 000 руб.

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

На рис.31 представлен бланк фильтра такой же, как и на рис.29. Во-первых, в бланке отображаются все условия фильтрации, во-вторых, использована возможность сортировки результатов фильтрации по полю Факультет, Группа, Фамилия, Имя иОтчество. Результат фильтрации представлен на рис.32.

 

Схема данных

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

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

Всего имеются три типа связей (отношений): 1-1 (один-к-одному), 1 -¥ (один-ко-многим) и ¥ -¥ (многие-ко-многим).

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

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

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

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

Ключ - это уникальный идентификатор (имя поля), который однозначно определяет запись в таблице.

Ключ задается при формировании структуры таблицы. Различают первичные (уникальные) или вторичные; простые и составные ключи.

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

Простой ключ состоит из одного поля, а составной - из комбинации полей.

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

 

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

 

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

 

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

 

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

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

- Открыть окно Схема данных, выбрав команду меню Правка - Схема данных или щелкнув по одноименной кнопке на панели инструментов База данных. Если окно открывается не впервые для данной базы данных, оно выглядит точно так, как было сохранено. При первом установлении связи окно Схема данных пустое.

- Добавить таблицы, активизировав команду меню Связи - Добавить таблицу.

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

При построении схемы данных по выбранному полю Access автоматически определяет тип связи между таблицами:

§ Если поле, по которому устанавливается связь, является уникальным ключом как в главной таблице, так и в подчиненной, устанавливается связь один-к-одному.

§ Если поле связи является уникальным ключом в главной таблице, а в подчиненной таблице - вторичным ключом, входит в составной ключ или является не ключевым, устанавливается связь один-ко-многим от главной таблицы к подчиненной. На рис.33 изображена схема данных, связывающая две таблицы Студенты и Платежи. В главной таблице Студенты ключевым полем является поле КодСтудента. Поле с таким же названием в таблице Платежи является не ключевым. Автоматически Access установил связь между таблицами один-ко-многим.

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

§ Если в качестве поля связи в главной таблице выбрано не ключевое поле, Access выдает сообщение о невозможности определения типа отношения. В этом случае между таблицами можно установить только связи-объединения. Такая связь объединяет записи двух таблиц, имеющих одинаковые значения в поле связи. Объединение каждой записи из одной таблицы производится с каждой записью из другой таблицы только при условии равенства значений в поле связи. Результатом объединения записей, который определяется выбором одного из трех способов, может быть:

- объединение только тех записей, в которых связанные поля обеих таблиц совпадают;

- объединение тех записей, в которых связанные поля обеих таблиц совпадают, а также объединение всех записей из первой таблицы(для которых нет связанных во второй) с пустой записью второй таблицы;

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

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

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

- в главной таблице нельзя удалить запись, если не удалены связанные с ней записи в подчиненной таблице;

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

При попытке пользователя нарушить эти условия в операциях обновления и удаления данных в связанных таблицах Access не допускает выполнения операции.

 

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

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

- обе таблицы сохраняются в одной базе данных Access;

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