Организация процессов обработки данных в БД

Прежде чем переходить к созданию запросов к БД, необходимо сначала заполнить все таблицы информацией.

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

INSERTINTO[Opt_firm].[dbo].[Kategoria]([nazvanie_kat])

VALUES ('Антифризы') \

GO

INSERTINTO[Opt_firm].[dbo].[Otdel]([nazvanie_otd])

VALUES('Отдел выписки товаров')

GO

INSERTINTO[Opt_firm].[dbo].[Operator]([FIO],[dolgnost],[adress],[tel],[ID_otd])

VALUES ('Горячева Оксана Александровна', 'Оператор выписки товаров', 'г. Киров, ул. Физкультурников, д. 33, кв. 67', '8-922-909-30-03',1)

GO

INSERTINTO[Opt_firm].[dbo].[Napravlenie]([nazvanie_napr], [ID_op])

VALUES ('Киров', 8)

GO

INSERTINTO[Opt_firm].[dbo].[Pokupatel]([naimenovanie],[INN],[adress],[tel],[kontact])

VALUES ('Атлашкин Антон Игоревич ИП', '434268651990', '610027, г. Киров, ул. КарлаЛибкнехта, д.133, кв.46', '8-922-660-78-81', '')

GO

INSERTINTO[Opt_firm].[dbo].[Prodazha]([num_dog],[data_dog],[summa_dog],[ID_pkp])

VALUES ('3594','26.12.2016', 152900, 1)

GO

INSERTINTO[Opt_firm].[dbo].[Tovar]([articul],[nazvanie],[ed_izm],[ves],[kol],[price],[ID_kat])

VALUES ('5631','Антигравий BODY-550 черный Проф. 1000 мл', 'шт.', 1, 116, 367, 2)

GO

INSERTINTO[Opt_firm].[dbo].[Otgruzka]([num_UPD],[data_UPD],[sum_ves],[cost],[ID_prd],[ID_op])

VALUES ('5985','01.03.2017', 489, 14757, 7, 7)

GO

INSERTINTO[Opt_firm].[dbo].[UPD]([ID_tvr],[ID_otgr],[kol_tvr],[ves_tvr],[cost_tvr])

VALUES (20, 8, 33, 165, 2145)

GO

Скриншоты заполненных таблиц представлены в Приложении 2.

Ниже представлены тексты различных запросов, которые были сформулированы на языке Transact-SQLс помощью условий, с применением сортировки, с ограничением числа выводимых записей, с использованием внутренних, внешних, перекрестных соединений и объединений, а также с использованием агрегатных функций, группировкой записей и применением подзапросов.

Запрос 1. Выбрать первые 10 товаров, стоимость которых ниже 200 р., отсортировать их по возрастанию цены.

Текстзапроса:

SELECTTOP(10)articul,nazvanie,ed_izm,ves,price

FROMTovar

WHEREprice< 200

ORDERBYpriceASC

GO

Запрос 2. Выбрать все отгрузки по договору №3378 от 20.12.2016 г. с указаниемданных договора и ФИО оператора, осуществлявшего отгрузку,отсортировать по номеру УПД.

Текст запроса:

SELECTdbo.Otgruzka.num_UPDASНомер_УПД,dbo.Otgruzka.data_UPDASДата_УПД,dbo.Otgruzka.costASСумма_УПД,dbo.Prodazha.num_dogASНомер_договора,dbo.Prodazha.data_dogASДата_договора,dbo.Prodazha.summa_dogASСумма_договора,dbo.Operator.FIOASОператор

FROMdbo.OperatorINNERJOIN

dbo.OtgruzkaONdbo.Operator.ID_op=dbo.Otgruzka.ID_opINNERJOIN

dbo.ProdazhaONdbo.Otgruzka.ID_prd=dbo.Prodazha.ID_prd

WHERE (dbo.Prodazha.num_dog='3378')AND(dbo.Prodazha.data_dog='20.12.2016')

ORDERBYНомер_УПД

3. Вывести всех покупателей, номера договоров, номера и сумму УПД по которым провели отгрузку 06.03.2017 года по каждому оператору.

Текст запроса:

SELECTdbo.Pokupatel.naimenovanieASПокупатель,dbo.Pokupatel.INNASИНН, dbo.Prodazha.num_dogASНомер_договора,dbo.Otgruzka.num_UPDASНомер_УПД,dbo.Otgruzka.costASСумма_УПД,

dbo.Operator.FIOASОператор

FROMdbo.OperatorINNERJOIN

dbo.OtgruzkaONdbo.Operator.ID_op=dbo.Otgruzka.ID_opINNERJOIN

dbo.ProdazhaONdbo.Otgruzka.ID_prd=dbo.Prodazha.ID_prdINNERJOIN

