end

Вызов процедуры:

go

declare @cena money

execute min_c @cena output,Омск

print 'Минимальная цена'

print @cena

Результат работы процедуры приведен на рисунке 83.

Рисунок 83 - Результат работы процедуры 7.2.5

В результате работы процедуры выводится минимальная цена на товар.

Вывести количество покупателей любого товара

Программный код:

Создание процедуры:

use magazin

go

create procedure poc_t

(@poc varchar(50) output,

@p_tov varchar(40)) as

begin

SELECT @poc=COUNT(NAIMENOVANIE_POKUPATEL)

FROM POKUPATEL,TOVAR,TOVAR_POKUPATEL

WHERE POKUPATEL.COD_POKUPATEL=TOVAR_POKUPATEL.COD_POKUPATEL and TOVAR.COD_TOVARA=TOVAR_POKUPATEL.COD_TOVARA and NAZVANIE=@p_tov

End

Вызов процедуры:

go

declare @poc varchar(50)

execute poc_t @poc output,Парфюм

print 'Кол-во покупателей'

print @poc

Результат работы процедуры приведен на рисунке 84.

Рисунок 84 - Результат работы процедуры 7.2.6

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

Вывести среднюю цену всех проданных товаров определенным менеджером

Программный код:

Создание процедуры:

use magazin

go

create procedure sr_c

(@sct_m money output,

@fio_m varchar(40)) as

begin

SELECT @sct_m=AVG(CENA)

FROM TOVAR,MANAGER,PRODAGA_MANAGER,TOVAR_POKUPATEL,

PRODAGA

WHERE MANAGER.COD_MANAGER=PRODAGA_MANAGER.COD_MANAGER and PRODAGA_MANAGER.COD_PRODAGA_MANAGER=PRODAGA.COD_PRODAGA_MANAGER and PRODAGA_MANAGER.COD_TOVARA=TOVAR.COD_TOVARA and FIO_MANAGER=@fio_m

end

Вызов процедуры:

go

declare @sct_m money

execute sr_c @sct_m output,'Степанов Валерий Александрович'

print 'Средняя цена'

print @sct_m

Результат работы процедуры приведен на рисунке 85.

Рисунок 85 - Результат работы процедуры 7.2.7

В результате работы процедуры выводится информация о средней цене всех проданных товарах.

Вывести количество поставщиков любого товара

Программный код:

Создание процедуры:

use magazin

go

create procedure pos_t

(@post varchar(60) output,

@p_tov varchar(50)) as

begin

SELECT @post=COUNT(COD_POSTAVSHIK)

FROM POSTAVKA,TOVAR,POSTAVSHIK_TOVAR

WHERE POSTAVKA.COD_POSTAVSHIK_TOVAR=

POSTAVSHIK_TOVAR.COD_POSTAVSHIK_TOVAR and POSTAVSHIK_TOVAR.COD_TOVARA=TOVAR.COD_TOVARA and NAZVANIE=@p_tov

end

Вызов процедуры:

go

declare @post varchar(60)

execute pos_t @post output, Тушь

print 'Количество поставщиков'

print @post

Результат работы процедуры приведен на рисунке 86.

Рисунок 86 - Результат работы процедуры 7.2.8

В результате работы процедуры мы видим информацию о количестве поставщиков.

Вывести среднюю цену на любой товар после акции с 7% скидкой

Программный код:

Создание процедуры:

use magazin

go

create procedure acz_t

(@aczia money output,

@sk_tov varchar(10)) as

begin

SELECT @aczia=AVG (CENA*0.07)

FROM TOVAR

WHERE NAZVANIE=@sk_tov

end

Вызов процедуры:

go

declare @aczia money

execute acz_t @aczia output, Очищение

print 'Средняя цена после скидки'

print @aczia

Результат работы процедуры приведен на рисунке 86.

Рисунок 87 - Результат работы процедуры 7.2.9

В результате процедуры выводится средняя цена на любой товар после акции с 7% скидкой.

Посчитать количество букв в ФИО любого менеджера

Программный код:

Создание процедуры:

use magazin

go

create procedure kol_b

(@kol_fio int output,

@b_fio varchar(50)) as

begin

SELECT @kol_fio=LEN(FIO_MANAGER)

FROM MANAGER

WHERE FIO_MANAGER=@b_fio

end

Вызов процедуры:

go

declare @kol_fio int

execute kol_b @kol_fio output,'Деньгина Светлана Юрьевна'

print 'Количество букв'

print @kol_fio

Результат работы процедуры приведен на рисунке 88.

Рисунок 88 - Результат работы процедуры 7.2.10

