Основные принципы проектирования базы данных

ВВЕДЕНИЕ В БАЗЫ ДАННЫХ

 

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

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

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

 

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

 

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

Наиболее важными аспектами БД являются:

* целостность и согласованность данных;

* защита от случайных потерь или разрушений данных;

* один уровень обновления повторяющихся записей;

* защита данных от несанкционированного доступа;

* способность к расширению и возможность обеспечения изменяющихся требований к данным.

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

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

Информационный объект - идентифицируемый объект реального мира, некоторое понятие или процесс, относящийся к предметной области, о которой хранятся данные.

Характеристикой объекта является элемент данных или идентификатор, который определяется именем и совокупностью некоторых значений. Элементы данных часто называются полями, которые выступают в роли ключей или атрибутов.

Ключ является идентификатором, уникально определяющим запись об объекте.

Атрибут - элемент данных, не являющийся ключом.

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

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

 

Понятие и функции СУБД

 

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

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

СУБД включают в себя три основных типа функций:

* определение (задание структуры и описание) данных,

* обработка данных,

* управление данными.

Все эти функциональные возможности в полной мере реализованы в Microsoft Access.

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

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

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

 

Реляционные базы данных

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

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

Отношение (relation) -это информация об объектах одного типа. В реляционных базах данных отношения хранятся в виде таблиц.

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

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

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

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

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

Предшественниками реляционных БД были иерархические и сетевые базы данных. В иерархических базах данных информация хранится в виде иерархий. Сетевая БД характеризуется внутренними ссылками между структурами данных.

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

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

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

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

 

1.4. Реляционные связи между таблицами баз данных

 

Отображение - традиционное средство для определения характера взаимосвязи между парами связанных элементов данных.

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

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

* "один-ко-многим";

* "один-к-одному";

* "многие-ко-многим".

 

Отношение "один-ко-многим".

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

случае сущность связи между таблицами остается неизменной.

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

 

Отношение "один-к-одному".

Отношение "один-к-одному" имеет место, когда одной записи в родительской таблице соответствует одна запись в дочерней таблице.

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

 

Отношение "многие-ко-многим".

Отношение "многие-ко-многим" имеет место, когда

* одной записи в родительской таблице может соответствовать более одной записи в дочерней таблице;

* одной записи в дочерней таблице может соответствовать более одной записи в родительской таблице.

Считается, что всякая связь "многие-ко-многим" может быть заменена на одну или более связей "один-ко-многим".

 

Пример проектирование базы данных

 

1. Определение задачи

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

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

* регистрация покупок;

* расчет стоимости при оформлении покупок;

* регистрация постоянных покупателей;

* выпуск каталогов продукции;

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

* анализ ежеквартальных объемов продаж;

* расчет прибыли.

 

2. Отбор необходимых данных и выбор объектов базы данных.

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

Например, для регистрации покупателей (объект ПОКУПАТЕЛИ) потребуются следующие элементы данных: Имя покупателя, Название фирмы, Город.

Для описания товаров (объект ТОВАРЫ) используются такие элементы данных: Название товара, Тип товара, Цена.

Для регистрации покупок (объект ОТПУСК) потребуются такие элементы данных: Порядковый номер покупки, Купленный товар, Дата покупки, Количество купленного товара, Покупатель.

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

 

3. Нормализация таблиц. Первая нормальная форма.

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

* каждое поле является неделимым, т.е. значение поля не делится на более мелкие значения. Если бы в структуре таблицы ПОКУПАТЕЛИ элементы данных Фирма и Город были бы объединены в один элемент данных Адрес, тогда можно было бы говорить о нарушении требований первой нормальной формы.

* нет повторяющихся групп, т.е. полей, содержащих одинаковые по по смыслу значения. Если бы в таблице ОТПУСК присутствовали элементы Товар1, Товар2 и т.д., они бы составили повторяющиеся группы.

 

4. Создание первичного ключа.

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

Для таблицы ТОВАРЫ Название не может служить первичным ключом, т.к. товары разных типов могут иметь одинаковые названия, поэтому введем первичный ключ КодТовара. Также и в таблице ПОКУПАТЕЛИ ни Имя, ни Фирма, ни Город не

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

 

