Разработка базы данных для управления заказами торговой организации

Оглавление

1. Техническое задание 3

2. Описание СУБД 4

3. Инфологическая модель 5

3. 1 Описание предметной области 5

3.2 Инфологическая схема объектов и связей 5

3.3 Ограничения предметной области 5

3.4 Основные операции в данном фрагменте предметной области 6

4. Концептуальная модель 7

Ограничения предметной области в концептуальной модели 7

Триггеры 7

5. Внешняя модель 9

6. Физическая модель 12

Список использованной литературы 13

Приложение 1 14

Приложение 2 19

 

1. Техническое задание

Разработать базу данных для управления заказами торговой организации. База должна отражать информацию:

• о товарах и производителях;
• о заказчиках;
• о менеджерах;
• о поступивших заказах.

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

 

2. Описание СУБД

В курсовом проекте используется комплект программ Denwer версии 3. Комплект состоит из следующих компонентов:

1) веб-сервер Apache 2

2) PHP5

3) СУБД MySQL 5

Основные преимущества MySQL:

1) многопоточность, поддержка нескольких одновременных запросов;

2) оптимизация связей с присоединением многих данных за один проход;

3) записи фиксированной и переменной длины;

4) ODBC драйвер;

5) гибкая система привилегий и паролей;

6) гибкая поддержка форматов чисел, строк переменной длины и меток времени;

7) интерфейс с языками C и Perl, PHP;

8) быстрая работа, масштабируемость;

9) совместимость с ANSI SQL;

10) бесплатна в большинстве случаев;

11) хорошая поддержка со стороны провайдеров услуг хостинга;

12) быстрая поддержка транзакций через механизм InnoDB.

Для разработки интерфейса был выбран входящий в Denwer язык PHP.

 

3. Инфологическая модель

3.1 Описание  предметной области

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

 

3.2 Инфологическая  схема объектов и связей

Для создания инфологической модели использовалась программа ErWin Data modeler. 

Рисунок 1. Схема  инфологической модели

 

3.3 Ограничения предметной области

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

Менеджеры (manager).В БД заносятся Ф.И.О., пол и дата рождения каждого менеджера организации. Нельзя удалить менеджера, если он связан, хотя бы с одним заказом.

Заказчики (buyer).В БД заносятся наименование фирмы-заказчика, ИНН, номер телефона и адрес. Нельзя удалить заказчика, если он связан, хотя бы с одним заказом.

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

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

Позиции накладной (items). Количество и отпускная цена должны быть положительными целыми числами.

4. Основные операции в данной предметной области
1. Работа с производителями

- добавление

- редактирование

- удаление

2. Работа с товарами

- добавление 

- редактирование

- удаление

3. Работа с менеджерами

- добавление

- редактирование

- удаление

4. Работа с заказчиками

- добавление 

- редактирование

- удаление

5. Работа с заказами

- добавление

- редактирование

- удаление

- заполнение  и редактирование накладной

 

4. Концептуальная  модель

Ограничения предметной области в концептуальной модели

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

Таблица «Производители» (vendor):

id – идентификатор производителя, формируется системой автоматически (autoincrement).

Таблица «Товары» (product):

id – идентификатор товара (autoincrement).

id_vendor – идентификатор производителя (внешний ключ).

sku – уникальный артикул товара.

sold – количество заказанных (формируется триггерами).

Таблица «Заказчики» (buyer):

id – идентификатор заказчика (autoincrement).

Таблица «Менеджеры» (manager):

id – идентификатор менеджера (autoincrement).

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

id – идентификатор заказа (autoincrement).

id_buyer– идентификатор заказчика(внешний ключ).

id_manager– идентификатор менеджера(внешний ключ).

invnum – уникальный номер заказа.

Таблица «Позиции накладной» (items):

id – идентификатор позиции (autoincrement).

id_invoice – идентификатор заказа(внешний ключ).

id_product – идентификатор товара(внешний ключ).

Рис. 2 Схема  концептуальной модели

 

Триггеры

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

Триггер «items.product_sale» – увеличивает количество проданных товаров в product при добавлении позиции с этим товаром в накладную (см. приложение 2).

Триггер «items.cancel_item» – уменьшает количество проданных товаров в product при удалении позиции с этим товаром из накладной (см. приложение 2).

 

5. Внешняя модель

Интерфейс создан с использованием HTMLи PHP.

Требования к системе:

1. Ввод информации о производителях, товарах, заказчиках, менеджерах, заказах
2. Изменение введенной информации
3. Редактирование накладных (добавление и удаление позиций)
4. Удаление производителей, товаров, заказчиков, менеджеров, заказов.