В результате работы процедуры мы можем увидеть количество букв в ФИО менеджера.

Вывести количество поставщиков из любого города

Программный код:

Создание процедуры:

use magazin

go

create procedure kp_g

(@kol_post varchar(50) output,

@k_p varchar(40)) as

begin

SELECT @kol_post=COUNT(COD_POSTAVSHIK)

FROM POSTAVSHIK,GOROD

WHERE POSTAVSHIK.COD_GOROD=GOROD.COD_GOROD and NAIMENOVANIE_GORODA=@k_p

End

Вызов процедуры:

go

declare @kol_post varchar(50)

execute kp_g @kol_post output,Москва

print 'количество поставщиков'

print @kol_post

Результат работы процедуры приведен на рисунке 89.

Рисунок 89 - Результат работы процедуры 7.2.11

В результате работы процедуры выводится количество поставщиков из города Москва.

Вывести количество продаж любого покупателя

Программный код:

Создание процедуры:

use magazin

go

create procedure kol_prod

(@k_p int output,

@k_pok varchar(50)) as

begin

SELECT @k_p=COUNT(POKUPATEL.COD_POKUPATEL)

FROM POKUPATEL,PRODAGA,TOVAR_POKUPATEL

WHERE POKUPATEL.COD_POKUPATEL=TOVAR_POKUPATEL.COD_POKUPATEL and TOVAR_POKUPATEL.COD_TOVAR_POKUPATEL=

PRODAGA.COD_TOVAR_POKUPATEL and NAIMENOVANIE_POKUPATEL=@k_pok

end

Вызов процедуры:

go

declare @k_p int

execute kol_prod @k_p output,'Рив Гош'

print 'Количество продаж'

print @k_p

Результат работы процедуры приведен на рисунке 90.

Рисунок 90 - Результат работы процедуры 7.2.12

В результате выполнения процедуры мы видим количество продаж.

Вывести сумму купленных товаров, покупателями из любого города

Программный код:

Создание процедуры:

use magazin

go

create procedure sum_tov

(@s_t money output,

@n_gor varchar(20)) as

begin

SELECT @s_t=SUM(CENA)

FROM TOVAR,GOROD,POKUPATEL,TOVAR_POKUPATEL

WHERE TOVAR.COD_TOVARA=TOVAR_POKUPATEL.COD_TOVARA and POKUPATEL.COD_POKUPATEL=TOVAR_POKUPATEL.COD_POKUPATEL

and POKUPATEL.COD_GOROD=GOROD.COD_GOROD and NAIMENOVANIE_GORODA=@n_gor

end

Вызов процедуры:

go

declare @s_t money

execute sum_tov @s_t output,Кемерово

print 'Сумма купленных товаров'

print @s_t

Результат работы процедуры приведен на рисунке 91.

Рисунок 91 - Результат работы процедуры 7.2.13

В результате выполнения процедуры выводится сумма купленных товаров из города Кемерово.

Вывести количество продаж для любого менеджера

Программный код:

Создание процедуры:

use magazin

go

create procedure kol_p

(@k_pr int output,

@fio_m varchar(40)) as

begin

SELECT @k_pr=COUNT(PRODAGA.COD_PRODAG)

FROM MANAGER,PRODAGA,PRODAGA_MANAGER

WHERE MANAGER.COD_MANAGER=PRODAGA_MANAGER.COD_MANAGER and PRODAGA.COD_PRODAG=PRODAGA_MANAGER.COD_PRODAG

and FIO_MANAGER=@fio_m

end

Вызов процедуры:

go

declare @k_pr int

execute kol_p @k_pr output,'Гаер Виталий Анатольевич'

print 'количество продаж'

print @k_pr

Результат работы процедуры приведен на рисунке 92.

Рисунок 92 - Результат работы процедуры 7.2.14

В результате выполнения процедуры выводится количество продаж менеджера.

Вывести количество поставщиков, которые поставляли товар в определенную дату

Программный код:

Создание процедуры:

use magazin

go

create procedure k_p

(@k_post varchar(50) output,

@per date) as

begin

SELECT @k_post=COUNT(NAIMENOVANIE_POSTAVSHIK)

FROM POSTAVSHIK,POSTAVKA,POSTAVSHIK_TOVAR

WHERE POSTAVSHIK.COD_POSTAVSHIK=

POSTAVSHIK_TOVAR.COD_POSTAVSHIK and POSTAVKA.COD_POSTAVSHIK_TOVAR=

POSTAVSHIK_TOVAR.COD_POSTAVSHIK_TOVAR and DATA_POSTAVKI=@per

End

Вызов процедуры:

