Рассмотрим следующий учебный пример построения модели данных: построить реляционную модель данных для диспетчера отдела продаж автомобильных шин

Шаг 1. Прежде все определим необходимые сущности для очерченной в условии примера предметной области:

- категория автошин -- эта сущность необходима для облегчения поиска шин в системе и анализа состава заказов.

Экземплярами этой сущности будут: зимние шины, летние шины, всесезонные шины и т. п.;

- шины --эта сущность не требует пояснений;

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

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

- заказы -- эта сущность содержит информацию о заказе: заказчик, дата принятия заказа, дата исполнения заказа, сведения о сотруднике, принявшем заказ.

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

Шаг 2. Определяем атрибуты каждой сущности и их характеристики.

Таблица 3.1

Атрибуты сущности «Категория автошин и их характеристики»

Таблица 3.2

Атрибуты сущности «Шины и их характеристики»

Таблица 3.3

Атрибуты сущности «Покупатели и их характеристики»

Таблица 3.4

Атрибуты сущности «Сотрудники и их характеристики»

Таблица 3.5

Атрибуты сущности «Заказы и их характеристики»

Шаг 3. На данном этапе построения модели необходимо определить логические связи между введенными в модель сущностями.

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

Таблица 3.6

Дополнительные атрибуты сущности «Шины и их характеристики»

Далее переходим к рассмотрению связи между сущностями «Заказы» и «Покупатели». Поскольку каждый заказчик может сделать множество заказов (в разные дни и разное время), а каждый заказ принадлежит только одному заказчику, то между этими сущностями имеется связь 1:?, причем сущность «Покупатели» находится на стороне «1» связи, а сущность «Заказы» -- на стороне «?».

Для реализации этой связи к сущности «Заказы» добавляем в качестве внешнего ключа атрибут «Код покупателя», являющийся первичным ключом сущности «Покупатели».

Таблица 3.7

Дополнительные атрибуты сущности «Покупатели и их характеристики»

Аналогичные рассуждения определяют связь между сущностями «Заказы» и «Сотрудники» (эта связь также будет иметь вид 1:?, причем сущность «Сотрудники» находится на стороне «1» связи, а сущность «Заказы» -- на стороне «?»).

Дополнительные атрибуты сущности «Заказы и их характеристики» представлены в таблице 3.8.

Таблица 3.8

Дополнительные атрибуты сущности «Заказы и их характеристики»

Теперь о связи между сущностями «Заказы» и «Шины».

Один заказ может включать в себя множество шин, в то же время одна и та же шина может входить в разные заказы. Следовательно, между сущностями «Заказы» и «Шины» существует связь ?:?.

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

В рассматриваемом примере такая ассоциативная сущность должна быть связана отношением 1:? с сущностью «Шины» (ассоциативная сущность на стороне «?», а сущность «Шины» -- на стороне «1») и отношением 1:? с сущностью «Заказы» (ассоциативная сущность на стороне «?», а сущность «Заказы» -- на стороне «1»). Каждый экземпляр ассоциативной сущности характеризует, какие шины входят в какой заказ и в каком количестве. Назовем вводимую в модель ассоциативную сущность «Состав заказа».

Для реализации двух связей 1:? перенесем в сущность «Состав заказа» атрибут «Код заказа», являющийся первичным ключом сущности «Заказы», и атрибут «Код шины», являющийся первичным ключом сущности «Шины». Оба эти атрибута будут в ассоциативной сущности внешними ключами.

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

Таблица 3.9

Атрибуты сущности «Состав заказов» и их характеристики

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

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

Рис. 3.1 Реляционная модель данных рассматриваемого примера

4. Физическая реализация базы данных

Запуск Microsoft Access, выполняется командой меню Пуск > Программы > Microsoft Office > Microsoft Office Access 2007.

