Сортировка записей

Настройка отображения записей в режиме таблицы

Ввод и редактирование данных таблицы.

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

Создание таблицы

Индексирование таблиц

Структура таблицы

Аннотация

Аннотация

Часть 4. СУБД ACCESS и ее структура

РАЗДЕЛ 3. СУБД ACCESS

Основные операции над данными

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

- Объединение таблиц с одинаковой структурой. Результат – общая таблица: сначала первая, затем вторая (конкатенация).

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

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

- Выборка (горизонтальное подмножество). Результат – выбираются записи, отвечающие определенным условиям.

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

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

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

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

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

Access входит в состав профессионального пакета программ Microsoft Office.

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

Access располагает разнообразными диалоговыми средствами, которые позволяют решать разнообразные задачи, не прибегая к программированию на язы­ке запросов SQL или Visual Basic.

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

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

Имеется возможность автоматического конструирования запросов, форм и отчетов и их элементов.

Для помощи начинающим пользователям имеются специальные программные средства - мастера (Wizard):

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

- Мастер по созданию форм и отчетов;

- Мастер по анализу таблиц, позволяет повысить эффективность базы данных за счет нормализации данных. Мастер разделяет ненормализованную таблицу на две или более таблиц меньшего размера, в которых данные размещаются без повторений (СЕРИС/Анализ/Таблица);

- Мастер подстановок создает в поле таблицы раскрывающийся список значений из другой таблицы для выбора и ввода нужного значения;

- Мастера по импорту/экспорту позволяют просматривать данные при импорте/экспорте текста или электронных таблиц, а также при экспорте БД в текстовые файлы;

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

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

В Access имеются и другие мастера.

Access может использовать данные других СУБД. Непосредственно могут обрабатываться файлы Paradox, dBase, FoxPro, Btrieve, а также файлы СУБД, поддерживающих стандарт доступа к данным JDBC (Open Database Connectivity) - Oracle, Microsoft SQL Serve, Sybase SQL Server.

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

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

Разграничение доступа осуществляется в соответствии с правами, предоставленными отдельным пользователям в сетевой операционной системе.

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

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

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

СУБД Access может функционировать в локальной сети, поддерживающей концепцию "клиент-сервер". В такой сети используется сервер баз данных SQL, который располагается на мощном компьютере, и позволяет выполнять размещенных на нем данных, обеспечивает их целостность и сохранность. Для управления БД на сервере используется язык структурированных запросов SQL (Structured Query Language).

Основными объектами базы данных, с которыми позволяет работать Access являются:

- таблицы;

- формы;

- запросы;

- отчеты;

- страницы доступа к данным;

- макросы;

- модули.

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

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

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

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

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

Макрос - это описание действий, которые должны быть выполнены в ответ на некоторое событие. Каждое действие реализуется макрокомандой. С помощью макросов можно выполнять такие действия, например, как открытие какой либо фор­мы, таблицы или отчета, выход из Aсcess и др.

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

Страницы доступа к данным представляют собой специальный тип Web-страниц, предназначенный для просмотра и работы через Интернет или интрасеть с данными, хранящимися в базах данных Microsoft Access или базах данных Microsoft SQL Server. Страница доступа к данным может также включать данные из других источников, таких как Microsoft Excel.

Часть 5. Технология работы с таблицами ACCESS

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

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

Структура таблицы определяется перечнем полей, для каждого из которых задается:

- имя поля;

- тип данных;

- перечень свойств;

- описание.

Имя поля должно быть уникальным, максимальная длина имени 64 символа, в имени можно использовать любые символы кроме «'», «.», «,», «!», «[», «]». Имя не должно начинаться с пробела.

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

Таблица может содержать следующие 9 типов полей:

- Текстовый. Короткий текст. Текст и числа, например, имена и адреса, номера телефонов и почтовые индексы. Текстовое поле может содержать до 255 символов.

- Поле Memo. Длинный текст и числа, например, комментарии и пояснения. Memo-поле может содержать до 64 000 символов.

- Числовой. Общий тип для числовых данных, допускающих проведение математических расчетов, за исключением расчетов для денежных значений. Свойство Размер поля позволяет указать различные типы числовых дан­ных. Длина - 8 байт.

- Дата/время. Значения даты и времени. Пользователь имеет возможность выбрать один из многочисленных стандартных форматов или создать специальный формат. Длина - 8 байт.

- Денежный. Денежные значения. Числа представляются с двумя знаками по­­с­ле запятой. Не рекомендуется использовать для проведения денежных расчетов значения, принадлежащие к числовому типу данных, так как последние могут округляться при расчетах. Значения типа "Денежный" всегда выводятся с указанным числом десятичных знаков после запятой. Длина - 8 байт.

- Счетчик. Автоматически вставляющиеся последовательные номера. Счетчик увеличивается на единицу для каждой следующей записи. Нумерация начинается с 1. Поле счетчика удобно для создания ключа. В таблице может быть только одно такое поле. Длина - 4 байта.

- Логический. Значения "Да"/"Нет", "Истина"/"Ложь", "Вкл"/"Выкл", т.е. одно из двух возможных значений. Длина - 1 байт.

- Поле объекта OLE. Объекты, созданные в других программах, поддержива­ю­щих протокол OLE, например графики, рисунки и т.п. Объекты связываются или внедряются в базу данных Micro­soft Access через элемент управления в форме или отчете.

