Магазин электроники и бытовой техники. Проектирование и разработка базы данных информационной системы " Магазин электроники и бытовой техники"
 Скачать 0.69 Mb.
|
|
Министерство Образования и Науки Российской Федерации Федеральное Государственное Бюджетное Образовательное Учреждение Высшего Профессионального Образования «Камская государственная инженерно-экономическая академия» (ИНЭКА) Кафедра «ММИТЭ» КУРСОВОЙ ПРОЕКТ По дисциплине «Базы данных» На тему «Проектирование и разработка базы данных информационной системы “ Магазин электроники и бытовой техники”» Выполнил: студент гр. ххх хххх № зачетной книжки: ххх Проверил: ст. преподаватель хххх Набережные Челны 2011 год ОглавлениеВведение 3 1 Спецификация требований 4 2 Концептуальное проектирование. Создание ER-модели предметной области 5 3 Логическое проектирование. Построение и проверка реляционной модели данных 9 Исключение из концептуальной модели особенностей несовместимых с реляционной моделью. 9 Формирование набора отношений 9 Определение ограничений целостности 12 4 Физическое проектирование и реализация базы данных в MS SQL Server 13 Определение состава и структуры таблиц 13 Реализация ограничений целостности 16 Проектирование и разработка пользовательских представлений 17 Заключение 20 ВведениеБаза данных - важнейший компонент любой информационной системы. База данных позволяет структурировано хранить большие объемы информации конкретного предприятия, что значительно рационализирует ведение отчетов и создание архивов. Оптимизированные базы данных значительно увеличивают производительность, построенных на их использовании, программ. С развитием информационных технологий и предпринимательства, актуальность использования баз данных значительно увеличилось. Успешные и крупные компании не могут представить свой бизнес без четко построенной информационной системы. Базы данных, построенные на SQL Server, отвечают высоким требованиям производительности и безопасности. Предметная область: информационная система магазина электроники и бытовой техники. Целью данного курсового проекта является реализация информационной системы “Магазин электроники и бытовой техники” для автоматизации деятельности и обеспечения хранения, накопления и предоставления информации. Задачи проекта: Исследовать предметную область и создать концептуальную модель, построение ER-модели; Логическое проектирование. Построение и проверка реляционной модели данных; Проектирование и разработка пользовательских представлений. 1Спецификация требованийПредметной областью базы данных является деятельность магазина электроники и бытовой техники. Описан один основной вид деятельности: Продажа электроники и бытовой техники. Сформулированы следующие задачи: Возможность добавления, удаления данных Учет поставок и сбыта товара Счет выручки Определение наиболее успешного менеджера Хранение всех сведений о товаре Требования к данным Информация о поставщике должна содержать следующие атрибуты: Наименование фирмы; Номер телефона представителя фирмы поставщика; Имя и фамилия представителя; Данная база данных предполагает учет продаж товара, следовательно, должны фиксироваться следующие данные: Дата продажи; количество; описание товара; скидка на товар; стоимость продажи; менеджер продавший товар. 2Концептуальное проектирование. Создание ER-модели предметной областиТипы сущностей
Типы связей
Выделенные сущности и связи обладают следующими атрибутами: Атрибуты типов сущностей и типов связей:
Домен атрибута – это набор допустимых значений одного или нескольких атрибутов. Были определены следующие допустимые значения: Для атрибутов quantity, orderprice, day, warescount допустимы только положительные значения отличные от нуля. Для атрибута percent допустимы только значения от 0 до 20 включительно (чтобы скидка не оказалась в убыток магазину). Атрибут salecount является вычисляемым столбцом и считается по формуле: ([orderprice]*(1.3)) (накрутка магазина 30% от закупа) Определяем первичные ключи для типов сущностей:
Концептуальная схема  3Логическое проектирование. Построение и проверка реляционной модели данныхИсключение из концептуальной модели особенностей несовместимых с реляционной моделью.В ходе проектирования концептуальной модели были выявлены связи «многие ко многим». Устранение из концептуальной модели этих связей можно путем добавления новой сущности saleitem (содержание продаж), orderitem(содержание заказов), period (сроки доставки)
 Формирование набора отношенийВ процессе концептуального моделирования выявила следующий набор отношений, необходимого для представления сущностей, связей и атрибутов: Описание реляционной схемы manager (idmanager,name,phone) Первичный ключ idmanager Альтернативный ключ phone On INSERT no action On UPDATE cascade sale (idsale, idmanager,saledate) Первичный ключ idsale On INSERT no action On UPDATE cascade saleitem(idsale, idwares, quantity) Первичный ключ составной idsale, idwares Внешний ключ idsale ссылается на sale (idsale) Внешний ключ idwares ссылается на wares (idwares) wares (idwares, name, description, orderprice, saleprice ,iddiscount) Первичный ключ idwares Альтернативный ключ description Внешний ключ iddiscount ссылается на discount (iddiscount) On INSERT cascade On UPDATE cascade discount (iddiscount, percent) Первичный ключ iddiscount Альтернативный ключ percent On UPDATE cascade period (idwares, idprovider, day) Первичный ключ составной idwares, idprovider Внешний ключ idwares ссылается на wares (idwares) Внешний ключ idprovider ссылается на provider (idprovider) provider(idprovider, name, agentname, phone) Первичный ключ idprovider Альтернативный ключ name, phone On INSERT cascade On UPDATE cascade order (idorder, idprovider, orddate) Первичный ключ idorder Внешний ключ idprovider ссылается на provider (idprovider) orderitem(idorder,idwares,warescount) Первичный ключ составной idorder, idwares Внешний ключ idwares ссылается на wares (idwares) Нормализация - это процесс, позволяющий гарантировать эффективность структур данных в реляционной базе данных. Отношения находятся во второй нормальной форме, и соответствуют требованиям пользовательских транзакций. Вторая нормальная форма обязана столбцу saleprice, так как он является вычисляемым столбцом и зависит от другого, неключевого, столбца.  Определение ограничений целостностиИспользование несколько проверочных ограничений. Для таблицы saleitem : [quantity]>(0) Для таблицы wares [orderprice]>(0) Для таблицы discount [percent]>=(0) [percent]<=(20) Для таблицы period [day]>(0) Для таблицы orderitem [warescount]>(0) Ограничение уникальности: Ограничение на уникальность следует использовать в тех случаях, когда нужно гарантировать, что дублирующие значения будут исключены:
