Теоретические материалы Access

 База данных (БД)  поименованная совокупность структурированных данных, относящихся к определенной предметной области.  Система управления базами данных (СУБД)  комплекс программных и языковых средств, необходимых для создания баз данных, поддержания их в актуальном состоянии и организации поиска в них необходимой информации.  SQL (structured query language)  формальный непроцедурный язык управления данными в реляционной базе данных 2

Ядром любой базы банных является модель данных. Модель данных  это совокупность структур данных и операций их обработки. СУБД основывается на использовании иерархической, сетевой или реляционной модели. Реляционная модель данных – множество взаимосвязанных двумерных массивов (таблиц). 3

Структурные элементы базы данных:  Поле  элементарная единица логической организации данных, которая соответствует неделимой единице информации  реквизиту. Для описания поля используются следующие характеристики: имя, тип, размер.  Запись  совокупность логически связанных полей. Экземпляр записи  это отдельная реализация записи, содержащая конкретные значения ее полей.  Таблица  Совокупность экземпляров записей одной структуры. 4

Имя поля 1 Имя поля 2 Имя поля 3 Имя поля 4 Запись Поле 5

Номер Фамилия Имя Отчество Дата рожд Группа 68130 Иванов Иван Иванович 06.01.92 691 68131 Петров Петр Петрович 01.06.91 692 68132 Сидорова Анна Львовна 03.04.93 691 6

Имя поля Тип поля Размер/ Признак Формат ключа Номер Текстовый 5 * 15 Фамилия Текстовый 10 15 Имя Текстовый Краткий Отчество Текстовый формат даты Дата рожд Дата/Время 4 Группа Текстовый 7

Ориентирована на организацию данных в виде двухмерных таблиц, обладающих следующими свойствами : 1. Каждый элемент (ячейка) таблицы  один элемент данных. 2. Все столбцы в таблице однородные, т.е. все элементы в столбце имеют одинаковый тип (числовой, символьный и так далее) и длину. 3. Каждый столбец имеет уникальное имя. 4. Одинаковые строки в таблице отсутствуют. 5. Порядок следования строк и столбцов  произвольный. 8

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

Нормализация отношений – формальный аппарат ограничений на формирование отношений (таблиц), который позволяет устранить дублирование, обеспечивает непротиворечивость хранимых в базе данных, уменьшает трудозатраты на ведение базы данных. Выделяются 3 нормальные формы отношений (таблиц). 10

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

Функционально полная зависимость неключевых реквизитов заключается в том, что каждый неключевой атрибут функционально зависит от ключа, но не находится в функциональной зависимости ни от какой части составного ключа. Отношение Студент=(Номер, Фамилия, Имя, Отчество, Дата, Группа) находится в первой и во второй нормальной форме одновременно, так как описательные реквизиты определены и функционально зависят от ключа Номер. 12

Отношение Успеваемость=(Номер, Фамилия, Имя, Отчество, Дисциплина, Оценка) находится в первой нормальной форме и имеет составной ключ Номер +Дисциплина. Это отношение не находится во второй нормальной форме, так как атрибуты Фамилия, Имя, Отчество не находятся в полной функциональной зависимости с составным ключом отношения (а только с его частью – Номер). 13

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

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

С целью нормализации базы данных необходимо расщепить таблицы: Студент1 Студент Группа Номер Номер Группа Фамилия Фамилия Староста Имя Имя Отчество Отчество Группа Группа Староста 16

Существуют следующие типы связи информационных объектов предметной области:  Один к одному (1:1)  Один ко многим (1:М)  Многие ко многим (М:М) 17

Пример: Дана совокупность информационных объектов, отражающих учебный процесс в вузе: СТУДЕНТ (Номер, Фамилия, Имя, Отчество, Дата, Группа) СЕССИЯ (Номер, Оценка1, Оценка2, Оценка3, Оценка4, Результат) СТИПЕНДИЯ (Результат, Процент) ПРЕПОДАВАТЕЛЬ (Код преподавателя, Фамилия, Имя, Отчество) 18

Связь 1:1 предполагает, что в каждый момент времени одному экземпляру информационного объекта A соответствует не более одного экземпляра информационного объекта B и наоборот. A1 B1 A2 A B A3 B2 Примером связи 1:1 может служить связь между объектами СТУДЕНТ СЕССИЯ Каждый студент имеет определенный набор экзаменационных оценок в сессию. 19

При связи 1:М одному экземпляру информационного объекта A соответствует 0,1 или более экземпляров объекта B, но каждый экземпляр объекта B связан не более чем с одним экземпляром объекта A. А1 B1 A2 B2 AB A3 B3 Пример связи 1:M СТИПЕНДИЯ СЕССИЯ Установленный размер стипендии по результатам сдачи сессии может повторяться многократно для различных студентов. 20