- Гиперссылка. Строка, состоящая из букв и цифр, и представляющая адрес гиперссылки. Адрес гиперссылки может состоять максимум из трех частей: текст (текст, выводимый в поле или в элементе управления); адрес (путь к файлу или странице), дополнительный адрес (смещение внутри файла или страницы). Гиперссылка может содержать до 2048 символов.

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

Свойства полей зависят от выбранного типа данных. Наиболее важными являются следующие свойства:

- размер поля задает максимальный размер данных сохраняемых в поле;

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

- число десятичных знаков задает для числового и денежного типов данных число знаков после запятой (может быть от 0 до 15);

- маска ввода задает маску ввода, облегчающую ввод данных в элемент управления - поле. Например, удобно создать следующую маску ввода для поля «Телефон», позволяющую вводить только цифры и автоматически добавляющую промежуточные символы: (___) ___-____.

- подпись задает текст, который выводится в таблицах, формах и отчетах для обозначения (маркирования) поля, если подпись не задана, то выводится название поля;

- значение по умолчанию позволяет указать значение, автоматически вводящееся в поле при создании новой записи. Например, в таблице «Адреса» может оказаться удобным указать автоматический ввод значения «Москва» в поле «Город». При заполнении таблицы пользователи смогут оставить в этом поле стандартное значение или, при необходимости, указать другой город.

- условие на значение позволяет осуществить контроль ввода, определяет требования к данным, вводящимся в запись, в поле или в элемент управления;

- сообщение об ошибке позволяет указать текст сообщения, выводящегося на экран, если введенные данные нарушают условие, определенное в свойстве "Условие на значение";

- обязательное поле указывает, требует ли поле обязательного ввода значения. Если это свойство имеет значение «Да», то при вводе новой записи необходимо ввести значение в это поле или в любой присоединенный к нему элемент управления. Пустые значения в этом поле не допускаются. Например, можно потребовать, чтобы в элементе управления «Фамилия» в каждой записи обязательно выводилась какая-либо фамилия;

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

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

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

Индексированное поле может содержать как уникальные, так и повторяющиеся значения. Например, в таблице Сотрудники можно создать индекс по полю КодСотрудника, которое содержит уникальные значения кода, или по полю Фамилия, которое может содержать повторяющиеся значения.

Не допускается создание индексов для полей MEMO, гиперссылок и объектов OLE.

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

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

Ключевые поля таблицы индексируются автоматически.

Существует пять способов создания новой таблицы:

1). В режиме таблицы: данные вводятся в табличном режиме по строкам и столбцам. Имена полей определены по умолчанию ПОЛЕ1, ПОЛЕ2 и т.д. Тип поля и длину поля Access определяет на основании введенных значений. Структура таблицы формируется в соответствии с введенными значениями и потом может быть модифицирована.

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

3). В режиме мастера таблиц: создание таблицы на основе имеющихся шаблонов из мастера баз данных.

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

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

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

Создание структуры таблицы представляет собой многошаговый процесс:

- Определение имен полей, их типов и описаний;

- Определение свойств полей;

- Определение ключевого поля.

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

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

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

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

Access позволяет устанавливать связи следующих типов:

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

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

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

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

Одна из связанных таблиц является базовой, а вторая – подчиненной.

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

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

Если связанное поле базовой таблицы индексировано, то тип связи определяется в соответствии со следующими правилами:

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

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

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

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

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

В окне Схема данных отдельные таблицы можно скрывать и отображать.

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

Для устанавливаемой связи следует задать параметры.

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

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

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

Если включена поддержка целостности данных в установленной связи, то на схеме линия типа 1 — ∞ изображается более жирной.

Ввод данных в таблицы и их редактирование ведется в режиме таблицы. В режиме таблицы данные отображаются в строках и столбцах. Строки (записи) таблицы в зависимости от выполняемых операций помечаются маркерами:

► - маркер текущей операции;

! («карандаш») – маркер редактируемой записи;

Æ - маркер блокированной записи при работе в многопользовательском режиме;

*- маркер новой записи.

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

Ввод данных осуществляется после установки курсора в поле таблицы. При вводе данных в поле осуществляется автоматическая проверка данных типов: числовой, денежный, дата/время, логический. Кроме того, можно установить дополнительные правила проверки вводимых данных в свойстве поля «Условие на значение». Для ввода в поле текущей записи значения того же поля предыдущей записи следует нажать комбинацию клавиш CTRL+’ (апостроф), для ввода текущей даты – CTRL+; для ввода текущего времени – CTRL+:.

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

Access позволяет:

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

Изменить ширину поля. Это можно сделать командой ФОРМАТ/Ширина столбца или перетаскиванием правой границы столбца (указатель мыши должен принять вид крестика с двунаправленной стрелкой ¬½½®). Если дважды щелкнуть мышью у правой границы столбца, ширина столбца будет выровнена по значению поля.

Изменить высоту записи. Это действие можно выполнить командой ФОРМАТ/Высота строки или перетаскиванием верхней границы строки таблицы. Установленная высота будет применена ко всем записям. Высота строки указывается в пунктах.

Изменить шрифт отображения данных с помощью команды ФОРМАТ/Шрифт.

Столбцы можно скрывать и отображать.

Если установлен первичный ключ, то записи таблицы автоматически упорядочиваются в соответствии со значением первичного ключа. Сортировка записей может выполняться по одному или нескольким полям. Для этого необходимо установить указатель в поле на уровне любой записи или выделить один или несколько столбцов и выполнить команду ЗАПИСИ/Сортировка по возрастанию (по убыванию) или воспользоваться кнопками панели инструментов.

Для восстановления первоначального порядка расположения записей следует выполнить команду ЗАПИСИ/Удалить фильтр.