Основные страницы:

• Главная страница – список заказов(index.php)
• Заказы (index.php?action=invoices)
• Производители(index.php?action=vendors)
• Товары (index.php?action=products)
• Покупатели (index.php?action=buyers)
• Менеджеры (index.php?action=managers)
• Общийинтерфейсвынесенвфайлыheader.php (заголовок и меню) иfooter.php (подвал), подключениекбазе - db.php.

 

Главная страница – index.php. Синоним – index.php?action=invoices 
Выдает из базы информацию о заказах.

 

Рис. 3 Главная страница

Возможные действия: добавить заказ (форма в нижней строке таблицы), редактировать заказ, удалить заказ. 
Редактирование заказа:

Рис. 4 Страница редактирования заказа

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

 

Список товаров – index.php?action==products 
Выдает из базы информацию о товарах.

Рис. 5 Страница товаров

Возможные действия: добавить товар (форма в нижней строке таблицы), редактировать товар, удалить товар.

Редактирование товара:

Рис. 6 Страница редактирования товара

Возможные действия: изменить информацию о заказе.

Для сущностей Производители, Заказчики и Менеджеры интерфейс ни чем принципиально не отличается. Логика работы с ними такая же, как и с Товарами.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

6. Физическая модель

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

Основные  особенности:

- В отличие  от MyISAM, таблицы для InnoDBне создаются в отдельных файлах. Все таблицы хранятся в едином табличном пространстве.

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

- Данный тип  таблиц поддерживает автоматическое  восстановление после сбоя.

- Обеспечивается  поддержка транзакций.

- Это единственный  тип таблиц MySQL, поддерживающий внешние ключи и каскадное удаление.

- Выполнение блокировок на уровне отдельных записей.

- Таблицы  InnoDB надежнее MyISAM, но заметно уступают в скорости работы и не поддерживают полнотекстовый поиск.

 

Список использованной литературы

1. Базы  данных : инфологические модели баз  данных: учеб.пособие/ М. В. Копейкин, В. В. Спиридонов, Е. О. Шумова. - СПб.: Изд-во СЗТУ, 2004. - 189 с.
2. Базы данных: учебно-методический комплекс /сост.: М.В. Копейкин,  В.В. Спиридонов, Е.О. Шумова. - СПб.: Изд-во СЗТУ, 2010. – 175 с.
3. Копейкин М.В., Спиридонов В.В., Шумова Е.О. Базы данных: учебное пособие: в 2 кн. Кн. 1. – СПб.: Изд-во СЗТУ, 2010. – 247 с.
4. Копейкин М.В., Спиридонов В.В., Шумова Е.О. Базы данных: учебное пособие: в 2 кн. Кн. 2. – СПб.: Изд-во СЗТУ, 2010. – 219 c.
5. Кузнецов М. В., Симдянов И.В. MySql 5 – СПб: БХВ-Петербург, 2010 – 1024с. 

 

Приложение 1

Описание таблиц и ограничений  базы данных

-- phpMyAdmin SQL Dump

-- version 3.5.1

-- http://www.phpmyadmin.net

--

-- Хост: 127.0.0.1

-- Время создания: Фев 12 2013 г., 04:19

-- Версия сервера: 5.5.25

-- Версия PHP: 5.3.13

 

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

SET time_zone = "+00:00";

 

--

-- База данных: `shop`

--

 

-- --------------------------------------------------------

 

--

-- Структура таблицы `buyer`

--

 

CREATE TABLE `buyer` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `name` varchar(255) NOT NULL,

  `inn` varchar(20) NOT NULL,

  `phone` varchar(20) NOT NULL,

  `address` varchar(255) NOT NULL,

  PRIMARY KEY (`id`),

  KEY `name` (`name`),

  KEY `inn` (`inn`),

  KEY `address` (`address`)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;

 

--

-- Дамп данных таблицы `buyer`

--

 

INSERT INTO `buyer` (`id`, `name`, `inn`, `phone`, `address`) VALUES

(1, 'ООО "Рога и Копыта"', '781233902933', '+7(812)111-2233', 'СПб, ул. Партизана  Германа, 38'),

(2, 'ОАО Газмяс', '771198334455', '+7(495)223-3322', 'Москва, Тверская ул., 25'),

(3, 'ОАО "ХайФайв"', '786546793245', '+7(812)355-5676', 'СПб, ул. Миллионная,д.7, кв.56'),