В открывшемся окне (Рис. 4.1) для создания базы данных имеется значок - «Новая База Данных». Щелчок по этому значку приводит к открытию в правой части окна поля, в которое необходимо вписать название имя создаваемой базы и указать папку её хранения. Нажатие на кнопку «Создать», приводит к формированию новой пустой базы.

Рис. 4.1 Окно Microsoft Access

4.1 Создание базы данных с помощью шаблонов

Создание пустой базы подразумевает, что пользователь сам разработает и создаст ее структуру и основные элементы. Однако, для ускорения процесса создания базы данных в Access введены шаблоны наиболее часто используемых типовых баз. К ним относятся локальные шаблоны, поставляемые совместно с программой Access (рисунок 4.2), и пополняемый ряд шаблонов из Microsoft Office Online, которые могут быть загружены из Интернета.

Рис. 4.2 Локальные шаблоны

4.2 Таблицы

После создания новой базы, открывается окно «База данных» (рисунок 4.3)

Рис. 4.3 Окно базы данных

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

– с помощью шаблонов;

– с помощью конструктора таблиц.

4.3 Создание таблицы при помощи шаблонов таблиц

Для использования шаблонов таблиц требуется перейти на закладку «Создание» (рисунок 4.4) и выбрать команду «Шаблоны таблиц».

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

Рис. 4.4 Создание таблицы с помощью шаблонов таблиц

Рис.4.5 Перечень типов шаблонов

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

Рис. 4.6 Окно таблицы

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

Выполнить это можно следующим образом:

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

- в поле «Код» автоматически устанавливается значение;

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

Заполненная таблица приведена на рисунке 4.7.

Рис. 4.7 Таблица данных

Для сохранения таблицы в базе данных требуется щелкнуть правой клавишей мыши и в открывшемся контекстном меню выбрать пункт «Сохранить». В открывшееся окно (рисунок 4.8) вводится имя созданной таблицы и дается команда OK.

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

4.4 Создание таблицы с помощью конструктора таблиц

Для использования конструктора таблиц требуется активировать закладку «Таблица 1» и выбрать в качестве режима работы с ней - «Конструктор» (см. рисунок 4.9).

Рис 4.9 Выбор режима работы с таблицей

Программа сразу предлагает сохранить таблицу и ввести её имя.

Начинаем создавать первую таблицу спроектированной ранее базы - таблицу «Категория». В открывшемся окне конструктора таблиц можно вводить имена полей новой таблицы или добавлять поля в уже созданную таблицу (рисунок 4.10).

Рис. 4.10 Окно конструктора таблиц

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

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

Рис. 4.11 Тип создаваемого поля

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

- текстовый.

Представляет собой текст или комбинацию текста и чисел. Сохраняет до 255 знаков. Свойство «Размер поля» определяет максимальное количество знаков, которые можно ввести;

- поле МЕМО.

Длинный текст или числа, например, примечания или описания, сохраняет до 65 536 знаков;

- числовой.

4.5 Данные, используемые для математических вычислений

Конкретный тип числового поля определяется значением свойства «Размер поля».

Допустимыми являются следующие значения этого свойства (таблица 4.1):

Табл. 4.1

Допустимые свойства «Размер поля»

- «Дата/время». Значения дат и времени. Сохраняет 8 байтов;

- «Денежный». Используется для денежных значений и для предотвращения округления во время вычислений. Сохраняет 8 байтов;

- «Счетчик». Автоматическая вставка уникальных последовательных или случайных чисел при добавлении записи. Сохраняет 4 байта;

- «Логический». Данные, принимающие только одно из двух возможных значений, таких как «Да/Нет», «Истина/Ложь». Пустые значения не допускаются. Сохраняет 1 бит;

- «Поле объекта OLE». OLE - это технология связи программ, разработанная фирмой Microsoft и позволяющая приложениям совместно использовать данные. Сохраняет до 1 Гигабайта;

- «Гиперссылка». Это выделенный объект, по щелчку на который выполняется переход к файлу, фрагменту файла или странице HTML в Интернете.