dbo.PokupatelONdbo.Prodazha.ID_pkp=dbo.Pokupatel.ID_pkp

WHERE (dbo.Otgruzka.data_UPD='06.03.2017')

4. Вывести список товаров с указанием количества, веса и суммы, а также номера УПД и даты отгрузки, которые были отгружены оператором Сидоровой Ольгой Юрьевной за период с 04.03.2017 г. по 06.03.2017 г.

Текст запроса:

SELECTdbo.Tovar.articulASАртикул,dbo.Tovar.nazvanieASНазвание,dbo.UPD.kol_tvrASКоличество, dbo.UPD.ves_tvrASВес,dbo.UPD.cost_tvrASСтоимость,dbo.Otgruzka.num_UPDASНомер_УПД, dbo.Otgruzka.data_UPDASДата_УПД,dbo.Operator.FIOASОператор

FROM dbo.TovarINNERJOIN

dbo.UPDONdbo.Tovar.ID_tvr=dbo.UPD.ID_tvrINNERJOIN

dbo.OtgruzkaONdbo.UPD.ID_otgr=dbo.Otgruzka.ID_otgrINNERJOIN

dbo.OperatorONdbo.Otgruzka.ID_op=dbo.Operator.ID_op

WHERE ((dbo.Otgruzka.data_UPD>='04.03.2017')AND(dbo.Otgruzka.data_UPD<='06.03.2017'))AND(dbo.Operator.ID_op= 7)

5. Вывести по каждому покупателю количество проведенных отгрузок и их сумму за период с 02.03.2017 г. по 05.03.2017 г.

Текст запроса:

SELECTDISTINCTdbo.Pokupatel.naimenovanieASПокупатель,dbo.Pokupatel.INNASИНН,COUNT(dbo.Otgruzka.ID_otgr)OVER (PARTITIONBYdbo.Pokupatel.ID_pkp)ASКоличество_отгрузок, SUM(dbo.Otgruzka.cost)OVER (PARTITIONBYdbo.Pokupatel.ID_pkp)ASСумма_отгрузок

FROMdbo.OtgruzkaINNERJOIN

dbo.ProdazhaONdbo.Otgruzka.ID_prd=dbo.Prodazha.ID_prdINNERJOIN

dbo.PokupatelONdbo.Prodazha.ID_pkp=dbo.Pokupatel.ID_pkp

WHERE (dbo.Otgruzka.data_UPD>='02.03.2017')AND(dbo.Otgruzka.data_UPD<='05.03.2017')

6. Вывести по каждому отгруженному 01.03.2017 г. товару общее количество, суммарный вес и стоимость, отсортировать по категориям товара.

Текст запроса:

SELECTDISTINCTdbo.Kategoria.nazvanie_kat,dbo.Tovar.articul,dbo.Tovar.nazvanie,

SUM(dbo.UPD.kol_tvr)OVER (PARTITIONBYdbo.Tovar.ID_tvr)ASКоличество,

SUM(dbo.UPD.ves_tvr)OVER (PARTITIONBYdbo.Tovar.ID_tvr)ASВес,

SUM(dbo.UPD.cost_tvr)OVER (PARTITIONBYdbo.Tovar.ID_tvr)ASСумма

FROMdbo.TovarINNERJOIN

dbo.UPDONdbo.Tovar.ID_tvr=dbo.UPD.ID_tvrINNERJOIN

dbo.OtgruzkaONdbo.UPD.ID_otgr=dbo.Otgruzka.ID_otgrINNERJOIN

dbo.KategoriaONdbo.Tovar.ID_kat=dbo.Kategoria.ID_kat

WHEREdbo.Otgruzka.data_UPD='02.03.2017'

ORDERBYdbo.Kategoria.nazvanie_kat

Хранимой процедуройназывается объект базы данных, представляющий собой набор SQL-инструкций, который компилируется один раз и хранится на сервере.

Ниже представлены тексты запросов разработанных хранимых процедур.

Хранимая процедура 1: вывод остатков товаров на складе.

Текст создания хранимой процедуры:

CREATEPROCEDUREOstatki

AS

BEGIN

SELECTdbo.Kategoria.nazvanie_katASКатегория,

dbo.Tovar.articulASАртикул,

dbo.Tovar.nazvanieASНазвание,

dbo.Tovar.ed_izmASЕд_изм,

dbo.Tovar.vesASВес_ед,

dbo.Tovar.kolASКоличество_на_складе,

dbo.Tovar.priceASЦена_за_ед

FROMdbo.TovarINNERJOIN

dbo.KategoriaONdbo.Tovar.ID_kat=dbo.Kategoria.ID_kat

END

GO

2. Хранимая процедура на сумму отгрузок по договору.

