Моделирование базы данных



1 Постановка задачи

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

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

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

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

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

 

2 Моделирование базы данных

2.1 Концептуальное моделирование «сущность-связь»

2.1.1 Сущности

  1. Выплата заработной платы;
  2. Закупка товара;
  3. Отдел-сотрудник;
  4. Отделы;
  5. Продажа;
  6. Поставщики;
  7. Покупатели;
  8. Сотрудники;
  9. Типы торговых точек;
  10. Таблица наценок;
  11. Товары;
  12. Торговые точки;

 

2.1.2 Принципиальная схема ER-модели

Рисунок 2.1.2.1 – Принципиальная схема ER – модели

2.1.3 Детализированная схема ER-модели

 

Рисунок 2.1.3.1 – Детализированная схема ER – модели

 

2.2 Детализация принципиальной схемы ER - модели

Таблица 2.2.1 - Торговые точки

Атрибут

Тип

Ключ

Описание

id маг

Счетчик

К1

Идентификатор торговой точки

название

Текстовый

  

Наименование  торговой точки

id типа

Числовой

  

Идентификатор типа торговой точки

размер торговой  точки

Числовой

  

Размер площади  торговой точки


Таблица 2.2.2 - Типы торговых точек

Атрибут

Тип

Ключ

Описание

id типа

Счетчик

К2

Идентификатор типа торговой точки

тип

Текстовый

  

Наименование  типа торговой точки


Таблица 2.2.3 - Сотрудники

Атрибут

Тип

Ключ

Описание

id сотр

Счетчик

К3

Идентификатор сотрудника

должность

Текстовый

  

Должность сотрудника

Фамилия

Текстовый

  

Инициалы сотрудника


Таблица 2.2.4 - Отделы

Атрибут

Тип

Ключ

Описание

id отдела

Числовой

К5

Идентификатор отдела магазина

id маг

Числовой

К5

Идентификатор магазина

название отдела

Текстовый

  

Наименование  магазина

id

Счетчик

К4

Идентификатор отдела


 

 

Таблица 2.2.5 - Отдел – сотрудник

Атрибут

Тип

Ключ

Описание

id сотр

Числовой

К3

Идентификатор сотрудника

id отд

Числовой

  

Идентификатор отдела, в котором работает сотрудник


Таблица 2.2.6 - Товары

Атрибут

Тип

Ключ

Описание

id товара

Счетчик

К6

Идентификатор товара

название

Текстовый

  

Наименование  товара


Таблица 2.2.7 - Таблица наценок

Атрибут

Тип

Ключ

Описание

id

Числовой

К4

Идентификатор отдела

наценка

Числовой

  

Наценка на товар


Таблица 2.2.8 - Поставщики

Атрибут

Тип

Ключ

Описание

id пост

Счетчик

К7

Идентификатор поставщика

название

Текстовый

  

Наименование  поставщика товаров


Таблица 2.2.9 - Покупатели

Атрибут

Тип

Ключ

Описание

id покупателя

Счетчик

К8

Идентификатор покупателя

кто купил

Текстовый

  

Наименование  покупателя товара


 

 

Таблица 2.2.10 - Закупка товара

Атрибут

Тип

Ключ

Описание

id

Числовой

К4

Идентификатор отдела

Id товара

Числовой

  

Идентификатор товара

количество

Числовой

  

Количество  поступившего товара

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

денежный

  

Цена поступившего товара

Id поставщика

Числовой

  

Идентификатор поставщика


Таблица 2.2.11 - Продажа

Атрибут

Тип

Ключ

Описание

id

Числовой

К4

Идентификатор отдела

id товара

Числовой

  

Идентификатор товара

количество

Числовой

  

Количество  проданного товара

кто купил

Числовой

  

Наименование покупателя товара


Таблица 2.2.12 - Выплата зарплаты

Атрибут

Тип

Ключ

Описание

Код

Счетчик

К9

Код выплаты  зарплаты

id

Числовой

К10

Идентификатор отдела

Id сотрудника

Числовой

К10

Идентификатор сотрудника

зарплата

Денежный

  

Сумма зарплаты за период

период

Дата/время

  

Период, за который  выдана зарплата


 

 

4 Программные средства реализации

Реляционная СУБД:  Sun StarOffice Base

Верия языка SQL:  HSQL, ANSI-SQL

Программное средство представления схемы базы данных: Microsoft Access 2007

5 Создание базы данных

