Часть I. Основы технологии Microsoft Access

Глава 3. Создание базы данных

Самоучитель Microsoft Access 2003
Бекаревич Ю.Б., Пушкина Н.В.


Создание файла базы данных Access

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

Непосредственный ввод данных в таблицы

Схема данных в Access

Отображение записей подчиненных таблиц в главной таблице

Модификация структуры базы данных

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

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

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

Завершается создание базы данных процедурой загрузки, т.е. заполнением таблиц конкретными данными. Особое значение имеет технология загрузки взаимосвязанных данных. Удобным инструментом загрузки данных во взаимосвязанные таблицы являются формы ввода/вывода, обеспечивающие интерактивный интерфейс для работы с данными базы. Формы позволяют создать экранный аналог документа источника, через который можно вводить данные в несколько взаимосвязанных таблиц. В настоящей главе рассматривается непосредственный ввод данных в таблицы.

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

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

Для отображения или скрытия области задач при запуске Access выполняется команда Сервис|Параметры (ToolsjOptions), где на вкладке Вид (View) в группе Отображать устанавливается или снимается флажок область задач при запуске.

Начать создание базы данных можно и с помощью команды Файл Создать (Fiie|New) или кнопки Создать (New) на панели инструментов База данных (Database).

В области задач в группе Создание (New) представлены четыре строки: Новая база данных, Пустая страница доступа к данным, Проект с имеющимися данными и Проект с новыми данными.

Новая база данных (NewDatabase) позволяет приступить к созданию новой оригинальной базы данных с пустого файла.

Пустая страница доступа к данным (DataAccessPage) позволяет создавать в режиме конструктора на основе таблиц и запросов Web-страницы специального типа. Страницы доступа к данным обеспечивают не только просмотр актуальной информации, но и ее редактирование, удаление и добавление в базу данных Microsoft Access или Microsoft SQL Server no Интернету или локальной сети. Страница доступа к данным сохраняется в отдельном файле вне базы данных Microsoft Access. Если во время выполнения этой команды база данных не открыта, необходимо выбрать источник данных для создаваемой страницы. При открытой базе данных она автоматически выбирается в качестве источника данных, и в окне базы данных на вкладке Страницы (Pages) автоматически создается ярлык к файлу, где будет храниться страница. Разработка страниц ведется в интерактивном режиме средствами, аналогичными тем, что используются при конструировании форм или ответов, однако и в разработке, и во взаимодействии со страницами имеются значительные отличия.

Работать со страницей доступа к данным можно непосредственно в среде Access. Страницы могут использоваться в приложении базы данных наряду с формами и отчетами. Пользователь из сети может открыть страницу доступа к данным и работать через нее со связанной базой данных с помощью программы Microsoft Internet Explorer 5.01 с пакетом обновления SP2 или более поздней версии. При этом наличие Access или Office Developer Edition не является обязательным.

Проект с имеющимися данными Project (Existing Database) и Проект с новыми данными — Project (New Database) позволяют создать проект-приложение пользователя, которое работает с базой данных, размещенной на SQL-сервере. Причем проект можно создать для уже существующей на сервере базы данных или одновременно с проектом начать создание новой базы данных.

Строка Из имеющегося файла предполагает создание новой базы данных на основе уже существующего файла.Строка Шаблоны позволяет создать базу данных с помощью шаблона, выбранного на рабочем компьютере (строка На моем компьютере) или на Web-узле Microsoft Office Online (www.Microsoft.com). Кроме того, по ключевым словам можно реализовать поиск шаблона в сети. Строка На моем компьютере открывает окно с двумя вкладками (рис. 3.1).

Рис. 3.1 Окно выбора варианта создания базы данных

На вкладке Общие (General) значками представлены все перечисленные средства создания баз данных и приложений. Вкладка Базы данных (BlankDatabase) предлагает создать одну из типовых баз данных с помощью мастера на основе существующих шаблонов. На вкладке представлены шаблоны этих баз данных. Чтобы начать работу мастера, достаточно щелкнуть на значке нужной базы данных.

Создание файла базы данных Access

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

Для создания файла новой пустой базы данных выберем в области задач Создание файла строку Новая база данных (New Database). В открывшемся окне Файл новой базы данных (File New Database) (рис. 3.2) раскроем список Создать папку (Save in) и выберем папку, в которой будет храниться файл, зададим имя файла новой базы данных, например, "Поставка TOBapoB.mdb", и нажмем кнопку Создать (Create). В результате откроется окно новой базы данных <имя БД>: база данных (Database ).

Рис. 3.2 Окно создания файла новой базы данных

Файлы, сохраняемые в Access, как и в других приложениях MS Office, по умолчанию попадают в папку Мои документы (My Documents). Для изменения используемой по умолчанию папки для файлов новых баз данных надо выполнить команду Сервис|Параметры (Tools|Options) и в появившемся диалоговом окне на вкладке Общие (General) в поле Рабочий каталог (Default Database Folder) ввести путь к папке, в которой предполагается хранить новые базы данных. Команда Сервис|Параметры (Tools|Options) доступна только при открытой базе данных.

Задавая имя файла базы данных, следует иметь в виду, что его предельная длина составляет 255 символов, включая пробелы, в Microsoft Windows NT 4.0 и 215 символов, включая пробелы, в Microsoft Windows XP или Millennium Edition. Имена файлов не должны содержать следующих символов: /, *, \, ?, “ , <>,|.

Тип файла по умолчанию имеет значение Базы данных Microsoft Access(*.mdb) (MicrosoftAccess Databases), что приводит к созданию файла базы данных, имеющего расширение mdb. Это расширение является зарегистрированным в Windows для данного типа файлов и связывается с программой Access.

Вертикальный ряд больших кнопок слева в окне Файл новой базы данных (FileNew Database) предназначен для быстрого открытия, просмотра содержимого и выбора папки для сохранения файла (рис. 3.2). Для отображения в папке только файлов баз данных в поле списка Тип файла (File of type) устанавливается тип Базы данных Microsoft Access(*.mdb) (Microsoft Access Databases).

Кнопки на панели инструментов окна Файл новой базы данных (File New Database) обеспечивают удобный поиск и переход к папке, в которой необходимо создать файл базы данных. По следующей кнопке Переход на один уровень вверх (Up One Level) выполняется переход к просмотру папки на уровень выше в иерархии папок. По кнопке Представления (Views) открывается список команд для настройки вида информации об отображаемых в окне файлах. Кнопкой Сервис (Tools) открывается список команд, которые позволяют удалить или переименовать выделенный объект. Команда Свойства отобразит информацию о базе данных, а для папки откроет общий доступ пользователям вашей сети и правила доступа. Кроме того, на панели инструментов представлены кнопки для удаления файлов или папок, создания новых папок, для поиска в Интернете.

