Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
ПОСОБИЕ ПО СУБД "MICROSOFT ACCESS"
для студентов Института Экономики и бизнеса
1. ВВЕДЕНИЕ В БАЗЫ ДАННЫХ
Базы данных занимают все более и более прочное место в нашей жизни. Мы пользуемся ими для решения различных задач, из которых наиболее общими (и часто встречающимися) являются задачи хранения, постоянного обновления и анализа данных о каких-то объектах, процессах, явлениях.
Без баз данных сегодня невозможно представить работу большинства финансовых, промышленных, торговых и прочих организаций. Потоки информации, циркулирующие в мире, который нас окружает, огромны. Во времени они имеют тенденцию к увеличению. Базы данных позволяют информацию структурировать, хранить и извлекать оптимальным для пользователя образом. Поскольку использование баз данных является одним из краеугольных камней, на которых построено существование
различных организаций, пристальное внимание разработчиков приложений баз данных вызывают инструменты, при помощи которых такие приложения можно было бы создавать. Выдвигаемые к ним требования в общем виде можно сформулировать как "быстрота, простота, эффективность надежность".
1.1. Основные понятия баз данных
Под базой данных (БД) понимают хранилище структурированных данных, при этом данные должны быть непротиворечивы, минимально избыточны и целостны. Базу данных можно определить как унифицированную совокупность данных. Сама БД должна быть хорошо спроектирована, чтобы система БД функционировала как единое целое.
Наиболее важными аспектами БД являются:
* целостность и согласованность данных;
* защита от случайных потерь или разрушений данных;
* один уровень обновления повторяющихся записей;
* защита данных от несанкционированного доступа;
* способность к расширению и возможность обеспечения изменяющихся требований к данным.
БД создаются для хранения и доступа к данным, содержащим сведения о некотором предмете.
Под предметной областью понимается часть реального мира, которая описывается или моделируется с помощью БД.
Информационный объект - идентифицируемый объект реального мира, некоторое понятие или процесс, относящийся к предметной области, о которой хранятся данные.
Характеристикой объекта является элемент данных или идентификатор, который определяется именем и совокупностью некоторых значений. Элементы данных часто называются полями, которые выступают в роли ключей или атрибутов.
Ключ является идентификатором, уникально определяющим запись об объекте.
Атрибут - элемент данных, не являющийся ключом.
Запись об объекте - совокупность идентификаторов, которые описывают конкретный экземпляр объекта, иначе запись - это совокупность, набор полей, образующих один законченный элемент в базе данных.
Таким образом, в общем понимании база данных представляет собой коллекцию организованных данных.
1.2. Понятие и функции СУБД
СУБД (система управления базами данных) представляет собой программно-аппаратный пакет, обеспечивающий пользователю простой доступ к БД.
Система управления базами данных предоставляет программные средства, необходимые для создания, загрузки, запроса и обновления данных, а также возможность контролировать задание структуры и описание данных, работу с ними и организацию коллективного пользования этой информацией. СУБД также существенно увеличивают возможности и облегчают ведение больших объемов хранящейся в многочисленных таблицах информации.
СУБД включают в себя три основных типа функций:
* определение (задание структуры и описание) данных,
* обработка данных,
* управление данными.
Все эти функциональные возможности в полной мере реализованы в Microsoft Access.
Определение данных - можно определить, какая именно информация будет храниться в базе данных, задать структуру данных и их тип, а также указать, как эти данные связаны между собой. В некоторых случаях можно также задать форматы и критерии проверки данных.
Обработка данных - данные можно обрабатывать самыми различными способами. Можно выбирать любые поля, фильтровать, сортировать данные, можно объединять данные с другой связанной с ними информацией и вычислять итоговые значения.
Управление данными - можно указать, кому разрешено знакомиться с данными, корректировать их или добавлять новую информацию. Можно также определить правила коллективного пользования данными.
1.3. Реляционные базы данных
Почти все современные системы основаны на реляционной модели управления базами данных.
Название "реляционная" связано с тем, что каждая запись в такой базе данных содержит информацию, относящуюся только к одному конкретному объекту. Кроме того, с данными двух типов можно работать как с единым целым, основанным на значениях связанных между собой данных.
Отношение (relation) -это информация об объектах одного типа. В реляционных базах данных отношения хранятся в виде таблиц.
В реляционной СУБД все обрабатываемые данные хранятся в виде таблиц. Информация об объектах определенного вида представляется в табличном виде: в столбцах таблицы сосредоточены различные характеристики этих объектов - атрибуты, а строки предназначены для описания значений всех атрибутов определенного объекта. Даже в том случае, когда используются функции СУБД для выбора информации из одной или нескольких таблиц (т.е. выполняется запрос),
результат представляется также в табличном виде. Более того, можно выполнить запрос с использованием результатов другого запроса.
Реляционные базы данных представляют связанную между собой совокупность таблиц баз данных (ТБД). Связь между таблицами может находить свое отражение в структуре данных, а может подразумеваться, то есть присутствовать на неформальном уровне.
Каждая таблица БД представляется как совокупность строк и столбцов, где строки соответствуют экземпляру объекта, конкретному событию или явлению, а столбцы - атрибутам (признакам, характеристикам, параметрам) объекта, события, явления.
В терминологии теории реляционных БД таблицам соответствуют отношения, столбцам - атрибуты, строкам - кортежи. При практической разработке БД таблицы так и называются таблицами, строки - записями, столбцы - полями.
Предшественниками реляционных БД были иерархические и сетевые базы данных. В иерархических базах данных информация хранится в виде иерархий. Сетевая БД характеризуется внутренними ссылками между структурами данных.
Реляционная БД практически вытеснила БД других видов. В качестве основной причины называют сложность представления данных в иерархической и сетевой моделях и необходимость определения связей между данными на этапе проектирования БД, в то время как в реляционной БД связи между таблицами устанавливаются непосредственно в момент исполнения запросов. Кроме того, разработчикам и пользователям значительно проще отображать сущности предметной области в табличных структурах данных.
Однако иерархический и сетевой подходы продолжают жить. Они находят свое воплощение в отдельных специализированных БД и являются одним из "кирпичиков", на которых строятся архитектуры так зазываемых "пост-реляционных" баз данных. Быстрыми темпами развиваются объектно-ориентированные базы данных, оперирующие категориями объектов, и так называемые полнотекстовые БД, позволяющие производить быструю выборку из неструктурированной информации (например, текстов, изображений и т. д.). Однако и в настоящее время реляционные БД остаются наиболее используемыми.
Реляционные БД имеют мощный теоретический фундамент, основанный на математической теории отношений. Большинство разработчиков БД, не будучи знакомы с теорией реляционных БД, тем не менее эффективно выполняют свою работу, поскольку табличная форма организации данных является интуитивно понятной и подчиненной здравому смыслу. Поэтому при наличии минимальной практики и следуя нескольким правилам разработчик может проектировать структуры данных, не противоречащие реляционной модели.
В каждой таблице БД должен существовать первичный ключ. Под первичным ключом понимают поле или набор полей, однозначно идентифицирующий запись. Значение первичного ключа в таблице БД должно быть уникальным, то есть в таблице не должно существовать двух и более записей с одинаковыми значениями первичного ключа. Первичный ключ должен быть минимально достаточным: в нем не должно быть полей, удаление которых из первичного ключа не отразится на его уникальности.
1.4. Реляционные связи между таблицами баз данных
Отображение - традиционное средство для определения характера взаимосвязи между парами связанных элементов данных.
Между двумя или более таблицами базы данных могут существовать отношения подчиненности. Отношения подчиненности определяют, что для каждой записи главной таблицы (называемой еще родительской) может существовать одна или несколько записей в подчиненной таблице (называемой еще дочерней).
Существует три разновидности связи между таблицами базы данных:
* "один-ко-многим";
* "один-к-одному";
* "многие-ко-многим".
Отношение "один-ко-многим".
Отношение "один-ко-многим" имеет место, когда одной записи родительской таблицы может соответствовать несколько записей дочерней таблицы. Связь "один-ко-многим" иногда называют свяью "многие-к-одному". И в том, и в другом
случае сущность связи между таблицами остается неизменной.
Связь "один-ко-многим" является самой распространенной для реляционных баз данных. Она позволяет моделировать также и иерархические структуры данных.
Отношение "один-к-одному".
Отношение "один-к-одному" имеет место, когда одной записи в родительской таблице соответствует одна запись в дочерней таблице.
Данное отношение встречается много реже, чем отношение "один-ко-многим". Его используют, если не хотят, чтобы таблица БД "распухала" от второстепенной информации. Использование связи "один-к-одному" приводит к тому, что для чтения связанной информации в нескольких таблицах приходится производить несколько операций чтения вместо одной, когда данные хранятся в одной таблице.
Отношение "многие-ко-многим".
Отношение "многие-ко-многим" имеет место, когда
* одной записи в родительской таблице может соответствовать более одной записи в дочерней таблице;
* одной записи в дочерней таблице может соответствовать более одной записи в родительской таблице.
Считается, что всякая связь "многие-ко-многим" может быть заменена на одну или более связей "один-ко-многим".
1.5. Основные принципы проектирования базы данных
При проектировании структуры новой БД определяют сущности (объекты, процессы, явления) предметной области, которые должны найти свое отражение в базе данных. Анализ предметной области обычно осуществляется на основании существующих сведений о предметной области в масштабах, в которых она должна быть представлена в создаваемой БД, а также исходя из целей проектирования.
При разработке приложений СУБД используется два подхода: проектирование сверху вниз, при котором разработка приложения начинается с определения основных функций и задач, т.е. функциональный подход, и проектирование снизу вверх, при котором вначале проводится анализ данных и определяется их структура, т.е. объектный подход. Оптимальный метод проектирования включает идеи и того, и другого подходов. Понятие "проектирование БД" понимается как определение структуры БД, т. е. объектов и их идентификаторов.
Объектный - подход от предметной области. Это описание объектов отображаемой в информационной системе части реального мира и связей между ними безотносительно к потребностям пользователя.
Функциональный - подход "от запроса". Основной источник информации о предметной области - запрос пользователей (задача).
Преимуществами объектного подхода является его объективность, системное отображение предметной области и как следствие, устойчивость информационной модели, возможность реализации большого числа предложений, в т. ч. и заранее не запланированных, на созданной БД. Недостаток - трудность отбора информации. Функциональный подход ориентирован на реализацию текущих запросов пользователя и не учитывает перспектив развития системы. При его использовании может возникнуть трудность в объединении взглядов различных пользователей. Однако учет запросов позволяет улучшить характеристики функционирования БД. Отдельно взятый ни один из указанных подходов не дает достаточной информации для проектирования реляционной БД. Целесообразно совместное применение обоих подходов с ведущим положением объектного подхода.
Проектирование базы данных включает следующие этапы:
1. Определение задач.
Определение задач и их группировка поможет решить, можно ли ограничиться только одной базой данных. Базы данных должны привязываться к решению определенных связанных между собой групп задач или функций.
2. Отбор необходимых данных.
Для каждой задачи определяется набор необходимых данных.
3. Взаимодействие задач.
Для связанных между собой задач определяются все поля данных и формируются объекты. Если нужно решить вопрос об использовании тех или иных данных в конкретной задаче в качестве входной информации, то должна существовать предшествующая задача, у которой этот элемент данных является выходным.
4. Анализ данных.
Теперь можно приступить к более тщательному анализу данных и объединению отдельных данных в объекты. Эти объекты станут впоследствии основой для создания некоторых таблиц в проектируемой базе данных. Данные каждого объекта являются основой для разработки в БД отдельной таблицы.
5. Выбор объектов базы данных.
После анализа данных и привязки их к объектам необходимо для каждого объекта определить все объекты, связанные с данным, и указать тип связи ("один-ко-многим", "один-к-одному", "много-ко-многим"). В большинстве случаев любые две таблицы (два объекта) связаны отношением "один-ко-многим". Это означает, что любая запись в первой таблице может быть связана с несколькими записями во второй , но в то же время любая запись второй таблицы связана только с одной записью первой. Существует и связь "один-к-одному". Это означает, что любая запись в первой таблице связана только с одной записью во второй таблице.
Для каждого элемента данных объекта нужно указать тип данных, длину поля, необходимую для их хранения. Можно дать краткое описание этого элемента, указать условия на значения для проверки данных.
В реляционной СУБД, такой как Microsoft Access, базу данных следует строить вокруг некоторого набора задач или функций.
Процесс проектирования структуры БД является, с одной стороны, процессом творческим, неоднозначным, с другой стороны, узловые его моменты могут быть формализованы. Одной из таких формализаций является требование, согласно которому реляционная база данных должна быть нормализована (т.е. подвергнута процедуре нормализации).
Процесс нормализации имеет своей целью устранение избыточности данных и заключается в приведении к трем нормальным формам.
Первая нормальная форма требует, чтобы каждое поле таблицы базы данных было уникальным, т.е.
* было неделимым;
* не содержало повторяющихся групп.
Повторяющимися являются поля, содержащие одинаковые по смыслу значения.
Вторая нормальная форма требует, чтобы все поля таблицы зависели от первичного ключа, т е. чтобы первичный ключ однозначно определял данную запись и не был избыточным. Первичные ключ может состоять из одного или нескольким полей таблицы.
Третья нормальная форма требует, чтобы в таблице не имелось транзитивных зависимостей между неключевыми полями, т. е. чтобы значение любого поля таблицы, не входящего в первичный ключ, не зависело от значения другого поля, также не входящего в первичный ключ. Другими словами, должна быть возможность изменять значения любого поля (не входящего в первичный ключ) и это не должно повлечь за собой изменение другого поля.
Каждый раз при создании новой таблицы в существующую таблицу включается поле, связывающее эти таблицы. Эти "связывающие" поля называются чужими ключами.
Задаваемые при создании таблиц в Microsoft Access связи первичных ключей с чужими ключами используются для объединения данных из нескольким таблиц. Для ускорения поиска можно создавать индексы по чужим ключам.
Чтобы создать связи между таблицами базы данных, нужно поместить копию первичного ключа из таблицы со стороны "один" в таблицу со стороны "много".
1.6. Целостность данных и методы доступа
К утере связей между записями в родительской и дочерней таблицах могут привести два вида изменений:
* изменение значения поля связи в записи родительской таблицы без изменения значения поля связи в соответствующих записях дочерней таблицы;
* изменение значения поля связи в одной из записей дочерней таблицы без соответствующего изменения значения поля связи в родительской и дочерней таблицах.
И в первом, и во втором случаях наблюдается нарушение целостности базы данных, поскольку информация в ней становится недостоверной. Следовательно, нужно блокировать действия, которые нарушают целостность связей между таблицами, называемую ссылочной целостностью.
Чтобы предотвратить потерю ссылочной целостности, используется механизм каскадных изменений. Он состоит в обеспечении следующих требований:
* необходимо запретить изменение поля связи в записи дочерней таблицы без синхронного изменения поля связи в родительской и дочерней таблицах, обычно инициатива изменения поля связи реализуется в записи родительской таблицы;
* при изменении поля связи в записи родительской таблицы следует синхронно изменить значения полей связи в соответствующих записях дочерней таблицы;
* при удалении записи в родительской таблице следует удалить соответствующие записи в дочерней таблицы.
Обычно в СУБД для реализации ссылочной целостности в дочерней таблице создают внешний (чужой) ключ, ссылающийся на родительскую таблицу, и указывают вид каскадного воздействия. Во внешний ключ входят поля связи дочерней
таблицы. Для связей типа "один-ко-многим" внешний ключ по составу полей должен совпадать с первичным ключом родительской таблицы.
Существует и другой вид целостности - смысловая (семантическая) целостность БД. Требование смысловой целостности определяет, что данные в БД должны изменяться таким образом, чтобы не нарушалась сложившаяся между ними
смысловая связь. Изменения данных в таблицах базы происходят под некоторым воздействием. Воздействие на БД, переводящее ее из одного целостного состояния в другое, называется транзакцией. Если одно из изменений, вносимых в БД в
рамках транзакции, завершается неуспешно, должен быть произведен откат к состоянию базы данных, имевшему место до начала транзакции.
Следовательно, все изменения, внесенные в БД в рамках транзакции, либо одновременно подтверждаются, либо не подтверждается ни одно из них.
По определенным ключам СУБД автоматически строит индексы. Механизм индексов основан на понятии методов доступа. Индексы представляют собой механизм быстрого доступа к данным в таблицах БД.
Сущность индексов состоит в том, что они хранят значения индексных полей (т.е. полей, по которым построены индексы) и указатель на данные в таблицах.
При использовании индексов пользователь сталкивается с различными методами доступа к данным таблицы.
При последовательном методе доступа для выполнения запроса к таблице БД просматриваются все записи таблицы от первой до последней. Неэффективность этого метода выражается прежде всего в потере быстродействия и трате
вычислительных ресурсов. Время выполнения запроса прямо пропорционально числу записей в таблице.
При индексно-последовательном методе доступа для выполнения запроса к таблице БД указатель в индексе устанавливается на первую строку, удовлетворяющую условиям запроса (или его части), и считывается запись из таблицы ,
соответствующая индексу, на котором установлен указатель. Затем указатель в индексе перемещается на следующую строку, удовлетворяющую условиям запроса (или его части), и из таблицы считывается запись. То же происходит для всех
строк в индексе, удовлетворяющих условиям запроса (или его части). Процесс выборки прекращается, когда текущая строка в индексе перестает удовлетворять условиям запроса.
Метод назван индексно-последовательным, потому что:
* поиск ведется по индексу, а не по самой таблице;
* поиск в индексе начинается только с первой строки, удовлетворяющей условиям запроса или его части (так называемый прямой доступ);
* строки в индексе, начиная с такой записи, просматриваются все-таки последовательно.
При прямом методе доступа записи из таблицы выбираются непосредственно по значениям одного поля или группы полей, минуя перебор других записей.
Определение первичного и внешнего (чужого) ключей таблиц БД приводит к созданию индексов по полям, объявленным в составе первичных или внешних ключей. Дополнительные индексы создаются вручную или программно, если индексы,
построенные по определенным первичным и внешним ключам недостаточны для:
* обеспечения нужного порядка сортировки данных;
* оптимизации доступа к базе данных.
1.7. Архитектура Microsoft Access
Программа Access - это система управления базой данных, она не только организует данные, но и позволяет реализовать все задачи управления данным. Access является системой управления реляционной базой данных.
Microsoft Access называет объектами все, что может иметь имя (в смысле Access).
В базе данных Access основными объектами являются: таблицы, запросы, формы, отчеты, макросы и модули.
Таблица - объект, который определяется и используется для хранения данных. Каждая таблица включает информацию об объекте определенного типа. Таблица содержит поля (столбцы), в которых хранятся данные различного рода, и записи (которые называются также строками). В записи собрана вся информация о конкретном предмете. Для каждой таблицы можно определить первичный ключ (одно или несколько полей, которые имеют уникальное значение для каждой записи) и один
или несколько индексов с целью увеличения скорости доступа к данным.
Запрос - объект, который позволяет пользователю получить нужные данные из одной или нескольких таблиц. Для создания запроса можно использовать QBE (запрос по образцу) или инструкцию SQL. Можно создать запрос на выбор, обновление, удаление или добавление данных. С помощью запросов можно создавать новые таблицы, используя данные одной или нескольких таблиц, которые уже существуют.
Форма - объект, предназначенный в основном для ввода данных, отображения их на экране или управления работой приложения. Можно использовать формы для того, чтобы реализовать требования пользователя к представлению данных из запросов или таблиц. С помощью формы можно в ответ на некоторое событие, например, на изменение значений определенных данных, запустить макрос или процедуру (модуль).
Отчет - объект, предназначенный для создания документа, который впоследствии может быть распечатан или включен в документ другого приложения.
Макрос - объект, представляющий собой структурированное описание одного или нескольких действий, которые должен совершить Access в ответ на определенные события. В макрос можно включить дополнительные условия для выполнения
или невыполнения тех или иных включенных в него действий. Можно также из одного макроса запустить другой макрос или функцию модуля.
Модуль - объект, содержащий программы на Microsoft Access Basic, которые позволяют разбить процесс на более мелкие действия и обнаружить ошибки, которые не удается найти с использованием макросов. Модули могут быть независимыми объектами, содержащими функции, которые можно вызывать из любого места приложения, но они могут быть и непосредственно "привязаны" к отдельным формам или отчетам для реакции на те или иные происходящие в них изменения.
В Microsoft Access база данных включает в себя все объекты, связанные с хранением данных, в том числе и те, которые определяет пользователь для автоматизации работы с ними.
1.8. Пример проектирование базы данных
1. Определение задачи
Предположим, было получено предложение разработать приложение Microsoft Access для небольшой фирмы. Эта фирма занимается продажей различных товаров определенной группе покупателей. Фирме необходимо иметь информацию о
постоянных клиентах, о поступающих товарах, фиксировать факты отпуска товаров. При разработке приложения необходимо решить следующие задачи:
* регистрация покупок;
* расчет стоимости при оформлении покупок;
* регистрация постоянных покупателей;
* выпуск каталогов продукции;
* подготовка отчетов о прибыли и убытках;
* анализ ежеквартальных объемов продаж;
* расчет прибыли.
2. Отбор необходимых данных и выбор объектов базы данных.
После определения всех задач нужно составить для каждой задачи описание данных, необходимых для ее решения, т.е. указать имя каждого элемента данных и имя объекта, к которому относится данных элемент.
Например, для регистрации покупателей (объект ПОКУПАТЕЛИ) потребуются следующие элементы данных: Имя покупателя, Название фирмы, Город.
Для описания товаров (объект ТОВАРЫ) используются такие элементы данных: Название товара, Тип товара, Цена.
Для регистрации покупок (объект ОТПУСК) потребуются такие элементы данных: Порядковый номер покупки, Купленный товар, Дата покупки, Количество купленного товара, Покупатель.
Как видно, отдельные элементы данных объединяются в объекты. Эти объекты станут впоследствии основой для создания таблиц в проектируемой базе данных.
3. Нормализация таблиц. Первая нормальная форма.
Проверим полученные структуры данных на соответствии первой нормальной форме. Полученные структуры таблиц соответствуют требованиям, т.к.
* каждое поле является неделимым, т.е. значение поля не делится на более мелкие значения. Если бы в структуре таблицы ПОКУПАТЕЛИ элементы данных Фирма и Город были бы объединены в один элемент данных Адрес, тогда можно было бы говорить о нарушении требований первой нормальной формы.
* нет повторяющихся групп, т.е. полей, содержащих одинаковые по по смыслу значения. Если бы в таблице ОТПУСК присутствовали элементы Товар1, Товар2 и т.д., они бы составили повторяющиеся группы.
4. Создание первичного ключа.
Вторая нормальная форма требует, чтобы все поля таблицы зависели от первичного ключа, т.е. чтобы первичный ключ однозначно определял запись и не был избыточным. Для таблицы ОТПУСК первичным ключом является поле Порядковый номер покупки, т.к. однозначно определяет дату, покупателя и другие элементы данных.
Для таблицы ТОВАРЫ Название не может служить первичным ключом, т.к. товары разных типов могут иметь одинаковые названия, поэтому введем первичный ключ КодТовара. Также и в таблице ПОКУПАТЕЛИ ни Имя, ни Фирма, ни Город не
могут служить первичным ключом. Введем первичный ключ КодПокупателяя.
5. Связь между таблицами.
Установим связи между таблицами. Один покупатель может неоднократно покупать товары. Поэтому между таблицами ПОКУПАТЕЛИ и ОТПУСК имеется связь "один-ко-многим" по полю Покупатели. Каждый покупатель может приобрести несколько
различных товаров. Поэтому между таблицами ТОВАРЫ и ОТПУСК имеется связь "один-ко-многим" по полю Товар.
Теперь нужно создать связи между таблицами базы данных. Для этого поместим копии первичных ключей из таблицы со стороны "один" в таблицу со стороны "много".
Для организации связи между таблицами ТОВАРЫ и ОТПУСК поместим копию поля КодТовара из таблицы ТОВАРЫ в таблицу ОТПУСК. Для организации связи между таблицами ПОКУПАТЕЛИ и ОТПУСК поместим копию поля КодПокупателя из таблицы
ПОКУПАТЕЛИ в таблицу ОТПУСК. Для таблицы ОТПУСК поля КодПокупателя и КодТовара являются чужими ключами.
Нормализация таблиц БД призвана устранить из них избыточную информацию. Как видно из приведенного примера, таблицы нормализованной БД содержат только один элемент избыточных данных - это поле связи, присутствующее
одновременно в родительской и дочерней таблицах.
6. Транзитивные зависимости.
Третья нормальная форма требует, чтобы в таблице не было транзитивных зависимостей между неключевыми полями. Но таких зависимостей в наших таблицах и нет. Если бы в таблицу ОТПУСК включить поле Стоимость, которое вычисляется
как произведение Количества на Цену, вот тогда можно говорить о наличии транзитивных зависимостей.
Итоговая структура таблиц базы данных:
Таблица ПОКУПАТЕЛИ Таблица ТОВАРЫ Таблица ОТПУСК
КодПокупателя КодТовара Номер покупки
Имя покупателя Название товара КодПокупателя
Название фирмы Тип товара КодТовара
Город Цена Количество
Дата
2. ПОСТРОЕНИЕ И МОДИФИКАЦИЯ БАЗЫ ДАННЫХ
Таблица - это объект, который определяется и используется для хранения данных. Можно выделить следующие типы таблиц по виду их изменения: справочные, операционные, транзакционные.
Справочные таблицы характеризуются невысокой степенью изменчивости и находятся в отношении "один-ко-многим" с операционными и транзакционными таблицами.
Под операционными таблицами понимаются таблицы БД, в которых происходит устойчивое во времени непрерывное или периодическое изменение (обновление, добавление) информации. Данные в операционных таблицах служат источником
формирования данных в транзакционных таблицах.
Транзакционные таблицы служат для накопления данных, основанных на значениях данных в других таблицах.
2.1. Определение таблицы
Для создания новой базы после запуска 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 или выполнить команду Правка/Удалить.
Переименование таблиц.
Для переименования таблиц необходимо выделить ее в окне базы данных и выполнить команду Файл/Переименовать и ввести в диалоговом окне новое имя таблицы.
2.3. Работа с таблицей в режиме конструктора
Переименование полей.
Для переименования поля нужно открыть таблицу в режиме конструктора , переместить курсор в столбце Поле на нужную строку и изменить имя поля.
Если уже определены некоторые запросы, формы, макросы или модули, использующие поле, имя которого изменено, необходимо также изменить имя этого поля во всех указанных объектах.
Вставка полей.
Для вставки поля необходимо открыть таблицу в режиме конструктора, переместить курсор на строку определения поля, перед которым нужно вставить новое поле, и выполнить команду Правка/Вставить строку.
Копирование полей.
Для копирования полей выполняются следующие действия:
1. В режиме конструктора следует выделить целиком строку, содержащую определение поля, которое нужно скопировать (щелкнув по области маркировки строки или нажав клавиши Shift+пробел).
2. Скопировать строку в буфер обмена, выполнив команду Правка/Копировать.
3. Переместить курсор на строку, которая должна следовать за вставляемой строкой и вставить пустую строку командой Правка/Вставить строку.
4. Выделить пустую строку, щелкнув по ее области маркировки и выполнить команду Правка/Вставить. После вставки скопированной строки следует заменить в ней имя поля.
Удаление полей.
Удалить ненужные поля можно в режиме конструктора, выделив определение поля, щелкнув по области маркировки строки, и нажать клавишу Del.
Можно выделить группу полей, удерживая нажатой клавишу Shift и нажимая клавиши со стрелками вверх и вниз, чтобы выделить несколько несмежных строк, следует при нажатой клавише Ctrl щелкнуть по области маркировки нужных строк.
Перемещение полей.
В режиме конструктора таблицы следует выделить перемещаемую строку, клавишами Ctrl+F8 включить режим сдвига, переместить выбранное поле в нужное место с помощью стрелок вверх и вниз и выключить режим сдвига клавишей Ecs.
Microsoft Access предоставляет пользователю изменять типы данных или размеры полей даже в том случае, когда в это поле уже внесены данные.
Изменение типов полей.
Для изменения типа данных достаточно открыть таблицу в режиме конструктора и для нужного поля выбрать новый тип данных. Единственный тип данных. Который нельзя преобразовать в другой тип - это объект OLE. С некоторыми
ограничениями Microsoft Access может успешно преобразовать любой тип данных в любой другой.
Изменение размера поля.
Всегда можно увеличить размер текстового или числового поля. Однако, уменьшение размера текстового поля может привести к частичной потере хранимой информации 9текстовые значения будут усечены справа до нового размера поля).
При уменьшении размера числового поля при преобразовании данных могут возникнуть ошибки. Microsoft Access округляет и усекает числовые значения при преобразовании данных с плавающей точкой к целому или денежному типу.
2.4. Работа с данными в режиме таблицы
Просмотр данных.
Самый простой способ просмотра данных - это открыть таблицу в режиме таблицы. Режим таблицы оказывается полезным для проверки данных. Для того, чтобы просматривать данные одной из таблиц, необходимо открыть базу данных, выбрать корешок Таблицы и дважды щелкнуть мышкой по имени нужной таблицы.
Изменение макета таблицы.
Для лучшего представления данных на экране можно менять макет таблицы, выполняя команду меню Формат, например, менять ширину столбца, в котором установлен курсор, командой Формат/Ширина столбца, увеличивать высоту строк командой Формат/Высота строки, выбирать нужный шрифт командой Формат/Шрифт, удалять линии сетки командой Формат/Сетка.
Кроме того, можно изменить порядок расположения столбцов при их просмотре. Для этого следует выделить нужный столбец, щелкнув по его имени в области маркировки столбца, и перетащить область маркировки в новое место.
Можно также убрать часть столбцов с экрана, выделив один или несколько столбцов и выполнив команду Формат/Убрать столбцы. Иногда во время просмотра данных может понадобится удерживать один из столбцов все время на экране, в то время как остальные столбцы будут прокручиваться в окне вправо и влево. Для этого нужно выделить один столбец или группу смежных столбцов и выполнить команду Формат/Зафиксировать.
Изменение данных.
В режиме таблицы можно вставлять в таблицу новые записи, изменять данные, удалять записи.
Добавление новой записи.
Если таблица пустая, Microsoft Access при ее открытии показывает единственную пустую строку. Если в ней имеются записи, то Microsoft Access выведет пустую строку после последней записи с данными. Перейти к пустой строке можно командой Запись/Перейти/Новая запись. Переход в следующий столбец осуществляется клавишей Tab. Чтобы сохранить новую запись в БД, следует выполнить команду Файл/Сохранить запись. Для отмены добавления записи в БД нужно дважды нажать клавишу Ecs.
Чтобы восстановить текущую запись, необходимо сначала выполнить команду Правка/Восстановить текущее поле, а затем команду Правка/Восстановить текущую запись.
Для выделения и изменения данных можно щелкнуть в ячейке, содержащую запись, непосредственно слева от первого символа, который нужно изменить, и расширить область выделения, чтобы включить в нее все изменяемые символы.
Для замены данных можно использовать команду Правка/Заменить.
Для копирования и вставки данных можно использовать буфер обмена. Командой Правка/Копировать или Правка/Вырезать следует поместить в буфер выделенные ячейки или строки таблицы и командой Правка/Вставить вставить данные из буфера в новом месте таблицы.
Для удаления одной иди нескольких строк необходимо выделить нужные строки и нажать клавишу Del.
2.5. Сортировка и поиск данных
При открытии таблицы Microsoft Access выводит строки в последовательности, определенной значениями первичного ключа этой таблицы.
В Microsoft Access имеется возможность сортировки данных в режиме таблицы. Для этого нужно установить курсор в столбец, по которому производится сортировка, и выбрать кнопку По возрастанию или По убыванию.
Сортировка таблицы по нескольким полям выполняется с помощью фильтра. Для этого необходимо выполнить следующие действия:
1. Щелкнуть по кнопке Изменить фильтр или выполнить команду Записи/Изменить фильтр.
2. В бланке фильтра расположить поля, по которым нужно провести сортировку, и указать для каждого поля вид сортировки: по возрастанию или убыванию.
3. Чтобы увидеть результаты, выполнить команду Записи/Применить фильтр.
Чтобы выполнить простой поиск по одному полю, необходимо выделить это поле, выполнить команду Правка/Найти и в образце поиска указать значение данных, которое нужно найти. В образце можно использовать шаблоны: символ * вместо строки произвольных символов любой длины и символ ? вместо одного произвольного символа.
Для поиска по нескольким полям или отбора выводимых значений следует использовать окно фильтра. Для этого необходимо выполнить следующие действия:
1. Открыть таблицу и открыть окно фильтра командой Записи/Изменить фильтр.
2. В бланке фильтра ввести поля, порядок сортировки и указать условия отбора. При задании условий отбора в окне фильтра можно использовать те же возможности, что и при создании запросов.
3. Щелкнуть по кнопке Применить фильтр, чтобы увидеть результаты сортировки.
Чтобы сохранить определение фильтра, следует выполнить команду Файл/Сохранить как запрос и присвоить запросу имя. Чтобы использовать созданный ранее фильтр, при открытии таблицы можно открыть окно фильтра и выполнить команду
Файл/Загрузить из запроса.
2.6. Встраивание и связывание объектов
В Access - таблицы можно встраивать объекты из других поддерживающих интерфейс OLE прикладных программ и связывать эти объекты с их родительскими программами.
При встраивании объекта Access сохраняет объект в таблице. Двойным щелчком мыши на объекте запускается прикладная программа, с помощью которой он был изготовлен (родительская программа). В ней можно произвести изменения в объекте. После завершения работы с родительской программой объект в измененной форме будет сохранен в таблице. Объект следует встраивать только в том случае, если его необходимо обрабатывать, не покидая Access.
Если же объект, порожденный другой прикладной программой, связывается с Access-таблицей без встраивания, то объект сохраняется не в таблице, а в отдельном файле, причем формат этого файла определяется прикладной программой, с помощью которой объект был создан. В момент загрузки и открытия таблицы Access связывается с этим файлом, извлекая его содержимое и вставляет его в таблицу.
Связывание объектов (а не встраивание) рекомендуется в тех случаях, когда в Access базу необходимо включить объект, не подлежащий изменению при работе Access.
Если в таблицу необходимо встроить объект из другой поддерживающей OLE интерфейс прикладной программы, следует обратиться к директиве Вставка/Объект. Эта директива будет доступна только в том случае, если в структуре таблицы активному в данный момент полю был присвоен тип OLE - объект, т.е. существует возможность встраивания.
С помощью директивы встраивания Вставка/Объект можно встроить уже готовый объект или запустить родительскую программу для его изготовления. В списке Тип объекта можно найти все инсталлированные на ПК пользователя поддерживающие интерфейс прикладные программы. Порожденные ими объекты могут быть встроены в базу данных.
Если необходимо связать объект с Access -таблицей, следует выбрать директиву Правка/Вставить . однако эта директива становится доступной лишь после того как подлежащий связыванию объект будет скопирован, т.е. будет помещен в буфер обмена Clipboard. Хотя связанный объект индицируется в активной таблице, он не сохраняется в ней.
Кроме встраивания связанных объектов в базу данных можно вставить и простую копию, которая никак не будет связана с родительской программой или файлом, где хранится оригинал. После того, как в буфер обмена помещена копия встраиваемого объекта, можно обратиться к директиве Правка/Специальная вставка. Она доступна только после копирования объекта в Clipboard.
2.7. Реализация связи между таблицами
Access позволяет создавать реляционные базы данных. В реляционной базе данных пользователь может описать отношения между несколькими таблицами. Access учитывает отношения при поиске взаимосвязанных данных, во время обработки запросов, форм, отчетов, базирующихся на нескольких таблицах. Определение связи между таблицами производится в окне базы данных путем выполнения директивы Правка/Схема данных. В схему данных нужно добавить таблицы, между которыми определяется связь. Обычно две таблицы связаны между собой отношением "один-ко-многим". , и для первичного ключа родительской таблицы (таблицы со стороны "один") существует соответствующий ему чужой ключ в дочерней таблице (таблице со стороны "много"). Для того, чтобы установить связь между этими таблицами, необходимо перетащить первичный ключ родительской таблицы и опустить его на соответствующий ему чужой ключ в дочерней таблице.
В диалоговом окне Связи можно установить флажки:
* обеспечение целостности данных, что не позволит добавить в дочернюю таблицу записи, не соответствующие информации родительской таблицы, а также не позволит удалить записи родительской таблицы, имеющие связанные с ними записи в дочерней таблице;
* каскадное обновление связанных полей, что приведет к обновлению всех значений чужих ключей в дочерних таблицах;
* каскадное удаление связанных данных, что приведет к удалению записей в дочерних таблицах при удалении соответствующих записей в родительских таблицах.
Для удаления связи достаточно установить курсор мыши на связи в окне схемы данных и нажать клавишу Del.
2.8. Импортирование и экспортирование таблиц
Одним из самых основных преимуществ Microsoft Access является возможность работы с самыми разнообразными данными других баз, электронных таблиц или текстовых файлов. Кроме использования Access - баз данных, можно
импортировать или присоединять данные, хранящиеся в базах данных Access, в файлах dBase, Paradox, FoxPro, Btrieve или в любых других базах данных SQL, поддерживающих стандарт ODBC.
Можно также экспортировать данные из таблиц Access в базы данных, электронные таблицы или текстовые файлы других приложений.
Microsoft Access использует для чтения, вставки, обновления и удаления данных язык, называемый SQL (Structured Query Language - структурированный язык запросов). Язык SQL был утвержден в качестве официального стандарта для реляционных баз данных.
Для всех основных вариантов языка SQL был разработан стандартный интерфейс языка CLI (Common Language Interface). Фирма Microsoft формализовала интерфейс CLI для доступа к базам данных SQL в операционной среде Microsoft Windows. Это интерфейс получил название Open Database Connectivity (ODBC) - открытый доступ к данным.
Импорт имеет преимущества, если:
* необходимый файл сравнительно небольшого размера и пользователь исходной базы данных редко меняет его содержимое;
* данные импортируемого файла не используются совместно пользователями других приложений;
* необходимо обеспечить максимальную эффективность работы с другой СУБД.
Присоединение следует выбирать, если:
* файл по размеру больше максимально возможного размера БД Microsoft Access;
* данные этого файла часто меняются пользователем исходной БД;
* данные этого файла будут использоваться в режиме коллективного доступа.
Импорт файлов dBase, Paradox, FoxPro.
1. Открыть базу данных Microsoft Access, в которую нужно импортировать файл.
2. В окне БД выполнить команду Файл/Импорт.
3. Выбрать соответствующий формат и нужный файл.
4. Щелкнуть по кнопке Импорт.
Экспортирование таблиц
Если необходимо экспортировать данные из Access-таблиц в текстовый файл, файл электронных таблиц или таблицу базы данных с не-Access форматом, то следует обратиться в меню к директиве Экспорт. Появляющееся при этом диалоговое
окно экспорта выглядит точно также, как окно импорта. В нем следует выбрать формат файла или базы данных, в который желательно произвести экспорт. Имеющиеся в распоряжении форматы экспорта идентичны форматам импорта. В зависимости
от того, какой формат был выбран пользователем, в следующем диалоговом окне следует установить специфические для формата опции экспорта и запустить его.
Присоединение таблиц
Если пользователь захочет установить связь с таблицей из другой прикладной программы управления базами данных, чтобы просмотреть и обработать имеющиеся там данные, то следует вызвать директиву Присоединить таблицу в меню.
Присоединенную таблицу можно открыть и рассмотреть как средствами Access, так и из родительской прикладной программы. К активной базе данных можно также присоединить таблицы из других Access -баз данных. Хотя структуру присоединенной таблицы нельзя изменить, у пользователя все же имеется возможность установить параметры полей, такие как формат, точность, шрифт, значение по умолчанию, ограничение ввода и сообщение о нарушении.
В диалоговом окне присоединения выбирается тип присоединяемой таблицы. Можно осуществить присоединение таблиц, сохраненных в одном из следующих форматов:
* MS Access;
* Paradox;
* dBase III и dBase iV;
* Btrive (с файлом словаря Xtrive);
* Microsoft SQL-сервер.
После выбора формата следует нажать кнопку OK. В зависимости от выбранного формата появляется следующее диалоговое окно, в котором можно точно определить спецификацию присоединяемой таблицы и затем выполнить присоединение.
3. ЗАПРОСЫ ДАННЫХ
3.1. Типы запросов
Выделяют два типа запросов: QBE - запросы (QBE - Query By Example - запрос по образцу), которые пользователь формирует, определяя отдельные параметры в окне проектирования с использованием подсказок (образцов), и SQL - запросы (SQL - Structured Query Language - структурированный язык запросов), при создании которых пользователь применяет инструкции и функции, выстраивая некоторое описание.
QBE - запрос Access легко транслирует в соответствующий SQL - запрос. Обратная операция тоже не составляет труда. Для Access безразлично, с какими типами запросов работает пользователь.
При выполнении запроса - выборки информация выбирается из таблиц и других запросов базы данных, в отличие от запроса - действия, при выполнении которого вставляются, обновляются, удаляются записи в основных таблицах.
Запрос - выборка используется для выбора из базы данных информации, которая интересует пользователя в данный момент. Результат обработки программой Access такого запроса представляет cобой таблицу (последовательность записей)
и называется Dynaset (динамический, временный набор данных). В эту таблицу включены выбранные из основной таблицы блоки данных, которые удовлетворяют критериям запроса. Dynaset - динамический набор данных, т.е. при каждом выполнении запроса он строится вновь на основе "свежих" табличных данных. В большинстве случаев с этими наборами можно работать точно также как с таблицами: просматривать и выбирать информацию, печатать и даже обновлять записи, однако в отличие от реальной таблицы, этот набор данных физически не существует в базе данных. Access соэдает набор данных из данных таблиц и других запросов. Если изменяются данные в наборе данных, Access вносит соответствующие изменения в таблицы, на базе которых построен запрос.
Набор записей, который создает Access при выполнении запросов, выглядит и ведет себя во многом точно так же, как реально содержащая данные таблица. Во многих случаях можно вставлять строки, удалять их и обновлять данные в наборе записей. при этом Access выполнит необходимые изменения в соответствующих таблицах. Однако при использовании запросов-выборки существуют ограничения для обновления данных. Так, не подлежат изменению:
* вычисляемые и итоговые поля;
* данные в перекрестном запросе;
* значения ключевых полей в таблице со стороны "один".
3.2. Создание запроса-выборки
Для создания запроса необходимо в окне базы выбрать корешок Запрос и выполнить команду Создание запроса / Конструктор.
Окно конструктора запросов содержит список полей и бланк для создания запроса по образцу (QBE). В верхней панели окна находятся списки полей таблиц или запросов, выбранных для данного запроса. Нижняя панель является бланком QBE, в котором выполняется вся работа по созданию запроса.
Каждый столбец бланка QBE относится к одному полю. Поле может быть просто полем одной из таблиц, вычисленным на основе значений нескольких полей, или итоговым полем, использующих одну из встроенных групповых функций Microsoft Access.
Для выбора полей используется первая строка бланка QBE. Это могут быть поля, которые должны присутствовать в наборе записей запроса, поля, по которым нужно провести сортировку, или поля, значения которых нужно проверить.
Включение полей в запрос может быть осуществлено двойным щелчком на имени поля в области верхней панели.
Во второй строке бланка QBE Access выводит на экран имена таблиц, из которых выбраны поля.
В третьей строке бланка можно задать выполнение сортировки по выбранному или вычисляемому полю.
Четвертую строку бланка Вывод на экран можно использовать для указания полей, которые должны быть выведены в наборе данных.
Строки Условие отбора используются для ввода условий в качестве фильтра. Несколько условий отбора для нескольких полей, связанных логической связкой И, записываются в одну строку, а связанные логической связкой ИЛИ - в разные строки условий отбора. Несколько условий для одного поля связываются логическим оператором И (AND), если необходимо выполнение всех приведенных условий, и логическим оператором ИЛИ (OR), если достаточно выполнение хотя бы одного условия. Кроме обычных операторов сравнения:
> - больше, чем,
>= - больше или равно,
< - меньше, чем,
<= - меньше или равно,
= - равно,
<> - не равно,
Access предоставляет также специальные операторы:
BETWEEN - для задания диапазона значений,
IN - для сравнения со списком значений,
LIKE - для поиска образцов в текстовых полях.
3.3. Примеры создания запросов выбора
Пример 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
3.4. Создание вычисляемых полей
Можно задать вычисления над любым полем таблицы и сделать вычисляемые значения новым полем в наборе данных. В выражении можно использовать следующие операторы:
& - конкатенация (сцепление, склеивание) текстовых полей или символьных констант;
+, -, *, /, \, ^, mod - соответственно сложение, вычитание, умножение, деление, целочисленное деление, возведение в степень и остаток от деления арифметических выражений.
Также можно использовать множество встроенных функций Access. При построении сложных выражений Access предлагает использовать Построитель выражений.
Для того, чтобы воспользоваться Построителем выражений, следует в бланке запроса QBE щелкнуть по пустому полю, а затем по кнопке Построить панели инструментов. Microsoft Access откроет окно Построителя выражений.
В верхней части этого окна расположена пустая область ввода, в которой можно создавать выражения.
В нижней части окна расположено три списка, которыми можно пользоваться, чтобы найти необходимые для создания выражения имена полей и функций.
Все имена объектов в Access должны быть заключены в квадратные скобки. Следует использовать полное имя поля, помещая перед именем поля имя таблицы. В этом случае перед именем поля стоит восклицательный знак, разделяющий имена объектов.
Пример 8. Рассчитать общую стоимость покупок.
Поле Название Стоимость:[ТОВАРЫ]![Цена]*
[ОТПУСК]![Количество]
Имя таблицы ТОВАРЫ
3.5. Создание итоговых запросов
Для вычисления в запросе итоговых значений следует щелкнуть по кнопке Групповые операции панели инструментов конструктора запросов, чтобы в бланке QBE появилась строка Групповая операция. Для получения итогов нужно заменить в строке Групповая операция установку Группировка на определение групповых функций.
Access предоставляет девять функций, обеспечивающих выполнение групповых операций:
Sum - вычисляет сумму всех значений данного поля в каждой группе;
Avg - вычисляет среднее арифметическое всех значений данного поля в каждой группе;
Min - возвращает наименьшее значение, найденное в этом поле внутри каждой группы;
Max - возвращает наибольшее значение, найденное в этом поле внутри каждой группы;
Count - возвращает число записей в каждой группе;
StDev - подсчитывает статистическое стандартное отклонение для всех значений данного поля в каждой группе;
Var - подсчитывает статистическую дисперсию для всех значений данного поля в каждой группе;
First - возвращает первое значение этого поля в группе;
Last - возвращает последнее значение этого поля в группе.
Пример 9. Определить количество и стоимость товаров, проданных за каждый день. Стоимость вычисляется по формуле
Стоимость:[ТОВАРЫ]![Цена]*[ОТПУСК]![Количество].
Поле Дата Количество Стоимость:
Имя таблицы ОТПУСК ОТПУСК
Групповая операция группировка Sum Sum
Чтобы в группы включать только определенные записи, можно добавить в бланке QBE поля, которые будут использоваться в фильтре. Для создания фильтра следует выбрать установку Условие в строке Групповая операция, удалить флажок
Вывод на экран для этого поля и ввести условие отбора.
Пример 10. Показать общую сумму продаж каждого товара с 1 мая 1999 года.
Поле Название Стоимость Дата
Имя таблицы ТОВАРЫ ОТПУСК
Групповая операция группировка Sum Условие
Условие отбора >#01.05.99#
Пример 11. Определить товары, общая сумма продажи которых превысила 10 000 рублей.
Поле Название Стоимость
Имя таблицы ТОВАРЫ
Групповая операция группировка Sum
Условие отбора >10 000
3.6. Создание параметрических запросов
В запросах выбора условия отбора вводятся непосредственно в бланках QBE окна запроса в режиме конструктора. Однако не всегда на этапе создания запроса можно решить, какие именно значения должен отыскивать Access. Вместо этого можно включить в запрос параметр и в этом случае перед выполнением запроса Access каждый раз будет запрашивать у пользователя конкретные условия отбора. Чтобы установить параметр, вместо конкретного значения в строку Условия отбора в бланке QBE нужно ввести имя или фразу, заключенную в квадратные скобки. То, что заключено внутри квадратных скобок, Access рассматривает как имя параметра. Это имя выводится в диалоговом окне при выполнении запроса, поэтому в качестве имени параметра следует использовать содержательную фразу. В одном запросе можно задать несколько параметров. При этом имя каждого параметра должно быть уникальным и информативным.
Пример 12. Определить общую сумму продаж за определенный день.
Поле Дата Стоимость
Имя таблицы ОТПУСК
Групповая операция группировка Sum
Условие отбора [Введите дату]
3.7. Создание перекрестных запросов
Access поддерживает специальный тип итоговых запросов, называемый перекрестным запросом или запросом кросс-таблиц. Такой запрос позволяет увидеть вычисляемые значения в виде перекрестной таблицы, напоминающей электронную таблицу. Если необходимо объединить данные в формате строк-колонок (двумерная таблица), то следует создать запрос кросс-таблицы. При проектировании запроса кросс-таблицы можно указать в качестве заголовков для колонок значения некоторых полей или выражений. Запросы кросс-таблиц позволяют более компактно, чем обычные запросы, индицировать данные, объединяя однотипную информацию. При создании перекрестного запроса необходимо в режиме конструктора запроса выполнить команду Запрос/ Перекрестный. Access добавляет в бланк QBE строку Перекрестная таблица. В этой строке для каждого поля перекрестного запроса может быть выбрана одна из четырех установок: Заголовки строк, Заголовки столбцов, Значение (выводимое в сетке перекрестного запроса) и Не выводить.
Для перекрестного запроса нужно определить по крайней мере одно поле в качестве заголовка строки, одно поле в качестве заголовка столбца и одно поле значений. Каждое поле, являющееся заголовком строки или столбца, должно иметь в строке групповых операций установку Группировка. Для поля, которое в бланке запроса имеет установку Значение, нужно выбрать одну из групповых функций или ввести выражение, содержащее групповые функции.
Как и в других типах итоговых запросов, для отбора данных, включаемых в набор записей, можно использовать дополнительные поля. Для таких полей необходимо выбрать установку Условия в строке групповых операций и Не выводить в строке Перекрестная таблица, а затем ввести Условия отбора. Условия отбора и сортировки можно указать для любого поля, используемого в качестве заголовка.
Пример 13. Определить итоговые суммы продаж по месяцам (столбцы таблицы) для каждого наименования товара (строки).
Поле Название Month([Дата])
Имя таблицы ТОВАРЫ
Групповая операция Группировка Группировка
Перекрестная таблица Заголовки строк Заголовки столбцов
Сортировка По возрастанию По возрастанию
Поле Стоимость
Имя таблицы
Групповая операция Sum
Перекрестная таблица Значение
Порядок столбцов в перекрестной таблице можно определить, используя бланк свойст запроса. Бланк свойств можно получить, если щелкнуть в любом месте верхней панели (вне списка полей) окна запроса в режиме конструктора, а затем
по кнопке Свойства на панели инструментов.
3.8.Модификация данных с помощью запросов действий
Запрос действия изменяет данные или копирует их из одной таблицы в другую. В отличие от запроса выбора он не создает Dynаset, который можно отобразить на экране в табличном представлении. Для того, чтобы увидеть изменения, нужно либо снова преобразовать запрос действия в запрос выбора (для запросов обновления), либо открыть таблицу (для запросов создания таблиц, добавления и удаления записей).
С помощью запроса действия пользователь может изменять или переносить данные таблицы, а также обновлять, добавлять или удалять группы блоков данных, создавать новые таблицы из набора Dynaset. Различают четыре типа запросов действия: запросы добавления, запросы удаления, запросы обновления и запросы создания таблицы.
Запрос добавления
С помощью запроса добавления блоки данных одной таблицы (все или отобранные запросом) можно присоединить в конец другой таблицы. Эта другая таблица может находиться как в той же базе данных, так и в другой.
Запрос удаления
С помощью запроса удаления можно удалить группу блоков данных, отобранных по определенному критерию. При этом следует проявить осмотрительность и тщательно проанализировать критерий отбора, поскольку эту операцию нельзя отменить. Для выполнения удаления записей из таблицы сначала следует спроектировать запрос выбора, чтобы выбрать удаляемые блоки данных. По завершению задания критериев отбора следует выполнить запрос выборки, чтобы оценить корректность сформированных условий. Чтобы создать запрос на удаление из запроса-выборки, следует выполнить команду Запрос/Удаление в режиме конструктора запроса.
Пример 14. Удалить сведения о продаже товаров за определенный месяц.
Поле Month([ОТПУСК]![Дата]) Year(ОТПУСК[]![Дата])
Имя таблицы
Условие отбора [Месяц удаления] [Год удаления]
Запрос обновления
Перед тем как приступить к созданию и выполнению запроса на обновление множества записей в базе данных, стоит сначала создать запрос-выборку, используя условия отбора, позволяющие выбрать те записи, которые нужно обновить.
После того, как Access выберет нужные записи, не составит особого труда преобразовать этот запрос-выборку в запрос-обновление или в другой тип запроса действия. Используя этот запрос, пользователь может изменить группу блоков данных, отобранную на основе определенных критериев.
Чтобы преобразовать запрос-выборку в запрос-обновление, следует в режиме конструктора запроса выполнить команду Запрос/Обновление. В строке Обновление для соответствующего поля вводится выражение. В квадратных скобках указываются имена полей. Когда Access готов приступить к обновлению данных в базовой таблице или запросе, он сначала делает копию исходной записи. Access применяет заданные пользователем выражения к исходной записи и помещает результаты в копию, затем меняет содержимое базы данных, переписывая модифицированную копию в таблицу.
Пример 15. Увеличить цену товаров заданного типа на 20%.
Поле Название Тип Цена
Имя таблицы ТОВАРЫ ТОВАРЫ ТОВАРЫ
Обновление [Цена]*1.2
Условие отбора [Введите тип]
Запрос создания таблицы
Из динамического набора Dynaset, сформированного при выполнении запроса, можно создать таблицу, например для архивирования старых блоков данных, сохранения резервных копий таблиц или накапливания итоговой информации.
Сначала следует создать запрос выбора и получить динамический набор данных Dynaset, заодно проверив при этом корректность запроса. Если Dynaset соответствует представлениям пользователя, следует превратить запрос-выборку в запрос на создание таблицы, выполнив команду Запрос/Создание таблицы в режиме конструктора запроса. В диалоговом окне Свойства запроса следует указать имя для создаваемой таблицы.
Во время выполнения запроса-действия Access различает следующие категории ошибок:
* дубликаты первичных ключей;
* ошибки преобразования данных, когда при добавлении данных в таблицу тип данных поля-получателя не совпадает типом поля-источника;
* заблокированные записи, когда при обновлении или удалении записей таблица используется другим пользователем сети;
* нарушение условий на значение, когда вставляемые или обновляемые данные не удовлетворяют условиям на значения полей или таблицы.