СУБД MS Access. Назначение, функциональные возможности. Построение простых реляционных таблиц

Тема:

СУБД MS Access. Назначение, функциональные возможности. Построение простых реляционных таблиц.

Итак, на предыдущей лекции рассмотрены основные принципы фактографических ИС:

  • структурирование данных и построение двумерных таблиц;
  • основы реляционного подхода
  • основы алгебры логики и принципы поиска информации.

На следующих трех лекциях мы изучим технику создания простейших ИС на примере СУБД MS Access 2000.

  1. Общие сведения об MS Access.

Запуск и общий интерфейс Access

MS Access – это набор инструментальных средств для создания и эксплуатации ИС. Другими словами средства MS Access предназначены для обслуживания двух принципиально разных функций ИС:

  • Проектирование и модификация структуры БД;
  • Манипуляция с данными ИС.

Ссылка на приложение MS Access обычно находится в одном из подчиненных меню пункта Главного меню Программы, а ярлык на рабочем столе имеет изображение ключа. Запуск и завершение MS Access осуществляется любым из стандартных способов, предусмотренных в среде Windows.

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

Поэтому в окне Access мы различаем следующие элементы интерфейса:

  • Три типа меню – горизонтальное (главное), ниспадающее и контекстное;
  • Панели инструментов;
  • Строки состояния;
  • Полосы прокрутки;
  • Различные элементы диалоговых окон и окон объектов (командные кнопки, списки и т.д.)

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

Принципиальная схема работы с MS Access

Создание БД.

После загрузки Access возможны два варианта продолжения (см. рис 2.1):

  • создать новую базу данных (файл *.MDB)
  • открыть существующую базу данных (файл *.MDB)

Рис. 2.1. Создание БД

Далее на экране появляется стандартный файлер (рис 2.2), в котором вам надо указать имя и адрес создаваемого файла БД. (Весь процесс создания БД и ее наполнения мы будем разбирать на примере макета БД ПОСТАВКИ, созданного на предыдущей лекции.)

Рис. 2.2. Сохранение созданного файла с именем Поставки.

Access – многооконное приложение, однако в любой момент может быть открыто только одна база данных. Именно ее окно является главным окном документа в приложении Access и его закрытие означает закрытие соответствующего файла *.MDB (рис 2.3).

Объекты Access

Итак, объектом обработки MS Access является файл базы данных, имеющий произвольное имя и расширение .MDB. В этот файл входят (или могут входить) основные объекты MS Access:

  • Таблицы (обязательный базовый объект Access, содержащий собственно данные)
  • Формы (необязательный элемент, который, однако, позволяет упростить операции ввода и просмотра данных)
  • Запросы (производная таблица, в которой собираются данные из других таблиц и проводятся над ними различные операции)
  • Отчеты (это тот же запрос, но красиво оформленный для представления на бумаге данных в наглядном виде)
  • Макросы (наборы простых инструкций по управлению данными, которые могут быть выполнены с помощью горячей клавиши или мыши)
  • Модули (это коллекция VBA-процедур, которая позволяет полностью автоматизировать работу с базой данных)

Каждый объект и работу с ним мы подробно рассмотрим позднее (кроме Макросов и Модулей, знакомство с которыми выходит за рамки нашего курса), а пока перечислим последовательность создания и эксплуатации ИС средствами Access:

  1. Проектирование базовых объектов ИС – двумерных таблиц, с разными типами данных (макеты таких таблиц Поставки, Клиенты и Продукты созданы "на бумаге" на предыдущей лекции).
  2. Установление связей между таблицами, с поддержкой целостности данных.
  3. Ввод, хранение, просмотр, сортировка, модификация и выборка данных из таблиц средствами Access. Эту группу операций на "описательном" уровне мы подробно рассмотрели на предыдущей лекции (контроль информации, индексирование таблиц, аппарат логической алгебры).
  4. Создание, модификация и использование производных объектов ИС (форм, запросов и отчетов).

По сути дела, таблицы и формы применяются для обслуживания "начинки" базы данных, а запросы и отчеты выполняют основную функцию ИС – извлечение, преобразование и представление информации.

Рис. 2.3. Главное окно базы данных.

Перечень возможных объектов БД находится в главном окне базы данных (слева)– таблицы, запросы и т.д. (рис. 2.3). Любой объект можно открыть в собственном (дочернем) окне. С дочерним окном можно работать автономно (открывать, закрывать, сохранять с заданным именем и т.п.) – точно так же, как это делается с файлами. Но главное окно базы данных при этом обязательно должно быть открыто, т.к. его закрытие закрывает всю БД вместе с содержащимися в ней объектами.