(4, 'ООО "Звезда"', '674245974325', '+7(965)675-6545', 'СПБ, ул. Дворцовая, д.5, кв. 87'),

(5, 'ЗАО "Тандер"', '786345234986', '+7(865)654-7895', 'Москва, ул. Ленина, д.86, кв. 93'),

(6, 'ОАО "Победа"', '675345096783', '+7(812)647-7867', 'СПб, ул. Карла Маркса, д.32, кв. 67'),

(7, 'ОАО "БКО"', '531254657891', '+7(81664)97-700', '');

 

-- --------------------------------------------------------

 

--

-- Структура таблицы `invoice`

--

 

CREATE TABLE `invoice` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `id_buyer` int(10) unsigned NOT NULL,

  `id_manager` int(10) unsigned NOT NULL,

  `invtime` datetime NOT NULL,

  `invnum` varchar(45) NOT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `invnum` (`invnum`),

  KEY `id_buyer` (`id_buyer`),

  KEY `id_manager` (`id_manager`),

  KEY `invtime` (`invtime`)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ;

 

--

-- Дамп данных таблицы `invoice`

--

 

INSERT INTO `invoice` (`id`, `id_buyer`, `id_manager`, `invtime`, `invnum`) VALUES

(2, 3, 3, '2013-01-23 01:43:02', '23-9332'),

(3, 4, 6, '2013-01-15 02:43:31', '64-2485'),

(5, 6, 5, '2012-02-08 14:11:43', '46-2992'),

(6, 5, 6, '2012-08-30 17:13:29', '94-0445'),

(7, 3, 8, '2013-02-09 22:26:00', '55-1122'),

(8, 3, 2, '2013-02-09 22:27:00', '55-1123');

 

-- --------------------------------------------------------

 

--

-- Структура таблицы `items`

--

 

CREATE TABLE `items` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `id_invoice` int(10) unsigned NOT NULL,

  `id_product` int(10) unsigned NOT NULL,

  `qty` smallint(6) NOT NULL DEFAULT '1',

  `price` float NOT NULL,

  PRIMARY KEY (`id`),

  KEY `id_invoice` (`id_invoice`),

  KEY `id_product` (`id_product`),

  KEY `price` (`price`)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=19 ;

 

--

-- Дамп данных таблицы `items`

--

 

INSERT INTO `items` (`id`, `id_invoice`, `id_product`, `qty`, `price`) VALUES

(1, 8, 1, 3, 70000),

(3, 2, 2, 5, 3700),

(4, 3, 5, 1, 73000),

(5, 5, 1, 1, 75000),

(7, 8, 4, 1, 28000),

(8, 3, 2, 5, 3600),

(10, 7, 2, 3, 3600),

(12, 2, 4, 3, 28000),

(13, 2, 6, 4, 4000),

(14, 2, 1, 1, 1),

(17, 8, 3, 3, 20000),

(18, 6, 8, 3, 300000);

 

--

-- Триггеры `items`

--

DROP TRIGGER IF EXISTS `cancel_item`;

DELIMITER //

CREATE TRIGGER `cancel_item` BEFORE DELETE ON `items`

FOR EACH ROW UPDATE product SET sold = sold - OLD.qty WHERE id = OLD.id_product

//

DELIMITER ;

DROP TRIGGER IF EXISTS `product_sale`;

DELIMITER //

CREATE TRIGGER `product_sale` BEFORE INSERT ON `items`

FOR EACH ROW UPDATE product SET sold = sold + NEW.qty WHERE id = NEW.id_product

//

DELIMITER ;

 

-- --------------------------------------------------------

 

--

-- Структура таблицы `manager`

--

 

CREATE TABLE `manager` (

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `name` varchar(255) NOT NULL,

  `birthday` date NOT NULL,

  `sex` enum('м','ж') NOT NULL DEFAULT 'м',

  PRIMARY KEY (`id`),

  KEY `name` (`name`),

  KEY `birthday` (`birthday`),

  KEY `sex` (`sex`)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ;

 

--

-- Дамп данных таблицы `manager`

--

 

INSERT INTO `manager` (`id`, `name`, `birthday`, `sex`) VALUES

(1, 'Василий Пупкин', '1984-08-14', 'м'),

(2, 'Елена Прекрасная', '1989-01-15', 'ж'),

(3, 'Геннадий Букин', '1976-05-19', 'м'),

(4, 'Людмила Смирнова', '1983-10-23', 'ж'),

(5, 'Петр Карпов', '1975-02-12', 'м'),

(6, 'Светлана Бадыва ', '1979-10-18', 'ж'),