Заполнение базы данных тестовыми данными

 

Для корректной работы базы данных необходимо заполнить её тестовыми данными.

1. Заполнение таблицы «Сотрудники» тестовыми данными.

INSERT СОТРУДНИКИ

(ОтделID, Фамилия, Имя, Отчество, Должность, Зарплата, Премия, Датаприеманаработу, Семейноеположение, Датарождения, Адрес, Телефон)

VALUES

(1,'Перминов', 'Алексей', 'Владимирович', 'Менеджер по продажам', 20000, 5000, GETDATE(),'Женат', '01/10/1990','Зянкина 9', '(953)1416433'),

(2,'Туманов', 'Георгий', 'Викторович', 'Менеджер по продажам', 20000, 5000, GETDATE(),'Женат', '04/10/1990','Воровского 29', '(912)4533554'),

(1,'Кочуров', 'Дмитрий', 'Николаевич', 'Системный администратор', 26000, 7000, GETDATE(),'Женат', '04/10/1990','Пугачева 29', '(951)7433545')GO

 

2. Заполнение таблицы «Покупатели» тестовыми данными.

INSERT ПОКУПАТЕЛИ

(Фамилия, Имя, Отчество, Адрес, Город, Телефон, Дата_регистрации)

VALUES

('Савиных','Викторович','Павлович','Труда 21а, 137','Киров','(912)3569385',DATEADD(DAY, -85, GETDATE())),

('Васильева','Алефтина','Генадьевна','Мопра 3а, 12','Пермь','(922)4622225',DATEADD(DAY, -35, GETDATE()))

GO

 

3. Заполнение таблицы «Товары» тестовыми данными.

INSERT ТОВАРЫ

(Название)

VALUES

('Коптильня дымка'), ('Коптильня ханхи'), ('Пивоварня Золингер'), ('Шампура столичные'), ('Дымогенератор дымка')

GO

 

4. Заполнение таблицы «Детали товара» тестовыми данными.

INSERT ДЕТАЛИТОВАРА

(ID, Цвет, материал, текстовоеописание)

VALUES

(1, 'Серый', 'Нержавеющая сталь','Коптильня преднозначена для горячего копчения'),

(2, 'Черный', 'Черная сталь','Коптильня преднозначена для горячего копчения'),

(3, 'Белый', 'Черная сталь','Пивоварня преднозначена для изготовления пивного сусла'),

(4, 'Стальной', 'Черная сталь','Шампура преднозначены для любителей отдыха на природе'),

(5, 'Стальной', 'Черная сталь','Дымогенератор преднозначен для производства дыма при холодном копчении для ')

GO

 

5. Заполнение таблицы «Склад» тестовыми данными.

INSERT СКЛАД

(ТоварID, Количество)

VALUES

(1, 20), (2, 10), (3, 7), (4, 8), (5, 9)

GO

 

6. Заполнение таблицы «Заказы» тестовыми данными.

INSERT ЗАКАЗЫ

(СотрудникID, ПокупательID, Датаоформления)

VALUES

(22,2, DATEADD(DAY, -85, GETDATE())), (24,1, DATEADD(DAY, -85, GETDATE()))

GO

 

7. Заполнение таблицы «Детали заказа» тестовыми данными.

INSERT ДЕТАЛИЗАКАЗА

(ЗаказID, Количествопозиций, ТоварID, Количествопродонного, Цена)

VALUES

(4,2,1,1,295), (5,1,2,1,445)

GO

 

8. Заполнение таблицы «Отдел» тестовыми данными.

INSERT ОТДЕЛ

(ID, Названиеотдела)

VALUES

(1, 'ОТДЕЛ ПРОДАЖ'), (2, 'ОТДЕЛ ПОДДЕРЖКИ')

GO

 

После выполнение вышеуказанных команд, сформируется готовая к работе база данных интернет-магазина. Чтобы просмотреть внесенные данные, необходимо выполнить следующие команду:

 