Следует четко различать две группы операций в Access:

  1. создание, открытие и закрытие файла базы данных (*.MDB);
  2. манипуляции с объектами Access: команды Сохранить и Сохранить как… применяются автономно к каждому открытому объекту БД (таблице, запросу и т.д.)

Итак, информационная система, созданная в MS Access – это файл базы данных .MDB плюс инструментарий самой Access. Эксплуатация такой ИС – это обработка документа .MDB приложением Access.

  1. Создание таблиц в MS Access.

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

В новой пустой базе данных нет созданных объектов. В панели инструментов окна базы данных доступна одна кнопка: «Создать», в данном случае – создать новую таблицу.

Таблица – это базовый объект Access: проектируются таблицы именно так, как обсуждалось на предыдущей лекции (рис. 2.4). Все остальные объекты являются производными и создаются только на базе подготовленных двумерных таблиц.

ПОСТАВКИ.

№

Имя поля

Дополнительная информация

Тип данных

Код поставки

-

числовые

Код клиента

-

числовые

Код продукта

-

числовые

Объем

т

числовые

Дата поставки

ДД.ММ.ГГГГ

данные типа даты

КЛИЕНТЫ.

№

Имя поля

Дополнительная информация

Тип данных

Код клиента

-

текстовые

Клиент

-

числовые

Телефон клиента

-

текстовые

Адрес клиента

-

текстовые

ПРОДУКТЫ

№

Имя поля

Дополнительная информация

Тип данных

Код продукта

-

текстовые

Продукт

-

числовые

Цена продукта

руб. / т

числовые

Рис. 2.4. Подготовленные макеты таблиц базы данных ПОСТАВКИ.

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

Рис. 2.5. Окно режима создания таблицы.

Совет для начинающих: таблицы и запросы создавать вручную, формы и отчеты – с помощью мастера.

Создание таблиц вручную.

Ввод, редактирование и удаление полей в режиме Конструктора

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

Рис. 2.6. Окно создания макета таблицы в режиме Конструктор.

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

Назначение имен полям.