В окне новой базы данных <имя БД>: база данных (Database) (рис. 3.3) <имя БД> соответствует заданному названию в окне Файл новой базы данных (File New Database) (рис. 3.2).

Рис. 3.3 Окно пустой базы данных «Поставка товаров»

В окне новой базы данных в разделе Объекты (Objects) вертикальным рядом кнопок представлены все типы объектов, которые могут быть созданы в БД: таблицы, запросы, формы, отчеты, страницы, макросы и модули. Для новой базы данных список объектов любого выбранного типа отсутствует.

Создание файла базы данных Access

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

Непосредственный ввод данных в таблицы

Схема данных в Access

Отображение записей подчиненных таблиц в главной таблице

Модификация структуры базы данных

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

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

Для создания новой таблицы в окне базы данных (рис. 3.3) выберите объект Таблицы (Tables) и нажмите кнопку Создать (New). В открывшемся окне Новая таблица (New Table) выберите один из режимов создания таблицы (рис. 3.4). Первые три режима (как основные) вынесены в рабочее поле, предназначенное для отображения списка существующих в БД таблиц (рис. 3.3). Это позволяет сразу перейти в нужный режим создания таблицы, сократив число выполняемых пользователем операций.

Рис. 3.4 Диалоговое окно для выбора режима создания таблицы

Строка Создание таблицы в режиме конструктора (Create table in Design View) в рабочем поле окна базы данных или Конструктор (Design View) в окне Новая таблица (New Table) определяет выбор основного способа создания новой таблицы, при котором создание таблицы начинается с определения ее структуры в режиме конструирования. В этом режиме пользователь может сам установить параметры всех элементов структуры таблицы.

Определение структуры новой таблицы в режиме конструктора

При выборе режима конструктора таблиц выводится окно Таблица 1: таблица (TableI: Table), в котором определяется структура таблицы базы данных (рис. 3.5).

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

Список названий панелей инструментов, где отмечаются активные панели инструментов, вызывается щелчком правой кнопки мыши на любой панели инструментов или строке меню команд Access. Название кнопки на панели инструментов появляется (всплывает) при установке курсора мыши на кнопку.

Определение полей таблицы

Для определения поля в окне Таблица (Table) (рис. 3.5) заполняются поля Имя поля (Field Name), Тип данных (Data Type), Описание (Description) — краткий комментарий — а также свойства поля в разделе Свойства поля (Field Properties). На вкладке Общие (General) представлены строки свойств поля, в том числе максимальный размер, подпись, которая выводится в заголовке столбца, значение по умолчанию и другие (рис. 3.5). На вкладке Подстановка (Lookup) выбирается тип элемента управления (Display Control): поле, список или поле со списком.

Имена полей и типы данных

Имя поля (Field Name). Каждое поле в таблице должно иметь уникальное имя, удовлетворяющее соглашениям об именах объектов в Access. Оно является комбинацией букв, цифр, пробелов и специальных символов, за исключением точки".", восклицательного знака"!", надстрочного знака"1" и квадратных скобок"[ ]". Имя не может начинаться с пробела и содержать управляющие символы с кодами ASCII от 00 до 31. Максимальная длина имени — 64 символа.

Тип данных (Data Type). Тип данных определяется значениями, которые предполагается хранить в поле, и операциями, которые будут выполняться с этими значениями. В Access допускается использование девяти типов данных. Список возможных типов данных вызывается нажатием кнопки списка при выборе типа данных каждого поля (рис. 3.5). Подробные сведения обо всех типах данных, поддерживаемых в Access, их назначении и размерах можно посмотреть в Справке к программе. В строке меню в поле Введите вопрос наберите "типы данных". В открывшейся области задач Справка Access просмотрите разделы: "Типы данных, которые могут иметь поля в Microsoft Access (MDB)" и "Типы данных и размеры полей (MDB)".

Общие свойства поля

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

Размер поля (Field Size) задает максимальный размер данных, сохраняемых в поле. Для поля с типом данных Текстовый задается размер от 1 до 255 знаков (по умолчанию 50 знаков). Для поля с типом данных Числовой можно задать следующие значения:

  • Байт (Byte) для целых чисел от 0 до 255, длина поля 1 байт;

  • Целое (Integer) для целых чисел от -32 768 до 32 767, занимает 2 байта;
  • Длинное целое (Long Integer) для целых чисел от -2 147 483 648 до 2 147 483 647, занимает 4 байта;

  • Одинарное с плавающей точкой (Single) для чисел от -3,4х1038 до 3,4х1038 с точностью до 7 знаков, занимает 4 байта;

  • Двойное с плавающей точкой (Double) для чисел от ~1,797х10308 до 1,797х10308 с точностью до 15 знаков, занимает 8 байт;

  • Действительное (Decimal) для целых чисел от —1038 - 1 до 1038 — 1 (при работе с проектами, которые хранятся в файлах с расширением

  • adp) и от —1028 — 1 до 1028 — 1 (для файлов с расширением mdb) с точностью до 28 знаков, занимает 12 байт;

  • Код репликации (Replication ID). Глобальный уникальный идентификатор (Globally unique identifier, GUID), занимает 16 байт. Поля такого типа используются в Access для создания системных уникальных идентификаторов реплик, наборов реплик, таблиц, записей и других объектов при репликации баз данных.

Для поля с типом данных Счетчик можно задать:;

  • Длинное целое (Long Integer) — 4 байта;

  • Код репликации (Replication ID) — 16 байт.

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

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

Формат поля (Format) является форматом отображения заданного типа данных при выводе их на экран или при печати в режиме таблицы, формы или отчета.

В Access определены встроенные стандартные форматы отображения для полей с типами данных Числовой (Number), Дата/время (Date/Time), Логический (Yes/No) и Денежный (Currency). Ряд этих форматов совпадает с настройкой региональных форматов, определяемых в окне Язык и региональные стандарты панели управления Microsoft Windows. Пользователь может создать собственный формат для всех типов данных, кроме OLE, с помощью символов форматирования.

Для указания конкретного формата отображения необходимо выбрать в раскрывающемся списке одно из значений свойства Формат поля(Format).