go

declare @k_post varchar(50)

execute k_p @k_post output,'2013-05-10'

print 'Кол-во поставщиков'

print @k_post

Результат работы процедуры приведен на рисунке 93.

Рисунок 93 - Результат работы процедуры 7.2.15

В результате выполнения процедуры мы видим информацию о количестве поставщиков, которые поставляли товар 10 мая 2013 года.

Информация об определенном товаре

Программный код:

Создание процедуры:

use magazin

go

create procedure naz_tov

@tovar varchar(20)

as

select*

from TOVAR

where NAZVANIE=@tovar

go

execute naz_tov @tovar='Парфюм'

Результат работы процедуры приведен на рисунке 94.

Рисунок 94 - Результат работы процедуры 7.2.16

В результате выполнения процедуры выводится информация об определенном товаре.

Информация о дате продажи любого товара

Программный код:

Создание процедуры:

use magazin

go

create procedure naz_t

@t_b varchar(30) as

select NAZVANIE, DATA_PRODAG

from TOVAR, PRODAGA,TOVAR_POKUPATEL

where TOVAR.COD_TOVARA=TOVAR_POKUPATEL.COD_TOVARA and PRODAGA.COD_TOVAR_POKUPATEL=

TOVAR_POKUPATEL.COD_TOVAR_POKUPATEL and TOVAR.NAZVANIE like @t_b

go

execute naz_t @t_b ='Б%'

Результат работы процедуры приведен на рисунке 95.

Рисунок 95 - Результат работы процедуры 7.2.17

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

Информация о менеджере по номеру телефона

Программный код:

Создание процедуры:

use magazin

go

create procedure in_m

@n_m varchar(40) as

select *

from MANAGER

where TELEFON like @n_m

go

execute in_m @n_m=89069067157

Результат работы процедуры приведен на рисунке 96

Рисунок 96 - Результат работы процедуры 7.2.18

В результате выполнения процедуры мы получили информацию о менеджере

Информация о товаре, купленного в определенный месяц

Программный код:

Создание процедуры:

use magazin

go

create procedure in_t

@period varchar(10) as

SELECT TOVAR.COD_TOVARA,NAZVANIE,CENA,DATA_PRODAG

FROM TOVAR,POKUPATEL,PRODAGA,TOVAR_POKUPATEL

WHERE POKUPATEL.COD_POKUPATEL=TOVAR_POKUPATEL.COD_POKUPATEL and TOVAR.COD_TOVARA=TOVAR_POKUPATEL.COD_TOVARA and PRODAGA.COD_TOVAR_POKUPATEL=

TOVAR_POKUPATEL.COD_TOVAR_POKUPATEL and DATA_PRODAG like @period

go

execute in_t @period='____-04-__'

Результат работы процедуры приведен на рисунке 97.

Рисунок 97 - Результат работы процедуры 7.2.19

В результате выполнения процедуры мы видим информацию о товаре, купленного в апреле.

Информация о менеджере по году его рождения

Программный код:

Создание процедуры:

use magazin

go

create procedure in_m

@d_r varchar(10) as

select *

from MANAGER

WHERE DATAR_MANAGER like @d_r

go

execute in_m @d_r='1992%'

Результат работы процедуры приведен на рисунке 98.

Рисунок 98 - Результат работы процедуры 7.2.20

В результате работы процедуры выводится информация о менеджере по году рождения

7.3 Представлени

Представление данных - это окно, через которое видна часть базы данных. Представления данных полезны для поддержания конфиденциальности путем ограничения доступа к определенным частям базы данных и для упрощения выполнения часто используемых запросов [7, стр.68]. Например, для того чтобы сохранить конфиденциальность, мы можем создать представление данных, показывающее всю информацию о работнике, кроме его ставки.

Для создания представления используется оператор CREATE VIEW, далее по своему составу представления особых отличий от запросов не имеют.

Вывести информацию обо всех менеджерах магазина

Программный код:

use magazin

go

IF OBJECT_ID ('dbo.info_manager') IS NOT NULL

DROP VIEW Info_manager

go

CREATE view Info_manager

as

SELECT distinct FIO_MANAGER as [Ф.И.О. менеджера], TELEFON as [Номер телефона],

NAZVANIE as [Проданный товар], PRODAGA.KOLVO as [Количество]

FROM MANAGER,PRODAGA_MANAGER,PRODAGA,TOVAR_POKUPATEL TOVAR

WHERE MANAGER.COD_MANAGER=PRODAGA_MANAGER.COD_MANAGER and PRODAGA_MANAGER.COD_PRODAG=PRODAGA.COD_PRODAG