В столбце Поле вы набираете произвольное имя первого поля. Правила назначения имен любым объектам Access очень просты:

  • имена не могут быть более 64 произвольных символов;
  • недопустимы: точка (.), знак восклицания (!), гравис (`), квадратные скобки ([ ]), управляющие символы ASCCI (с кодом от 0 до 31);
  • имя не может начинаться с пробела.

В имя могут входить и русские буквы. Нежелательно использовать пробелы (лучше использовать заглавную букву для второго слова (например, КодПродукта) или знак подчеркивания (Код_Продукта)), знаки пунктуации, стандартные названия функций и методов (Name, Date, Count). Неудобны длинные имена, т.к. при просмотре сильно растягиваю таблицу по горизонтали и делают ее плохо обозримой.

После ввода имени первого поля (КодПродукта) перемещаемся в столбец Тип данных. Для перемещения между столбцами и строками можно нажать Tab, Enter или просто щелкнуть мышкой на нужной ячейке.

Типы полей.

В Access допускаются следующие типы данных:

  1. Текстовый – произвольная последовательность символов длиной до 255;
  2. Числовой – любое число;
  3. Денежный
  4. Дата / время
  5. Логический - TRUE или FALSE
  6. Счетчик – тип поля "Счетчик" удобно применять для первичного ключа таблицы (если не используется иное). В качестве значений таких поле Access автоматически выбирает целые порядковые номера (1,2,3….). В дальнейшем номер, присвоенный записи при ее создании, не изменяется (независимо от удаления или вставки новых записей).
  7. Поле объекта OLE
  8. Гиперссылка

Например, последовательно вводим имена полей и задаем их типы (в соответствии с таблицей Продукты):

  • Полю КодПродукта присваиваем тип Счетчик, т.к. это поле является первичным ключом таблицы Продукты.
  • Полю Продукт присваиваем тип Текстовый
  • Полю Цена присваиваем тип Денежный

Бланк свойств поля.

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

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

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

Для текстового поля надо указать размер – допустимую длину значения поля в символах (например, 20 или 40).

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

Для поля Дата/время надо указать формат даты – выбрать из раскрывающегося списка, например Каткий формат даты (в русской версии ДД.ММ.ГГ) или Краткий формат времени (ЧЧ.ММ). Тогда система будет знать, как обрабатывать введенные значения.

В качестве значения свойства Условие на значение можно указать правило верификации, т.е. логическое выражение, которое не пропускает ввод ошибочных значений в поле. Например, если при составлении базы успеваемости для поля БАЛЛ записать: БАЛЛ>=1 AND БАЛЛ <=50, система признает ошибочным любые значения, кроме 1-50.

В свойстве Сообщение об ошибке можно записать любое сообщение, которое выводится на экран при попытке ввести недопустимое значение при заполнении таблицы (например, "Введите значение балла 1-50").

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

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

Сохранение таблицы.

Завершив создание макета таблицы, выберите команду Файл-Сохранить как и присвойте имя новой таблице. Правила именования для таблиц такие же, как и для полей. Если вы попытаетесь закрыть окно конструктора, не сохранив макет, система напомнит, что надо дать таблице имя (рис. 2.7), либо аннулировать свою работу.

Рис. 2.7. Окно сохранения макета таблицы с именем Продукты.

Ключевые поля.

Если на этапе конструирования макета ключевое поле не было задано, то при сохранении система обязательно напомнит вам об этом (рис 2.8).

Рис. 2.8.

Если для некоторого поля был задан тип поля "Счетчик" (как в примере для поля КодПродукта), то система автоматически выберет это поле в качестве первичного ключа таблицы (при подтверждении создания ключевого поля).

Открытие существующей таблицы.

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

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

  1. Слева от поля КодПродукта появилось изображение ключа (ключевое поле)
    1. В свойстве Индексированное поле появилось: "Да, совпадения не допускаются".

Рис. 2.9. Окно базы данных с созданной таблицей Продукты

Рис. 2.10. Окно таблицы Продукты (в режиме Конструктор).

Заполнение созданной таблицы в режиме Таблица.

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

Рис. 2.11 а. Окно таблицы Продукты (в режиме Таблица).

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

Кроме того, по таблице можно перемещаться с помощью стрелок курсора.

Рис. 2.11 б. Результат заполнения таблицы Продукты (в режиме Таблица).

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

Импорт таблиц.

Средства Access позволяют не только вручную создавать и заполнять таблицы, но и работать с данными других форматов. Например, можно в Access импортировать данные, хранящиеся в формате TXT (текстовые файлы) или XLS (электронные книги или таблицы Excel). Для этого при создании новой таблицы выберем в окне Новая таблица (рис. 2.5) режим Импорт таблиц.

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

Создадим в БД ПОСТАВКИ новую таблицу Клиенты путем импорта данных из текстового файла.

Пусть данные о клиентах хранятся в файле Клиенты.txt. Известно, что это текстовый файл с разделителем данных (точка с запятой). В качестве данных приводятся: название фирмы-покупателя; телефон; адрес.

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

  1. В режиме Импорта таблиц откроется диалоговое окно Импорт (стандартный файлер). Задав Тип файла -Текстовые файлы, найти папку, содержащую нужный текстовый файл и подтвердить его импорт (файл Клиенты.txt).
  2. В окне мастера Импорт текста
    • Шаг 1: задать параметр с разделителями
    • Шаг 2: установить разделитель (точка с запятой)
    • Шаг 3: данные сохранить в новой или существующей таблице (в новой)
    • Шаг 4: описать каждое поле (задать имя, тип данных и если нужно, индекс)

В соответствии с раннее разработанной структурой таблицы Клиенты (рис 2.4) задаем поля: Клиент, текстовое; Телефон, текстовое; Адрес, текстовое.

  • Шаг 6: выбрать параметр определения первичного ключа таблица. Можно указать автоматическое создание ключа, выбрать поле, которое будет использоваться в качестве или вообще не создавать первичный ключ.

Выбираем автоматическое создание ключа (добавляется ключевое поле Код типа Счетчик размером Длинное целое, индексированное Совпадения не допускаются)

  • Шаг 7: присвоить создаваемой таблице имя (по умолчанию предлагается имя текстового файла).

Таблице присваиваем имя Клиенты.

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

Создание новой таблицы путем импорта из файла Excel (с ключевым полем)

Создадим в БД ПОСТАВКИ новую таблицу Поставки путем импорта данных из таблицы Excel.

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

При импорте данных из электронной таблицы Excel с помощью интерфейса Access используется мастер Импорт электронной таблицы, который содержит подробные инструкции по выполнению операции импорта.

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

Рис. 2.12. Макет таблицы Клиенты.

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

  1. В режиме Импорта таблиц откроется диалоговое окно Импорт (стандартный файлер). Задав Тип файла –Microsoft Excel, найти папку, содержащую нужный файл и подтвердить его импорт (файл Поставки.xls)
  2. В окне мастера Импорт электронной таблицы:
    • Шаг 1: выбрать листы или поименованные диапазоны (выбираем диапазон Postavky)
    • Шаг 2: установить флажок, если Первая строка содержит заголовки столбцов (да)
    • Шаг 3: данные сохранить в новой или существующей таблице (в новой)
    • Шаг 4: описать каждое поле (задать имя и если нужно, индекс). Типы данных Excel обычно преобразуются в соответствующий тип данных Access, но, если этого не произошло, то использовать раскрывающийся список Тип данных. Если список Тип данных отключен, то для выбранного поля доступен только один тип данных.

В соответствии с раннее разработанной структурой таблицы Поставки (рис 2.4) задаем поля: КодПоставки, идекс (да, Совпадения не допускаются); Дата; Объем; КодКлиента, идекс (да, Совпадения допускаются); КодПродукта, идекс (да, Совпадения допускаются) (рис.2.13).

  • Шаг 5: выбрать параметр определения первичного ключа таблица. Можно указать автоматическое создание ключа, выбрать поле, которое будет использоваться в качестве или вообще не создавать первичный ключ.

Выбираем в качестве первичного ключа поле КодПоставки

  • Шаг 6: присвоить создаваемой таблице имя (по умолчанию предлагается имя импортируемого диапазона).

Таблице присваиваем имя Поставки.

Рис. 2.13. Диалоговое окно описания полей импорта из таблицы Excel.

После завершения импорта надо обязательно поправить таблицу в Конструкторе: изменить размер для полей с индексом (КодПоставки, КодПродукта и КодКлиента) на Длинное целое, т.к. ключевые поля впоследствии могут участвовать в связывании таблиц по полю, имеющему тип Счетчик.

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

Общие положения.

Между одноименными полями двух таблиц Access автоматически устанавливает связь. Например, между таблицами Поставки и Продукты устанавливается связь по полю КодПродукта. Это означает, что при составлении отчетов по поставкам, Access соединит каждую запись с наименованием продукта из таблицы Продукты со всеми записями таблицы Поставки, в которых значение поля КодПродукта (цифровое) совпадает со значением этого поля в записи таблицы Продукты (например, 1 заменится на Бензин автомобильный Аи-95).

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

Техника создания связей между таблицами.

Установление связей между таблицами рассмотрим на конкретном примере – нашей базе данных ПОСТАВКИ.

Выберите команду Сервис_Схема данных. На экране появится диалоговое окно (рис. 2.14) со списком всех таблиц открытой базы данных. Надо указать системе те таблицы, между которыми вы устанавливаете связи. Выделите таблицу Поставки и нажмите кнопку Добавить, затем то же самое проделайте с таблицами Клиенты и Продукты. Нажмите кнопку Закрыть. На экране появится окно документа Схема данных (рис. 2.15)

Рис. 2.14. Окно добавления таблиц в схему данных.

Это окно содержит все таблицы базы данных, между которыми устанавливаются связи. Для установления связи между двумя таблицами можно методом "Drag-and-Drop" переместить имя поля с первичным ключом главной таблицы на одноименной поле подчиненной таблицы (на рис. 2.15 показана уже завершенная схема).

Рис. 2.15. Окно документа Схема данных

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

На экране появится диалоговое окно Изменение связи (рис. 2.16).

Рис. 2.16. Окно определения параметров связи между таблицами.

В этом окне установите флажок "Обеспечение целостности данных". Этим вы включаете механизм поддержки целостности данных в таблице Продукты и Поставки.

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

Целостность данных.

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

Целостность данных означает:

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

При попытке нарушить эти запреты, Access выдает сообщение об ошибке.

Каскадное обновление поле и каскадное удаление записей.

При установке характеристик связи (рис. 2.16) можно установить любой флажок (или оба) каскадной модификации – обновления или удаления.

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

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

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

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

Точно так же устанавливается связь между таблицами Клиенты и Поставки.

Для удаления связи надо выделить ее (щелкнуть мышью на линии связи) и нажать клавишу {Del}.

10

СУБД MS Access. Назначение, функциональные возможности. Построение простых реляционных таблиц