CREATE TABLE "Торговые точки"(

  "id маг" INTEGER IDENTITY NOT NULL,

  "название" VARCHAR(30) NOT NULL,

  "id типа" INTEGER IDENTITY NOT NULL,

  "размер  торговой площади" INTEGER IDENTITY NOT NULL,

  CONSTRAINT "K1" PRIMARY KEY ("id маг"))

 

CREATE TABLE "Типы торговых точек"(

  "id типа" INTEGER IDENTITY NOT NULL,

  "тип" VARCHAR(30) NOT NULL,

  CONSTRAINT "K2" PRIMARY KEY ("id типа"))

 

CREATE TABLE " Сотрудники"(

  " id сотр" INTEGER IDENTITY NOT NULL,

  "должность" VARCHAR(30) NOT NULL,

  "Фамилия" VARCHAR(30) NOT NULL,

  CONSTRAINT "K3" PRIMARY KEY ("id сотр"))

 

CREATE TABLE "Отделы"(

  "id отдела" INTEGER IDENTITY NOT NULL,

  "id маг" INTEGER IDENTITY NOT NULL,

  "название отдела" VARCHAR(30) NOT NULL,

  "id" INTEGER IDENTITY NOT NULL,

  CONSTRAINT "K4" PRIMARY KEY ("id")

CONSTRAINT "K5"  UNIQUE ("id отдела", " id маг"))

 

CREATE TABLE "Отдел-Сотрудник"(

  "id сотр" INTEGER IDENTITY NOT NULL,

  "id отд" INTEGER IDENTITY NOT NULL,

  CONSTRAINT "K3" PRIMARY KEY ("id сотр"))

 

CREATE TABLE "Товары"(

  "id товара" INTEGER IDENTITY NOT NULL,

  "название" VARCHAR(30) NOT NULL,

  CONSTRAINT "K6" PRIMARY KEY ("id товра"))

 

CREATE TABLE "Таблица наценок"(

  "id" INTEGER IDENTITY NOT NULL,

  "наценка" INTEGER IDENTITY NOT NULL,

  CONSTRAINT "K4" PRIMARY KEY ("id"))

 

CREATE TABLE "Поставщики"(

  "id пост" INTEGER IDENTITY NOT NULL,

  "название" VARCHAR(30) NOT NULL,

  CONSTRAINT "K7" PRIMARY KEY ("id пост"))

 

CREATE TABLE "Покупатели"(

  "id покупателя" INTEGER IDENTITY NOT NULL,

  "кто купил" VARCHAR(30) NOT NULL,

  CONSTRAINT "K8" PRIMARY KEY ("id покупателя"))

 

CREATE TABLE "Закупка товара"(

  "id" INTEGER IDENTITY NOT NULL,

  "id товара" INTEGER IDENTITY NOT NULL,

  "количество" INTEGER IDENTITY NOT NULL,

  "закупочная цена" INTEGER IDENTITY NOT NULL,

  "id пост" INTEGER IDENTITY NOT NULL,

  CONSTRAINT "K4" PRIMARY KEY ("id"))

 

CREATE TABLE "Продажа"(

  "id" INTEGER IDENTITY NOT NULL,

  "id товара" INTEGER IDENTITY NOT NULL,

  "количество" INTEGER IDENTITY NOT NULL,

  "кто купил" VARCHAR(30) NOT NULL,

  CONSTRAINT "K4" PRIMARY KEY ("id"))

 

CREATE TABLE "Выплата заработной платы"(

  "kod" INTEGER IDENTITY NOT NULL,

  "id" INTEGER IDENTITY NOT NULL,

  "id сотр" INTEGER IDENTITY NOT NULL,

  "зарплата" INTEGER IDENTITY NOT NULL,

  "период" DATE NOT NULL,

  CONSTRAINT "K9" PRIMARY KEY ("kod")

CONSTRAINT "K10"  UNIQUE ("id", " id сотр"))

 

 

6 Схема созданной базы данных

 

Рисунок 7.1 – Схема базы данных

7 Примеры вставки данных

INSERT INTO "Торговые точки" VALUES (1,'Базис',1)

INSERT INTO "Торговые точки" VALUES (2,'Практик',2)

INSERT INTO "Торговые точки" VALUES (3,'Ландыш',3)

INSERT INTO "Торговые точки" VALUES (4,'Хозтовары',3)

INSERT INTO "Торговые точки" VALUES (5,'Сатурн',2)

INSERT INTO "Торговые точки" VALUES (6,'Хозмастер',2)

 

