Файл: Проектирование базы данных учёта реализации продукта.pdf

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

Также Access обладает большими возможностями по экспорту/импорту данных в разнообразные форматы через механизм ODBC: от текстовых файлов и таблиц Excel до любой серверной СУБД.

Еще одним немаловажным преимуществом MS Access является встроенные средства разработки приложений. Большое количество приложений, которые распространяемые среди пользователей, содержат некоторый объем кода языка Visual Basic for Applications.

VBA – единственное средство для выполнения различных стандартных задач в MS Access (построение команд SQL, обработка ошибок, работа с переменными, использование Windows API), для создания сложных приложений.

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

Одной из самых необходимых функций каждой СУБД является защита информации, которая размещена в таблицах базы данных. [7]

СУБД MS Access хранит данные о защите в двух местах. При установке программа Setup создает в папке Program Files\Microsoft_Ofice\Оffice стандартный файл для рабочей группы – System.mdw, который далее при запуске Access используется по умолчанию. Этот файл содержит информацию обо всех группах и пользователях. При создании новой базы данных MS Access сохраняет данные о правах, которые предоставляются конкретным группам и пользователям, непосредственно в файле базы данных.

Расположение файла рабочей группы находится в реестре Windows. Можно также использовать служебную программу операционной системы Windows – Wrkadm.exe (администрирование рабочих групп) для редактирования текущего или создания новой рабочей группы. Также можно выбрать необходимый файл рабочей группы при выполнении приложения, задав при этом соответствующий параметр в командной строке.

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

1.3.Проектирование логической структуры базы данных

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

Для решения исходной задачи, необходимы такие входные документы:

---

– список поставщиков (код поставщика, название фирмы-поставщика, адрес, телефон);

– список покупателей (код покупателя, название фирмы-покупателя, адрес, телефон);

– список товаров (код товара, поставщик, название товара, единицы измерения, количество, цена покупки за единицу товара, цена продажи за единицу товара);

– перечень сделок (код товара, поставщик, покупатель, количество проданного товара).

Рассмотрим следующие сущности:

– Поставщики;

– Покупатели;

– Товар на складе;

– Сделки о продаже

и определим для них атрибуты.

– Покупатели:

• Код покупателя;
• Наименование фирмы-покупателя;
• Адрес;
• Телефон.

– Поставщики:

• Код поставщика;
• Наименование фирмы-поставщика;
• Адрес;
• Телефон.

– Товары на складе:

• Код товара;
• Поставщик;
• Количество;
• Цена покупки;
• Название товара;
• Единицы измерения;
• Цена продажи.

– Сделки о продаже:

• № п/п;
• Код товара;
• Поставщик;
• Покупатель;
• Количество;
• Сделки о продаже.

Определим перечень ограничений целостности.

У каждого товара, который имеет свой уникальный код, есть определенный поставщик, а один поставщик может поставлять разные товары. Поэтому связь между сущностями Товары на складе и Поставщик многие к одному (М:1).[3]

Каждый покупатель может купить несколько разных товаров, а один товар может быть куплен только определённым покупателем. Поэтому связь между сущностями Покупатели и Сделки о продаже один ко многим (1: М).

Каждый поставщик может поставлять несколько разных товаров, а один покупатель может несколько товаром разных поставщиков. Поэтому связь между сущностями Покупатели и Поставщики многие ко многим (N: М).

В результате получим ER-диаграмму:

Рисунок 2. ER - диаграмма

1.4.Проектирование физической структуры базы данных

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

Таблица 1. Описание таблиц базы данных

№ п/п	Название поля	Тип данных
Таблица Покупатели
1	Код покупателя (ключевое поле)	Числовой
2	Название фирмы-покупателя	Короткий текст
3	Адрес	Короткий текст
4	Телефон	Короткий текст
Таблица Поставщики
1	Код покупателя (ключевое поле)	Числовой
2	Название фирмы-покупателя	Короткий текст
3	Адрес	Короткий текст
4	Телефон	Короткий текст
Таблица Сделки о продаже
1	№ п/п (ключевое поле)	Счетчик
2	Код товара	Числовой
3	Поставщик	Числовой
4	Покупатель	Числовой
5	Количество проданного товара	Числовой
Таблица Товар на складе
1	Код товара (ключевое поле)	Числовой
2	Поставщик	Числовой
3	Название товара	Короткий текст
4	Единицы измерения	Короткий текст
5	Количество	Числовой
6	Цена покупки за единицу товара	Денежный
7	Цена продажи за единицу товара	Денежный

---

Выполним нормализацию базы данных.

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

В таблице 1 все атрибуты являются атомарными.

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

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