Связь М:М предполагает, что в каждый момент времени одному экземпляру информационного объекта A соответствует 0,1 или более экземпляров объекта B и наоборот. A1 B1 A2 B2 AB A3 B3 Пример связи М:М СТУДЕНТ ПРЕПОДАВАТЕЛЬ Один студент обучается у многих преподавателей, один преподаватель обучает многих студентов. 21

В Access БД - это общее хранилище данных и соответствующих им объектов. БД Access может содержать объекты:  Таблицы  Запросы  Формы  Отчеты  Макросы  Модули 22

Access может работать одновременно только с одной БД, включающей различные объекты, которые хранятся в единственном файле с расширением .accdb. Для автоматизации процесса создания отдельных объектов БД используются: конструкторы – специализированные диалоговые средства с панелями инструментов; мастера – специальные программные средства:  мастер баз данных (шаблоны типовых БД);  мастер по созданию форм и отчетов;  мастер по анализу таблиц;  мастер защиты и др. 23

 Конструирование (создание БД): ◦ Создание структур таблиц ◦ Заполнение таблиц данными ◦ Конструирование запросов, форм, отчетов ◦ Автоматизация управления БД путем создания макросов и модулей  Эксплуатация БД: ◦ Ввод и редактирование данных таблиц ◦ Выборка данных из таблиц (запросы, формы) ◦ Отображение и печать данных (отчеты) 24