INSERT INTO "Типы торговых точек" VALUES (1,'Универмаг')

INSERT INTO "Типы торговых точек" VALUES (2,'Магазин')

INSERT INTO "Типы торговых точек" VALUES (3,'Киоск')

 

INSERT INTO "Сотрудники" VALUES (1,’продавец-консультант’,’Крист’)

INSERT INTO "Сотрудники" VALUES (2,’продавец-консультант’,’Черевко’)

INSERT INTO "Сотрудники" VALUES (3,’администратор’,’Шихарев’)

INSERT INTO "Сотрудники" VALUES (4,’продавец-консультант’,’Петерс’)

INSERT INTO "Сотрудники" VALUES (5,’продавец-консультант’,’Тонких’)

INSERT INTO "Сотрудники" VALUES (6,’продавец-консультант’,’Шуршикова’)

INSERT INTO "Сотрудники" VALUES (7,’старший продавец’,’Городецкая’)

INSERT INTO "Сотрудники" VALUES (8,’продавец-консультант’,’Зейда’)

INSERT INTO "Сотрудники" VALUES (9,’заведующая’,’Петрова’)

INSERT INTO "Сотрудники" VALUES (10,’кассир’,’Глазунова’)

INSERT INTO "Сотрудники" VALUES (11,’продавец’,’Ильиых’)

INSERT INTO "Сотрудники" VALUES (12,’продавец’,’Калиниченко’)

INSERT INTO "Сотрудники" VALUES (13,’продавец’,’Элерт’)

INSERT INTO "Сотрудники" VALUES (14,’продавец’,’Ганина’)

INSERT INTO "Сотрудники" VALUES (15,’продавец’,’Дудченко’)

INSERT INTO "Сотрудники" VALUES (16,’продавец’,’Лубнина’)

INSERT INTO "Сотрудники" VALUES (17,’заведующая’,’Дубянская’)

INSERT INTO "Сотрудники" VALUES (18,’продавец’,’Гартман’)

INSERT INTO "Сотрудники" VALUES (19,’продавец’,’Головченко)

INSERT INTO "Отделы" VALUES (1,1,’Бытовая техника’,1)

INSERT INTO "Отделы" VALUES (2,1,’Инструменты’,2)

INSERT INTO "Отделы" VALUES (3,1,’Ковровые изделия’,3)

INSERT INTO "Отделы" VALUES (4,1,’Лакокрасочные материалы’,4)

INSERT INTO "Отделы" VALUES (5,1,’Стройматериалы’,5)

 

INSERT INTO "Отдел-Сотрудник" VALUES (1,4)

INSERT INTO "Отдел-Сотрудник" VALUES (2,2)

INSERT INTO "Отдел-Сотрудник" VALUES (3,3)

INSERT INTO "Отдел-Сотрудник" VALUES (4,1)

INSERT INTO "Отдел-Сотрудник" VALUES (5,5)

INSERT INTO "Отдел-Сотрудник" VALUES (6,1)

INSERT INTO "Отдел-Сотрудник" VALUES (7,2)

INSERT INTO "Отдел-Сотрудник" VALUES (8,3)

INSERT INTO "Отдел-Сотрудник" VALUES (9,4)

INSERT INTO "Отдел-Сотрудник" VALUES (10,5)

 

INSERT INTO "Товары" VALUES (1,’панель пластик’)

INSERT INTO "Товары" VALUES (2,’обои’)

INSERT INTO "Товары" VALUES (3,’фен 145’)

INSERT INTO "Товары" VALUES (4,’дрель 500’)

INSERT INTO "Товары" VALUES (5,’клей момент’)

INSERT INTO "Товары" VALUES (6,’светильник 400/3’)

INSERT INTO "Товары" VALUES (7,’эмали ПФ-115’)

INSERT INTO "Товары" VALUES (8,’холодильник Бирюса’)

INSERT INTO "Товары" VALUES (9,’микроволновка LG-143’)

INSERT INTO "Товары" VALUES (10,’унитаз-Комп Ладога’)

INSERT INTO "Товары" VALUES (11,’фен 126’)

INSERT INTO "Товары" VALUES (12),’микроволновка Samsung G-200’)

 

INSERT INTO "Таблица наценок" VALUES (1,50)

INSERT INTO "Таблица наценок" VALUES (2,50)

INSERT INTO "Таблица наценок" VALUES (3,50)

INSERT INTO "Таблица наценок" VALUES (4,50)

