Проектирование баз данных с помощью Microsoft Access

Таблица Книги содержит всю информацию о книгах в магазине.

Таблица очень большая поэтому приведу лишь часть скриншота

.

Книги
Код Книги	Название	Раздел	Код Автора	Код Поставщика	Год издания	Количество	Цена	ДатаПоставки
1	Атлас автодорог Подмосковья	Автомобили	1	1	2011	23	100,00р.	22.11.2005
2	Моя любовь - автомобиль	Автомобили	2	3	2010	12	160,00р.	22.11.2004
3	Дураки, дороги и другие особенности национального вождения	Автомобили	2	5	2010	21	255,00р.	22.11.2004
4	Правила дорожного движения 2010	Автомобили	3	3	2007	54	122,00р.	02.06.2006
5	Ошибки начинающих автомобилистов. Советы бывалых	Автомобили	4	2	2008	12	452,00р.	02.06.2006

Таблица Отделы хранит информацию об отделах магазина.

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

Таблица Покупатели хранит информацию о клиентах магазина.

Таблица Продавцы содержит информацию о продавцах, работающих в магазине.

Таблица Разделы содержит информацию о том какие разделы входят в отдел и о том, кто из продавцов продает книги данного раздела.

 

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

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

В Microsoft Access можно выделить три типа ключевых полей: счетчик, простой ключ и составной ключ.

Ключевые  поля счетчика

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

Простой ключ

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

Составной ключ

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

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

 

Схема данных.

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

.

 

Целостность Базы Данных.

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

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

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

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

Установив целостность данных, необходимо следовать следующим  правилам.

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

Создание запроса.

Часто запросы в Microsoft Access создаются автоматически, и пользователю не приходится самостоятельно их создавать.

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

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

Запросы на выборку и их использование.

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

Например, в нашем проекте  используется запрос на выборку: объем продаж по месяцам. Вот результат работы этого запроса:

Объем продаж по Месяцам
Месяц	Sum-Кол-во
3	27
4	64
5	1

 

Параметрические запросы и их использование.

Как правило, запросы с  параметром создаются в тех случаях, когда предполагается выполнять  этот запрос многократно, изменяя лишь условия отбора. В отличии от запроса на выборку, где для каждого условия отбора создается свой запрос и все эти запросы хранятся в БД, параметрический запрос позволяет создать и хранить один единственный запрос и вводить условие отбора (значение параметра) при запуске этого запроса, каждый раз получая новый результат. В качестве параметра может быть любой текст, смысл которого определяет значение данных, которые будут выведены в запросе. Значение параметра задается в специальном диалоговом окне. В случае, когда значение выводимых данных должно быть больше или меньше указываемого значения параметра, в поле «Условие отбора» бланка запроса перед параметром, заключенным в квадратные скобки ставится соответствующий знак. Можно также создавать запрос с несколькими параметрами, которые связанны друг с другом логическими операциями И и ИЛИ. В момент запуска на выполнение MS Access отобразит на экране диалоговое окно для каждого из параметров. Помимо определения параметра в бланке запроса, необходимо указать с помощью команды Запрос - Параметры соответствующий ему тип данных:

Примером параметрического запроса в нашем проекте может быть запрос Код_Автор. При выполнении запроса access просит ввести код автора. От этого кода зависит и результат который выведется на экран. Вот скриншоты.

 

Запросы на изменение записей. Удаление.

Эти запросы позволяют удалять  записи из таблиц.

Рассмотрим пример этого запроса в нашей базе данных.

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

В случае, если заказ выполнен. Для этого снимаем галочку в поле Актуальность для интересующей записи и при выполнение запроса на удаление, удалится эта запись.

Вот скриншоты:

 Выбираем из списка запросов Удаление выполненных заказов.

И вот результат:

 

Всего же в моей БД 18  запросов и  каждый относится к какому-то определенному  типу. Вот их список:

1) График Заказов – запрос, на основе которого строится график заказов.

2) График Поставок – запрос, на основе которого строится график поставок.

3) Заказы покупателей со стоимостью – запрос выводящий информацию о заказе покупателей и считающий стоимость заказа.

4) Код_Автор – запрос, позволяющий получить информацию об авторе по его коду.

5) Код_Поставщик – запрос, позволяющий получить информацию о поставщике по его коду.

6) Объем продаж по месяцам – отображает информацию о количестве проданных магазином книг за каждый месяц.

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

8)  Подведение итогов по магазину за день -  позволяет получить информацию о количестве проданных магазином книг за  конкретный  день.

9) Подведение итогов по отделам за день – сколько продал отдел за конкретный день.

10)  Подсчет остатков по отделам - выводит информацию о книгах которые заканчиваются.

11) Поиск литературы по Автору – ищет книги по фамилии/псевдониму автора.

12) Поиск литературы по названию Книги – выдает информацию о книге по названию.

13)  Поиск литературы по Отделам – ищет книги содержащиеся в введенном отделе.

14) Поиск Литературы по разделам – ищет книги по введенному разделу.

15) Продавцы-кондидаты  на премию – выдает список продавцов на премию и назначает премию.

16) Учет продаж по отделам – выдает список заказов отдела.

17)Удаление выполненных заказов – удаляет не актуальные заказы.

18)Удаление выполненных поставок – удаляет не актуальные поставки.

 

Создание формы

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

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

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

Каждая вкладка объединяет в себе набор схожих действий над БД. Каждая кнопку при нажатие выполняет какое-то действие. Чаще всего это встроенный макрос открытия формы или отчета.

 

С помощью формы заказы покупателей можно посмотреть интересующую информацию о заказах покупателей.

С помощью формы Заказы магазина можно посмотреть информацию о заказах магазина.

 

 

Форма поиск литературы по автору:

 

С помощью формы книги можно изменить информацию о книгах. Но только поле «Цена» и «Количество» т.к. Эти параметры могут меняться со временем. Остальные поля были защищены от изменений.

Всего же в моем проекте 26 форм. Вот их список:

1) Авторы – отображает информацию об авторах книг.

2) Главная – форма, запускающаяся при запуске базы данных благодаря которой можно работать с БД пользователю, не “влезая вовнутрь” БД.