Выполнение команды на вывод таблиц: «СОТРУДНИКИ» «Покупатели», «Склад», «Отдел», «Заказы», «Детализаказа», «Товары», «Деталитовара».

 

SELECT * FROM СОТРУДНИКИ

SELECT * FROM ПОКУПАТЕЛИ

SELECT * FROM СКЛАД

SELECT * FROM ОТДЕЛ

SELECT * FROM ЗАКАЗЫ

SELECT * FROM ДЕТАЛИЗАКАЗА

SELECT * FROM ТОВАРЫ

SELECT * FROM ДЕТАЛИТОВАРА

Эта команда позволит вывести на экран пользователя все созданные таблицы с заполненными данными (см. рис. 3).

Рис.3. Таблицы с заполненными данными

 

 

Создание логических триггеров

 

С помощью триггеров можно проводить “каскадное” изменение данных в связанных таблицах, что обеспечит ссылочную целостность данных в базе.

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

 

Создание триггера, с помощью которого можно удалить товар с условиями.

 

CREATE TRIGGER УДАЛЕНИЯПРОДУКТА

ON ТОВАРЫ

INSTEAD OF DELETE

AS

IF @@ROWCOUNT = 0

RETURN

 

SET NOCOUNT ON

 

IF EXISTS (SELECT 1 FROM ДЕТАЛИЗАКАЗА od

JOIN deleted d

ON od.ТоварID = d.ID)

RAISERROR('Товар не может быть удален т.к. стоит в заказах', 10, 1)

ELSE IF EXISTS (SELECT 1 FROM СКЛАД s

JOIN deleted d

ON s.ТоварID = d.ID

WHERE s.Количество <> 0)

RAISERROR('Товар не может быть удален т.к. есть остаток на складе', 10, 2)

ELSE

DELETE ТОВАР WHERE ID IN (SELECT ID FROM deleted)

GO

Эта команда создаст триггер, который удаляет товары из таблицы товары, с условиями:

· удаляемый товар отсутствует в заказах;

· остаток товара на складе нулевой.

 

Создание запросов

 

Запросы − это объект базы данных, который служит для извлечения данных из таблиц и предоставления их пользователю в удобном виде. Особенность запросов состоит в том, что они черпают данные из базовых таблиц и создают на их основе временную таблицу. Применение запросов позволяет избежать дублирования данных в таблицах и обеспечивает максимальную гибкость при поиске и отображении данных в базе данных [10].

1. Создание запроса, с помощью которого можно вывести информацию о заказах.

 

SELECT c.Фамилия +' '+ c.Имя +' '+c.Отчество AS 'Заказы',

o.СотрудникID Сотрудники, Датаоформления, p.НАЗВАНИЕ, pd.Текстовоеописание, od.Цена

FROM Покупатели c INNER JOIN Заказы o

ON c.ID = o.ПокупательID

INNER JOIN Детализаказа od

ON o.ID = od.ЗаказID

INNER JOIN ТОВАРЫ p

ON od.ТоварID = p.ID

INNER JOIN Деталитовара pd

ON p.ID = pd.ID

WHERE o.СотрудникID is NULL;

 

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

 

2. Создание запроса, с помощью которого можно вывести все продажи за последние два месяца в хронологическом порядке.

 

SELECT Датаоформления, c.Имя +' '+ c.Фамилия +' '+ c.Отчество AS Покупатели,

p.Название, pd.ТоварID, od.Количествопродонного, od.Цена

FROM Покупатели c

INNER JOIN ЗАКАЗЫ o

ON c.ID = o.ПокупательID

INNER JOIN Детализаказа od

ON o.ID = od.ЗаказID

INNER JOIN ТОВАРЫ p

ON od.ТоварID = p.ID

INNER JOIN ДЕТАЛИЗАКАЗА pd

ON p.ID = pd.ЗаказID

WHERE Датаоформления BETWEEN DATEADD(month, -2, GETDATE()) AND GETDATE()

ORDER BY Датаоформления;

Эта команда создаст запрос, который выводит информацию о продажах за последние два месяца в хронологическом порядке.

 

