Разработка базы данных "Склад"

   Содержание

 

1. Вступление.

   В ходе выполнения курсового проекта  была разработана база данных «СКЛАД». В базе данных учтены данные об сущностях, которые описывают иерархию товара, покупателя (продавца), приходных (расходных) накладных, место на складе. В основе создания базы данных лежит задание, полученное на кафедре «Информатика и интеллектуальная собственность».

 

2. Индивидуальное задание.

      Задание для курсового проекта:

1. Построить ER-диаграмму для предметной области «СКЛАД.
2. Конвертировать ER-диаграмму в концептуальную схему, отображаемую реляционные таблицы, и нормализовать таблицы к форме не ниже Нормальной Формы Бойса-Кодда.
3. Составить SQL-скрипты для создания таблиц базы данных и включить в них все, оговоренные логикой предметной области, декларативные ограничения целостности (первичные и внешние ключи, проверочные ограничения и т.д.)
4. SQL-скрипты должны быть разработаны для СУБД DB2.
5. Разработать необходимые ограничения целостности для построенной базы данных.

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

3. Построение инфологической  модели.

          Склад имеет следующую структуру. Покупатель(Поставщик) товара заключает со складом договор о покупке(продаже) некоторого товара. По договору формируется расходная(приходная) накладная в ней прописывается дата её оформления и код договора, потом формируется расходная(приходная) накладная на товар в которой прописывается код товара и его количество, а после этого в таблицах Изъятие(Размещение) товара со(на) склада(е) проверяется есть ли данного товара столько сколько в накладной (проверяется есть ли место на складе для размещения товара).

      В результате получим следующую ER-диаграмму.

   Рис. 1 ER-диаграмма. 
4. Построение концептуальной модели базы данных.

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

   В результате имеем 15 таблиц. Определим для них первичные ключи.

   Практически все таблицы за исключением Bye,Spendon, Maxonplace, Saleones, Incash, Incashon имеют первичные ключи, имя которых было образовано путем добавления к имени сущности выражения “ID”. Ключи имеют тип INTEGER ввиду того, что объем данных, которые могут содержаться в них, будет очень велик.

   В результате имеем следующую концептуальную модель:

   

   Рис.2 Концептуальная модель.

5. Построение физической модели и создание базы данных.

   В разрабатываемой базе данных содержится 15 таблиц. Приведем описание каждой таблицы базы данных. Скрипты создания базы данных содержатся в приложении.

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

   6. Создание процедурных  ограничений целостности.

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

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

   Назначим код ошибки 75000 и текст сообщения 'Изъятого товара не может быть больше чем на складе'.

   Текст на создание триггера находится в приложении.

 

    7. Создание типичных  запросов к базе  данных.

   7.1. Примеры операторов, которые записывают  данные в таблицы.

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

   Таблица Сustomer:

insert into db2admin.customer values(1, 'Василий Иванович Кабысдохов', 'Харьков,Ленина 14,кв.23', 5678934);

insert into db2admin.customer values(2, 'Кабанов Михаил Владиславович', 'Донецк,Ленина 14,кв.3', 9878934);

insert into db2admin.customer values(3, 'Иванов Иван Иванович', 'Киев,Богд. Хмель. 54,кв.56', 8008934);

   Таблица Сustomer_order:

insert into db2admin.customer_order values(1,3, '11.12.2008', '12.12.2008', 'yes');

insert into db2admin.customer_order values(2,2, '19.01.2009', '21.01.2009', 'no');

insert into db2admin.customer_order values(3,1, '21.01.2009', '23.01.2009', 'yes');

insert into db2admin.customer_order values(4,2, '24.01.2009', '26.01.2009', 'no');

insert into db2admin.customer_order values(5,1, '04.01.2009', '06.01.2009', 'yes');

   Таблица Product:

insert into db2admin.product values(1, 'Колбаса', 20000, 'качалка', 'вес = 500 гр. длинна 35 см, диаметр = 5 см');