5. Связь между таблицами.

Установим связи между таблицами. Один покупатель может неоднократно покупать товары. Поэтому между таблицами ПОКУПАТЕЛИ и ОТПУСК имеется связь "один-ко-многим" по полю Покупатели. Каждый покупатель может приобрести несколько

различных товаров. Поэтому между таблицами ТОВАРЫ и ОТПУСК имеется связь "один-ко-многим" по полю Товар.

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

Для организации связи между таблицами ТОВАРЫ и ОТПУСК поместим копию поля КодТовара из таблицы ТОВАРЫ в таблицу ОТПУСК. Для организации связи между таблицами ПОКУПАТЕЛИ и ОТПУСК поместим копию поля КодПокупателя из таблицы

ПОКУПАТЕЛИ в таблицу ОТПУСК. Для таблицы ОТПУСК поля КодПокупателя и КодТовара являются чужими ключами.

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

одновременно в родительской и дочерней таблицах.

 

6. Транзитивные зависимости.

Третья нормальная форма требует, чтобы в таблице не было транзитивных зависимостей между неключевыми полями. Но таких зависимостей в наших таблицах и нет. Если бы в таблицу ОТПУСК включить поле Стоимость, которое вычисляется

как произведение Количества на Цену, вот тогда можно говорить о наличии транзитивных зависимостей.

Итоговая структура таблиц базы данных:

 

Таблица ПОКУПАТЕЛИ Таблица ТОВАРЫ Таблица ОТПУСК

КодПокупателя КодТовара Номер покупки

Имя покупателя Название товара КодПокупателя

Название фирмы Тип товара КодТовара

Город Цена Количество

Дата

 

Определение таблицы

 

Для создания новой базы после запуска Microsoft Access нужно выбрать пункт:

Создание базы данных / Новая база данных / ОК

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

модули.

В режиме определения новой таблицы окно базы данных должно быть активным. Последовательность действий:

Таблица / Создать / Конструктор / ОК

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

каждого поля определенных свойств, зависящих от выбранного типа данных.

 

Определение полей

В режиме конструктора в столбце Поле указывается имя поля. Несмотря на то, что в Microsoft Access при задании имен можно использовать символ "пробел", лучше все-таки использовать имена полей без пробелов. Большинство баз

данных SQL, к которым Microsoft Access может присоединять свои таблицы, не поддерживают использование пробелов при задании имен полей.

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

 

Типы данных.

Access поддерживает восемь типов данных:

Текстовый - для хранения алфавитно-цифровых данных объемом не более 255 байт;

Memo - для хранения алфавитно- цифровых данных большого объема (до 64 000 байт), например, предложения, абзацы, тексты;

Числовой - для хранения числовых значений длиной 1, 2, 4 или 8 байт (байт, целое, длинное целое, с плавающей точкой);

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

Денежный - для хранения данных о денежных суммах;

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

Логический - используется для хранения значений Истина и Ложь;

Объект OLE - позволяет хранить такие данные как картинки, диаграммы или звуковые фрагменты, которые могут иметь динамические связи с другими приложениями Windows. Для каждого поля можно задать определенные свойства.

 

Свойства полей.

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

Число десятичных знаков применяется для задания фиксированного числа десятичных знаков от 0 до 15.

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

Подпись поля используется для определения более описательного имени поля (например, без пробелов), которое Access будет выводить в элементах управления - подпись форм и в заголовках отчетов.

Значение по умолчанию используется для определения значения, которое появится в поле, если пользователь не ввел значение для этого поля.

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

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

Индексированное поле - можно задать построение индекса для ускоренного доступа к данным.

 

Задание условия на значение для полей.

Условие на значение задается выражением, которое состоит из операторов сравнения и операндов. Несколько условий могут быть связаны логическими связками OR (ИЛИ) и AND(И).

В качестве операторов используются следующие:

< меньше, чем;

<= меньше или равно;

> больше, чем;

>= больше или равно;

= равно;

<> не равно;

 

Оператор IN обеспечивает проверку на равенство любому значению из списка, заключенного в круглые скобки.

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