Создание представлений

 

Представление – это виртуальная (логическая) таблица, представляющая собой поименованный запрос (синоним к запросу), который будет подставлен как подзапрос при использовании представления [11].

 

1. Создание представления, с помощью которого можно вывести данные о товаре.

CREATE VIEW Представлениеописаниетотовара

AS

SELECT p.ID, НАЗВАНИЕ, Цвет, Материал, Текстовоеописание

FROM ТОВАРЫ p

LEFT JOIN ДЕТАЛИТОВАРА pd

ON p.ID = pd.ID

GO

 

SELECT * FROM Представление_описание_тотовара

GO

 

Эта команда создаст представление, которое выводит информацию об атрибутах сущности «Товары»: ID, название, цвет, описание товара.

 

2. Создание представления, с помощью которого можно вывести данные о количестве проданного товара и остатках на складе.

CREATE VIEW представление_остатки

AS

SELECT p.ID, p.НАЗВАНИЕ, s.Количество Остаток,

(SELECT SUM(od.Количествопродонного) FROM ДЕТАЛИЗАКАЗА od

WHERE od.ТоварID = p.ID) Продано

FROM ТОВАРЫ p

LEFT JOIN СКЛАД s

ON p.ID = s.ТоварID

GO

SELECT * FROM представление_остатки

GO

 

Эта команда создаст представление, которое выводит информацию о продажах и остатках товара: ID, название, остаток товара на складе.

 

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

 

Хранимая процедура − объект базы данных, представляющий собой набор SQL-инструкций, который компилируется один раз и хранится на сервере. Хранимые процедуры очень похожи на обыкновенные процедуры языков высокого уровня, у них могут быть входные и выходные параметры и локальные переменные, в них могут производиться числовые вычисления и операции над символьными данными, результаты которых могут присваиваться переменным и параметрам [12].

 

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

 

CREATE PROC процедурапоиска_покупателя

@Фамилия nvarchar(20) = '%',

@Город nvarchar(20) = '%'

AS

SET NOCOUNT ON

 

SELECT c.Фамилия + ' ' + c.Имя + ' ' + c.Отчество Покупатели,

o.Датаоформления,

p.НАЗВАНИЕ, od.Количествопродонного, od.Цена

FROM ПОКУПАТЕЛИ c

JOIN ЗАКАЗЫ o

ON c.ID = o.ПокупательID AND c.Город LIKE @Город

JOIN ДЕТАЛИЗАКАЗА od

ON o.ID = od.ЗаказID

JOIN ТОВАРЫ p

ON od.ТоварID = p.ID

WHERE c.Фамилия LIKE @Фамилия

GO

 

EXEC процедурапоиска_покупателя 'Кр%'

EXEC процедурапоиска_покупателя @Город = 'Л%'

GO

Эта команда создаст хранимую процедуру, с помощью которой можно производить поиск покупателя по фамилии и/или городу.

 

 

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

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

ЗАКЛЮЧЕНИЕ

 

Темой данной курсовой работы являлось создание базы данных интернет-магазина для ООО «Приволжской Механический Завод».

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

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

Практическая реализация спроектированной базы данных была произведена средствами интегрированной среды SQL Server Management Studio, которая сочетает в себе обширную группу графических инструментов с рядом отличных редакторов сценариев для обеспечения доступа, настройки, администрирования, разработки всех компонентов SQL Server и управления ими.

Для обеспечения взаимодействия (обмена данными) между пользователем и базой данных были созданы специальные объекты: запросы, хранимые процедуры и триггеры.

Итогом выполненной работы является созданная и апробированная база данных интернет-магазина для ООО «Приволжской Механический Завод.

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

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

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

 

1. Грофф, Д.Р. Энциклопедия SQL / Д.Р. Грофф, П.Н. Вайнберг. – СПб.: Изд-во «Питер», 2003. – 896 с.

2. Дейт, К.Д. Введение в системы баз данных / К.Д. Дейт. – М.: Изд-во «Вильямс», 2003. – 1328 с.