insert into db2admin.product values(2,'Пшеница',15,'нет','тонн');

insert into db2admin.product values(3,'Консервы',2000,'Жестяная банка','вес  1 банки =325 гр. Килька в томате');

insert into db2admin.product values(4,'Автомобиль',4,'нет','Lada Kalina, color-orange, motor-1,8L, complect standart');

insert into db2admin.product values(5,'Телефон',500,'коробка','Nokia N98');

   Таблица Bye:

insert into db2admin.bye values(5,5);

insert into db2admin.bye values(3,4);

insert into db2admin.bye values(1,4);

insert into db2admin.bye values(2,3);

insert into db2admin.bye values(1,5);

insert into db2admin.bye values(2,2);

   Таблица Salesman:

insert into db2admin.salesman values(1, 'Королёв Игорь Владимирович', 'Харьков,Сумская 143,кв.45', 5548924);

insert into db2admin.salesman values(2, 'Горюшкин Эрнест Вадимович', 'Львов,Киевская 53,кв.25', 3448924);

insert into db2admin.salesman values(3, 'Шевченко Вячеслав Павлович', 'Харьков,Блюхера 32,кв.17', 548924);

   Таблица Spend:

insert into db2admin.spend values(1, 2, '25.01.2009');

insert into db2admin.spend values(2, 1, '20.12.2008');

insert into db2admin.spend values(3, 5, '08.01.2009');

insert into db2admin.spend values(4, 4, '28.01.2009');

insert into db2admin.spend values(5, 3, '22.01.2009');

   Таблица Place:

insert into db2admin.place values(1,1,45.2,15000);

insert into db2admin.place values(2,2,65.5,15);

insert into db2admin.place values(3,3,99.9,2000);

insert into db2admin.place values(4,1,45.2,5000);

insert into db2admin.place values(5,4,65.5,4);

insert into db2admin.place values(6,5,45.2,500);

   Таблица Spendon:

insert into db2admin.spendon values(1,3,1000);

insert into db2admin.spendon values(2,5,100);

insert into db2admin.spendon values(3,2,5);

insert into db2admin.spendon values(2,4,1);

   Таблица Maxonplace:

insert into db2admin.maxonplace values(1,1,1000);

insert into db2admin.maxonplace values(1,2,5000);

insert into db2admin.maxonplace values(2,3,5);

insert into db2admin.maxonplace values(3,2,1000);

insert into db2admin.maxonplace values(4,3,1);

insert into db2admin.maxonplace values(5,1,500);

   Таблица Take:

insert into db2admin.take values(1,2,1);

insert into db2admin.take values(2,1,2);

   Таблица Sale_order:

insert into db2admin.sale_order values(1,1, '31.12.2008', '02.01.2009', 'yes');

insert into db2admin.sale_order values(2,2, '31.01.2009', '02.02.2009', 'yes');

insert into db2admin.sale_order values(3,3, '21.01.2009', '22.02.2009', 'yes');

insert into db2admin.sale_order values(4,4, '21.02.2009', '22.02.2009', 'yes');

   Таблица Saleones:

insert into db2admin.saleones values(1,1,300);

insert into db2admin.saleones values(2,2,10);

insert into db2admin.saleones values(3,3,1000);

insert into db2admin.saleones values(4,4,10);

   Таблица Incash:

insert into db2admin.incash values(3, 3, '4.02.2009');

insert into db2admin.incash values(2, 2, '9.03.2009');

   Таблица Incashon:

insert into db2admin.incashon values(1, 3, 500);

insert into db2admin.incashon values(3, 2, 3);

   Таблица Placing_incash:

insert into db2admin.placing_incash values(2,3,3,1);

insert into db2admin.placing_incash values(3,2,3,5); 

      7.2. Примеры операторов на выборку данных из базы данных.

   1.Посчитать сумму всех товаров на местах.