Число десятичных знаков (DecimalPlaces) задает для числового и денежного типов данных число знаков после запятой. Можно задать значение от 0 до 15. По умолчанию (значение Авто (Auto)) это число определяется установкой в свойстве Формат поля (Format). Следует иметь в виду, что установка этого свойства не действует, если свойство Формат поля (Format) не установлено или выбрано значение Основной (General Namber). Свойство Число десятичных знаков (DecimalPlace влияет только на количество десятичных знаков, отображаемых на экране, и не влияет на число сохраняемых десятичных знаков. Для изменения числа сохраняемых знаков нужно изменить свойство Размер поля (FieldSize).

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

Значение по умолчанию (DefaultValue) определяет текст или выражение, которые автоматически вводятся в поле при создании новой записи. Например, если задана функция =Now, то в поле будут введены текущие дата и время. При добавлении записи в таблицу можно оставить значение, введенное по умолчанию, или ввести другое. Свойство Значение по умолчанию (DefaultValue) используется только при создании новой записи. Максимальная длина значения свойства составляет 255 знаков. Свойство не определено для полей с типом данных Счетчик (AutoNumber) или Поле объекта OLE (OLE Object).

Условие на значение (ValidationRule) позволяет осуществлять контроль ввода, задавая ограничения на вводимые значения, и при нарушении условий запрещает ввод и выдает текст, заданный свойством Сообщение об ошибке (ValidationText).

Сообщение об ошибке (ValidationText) задает текст сообщения, выводимый на экран при нарушении ограничений, заданных свойством Условие на значение (ValidationRule).

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

Тип элемента управления

Свойство Тип элемента управления (DisplayControl) задается в окне конструктора таблиц на вкладке Подстановка (Lookup) (рис. 3.5). Это свойство определяет, будет ли отображаться поле в таблице и в элементе управления формы в виде Поле (Text Box), Список (List Box) или Поле со списком (Combo Box).

Если для поля выбран тип элемента управления Список (List Box) или Поле со списком (Combo Box), на вкладке Подстановка (Lookup) появляются дополнительные свойства, которые определяют источник данных для строк списка и ряд других характеристик списка. В качестве источника данных для списка выбирается таблица, с которой осуществляется постоянная связь, что обеспечивает актуальное состояние списка.

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

Определение первичного ключа

Каждая таблица в реляционной базе данных должна иметь уникальный "(первичный) ключ, однозначно определяющий каждую запись в таблице. Это позволяет быстро найти нужную запись, а также связать данные из разных Таблиц в запросах, формах и отчетах. Ключевое поле должно содержать уникальные значения, такие как коды или инвентарные номера, и не может "Одержать значения Null. Если для таблицы определен первичный ключ, то Access предотвращает дублирование ключа или ввод значений Null в эти поля. Ключ может быть простым или составным, включающим несколько - полей (до 10). Для определения ключа выделяются поля, составляющие ключ, и на панели инструментов Конструктор таблиц (Table Design) нажимается кнопка Ключевое поле (Primary Key) или выполняется команда меню Правка|Ключевое поле (Edit|Primary Key).

Для ключевого поля автоматически строится уникальный индекс. В этом можно убедиться, просмотрев информацию об индексах таблицы. Окно Индексы: <...> (Indexes: <...>) (рис. 3.7) вызывается щелчком на кнопке просмотра и редактирования индексов Индексы (Indexes) на панели инструментов или выполнением команды меню Вид|Индексы (View|Indexes).

 

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

На рис. 3.7 показано, что индексу первичного ключа присвоено имя PrimaryKey, в столбце Имя поля (Field Name) перечисляются имена полей, составляющие индекс. Индекс первичного ключа всегда уникален и не допускает пустых полей в записях.

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

В качестве первичного ключа может быть задано поле с типом данных Счетчик (AutoNum6er). В этом случае при добавлении каждой новой записи в таблицу в это поле автоматически вводятся уникальные целые последовательно возрастающие (на 1) или случайные числа. Указание такого поля является наиболее простым способом создания первичного ключа. Значение этого поля нельзя изменить или удалить. Длина поля — 4 байта для длинного целого, для кода репликации — 16 байт. По умолчанию в поле вводятся последовательные значения. В таблице не может быть более одного поля этого типа. Если первичный ключ не установлен пользователем до сохранения вновь созданной таблицы, Access спросит о необходимости создания первичного ключа. При утвердительном ответе Access раздаст первичный ключ с типом данных Счетчик (AutoNumber).

Создание таблиц базы данных "Поставка товаров”

Напомним, что все объекты базы данных Access создаются и сохраняются в одном файле, который создается щелчком мыши в области задач на строке Новая база данных (New Database). В окне Файл новой базы данных (File New Database) выбирается папка, в которой будет размещен файл, и задается имя файла новой БД — "Поставка товаров. mdb" (рис. 3.2).

В результате открывается окно (рис. 3.3) новой базы данных Поставка товаров: база данных (формат Access 2002 - 2003).

В соответствии с технологией проектирования реляционной БД структура каждой таблицы базы данных "Поставка товаров" должна определяться составом реквизитов соответствующего информационного объекта в ИЛМ

Основные параметры структуры таблицы, соответствующей информационному объекту товар, представлены далее в табл. 3.1.

Создание структуры таблицы

Начнем создание таблицы товар с определения ее структуры в режиме конструктора таблиц. В окне базы данных (рис. 3.3) выберем объект Таблицы (Tables) и нажмем кнопку Создать (New). В окне Новая таблица (New Table) (рис. 3.4) выберем строку Конструктор (Design View). Еще проще перейти в режим конструирования таблицы, дважды щелкнув на строке Создание таблицы в режиме конструктора (Create table in Design View) в рабочем поле окна базы данных.

В соответствии с приведенными в табл., 3.1 проектными параметрами структуры, последовательно для каждого поля таблицы товар в окне конструктора Таблица 1: таблица (рис. 3.8) определим Имя поля (Field Name) и Тип данных (Data Type).

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

На вкладке Общие (General) зададим свойства полей:

Размер поля (Field Size):

  • для числового поля ставка_ндс выберем, нажав в нем кнопку списка, значение Одинарное с плавающей точкой (Single);
  • для текстовых полей зададим размеры, указанные в соответствующем столбце;

Формат поля (Format):

  • для поля цена выберем значение Денежный (Currency). Денежные суммы в соответствии с установленным по умолчанию в Microsoft Windows значением будут отображаться в таком виде: 123 456 789,00 р;
  • для поля ставка_ндс выберем значение Процентный. При отображении одинарных с плавающей точкой чисел в поле ставка_ндс, для которых выбран встроенный формат поля Процентный, их значения умножаются на 100 и к ним добавляется знак процента. Ввод ставки НДС должен осуществляться в формате отображения, например, 15%, 7,5%;

Число десятичных знаков (DecimalPlaces) для поля цена — 2, для ставка_ндс — 0;

Подпись поля (Caption), Условие на значение (ValidationRule), Сообщение об ошибке (ValidationText) — для каждого из этих полей выберем значения, указанные в табл. 3.1.

Таблица 3.1. Основные параметры структуры таблицы товар

Имя поля

Признак первичногоключа

Обязательное поле

Тип данных

Размер

Формат

Число десятичных знаков

Подписьполя

КОД_TOB

Простой

Да

Текстовый

5

 

 

Код товара

НАИМ_ТОВ

 

Нет

Текстовый

25

 

 

Наименование товара

ЦЕНА

 

Нет

Денежный

 

 

2

Цена

ЕИ

 

Нет

Текстовый

8

 

 

Единица измерения

СТАВКА_НДС

 

Нет

Числовой

Одинарный с плавающей точкой

 

 

Ставка НДС

ФОТО

 

Нет

Поле объекта OLE

 

 

 

Фото товара

Для поля ставка_ндс необходимо предусмотреть условие на значение: >=0,05 And <=0, 35 и сообщение об ошибке: "Ставка НДС должна быть >=5% и <=35%". Для поля цена следует предусмотреть условие на значение: >=0 And <=35000 и сообщение об ошибке: "Цена должна быть >=0 и <=35 000".

Условие на значение, которое задается в свойство поля, является выражением. Оно может быть сформировано с помощью построителя выражений (рис. 3.9). Построитель вызывается нажатием кнопки справа от строки Условие на значение (ValidationRule), в которую должно быть введено выражение в окне конструктора таблиц (рис. 3.8).

Рис. 3.7 Определение свойств поля ставка_ндс

Рис. 3.7 Окно построителя выражений с выеденным списком операторов

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

После ввода выражения в окно построителя и нажатия кнопки OK Access выполнит синтаксический анализ выражения и отобразит его в строке Условие на значение (ValidationRule).

Теперь определим первичный ключ таблицы. Выделим поле КОД_TOB, щелкнув кнопкой мыши на области маркировки слева от имени поля, и нажмем кнопку Ключевое поле (Primary Key). Признаком установки ключа является

Теперь определим первичный ключ таблицы. Выделим поле КОД_TOB, щелкнув кнопкой мыши на области маркировки слева от имени поля, и нажмем кнопку Ключевое поле (Primary Key). Признаком установки ключа является изображение ключа слева от имени поля. Определим свойства ключевого поля в соответствии с табл. 3.1.

Сохраним созданную структуру таблицы и присвоим имя новой таблице — товар. Для этого выполним команду Файл|Сохранить (File|Save) или нажмем кнопку панели инструментов конструктора Сохранить (Save). В окне Сохранение (Save As) заменим имя таблицы таблица 1 на товар.

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

Таблица товар: появится в списке объектов Таблицы (Tables) в окне Поставка товаров: база данных.

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

ЗАДАНИЕ 1. Создание таблиц базы данных

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

Рассмотрим некоторые особенности структуры таблиц товар, накладная, отгрузка, покупатель, договор, поставка__план базы данных "Поставка товаров".

Использование данных типа Поле объекта OLE

В таблице товар предусмотрено поле ФОТО, которое должно содержать фотографию товара, хранящуюся в формате графического редактора Paint в файле с расширением bmp. Тип данных такого поля должен быть определен как Поле объекта OLE (OLE Object). Размещение этого объекта в поле производится на этапе заполнения полей таблицы в режиме таблицы или через форму. В режиме таблицы Access отобразит название объекта точечный рисунок. Отображение объекта возможно только в форме или отчете. Объект может быть внедренным или связанным.

OLE (Object Linking and Embedding — связывание и внедрение объектов) — это метод передачи информации в виде объектов между приложениями Windows, Поле объекта OLE является средством, позволяющим установить связь с объектами другого приложения или внедрить объект в базу данных. Объектом является документ или его часть, созданная в другом приложении, сохраняющая формат документа источника и информацию о создавшем его приложении. Объектами могут быть простые и форматированные тексты, рисунки, диаграммы, файлы звукозаписи (WAV), музыка в формате MIDI (музыкально-инструментальный цифровой интерфейс), файлы анимации (FLI, МММ), видеоклипы (AVI), электронные таблицы и другие элементы различных приложений, поддерживающих это средство. Access, поддерживая OLE, полностью интегриро­ван с другими приложениями Microsoft.

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

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

Использование данных типа Поле MEMO

В таблице покупатель предусмотрено поле описание, которое будет содержать текстовые данные большой длины — характеристику покупателя. Для такого поля выберите тип данных Поле MEMO (Memo). Ввод данных в это поле можно выполнить непосредственно в таблице либо через область ввода, вызываемую нажатием клавиш <Shift>+<F2>.

Если тексты описаний покупателей подготовлены в некотором текстовом редакторе, например,.в Microsoft Word, и хранятся в отдельных файлах, для этого поля удобно задать тип Поле объекта OLE (OLE Object). При вводе объекта в поле можно внедрить его в базу данных или установить связь с файлом объекта.

Использование данных типа Гиперссылка

Специальный тип данных Гиперссылка (Hyperlink) позволяет хранить гиперссылки в полях таблиц БД.

Поля с типом данных Гиперссылка (Hyperlink) используются для перехода к объектам той же самой или другой базы данных Access к документам, созданным в различных приложениях Microsoft Office и расположенным на локальных или сетевых дисках, к страницам Web-серверов и документам других ресурсов в Интернете или локальной сети. При щелчке мышью на гиперссылке осуществляется переход к документу, который открывается создавшим его приложением. Подробно создание гиперссылок в таблицах будет рассмотрено далее (включая создание гиперссылки в поле иев_адрес таблицы покупатель).

Определение составного первичного ключа

В таблице накладная в составной первичный (уникальный) ключ входят поля ном_накл и код_ск. Для определения этого ключа в режиме конструктора таблиц надо вьщелить оба эти поля, щелкая кнопкой мыши на области маркировки при нажатой клавише <Ctrl>, а затем нажать кнопку панели инструментов Ключевое поле (Primary Key).

Аналогично определяются составные ключи в таблицах отгрузкаиПОСТАВКА_ПЛАН.

Использование данных типа Дата/время

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

Маска ввода для даты и текста

Для удобства ввода даты в таблице договор целесообразно в свойствах поля дата__дог определить маску ввода. Маска ввода задает формат, состоящий из постоянных символов (таких как скобки, точки или дефисы) и специальных символов маски, указывающих, в какие позиции, в каком количестве и какого типа данные могут быть введены. Например, маска для > ввода даты в кратком формате имеет вид 00.00.0000, в среднем формате — QO->L<LL-0000. Специальный символ "О" указывает, что в данную позицию на его место должна быть введена цифра от 0 до 9 и ввод ее обязателен. Специальный символ "9" указывает на необязательный ввод цифры или пробела. Специальный символ "L" указывает на обязательный, ввод буквы, от А др Я или от А до Z. Специальные символы ">" и "<" указывают на перевод всех следующих знаков в верхний и нижний регистр соответственно. Воспользуйтесь значком построителя в конце строки Маска ввода для вызова мастера, который позволит без труда сформировать маску ввода.

Для ввода номера телефона в поле тел таблицы покупатель определите маску (999)000-0099, также воспользовавшись мастером. Эта маска позволит вводить номера телефонов с кодами городов и без них. Число цифр в номере телефона не может быть менее 5 и более 7. Знак 9 означает, что вместо цифр кода можно ввести пробелы, а последние две цифры номера вводить необязательно.

Создание файла базы данных Access

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

Непосредственный ввод данных в таблицы

Схема данных в Access

Отображение записей подчиненных таблиц в главной таблице

Модификация структуры базы данных

Непосредственный ввод данных в таблицы

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

Непосредственный ввод данных в таблицу осуществляется в режиме таблицы (Datasheet View). Переход в этот режим из окна базы данных выполняется нажатием кнопки Открыть (Open) при выделенной таблице в окне базы данных (рис. 3.3). Переход в режим таблицы из режима конструктора выполняется нажатием кнопки Вид (View) на панели инструментов (рис. 3.6) или выполнением команды Вид|Режим таблицы (View|Datasheet).

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

Просмотреть и изменить права доступа к данным таблицы можно по команде Сервис|Защита|Разрешения (Tools|Security|User And Group Permissions).

При дополнении таблицы новыми записями может быть использован режим ввода записи, при котором видна только вводимая запись. Выполнение команды Записи|Ввод данных (Records|Data Entry) позволяет перейти в этот режим;. Для возврата к просмотру всей таблицы надо выполнить команду Записи | Удалить фильтр (Records|Remove Filter/Sort).

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

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

Макет таблицы

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

Настройка макета выполняется в режиме таблицы. При этом могут быть использованы команды меню Формат (Format) или панель инструментов Формат (режим таблицы) (Formatting (Datasheet)), которую можно вызвать из контекстного меню на доступной панели инструментов.

Многие операции настройки макета можно выполнить непосредственно в таблице с помощью мыши:

  • изменение ширины столбца. Для изменения ширины столбца курсор мыши устанавливается на линию, разделяющую имена столбцов. При этом он превращается в планочку со стрелками влево и вправо. Далее границу столбца можно перетащить в нужное место;
  • изменение высоты строки. Для изменения высоты строки курсор мыши устанавливается в области маркировки записи, расположенной слева, на границе между записями. Граница строки перетаскивается на требуемое расстояние. При этом изменяется высота всех строк таблицы;
  • удаление столбцов с экрана. Убрать столбец можно, перетащив его правую границу влево до исчезновения столбца;
  • изменение порядка расположения столбцов на экране. Столбец выделяется щелчком кнопки мыши на его имени. Протащив курсор мыши поперек столбцов, можно выделить несколько столбцов. Выделенный столбец перетаскивается в новое место при установке курсора на его имени (области маркировки столбца);
  • скрытие столбцов, ненужных для текущей работы, закрепление столбцов, а также отмена этих действий могут быть выполнены при помощи соответствующих команд меню Формат (Format);
  • сохранение макета таблицы выполняется командой Файл|Сохранить (File|Save Layout) или при закрытии таблицы после утвердительного ответа на вопрос "Сохранить изменения макета таблицы “<имя таблицы>”? (Do you want to save changes to the layout of table <имя таблицы>?).

Ввод данных в таблицы БД "Поставка товаров"

В режиме таблицы введем несколько записей в таблицу товар. В окне Поставка товаров: база данных установим курсор на таблице товар и нажмем кнопку Открыть (Open). Таблица откроется в режиме таблицы (Datasheet View). Заполним строки (записи) открывшейся таблицы (рис. 3.13) в соответствии с названиями столбцов (полей).

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

Отменить ввод значения в поле до перехода к другому полю можно, нажав клавишу <Esc> или выполнив команду Правка|Отменить ввод. Переход от одного поля к другому можно выполнить клавишей <Jab> или переводом курсора. Отмена ввода значения в поле после перехода к другому полю текущей записи (до перехода к другой записи) происходит с помощью клавиши <Esc> или команды Правка|Восстановить текущее поле/запись (Edit| Undo Current Field/Record).

Рис. 3.13 Ввод записей в таблицу товар

Завершение ввода новых значений записи или редактирования осуществляется при переходе к любой другой записи (при смене текущей записи). После перехода к другой записи можно отменить ввод (редактирование) всей записи, выполнив команду Правка|Восстановить запись (Edit|Undo Saved Record).

Команда Восстановить (Undo) занимает одну позицию в меню, но в зависимости от контекста приобретает различный смысл и наименование. Это же касается и соответствующей кнопки панели инструментов, всплывающая подсказка которой сообщает о текущем её назначении. По этой команде может быть отменено только одно последнее действие.

Добавление записи в таблицу начинается с заполнения пустой строки, размещенной в конце таблицы и помеченной звездочкой (*).

Переход к этой записи можно выполнить также по команде Правка|Перейти|Новая запись (Edit|Go To|New Record) или нажатием кнопки Новая запись (New Record) панели инструментов Таблица в режиме таблицы(Table Datasheet). Корректируемая запись помечается слева символом карандаша.

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

Сохранение новой записи происходит после перехода к другой записи или выполнения команды Записи|Сохранить запись (Records Save Record).

Для удаления записи в таблице ее нужно сделать текущей и нажать кнопку панели инструментов Удалить запись (Delete Record) или выполнить соответствующую команду Правка|Удалить запись (Edit|Delete Record). Для исключения ошибочного удаления в Access предусмотрен запрос на подтверждение удаления. После подтверждения на удаление восстановление удаления.

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

Размещение объекта OLE

Рассмотрим размещение объекта OLE на примере поля ФОТО в таблице товар. Пусть фотографии хранятся в формате графического редактора Paint в файлах с расширением bmp.

Рассмотрим вариант внедрения объекта в файл базы данных. Установим курсор в соответствующем поле таблицы (рис. 3.13). Выполним команду меню Вставка | Объект (Inseit | Object). В открывающемся окне (рис. 3.14) надо отметить переключатель Создать из файла (Create from File).

Рис. 3.14 Окно для выбора варианта вставки объекта

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

Рис. 3.15 Окно для выбора вставляемого объекта из файла

ФлажокСвязь (Link) по умолчанию не отмечен и, следовательно, содержимое файла будет введено в поле как внедренный объект. Увидеть содержимое поля можно через форму или отчет. Дальнейшие изменения файла не будут отображаться на встроенном объекте. Для ввода в поле связанного объекта достаточно установить флажок Связь (Link). Это сэкономит место в базе данных и даст возможность отображать все изменения, вносимые в файл другими приложениями.

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

Для отображения содержимого поля в виде значка, представляющего файл с документом, в окне Вставка объекта (Insert Object) нужно установить флажок В виде значка (Display As Icon). Значок может быть использован для представления связанного объекта.

Ввод логически связанных записей

Введем несколько логически взаимосвязанных записей в таблицы договор и поставка_план. Логическая связь этих таблиц обеспечивается полем ном_дог — номером договора, входящим в структуру обеих таблиц.

Объекты договор и поставка_план связаны одно-многозначными отношениями. Связь таких таблиц реализуется в схеме данных с помощью ключа главной таблицы договор. В подчиненной таблице это поле связи является в данном случае частью ее составного ключа. Пока не создана схема; данных, в которой устанавливаются связи между таблицами, система не может контролировать логическую взаимосвязь вводимых данных Поэтому для получение целостной базы, в которой каждая запись подчиненной таблицы имеет логически связанную с ней главную запись, пользователю придется самому отслеживать взаимосвязи записей. При вводе подчиненной записи в таблицу поставка_план необходимо проверять наличие записи в главной таблице договор с ключом, значение которого совпадает со значением поля связи (внешнего ключа) в подчиненной записи. То есть при добавлении строки плана поставки для договора Д111 необходимо, чтобы договор с этим номером уже был представлен в таблице договор.

Для удобного отображения открытых таблиц можно -воспользоваться коман­дой меню Сверху вниз (Tile Horizontally) или Слева направо (Tile Vertically) в меню Окно (Windows).

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

Использование поля со списком при вводе записей

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

Создадим в таблице поставка_план для поля ном_дог (номер договора) поле со списком, построенным на основе ключа таблицы договор. Для этого откроем таблицу поставка_план в режиме конструктора (рис. 3.5). Установим курсор в поле ном_дог на столбце Тип данных (Data Type), откроем список и выберем строку Мастер подстановок (Lookup Wizard). В открывшемся окне мастера Создание подстановки (Create Lookup) установим флажок на строке, которая приведет к построению списка на основе значений из таблицы (рис. 3.17).

Рис. 3.17. Первое окно Мастера подстановок

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

Далее выберем столбец, из данных которого будет формироваться список

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

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

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

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

Рассмотрим еще один пример использования поля со списком. Преобразуем в таблице поставка_план поле код_тов (код товара) в поле со списком. Для этого откроем таблицу в режиме конструктора, установим курсор в поле код_тов на столбце Тип данных (Data Type), откроем список этого столбца и выберем строку Мастер подстановок (Lookup Wizard). В диалоге с мастером выберем главную по отношению к таблице поставка_план таблицу товар, на основе которой создается список, и поля, включаемые в список: код_тов (код товара) и наймитов (наименование товара). Кроме того, оставим, как предлагает мастер, помеченным флажок Скрыть ключевой столбец (рекомендуется).

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

Определение поля со списком с такими параметрами приводит к отображению в поле код_тов вместо значений ключевого поля значений второго поля списка — поля hammjtob (наименование товара), взятого из главной габлицы товар.

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

При создании полей со списком Мастер подстановки автоматически создает связь между таблицей, в которой создается поле со списком, и таблицей, являющейся источником значений списка. Эту связь можно увидеть в Схеме данных (Relationships), щелкнув на кнопке панели инструментов Отобразить все связи (Show All).

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

Далее будут рассмотрены средства Access, позволяющие автоматизировать контроль связной целостности и сделать загрузку базы данных простой и удобной процедурой. К таким средствам относятся, прежде всего, Схема данных (Relationships) и Формы (Forms). Кроме того, может быть использовано такое простое средство, как отображение в главной таблице записей подчиненных таблиц нескольких уровней — подтаблиц.

ЗАДАНИЕ 2. Создание полей со списком

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

Создание файла базы данных Access

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

Непосредственный ввод данных в таблицы

Схема данных в Access

Отображение записей подчиненных таблиц в главной таблице

Модификация структуры базы данных

Схема данных в Access

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

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

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

Создание схемы базы данных "Поставка товаров"

Рассмотрим процесс создания схемы данных, соответствующей логической структуре базы данных "Поставка товаров11, разработка которой рассмотрена в главе 2. Создание схемы данных начинается при активном окне Поставка товаров: База данных (Database) с выполнения команды Сервис|Схема данных (Tools|Relationships) или нажатия кнопки Схема данных (Relationships)на панели инструментов базы данных, после которого открываются окно схемы данных и диалоговое окно для выбора таблиц, включаемых в схему.

Включение таблиц в схему данных

В окне Добавление таблицы (Show Table) (рис. 3.26) выберем закладку Таблицы (Tables) и, нажимая кнопку Добавить (Add), разместим в окне Схема данных (Relationships) все ранее созданные таблицы базы данных "Поставка товаров", которые отображены в окне Добавление таблицы (Show Table). Затем нажмем кнопку Закрыть (Close). В результате в окне Схема данных (Relationships) таблицы базы будут представлены окнами со списками своих полей и выделенными жирным шрифтом ключами (рис. 3.30).

Рис 3.26 Добавление таблиц в схему данных

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

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

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

Создание связей по простому ключу. Установим связь между таблицами покупатель и договор, которые находятся в отношении один-ко-многим. Устанавливая - связи между парой таблиц, находящихся в отношении типа 1:М, выделим в главной таблице покупатель ключевое поле код_пок, по которому устанавливается связь. Далее, удерживая нажатой кнопку мыши перетащим его в соответствующее поле подчиненной таблицы договор.

Поскольку поле связи является уникальным ключом в главной таблице связи, а в подчиненной таблице связи не является ключевым, Access выявляет отношение один-ко-многим между записями этих таблиц. Значение один-ко-многим (One-To-Many) отобразится в окне Изменение связей (Edit Relationships) в строке Тип отношения (Relationship Type) (рис. 3.27).

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

ЗАДАНИЕ 3. Создание связей по простому ключу

Добавьте в схему данных и установите связь по простому ключу для Других пар таблиц базы данных "Поставка товаров": СКЛАД НАКЛАДНАЯ (ключ КОД_СК), ДОГОВОР ПОСТАВКА_ЛЛАН (КЛЮЧ НОМ_ДОГ), ТОВАР ОТГРУЗКА (КЛЮЧ КОД_ТОВ), ТОВАР ПОСТАВКА_ПЛАй (КЛЮЧ КОД_ТОВ), ДОГОВОРНАКЛАДНАЯ (КЛЮЧ НОМ_ДОГ).

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

Определение связей по составному ключу. Определим связи между таблицами накладная отгрузка, которые связаны по составному ключу НОМ_НАКЛ + КОД_СК. Для этого в главной таблице накладная выделим оба этих поля, удерживая клавишу <Ctrl>, и перетащим их в подчиненную таблицу ОТГРУЗКА.

В окне Изменение связей (Edit Relationships) (рис. 3.28) для каждого поля составного ключа главной таблицы накладная, названной Таблица/запрос (Table/Query), выберем соответствующее поле подчиненной таблицы отгрузка, названной Связанная таблица/запрос (Related Table/Query).

Рис. 3.28 Окно выбора параметров связи

Обеспечение целостности данных

Если связываемые в схеме данных таблицы находятся в отношении 1:1 и 1:.М, для связи можно задать параметр обеспечения связной целостности данных.

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

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

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

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

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

Для каждой из ранее установленных связей базы данных "Поставка товаров" установим в окне Изменение связей (Edit Relationships) флажок Обеспечение целостности данных (Enforce Referential Integrity). Установление связи с параметром обеспечения целостности данных возможно только в том случае, если таблицы ранее были заполнены корректными данными. В противном случае появится сообщение о невозможности установить связь.

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

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

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

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

Установить в окне Изменение связей (Edit Relationships) (рис. 3.27) флажки Каскадное обновление связанных полей (Cascade Update Related Fields) и Каскадное удаление связанных записей (Cascade Delete Related Records) можно только после задания параметра обеспечения целостности данных.

После создания связей изображения таблиц могут перемещаться в пределах рабочего пространства окна схемы данных. Перемещения и изменения размеров окон со списками полей таблиц в окне схемы данных осуществляются принятыми в Windows способами. На рис. 3.30 показана схема базы данных "Поставка товаров", где таблицы размещены в соответствии с размещением информационных объектов канонической модели данных, полученной при проектировании базы данных в главе 2 (рис. 2.18).

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

Рис 3.30 Схема данных базы данных «Поставка товаров»

ЗАДАНИЕ 4. Проверка поддержания целостности в базе данных

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

Проверка автоматического поддержания целостности при изменении значений Ключей связи в таблицах. Откроем главную таблицу связи покупатель в режиме таблицы. Изменим значение ключевого поля код_пок (код покупателя) в одной из записей. Убедимся, что во всех записях подчиненной таблицы договор для договоров, заключенных этим покупателем, автоматически также изменится значение поля код_пок. Изменение происходит, т.к. был установлен параметр Каскадное Обновление связанных полей (Cascade Update Related Fields) (рис. 3.27). Причем это изменение осуществляется, как только изменяемая запись перестает быть текущей. Для наблюдений за автоматическими изменениями в подчиненной таблице откроем на экране одновременно таблицы покупатель и договор.

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

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

Проверка при удалении записи в главной таблице. Убедимся, что вместе q удалением записи в главной таблице покупатель удаляются все подчиненные записи в таблице договор, т.к. был установлен параметр Каскадное удаление связанных записей (Cascade Delete Related Records).

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

Объединение записей взаимосвязанных таблиц

При создании форм, запросов, отчетов, страниц доступа к данным на основе взаимосвязанных таблиц производится объединение их записей: объединяется каждая запись из одной таблицы с каждой записью из другой таблицы при условии равенства значений в поле связи. Рассмотрим пример объединения записей таблиц товар и поставка_планпо полю код_тов. Таблица поставка_плансодержит план отгрузки товаров, заданный в спецификациях договоров. Каждая запись этой таблицы представляет один из товач ров, который должен быть отгружен в указанные сроки покупателю. Рассматриваемые таблицы находятся в отношении один-ко-многим. Поле код_тов является уникальным ключом в таблице товар. В таблице поставка_планзначения этого поля повторяются во многих записях. Пример заполнения таблиц товари поставка__план, для которых не установлен параметр обеспечения связной целостности, показан на рис. 3.31.

Рис 3.31 Содержимое объединяемых таблиц товари поставка__план,

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

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

Как видно из таблицы результатов (рис. 3.32), ее записи образований слиянием только тех записей таблиц, которые имеют совпадающие значения поля связи код_тов = Т001, Т002, ..., Т005. Здесь объединение произведено первым способом, устанавливаемым для любой связи таблиц по умолчанию.

Если для некоторой записи, например, записи с кодом товара Т008 в таблице товар отсутствуют связанные записи в другой таблице — объединять запись не с чем — и в результат такая запись не включается. Именно по этой причине в результате объединения таблиц товари поставка_планотсутствуют записи о товарах, не представленных в плане поставки, а также записано плановых поставках, в которых указан товар, отсутствующий в таблице ТОВАР.

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

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

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

3. Объединение тех записей, в которых связанные поля обеих таблиц совпадают, а также объединение всех записей из второй таблицы, для которых нет связанных в первой; с пустой записью первой таблицы.

Любой из перечисленных способов объединения может быть выбран для связи вне зависимости от того, находятся связываемые таблицы в отношениях 1:1, 1:М или тип отношения не может быть определен системой.

Выбор способа объединения записей

В окне Изменение связей (Edit Relationships) (рис. 3.28) есть кнопка Объединение. При нажатии этой кнопки появится окно Параметры объединения, и можно будет убедиться, что для связываемых таблиц по умолчанию выбран первый способ объединения (рис. 3.33).

Рис 3.33 Окно выбора способа объединения связываемых таблиц

Результаты объединения таблиц получены по запросу выборки (описание запросов см. в главе 7), в котором определены включаемые в результат запроса поля из объединяемых таблиц. Заметим, что для каждого способа объедине­ния таблиц должен формироваться новый запрос.

При объединении таблиц вторым способом к записям, полученным первым способом, добавились записи из таблицы товар, не имеющие связанных записей в таблице поставка__план. Каждая такая запись дополнена полями записи поставка_план, которые содержат значения Null. Такая ситуация возникла вследствие того, что в плане отсутствуют заказы на тойары Т006, Т007, ..., Т010.

Null— это неизменяющаяся константа, которая означает, что поле не содержит символов или значений. Поле получает значение Null, когда его содержимое неизвестно. Такое поле не следует путать с полем, содержащим пустую строку (""), хотя выглядят они одинаково. Значение Null можно ввести в поле или использовать в выражениях и запросах для указания отсутствующих или неизвестных данных: Пустая строка служит для указания того, что строковое значение для этого поля отсутствует. Для ввода пустой строки с клавиатуры следует ввести два символа прямых кавычек без пробела (""). Пользователь имеет возможность различать значения Null и пустые строки с помощью выражения.

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

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

Напомним, что если для связи товар > поставка_план установлен параметр обеспечения целостности, ввод в таблицу поставка_план записей с указанием товаров, отсутствующих в таблице товар, невозможен.

Создание файла базы данных Access

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

Непосредственный ввод данных в таблицы

Схема данных в Access

Отображение записей подчиненных таблиц в главной таблице

Модификация структуры базы данных

Отображение записей подчиненных таблиц в главной таблице

В Access имеется возможность при просмотре главной таблицы отображать записи подчиненных таблиц, называемых в этом случае подтаблицами. Это позволяет пользователю при добавлении, удалении и редактировании записей осуществлять контроль за корректностью связей в отображаемой цепочке таблиц. Например, если отобразить в таблице договор подчиненную таблицу поставка_план, связь между которыми осуществляется по полю ном_дог (номер договора), то при заполнении таблицы поставка__план невозможно допустить ошибку в поле ном_дог, т.к. оно даже не отображается в подчиненной таблице. Однако это никоим образом не помогает! при заполнении других полей связи таблицы поставка_план, таких как код_тов (код товара).

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

Открыть или закрыть все подчиненные записи можно, воспользовавшись командой меню Формат|Подтаблица|Развернуть все (Formar|Subdatasheet| Expand All) или Свернуть, все (Collapse All).

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

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

Для определения подтаблицы щелкните на знаке "+" в строке записи или выполните команду Подтаблица (Subdatasheet). Отобразится окно Вставка подтаблицы (Insert Subdatasheet) (рис. 3.38), в котором следует выбрать подчиненную таблицу или запрос и указать поля связи.

Рис. 3.38 Окно для выбора подчиненной таблицы и поля связи с ней

Если в главной и подчиненной таблицах поле связи имеет разные имена, то в окне Вставка подтаблицы (Insert Subdatasheet) можно ввести имена полей связи, при составном ключе имена полей разделяются точкой с запятой. Если в схеме данных между таблицами товар и поставка_план не было установлено связи, система может автоматически добавить эту связь в схему базы данных после ответа пользователя в окне диалога

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

Для просмотра свойств главной таблицы, например, товар, откройте ее в режиме конструктора и нажмите кнопку Свойства на панели инструментов. В строке свойства Имя подтаблицы (Subdatasheet Name) указано имя подчиненной таблица, в свойствах Подчиненные поля (Link Child Fields) и Основные поля (Link Master Fields) указано имя ключа связи (простого или составного) (рис. 3.40).

Рис. 3.40 Свойства таблицы поставка_план

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

По умолчанию в строке Имя подтаблицы (Subdatasheet Name) установлено значение Авто (Auto), которое означает, что если в схеме данных установлена связь таблиц, Access автоматически выполнит вывод столбца со знаками "+" для открытия подчиненных записей по этой связи.

Если у таблицы имеются две или более подчиненных таблиц, определенных в схеме, то при щелчке на знаке "+" автоматически откроется окно для выбора подчиненной таблицы. После выбора подчиненной таблицы и сохранения главной таблицы эта связь фиксируется в свойствах. Для того чтобы в таблице не выводился столбец, позволяющий открывать подчиненные записи, в качестве значения свойства должно быть установлено Нет (None), Значение Нет (None) можно установить в режиме таблицы с помощью команды меню Формат|Подтаблица|Удалить (Formar|Subdatasheet|Remove) или соответствующей кнопки панели инструментов.

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

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

ЗАДАНИЕ 5. Отображение подтаблиц

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

Создание файла базы данных Access

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

Непосредственный ввод данных в таблицы

Схема данных в Access

Отображение записей подчиненных таблиц в главной таблице

Модификация структуры базы данных

Модификация структуры базы данных

К модификации структуры базы данных относится изменение структуры отдельных таблиц, добавление и удаление таблиц, а также изменение схемы данных.

Изменение структуры таблиц

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

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

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

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

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

Изменение схемы данных

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

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

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

Для внесения изменений в схему данных перед ее открытием нужно закрыть все таблицы. Команда Сервис|Схема данных (Tools|Relationships) или кнопка Схема данных (Relationships) на панели инструментов откроют схему данных. При этом открывается панель инструментов Связь (Relationship).

Добавление таблиц выполняется при нажатии кнопки Добавить таблицу (Show Table) И на панели инструментов Связь (Relationship). В диалоговом окне Добавление таблицы (Show Table) (рис. 3.26) надо выделить нужную таблицу и нажать кнопку Добавить (Add).

Удаление таблицы из схемы данных возможно только после удаления всех связей таблицы. Сделайте таблицу текущей и выполните команду Правка|Удалить (Edit|Delete) или нажмите клавишу <Del>.

Удаление связи осуществляется, если к связи подвести курсор мыши и отметить связь щелчком мыши, затем нажать правую кнопку мыши, вызывающую контекстное меню, и выполнить команду Удалить связь (Delete Relationship). Помеченную связь можно также удалить по команде Правка|Удалить (Edit|Delete) или нажатием клавиши <Del>.

Изменение параметров связи выполняется при выделенной связи в диалоговом окне, которое вызывается командой Связи|Изменить связь (Relationships) Edit Relationship) или соответствующей командой контекстного меню.