3. Кириллов В.В. Введение в реляционные базы данных / В.В. Кириллов, Г.Ю. Громов. – СПб.: Изд-во «БХВ–Петербург», 2009. – 464 с.

4. Когаловский, М.Р. Энциклопедия технологий баз данных / М.Р. Когаловский. – М.: Финансы и статистика, 2002. – 800 с.

5. Корнеев, В.В. Базы данных. Интеллектуальная обработка информации / В.В. Корнеев, А. Гареев, С. Васютин, В. Райх. – М.: Изд-во «Нолидж», 2001. – 496 с.

6. Крёнке, Д.М. Теория и практика построения баз данных / Д.М. Крёнке. – СПб.: Изд-во «Питер», 2003. – 800 с.

7. Хомоненко, А.Д. Базы данных: Учебник для высших учебных заведений / А.Д. Хомоненко, В. Цыганков, В. Мальцев. – СПб.: Изд-во «Корона», 2002. – 672 с.

8. Сеть разработчиков Microsoft [Электронный ресурс]. – Альбукерке: Майкрософт, 1975-. – Режим доступа: http://msdn.microsoft.com, свободный. – Загл. с экрана.

9. ЦИТ Форум [Электронный ресурс]. – М.: Центр информационных технологий, 1992 -. – Режим доступа: http://citforum.ru, свободный. – Загл. с экрана.

10. Википедия [Электронный ресурс]. – Ашбурн: Википедия, 2001 -. – Режим доступа: https://wikipedia.org, свободный. – Загл. с экрана.

11. Elibrary.ru: научная электронная библиотека [Электронный ресурс]. – М.: Интра- Плюс, 1997 -. – Режим доступа: http://www.elibrary.ru, свободный. – Загл. с экрана.

ПРИЛОЖЕНИЕ

Листинг кода базы данных

 

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

CREATE DATABASE интернет_магазин5

COLLATE Cyrillic_General_CI_AS

GO

 

-- Код создания таблиц

CREATE TABLE ПОКУПАТЕЛИ

(ID int NOT NULL IDENTITY,

Фамилия nvarchar(20) NULL,

Имя nvarchar(20) NULL,

Отчество nvarchar(20) NULL,

Адрес nvarchar(50) NULL,

Город nvarchar(20) NULL,

Телефон char(12) NULL,

Дата_регистрации date DEFAULT GETDATE(),)

GO

CREATE TABLE СОТРУДНИКИ

(ID int NOT NULL IDENTITY,

ОтделID int NOT NULL,

Фамилия nvarchar(20) NOT NULL,

Имя nvarchar(20) NULL,

Отчество nvarchar(20) NOT NULL,

Должность nvarchar(25) NOT NULL,

Зарплата money NOT NULL,

Премия money NULL,

Датаприеманаработу date NOT NULL,

Семейноеположение varchar(10) NOT NULL,

Датарождения date NOT NULL,

Адрес nvarchar(50) NOT NULL,

Телефон char(12) NOT NULL)

GO

CREATE TABLE ОТДЕЛ

(

ID int NOT NULL,

Названиеотдела nvarchar(20) NOT NULL,

 

)

GO

CREATE TABLE ТОВАРЫ

(

ID int NOT NULL IDENTITY,

НАЗВАНИЕ nvarchar(50) NOT NULL

)

GO

CREATE TABLE ДЕТАЛИТОВАРА

(

ID int NOT NULL,

Цвет nchar(20) NULL,

Материал nchar(20) NULL,

Текстовоеописание nvarchar(max) NULL

)

GO

CREATE TABLE СКЛАД

(

ТоварID int NOT NULL,

Количество int DEFAULT 0

)

GO

CREATE TABLE ЗАКАЗЫ

(

ID int NOT NULL IDENTITY,

ПокупательID int NULL,

СотрудникID int NULL,

Датаоформления date DEFAULT GETDATE()

)

GO

CREATE TABLE ДЕТАЛИЗАКАЗА

