Система баз данных MS ACCESS

Введение
1.База и банк данных
1.1.Основные требования к БД
1.1.1.Секретность данных
1.2.Этапы создания БД
1.3Правила и рекомендации, которых необходимо придерживаться
2.Системы управления базами данных (СУБД)
2.1.Что такое системы управления базами данных?
2.2.Основные функции СУБД
2.3.Типовая организация современной СУБД
2.4.Классификация СУБД
2.5.Особенности использования
3.Проектирование модели данных
3.1.Информационное исследование
3.2.Построение таблиц
3.3.Разработка схемы связи между таблицами - модели базы данных
3.4.Реализация модели в среде СУБД Access
3.5.Проектирование интерфейса базы данных
3.5.1.Разработка экранных форм ввода данных и отчетных форм
3.6.Реализация интерфейса
3.6.1.Разработка запросов
3.7.Разработка отчетных форм
3.8.Оформление интерфейса как единого целого
Заключение
Глоссарий
Список литературы
Список сокращений
Приложение А
Приложение Б
Приложение В
Приложение Г
Приложение Д

Система баз данных MS ACCESS

Организация
Руководитель
Адрес
Телефон
Расчетный счет
Оплата
Номер заказа
Дата оплаты
Сумма оплаты
Спецификация
Код заказа
Код товара
Цена
Наценка
Скидка
Количество
Товар
Код товара
Наименование
Единица измерения
Цена
В таблице «Заказы» хранится информация о всех заказах, которые сделали клиенты компании.
В таблице «Клиенты» хранится информация о клиентах, с которыми сотрудничает данная компания.
В таблице «Товары» находится данные о тех товарах, которые реализует данная компания.
В таблице «Оплата», хранятся данные об оплате клиентами товара, и дата когда эта оплата была произведена.
В таблице «Спецификация» содержит данные о всех заказах, которые сделали заказчики, о количестве товаров, цене и о наценке для конкретного заказчика. В данной таблице также хранитсяданные о скидках, которые получают клиенты на конкретный заказ.
3.2. Построение таблиц
Для построения таблиц в базе данных, необходимо привести все таблицы к 3- нормальной форме.
Нормализация- это способ реализации связей и хранение данных в таблицах базы данных. Целью нормализации является избавление от лишних связей данных в таблицах.
В таблицы базы данных достаточно привести к третей нормальной форме, чтобы удалить все ненужные связи, и привести связи между таблицами к оптимальной форме.
Разработанная база данных приведена к третей нормальной форме. Для того, чтобы разработанная база данных была приведена к 3-НФ, необходимо, чтобы она соответствовала двум другим нормальным формам.
1-НФ
В 1-НФ-на пересечении строки и столбца не может содержаться нескольких значений.
Если открыть разработанную базу, можно убедится, что каждое значение в базе данных расположено в отдельных ячейках, и что нет таких ячеек, в которых содержалось бы более одного значения.
Итак делаем вывод, что разработанная база данных приведена в 1-НФ.
2-НФ
Первым условием для второй нормальной формы -2-НФ- является выполнение требований, предъявляемых к 1-НФ. Второе условие состоит в том, что у каждой строки таблицы базы данных должен быть уникальный идентификатор.
В данной базе данных в таблице «Заказы» это поле «Номер заказа». В таблице «Клиенты» это поле «Код клиента». В таблице «Товары» это поле « Код товара». В таблице «Оплата» это поле «Номер заказа». В таблице «Спецификация» это два поля «Код заказа» и «Код товара».
Исходя из этого, делаем вывод, что разработанная база данных приведена ко 2 – НФ.
3-НФ
Первым условием для третий нормальной формы -3-НФ- является выполнение требований, предъявляемых к 1-НФ и 2-НФ. В таблице модели 3-НФ не должно быть избыточных неключевых столбцов, связанных с неключевыми столбами других таблиц.
Главная цель нормализации таблиц- избавится от избыточной информации, содержащейся в неключевых столбцах. Т.е. в базе данных должна быть возможность изменять значение любого поля (не входящего в первичный ключ) без воздействия на данные других полей.
Например, если необходимо изменить данные о клиентах, то изменения коснутся только таблицы «Клиенты».
Исходя из этого делаем вывод, что данная база данных приведена к 3-НФ.
3.3. Разработка схемы связи между таблицами - модели базы данных
Последним этапом проектирования базы данных заключается в создании связей между таблицами. В большинстве случаев две таблицы связаны отношением «один – ко-многим». Это означает, что любая запись в первой таблице может быть связана с несколькими записями во второй, однако любая запись второй таблицы связана только с одной записью в первой.
Разработка схемы данных включает в себя построение связей между таблицами базы. Эти связи обеспечивают целостность данных в таблицах. И при изменении данных в одной таблице меняются данные во всех таблицах. Как видно и рисунка 1 связь между таблицами осуществляется по ключевым атрибутам таблицы.
Рис.1 Схема данных
3.4. Реализация модели в среде СУБД Access
Для реализации модели в СУБД Access, необходимо создать таблицы и заполнить их данными.
Для того, чтобы создать таблицу в Access, необходимо воспользоваться конструктором. На мой взгляд это очень удобный инструмент, для решения такого рода задач. В нем можно сразу определить тип поля, определиться с первичным ключом.
В данной работе были разработаны пять таблиц с данными организации, и одна таблица для кнопочной формы.
Таблица «Клиенты»
Код клиента
Организация
Руководитель
Адрес
Телефон
Расчетный счет
Где «Код клиента» является ключевым полем.
Таблица «Заказы»
Номер заказа
Код клиента
Дата заказа
Дата поставки
Где «Номер заказа»» ключевое поле
Таблица «Оплата»
Номер заказа
Дата оплаты
Сумма оплаты
Где «Номер заказа» ключевое поле.
Таблица «Спецификация»
Код заказа
Код товара
Цена
Наценка
Скидка
Количество
Где «Код заказа» и «Код товара» ключевые поля.
Таблица « Товар»
Код товара
Наименование
Единица измерения
Цена
Где «Код товара» является ключевым полем.
3.5. Проектирование интерфейса базы данных
3.5.1. Разработка экранных форм ввода данных и отчетных форм
В данной работе было разработано пять форм для ввода информации в таблицы.
Форма «Заказы», «Клиенты», «Спецификация», «Оплата», «Товар».
Форма «Спецификация» является подчиненной формой форме «Заказы».
Экранные формы должны удовлетворять следующим требованиям
1. Ввод и редактирование данных
2. Управлять ходом выполнения приложения (использовать VBA).
3. Просмотр введенных данных
Для создания формы в СУБД Access, на мой взгляд лучше воспользоваться мастером форм.
Форма «Товар»
Данная форма предназначена для записи новых товаров в таблицу «Товары».
Структура данной формы.
В заголовке формы расположены надписи «Справочник товаров», «Наименование», «Единица измерения», «Цена». В области данных расположены поля «Наименование», «Единица измерения», «Цена».
Данные поля служат для ввода информации в таблицу.
В примечании формы расположено поле, в котором расположена формула =Nz(DMax("[Код товара]";"[Товар]"))+1.
Недостатком такой формы является то, что для пользователя, который будет работать с приложением, непонятно, для чего нужен код товара. Он может ошибиться при его заполнении. Поэтому лучше организовать автоматическое формирование кода товара и скрыть его от пользователя приложения. Принцип создания кода товара для вновь вводимого наименования прост: сформировать новый код товара и занести его в поле «Код товара» перед тем, как запись из формы будет помещена в таблицу. Для этого можно воспользоваться следующим способом:
1. Создать несвязное поле в разделе примечаний, установить для свойства «Данные» в одноименной вкладке значение строки вычисляющей новый код товара
=Nz(DMax("[Код товара]";"[Товар]"))+1
2. Построить для события «До обновления» формы процедуру позволяющую добавлять во вновь вводимый товар код товара, вычисляемый в несвязном поле. Событие «До обновления» имеет следующий смысл. Событие это любое изменение состояния объекта MS Access.Связанные поля формы отражают данные содержащиеся в соответствующей таблице (в данном случае таблице «Товар»). При вводе нового товара наименование, единица измерения и цена хранятся в полях формы и переносятся в таблицу только при переходе к следующей строке формы. Перед тем, как они будут помещены в таблицу, возникает событие «До обновления». Поскольку на этот момент для нового товара занесены все данные кроме кода товара, необходимо занести код, сформированный в несвязанном поле в поле «Код товара». Это делается с помощью фрагмента программы, который переносит рассчитанный в «Поле15» код нового товара в поле «Код товара». И уже полный набор сведений о товаре переносится в таблицу базы данных.
3. Перевести форму в режим просмотра, проверить правильность занесения кода товара и скрыть объекты «Код товара» и «Поле15».
В данной форме присутствует поле со списком «Единица измерения». Список, который в данном поле находится, позволяет быстро выбирать нужную единицу измерения из раскрывающегося списка. Данный список удобен тем, что каждый раз при вводе единицы измерения, ненужно ее писать.
А также в примечании формы расположена кнопка, которая позволяет выйти из формы.
Для того, чтобы кнопка работала необходимо для нее выбрать событие, которое она будет совершать. В данном случае это событие «Закрыть форму».
В приложении А приведена копия экрана данной формы.
Форма «Список клиентов»
Данная форма предназначена для занесение в таблицу данных о новых клиентах и просмотр информации о существующих клиентах.
Структура данной формы
В заголовке формы расположены надписи «Справочник клиентов», «Организация», «Руководитель», «Адрес», «Телефон», «Расчетный счет».
В области данных расположены поля «Организация», «Руководитель», «Адрес», «Телефон», «Расчетный счет».
В примечании формы расположено поле, в котором находится формула =Nz(DMax("[Код клиента]";"[Клиенты]"))+1
Данная формула позволяет автоматическое формирование кода клиента. Для этого необходимо написать процедуру аналогично как для формы «Справочник товаров». Только Поле15 заменить на Поле14 (в нем находится формула, которая описана выше, которая и формирует автоматически код клиента.)
В приложении Б приведена копия экрана данной формы.
Форма «Заказы»
Форма «Заказы» имеет подчиненную форму «Спецификация».
Такая форма построение форм позволяет заносить данные и просматривать их одновременно сразу из нескольких таблиц (в нашем случае из двух). При этом следует всегда начинать с создания наиболее вложенной формы и продолжать работу, постепенно приближаясь к основной.
Структура формы «Заказы»
Заголовок формы
В заголовке формы расположены надписи «Заказ №», «Организация», «Дата заказа», «Дата поставки», а также поля «Номер заказа», «Организация», «Дата заказа», «Дата поставки», и две кнопки «Новый», «Оплата». В заголовке расположена подчиненная форма «Спецификация».
Кнопка «Новый» вызывает новый экран для ввода нового заказа клиента. Кнопка «Оплата» вызывает форму «Оплата», в которой фиксируются все производимые оплаты данного клиента, а так же дата внесения сумм.
Для работы кнопки «Новый» необходимо написать обработку действий:
Private Sub Кнопка12_Click()
On Error GoTo Err_Кнопка12_Click
DoCmd.GoToRecord , , acNewRec
If Nz([Номер заказа]) = 0 Then
[Номер заказа] = Nz(DMax("[Номер заказа]", "[Заказы]")) + 1
Me![ДатаЗаказа] = Date
End If
DoCmd.GoToControl "ПолеСоСписком8"
Для работы с кнопкой «Оплата», необходимо написать обработку действий
Private Sub Кнопка13_Click()
On Error GoTo Err_Кнопка13_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Оплата"
stLinkCriteria = "[Номер заказа]=" & Me![Номер заказа]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Кнопка13_Click:
Exit Sub
В примечании форм расположена надпись «Итого по заказу» и поле, в котором расположена формула считающая итог. Для создания данной формы необходимо в конструкторе форм создать форму и сохранить. Далее вызвать конструкцию формы «Заказы» и на панели элементов выбрать «Подчиненная форма». После необходимо указать какая форма будет считаться подчиненной.
Структура подчиненной формы «Спецификация».
Заголовок формы
В заголовке формы находятся надписи «Товар», «Цена», «Наценка», «Количество», «Стоимость».
В области данных находятся поля «Товар», «Цена», «Наценка», «Количество», «Стоимость». В поле «Стоимость» введена формула, для вычисления стоимости продукции, исходя из цены, наценки количества и наценки.
В примечании формы находится поле, в котором находится формула, подводящая итоговую сумму заказа для выбранного заказчика.
Для автоматического занесения цены после выбора товара в свойство «После обновления» (в базу данных данные заносятся после того, как все поля таблиц будут заполнены) поля «Код товара» нужно вставить процедуру обработки, включающую следующий код: [Цена]=DLookup("[Цена]", "[Товар]", "[Код товара]=" & [Код товара]) . Здесь полю «Цена» присваивается значение, возвращаемое функцией Dlookup , которая использует три параметра: имя поля с ценой, имя таблицы, условие отбора. Функция Dlookup выбирается из встроенных функций, предоставляемых построителем выражений в группе «По подмножеству». Условие представляет совой вычисляемое выражение, включающее текстовую константу и код товара из поля «Код товара». Если введенный код товара будет, например, равен 2, то выражение даст "[Код товара]=2", и в таком виде будет передано функции Dlookup для поиска.
Форма «Оплата»
Форма «Оплата» предназначена для занесения сведений обо всех производимых оплат клиента.
Способ занесения номера заказа в поле «Номер Заказа».
Автоматически сформировать «Номер заказа» перенеся его из формы «Заказы». Для этого в событии «До обновления» формы «Оплата» необходимо с помощью редактора создать процедуру обработки события следующего вида:
If Nz([Номер заказа]) = 0 Then
[Номер заказа] = Forms![Заказы]![Номер заказа]
End If
Эта процедура для каждой новой записи значению поля «Номер заказа» присваивает значение одноименного поля из формы «Заказы».
Для всех элементов форм в Свойство группа «Макеты» занесены такие характеристики как цвет символа, ширина, высота, граница. В общем такие характеристики, которые позволяют настроить форму.
Для всех элементов форм в Свойство группа «Данные» занесены такие данные как, в какое поле таблицы связано с полем форм, маска ввода данных и др. В общем информация для корректного занесения данных в поле таблицы.
В приложении В приведена копия экрана данной формы.
3.6. Реализация интерфейса
Под запросом понимается совокупность атрибутов реляционной базы данных, включающих необходимую пользователю информацию и условий на их выборку.
3.6.1. Разработка запросов
В данной работе было реализовано девять запросов. Все они помогают оператору базы данных оперативно найти нужные данные, даже если они находятся в разных таблицах базы.
Для создания запроса необходимо запустить конструктор запросов, и выбрать те таблицы, данные которых будут выбираться для запроса. Существует два метода создания запроса- это использовать конструктор или использовать язык запросов-SQL. В данной работе приведен пример использования языка SQL.
Запрос «Неоплаченные заказы»
Данный запрос выводит на экран тех заказчиков, которые еще не оплатили заказ.
Программа
SELECT Клиенты.Организация, Заказы.[Номер заказа], Оплата.[Сумма оплаты]
FROM Клиенты INNER JOIN (Заказы LEFT JOIN Оплата ON Заказы.[Номер заказа] = Оплата.[Номер заказа]) ON Клиенты.[Код клиента] = Заказы.[Код клиента]
WHERE (((Оплата.[Сумма оплаты]) Is Null));
Запрос «Сумма заказов»
Данный запрос выводит на экран итоговую сумму заказав по всем заказчикам.
Программа
SELECT Клиенты.Организация, Заказы.[Номер заказа], Sum([Цена]*[Количество]*(1+[Наценка]-[Скидка])) AS Стоимость
FROM Клиенты INNER JOIN (Заказы INNER JOIN Спецификация ON Заказы.[Номер заказа] = Спецификация.[Код заказа]) ON Клиенты.[Код клиента] = Заказы.[Код клиента]
GROUP BY Клиенты. Организация, Заказы.[Номер заказа];
Запрос «Анализ оплаты»
Он показывает общую картину состояния оплаты заказов и может быть использован для анализа ситуации.
Программа
SELECT [Суммы заказов].Организация, [Суммы заказов].[Номер заказа], [Суммы заказов].Стоимость, [Суммы оплаты заказов].[Sum-Сумма оплаты], [Суммы заказов]![Стоимость]-Nz([Суммы оплаты заказов]![Sum-Сумма оплаты]) AS Отклонение
FROM [Суммы заказов] INNER JOIN [Суммы оплаты заказов] ON [Суммы заказов].[Номер заказа] = [Суммы оплаты заказов].[Номер заказа];
Запрос «Заказы на отчетную дату»
Данный запрос определяет сколько было заказов на начальную дату и конечную дату. Это позволяет фирме анализировать сезонные продажи товара.
Программа
SELECT Клиенты.Организация, Заказы.[Номер заказа], Оплата.[Сумма оплаты]
FROM Клиенты INNER JOIN (Заказы LEFT JOIN Оплата ON Заказы.[Номер заказа] = Оплата.[Номер заказа]) ON Клиенты.[Код клиента] = Заказы.[Код клиента]
WHERE (((Оплата.[Сумма оплаты]) Is Null));
Также в данной базе данных были разработаны такие запросы как
«Какие товары заказали заказчики», «Сколько товара заказали заказчики», «Клиенты заказавшие товар».
3.7. Разработка отчетных форм
Отчеты представляют собой наилучшее средство для представления информации их базы данных в виде печатного документа.
Для создания отчета, необходимо воспользоваться мастером построения отчета.
В данной работе был создан отчет «Анализ оплаты заказов», который выводит результаты выполнения второго запроса. Перед созданием отчета необходимо выработать требования к его виду. Для этого проанализируем результат выполнения второго запроса. Возможным результатом анализа будут следующие требования.