После выбора типа данных поля в нижней части окна открывается таблица свойств поля (рисунок 4.12).

Рис. 4.12. Свойства числового поля

При создании поля рекомендуется обязательно заполнять следующие свойства: «Размер поля», «Подпись» -- содержит название столбца таблицы в режиме просмотра, «Обязательное поле», «Индексированное поле» -- обязательно индексируются первичные и внешние ключи таблицы.

Свойство «Формат поля» обязательно следует задавать из выпадающего списка для типа данных «Дата/Время», а для текстовых полей это свойство не заполняется. Для числовых полей свойство «Формат поля» можно не заполнять, оставив значение «Авто» в свойстве «Число десятичных знаков».

Значение свойства «Значение по умолчанию» для большинства полей лучше очистить, а для типа данных «Дата/Время» в это свойство обычно вводят функции автоматического заполнения текущей даты ( Date() или Time() ).

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

Свойство «Сообщение об ошибке» содержит текст, который появляется в окне сообщения, когда пользователь вводит в поле значение, нарушающее свойство «Условие на значение».

Значение свойства «Маска ввода» вводится обычно только для полей, имеющих тип данных «Дата/Время» и текстовых полей.

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

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

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

Таблица 4.2

Знаки, используемые для формирования масок ввода

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

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

Так последовательно разработчик вводит все поля таблицы.

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

Рис. 4.13 Заполнение полей таблицы

5. Ввод значений в базу данных

5.1 Мастер подстановки

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

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

Вернемся к нашему примеру. При создании поля «Код_категории» таблицы «Шины» выберем ему тип данных «Мастер подстановки», поскольку в это поле нужно заносить данные, содержащиеся в таблице «Категория».

После выбора типа данных «Мастер подстановки» сразу запускается мастер, который предлагает последовательность окон построения поля подстановки. Переход к следующему окну мастера осуществляется кнопкой «Далее» >, а возврат к предыдущему окну -- кнопкой < «Назад». После завершения построения (в последнем окне мастера) надо щелкнуть кнопку «Готово». Последовательность окон мастера подстановки для поля «Код_категории» приведена на рисунки. 5.1-5.6).

Первое окно мастера запрашивает информацию о типе создаваемого набора значений: фиксированный или считываемый из ключевого столбца.

Рис. 5.1 Выбор типа создаваемого набора значений

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

Рис. 5.2 Выбор таблицы или запроса со значениями

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

Рис. 5.3 Выбор поля

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

Рис. 5.4 Создание подстановки

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

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

Рис. 5.5 Выбор подстановки

И наконец, в последнем окне задается параметр «Подпись для поля подстановки».

Обычно на этом окне ничего менять не надо, так как «Мастер» сам корректно выбирает имя.

Рис. 5.6 Продолжение создания подстановки

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

Вид функционирования настроенной приведенным выше способом подстановки значений в поле «Код_категории» рассмотренной выше таблицы «Шины» представлен на рисунке 5.7.

Рис.5.7 Подстановка значения в поле «Код_категории»

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

Рис.5.8 Ввод значений фиксированного списка подстановок

Использование фиксированного списка подстановок показано на рисунке 5.9.

Рис. 5.9 Фиксированный список подстановок

5.2 Связь таблиц

После создания всех таблиц базы данных остаётся установить связи между ними. Для этого необходимо:

1. Перейти на закладку «Работа с базами данных» и нажать на кнопку «Схема данных»

2. В открывшемся окне «Схема данных» открыть, используя правую клавишу, контекстное меню.

3. Выбрать пункт «Добавить таблицу».

В появившемся окне «Добавление таблицы» выделить мышью название нужной таблицы и щелкнуть на кнопке «Добавить» (рисунок 5.10).

Рис. 5.10 Добавление таблицы в окно «Схема данных»

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

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

Настроить размеры макетов таблиц, растягивания их мышью (рисунок 5.11).

Рис. 5.11 Настойка макетов таблиц

Для установления связи между двумя таблицами необходимо:

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

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

