Содержание
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 таблиц. Приведем описание каждой таблицы базы данных. Скрипты создания базы данных содержатся в приложении.

	Название столбца	Тип данных			Ограничение				Комментарии
	Customer(Покупатель)
1	customer_id	integer NOT NULL				Первичный ключ
2	NAME	VARCHAR (250) NOT NULL						ФИО
3	address	VARCHAR(100) NOT NULL						Адрес
4	phone_number	integer not null						телефон
	Customer_order(Договор продажа)
1	cusorder_id	Integer not null				Первичный ключ
2	customer_id	integer NOT NULL				ссылка на customer
3	date_1	DATE NOT NULL						Дата заключения
4	date_2	DATE NOT NULL						Дата поставки
5	success	VARCHAR (4)						Выполнение (да/нет)
	Product(Товар)
1	product_id	Integer not null				Первичный ключ
2	product_name	Varchar(100)						Наименование
3	quantity	integer not null						Количество
4	cover	varchar(100)						Упаковка
5	comments	VARCHAR (250) NOT NULL						Коментарии
	Salesman (Продавец)
1	salesman_id	INTEGER NOT NULL				Первичный ключ
2	NAME	VARCHAR (100) NOT NULL						ФИО
3	address	VARCHAR (100) NOT NULL						Адрес
4	phone_number	integer not null						телефон
	Sale_order(Договор о покупке)
1	saleorder_id	INTEGER NOT NULL				Первичный ключ
2	salesman_id	INTEGER NOT NULL				ссылка на salesman
3	date_1	DATE NOT NULL						Дата заключения
4	date_2	DATE NOT NULL						Дата поставки
5	success	VARCHAR (4)						Выполнение (да/нет)
	Place(Место на складе)
1	place_id	INTEGER NOT NULL				Первичный ключ
2	product_id	INTEGER NOT NULL				Ссылка на product
3	size	decimal(5,2)						размеры места(M^2)
4	quantity	integer not null						Количество
	Maxonplace(Максимальное количество товара на месте)
1	place_id	INTEGER NOT NULL				Первичный ключ
2	MAX	INTEGER NOT NULL						max количество на место
	Bye(Продажа)
1	cusorder_id	Integer not null				Первичный ключ
2	product_id	INTEGER NOT NULL				Первичный ключ
	Spend(Расходная накладная)
1	spend_id	Integer not null				Первичный ключ
2	cusorder_id	Integer not null				ссылка на customer_order
3	date	DATE NOT NULL						Дата
	Spendon(Расходная накладная на товар)
1	spend_id	Integer not null				Первичный ключ
2	product_id	Integer not null				Первичный ключ
3	quantity	integer not null						Количество
	Take(Изъятие товара со склада)
1	place_id	INTEGER NOT NULL				ссылка на place
2	spend_id	Integer not null				Первичный ключ
3	product_id	Integer not null				Первичный ключ
	Saleones(Покупка товара по договору)
1	product_id			Integer not null			Первичный ключ
2	saleorder_id			INTEGER NOT NULL			Первичный ключ
3	quantity			integer not null					Количество
	Incash(Приходная накладная)
1	come_id		Integer not null				Первичный ключ
2	saleorder_id		INTEGER NOT NULL				ссылка на sale_order
3	date		DATE NOT NULL						Дата
	Incashon(Приходная накладная на товар)
1	product_id		Integer not null				Первичный ключ
2	come_id		Integer not null				Первичный ключ
3	quantity		integer not null						Количество
	Placing_incash (Размещение товара на складе в соответствии с накладной)
1	come_id		Integer not null				Первичный ключ
2	product_id		Integer not null				Первичный ключ
3	place_id		INTEGER NOT NULL				ссылка на place
4	quantity		integer not null						Количество

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

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

20000	1
15	2
2000	3
4	4
5000	5

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