and PRODAGA.COD_TOVAR_POKUPATEL=

TOVAR_POKUPATEL.COD_TOVAR_POKUPATEL and TOVAR_POKUPATEL.COD_TOVARA=TOVAR.COD_TOVARA

go

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

Рисунок 99 - Результат выполнения представления 7.3.1

В результате выполнения представления, мы видим информацию обо всех менеджерах магазина

Вывести информацию обо всех товарах в магазине

Программный код:

use magazin

go

IF OBJECT_ID ('dbo.info_tovar') IS NOT NULL

DROP VIEW info_tovar

go

CREATE view info_tovar

as

SELECT distinct NAZVANIE as [Название товара], (CAST(TOVAR.KOLVO as char(6))+'шт.')as [Кол-во],

(CAST(CENA as char(10))+' руб.')as [Цена], NAIMENOVANIE_POSTAVSHIK as [Поставщик],

NAIMENOVANIE_POKUPATEL as [Покупатель], ('доставлено ' + CAST(DATA_PRODAG as char)) as [Дата]

FROM TOVAR, POSTAVSHIK, POSTAVSHIK_TOVAR, POKUPATEL, PRODAGA,TOVAR_POKUPATEL

WHERE POKUPATEL.COD_POKUPATEL=TOVAR_POKUPATEL.COD_POKUPATEL and TOVAR_POKUPATEL.COD_TOVARA=TOVAR.COD_TOVARA

and TOVAR.COD_TOVARA=POSTAVSHIK_TOVAR.COD_TOVARA and POSTAVSHIK_TOVAR.COD_POSTAVSHIK=

POSTAVSHIK.COD_POSTAVSHIK and PRODAGA.COD_POKUPATEL_TOVAR=

POKUPATEL_TOVAR.COD_POKUPATEL_TOVAR

go

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

Рисунок 100 - Результат выполнения представления 7.3.2

В результате выполнения представления выводится информация обо всех товарах в магазине

Вывести информацию о поставщиках

Программный код:

use magazin

go

IF OBJECT_ID ('dbo.info_postavchik') IS NOT NULL

DROP VIEW info_postavchik

go

CREATE view info_postavchik

as

SELECT distinct NAIMENOVANIE_POSTAVSHIK as [Поставщик], NAZVANIE as [Название товара],

TELEFON as [Телефон], ADRES as [Адрес],NAIMENOVANIE_GORODA as [Город]

FROM POSTAVSHIK, POSTAVSHIK_TOVAR, TOVAR, GOROD

WHERE GOROD.COD_GOROD=POSTAVSHIK.COD_GOROD and POSTAVSHIK.COD_POSTAVSHIK=POSTAVSHIK_TOVAR.COD_POSTAVSHIK

and POSTAVSHIK_TOVAR.COD_TOVARA=TOVAR.COD_TOVARA

go

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

Рисунок 101 - Результат выполнения представления 7.3.3

В результате выполнения представления видна информация о поставщиках

7.4 Триггеры

Триггеры - особый инструмент SQL-сервера, используемый для поддержания целостности данных в базе данных. С помощью ограничений целостности, правил и значений по умолчанию не всегда можно добиться нужного уровня функциональности. Часто требуется реализовать сложные алгоритмы проверки данных, гарантирующие их достоверность и реальность [6, стр.71]. Триггеры можно рассматривать как своего рода фильтры, вступающие в действие после выполнения всех операций в соответствии с правилами, стандартными значениями и т.д.

Триггер. Запрещается удалять данные из таблицы MANADGER

Программный код:

drop trigger udalenie

go

CREATE trigger udalenie

on dbo.MANAGER

after delete

as begin

set nocount on;

if (select COD_MANAGER from deleted)='4'

rollback

print 'Удаление данных о менеджере с кодом 4 запрещено'

end

go

use magazin

DELETE dbo.MANAGER

WHERE dbo.MANAGER.COD_MANAGER='4'

Результат выполнения триггера представлен на рисунке 102.

Рисунок 102 - Результат выполнения триггера 7.4.1

В результате выполнения триггера выводится сообщение о запрете на удаление данных.

Триггер. Запрет на добавление цены товара меньше 100 рублей в таблицу TOVAR

Программный код:

drop trigger vstavka

go

CREATE trigger vstavka

on dbo.TOVAR

after insert

as begin

set nocount on;

if (select CENA from inserted)<100

rollback

print 'Вы не можете добавить запись, если цена товара меньше 100 рублей'

end

go

use magazin

INSERT dbo.TOVAR values (900,'Очищение',90,35)

Результат выполнения триггера приведен на рисунке 103



