База данных «Оптовые продажи» Концептуальная модель
Определение сущностей Поставщики – продают предприятию товар Поставки - оформление товаров от поставщиков Банк – способствует оплате товаров клиентами и поставщиками Накладная - документ, оформляющийся при поставке от поставщика Товар – то, что предприятие продает Склад уцененных товаров – место хранения уцененных товаров Склад – место хранения товаров Реклама – способствует продаже товара Газета – место размещения рекламы Накладная списания/переоценки/уцененных товаров –документ на списание, переоценку, уценку Уценка/переоценка/списание Филиал – одна из частей предприятия Сотрудник – работник филиала Касса – временное место для хранения денег Розничная продажа Накладная розничной продажи- документ на розничную продажу Клиент – покупатель Договор о продаже – документ с клиентом Заказ – выбор клиента Накладная заказа- документ о выборе клиента Продажа опт Накладная продаж- документ о продажах
Определение типов связей
Определение типов связей
ER-диаграмма Договор о продаже Сотрудники составл яет Клиент об заключа ет Заказ делает Клиент дебитор описывае тся Накладная заказа переносит ся Оплачи в. Продажа опт. Расшифров. прописы вается Банк заноситс я Накладная продаж оплачивает ся Сдает деньги в Касса оформл яет Рознич ная продаж а прописыва ется Наклад ная розн. Товар прописыва ется
ER-диаграмма Физические лица Юридические лица делятс я Сотрудники по закупкам делятс я Договор о покупке Поставщики заключа ет составля ет Уценка работаю т Списания Оформляю т. поставляе т Поставки Филиал Сотрудники Расшифр. Накладная переоценн. Сотрудники по продажам Накладная уцененных товаров Расшифр. Газета Накладная списания прописыв ся расшифро в оплачива ется описывае т Рекла Переоценка Прописывается в Накладная Банк Описыва ется в леж ит прописы вается Товар леж ит Скл уценен товар
База данных «Оптовая торговля» Логическое проектирование
ER-диаграмма логической модели
ER-диаграмма логической модели
ER-диаграмма логической модели
Удаление связей типа 1: 1 Оставляем связь клиент заключает договор о покупке, т. к. клиент по заданию заключает только один договор о покупке.
Бизнес-правила Скидки клиентам: Мелкооптовые - до 10/3% Оптовые - от 10 до 30/5% Крупно-оптовые от 30/10%
ГЕНЕРАЛИЗАЦИЯ И СПЕЦИАЛИЗАЦИЯ Поставщики делятся на физические и юридические, идем по пути т. к. если мы пойдем по пути то в отношении будет много полей заполненные NULL. Клиенты делятся на простых и дебиторов, пошли по пути ^ т. к. информация в 2 -х отношениях идентична.
Определение атрибутов сущностей и связей Оптовые продажи Название поля Тип Null Ключ Код продажи int - + Дата datetime - Цена int - Код клиента int - Дата фактическая datetime - Дата должен. datetime - Склад int -
Определение атрибутов сущностей и связей Розничные продажи Название поля Тип Null Ключ Код продажи int - + Дата datetime - Цена int - Склад int - Реклама Название поля Тип Null Ключ Код товара int - + Дата datetime - + Цена int - Код газеты int - +
Определение атрибутов сущностей и связей Склад Название поля Тип Null Ключ Код товара int - + Количество int - Цена int - Склад уцененных товаров Название поля Тип Null Ключ Код товара int - + Количество int - Цена int -
Определение атрибутов сущностей и связей Сотрудник Название поля Тип Null Ключ Код сотрудника int - + ФИО Char(30) - Адрес Char(30) - Телефон Char(11) - Дата вступления datetime - Код филиала int -
Определение атрибутов сущностей и связей Списание Название поля Тип Null Ключ Код списания int - + Дата datetime - Код сотрудника int - Цена int - Склад int -
Определение атрибутов сущностей и связей Товар Название поля Тип Null Ключ Код товара int - + Название Char(30) - Единицы Char(10) - Фирма Char(30) - Страна Char(30) - Срок годности int - Розничная наценка int - Мелк. int - Опт. int - Крупноопт. int - Код категории int -
Определение атрибутов сущностей и связей Уценка Название поля Тип Null Ключ Код уценки int - + Дата datetime - Примечание Char(50) - Код сотрудника int - Цена int - Заказ Название поля Тип Null Ключ Код заказа int - + Дата datetime - Цена int - Код клиента int - Склад Int -
Определение атрибутов сущностей и связей Банк Атрибут Тип NULL Ключ Код операции int - + б/р мой int + б/р поставщика int + Сумма int - Дата datetime - Договор о продаже Атрибут Тип NULL Ключ Код договора int - + отсрочка int + штраф int + Код поставщика int - Код сотрудника int - Дата datetime -
Определение атрибутов сущностей и связей Договор о покупке Атрибут Тип NULL Ключ Код договора int - + отсрочка int + штраф int + Код клиента int - Код сотрудника int - Дата datetime - Тип скидки Char(10) -
Определение атрибутов сущностей и связей Филиал Атрибут Тип NULL Ключ Код филиала int - + Адрес Char(30) - Телефон Char(11) - Факс Char(11) + Б/р Char(10) - Газета Атрибут Тип NULL Ключ Код газеты int - + Название Char(30) - Адрес Char(30) - Телефон Char(11) - Факс Char(11) + Имя контакта Char(30) -
Определение атрибутов сущностей и связей Касса Атрибут Тип NULL Ключ Чек int - + Сумма int - Дата datetime - Категория Атрибут Тип NULL Ключ Код категории int - + Нaзвание Char(30) -
Определение атрибутов сущностей и связей Клиент Название поля Тип Null Ключ Код клиента int - + ФИО Char(30) - Адрес Char(30) - Телефон Char(11) - Факс Char(11) - Банк реквk int - Отсрочка Int -
Определение атрибутов сущностей и связей Накладная Описание Тип NULL Ключ Код товара Int - + Код поставки Int - + Количество Int - Цена Int - Дата производства Datetime - Накладная переоценки Описание Тип NULL Ключ Код товара Int - + Код переоценки Int - + Количество Int - Цена Int -
Определение атрибутов сущностей и связей Накладная продаж Описание Тип NULL Ключ Код товара Int - + Код продаж Int - + Количество Int - Цена Int - Накладная розничная Описание Тип NULL Ключ Код товара Int - + Код продажи Int - + Количество Int - Цена Int -
Определение атрибутов сущностей и связей Накладная списания Описание Тип NULL Ключ Код товара Int - + Код списания Int - + Количество Int - Цена Int - Накладная уценки Описание Тип NULL Ключ Код товара Int - + Код уценки Int - + Количество Int - Цена Int -
Определение атрибутов сущностей и связей Накладная заказа Описание Тип NULL Ключ Код товара Int - + Код заказа Int - + Количество Int - Цена Int - Переоценка Описание Тип NULL Ключ Код переоценки Int - + Дата переоценки Datetime - Примечания Char(50) - Код сотрудника Int -
Определение атрибутов сущностей и связей Поставки Описание Тип NULL Ключ Код поставки Int - + Дата поставки Datetime - Цена int - Дата фактич. Datetime - ГТД Char(10) + Код поставки Int - Дата должен. Datetime -
Определение атрибутов сущностей и связей Поставщики физические Описание Тип NULL Ключ Код поставщика Int - + ФИО Char(30) - Адрес Char(30) - Телефон Char(11) - Банк. Реквизит Char(0) - Отсрочка Int -
Определение атрибутов сущностей и связей Поставщики юридические Описание Тип NULL Ключ Код поставщика Int - + Название комп. Char(30) - Тип Char(20) - ФИО Char(30) - Адрес Char(30) - Телефон Char(11) - Банк. Реквизит Char(10) - Отсрочка int -
Определение атрибутов сущностей и связей Заказ у поставщика Название поля Тип Null Ключ Код заказа int - + Дата datetime - Сумма int - Код поставщика int - Дата производства datetime -
Определение атрибутов сущностей и связей Накладная заказа у поставщика Описание Тип NULL Ключ Код товара Int - + Код заказа Int - + Количество Int - Цена Int -
База данных «Оптовая торговля» Физическое проектирование
ER-Диаграмма
Контролируемая избыточность В таблицу «клиент» добавили атрибут «otsrochka» , который является избыточной информацией, это позволит в разы увеличить скорость выполнения транзакций. В таблицу «Поставщики» добавили атрибут «otsrochka» , который является избыточной информацией, это позволит в разы увеличить скорость выполнения транзакций.
Триггеры и процедуры Триггер на вставку в накладную поставки(Обновляет информацию на складе и в поставке обновляет сумму поставки): ALTER trigger naklad_ins on dbo. nakladnaya after insert as if @@Rowcount=0 return declare @kol int, @cena dec(7, 2), @kod_t int, @kod_p int declare @nak_insert cursor set @nak_insert=cursor static for select kod_p, kod_t, kol, cena from inserted open @nak_insert fetch first from @nak_insert into @kod_p, @kod_t, @kol, @cena while @@fetch_status=0 begin update sklad set kol=kol+@kol, cena=cena+(@kol*@cena) where kod_t=@kod_t update postavki set cena=cena+(@kol*@cena) where kod_p=@kod_p fetch next from @nak_insert into @kod_p, @kod_t, @kol, @cena end
Триггеры и процедуры Триггер на вставку в накладную продажи оптовой(Обновляет информацию на складе и в продаже оптовой обновляет сумму продажи): ALTER trigger naklad_prod_insert on dbo. nakladnaya_prod after insert as if @@Rowcount=0 return declare @kol int, @cena dec(7, 2), @kod_t int, @kod_prod int, @skl int declare @nak_insert cursor set @nak_insert=cursor static for select kod_prod, kod_t, kol, cena from inserted open @nak_insert fetch first from @nak_insert into @kod_prod, @kod_t, @kol, @cena while @@fetch_status=0 begin select @skl=skl from inserted i join prod_opt p on i. kod_prod=p. kod_prod if (@skl=0) begin update sklad set kol=kol-@kol, cena=cena-(@kol*@cena) where kod_t=@kod_t update prod_opt set cena=cena+(@kol*@cena) where kod_prod=@kod_prod end if (@skl=1) begin update sklad_yc set kol=kol-@kol, cena=cena-(@kol*@cena) where kod_t=@kod_t update prod_opt set cena=cena+(@kol*@cena) where kod_prod=@kod_prod end fetch next from @nak_insert into @kod_prod, @kod_t, @kol, @cena end
Триггеры и процедуры Триггер на вставку в накладную уценки (Обновляет информацию на складе и в уценке обновляет сумму уценки): ALTER trigger naklad_yc_ins on dbo. nakladnaya_yc after insert as if @@Rowcount=0 return declare @kol int, @cena dec(7, 2), @kod_t int, @kod_yc int declare @nak_insert cursor set @nak_insert=cursor static for select kod_yc, kod_t, kol, cena from inserted open @nak_insert fetch first from @nak_insert into @kod_yc, @kod_t, @kol, @cena while @@fetch_status=0 begin update sklad set kol=kol-@kol, cena=cena-(@kol*@cena) where kod_t=@kod_t update sklad_yc set kol=kol+@kol, cena=cena+(@kol*@cena) where kod_t=@kod_t update ycenka set cena=cena+(@kol*@cena) where kod_yc=@kod_yc fetch next from @nak_insert into @kod_yc, @kod_t, @kol, @cena end
Триггеры и процедуры Триггер на вставку в накладную переоценки (Обновляет информацию на складе и в переоценке обновляет сумму переоценки): ALTER trigger naklad_pc_insert on dbo. nakladnaya_pc after insert as if @@Rowcount=0 return declare @kol int, @cena dec(7, 2), @kod_t int, @kod_pc int, @skl_cena int declare @nak_insert cursor set @nak_insert=cursor static for select kod_pc, kod_t, kol, cena from inserted open @nak_insert fetch first from @nak_insert into @kod_pc, @kod_t, @kol, @cena while @@fetch_status=0 begin select @skl_cena=p. cena/p. kol from inserted i join sklad p on i. kod_t=p. kod_t update sklad set cena=cena-(@kol*@skl_cena) where kod_t=@kod_t update sklad set cena=cena+(@kol*@cena) where kod_t=@kod_t fetch next from @nak_insert into @kod_pc, @kod_t, @kol, @cena end
Триггеры и процедуры Триггер на вставку в накладную розничной продажи (Обновляет информацию на складе, в розничной продаже обновляет сумму продажи и «заносит» деньги в кассу): ALTER trigger naklad_rozn_ins on dbo. nakladnaya_rozn after insert as if @@Rowcount=0 return declare @kol int, @cena dec(7, 2), @kod_t int, @kod_prod int, @skl int, @roz int declare @nak_insert cursor set @nak_insert=cursor static for select kod_prod, kod_t, kol, cena from inserted open @nak_insert fetch first from @nak_insert into @kod_prod, @kod_t, @kol, @cena while @@fetch_status=0 begin select @skl=skl from inserted i join prod_rozn p on i. kod_prod=p. kod_prod select @roz=rozn_nac from inserted i join tovar p on i. kod_t=p. kod_t if (@skl=0) begin update sklad set kol=kol-@kol, cena=cena-(@kol*@cena) where kod_t=@kod_t update prod_rozn set cena=cena+(@kol*@cena)*@roz/100 where kod_prod=@kod_prod update kassa set money 2=money 2+(@kol*@cena)*@roz/100 end if (@skl=1) begin update sklad_yc set kol=kol-@kol, cena=cena-(@kol*@cena) where kod_t=@kod_t update prod_rozn set cena=cena+(@kol*@cena)*@roz/100 where kod_prod=@kod_prod update kassa set money 2=money 2+(@kol*@cena)*@roz/100 end fetch next from @nak_insert into @kod_prod, @kod_t, @kol, @cena end
Триггеры и процедуры Процедура пересылки заказа клиента в реальную накладную продажи alter proc zakaz_prod @kod_kl int, @skl int, @kod_zak int as declare @kod_prod int, @otsr int, @kol int, @cena int, @kod_t int select @otsr=otsrochka from klient where kod_kl=@kod_kl select @kod_prod=max(kod_prod) from prod_opt set @kod_prod=@kod_prod+1 insert into prod_opt (kod_prod, data, cena, kod_kl, data_fact, data_doljen, skl) values (@kod_prod, getdate(), 0, @kod_kl, NULL, DATEADD(day, +@otsr, getdate()), @skl) ------------------declare @nak_insert cursor set @nak_insert=cursor static for select kod_zak, kod_t, kol, cena from nakladnaya_zak where kod_zak=@kod_zak open @nak_insert fetch first from @nak_insert into @kod_zak, @kod_t, @kol, @cena while @@fetch_status=0 begin insert into nakladnaya_prod (kod_t, kol, cena, kod_prod) values (@kod_t, @kol, @cena, @kod_prod) fetch next from @nak_insert into @kod_zak, @kod_t, @kol, @cena end
Скачать презентацию База данных Оптовые продажи Концептуальная модель Определение
OPT.ppt