(

ЗаказID int NOT NULL,

Количествопозиций int NOT NULL,

ТоварID int NULL,

Количествопродонного int NOT NULL,

Цена money NOT NULL,

Стоимостьзаказа AS CONVERT(money, Количествопродонного*Цена)

)

GO

 

-- Код установки связи между таблицами

ALTER TABLE ПОКУПАТЕЛИ ADD

CONSTRAINT Первичный_ключ_покупатели PRIMARY KEY(ID)

GO

ALTER TABLE СОТРУДНИКИ ADD

CONSTRAINT Первичный_ключ_сотрудники PRIMARY KEY(ID)

GO

ALTER TABLE ОТДЕЛ ADD

CONSTRAINT Уникальный_ключ_отдел UNIQUE(ID)

GO

ALTER TABLE СОТРУДНИКИ ADD

CONSTRAINT внешний_ключ_отделы_к_сотрудникам FOREIGN KEY (ОтделID)

REFERENCES ОТДЕЛ(ID)

ON DELETE CASCADE

GO

ALTER TABLE ТОВАРЫ ADD

CONSTRAINT первичный_ключ_товары PRIMARY KEY (ID)

GO

ALTER TABLE ДЕТАЛИТОВАРА ADD

CONSTRAINT Уникальный_ключ_деталитовара UNIQUE(ID)

GO

ALTER TABLE ДЕТАЛИТОВАРА ADD

CONSTRAINT внешнийключ_деталитовара_к_Товарам FOREIGN KEY (ID)

REFERENCES ТОВАРЫ(ID)

ON DELETE CASCADE

GO

ALTER TABLE СКЛАД ADD

CONSTRAINT Уникальный_ключ_склад UNIQUE(ТоварID)

GO

ALTER TABLE СКЛАД ADD

CONSTRAINT внешний_ключ_склад_к_товарам FOREIGN KEY (ТоварID)

REFERENCES ТОВАРЫ(ID)

ON DELETE CASCADE

GO

ALTER TABLE ЗАКАЗЫ ADD

CONSTRAINT первичный_ключ_заказы PRIMARY KEY (ID)

GO

ALTER TABLE ЗАКАЗЫ ADD CONSTRAINT

внешнийключ_заказы_к_покупателям FOREIGN KEY(ПокупательID)

REFERENCES ПОКУПАТЕЛИ(ID)

ON DELETE SET NULL

GO

ALTER TABLE ЗАКАЗЫ ADD CONSTRAINT

внешний_ключ_заказы_к_сотрудники FOREIGN KEY(СотрудникID)

REFERENCES СОТРУДНИКИ(ID)

ON DELETE SET NULL

GO

ALTER TABLE ДЕТАЛИЗАКАЗА ADD CONSTRAINT

первичныйключ_детализаказа PRIMARY KEY

(ЗаказID,Количествопозиций)

GO

ALTER TABLE ДЕТАЛИЗАКАЗА ADD CONSTRAINT

внешнийключ_детализаказа_к_заказам FOREIGN KEY(ЗаказID)

REFERENCES ЗАКАЗЫ(ID)

ON DELETE CASCADE

GO

ALTER TABLE ДЕТАЛИЗАКАЗА ADD CONSTRAINT

внешний_ключ_детализаказа_к_товары FOREIGN KEY(ТоварID)

REFERENCES ТОВАРЫ(ID)

ON DELETE SET NULL

GO

 

--. Код создания пользовательских ограничений

ALTER TABLE СОТРУДНИКИ

ADD CONSTRAINT ограничение_сотрудники_телефон

CHECK (ТЕЛЕФОН LIKE '([0-9][0-9][0-9])[0-9][0-9][0-9][0-9][0-9][0-9][0-9]') GO

ALTER TABLE ПОКУПАТЕЛИ

ADD CONSTRAINT ограничение_покупатели_телефон

CHECK (ТЕЛЕФОН LIKE '([0-9][0-9][0-9])[0-9][0-9][0-9][0-9][0-9][0-9][0-9]') GO