INSERT INTO "Таблица наценок" VALUES (5,50)

INSERT INTO "Таблица наценок" VALUES (6,50)

INSERT INTO "Таблица наценок" VALUES (7,50)

INSERT INTO "Таблица наценок" VALUES (8,30)

INSERT INTO "Таблица наценок" VALUES (9,30)

INSERT INTO "Таблица наценок" VALUES (10,40)

INSERT INTO "Таблица наценок" VALUES (11,40)

INSERT INTO "Таблица наценок" VALUES (12,30)

INSERT INTO "Таблица наценок" VALUES (13,30)

INSERT INTO "Таблица наценок" VALUES (14,40)

INSERT INTO "Таблица наценок" VALUES (15,40)

INSERT INTO "Таблица наценок" VALUES (16,50)

INSERT INTO "Таблица наценок" VALUES (17,50)

INSERT INTO "Таблица наценок" VALUES (18,50)

 

INSERT INTO "Поставщики" VALUES (1,’ИП Ковылин’)

INSERT INTO "Поставщики" VALUES (2,’ИП Красулин’)

INSERT INTO "Поставщики" VALUES (3,’ООО «Роса»’)

INSERT INTO "Поставщики" VALUES (4,’ООО «ТЦХ фармаг»’)

INSERT INTO "Поставщики" VALUES (5,’ИП Афанасьев’)

INSERT INTO "Поставщики" VALUES (6,’ООО «Компания Граф»’)

INSERT INTO "Поставщики" VALUES (7,’ООО «Эль-Трейд»’)

INSERT INTO "Поставщики" VALUES (8,’ООО «Интер-Плюс Алтай»’)

 

INSERT INTO "Покупатели" VALUES (1,’Иванов’)

INSERT INTO "Покупатели" VALUES (2,’Петров’)

INSERT INTO "Покупатели" VALUES (3,’Сидоров’)

INSERT INTO "Покупатели" VALUES (4,’Школа 12’)

INSERT INTO "Покупатели" VALUES (5,’Д/сад №28’)

INSERT INTO "Покупатели" VALUES (6,’ООО «Заря»’)

 

INSERT INTO "Закупка товара" VALUES (1,3,5,300,4)

INSERT INTO "Закупка товара" VALUES (2,3,2,300,4)

INSERT INTO "Закупка товара" VALUES (3,9,40,3000,4)

INSERT INTO "Закупка товара" VALUES (4,7,20,30,1)

INSERT INTO "Закупка товара" VALUES (5,1,50,70,1)

INSERT INTO "Закупка товара" VALUES (8,4,40,2500,6)

INSERT INTO "Закупка товара" VALUES (9,3,6,250,8)

INSERT INTO "Закупка товара" VALUES (10,10,70,1500,4)

INSERT INTO "Закупка товара" VALUES (11,1,40,65,6)

INSERT INTO "Закупка товара" VALUES (12,7,15,29,5)

INSERT INTO "Закупка товара" VALUES (13,1,60,65,6)

 

INSERT INTO "Продажа" VALUES (1,1,3,’Д/сад №28’)

INSERT INTO "Продажа" VALUES (2,1,2,’Иванов’)

INSERT INTO "Продажа" VALUES (2,2,1,’ООО «Заря»’)

INSERT INTO "Продажа" VALUES (4,12,10,’Д/сад №28’)

INSERT INTO "Продажа" VALUES (5,5,20,’Школа 12’)

INSERT INTO "Продажа" VALUES (9,1,1,’ООО «Заря»’)

INSERT INTO "Продажа" VALUES (11,5,20,’Петров’)

INSERT INTO "Продажа" VALUES (12,12,10,’ООО «Заря»’)

INSERT INTO "Продажа" VALUES (13,5,50,’Петров’)

 

INSERT INTO " Выплата заработной платы" VALUES (1,1,1,5050,’01.01.2010’)

INSERT INTO " Выплата заработной платы" VALUES (2,2,1,6000,’01.02.2010’)

INSERT INTO " Выплата заработной платы" VALUES (3,3,2,3800,’01.01.2010’)

INSERT INTO " Выплата заработной платы" VALUES (4,11,12,10100,’05.04.2009’)

INSERT INTO " Выплата заработной платы" VALUES (5,14,13,4800,’05.05.2009’)

INSERT INTO " Выплата заработной платы" VALUES (6,1,3,4800,’01.01.2010’)

INSERT INTO " Выплата заработной платы" VALUES (7,2,3,5200,’01.02.2010’)

