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

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

Задание

Спроектировать БД для организации, занимающейся оптовой продажей краски. В БД учесть следующие потоки информации: Товар, Кассиры, Клиенты, Заказы, Состав заказов.

Назначение БД

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

·      Закупочная цена

·        Доход

·        Зарплата кассиров

·        Прибыль

·        Чистая прибыль

Теоретическая часть

база данный краска access

Схема.

Определение первичных ключей

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

Таблица «Кассиры»

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

Таблица «Клиенты»

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

Таблица «Заказы»

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

Таблица «Состав заказа»

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

Таблица «Товар»

Поле «Марка» не может являться первичным ключом, так как оно может повториться, потому что краска одного типа может быть произведена разными фирмами. Поле «Тип» не будет первичным ключом, так как краски разных марок могут быть одинакового типа. Поле «Цвет» может повториться, так как краски разных марок и типов могут быть одного цвета. Поле «Объём» повторяется, потому что краски разных цветов могут быть в упаковках одного объёма. «Разбавитель» для красок разных типов и производителей может применяться один и тот же. «Расход» у красок различных типов и производителей может быть одинаков. Различные краски могут высыхать за одинаковое время. «Цена» красок различных марок и типов может быть одинаковой.

Таблицы

Базы данных могут содержать различные объекты, но основными объектами любой базы являются её таблицы. Таблица - это объект, который определяется и используется для хранения данных. Каждая таблица содержит поля (столбцы) и записи (строки) и включает информацию об объекте определённого типа. Тип данных определяется значениями, которые предполагается вводить в поле, и операциями, которые будут выполняться с этими значениями. В своей работе я присвоил полям следующие типы данных:

Таблица «Кассиры»:

ФИО кассира - текстовый

Адрес кассира - текстовый

Дата рождения - дата/время

Телефон кассира - числовой

Таблица «Клиенты»:

Код клиента - счётчик

Название магазина - текстовый

Адрес магазина - текстовый

Телефон магазина - числовой

ФИО директора - текстовый

Таблица «Заказы»:

№ заказа - счётчик

Дата - дата/время

Время - дата/время

Код клиента - числовой

Код кассира - числовой

Таблица «Состав заказа»:

№ заказа - числовой

Код товара - числовой

Количество - числовой

Таблица «Товар»:

Код товара - счётчик

Марка - текстовый

Тип - текстовый

Цвет - текстовый

Объём - текстовый

Разбавитель - текстовый

Расход - текстовый

Время высыхания - текстовый

Цена - денежный

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

Все таблицы находятся в Приложении №1.

Списки

Фиксированный список - поле, в котором представлен набор фиксированных значений.

Фиксированные списки в БД созданы в таблице «Товар» на поля:

Марка - «Акродекор», «Лакра», «Текс», «Тиккурила, «Царицино»

Тип - «Для потолка», «Для пола», «По дереву», «По металлу», «Интерьерная», «Влагостойкая»

Цвет - «Белый», «Жёлтый», «Зелёный», «Красный», «Синий», «Чёрный»

Объём - «1 кг», «2 кг», «3 кг», «5 кг», «10 кг», «15 кг»

Разбавитель - «Не нужен», «Вода», «Олифа», «Скипидар», «Уайт-спирит»

Расход - «0,1 кг/кв м», «0,2 кг/кв м», «0,3 кг/кв м», «0,4 кг/кв м»,»0,5 кг/кв м»

Время высыхания - «2 ч», «6 ч», «10 ч», «12 ч», «24 ч»

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

Нефиксированные списки выводятся на внешние ключи. Количество нефиксированных списков равно количеству взаимосвязей минус количество сложных форм. Для определения сложных форм сначала находят центральную таблицу, исходя из пересечения потоков информации. Поток информации не прекращается, пока есть последовательность «1 - ∞». В моём случае центральной таблицей является «Состав заказа».

Схема