select sum(quantity), product_id

from db2admin.place

group by product_id

   Результат выборки:

2. Список товаров и их количество, которое подлежат поставке на заданную дату 4.02.2009.

select product_name,saleones.quantity

from product,saleones,incash

where product.product_id=saleones.product_id and date='4.02.2009'

Результат выборки:

3. Список товаров и их количество, которое проданы на заданную дату 20.12.2008.

select product_name,spendon.quantity

from product,spendon,spend

where product.product_id=spendon.product_id

and date='20.12.2008'

Результат выборки:

4. Список договоров по покупке, которые не выполнены на заданную дату 19.01.2009.

select cusorder_id,r.customer_id,name

from customer_order c,customer r

where c.customer_id=r.customer_id

and success='no'

and date_1='19.01.2009'

Результат выборки:

5. Список договоров по продаже, которые выполнены на заданную дату 31.12.2008.

select saleorder_id,s.salesman_id,s.name

from salesman s,sale_order o

where s.salesman_id=o.salesman_id

and success='yes'

and date_1='31.12.2008'

Результат выборки:

6. Список мест на складе на которые можно разместить товар заданного типа.

select p.place_id from place p,maxonplace,product pr 

where pr.product_id=p.product_id

and max>p.quantity

and product_name='Пшеница'

Результат выборки:

 
 
 

      7.3. Проверка ограничений целостности.

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

update spendon set quantity=2001 where product_id=3

------------------------------ Введенные команды ----------------------------

update spendon set quantity=2001 where product_id=3;

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

update spendon set quantity=2001 where product_id=3

DB21034E  Данная команда обрабатывалась как оператор SQL, поскольку она не

является  допустимой командой процессора командной  строки.  При обработке SQL

было получено сообщение:

SQL0438N  Программа генерирует ошибку с текстом диагностики: "Изъятого товара не может быть больше чем на складе". 

SQLSTATE=75000 

insert into spendon values(5,3,2001); 

------------------------------ Введенные команды ----------------------------

insert into spendon values(5,3,2001);

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

insert into spendon values(5,3,2001)

DB21034E  Данная команда обрабатывалась как оператор SQL, поскольку она не

является  допустимой командой процессора командной  строки.  При обработке SQL

было получено сообщение:

SQL0438N  Программа генерирует ошибку с текстом диагностики: "Изъятого товара не может быть больше чем на складе". 

SQLSTATE=75000  
 

Приложение А. SQL-скрипты создания таблиц базы данных.

create table db2admin.customer(

customer_id integer not null,

name varchar(45) not null,

address varchar(45) not null,

phone_number integer not null,

primary key (customer_id)

); 
 

create table db2admin.product(

product_id integer not null,

product_name varchar(45) not null,

quantity integer,

cover varchar(45) not null,

comments varchar(500),

primary key (product_id)

); 
 

create table db2admin.salesman(

salesman_id integer not null,

name varchar(45) not null,

address varchar(45) not null,

phone_number integer not null,

primary key (salesman_id)

); 
 

create table db2admin.place(

place_id integer not null,

product_id integer not null,

quantity integer not null,

size decimal(4,2) not null,

primary key (place_id),

foreign key (product_id) REFERENCES db2admin.product(product_id)

); 
 

create table db2admin.customer_order(

cusorder_id integer not null,

customer_id integer not null,

date_1 date not null,

date_2 date not null,

success varchar(4) not null,

primary key (cusorder_id),

foreign key (customer_id) references db2admin.customer(customer_id)

); 
 

create table db2admin.bye(

cusorder_id integer not null references db2admin.customer_order(cusorder_id)

ON DELETE CASCADE ON UPDATE RESTRICT,

product_id integer not null references db2admin.product(product_id)

ON DELETE CASCADE ON UPDATE RESTRICT,

primary key (cusorder_id, product_id)

); 
 