INSERT INTO " Выплата заработной платы" VALUES (8,3,3,8000,’01.03.2010’)

INSERT INTO " Выплата заработной платы" VALUES (9,4,4,10000,’01.02.2010’)

INSERT INTO " Выплата заработной платы" VALUES (10,5,4,5000,’01.03.2010’)

 

8 Построение запросов

8.1 Где работают сотрудники

Запрос:

SELECT [Торговые  точки].название, [Типы торговых точек].тип,  Отделы.[название отдела], [Отдел-Сотрудник].[id сотр], Сотрудники.ФИО, Сотрудники.должность

FROM ([Типы торговых  точек] INNER JOIN [Торговые точки] ON [Типы  торговых точек].[id типа] = [Торговые  точки].[id типа]) INNER JOIN (Сотрудники INNER JOIN (Отделы INNER JOIN [Отдел-Сотрудник] ON Отделы.id = [Отдел-Сотрудник].[id отд]) ON Сотрудники.[id сотр] = [Отдел-Сотрудник].[id сотр]) ON [Торговые точки].[id маг] = Отделы.[id маг]

ORDER BY [Торговые  точки].[id маг], [Отдел-Сотрудник].[id сотр];

8.2 Вывод остатков товара по торговым точкам

Запрос:

SELECT [Торговые  точки].название, [Закупка товара].[id товара], [Закупка товара].количество, Продажа.количество, [Закупка товара]!количество-Продажа!количество AS [Остаток товара], [Остаток товара]*Round([Закупка  товара]![закупочная цена]*[Таблица наценок]!наценка) AS [Сумма остатка]

FROM [Торговые  точки] INNER JOIN ((Отделы INNER JOIN [Таблица  наценок] ON Отделы.id = [Таблица наценок].id) INNER JOIN ([Закупка товара] INNER JOIN Продажа  ON [Закупка товара].id = Продажа.[id товара]) ON Отделы.id = [Закупка товара].id) ON [Торговые точки].[id маг] = Отделы.[id маг];

8.3 Сведения о зарплате сотрудника за выбранный период

Запрос:

SELECT [Торговые  точки].название, Сотрудники.ФИО, Сотрудники.должность, [Выплата заработной платы].зарплата, [Выплата заработной платы].период

FROM [Торговые  точки] INNER JOIN ((Сотрудники INNER JOIN [Выплата  заработной платы] ON Сотрудники.[id сотр] = [Выплата заработной платы].[id сотр]) INNER JOIN Отделы ON [Выплата заработной  платы].id = Отделы.id) ON [Торговые точки].[id маг] = Отделы.[id маг]

GROUP BY [Торговые  точки].название, Сотрудники.ФИО, Сотрудники.должность, [Выплата заработной платы].зарплата, [Выплата заработной платы].период

HAVING ((([Выплата  заработной платы].период)=[Введите  период]))

ORDER BY [Выплата заработной платы].зарплата;

8.4 Сведения о продаже товара

Запрос:

SELECT Отделы.id, Продажа.[id товара], Продажа.количество, Round([Закупка  товара]!количество*[Закупка товара]![закупочная  цена]*[Таблица наценок]!наценка) AS [Сумма  продажи], Покупатели.[кто купил]

FROM Покупатели INNER JOIN ((Продажа INNER JOIN Отделы ON Продажа.id = Отделы.id) INNER JOIN [Закупка товара] ON Отделы.id = [Закупка товара].id) ON Покупатели.[id покупателя] = Продажа.[кто купил];

8.5 Сравнение цен на товар в торговых точках

Запрос:

SELECT [Торговые  точки].название, Товары.название, Round([Закупка  товара]![закупочная цена]*[Таблица  наценок]!наценка) AS [Розничная цена], Поставщики.название

FROM [Торговые  точки] INNER JOIN (Товары INNER JOIN (Поставщики INNER JOIN ((Отделы LEFT JOIN [Таблица наценок] ON Отделы.id = [Таблица наценок].id) INNER JOIN [Закупка товара] ON Отделы.id = [Закупка товара].id) ON Поставщики.[id пост] = [Закупка товара].[id пост]) ON Товары.[id товара] = [Закупка товара].[id товара]) ON [Торговые точки].[id маг] = Отделы.[id маг];

 






Моделирование базы данных 📙 Реферат → 🆔 154543

Моделирование базы данных 📙 Реферат → 🆔 154543

Моделирование базы данных