Оффисные приложения Ms Access
.pdfМинский филиал государственного образовательного учреждения высшего профессионального образования «Московский государственный университет экономики, статистики и информатики (МЭСИ)»
Курбацкий В.Н.
Информатика
для студентов экономических специальностей
Часть 4
Стандартные оффисные приложения. Система управления базами данных MS Access
Учебно-методическое пособие
МИНСК
2011
Общие понятия теории баз данных. Основы работы с СУБД Microsoft Access
Оглавление |
|
1. Общие понятия теории баз данных. Основы работы с СУБД Microsoft Access .............. |
4 |
1.1.Понятие базы данных. Система управления базами данных (СУБД). Модели
данных: реляционная, иерархическая, сетевая....................................................................... |
4 |
1.2.Microsoft Access как реляционная СУБД. Назначение, возможности и области
применения Microsoft Access. .................................................................................................. |
4 |
1.3.Основные принципы создания базы данных. Основные этапы создания базы
данных. ....................................................................................................................................... |
7 |
1.4.Интерфейс Microsoft Access. Запуск и завершение работы с приложением.
Пользовательский интерфейс Access 2010 ........................................................................... |
15 |
1.5.Основные приемы работы с базами данных: способы создания новой базы данных,
открытие существующей, завершение работы с базой данных.......................................... |
22 |
2. Работа с таблицами в Microsoft Access............................................................................... |
26 |
2.1. Введение в таблицы...................................................................................................... |
26 |
2.2.Создание таблиц. Определение первичного ключа таблицы. Задание свойств
таблицы. Задание свойств полей............................................................................................ |
31 |
2.3.Определение связей между таблицами. редактирование связей между таблицами. 45
2.4. Сортировка, поиск и фильтрация данных................................................................... |
51 |
2.5.Модификация набора данных: удаление и переименование таблиц, изменение
структуры таблиц, редактирование связей между таблицами............................................ |
53 |
3. Работа с данными при помощи запросов в Microsoft Access ........................................... |
57 |
3.1. Классификация запросов по способу создания: запросы по образцу, SQL-запросы. |
|
Основные типы запросов: запросы на выборку, параметрические запросы, перекрестные |
|
запросы, запросы на изменение. ............................................................................................ |
57 |
3.2. Создание запросов на выборку с помощью конструктора запросов........................ |
58 |
3.3. Редактирование запросов на выборку......................................................................... |
64 |
3.4. Создание запросов с помощью Мастера запросов..................................................... |
65 |
3.5.Создание вычисляемых полей. Работа с построителем выражений. Использование
вычисляемых выражений для задания условий отбора данных. ........................................ |
67 |
|
3.6. |
Создание итоговых запросов. ...................................................................................... |
75 |
3.7. Создание запросов с параметрами............................................................................... |
76 |
|
3.8. |
Создание перекрестных запросов................................................................................ |
78 |
3.9. Создание запросов на обновление записей ................................................................ |
84 |
|
3.10. Создание запросов на добавление и удаление записей......................................... |
91 |
|
4.Использование форм для ввода и просмотра данных, создание отчетов в Microsoft
Access |
............................................................................................................................................ |
96 |
4.1. ............................................................................................................................ |
Формы |
96 |
4.2. ...........................................Создание формы с помощью различных инструментов |
97 |
|
4.3. ................................. |
Работа с формами в Режиме макета и режиме конструктора |
101 |
2
Общие понятия теории баз данных. Основы работы с СУБД Microsoft Access |
|
4.4. Создание и изменение отчетов в режиме конструктора. Основные приемы работы |
|
в режиме конструктора отчетов. Использование группировки данных и сумм с |
|
накоплением. Способы вычисления итоговых значений. ................................................. |
112 |
5. Приложения в Microsoft Access ........................................................................................ |
116 |
5.1. Создание пользовательского интерфейса для базы данных. .................................. |
116 |
5.2. Сжатие и восстановление базы данных.................................................................... |
116 |
5.3. Шифрование базы данных с использованием пароля базы данных...................... |
119 |
3
Общие понятия теории баз данных. Основы работы с СУБД Microsoft Access
1.ОБЩИЕ ПОНЯТИЯ ТЕОРИИ БАЗ ДАННЫХ. ОСНОВЫ РАБОТЫ С СУБД
MICROSOFT ACCESS
Цель модуля – ознакомиться с основными принципами построения и функционирования баз данных и способами их реализации в MS Access.
1.1.ПОНЯТИЕ БАЗЫ ДАННЫХ. СИСТЕМА УПРАВЛЕНИЯ БАЗАМИ ДАННЫХ (СУБД). МОДЕЛИ ДАННЫХ: РЕЛЯЦИОННАЯ, ИЕРАРХИЧЕСКАЯ, СЕТЕВАЯ.
База данных – это набор взаимосвязанных данных и средств взаимодействия с ними, обеспечивающий оптимальное использование информации для одного или нескольких приложений в определенных областях человеческой деятельности.
База данных представляет собой набор сведений, связанных с определенной темой или функцией, например отслеживанием заказов покупателей или обработкой музыкальной коллекции. Если база данных полностью или частично хранится не на компьютере, данные могут быть собираться из нескольких источников, которые необходимо координировать.
База данных может быть основана на одной или на совокупности нескольких моделей. Иерархическая модель данных представляет собой набор элементов, расположенных в порядке их подчинения от общего к частному и образующих «перевернутое дерево». В этой модели несколько узлов более низкого уровня соединяются при помощи связи с одним узлом более высокого уровня. К основным недостаткам модели следует отнести некоторые сложности поиска и сортировки данных, невозможность перехода по записям одного уровня (необходимо выйти на предыдущий уровень и войти в другую ветвь связей).
Реляционная модель (от латинского relatio – отношение) хранения данных построена на взаимоотношении составляющих ее частей. Реляционная база данных - хранилище, состоящее их небольших наборов данных (таблиц), позволяющих устранить избыточность и связанных друг с другом через общие элементы (поля). В качестве примера такой организации данных можно привести двумерную таблицу или совокупность взаимосвязанных таблиц. В такой модели удобно производить сортировку, выборку данных по группам, поиск записей.
Система управления базами данных (СУБД) представляет собой комплекс программных средств, необходимых для создания и использования наборов данных. Ее основное назначение – обеспечение быстрого доступа к необходимой информации.
1.2.MICROSOFT ACCESS КАК РЕЛЯЦИОННАЯ СУБД. НАЗНАЧЕНИЕ, ВОЗМОЖНОСТИ И ОБЛАСТИ ПРИМЕНЕНИЯ MICROSOFT ACCESS.
Приложение Access позволяет быстро и легко разрабатывать приложения реляционных баз данных, предназначенные для управления информацией. В Access можно разрабатывать базы данных для отслеживания практически любых сведений, в том числе складских запасов, контактных данных и бизнес-процессов. Приложение Access поставляется с шаблонами, которые сразу же можно использовать для работы с различными данными, что позволяет быстро освоить программу даже начинающим пользователям.
Основными функциями СУБД, полностью реализованными в Мicrosoft Асcess, являются:
определение данных – возможность описания информации, хранимой в базе данных, определения структуры данных, их типа, а также указания, как эти данные связаны между собой;
обработка данных – возможность выбора любых характеристик объектов, фильтрации и сортировки, объединения данных с другой связанной с ними информацией и вычисления итоговых значений;
4
Общие понятия теории баз данных. Основы работы с СУБД Microsoft Access
управление данными – возможность установления разрешений на доступ к данным, правил их изменения и добавления, определение режимов коллективного использования данных.
Основные типы объектов Microsoft Access.
База данных Мicrosoft Асcess включает в себя не только набор данных, но и объекты, связанные с хранимыми данными, в том числе и те, которые созданы для автоматизации работы с ними.
Знакомство с таблицами, формами, запросами и прочими объектами базы данных облегчает выполнение самых разных задач, таких как ввод данных в форму, добавление или удаление таблиц, поиск и замена данных и выполнение запросов.
База данных представляет собой набор сведений, связанных с определенной темой или функцией, например отслеживанием заказов покупателей или обработкой музыкальной коллекции. Если база данных полностью или частично хранится не на компьютере, данные могут быть собираться из нескольких источников, которые необходимо координировать.
Приложение Access можно использовать для управления всеми данными в одном файле. В файле базы данных Access можно использовать:
таблицы для хранения данных;
запросы на поиск и извлечение только необходимых данных;
формы для просмотра, добавления и обновления данных в таблицах;
отчеты для анализа или печати данных в специальном формате.
Все эти элементы — таблицы, запросы, формы и отчеты — являются объектами базы данных.
Таблицы. Связи между таблицами. Типы связей: один-ко-многим, один-к-одному, многие- ко-многим.
Таблица – совокупность строк и столбцов, объект базы, который используется для хранения данных. Простая база данных может состоять всего из одной таблицы. Большинство баз данных включают несколько таблиц. Например, в одной таблице могут храниться сведения о продуктах, во второй — сведения о заказах, а в третьей — сведения о клиентах. Чтобы объединить данные из нескольких таблиц в запросе, форме или отчете, нужно определить связи между таблицами.
Каждая строка в таблице называется записью, а каждый столбец — полем. Записи представляют собой четкий и надежный способ объединения данных о чем-либо. Поля представляют собой отдельные элементы данных — типы элементов, имеющиеся в каждой записи.
Каждая таблица должна содержать столбец или набор столбцов для однозначного определения каждой строки таблицы. Как правило, в этих целях используется уникальный идентификационный номер, например код сотрудника или серийный номер. В базе данных такие сведения носят название первичного ключа таблицы. В Access первичные ключи служат для быстрого связывания данных из нескольких таблиц и их отображения для пользователя.
Access — это система управления реляционными базами данных. В реляционной базе данных сведения распределяются по отдельным тематически организованным таблицам. Для объединения данных используются связи между таблицами.
Выделяют три основных типа связей (или отношений) между таблицами: «один-к-одному»,
«один-ко-многим», «многие-ко-многим».
Связь типа «один-к-одному» – межтабличное отношение, при котором любая запись в первой таблице связана только с одной записью во второй таблице и наоборот.
5
Общие понятия теории баз данных. Основы работы с СУБД Microsoft Access
Связь типа «один-ко-многим» – межтабличное отношение, при котором любая запись в первой таблице может быть связана с несколькими записями во второй, но в то же время любая запись второй таблицы связана только с одной записью в первой. Если при этом связующее поле в одной из таблиц является ключевым, то такая таблица называется
главной. Одна и та же таблица может выступать в одной связи как главная, а в другой как подчиненная. Статус таблицы влияет на действия, предпринимаемые Мicrosoft Access при редактировании и обновлении записей из связанных таблиц.
Связь типа «многие-ко-многим» – межтабличное отношение, при котором каждой записи в первой таблице могут соответствовать несколько записей во второй таблице, и каждой записи во второй таблице – несколько записей в первой таблице. При наличии подобной связи ее разбивают на несколько связей типа «один-ко-многим» с помощью дополнительных таблиц.
При определении связей между таблицами можно дополнительно установить параметры:
обеспечение целостности данных, при котором не допускается наличия в подчиненной таблице записи без связанной с ней записи в главной таблице;
каскадное обновление связанных полей, который обеспечивает при изменении значения связующего поля в главной таблице автоматическое обновление соответствующих полей подчиненных таблиц;
каскадное удаление связанных записей, при котором в случае удаления записи из главной таблицы автоматически удаляются все связанные с ней записи из подчиненных таблиц.
Структура связей между таблицами может быть представлена графически в виде
схемы данных.
Запросы
С помощью запроса можно найти и извлечь данные (в том числе и данные из нескольких таблиц), соответствующие указанным условиям. Запросы также используются для обновления или удаления нескольких записей одновременно и выполнения предопределенных или пользовательских вычислений на основе данных.
Формы
Формы можно использовать для просмотра, ввода и изменения данных в одной строке за раз. Кроме того, с их помощью можно выполнять такие действия, как отправка данных другим приложениям. Формы обычно содержат элементы управления, связанные с полями базовых таблиц. При открытии формы Access извлекает данные из одной или нескольких таких таблиц и отображает их в выбранном при создании формы формате. Форму можно создать с помощью команд Форма на ленте, в мастере создания формы, а также самостоятельно в режиме конструктора. Для создания форм и отчетов в веб-базах данных вместо режима конструктора необходимо использовать режим макета.
В таблице одновременно отображается множество записей, однако для просмотра всех данных в одной записи иногда необходимо прокрутить ее горизонтально. Кроме того, при просмотре таблицы невозможно обновить данные из нескольких таблиц одновременно.
Форма отображает по одной записи за раз и может отображать поля из нескольких таблиц, а также картинки и другие объекты. Форма может содержать кнопку, используемую для печати отчета, открытия других объектов или автоматического выполнения других задач.
Отчеты
Отчет можно использовать для быстрого анализа данных или их отображения в определенном виде в печатном или другом формате. Например, можно отправить коллеге
6
Общие понятия теории баз данных. Основы работы с СУБД Microsoft Access
отчет, в котором сгруппированы данные и вычислены итоги, или создать отчет с адресом, отформатированным для печати наклеек на почтовые конверты.
1.3.ОСНОВНЫЕ ПРИНЦИПЫ СОЗДАНИЯ БАЗЫ ДАННЫХ. ОСНОВНЫЕ ЭТАПЫ СОЗДАНИЯ БАЗЫ ДАННЫХ.
Основные принципы создания базы данных
В основе процесса создания базы данных лежат определенные принципы. Первый принцип заключается в необходимости исключать повторяющиеся (или лишние) данные, т. к. они занимают место и повышают вероятность возникновения ошибок и неполадок. Второй принцип касается важной роли правильных и полных данных. Если база данных содержит неправильные данные, все отчеты, основанные на сведениях из нее, будут также содержать неверные сведения. Это может привести к принятию неправильных решений на основе таких отчетов.
Правильная структура базы данных подразумевает:
распределение данных по тематическим таблицам в целях сокращения объема повторяющихся данных;
добавление в Access данных, необходимых для объединения сведений, которые содержатся в таблицах;
возможность поддержания и отслеживания точности и целостности данных;
соответствие требованиям к обработке данных и созданию отчетов.
Проектирование базы данных
Построение логической и физической моделей данных является основной частью проектирования базы данных. Полученная в процессе анализа информационная модель сначала преобразуется в логическую, а затем в физическую модель данных. После этого для разработчиков информационной системы создается пробная база данных.
Выделяются три этапа проектирования базы данных:
1.Построение концептуальной модели
2.Построение физической модели для конкретной СУБД.
3.Генерация базы данных на основе физической модели.
Концептуальная модель представляет собой описание данных в терминах предметной области. На концептуальную модель возлагается ответственность за отображение семантики реального мира.
На концептуальном уровне можно установить связи «один-ко-многим», «многие-ко-многим» и «один-к-одному».
Концептуальная модель позволяет понять суть разрабатываемой СУБД, но она не подходит для создания непосредственно структуры базы данных. Для генерации структуры базы данных необходимо преобразовать концептуальную модель в физическую.
Впроцессе преобразования:
каждая сущность преобразуется в таблицу, а имя сущности становится именем таблицы.
каждый атрибут становится полем таблицы с тем же именем, уточняется тип данных, выбирается более точный формат.
идентифицирующие атрибуты сущности превращаются в первичный ключ таблицы.
7
Общие понятия теории баз данных. Основы работы с СУБД Microsoft Access
отношения «один-ко-многим» и «один-к-одному» становятся внешними ключами, для них создается копия уникального идентификатора с одиночного конца связи, и соответствующие поля составляют внешний ключ.
для отношений «многие-ко-многим» создается таблица, полями которой являются уникальные идентификаторы связываемых сущностей (они составляют первичный ключ).
На последнем этапе происходит непосредственно генерация базы данных на основе физической модели в базу данных под управлением СУБД MS Access 2010 с сохранением всех связей между таблицами.
Процесс разработки Процесс разработки базы данных включает перечисленные ниже действия.
Определение цели создания базы данных. Это позволяет подготовиться к выполнению следующих шагов.
Поиск и организация необходимых данных. Следует собирать все данные,
которые необходимо сохранить в базе данных.
Распределение данных по таблицам. Распределите элементы данных по группам или темам. Для каждой темы создается таблица.
Преобразование элементов данных в столбцы. Определите, какие данные требуется хранить в каждой таблице. Каждый элемент данных будет занимает отдельное поле и отображается в виде столбца таблицы.
Задание первичных ключей. Выберите первичные ключи таблиц. Первичный ключ — столбец для однозначного определения каждой записи.
Создание связей между таблицами. Проанализируйте все таблицы и определите, как данные одной таблицы связаны с данными других таблиц. Добавьте в таблицы поля или создайте новые таблицы для создания необходимых связей.
Усовершенствование структуры. Проверьте структуру базы данных на наличие ошибок. Создайте таблицы и добавьте несколько записей с образцами данных. Проанализируйте полученные результаты. Внесите в структуру необходимые изменения.
Применение правил нормализации. Примените правила нормализации, чтобы проверить правильность структуры таблиц. Внесите в таблицы необходимые изменения.
Определение цели создания базы данных
Целесообразно записать цель создания базы данных на бумаге: задачи, способы использования и список пользователей. Для базы данных небольшого объема, например, предназначенной для работы на дому можно определить следующую простую цель: "База данных клиентов содержит сведения о клиентах и используется для рассылки сообщений электронной почты и отчетов". При создании более сложной базы данных для большого количества пользователей, например в организации, описание цели может состоять из нескольких параграфов; необходимо указать время и способы использования базы данных различными пользователями. Основная задача — составить подробное описание цели создания базы данных, чтобы иметь возможность обращаться к нему в процессе проектирования. Наличие описания позволяет следовать поставленным целям в процессе принятия решений.
Поиск и организация необходимых данных
Процесс поиска и организации необходимых данных следует начать с записи имеющихся сведений. Например, можно записать заказы из главной книги или сохранить сведения о клиентах из картотеки. Соберите необходимые документы и составьте список типов данных
8
Общие понятия теории баз данных. Основы работы с СУБД Microsoft Access
(например, список полей в бланке). Если бланки не используются, представьте, что необходимо создать бланк для записи сведений о клиентах. Какие данные требуется записывать? Какие поля для заполнения необходимо создать? Определите нужные элементы и составьте их список. Предположим, что сведения о клиентах хранятся в картотеке. Анализ карточек позволяет определить, что в них содержатся такие сведения, как имя клиента, адрес, город, область, почтовый индекс и номер телефона. Каждый из этих элементов может стать столбцом таблицы.
При подготовке списка необязательно стремиться придать ему законченный вид с первого раза. Следует записывать все возможные варианты. Если база данных предназначена для нескольких пользователей, попросите их предложить свои варианты. Список можно исправить позднее.
Затем следует определить типы отчетов или рассылок, которые требуется выполнять из базы данных. Например, может потребоваться выполнять отчет о продажах продуктов по регионам или сводный отчет по запасам продуктов. Может также потребоваться создавать письма, адресованные клиентам и содержащие сведения о продажах или специальных предложениях. Продумайте структуру отчета, определите его внешний вид. Какие сведения требуется включить в отчет? Составьте список нужных сведений. Выполните те же действия для писем и других отчетов.
Определение структуры отчетов и рассылок позволяет выявить элементы, которые требуется включить в базу данных. Предположим, что требуется предоставить клиентам возможность подписываться или отменять подписку на регулярную рассылку новостей, а также иметь возможность выводить на печать список подписчиков. Для записи этих сведений в таблицу клиентов следует добавить столбец для отправки сообщений электронной почты. В этом поле можно выбрать вариант "Да" или "Нет" для каждого клиента.
Для отправки клиентам сообщений электронной почты необходимо записать еще один элемент данных. Если клиент согласится получать сообщения электронной почты, потребуется записать его адрес электронной почты. Следовательно, для каждого клиента потребуется сохранить адрес электронной почты.
Удобно создать прототип каждого отчета или списка клиентов для печати и определить, какие элементы данных потребуются для их создания. Например, при анализе письма можно выявить несколько элементов. Если требуется включить в письмо приветствие — например строку "г-н" или "г-жа", — необходимо создать соответствующий элемент. Кроме того, письмо, как правило, начинается со стандартной фразы "Уважаемый г-н Егоров", а не с "Уважаемый г-н Владимир Егоров". Поэтому фамилию требуется сохранять отдельно от имени.
Важно помнить, что данные необходимо разбивать на минимальные элементы. Например, имя следует разделять на две части: имя и фамилию, чтобы фамилию было легче использовать. В частности, чтобы выполнить сортировку отчета по фамилиям, необходимо сохранить их в отдельных полях. В целом, если требуется выполнять сортировку, поиск, вычисления или отчеты для отдельных элементов данных, эти элементы следует сохранять в отдельных полях.
Составьте список вопросов, ответы на которые требуется получать с помощью базы данных. Например, каков объем продаж отдельного продукта за последний месяц? Где находятся самые перспективные клиенты? Кто поставляет самый продаваемый продукт? Список возможных вопросов позволяет определить дополнительные элементы данных для записи.
После сбора данных можно переходить к следующему действию. Распределение данных по таблицам Чтобы распределить данные по таблицам, выделите основные группы или темы.
9
Общие понятия теории баз данных. Основы работы с СУБД Microsoft Access
При разработке базы данных следует стремиться к однократному сохранению каждого элемента данных. Если данные повторяются (например, адрес отдельного поставщика), поместите их в отдельную таблицу.
После выбора темы для таблицы необходимо отслеживать, чтобы столбцы этой таблицы содержали данные только по этой теме. Например, таблица продуктов должна содержать только сведения о продуктах. Поскольку адрес поставщика относится к данным о поставщиках, а не о продуктах, он должен храниться в таблице поставщиков.
Преобразование элементов данных в столбцы
Чтобы определить столбцы таблицы, следует решить, какие сведения требуется отслеживать о предмете, записи о котором хранятся в таблице.
Следует также определить, какого рода данные будут храниться в базе данных: национального или международного уровня. В приведенном ниже списке содержится несколько советов по созданию столбцов.
1. Не включайте в таблицу вычисляемые данные
Не следует хранить в таблицах результаты вычислений. Вместо этого можно выполнять вычисления в Access каждый раз, когда необходимо получить результат.
2. Разбивайте информацию на минимальные логические компоненты
Может показаться, что лучше создавать единые поля для сохранения полных имен или названий продуктов вместе с их описаниями. Однако объединение различных данных в одном поле затрудняет их дальнейшее извлечение. Следует разбивать информацию на логические компоненты. После создания столбцов в таблицах можно выбрать первичный ключ для каждой таблицы.
Задание первичных ключей
Каждая таблица должна содержать столбец или набор столбцов для однозначного определения каждой строки таблицы. Как правило, в этих целях используется уникальный идентификационный номер, например код сотрудника или серийный номер. В базе данных такие сведения носят название первичного ключа таблицы. В Access первичные ключи служат для быстрого связывания данных из нескольких таблиц и их отображения для пользователя.
Если в таблице имеется уникальный идентификатор, например номер продукта, который однозначно определяет каждый продукт в каталоге, его можно использовать в качестве первичного ключа таблицы, но значения этого столбца должны быть различными для всех записей. Первичный ключ не должен содержать повторяющихся значений. Например, не следует использовать в качестве первичного ключа имена людей, т. к. они не являются уникальными. В одной таблице могут существовать две записи с одинаковыми именами.
Первичный ключ должен всегда иметь значение. Если столбец может содержать неназначенное или отсутствующее значение, его не следует использовать в качестве компонента первичного ключа.
Значение первичного ключа не должно меняться. В базе данных с несколькими таблицами первичный ключ одной таблицы может использоваться в качестве ссылки в других таблицах. Если первичный ключ изменяется, это изменение необходимо применить ко всем ссылкам на этот ключ. Благодаря использованию первичного ключа с постоянным значением снижается вероятность нарушения синхронизации с другими таблицами.
Часто в качестве первичного ключа используется произвольное уникальное числовое значение. Например, каждому заказу можно присвоить уникальный номер для однозначного определения заказа. Этот номер не подлежит изменению.
10