Оператор LIKE проверяет соответствие текстового или emo поля заданному шаблону символов. В качестве символов шаблона используются:

? - для замены одного произвольного символа;

* - для замены любой последовательности символов;

# - для замены одной любой цифры.

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

Microsoft Access позволяет задать условия не только на значение поля, но также и на значения для таблицы. Эти условия Microsoft Access будет использовать для проверки каждой записываемой в таблицу строки.

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

 

Задание маски ввода.

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

9 - в данной позиции может быть введена цифра или пробел,

# - в данной позиции может быть введена цифра, пробел, минус или плюс,

0 - в данной позиции может быть введена цифра,

L - в данной позиции может быть введена произвольная буква,

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

. - определяется место для десятичной точки,

, - определяется место для разделителя тысяч.

 

Создание первичного ключа

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

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

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

Чтобы устранить определение поля как первичного ключа, не удаляя само поле, необходимо открыть окно Индексы выполнив команду Вид/Индексы, щелкнуть по области маркировки определения первичного ключа и нажать клавишу Del, чтобы удалить индекс.

 

2.2. Работа с таблицами в окне базы данных

 

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

* Microsoft Access не вносит автоматически сделанные в таблицах изменения в использующие эти таблицы запросы, формы, отчеты, макросы или модули, пользователь должен сам внести изменения в зависимые объекты;

* пользователь не сможет изменить тип данных для поля, которое используется в определении связи между таблицами. Сначала нужно удалить определение связи, в котором участвует это поле;

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

 

Создание резервной копии.

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

1. Открыть базу данных, содержащую таблицу, которую нужно скопировать, щелкнуть по корешку Таблицы в окне базы данных.

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

3. Выполнить команду Правка/Копировать. С помощью этой операции вся таблица (структура и данные) копируется в буфер обмена.

4. Выполнить команду Правка/Вставить, ввести новое имя таблицы и выбрать режим копирования: Структура и данные, Только структура или Добавление данных в таблицу. От последних изменений, если они оказались неудачными, можно отказаться, выполнив команду Правка/Отменить.

 

Удаление таблиц.

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

 

Переименование таблиц.

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

 

Сортировка и поиск данных

 

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

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

Сортировка таблицы по нескольким полям выполняется с помощью фильтра. Для этого необходимо выполнить следующие действия:

1. Щелкнуть по кнопке Изменить фильтр или выполнить команду Записи/Изменить фильтр.

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

3. Чтобы увидеть результаты, выполнить команду Записи/Применить фильтр.

 

Чтобы выполнить простой поиск по одному полю, необходимо выделить это поле, выполнить команду Правка/Найти и в образце поиска указать значение данных, которое нужно найти. В образце можно использовать шаблоны: символ * вместо строки произвольных символов любой длины и символ? вместо одного произвольного символа.

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

1. Открыть таблицу и открыть окно фильтра командой Записи/Изменить фильтр.

2. В бланке фильтра ввести поля, порядок сортировки и указать условия отбора. При задании условий отбора в окне фильтра можно использовать те же возможности, что и при создании запросов.

3. Щелкнуть по кнопке Применить фильтр, чтобы увидеть результаты сортировки.

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

Файл/Загрузить из запроса.

 

ЗАПРОСЫ ДАННЫХ

 

Типы запросов

Выделяют два типа запросов: QBE - запросы (QBE - Query By Example - запрос по образцу), которые пользователь формирует, определяя отдельные параметры в окне проектирования с использованием подсказок (образцов), и SQL - запросы (SQL - Structured Query Language - структурированный язык запросов), при создании которых пользователь применяет инструкции и функции, выстраивая некоторое описание.

QBE - запрос Access легко транслирует в соответствующий SQL - запрос. Обратная операция тоже не составляет труда. Для Access безразлично, с какими типами запросов работает пользователь.

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

Запрос - выборка используется для выбора из базы данных информации, которая интересует пользователя в данный момент. Результат обработки программой Access такого запроса представляет cобой таблицу (последовательность записей)

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

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

* вычисляемые и итоговые поля;

* данные в перекрестном запросе;

* значения ключевых полей в таблице со стороны "один".

 

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

 