4Физическое проектирование и реализация базы данных в MS SQL ServerОпределение состава и структуры таблицИнформационная система " Магазин электроники и бытовой техники " была сделана на MS SQL Server. Создание таблицы discount CREATE TABLE [dbo].[discount]( [Iddiscount] [int] IDENTITY(1,1) NOT NULL, [percent] [int] NOT NULL, CONSTRAINT [PK_discount] PRIMARY KEY CLUSTERED ( [Iddiscount] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [IX_discount] UNIQUE NONCLUSTERED ( [percent] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] Создание таблицы manager CREATE TABLE [dbo].[manager]( [Idmanager] [int] IDENTITY(1,1) NOT NULL, [name] [nvarchar](50) NOT NULL, [phone] [nvarchar](16) NULL, CONSTRAINT [PK_manager] PRIMARY KEY CLUSTERED ( [Idmanager] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [IX_manager] UNIQUE NONCLUSTERED ( [phone] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] Создание таблицы order CREATE TABLE [dbo].[order]( [Idorder] [int] IDENTITY(1,1) NOT NULL, [Idprovider] [int] NOT NULL, [orddate] [smalldatetime] NOT NULL CONSTRAINT [DF_order_orddate] DEFAULT (getdate()), CONSTRAINT [PK_order] PRIMARY KEY CLUSTERED ( [Idorder] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] Создание таблицы orderitem CREATE TABLE [dbo].[orderitem]( [Idorder] [int] NOT NULL, [Idwares] [int] NOT NULL, [warescount] [int] NOT NULL, CONSTRAINT [PK_orderitem] PRIMARY KEY CLUSTERED ( [Idorder] ASC, [Idwares] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] Создание таблицы period CREATE TABLE [dbo].[period]( [Idprovider] [int] NOT NULL, [Idwares] [int] NOT NULL, [day] [int] NOT NULL, CONSTRAINT [PK_period] PRIMARY KEY CLUSTERED ( [Idprovider] ASC, [Idwares] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] Создание таблицы provider CREATE TABLE [dbo].[provider]( [Idprovider] [int] IDENTITY(1,1) NOT NULL, [name] [nvarchar](50) NOT NULL, [agentname] [nvarchar](50) NOT NULL, [phone] [nvarchar](50) NOT NULL, CONSTRAINT [PK_provider] PRIMARY KEY CLUSTERED ( [Idprovider] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [IX_provider] UNIQUE NONCLUSTERED ( [name] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [IX_provider_1] UNIQUE NONCLUSTERED ( [phone] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] Создание таблицы sale REATE TABLE [dbo].[sale]( [Idsale] [int] IDENTITY(1,1) NOT NULL, [Idmanager] [int] NULL, [saledate] [smalldatetime] NOT NULL CONSTRAINT [DF_sale_saledate] DEFAULT (getdate()), CONSTRAINT [PK_sale] PRIMARY KEY CLUSTERED ( [Idsale] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] Создание таблицы saleitem CREATE TABLE [dbo].[saleitem]( [Idsale] [int] NOT NULL, [Idwares] [int] NOT NULL, [quantity] [int] NOT NULL, CONSTRAINT [PK_saleitem] PRIMARY KEY CLUSTERED ( [Idsale] ASC, [Idwares] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] Создание таблицы wares CREATE TABLE [dbo].[wares]( [Idwares] [int] IDENTITY(1,1) NOT NULL, [name] [nvarchar](50) NOT NULL, [description] [nvarchar](100) NOT NULL, [orderprice] [int] NOT NULL, [saleprice] AS ([orderprice]*(1.3)), [Iddiscount] [int] NOT NULL, CONSTRAINT [PK_wares] PRIMARY KEY CLUSTERED ( [Idwares] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [IX_wares] UNIQUE NONCLUSTERED ( [description] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] Реализация ограничений целостностиОбеспечение доменной целостности. Основными инструментами обеспечения доменной целостности являются ограничения проверки и значения по умолчанию. Использование проверочных ограничений. Для атрибутов quantity, orderprice, day, warescount допустимы только положительные значения отличные от нуля. Для атрибута percent допустимы только значения от 0 до 20 включительно (чтобы скидка не оказалась в убыток магазину и не была отрицательной). Использование значений по умолчанию. В столбцах saledate в таблице sale и orderdate в таблице order по умолчанию значение поля date определяется в виде текущей даты. Использование вычисляемых столбцов Столбец salecount является вычисляемым и считается по формуле: ([orderprice]*(1.3)) (накрутка магазина 30% от закупа) Обеспечение сущностной целостности. Основными инструментами обеспечения целостности сущностей являются первичные ключи и ограничения уникальности. Первичные ключи
Проектирование и разработка пользовательских представленийДобавление клиента Процедура spr_addmanager create PROCEDURE [dbo].[spr_addmanager] @Idmanager int OUT, @name nvarchar(30), @phone nvarchar(16) AS IF EXISTS(SELECT * FROM manager WHERE phone=@phone) RETURN -100 INSERT manager([name], phone) VALUES (@name, @phone) SET @Idmanager = @@IDENTITY RETURN 0 Вызов процедуры DECLARE @return_value int, @Idmanager int EXEC @return_value = spr_addmanager @name='Саудов Геннадий', @phone= '8-960-124-11-11', @Idmanager = @Idmanager OUTPUT IF @return_value = 0 BEGIN PRINT 'Менеджер успешно добавлен' SELECT @Idmanager as 'Номер менеджера' END ELSE BEGIN PRINT 'При добавлении произошла ошибка' END Удаление менеджера Процедура spr_delmanager Create procedure [dbo].[spr_delmanager] @idmanager int AS if not exists (select * from manager where idmanager=@idmanager) return -100 delete from manager where idmanager=@idmanager RETURN 0 Вызов процедуры DECLARE @return int EXEC @return = spr_delmanager @idmanager=5 IF @return = 0 BEGIN PRINT 'Менеджер успешно удален' END ELSE BEGIN PRINT 'Во время удаления произошла ошибка' END Вся информация о товаре Представление dbo.ALL_info_wares SELECT dbo.wares.name AS Наименование, dbo.wares.description AS Описание, dbo.provider.name AS Поставщик, dbo.wares.orderprice AS [Цена закупа], dbo.wares.saleprice AS [Цена продажи], dbo.discount.[percent] AS [Скидка (%)] FROM dbo.discount INNER JOIN dbo.wares ON dbo.discount.Iddiscount = dbo.wares.Iddiscount INNER JOIN dbo.period ON dbo.wares.Idwares = dbo.period.Idwares INNER JOIN dbo.provider ON dbo.period.Idprovider = dbo.provider.Idprovider Вызов представления SELECT * FROM All_info_wares Список всех заказов Представление dbo.orders_info SELECT TOP (100) PERCENT dbo.wares.name AS Наименовани, dbo.wares.description AS Описание, dbo.orderitem.warescount AS [Кол-во],dbo.wares.orderprice AS Цена, dbo.[order].orddate AS Дата FROM dbo.[order] INNER JOIN dbo.orderitem ON dbo.[order].Idorder = dbo.orderitem.Idorder INNER JOIN dbo.wares ON dbo.orderitem.Idwares = dbo.wares.Idwares ORDER BY dbo.wares.name Вызов представления SELECT * FROM orders_info Просмотр сроков поставки товаров всех фирм с указание контактной информации и стоимости товаров Представление dbo.firms_periods SELECT dbo.provider.name AS Фирма, dbo.provider.agentname AS Представитель, dbo.provider.phone AS Телефон,dbo.wares.name AS [Наименование товара], dbo.wares.description AS [Описание товара], dbo.wares.orderprice AS [Цена закупки], dbo.period.day AS [Срок ожидания] FROM dbo.provider INNER JOIN dbo.period ON dbo.provider.Idprovider = dbo.period.Idprovider INNER JOIN dbo.wares ON dbo.period.Idwares = dbo.wares.Idwares Вызов представления SELECT * FROM firms_periods Поиск продаж по промежутку даты create procedure [dbo].[spr_salesearch] @date1 datetime, @date2 datetime AS SELECT w.[name], w.description, s.saledate FROM wares w, sale s,saleitem si where s.idsale=si.idsale and si.idwares=w.idwares and saledate BETWEEN @date1 AND @date2 RETURN Вызов процедуры EXEC spr_salesearch @date1 = '10.06.2011', @date2 = '01.10.2011' Сумма выручки за заданный период Create FUNCTION [dbo].[sum] (@date1 smalldatetime, @date2 smalldatetime) RETURNS TABLE AS return ( select sum(w.saleprice-(w.saleprice*(d.[percent]*0.01))-w.orderprice)as 'сумма' from wares w inner join discount d on w.iddiscount=d.iddiscount inner join saleitem si on w.idwares=si.idwares inner join sale s on si.idsale=s.idsale where (s.saledate >= @date1) and (s.saledate <= @date2) ) Вызов select * from dbo.[sum] ('10.06.2011', '16.10.2011') Определить лучшего менеджера create FUNCTION [dbo].[bestmanager]() returns table as return( select m.[name], count(s.idsale) 'Продажи' from manager m inner join sale s on m.idmanager=s.idmanager group by m.[name] ) Вызов select * from dbo.bestmanager() order by 'Продажи' desc ЗаключениеВ ходе проекта изучила предметную область информационной системы магазина электроники и бытовой техники, в ходе проекта решила следующие задачи: Изучила предметную область информационной системы “ Магазин электроники и бытовой техники ” Описала актуальность выполняемого проекта Рассмотрела вид деятельности Определила требования к данным Создание концептуальной модели, построение ER-модели: Определила типы сущностей и типы связей, а также их атрибуты; Выявляла потенциальные ключи и выбрала первичный ключ для каждой сильной сущности. Логическое проектирование. Построение и проверка реляционной модели данных: Исключила из концептуальной модели особенностей несовместимых с реляционной моделью; Определила набор отношений, необходимого для представления сущностей, связей и атрибутов, выявленных в процессе концептуального моделирования; Проверила отношения с помощью правил нормализации; Определила ограничение целостности. Физическое проектирование и реализация базы данных в MS SQL Server : Определение состава и структуры таблиц; Реализация ограничений целостности; Проектирование и разработка пользовательских представлений. |