Сложной формой является: «Заказы» - «Состав заказа». В нефиксированный список не выводится первичный ключ, а выводится несколько полей. В данной работе я создал следующие нефиксированные списки: в таблице «Заказы» на поле «Код кассира», ссылаясь на таблицу «Кассиры» и поля «ФИО кассира», «Дата рождения». Одним полем «ФИО кассира» ограничиться нельзя, так как в организации могут работать несколько кассиров с одинаковым ФИО. В таблице «Заказы» на поле «Код клиента», ссылка происходит на таблицу «Клиенты» и поля «Название магазина», «Адрес магазина». Полем «Название магазина» нельзя ограничиться, так как филиалы одного магазина могут располагаться по разным адресам. В таблице «Состав заказа» на поле «код товара», ссылаясь на таблицу «Товар» и поля «Марка», «Тип», «Цвет», «Объём». Ссылка происходит на эти четыре поля, потому что для нахождения нужного товара потребуется именно эта информация.

Схема БД

Схема БД - это группа таблиц, взаимосвязанных между собой. Схема данных графически отображается в своём окне, где таблицы представлены списками полей, а связи - линиями между полями. Для создания схемы БД нужно открыть пункт меню Сервис и выбрать команду Схема данных или нажать кнопку Схема данных на панели инструментов. Затем нужно добавить все таблицы (контекстное меню/Добавить таблицу), потом устанавливаем взаимосвязи между ними. Для этого выделяем «мышью» в главной таблице ключевое поле, по которому устанавливается связь, и при нажатой кнопке «мыши» перетаскиваем курсор в соответствующее поле подчинённой таблицы. Откроется диалоговое окно Изменение связи. В нём нужно обеспечить целостность данных (поставить флажок), иначе при изменении данных в одной таблице, в связанной с ней другой таблице значение этого же поля не изменится, так же при занесении в таблицу несуществующих данных, программа будет выдавать ошибку, и мы не сможем занести данные, так как они не сохранятся. Также нужно поставить флажки напротив Каскадного обновления связанных полей и Каскадного удаления связанных полей. Наконец нажимаем ОК.

После проделанной операции над взаимосвязью появятся 1 (возле первичного ключа) и ∞ (возле неключевого поля или части составного ключа). Так обозначается вид взаимосвязи «один-ко-многим». Этот вид взаимосвязи означает, что одно значение в одной таблице может соответствовать нескольким значениям в другой таблице.

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

Формы

Таблицы в Microsoft Office Access предназначены только для хранения информации, в них не работают. Всё что представлено в таблицах, можно представить в формах в более удобном виде. Форма - это объект, в основном предназначенный для удобного ввода и отображения данных. Всё, что занесено в формы, будет сохранено в таблицах. Формы могут строиться только на основе таблиц или запросов. Формы позволяют вводить данные, корректировать их, добавлять и удалять записи. Можно создавать формы для работы с несколькими взаимосвязанными таблицами. Я создавал формы с помощью мастера.

Простая форма - форма, созданная на основе одной таблицы. Необходимое количество простых форм определяют из того, сколько таблиц не вошло в сложные формы. В моей БД простые формы созданы на таблицы «Кассиры», «Клиенты» и «Товар». В них включены все поля, которые находятся в таблицах. Заголовки форм соответствуют заголовкам таблиц. В режиме конструктора следует создать кнопки: «предыдущая запись», «следующая запись», «добавить запись» и «закрыть форму».

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

Для определения необходимого количества сложных форм нужно рассмотреть центральную таблицу (см. п. 4.1.2). После этого рассмотреть все возможные варианты сложных форм, включающих её, и выбрать наиболее удобный и практичный. В данной БД центральной таблицей является «Состав заказа». Возможные варианты сложной формы: «Заказы» - «Состав заказа», «Товар» - «Состав заказа». Первый вариант является наиболее удобным для заполнения, потому что при оформлении продажи одному из клиентов удобнее заносить товары, которые он покупает. А во втором варианте пришлось бы на каждый покупаемый товар, открывать новую запись и так вносить одни и те же данные по клиенту и продаже, а это занимало бы намного больше времени.