create table db2admin.maxonplace(

product_id integer not null references db2admin.product(product_id)

ON DELETE CASCADE ON UPDATE RESTRICT,

place_id integer not null references db2admin.place(place_id)

ON DELETE CASCADE ON UPDATE RESTRICT,

max integer not null,

primary key (product_id, place_id)

); 
 

create table db2admin.spend(

spend_id integer not null,

cusorder_id integer not null,

date date not null,

primary key (spend_id),

foreign key (cusorder_id) references db2admin.customer_order(cusorder_id)

); 
 

create table db2admin.spendon(

spend_id integer not null REFERENCES db2admin.spend(spend_id)

ON DELETE CASCADE ON UPDATE RESTRICT,

product_id integer not null references db2admin.product(product_id)

ON DELETE CASCADE ON UPDATE RESTRICT,

quantity integer not null,

primary key (spend_id, product_id)

); 
 

create table db2admin.take(

spend_id integer not null REFERENCES db2admin.spend(spend_id)

ON DELETE CASCADE ON UPDATE RESTRICT,

product_id integer not null REFERENCES db2admin.product(product_id)

ON DELETE CASCADE ON UPDATE RESTRICT,

place_id integer not null,

primary key (spend_id, product_id),

foreign key (place_id) references db2admin.place(place_id)

); 
 

create table db2admin.sale_order(

saleorder_id integer not null,

salesman_id integer not null,

date_1 date not null,

date_2 date not null,

success varchar(4) not null,

primary key (saleorder_id),

foreign key (salesman_id) references db2admin.salesman(salesman_id)

); 
 

create table db2admin.saleones(

product_id integer not null REFERENCES db2admin.product(product_id)

ON DELETE CASCADE ON UPDATE RESTRICT,

saleorder_id integer not null REFERENCES db2admin.sale_order(saleorder_id)

ON DELETE CASCADE ON UPDATE RESTRICT,

quantity integer not null,

primary key (product_id, saleorder_id)

); 
 

create table db2admin.incash(

come_id integer not null,

saleorder_id integer not null,

date date not null,

primary key (come_id),

foreign key (saleorder_id) references db2admin.sale_order(saleorder_id)

); 
 

create table db2admin.incashon(

product_id integer not null REFERENCES db2admin.product(product_id)

ON DELETE CASCADE ON UPDATE RESTRICT,

come_id integer not null REFERENCES db2admin.incash(come_id)

ON DELETE CASCADE ON UPDATE RESTRICT,

quantity integer not null,

primary key (product_id, come_id)

); 
 

create table db2admin.placing_incash(

come_id integer not null REFERENCES db2admin.incash(come_id)

ON DELETE CASCADE ON UPDATE RESTRICT,

product_id integer not null REFERENCES db2admin.product(product_id)

ON DELETE CASCADE ON UPDATE RESTRICT,

place_id integer not null,

quantity integer not null,

primary key (come_id, product_id),

foreign key (place_id) references db2admin.place(place_id)

); 
 
 

   Приложение Б. SQL-скрипты создания триггеров.

create trigger nedostach no cascade before insert on spendon

    referencing new as neww  

    for each row mode db2sql

  when(

neww.quantity > (select sum(quantity) from place

where product_id=neww.product_id )

)

signal sqlstate '75000'('Изъятого товара не может быть больше чем на складе'); 
 
 

create trigger nedostach1 no cascade before update on spendon

    referencing new as neww  

    for each row mode db2sql

  when(

neww.quantity > (select sum(quantity) from place

where product_id=neww.product_id )

)

signal sqlstate '75000'('Изъятого товара не может быть больше чем на складе'); 
 
 

      Приложение В. Полные выборки таблиц.

select * from customer

select * from customer_order

select * from bye

select * from spend

select * from spendon

select * from take

select * from salesman

select * from place

select * from maxonplace

select * from sale_order

select * from saleones

select * from incash

select * from incashon

select * from placing_incash