Текст создания хранимой процедуры:

CREATEPROCEDUREOtgruzka_po_dogovoru

@nomer_dogovoraVARCHAR(12)

AS

BEGIN

SELECTDISTINCTdbo.Prodazha.num_dogASНомер_договора,

dbo.Prodazha.data_dogASДата_договора,

dbo.Prodazha.summa_dogASСумма_договора,

SUM(dbo.Otgruzka.cost)OVER (PARTITIONBYdbo.Otgruzka.ID_prd)ASСумма_отгрузок

FROMdbo.OtgruzkaINNERJOIN

dbo.ProdazhaONdbo.Otgruzka.ID_prd=dbo.Prodazha.ID_prd

WHEREdbo.Prodazha.num_dog=@nomer_dogovora;

END

GO

Представлением (view) называют объект базы данных, являющийся результатом выполнения запроса к базе данных, определенного с помощью оператора SELECT, в момент обращения к представлению. Представления иногда называют «виртуальными таблицами» и используюттакие конструкции для выборки данных, содержащихся в одной или более таблицах базы данных.

Ниже представлены тексты запросов на создание представлений.

1. Представление, выводящее информацию по покупателям и их договорам.

Текст запроса:

SELECTdbo.Pokupatel.naimenovanieASПокупатель,dbo.Pokupatel.INNASИНН,dbo.Pokupatel.adressASАдрес,dbo.Pokupatel.telASТелефон, dbo.Pokupatel.kontactASКонтактное_лицо,dbo.Prodazha.num_dogASНомер_договора,dbo.Prodazha.data_dogASДата_договора,dbo.Prodazha.summa_dogASСумма_договора

FROMdbo.PokupatelINNERJOIN

dbo.ProdazhaONdbo.Pokupatel.ID_pkp=dbo.Prodazha.ID_pkp

2. Представление, выводящее информацию по договорам, отгрузкам и операторам.

Текст запроса:

SELECTTOP (100)dbo.Prodazha.num_dogASНомер_договора,dbo.Prodazha.data_dogASДата_договора,

dbo.Prodazha.summa_dogASСумма_договора,dbo.Otgruzka.num_UPDASНомер_УПД,dbo.Otgruzka.data_UPDASДата_УПД,

dbo.Otgruzka.costASСумма_УПД,dbo.Otdel.nazvanie_otdASОтдел,dbo.Operator.FIOASОператор,

dbo.Operator.dolgnostASДолжность,dbo.Operator.telASТелефон

FROMdbo.OperatorINNERJOIN

dbo.OtdelONdbo.Operator.ID_otd=dbo.Otdel.ID_otdINNERJOIN

dbo.OtgruzkaONdbo.Operator.ID_op=dbo.Otgruzka.ID_opINNERJOIN

dbo.ProdazhaONdbo.Otgruzka.ID_prd=dbo.Prodazha.ID_prd

ORDERBYdbo.Prodazha.num_dog

Триггер (trigger) - это хранимая процедура особого типа, которую пользователь не вызывает непосредственно, а исполнение которой обусловлено действием по модификации данных: добавлением INSERT, удалением DELETE строки в заданной таблице, или изменением UPDATE данных в определённом столбце заданной таблицы.

Ниже представлены тексты SQL-конструкций на создание триггеров.

1. Триггер, обновляющий остаток товара на складе в таблице «Tovar» послеотгрузки.

Текст конструкции на создание триггера:

CREATETRIGGERUpdate_Tovar

ONdbo.UPD

AFTERINSERT

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SETNOCOUNTON;

declare@tov_count_oldinteger

declare@tov_count_newinteger

declare@tov_count_otgrinteger

declare@idinteger

declare@id_tovinteger

SET@id=@@IDENTITY

SET@id_tov=(SELECTdbo.UPD.ID_tvrFROMdbo.UPDWHEREID_upd=@id)

SET@tov_count_old=(SELECTdbo.Tovar.kolFROMdbo.TovarWHEREdbo.Tovar.ID_tvr=@id_tov)

SET@tov_count_otgr=(SELECTdbo.UPD.kol_tvrFROMdbo.UPDWHEREID_upd=@id)

SET@tov_count_new=@tov_count_old-@tov_count_otgr

UPDATEdbo.Tovar

SETdbo.Tovar.kol=@tov_count_new

WHEREdbo.Tovar.ID_tvr=@id_tov

END

GO

2. Триггер,осуществляющий логгирование изменений в таблице «Otgruzka».

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

CREATETABLEAudit_Otgruzka(

IDintegerIDENTITY(1,1)NOTNULL,

opervarchar(15)NOTNULL,

record_countintegerNOTNULL,

date_chdatetimeNULL,

CONSTRAINT[PK_Audit_Otgruzka]PRIMARYKEYCLUSTERED

(

[ID]ASC

)WITH (IGNORE_DUP_KEY=OFF)

)