ALTER TABLE СОТРУДНИКИ

ADD CONSTRAINT ограничение_сотрудники_деньрождение

CHECK (Датарождения BETWEEN DATEADD(YEAR, -50, GETDATE()) AND DATEADD(YEAR, -18, GETDATE()))

ALTER TABLE СОТРУДНИКИ

ADD CONSTRAINT ограничение_сотрудники_Семейноеположение

CHECK (Семейноеположение IN ('Женат','Не женат', 'Замужем','Не замужем'))

GO

ALTER TABLE СОТРУДНИКИ

ADD CONSTRAINT ограничение_сотрудники_Премия

CHECK (Премия < Зарплата)

GO

ALTER TABLE СКЛАД

ADD CONSTRAINT CN_Stocks_Qty

CHECK (Количество>= 0)

GO

 

-- Код наполнения таблиц текстовыми данными

INSERT ОТДЕЛ

(ID, Названиеотдела)

VALUES

(1, 'ОТДЕЛ ПРОДАЖ'),

(2, 'ОТДЕЛ ПОДДЕРЖКИ')

GO

INSERT СОТРУДНИКИ

(ОтделID, Фамилия, Имя, Отчество, Должность, Зарплата, Премия, Датаприеманаработу, Семейноеположение, Датарождения, Адрес, Телефон)

VALUES

(1,'Перминов', 'Алексей', 'Владимирович', 'Менеджер по продажам', 20000, 5000, GETDATE(),'Женат', '01/10/1990','Зянкина 9', '(953)1416433'),

(2,'Туманов', 'Георгий', 'Викторович', 'Менеджер по продажам', 20000, 5000, GETDATE(),'Женат', '04/10/1990','Воровского 29', '(912)4533554'),

(1,'Кочуров', 'Дмитрий', 'Николаевич', 'Системный администратор', 26000, 7000, GETDATE(),'Женат', '04/10/1990','Пугачева 29', '(951)7433545')

GO

INSERT ПОКУПАТЕЛИ

(Фамилия, Имя, Отчество, Адрес, Город, Телефон, Дата_регистрации)

VALUES

('Савиных','Викторович','Павлович','Труда 21а, 137','Киров','(912)3569385',DATEADD(DAY, -85, GETDATE())),

('Васильева','Алефтина','Генадьевна','Мопра 3а, 12','Пермь','(922)4622225',DATEADD(DAY, -35, GETDATE()))

GO

INSERT ТОВАРЫ

(Название)

VALUES

('Коптильня дымка'),

('Коптильня ханхи'),

('Пивоварня Золингер'),

('Шампура столичные'),

('Дымогенератор дымка')

GO

INSERT ДЕТАЛИТОВАРА

(ID, Цвет, материал, текстовоеописание)

VALUES

(1, 'Серый', 'Нержавеющая сталь','Коптильня преднозначена для горячего копчения'),

(2, 'Черный', 'Черная сталь','Коптильня преднозначена для горячего копчения'),

(3, 'Белый', 'Черная сталь','Пивоварня преднозначена для изготовления пивного сусла'),

(4, 'Стальной', 'Черная сталь','Шампура преднозначены для любителей отдыха на природе'),

(5, 'Стальной', 'Черная сталь','Дымогенератор преднозначен для производства дыма при холодном копчении для ')

GO

INSERT СКЛАД

(ТоварID, Количество)

VALUES

(1, 20),

(2, 10),

(3, 7),

(4, 8),

(5, 9)

GO

INSERT ЗАКАЗЫ

(СотрудникID, ПокупательID, Датаоформления)

VALUES

(1,2, DATEADD(DAY, -85, GETDATE())),

(2,1, DATEADD(DAY, -85, GETDATE()))

GO

INSERT ДЕТАЛИЗАКАЗА

(ЗаказID, Количествопозиций, ТоварID, Количествопродонного, Цена)

VALUES

(4,2,1,1,295),

(5,1,2,1,445)