Главная кнопочная форма - это форма, на которой находятся кнопки, по которым можно перейти к любой форме БД, и кнопка перехода к отчётам, при нажатии на которую осуществляется переход к кнопочной форме отчётов (форма, содержащая кнопки, по которых осуществляется переход ко всем отчётам). Также на ней находится кнопка выхода из этой формы. Главная кнопочная форма создаётся в режиме конструктора. Для удобства, при открытии БД, главная кнопочная форма появляется автоматически.

Все формы находятся в Приложении №2.

Запросы

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

В своей БД я создал следующие запросы:

Закупочная цена

В моей фирме товар продаётся с наценкой в 40% от закупочной цены. Следовательно, закупочная цена будет равна 60% от цены. Для создания этого запроса в режиме мастера выбираем следующие поля: из таблицы «Товар» - «Код товара», «Марка», «Тип», «Цвет», «Объём», «Цена». Закупочную цену рассчитываю по формуле:

закупочная цена = ценна * 0,6

Доход

Доход будет рассчитываться как произведение цены и количества. Для создания запроса выбираем следующие поля: из таблицы «Товар» - «Код товара», «Марка», «Тип», «Цвет», «Объём», «Цена»; из таблицы «Состав заказа» - «№ заказа» и «Количество». Доход рассчитываем по формуле:

доход = цена * количество

Зарплата кассиров

Зарплаты кассиров в моей фирме будет равна 10% от дохода. Выбираем следующие поля: из таблицы «Кассиры» - «Код кассира», «ФИО кассира»; из таблицы «Заказы» - «№ заказа» и «Дата»; из таблицы «Товар» - «Код товара», «Марка», «Тип», «Цвет», «Объём»; из запроса «Доход» - «Доход». Зарплату кассиров рассчитываем по формуле:

зарплата = доход * 0,1

Прибыль - это доход минус издержки. А доход - это закупочная цена (60%) плюс надбавка (40%) или по-другому доход * 0,6 + доход * 0,4.

В свою очередь надбавка равна доход - доход * 0,6 или доход * 0,4. Таким образом, прибыль равна:

доход * 0,4 - зарплата.

Для создания данного запроса выбираем из сложного запроса «Зарплата кассиров» следующие поля: «№ заказа», «Дата продажи», «Код товара», «Марка», «Тип», «Цвет», «Объём», «Доход», «Зарплата».

Чистая прибыль

Чистая прибыль фирмы будет равна прибыли минус налог на прибыль. Налог на прибыль в нашей стране равен 24%. Выбираем следующие поля: из запроса «Прибыль» - «Дата», «Код товара», «Марка», «Тип», «Цвет», «Объём», «Прибыль». Чистую прибыль рассчитываю по формуле:

чистая прибыль = прибыль - прибыль * 0,24

Все запросы находятся в Приложении №3.

Отчёты

Отчёт - итоговый документ, выводящий результирующую информацию по БД. Отчёты создаются для вывода информации на печать. Отчёты можно создавать на основании таблиц и запросов. Я создавал отчёты с помощью мастера создания отчётов. В данной БД на основании запросов созданы следующие отчёты:

·      Закупочная цена

·        Доход

·        Зарплата кассиров

·        Прибыль

·        Чистая прибыль

Все отчеты находятся в Приложении №4

Заключение

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

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

Приложение 1

Таблица «Кассиры»

Таблица «Клиенты»

Таблица «Заказы»

Таблица «Состав заказа»

Таблица «Товар»

Приложение 2

Рис. Главная кнопочная форма

Рис. Кнопочная форма отчётов

Рис. Форма «Кассиры»

Рис. Форма «Клиенты»

Рис. Форма «Товар»

Рис. Форма «Заказы»

Приложение 3

Рис. Запрос «Доход»

Рис. Запрос «Закупочная цена»

Рис.

Рис. Запрос «Зарплата кассиров»

Рис.

Рис. Запрос «Прибыль»

Рис. Запрос «Прибыль» (продолжение)

Рис. Запрос «Чистая прибыль»

Рис.