GO

Текст конструкции на создание триггера:

CREATETRIGGERlog_OtgruzkaONdbo.Otgruzka

forINSERT

AS

BEGIN

SETNOCOUNTon

declare@record_cntinteger

SET@record_cnt=(SELECTCOUNT(*)FROMdbo.Otgruzka);

INSERTINTOAudit_Otgruzka(oper,record_count,date_ch)

VALUES ('insert',@record_cnt,GETDATE())

END

GO

Тестирование созданной БД

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

Результаты выполнения запросов 1 - 5 представлены на рис. 6-10 соответственно.

Рис. 6. Результат выполнения запроса 1

Рис. 7. Результат выполнения запроса 2

Рис. 8 - Результат выполнения запроса.

Рис. 9 - Результат выполнения запроса.

Рис. 10 - Результат выполнения запроса.

Рис. 11 - Результат выполнения запроса.

Результаты выполнения хранимых процедур представлены на рис. 12 - 13.

Рис. 12. Результат выполнения хранимой процедуры 1

Рис. 13 - Результат выполнения хранимой процедуры с параметром.

Результаты загрузки представлений представлены на рис. 14 - 15.

Рис. 14.Результат выполнения представления 1

Рис. 15 - Результат выполнения представления.

Результат выполнения триггера 1 представлены на рис. 16, 17, 18.

Рис.16.Таблица «Tovar» до изменения

Рис. 17.Внесение данных в таблицу «UPD»

Рис. 18.Таблица «Tovar» после изменения

Как видно из рисунков, после добавления позиции по товару с ID_tvr=2 в количестве 10 шт. в таблицу «UPD», значение столбца «kol»таблицы «Tovar»по данной записи уменьшилось на 10.

Результат выполнения триггера 2 представлен на рис. 19, 20, 21.

Рис.19.Таблица «Audit_Otgruzka» до изменения

Рис. 20. Внесение данных в таблицу «Otgruzka»

Рис. 21. Таблица «Audit_Otgruzka» после изменения

Вывод по второй главе

Таким образом,

ЗАКЛЮЧЕНИЕ

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

Практическая часть курсовой работы была посвящена физической реализации базы данных отдела выписки товаров в программной среде ManagementStudioв СУБД MicrosoftSQLServer 2008: создана сама база данных и ее таблицыс учетом приведенных ограничений, проведено заполнение этих таблиц данными, на языке Transact-SQLразработаны различные запросы, представления, хранимые процедуры и триггеры.

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

БИБЛИОГРАФИЧЕСКИЙ СПИСОК

1. Гайдамакин Н. А. Автоматизированные информационные системы, базы и банки данных. Вводный курс: Учебное пособие. / Н.А. Гадамакин. - М.: Гелиос АРВ, 2002. - 368 с.

2. Зрюмов Е. А., Базы данных для инженеров: учебное пособие. Алт. гос. техн. ун-т им. И. И. Ползунова / Е.А. Зрюмов, А. Г. Зрюмова. – Барнаул: Изд-во АлтГТУ, 2010. – 131 с.

3. Кузин А. В. Базы данных: учебное пособие. 5-е изд. / А. В. Кузин. - М.: Академия, 2012. - 317 с.

4. Петров В. Н. Информационные системы / В.Н. Петров. - СПб.: Питер, 2002. - 688 с.

5. Петров Г.А. Базы данных: учебное пособие /Г.А. Петров, С.В. Тихов, В.П. Яковлев, СПбГТУ РП. - СПб. 2015. – 74 с.

6. Ульман Джеффри Д., Основы реляционных баз данных / Д. Ульман, Д. Уидом. - М.: Лори, 2006. - 384 с.

7. Хомоненко А.Д. Базы данных:учебник для высших учебных заведений / Под ред. проф. А.Д. Хомоненко. - СПб.: КОРОНА принт, 2004. - 736 с.

8. Codd. E. F. A Relational Model of Data for Large Shared Data Banks // Commun. ACM.- 26, N 1.- 1970.- 377-387с.

Приложение 1

Диаграмма БД в СУБД MSSQL-Server

Приложение 2

Заполненные таблицы БД

Рис. 1. Таблица «Operator»

Рис. 2 Таблица «Otdel».

Рис. 3 - Таблица «Napravlenie».

Рис. 4 - Таблица «Pokupatel».

Рис. 5 - Таблица «Tovar».

Рис. 6 - Таблица «Kategoria».

Рис. 7 - Таблица «Prodazha».

Рис. 8 - Таблица «Otgruzka».

Рис. 9 - Таблица «UPD».