В реляционных базах данных вся информация хранится в виде таблиц. Каждая строка таблицы представляет собой запись, а столбец – поле. Запись содержит набор данных об одном объекте, а поле – однородные данные обо всех объектах. Создание новой таблицы начинается с задания структуры записи таблицы (имя поля, тип поля, перечень свойств и описание). Имя поля:  до 64 символов (включая пробелы)  не используются символы („.,![ ] *;:!#&-”$%)  имя не должно начинаться с пробела 25

Типы данных полей:  текстовый (до 255 символов) – текст и числа, не требующие вычислений;  МЕМО (Длинный текст и числа, до 65536 символов);  числовой (разные типы и форматы (1, 2, 4 или 8 байт). Конкретный тип числа определяется значением свойства «Размер поля».  дата/время - значения дат и времени (8 байт);  денежный (8 байт);  счетчик - автоматически формируемые последовательные номера, увеличивающиеся на 1. (4 байта);  логический - да/нет, истина/ложь (1 бит);  объект OLE (рисунки, диаграммы, звук, видео, документы WORD, таблицы EXCEL и так далее) до 1 Гбайт;  Гиперссылка - текст, который используется в качестве адресов гипертекстовых связей (адреса Web-страниц) до 64000 символов; 26

Для осуществления одновременной выборки данных из нескольких таблиц их следует связать между собой. Связываться (1:1, 1:М) могут только таблицы, имеющие ключевые поля. При этом связывают первичный ключ одной таблицы с полем такой же структуры и типа другой таблицы. Ключевое поле (первичный ключ) однозначно определяет каждую запись в таблице; повторяющиеся значения ключа не допускаются. Если данные одного поля не могут быть уникальными для каждой записи, можно назначить ключевыми несколько полей. Связать можно только те таблицы, которые содержат поля с одинаковыми значениями. Имена этих полей могут не совпадать, обязательно должны совпадать типы данных, длины полей, а главное - значения. 27

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

Запросы  основное средство обработки данных. Запрос  это шаблон выборки информации из таблиц по определенным критериям. В запросе можно:  использовать до 32 таблиц;  производить сортировку по 10 полям. После создания запроса Access отображает множество записей, соответствующих запросу  динамический набор данных (НД). Динамический НД формируется выглядит как таблица, но таблицей не является  временный (виртуальный) набор записей, не хранится в БД. После закрытия запроса динамический НД прекращает свое существование. При сохранении запроса сохраняется только его структура. При каждом выполнении запроса динамический НД создается заново. 29

Запросы классифицируются по следующим признакам:  По числу обрабатываемых таблиц: ◦ однотабличные ◦ многотабличные (все таблицы и запросы д.б. связанными)  По типу языка запросов: ◦ QBE (Query By Example) -запрос по образцу, построенный на основе реляционного языка запросов графического типа; Эти запросы строятся с помощью конструктора в окне конструктора запросов. ◦ SQL (Structured Query Language) - структурированный язык запросов, представляющий собой последовательность операций и функций языка SQL. Как правило, запросы создаются с использованием средств языка QBE. Запросы QBE автоматически транслируются на язык SQL.  По типу алгоритмов обработки: ◦ Запросы- выборки ◦ Запросы-действия 30

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

Запросы-действия предназначены для выполнения требуемых действий над данными таблиц. Они позволяют добавлять, изменять или удалять данные. Существует 4 вида запросов-действий:  на создание новой таблицы - автоматическое формирование структуры записи новой таблицы и загрузка;  на добавление - ввод новых записей, являющихся результатом выполнения запроса;  на удаление - удаление группы записей из таблицы;  на обновление (изменение значений полей).  Сначала создается запрос на выборку, затем его тип можно изменить 32

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

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

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

 Имена объектов -это идентификаторы. Они относятся к реальным объектам: таблицам, формам, отчетам, элементам управления и полям. Для выделения имен объектов их заключают в [ ] Например: [Дата рождения]  Функции – возвращают вычисленные значения (Date, Sum). VBA Access позволяет создавать пользовательские функции.  Литералы представляют значения (числовые, строковые, дата) в том виде, как они записаны: 280, #6-май-02#, “Paris”  Константы True, False, Да, Нет, Null представляют значения, остающиеся неизменными. 36

Типы операторов:  арифметические (^ * / \\ Mod +-)  сравнения (=, >, <, >=, <=, <>)  строковые (& , Like)  логические (NOT, AND, OR)  вспомогательные: (Between … And, In, Is)  Специальные операторы идентификации: (.) и (!) 37

 & - конкатенация  Like - сравнивает два строковых объекта с помощью шаблона, состоящего из символов подстановки. выражение Like шаблон Если объект- выражение соответствует объекту-шаблону, то формула возвращает истинное значение. Например: [Фамилия] Like «И[Вв]» – истинно, если в поле Фамилия находится строка текста, которая начинается с символов «Ив» или «ИВ» 38

 Between … And определяет принадлежность значения указанному диапазону. (Выражение Between Значение1 And Значение2) результат данного значения будет истинным, если величина, заданная параметром Выражение находится в указанных пределах, определяемых параметрами Значение1 и Значение2 IIF([Сумма] Between 0 And 250, «Выплата», «Отложить»)  Оператор In проверяет, совпадает ли значение выражения с одним из элементов указанного списка. Выражение In (Значение1, Значение2, …) Пример: IIF([Животные] In («Кошка», «Собака»), «Домашние», «Экзотические»)  Оператор Is используется для сравнения двух переменных, содержащих ссылки на объекты. Часто используется вместе с ключевым словом Null для определения, является ли объект пустым (Is Null) Пример: IIF([Фамилия клиента] Is Null, «Ввести фамилию клиента»] 39

Специальные операторы идентификации: (.) и (!)  (!) - ключевой оператор(символ), так как используется вместе с зарезервированными словами. За (!) всегда следует имя объекта: ◦ Forms!имя_формы ◦ Forms![Студент] ◦ Forms![Студент]![Дата рождения]  Оператор идентификации (.) отделяет свойства заданного объекта: ◦ Forms![Студент]![Дата рождения].Visible 40

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

Существует несколько режимов работы с формами:  Режим формы (пользовательский, используется для просмотра, добавления, изменения записей в таблице или запросе)  Режим таблицы (то же, что и форма, но не применяются свойства форматирования элементов управления)  Режим конструктора (используется разработчиками, позволяет изменять структуру и внешний вид формы, добавлять и удалять элементы управления)  Режим сводной таблицы (используется для представления и анализа исходных данных) 42

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

Конструирование форм Access предлагает следующие способы создания формы:  С применением Автоформы. Автоформа позволяет создавать формы пяти типов: в столбец, ленточную, табличную, сводную таблицу и сводную диаграмму. При этом в форму вставляются все поля источника данных.  С помощью мастера форм. В процессе диалога с пользовате­лем мастер создает форму одного из стандартных типов (и еще выровненную). При этом в форму вставляются выбранные пользователем поля из источ­ника данных.  С помощью конструктора форм. Форма конструируется пользователем в окне конструктора форм. 44

Отчеты предназначены для вывода информации из базы данных и обеспечивают наиболее гибкий способ просмотра и распечатки итоговой информации. Они позволяют отображать и печатать данные с любой степенью детализации. В отчете можно получить результаты сложных расчетов, статистических сравнений, поместить в него рисунки и диаграммы. Существует три режима работы с отчетами:  Режим Конструктор (можно изменять структуру и внешний вид, отчета, добавлять и удалять поля).  Режим Образец (средство тестирования создаваемого отчета. Выводятся только данные, необходимые для отображения каждого элемента управления отчета, могут не совпадать с реальными данными);  Режим Предварительный просмотр. 45

Существуют следующие способы создания отчетов:  С помощью конструктора  С помощью мастера отчетов  Используя автоотчет. Автоотчет позволяет быстро создать отчет одного из следующих видов: ◦ Ленточный (Данные распечатываются в виде строк и столбцов. Их разновидности включают отчеты с итоговым и групповым суммированием). ◦ В столбец (Данные распечатываются в виде формы с возможным включением итогов и графиков). 46

Основные виды отчетов:  отчет в одну колонку (в столбец);  многоколонный отчет;  табличный отчет;  отчет с группировкой данных и подведением итогов;  перекрестный отчет;  составной отчет и др. 47

 Макросы - включают простые команды для автоматизации выполнения задач без программирования.  Модули - программы, написанные на VBA. 48