Приведем ко второй нормальной форме. Для этого нужно определить ключи. Это выполнено в пункте выше.

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

Отношение находится в третьей нормальной форме (3НФ) тогда и только тогда, когда отношение находится в 2НФ и все неключевые атрибуты взаимно независимы.

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

Рассмотрим структуры созданных таблиц в выбранной СУБД:

После запуска СУБД и создания базы данных нужно нажать на ленту «Создание» и выбрать в разделе «Таблицы» Конструктор таблиц.

После этого откроется окно в котором нужно указать название поля, тип данных, описание (в случае необходимости), свойства полей, ключевое поле. Внешний вид окна конструктора таблиц показан на рис. 3 – 6:

Рисунок 3. Таблица Покупатели в режиме конструктора

Рисунок 4. Таблица Поставщики в режиме конструктора

Рисунок 5. Таблица Товар на складе в режиме конструктора

Рисунок 6. Таблица Сделки по продажам в режиме конструктора

В результате получим следующую структуру БД:

Рисунок 7. Схема данных

ГЛАВА 2.ПРАКТИЧЕСКАЯ ЧАСТЬ

2.1. Контрольный пример решения задачи

---

Для реализации контрольного примера рассмотрим заполненные таблицы спроектированной БД (рисунки 8 – 11):

Рисунок 8. Таблица Покупатели

Рисунок 9. Таблица Поставщики

Рисунок 10. Таблица Товар на складе

Рисунок 11. Таблица Сделки по продажам

В Access можно вводить данные непосредственно в таблицу в режиме таблица. Но обычно для ввода данных в БД Access используют формы (рисунки 12 – 15):

Рисунок 12. Форма Покупатели

Рисунок 13. Форма Поставщики

Рисунок 14. Форма Товар на складе

Рисунок 15. Форма Сделки по продажам

Отчет – это форматированное представление данных, которое выводится на экран, в печать или файл.

Они позволяют извлечь из базы нужные сведения и представить их в виде, удобном для восприятия, а также предоставляют широкие возможности для обобщения и анализа данных (рисунки 16 - 18).[3]

Рисунок 16. Отчет Продукция на складе

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

Рисунок 17. Отчет Покупатели

Рисунок 18. Отчет Сделки о продаже

2.2. Разработка интерфейса и реализация проекта

Запрос – это средство выбора необходимой информации из базы данных. Вопрос, сформированный по отношению к базе данных, и есть запрос.

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

Создадим запрос Спрос на товар с помощью режима QBE.

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

Рисунок 19. Окно Добавление таблицы

Введем информацию в конструктор запросов так, как показано ниже:

Рисунок 19. Окно Ввод полей в конструктор запросов

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

---

Далее нужно выбрать необходимую функцию (Sum()) в строке группировка:

Рисунок 20. Окно Итоговая операция

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

[Введите название товара]

Рисунок 21. Окно создания параметрического запроса

Запустим запрос на выполнения и введем название товара:

Рисунок 22. Окно задания параметра

В результате получим:

Рисунок 23. Запрос Спрос

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

Для этого используем SQL-выражения, которые имеют следующий синтаксис:

SELECT Список столбцов

FROM Список таблиц

[WHERE Условия выбора строк]

[ORDER ВУ Спецификация сортировки];

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

Созданный запрос будет иметь такой код:

SELECT TOP 1 Покупатели.[Название фирмы-покупателя], Sum([Сделки о продаже].[Количество проданного товара]) AS [Sum-Количество проданного товара]

FROM Покупатели INNER JOIN [Сделки о продаже] ON Покупатели.[Код покупателя] = [Сделки о продаже].Покупатель

GROUP BY Покупатели.[Название фирмы-покупателя]

ORDER BY Sum([Сделки о продаже].[Количество проданного товара]) DESC;

В результате получим:

Рисунок 24. Запрос Выгодные по объемам

То есть, имеется 2 покупателя, которые купили максимальное количество товаров.

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

SELECT TOP 1 Покупатели.[Название фирмы-покупателя], Sum([Количество проданного товара]*[Цена продажи за единицу товара]) AS Сумма

FROM [Товар на складе] INNER JOIN (Покупатели INNER JOIN [Сделки о продаже] ON Покупатели.[Код покупателя] = [Сделки о продаже].Покупатель) ON [Товар на складе].[Код товара] = [Сделки о продаже].[Код товара]

GROUP BY Покупатели.[Название фирмы-покупателя], [Сделки о продаже].[Количество проданного товара], [Товар на складе].[Цена продажи за единицу товара]

ORDER BY Sum([Количество проданного товара]*[Цена продажи за единицу товара]) DESC;

---