GO

 

-- Код вывода данных из таблиц

SELECT * FROM СОТРУДНИКИ

SELECT * FROM ПОКУПАТЕЛИ

SELECT * FROM СКЛАД

SELECT * FROM ОТДЕЛ

SELECT * FROM ЗАКАЗЫ

SELECT * FROM ДЕТАЛИЗАКАЗА

SELECT * FROM ТОВАРЫ

SELECT * FROM ДЕТАЛИТОВАРА

 

-- Код создания тригера

CREATE TRIGGER УДАЛЕНИЯПРОДУКТА

ON ТОВАРЫ

INSTEAD OF DELETE

AS IF @@ROWCOUNT = 0

RETURN

SET NOCOUNT ON

IF EXISTS (SELECT 1 FROM ДЕТАЛИЗАКАЗА od

JOIN deleted d

ON od.ТоварID = d.ID)

RAISERROR('Товар не может быть удален т.к. стоит в заказах', 10, 1)

ELSE IF EXISTS (SELECT 1 FROM СКЛАД s

JOIN deleted d

ON s.ТоварID = d.ID

WHERE s.Количество <> 0)

RAISERROR('Товар не может быть удален т.к. есть остаток на складе', 10, 2)

ELSE

DELETE ТОВАР WHERE ID IN (SELECT ID FROM deleted)

GO

 

-- Код вывода запроса

SELECT Датаоформления, c.Имя +' '+ c.Фамилия +' '+ c.Отчество AS Покупатели,

p.Название, pd.ТоварID, od.Количествопродонного, od.Цена

FROM Покупатели c

INNER JOIN ЗАКАЗЫ o

ON c.ID = o.ПокупательID

INNER JOIN Детализаказа od

ON o.ID = od.ЗаказID

INNER JOIN ТОВАРЫ p

ON od.ТоварID = p.ID

INNER JOIN ДЕТАЛИЗАКАЗА pd

ON p.ID = pd.ЗаказID

WHERE Датаоформления BETWEEN DATEADD(month, -2, GETDATE()) AND GETDATE()

ORDER BY Датаоформления

 

-- Код создания представлений

CREATE VIEW Представлениеописаниетотовара

AS

SELECT p.ID, НАЗВАНИЕ, Цвет, Материал, Текстовоеописание

FROM ТОВАРЫ p

LEFT JOIN ДЕТАЛИТОВАРА pd

ON p.ID = pd.ID

GO

 

SELECT * FROM Представление_описание_тотовара

GO

 

CREATE VIEW представление_остатки

AS

SELECT p.ID, p.НАЗВАНИЕ, s.Количество Остаток,

(SELECT SUM(od.Количествопродонного) FROM ДЕТАЛИЗАКАЗА od

WHERE od.ТоварID = p.ID) Продано

FROM ТОВАРЫ p

LEFT JOIN СКЛАД s

ON p.ID = s.ТоварID

GO

 

SELECT * FROM представление_остатки

GO

 

-- Код создания процедуры.

CREATE PROC процедурапоиска_покупателя

@Фамилия nvarchar(20) = '%',

@Город nvarchar(20) = '%'

AS

SET NOCOUNT ON

 

SELECT c.Фамилия + ' ' + c.Имя + ' ' + c.Отчество Покупатели,

o.Датаоформления,

p.НАЗВАНИЕ, od.Количествопродонного, od.Цена

FROM ПОКУПАТЕЛИ c

JOIN ЗАКАЗЫ o

ON c.ID = o.ПокупательID AND c.Город LIKE @Город

JOIN ДЕТАЛИЗАКАЗА od

ON o.ID = od.ЗаказID

JOIN ТОВАРЫ p

ON od.ТоварID = p.ID

WHERE c.Фамилия LIKE @Фамилия

GO

 

EXEC процедурапоиска_покупателя

EXEC процедурапоиска_покупателя '%'

EXEC процедурапоиска_покупателя @Город = '%'

EXEC процедурапоиска_покупателя '%', '%'

GO