- отпустить левую кнопку мыши над внешним ключом подчиненной таблицы.

6. В появившемся окне «Изменение связей» будут высвечены имена полей, по которым связываются таблицы. Необходимо отметить пункт «Обеспечение целостности данных». При этом MS Access сделает невозможным запись в подчиненную таблицу такого значения общего поля, которого нет в главной таблице (рисунок 5.12).

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

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

В окне «Схема данных» между таблицами устанавливается связь в виде помеченной линии. Для удаления связи выделить её мышью (линия приобретает жирные очертания) и нажать кнопку «Delete» на клавиатуре.

7. Повторить действия п. п. 5-6 для установления всех связей.

8. Закрыть окно «Схема данных», ответив «Да» на предложение системы сохранить изменение макета схемы данных.

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

Замечание: при построении схемы данных таблицы должны быть пустыми.

Рис. 5.13 Схема данных рассматриваемого примера

6. Поиск информации в базе данных

6.1 Сортировка

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

Рис. 6.1 Группа команд «Сортировка и фильтр»

Альтернативным вариантом сортировки является использование контекстного меню, вызываемое щелчком правой клавиши меню на заголовке поля таблицы

Результат сортировки зависит от типа данных поля:

- значения текстовых полей упорядочиваются по алфавиту;

- числа и денежные суммы упорядочиваются по величине;

- даты упорядочиваются в хронологическом порядке;

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

Для полей с типом данных «Поле МЕМО», «Поле объекта OLE», «Гиперссылка» и «Вложение» сортировка не поддерживается.

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

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

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

Например, если в таблице «Шины» необходимо отсортировать записи по значению поля «Производитель», а затем по «Цене», необходимо поместить столбец «Цена» непосредственно после поля «Производитель».

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

2. Следующим шагом является выделение диапазона столбцов для сортировки.

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

3. Открыть на ленте вкладку «Главная» и нажать кнопку для сортировки по возрастанию или по убыванию (рисунок 6.2).

Рис. 6.2 Сортировка по значениям нескольких полей

Чтобы созданная сортировка была применена при следующем открытии таблицы, требуется сохранить изменения, нажав сочетание клавиш «Ctrl+S».

6.2 Фильтрация

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

В программе MS Access предусмотрены следующие типы фильтров:

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

- фильтр по форме позволяет отбирать записи по значениям нескольких столбцов;

- расширенный фильтр позволяет использовать сложные условия отбора.

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

Для управления фильтрами служит группа команд «Сортировка» и фильтр вкладки «Главная» (см. рисунок 2.1).

Чтобы включить или отключить созданный фильтр, используется кнопка «Применить фильтр», а чтобы удалить, фильтр -- кнопка «Дополнительно», где в меню есть пункт «Очистить все фильтры».

6.3 Простой фильтр

Простой фильтр содержит условие на значение одного столбца.

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

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

1. Щелкнуть кнопкой мыши в любой ячейке столбца.

2. На вкладке «Главная» нажмите кнопку «Фильтр».

На экране появится окно сортировки и фильтрации (рисунок 6.3).

Рис. 6.3 Окно сортировки и фильтрации

3. Создать условие отбора можно одним из двух способов:

Первый способ.

В перечне всех значений поля снять флажок «Выделить все» и установить флажки для тех значений поля, которые необходимы (рисунок 6.4).

Рис.6.4 Выбор требуемых значений

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

Рис. 6.5 Результат фильтрации

Второй способ.

В пункте меню, расположенном непосредственно над перечнем значений: в зависимости от типа данных этот пункт может называться «Текстовые фильтры», «Числовые фильтры» или «Фильтры дат». В подменю выбрать один из встроенных фильтров (рисунок 6.6).

Рис. 6.6 Выбор значения фильтра

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

Например, если для числового поля выбирается встроенный фильтр «Между», программа MS Access предлагает ввести нижнюю и верхнюю границу интервала значений (рисунок 6.7).