Для создания запроса необходимо в окне базы выбрать корешок Запрос и выполнить команду Создание запроса / Конструктор.

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

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

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

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

Во второй строке бланка QBE Access выводит на экран имена таблиц, из которых выбраны поля.

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

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

Строки Условие отбора используются для ввода условий в качестве фильтра. Несколько условий отбора для нескольких полей, связанных логической связкой И, записываются в одну строку, а связанные логической связкой ИЛИ - в разные строки условий отбора. Несколько условий для одного поля связываются логическим оператором И (AND), если необходимо выполнение всех приведенных условий, и логическим оператором ИЛИ (OR), если достаточно выполнение хотя бы одного условия. Кроме обычных операторов сравнения:

> - больше, чем,

>= - больше или равно,

< - меньше, чем,

<= - меньше или равно,

= - равно,

<> - не равно,

Access предоставляет также специальные операторы:

BETWEEN - для задания диапазона значений,

IN - для сравнения со списком значений,

LIKE - для поиска образцов в текстовых полях.

 

Примеры создания запросов выбора

 

Пример 1. Показать названия и цену товаров, проданных в объеме более 10 единиц.

Поле Название Цена Количество

Имя таблицы ТОВАРЫ ТОВАРЫ ОТПУСК

Условие отбора >20

 

Пример 2. Показать фамилии покупателей Москвы или Ульяновска.

Поле Фамилия Город

Имя таблицы ПОКУПАТЕЛИ ПОКУПАТЕЛИ

Условие отбора IN("Москва";"Ульяновск")

или

Поле Фамилия Город

Имя таблицы ПОКУПАТЕЛИ ПОКУПАТЕЛИ

Условие отбора "Москва" Or "Ульяновск"

 

Пример 3. Вывести названия товаров, цена которых лежит в интервале от 100 до 200 рублей.

Поле Название Цена

Имя таблицы ТОВАРЫ ТОВАРЫ

Условие отбора BETWEEN 100 And 200

или

Поле Название Цена

Имя таблицы ТОВАРЫ ТОВАРЫ

Условие отбора >= 100 And <=200

 

Пример 4. Найти фирмы, название которых начинается с буквы А или Д.

Поле Фирма

Имя таблицы ПОКУПАТЕЛИ

Условие отбора Like "А" Or Like "Д"

 

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

Поле Фирма

Имя таблицы ПОКУПАТЕЛИ

Условие отбора Like "?[а-к]д[0-9]*"

 

Отдельно рассмотрим условия отбора для даты и времени.

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

символами #. Для ввода условий отбора для даты и времени используются следующие функции:

Day(дата) - возвращает значение дня от 1 до 31;

Month(дата) - возвращает значение месяца от 1 до 12;

Year(дата) - возвращает значение года от 100 до 9999;

Weekday(дата) - возвращает целое число от 1 до 7, соответствующее дню недели;

Hour(дата) - возвращает число от 0 до 23 - значение часа;

Date() - возвращает системную дату.

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

 

Пример 6. Вывести список товаров, проданных 15 мая 1998 года.

Поле Название Дата

Имя таблицы ТОВАРЫ ОТПУСК

Условие отбора #15.05.98#

 

Пример 7. Вывести список товаров, проданных в мае 1998 года. В запросе создадим вычисляемые поля по формулам Month([ОТПУСК]![Дата]) и Year([ОТПУСК]![Дата]).

Поле Название Month([ОТПУСК]![Дата])

Имя таблицы ТОВАРЫ

Условие отбора 5

 

Поле Year([ОТПУСК]![Дата])

Имя таблицы

Условие отбора 1998

 

 

Создание вычисляемых полей

 

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

& - конкатенация (сцепление, склеивание) текстовых полей или символьных констант;

+, -, *, /, \, ^, mod - соответственно сложение, вычитание, умножение, деление, целочисленное деление, возведение в степень и остаток от деления арифметических выражений.

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

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

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

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

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

 

Пример 8. Рассчитать общую стоимость покупок.

Поле Название Стоимость:[ТОВАРЫ]![Цена]*

[ОТПУСК]![Количество]

Имя таблицы ТОВАРЫ