Рисунок 103 - Результат выполнения триггера 7.4.2

В результате выполнения триггера, выводится сообщение о запрете на добавление записи в таблицу, если цена товара меньше 100 рублей

7.5 Курсор

Курсор на получение накладной на поступивший товар

Программный код:

use magazin

go

declare

@d_post varchar (10),

@c_post varchar (7),

@fio_m varchar (50),

@n_post varchar(20),

@ad_p varchar (30),

@gor_p varchar (20),

@tel varchar (15),

@cod_t varchar(10),

@naz varchar (20),

@kol_p varchar (10),

@cena varchar (10),

@s_t varchar (10)

Declare Cursor_mag Cursor Local for

select FORMAT((CENA*POSTAVKA.KOLVO),'0.0') as summa,DATA_POSTAVKI,COD_POSTAVKI,FIO_MANAGER,NAIMENOVANIE_POSTAVSHIK,POSTAVSHIK.ADRES,

NAIMENOVANIE_GORODA,POSTAVSHIK.TELEFON,TOVAR.COD_TOVARA,NAZVANIE,POSTAVKA.KOLVO,TOVAR.CENA

from POSTAVKA,MANAGER,POSTAVSHIK,TOVAR,PRODAGA,PRODAGA_MANAGER,GOROD

where GOROD.COD_GOROD=POSTAVSHIK.COD_GOROD and POSTAVKA.COD_POSTAVSHIK=POSTAVSHIK.COD_POSTAVSHIK

and TOVAR.COD_TOVARA=POSTAVKA.COD_TOVARA and PRODAGA.COD_TOVARA=TOVAR.COD_TOVARA

and PRODAGA_MANAGER.COD_PRODAG=PRODAGA.COD_PRODAG and MANAGER.COD_MANAGER=PRODAGA_MANAGER.COD_MANAGER

and COD_POSTAVKI=14

Open Cursor_mag

FETCH

NEXT

FROM Cursor_mag

INTO

@s_t,@d_post,@c_post,@fio_m,@n_post,@ad_p,@gor_p,@tel,@cod_t,@naz,@kol_p,@cena

print

НАКЛАДНАЯ

№ '+@c_post+'

_____

Кому '+@fio_m+'

От кого '+@n_post+'

Адрес '+@ad_p+' Город '+@gor_p+'

Контактный телефон '+@tel+'

+-----------+-------------------------+-----------------+-----------+------------------+

| | | | | |

| Код | Наименование | Количество | Цена | Сумма |

| Товара | | (шт.) | (руб.) | (руб.) |

| | | | | |

+-----------+-------------------------+-----------------+------------+-----------------+

|'+@cod_t+' | '+@naz+' | '+@kol_p+' | '+@cena+' | '+@s_t+' |

+-----------+-------------------------+--------------------+------------+--------------+

CLOSE Cursor_mag

DEALLOCATE Cursor_mag

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

Рисунок 104 - Результат выполнения курсора 7.5.1

В результате выполнения курсора, мы видим заполненную форму накладной на поступивший товар

Заключение

В ходе выполнения курсовой работы я рассмотрела структуру работы оптового магазина и, проанализировав полученную информацию , - создала базу данных таким образом чтобы внесенная в нее информация была пригодна для реализации поставленных задач. В ходе выполнения курсовой работы были продемонстрированы возможности СУБД Microsoft SQL Server 2008 и реализованы все поставленные задачи.

Список использованных источников

1 Глушаков С.В., Ломотько Д..В. Базы данных: Учебный курс. - Харьков: Фолио, 2000

2 Грабер М. Справочное руководство по SQL. - М.: Лори, 1998;

3 Дейт К. Дж. Введение в системы баз данных. - К.: Диалектика, 1998

4 Диго С.М. Проектирование и использование баз данных: Учебник. - М.: Финансы и статистика, 1995

5 Дунаев В.В. Базы данных язык SQL - СПб.: БХВ Петербург, 2006. - 288с

6 Коннолли Т. , Бегг К. , Страчан А. Базы данных: Проектирование, реализация и сопровождение: Теория и практика. - К,М,СПб: Вильямс, 2000

7 Марков, А.С. Базы данных: Введение в теорию и методологию: Учебник.- М.: Финансы и статистика, 2004

8 Родигин Л. А. Базы данных для карманного персонального компьютера: Учеб.-метод. пособие для вузов. - М.: КноРус, 2008

9 Пашков П.М., Методические пособия

10 (URL:http://msdn.microsoft.com/ru-ru/library/bb418440%28v=SQL.10%29.aspx)

Размещено на Allbest.ru