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

В ходе выполнения практической части курсовой работы были закреплены основные навыки проектирования и реализации базы данных.Разработанная база данных, хранимые процедуры и триггеры, позволяют использовать ее с любым приложением, способным вызывать хранимые процедуры, что значительно повышает универсальность решения.В то же время навыки, полученные и закрепленные в ходе выполнения практической части курсовой работы позволяют уверенно работать с одним из самых распространенных на рынке систем управления базами данных продуктов – MicrosoftSQLServerи сопутствующим пакетом MicrosoftSQLManagementStudio.В качестве развития разработанной базы данных можно рекомендовать разработку веб-сервиса, который бы позволил работать с базой данных посредством совершения http-запросов, что значительно расширит область применения разработанного решения.Также при использовании разработанного и других решений на базе MicrosoftSQLServer следует уделять должное внимание резервному копированию данных и обеспечению высокой доступности системы. Например, пакетMicrosoftSQLServer позволяет добавить к изначально централизованной системе управления базами данных элементы распределенной системы. Это достигает путем внедрения и конфигурирования процесса DatabaseMirroring, который в реальном времени создает полную копию продуктивной базы данных на удаленном standbySQL сервере, а также поддерживает возможность автоматического перевода резервного сервера в режим основного. Применение подобных мер позволит значительно повысить общую устойчивость системы к сбоям и предотвратить потери данных в случае наступления каких-либо нештатных либо форс-мажорных ситуаций.СПИСОК ИСПОЛЬЗОВАННОЙ ЛИТЕРАТУРЫГОСТ 12.2.032-78 «ССБТ. Рабочее место при выполнении работ сидя. Общие эргономические требования»,ГОСТ 19.101 – 77 Виды программ и программных документов.ГОСТ 19.701-90 «Схемы алгоритмов, программ, данных и систем»ГОСТ Р50948-2001 «Средства отображения информации индивидуального пользования. Общие эргономические требования и требования безопасности»Акила К., Еремеевский А. Интернет-магазин с нуля. Полное пошаговое руководство; Питер - Москва, 2013. - 176 c.Андреев А.С., Карпович Е.Е. Товарное продвижение интернет-магазина // Научный вестник Московского государственного горного университета. 2011. № 6. С. 3-8.Басыров Р. 1С-Битрикс. Строим профессиональный сайт и интернет-магазин; Книга по Требованию - Москва, 2014. - 544 c.Буч Г. Язык UML. Руководство пользователя / ГрейдиБуч , Джеймс Рамбо , Айвар Джекобсон. - М.: ДМК, 2015. - 432 c.Глушаков, С.В. Базы данных / С.В. Глушаков, Д.В. Ломотько. - М.: Харьков: Фолио, 2017. - 504 c.Голицына Базы данных / Голицына, О.Л. и. - М.: Форум; Инфра-М, 2015. - 399 c. Грекул, В. И. Управление внедрением информационных систем / В.И. Грекул, Г.Н. Денищенко, Н.Л. Коровкина. - Москва: РГГУ, 2014. - 224 c.Карпова, Т.С. Базы данных. Модели, разработка, реализация / Т.С. Карпова. - М.: СПб: Питер, 2016. - 304 c.Малыхина, М. Базы данных: основы, проектирование, использование / М. Малыхина. - М.: БХВ-Петербург, 2016. - 512 c.Мюллер, Р.Дж. Базы данных и UML. Проектирование / Р.Дж. Мюллер. - М.: ЛОРИ, 2017. - 420 c.Редько, В.Н. Базы данных и информационные системы / В.Н. Редько, И.А. Басараб. - М.: Знание, 2017. - 341 c.Приложение 1Полный листинг кода создания таблиц базы данныхUSE [goods]GO/****** Object: Table [dbo].[amounts] Script Date: 03.10.2019 18:10:42 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[amounts]([id] [bigint] IDENTITY(1,1) NOT NULL,[good_id] [bigint] NOT NULL,[store_id] [bigint] NOT NULL,[amount] [int] NOT NULL, CONSTRAINT [PK_amounts] PRIMARY KEY CLUSTERED ([id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOALTER TABLE [dbo].[amounts] WITH CHECK ADD CONSTRAINT [FK_amounts_goods] FOREIGN KEY([good_id])REFERENCES [dbo].[goods] ([id])ON UPDATE CASCADEON DELETE CASCADEGOALTER TABLE [dbo].[amounts] CHECK CONSTRAINT [FK_amounts_goods]GOALTER TABLE [dbo].[amounts] WITH CHECK ADD CONSTRAINT [FK_amounts_stores] FOREIGN KEY([store_id])REFERENCES [dbo].[stores] ([id])ON UPDATE CASCADEON DELETE CASCADEGOALTER TABLE [dbo].[amounts] CHECK CONSTRAINT [FK_amounts_stores]GOUSE [goods]GO/****** Object: Table [dbo].[categories] Script Date: 03.10.2019 18:11:32 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[categories]([id] [bigint] IDENTITY(1,1) NOT NULL,[category_name] [nvarchar](50) NOT NULL, CONSTRAINT [PK_categories] PRIMARY KEY CLUSTERED ([id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOUSE [goods]GO/****** Object: Table [dbo].[customers] Script Date: 03.10.2019 18:11:44 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[customers]([id] [bigint] IDENTITY(1,1) NOT NULL,[last_name] [nvarchar](50) NOT NULL,[first_name] [nvarchar](50) NOT NULL,[middle_name] [nvarchar](50) NOT NULL,[birth_date] [date] NOT NULL,[sex] [int] NOT NULL,[phone] [nvarchar](20) NOT NULL,[email] [nvarchar](50) NOT NULL, CONSTRAINT [PK_customers] PRIMARY KEY CLUSTERED ([id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOUSE [goods]GO/****** Object: Table [dbo].[goods] Script Date: 03.10.2019 18:11:56 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[goods]([id] [bigint] IDENTITY(1,1) NOT NULL,[good_name] [varchar](100) NOT NULL,[good_desc] [varchar](200) NOT NULL,[good_price] [float] NOT NULL,[good_exp_from] [date] NULL,[good_in] [int] NULL,[good_cat] [bigint] NULL,[good_exp_to] [date] NULL, CONSTRAINT [PK_goods] PRIMARY KEY CLUSTERED ([id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGOALTER TABLE [dbo].[goods] WITH CHECK ADD CONSTRAINT [FK_goods_categories] FOREIGN KEY([good_cat])REFERENCES [dbo].[categories] ([id])ON UPDATE CASCADEON DELETE CASCADEGOALTER TABLE [dbo].[goods] CHECK CONSTRAINT [FK_goods_categories]GOUSE [goods]GO/****** Object: Table [dbo].[requests] Script Date: 03.10.2019 18:12:08 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[requests]([id] [bigint] IDENTITY(1,1) NOT NULL,[request_number] [int] NULL,[good_id] [bigint] NOT NULL,[store_id] [bigint] NOT NULL,[customer_id] [bigint] NOT NULL,[amount] [int] NOT NULL,[closed] [int] NOT NULL,[request_date] [date] NULL,[money] [float] NULL, CONSTRAINT [PK_requests] PRIMARY KEY CLUSTERED ([id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOALTER TABLE [dbo].[requests] WITH CHECK ADD CONSTRAINT [FK_requests_customers] FOREIGN KEY([customer_id])REFERENCES [dbo].[customers] ([id])ON UPDATE CASCADEON DELETE CASCADEGOALTER TABLE [dbo].[requests] CHECK CONSTRAINT [FK_requests_customers]GOALTER TABLE [dbo].[requests] WITH CHECK ADD CONSTRAINT [FK_requests_goods] FOREIGN KEY([good_id])REFERENCES [dbo].[goods] ([id])ON UPDATE CASCADEON DELETE CASCADEGOALTER TABLE [dbo].[requests] CHECK CONSTRAINT [FK_requests_goods]GOALTER TABLE [dbo].[requests] WITH CHECK ADD CONSTRAINT [FK_requests_stores] FOREIGN KEY([store_id])REFERENCES [dbo].[stores] ([id])ON UPDATE CASCADEON DELETE CASCADEGOALTER TABLE [dbo].[requests] CHECK CONSTRAINT [FK_requests_stores]GOUSE [goods]GO/****** Object: Table [dbo].[stores] Script Date: 03.10.2019 18:12:18 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[stores]([id] [bigint] IDENTITY(1,1) NOT NULL,[store_name] [varchar](50) NOT NULL,[store_address] [varchar](100) NOT NULL, CONSTRAINT [PK_stores] PRIMARY KEY CLUSTERED ([id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGOПриложение 2Полный листинг хранимых процедурUSE [goods]GO/****** Object: StoredProcedure [dbo].[addCustomer] Script Date: 03.10.2019 18:13:21 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[addCustomer](@last_nameNVARCHAR(50),@first_nameNVARCHAR(50),@middle_nameNVARCHAR(50),@birth_date DATE,@sex INT,@phone NVARCHAR(20),@email NVARCHAR(50))ASBEGININSERT INTO dbo.customers(last_name, first_name, middle_name, birth_date, sex, phone, email)VALUES (@last_name, @first_name, @middle_name, @birth_date, @sex, @phone, @email)ENDUSE [goods]GO/****** Object: StoredProcedure [dbo].[addGood] Script Date: 03.10.2019 18:13:47 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[addGood](@good_nameNVARCHAR(100),@good_descNVARCHAR(200),@good_price float)ASBEGININSERT INTO dbo.goods(good_name, good_desc, good_price)VALUES (@good_name, @good_desc, @good_price)ENDUSE [goods]GO/****** Object: StoredProcedure [dbo].[addRequest] Script Date: 03.10.2019 18:13:56 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[addRequest](@good_id BIGINT,@store_id BIGINT,@customer_id BIGINT,@amount INT)ASBEGINDECLARE @openRequest INT;SET @openRequest=NULL;SET @openRequest=(SELECT request_number FROM requestsWHERE customer_id = @customer_id AND closed=0);IF(@openRequest IS NULL)BEGINSET @openRequest=(SELECT MAX(request_number) FROM requests);IF(@openRequest IS NULL)SET @openRequest=1;ELSESET @openRequest = @openRequest + 1;ENDINSERT INTO requests(request_number, good_id, store_id, customer_id, amount, closed)VALUES(@openRequest, @good_id, @store_id, @customer_id, @amount, 0);ENDUSE [goods]GO/****** Object: StoredProcedure [dbo].[addStore] Script Date: 03.10.2019 18:14:04 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[addStore](@store_nameNVARCHAR(50),@store_addressNVARCHAR(100))ASBEGININSERT INTO dbo.stores(store_name, store_address)VALUES (@store_name, @store_address)ENDUSE [goods]GO/****** Object: StoredProcedure [dbo].[closeRequest] Script Date: 03.10.2019 18:14:12 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[closeRequest](@request_number INT)ASBEGINdeclare @requested INTdeclare @remains INTdeclare @id BIGINTIF OBJECT_ID('goods..#mytemp') IS NOT NULL DROP TABLE #mytempselect id, good_id, store_id, amount into #mytempfrom goods.dbo.requests as reqwhere req.request_number = @request_numberselect * from #mytempselect @id = MIN(id) from #mytempwhile @id IS NOT NULLBEGINselect @requested = req.amountfrom goods.dbo.requests as req, goods.dbo.amounts as amwhere req.id = @id and req.closed = 0 and req.good_id = am.good_id and req.store_id = am.store_idselect @remains = am.amountfrom goods.dbo.requests as req, goods.dbo.amounts as amwhere req.id = @id and req.closed = 0 and req.good_id = am.good_id and req.store_id = am.store_idselect @remains as remainsselect @requested as requestedif (@requested IS NULL)return 1if (@remains is NULL)return 2if (@remains < @requested)return 3delete from #mytemp where id = @idselect @id = MIN(id) from #mytempENDIF OBJECT_ID('goods..#mytemp') IS NOT NULL DROP TABLE #mytempselect id, good_id, store_id, amount into #mytemp2from goods.dbo.requests as reqwhere req.request_number = @request_numberselect * from #mytemp2select @id = MIN(id) from #mytemp2while @id IS NOT NULLBEGIN declare @good_id BIGINTdeclare @store_id BIGINTdeclare @amount INTselect @good_id = #mytemp2.good_id from #mytemp2where #mytemp2.id = @idselect @store_id = #mytemp2.store_idfrom #mytemp2where #mytemp2.id = @idselect @amount = #mytemp2.amount from #mytemp2where #mytemp2.id = @idupdate goods.dbo.amountsset amount = amount - @amountwhere store_id = @store_id and good_id = @good_idupdate goods.dbo.requestsset closed = 1where id = @iddelete from #mytemp2 where id = @idselect @id = MIN(id) from #mytemp2ENDIF OBJECT_ID('goods..#mytemp2') IS NOT NULL DROP TABLE #mytemp2ENDUSE [goods]GO/****** Object: StoredProcedure [dbo].[getCustomerByGood] Script Date: 03.10.2019 18:14:22 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER procedure [dbo].[getCustomerByGood](@good_namenvarchar(100))asbeginselect last_name, first_name, middle_namefrom customerswhere id in (select customer_idfrom requestswhere good_id = (select idfrom goodswhere good_name = @good_name))order by 1, 2, 3endUSE [goods]GO/****** Object: StoredProcedure [dbo].[getCustomerByGood2] Script Date: 03.10.2019 18:14:32 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER procedure [dbo].[getCustomerByGood2](@good_namenvarchar(100))asbeginselect last_name, first_name, middle_namefrom customersright join requests on customers.id = requests.customer_idleft outer join goods on requests.good_id = goods.idwhere goods.good_name = @good_nameorder by 1, 2, 3endUSE [goods]GO/****** Object: StoredProcedure [dbo].[getCustomersByCat] Script Date: 03.10.2019 18:14:39 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER procedure [dbo].[getCustomersByCat](@category_namenvarchar(50))asbeginselect last_name, first_name, middle_namefrom customersright join (select r.customer_idfrom customers as c, requests as r, categories as cats, goods as gwhere c.id = r.customer_id and r.good_id = g.id and g.good_cat = cats.id and cats.category_name = 'Масло'group by r.customer_id) s on s.customer_id = customers.idorder by 1, 2, 3endUSE [goods]GO/****** Object: StoredProcedure [dbo].[getGoodsByClient] Script Date: 03.10.2019 18:14:48 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[getGoodsByClient](@customer_idbigint,@from_date date,@to_date date)ASBEGINselect goods.good_namefrom goods, requestswhere requests.closed = 1 and goods.id = requests.good_id and requests.request_date >= @from_date and requests.request_date <= @to_dategroup by goods.good_nameorder by 1ENDUSE [goods]GO/****** Object: StoredProcedure [dbo].[getSpentCustomers] Script Date: 03.10.2019 18:14:55 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER procedure [dbo].[getSpentCustomers]ASbeginselect last_name, first_name, middle_name, s.totalSpentfrom customersleft outer join (select r.customer_id, SUM(r.money) as totalSpentfrom customers as c, requests as rwhere c.id = r.customer_idgroup by r.customer_id) s on s.customer_id = customers.idwhere s.totalSpent > 0order by 1, 2, 3endUSE [goods]GO/****** Object: StoredProcedure [dbo].[getSpentCustomersN] Script Date: 03.10.2019 18:15:03 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER procedure [dbo].[getSpentCustomersN](@N float)ASbeginselect last_name, first_name, middle_name, s.totalSpentfrom customersleft outer join (select r.customer_id, SUM(r.money) as totalSpentfrom customers as c, requests as rwhere c.id = r.customer_idgroup by r.customer_id) s on s.customer_id = customers.idwhere s.totalSpent > @Norder by 1, 2, 3endUSE [goods]GO/****** Object: StoredProcedure [dbo].[updateCustomer] Script Date: 03.10.2019 18:15:11 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[updateCustomer](@id BIGINT,@last_nameNVARCHAR(50) = NULL,@first_nameNVARCHAR(50) = NULL,@middle_nameNVARCHAR(50) = NULL,@birth_date DATE = NULL,@sex INT = NULL,@phone NVARCHAR(20) = NULL,@email NVARCHAR(50) = NULL)ASBEGINDECLARE @updateSQLNVARCHAR(1000);DECLARE @getChange INT;SET @getChange = 0;SET @updateSQL = 'UPDATE dbo.customers SET ';IF(NOT(@last_name IS NULL OR @last_name = ''))BEGINSET @updateSQL = @updateSQL + 'last_name=''' + @last_name + '''';SET @getChange = 1;ENDIF(NOT(@first_name IS NULL OR @first_name = ''))BEGINIF(@getChange = 1)SET @updateSQL = @updateSQL + ', ';SET @updateSQL = @updateSQL + 'first_name=''' + @first_name + '''';SET @getChange = 1;ENDIF(NOT(@middle_name IS NULL OR @middle_name = ''))BEGINIF(@getChange = 1)SET @updateSQL = @updateSQL + ', ';SET @updateSQL = @updateSQL + 'middle_name=''' + @middle_name + '''';SET @getChange = 1;ENDIF(NOT(@birth_date IS NULL OR @birth_date = ''))BEGINIF(@getChange = 1)SET @updateSQL = @updateSQL + ', ';SET @updateSQL = @updateSQL + 'birth_date=''' + CONVERT(VARCHAR(10), @birth_date, 23) + '''';SET @getChange = 1;ENDIF(NOT(@sex IS NULL OR @sex = ''))BEGINIF(@getChange = 1)SET @updateSQL = @updateSQL + ', ';SET @updateSQL = @updateSQL + 'sex=' + @sex;SET @getChange = 1;ENDIF(NOT(@phone IS NULL OR @phone = ''))BEGINIF(@getChange = 1)SET @updateSQL = @updateSQL + ', ';SET @updateSQL = @updateSQL + 'phone=''' + @phone + '''';SET @getChange = 1;ENDIF(NOT(@email IS NULL OR @email = ''))BEGINIF(@getChange = 1)SET @updateSQL = @updateSQL + ', ';SET @updateSQL = @updateSQL + 'email=''' + @email + '''';SET @getChange = 1;ENDSET @updateSQL = @updateSQL + ' WHERE id=' + STR(@id);PRINT @updateSQL;IF(@getChange = 1)EXEC(@updateSQL);ENDUSE [goods]GO/****** Object: StoredProcedure [dbo].[updateGood] Script Date: 03.10.2019 18:15:18 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[updateGood](@id BIGINT,@good_nameNVARCHAR(100) = NULL,@good_descNVARCHAR(200) = NULL,@good_price float = NULL)ASBEGINDECLARE @updateSQLNVARCHAR(1000);DECLARE @getChange INT;SET @getChange = 0;SET @updateSQL = 'UPDATE dbo.goods SET ';IF(NOT(@good_name IS NULL OR @good_name = ''))BEGINSET @updateSQL = @updateSQL + 'good_name=''' + @good_name + '''';SET @getChange = 1;ENDIF(NOT(@good_desc IS NULL OR @good_desc = ''))BEGINIF(@getChange = 1)SET @updateSQL = @updateSQL + ', ';SET @updateSQL = @updateSQL + 'good_desc=''' + @good_desc + '''';SET @getChange = 1;ENDIF(NOT(@good_price IS NULL OR @good_price = ''))BEGINIF(@getChange = 1)SET @updateSQL = @updateSQL + ', ';SET @updateSQL = @updateSQL + 'good_price=' + STR(@good_price, 10, 2);SET @getChange = 1;ENDSET @updateSQL = @updateSQL + ' WHERE id=' + STR(@id);PRINT @updateSQL;IF(@getChange = 1)EXEC(@updateSQL);ENDUSE [goods]GO/****** Object: StoredProcedure [dbo].[updateStore] Script Date: 03.10.2019 18:15:27 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[updateStore](@id BIGINT,@store_nameNVARCHAR(50) = NULL,@store_addressNVARCHAR(100) = NULL)ASBEGINDECLARE @updateSQLNVARCHAR(1000);DECLARE @getChange INT;SET @getChange = 0;SET @updateSQL = 'UPDATE dbo.stores SET ';IF(NOT(@store_name IS NULL OR @store_name = ''))BEGINSET @updateSQL = @updateSQL + 'store_name=''' + @store_name + '''';SET @getChange = 1;ENDIF(NOT(@store_address IS NULL OR @store_address = ''))BEGINIF(@getChange = 1)SET @updateSQL = @updateSQL + ', ';SET @updateSQL = @updateSQL + 'store_address=''' + @store_address + '''';SET @getChange = 1;ENDSET @updateSQL = @updateSQL + ' WHERE id=' + STR(@id);PRINT @updateSQL;IF(@getChange = 1)EXEC(@updateSQL);END