Проектирование базы данных "Двери"

Размещено на http://www.allbest.ru/

Федеральное государственное бюджетное образовательное учреждение высшего профессионального образования

«Российский государственный педагогический университет им. А.И.ГЕРЦЕНА»

Курсовая работа

по дисциплине «Управление данными»

на тему: Проектирование базы данных «Двери»

Санкт-Петербург, 2016



1. Описание предметной области

Название предметной области, для которой проектируется БД.

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

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

· Заключение договора:

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

· Оплата квитанции №1 (аванс):

Заказчик обязан произвести оплату до начала выполнения работ. Если квитанция не оплачена в срок, то работы производится не будут. Авансом считается 20% от всей суммы заказа.

· Оплата квитанции №2 (остаток суммы):

Заказчик обязан произвести оплату после окончания работ

(после закрытия акта приемки) не позднее месяца. Остатком суммы считается вся сумма заказа минус аванс.

· Выбор услуги:

Заказчик из прайса выбирает нужное кол-во дверей, цвет, а также указывает нужна ли установка дери или нет.

· Приемка:

Приемку производит лицо со стороны заказчика. Во время приемки создается акт приемки. В приемке указывается качество двери и качество работ. Если в БД значение 1(true), то проверка прошла успешно, если 0(false), то работу надо исправлять.

· Составление акта приема:

У одного договора может быть несколько актов приема. Дата акта должна совпадать с датой приемки, так как акт приемки составляется вместе с приемкой.

· Ведение складского учета:

Прием дверей на склад.

Пользователи информационной системы, для которой проектируется БД.

· Директор:

Заключение договора. Составление акта приема.

· Кладовщик:

Ведет складской учет. Вводит данные о дверях в БД.

· Менеджеры фирмы:

Вводят и изменяют данные в БД. Столбцы "Прайс", "Рабочие", "Приемка", "Квитанция1", "Квитанция2", "Услуги".

Данные, необходимые для решения каждой из задач пользователей.

Директор:

· Составление договора.

Данные: название фирмы, ей реквизиты, сроки работ.

· Составление акта приема.

Данные : нужны данные о приемке дверей, а так же дата приема.

Менеджеры:

· Ввод рабочих в БД и присвоение им индивидуального номера

Данные : индивидуальный номер , ФИО, телефон, дата рождения, паспортные данные.

· Ввод информации в прайс

Данные: серийный номер двери, цвет, материал, цена двери и цена установки (монтаж).

· Ввод услуг

Данные: номер договора, серийный номер двери, кол-во, установка двери: если установка на данную дверь нужна, то ставится 1 (true), если установка не нужна, то ставится 0 (false).

· Квитанции.

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

· Приемка.

Данные: инвентарный номер двери, номер акта, дата приемки, качество двери и качество работы (1, если проверка прошла успешно, 0, если проверка прошла не успешно), номер рабочего.

Кладовщик:

· Ведет складской учет дверей. Ввод данные о дверях в БД и присвоение им индивидуального номера.

Данные : индивидуальный номер , серийный номер, информация о том бракована дверь или нет: если 0 (false) значит брака нет, если 1 (true), то брак присутствует.

Пользовательские ограничения предметной области.

· Каждый заказчик может заключить несколько договоров;

· У заказчика только 2 квитанции ( аванс, ост. сумма);

· Квитанция1 должна оплачиваться до срока начала работ, если квитанция1 не оплачена в срок, то работы производится не будут;

· Квитанция2 должна оплачиваться не позднее месяца с даты подписания в акте приема;

· Акт приемки создается в тот момент, когда идет приемка;

· У каждой двери свой инвентарный номер, а вот серийный номер уже может повторяться;

· Инвентарный номер двери не обязательно привязан к какому-нибудь договору;

· В таблице "Приемка" в столбце "КачествоРаботы" устанавливается по умолчанию Null, если в таблице "Услуги" заказчик не выбрал установку двери.

· Если договор не продлен, то есть акт приемки закрыт за один раз, то дата в акте приемке должна быть от срока начала до срока окончания договора.

2. ER-модель предметной области в стандарте IDEF1X

Описание сущностей и их атрибутов

· Договор

Заказчик с фирмой может заключить несколько договоров. Один договор может содержать несколько актов приемки приемки и две квитанции. Одна квитанция на оплату аванса 20%, другая на оплату оставшейся суммы.

Идентификатор:

НомерДоговора (PK) Not Null

Атрибуты:

Название Null

Реквизиты Not Null

Срок_Начала Not Null

Срок_Окончания Not Null

· Квитанция 1(аванс)

Квитанция 1 выдается сразу после заключения договора. В ней указывается 20% от стоимости заказа. Заказчик должен оплатить эту сумму до начала работ, указанных в договоре.

Идентификатор:

НомерКвитанции(PK) Not Null

Атрибуты:

Реквизиты Not Null

Аванс Not Null

Дата Null

НомерДоговора(FK) Not Null

· Квитанция 2(оставшаяся сумма)

Квитанция 2 выдается сразу после закрытия акта приемки. В ней указывается оставшиеся 80% от стоимости заказа. Заказчик должен оплатить эту сумму не позже чем через месяц после закрытия акта приемки.

Идентификатор:

НомерКвитанции(PK) Not Null

Атрибуты:

Реквизиты Not Null

ОстСумма Not Null

Дата Null

НомерАкта(FK) Not Null

· Прайс

В прайс - листе указываются все типы дверей, их характеристики, стоимость, стоимость установки. Каждый серийный номер дверей только в 1 цвете и 1 материала. Если у двери такие же характеристики, но разный цвет, то это другой серийный номер.

Идентификатор:

СерийныйНомер(PK) Not Null

Атрибуты:

Цвет Not Null

Производитель Null

Материал Not Null

Цена_Двери Null

Цена_Монтажа Null

· Услуги

В услугах заказчик формируем свой заказ. Указывает кол-во нужных ему дверей, а так же нужна установка или нет. В таблице "Услуги" столбец "Монтаж" может принимать значение только 0 или 1. Монтаж входит в ключ, так как "НомерДоговора" и "СерийныйНомер" могут повторятся. Например, у одного серийного номер есть монтаж, а у остальных нет.

Идентификатор:

СерийныйНомер(PK, FK) Not Null

НомерДоговора(PK, FK) Not Null

Монтаж(PK) Not Null

Атрибуты:

Кол-во Not Null

· Двери

Это склад дверей. Каждая дверь имеет уникальный инвентарный номер, но несколько дверей может могут иметь один и тот же серийный номер. У инвентарного номера серийного номера может не быть, если серийный номер не успели внести в прайс.

Идентификатор:

ИнвентарныйНомер(PK) Not Null

Атрибуты:

СерийныйНомер(FK) Null

Брак Not Null

· Приемка

В приемке проверяющее лицо со стороны заказчика( это может быть сам заказчик) проверяет качество и установку дверей. В столбце"КачествоРаботы" может бытьтолько Null, 0 или 1. 1 - принято, 0 - не принято, Null, если у данной двери не заказывали монтаж. А так же "НомерРабочего " так же может быть Null, если нет монтажа у двери, а кто именно доставил дверь не известно.

Идентификатор:

ИнвентарныйНомер(PK,FK) Not Null

НомерАкта(PK,FK) Not Null

ДатаПриемки(PK) Not Null

Атрибуты:

КачествоДвери Not Null

КачествоРаботы Null

НомерРабочего(FK) Null

· Рабочие

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

Идентификатор:

НомерРабочего(PK) Not Null

Атрибуты:

ФИО Null

Номер_Телефона Null

Дата_Рождения Not Null

Номер_Паспорта Null

· Акт Приема

Акт приема - документ. У одного договора может быть только один акт приема. Если в столбце "НовыйСрокОкончания" Null, то акт успешно закрыт. Закрыт он в том случае, если в таблице"Приемка" качество дверей и качество работ равно 1 или Null. Если где-то есть хотя бы один 0, то договор продлевают на срок по согласованию обеих сторон. В столбце " ДатаПриема " может быть Null до приемки проверяющего лица.

Идентификатор:

НомерАкта(PK) Not Null

Атрибуты:

ДатаАкта Null

НомерДоговора(FK) Not Null

Описание связей между сущностями.

Сущности «Договор» и «Квитанция1» - неидентифицирующая связь.

Родитель - договор, потомок - квитанция1.

Кардинальность: родитель (min 1, max 1), потомок (min 1, max 1).

Одна фирма - одна квитанция.

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

Сущности «Договор» и «Услуги» - идентифицирующая связь.

Родитель - договор, потомок - услуги.

Кардинальность: родитель (min 1, max 1), потомок (min 0, max N).

Один договор - несколько услуг.

Заказчик в одном договоре может выбрать несколько услуг.

Сущности «Прайс» и «Услуги» - идентифицирующая связь.

Родитель - прайс, потомок - услуги.

Кардинальность: родитель (min 1, max 1), потомок (min 0, max N).

Один прайс - несколько услуг.

Заказчик из одного прайса может выбрать несколько услуг.

Сущности «Прайс» и «Двери» - неидентифицирующая связь.

Родитель - прайс, потомок - двери.

Кардинальность: родитель (min 0, max 1), потомок (min 0, max N).

Один прайс - много дверей.

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

Сущности «Двери» и «Приемка» - идентифицирующая связь.

Родитель - двери, потомок - приемка.

Кардинальность: родитель (min 1, max 1), потомок (min 0, max N).

Одна дверь - одна приемка

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

Сущности «Рабочие» и «Приемка» - неидентифицирующая связь.

Родитель - рабочие, потомок - приемка.

Кардинальность: родитель (min 0, max 1), потомок (min 0, max N).

Один рабочий - много поставленных дверей

Необязательный родитель. Рабочий может быть в таблице, но не обязательно, чтобы он монтировал дверь.

Сущности «Акт приема» и «Приемка» - идентифицирующая связь.

Родитель - акт приема , потомок - приемка.

Кардинальность: родитель (min 1, max 1), потомок (min 0, max N).

Одна акт приема - несколько приемок.

Приемка не может быть без акта приема, поэтому приемка начинается с оформления акта.

Сущности «Договор» и «Акт приема» - неидентифицирующая связь.

Родитель - договор, потомок - акт приема.

Кардинальность: родитель (min 1, max 1), потомок (min 0, max N).

Один договор - один акт приема.

Данная связь нужна, чтобы проверять качество работ. А так же проверять сколько раз продлевали договор.

Сущности «Акт приема» и «Квитанция2» - неидентифицирующая связь. Родитель - акт приема , потомок - квитанция2.

Кардинальность: родитель (min 1, max 1), потомок (min 1, max 1).

Одна договор - одна квитанция.

После подписания акта приема выдается одна квитанция на оплату оставшейся сумма заказа.

Описание дополнительных ограничений модели предметной области.

"ДатаАкта" должна совпадать с "ДатаПриемки",

"Квитанция1" создается сразу при создании "Договора",

"Квитанция2" создается сразу после закрытия акта приема.

"Аванс" должен быть оплачен раньше "Срок_Начала",

"ОстСумма" должна быть оплачена не позже, чем за месяц после "Срок_Окончания", в "Монтаж" может стоять только 1(True) или 0(False),

в "Брак" может стоять только 1(True) или 0(False),

в "КачествоДвери" может стоять только 1(True) или 0(False),

в "КачествоРаботы" может стоять только 1(True), 0 (False) или Null.

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

1. Как только создается запись в таблице "Договор" автоматически добавляется запись в таблицу "Квитанция1".

2. Не может быть приемки без "ДатаАкта" в таблице "Акт_Приема".

То есть как только происходит запись в столбце "ДатаПриемки" формируется запись с такой же датой в столбце "ДатаАкта".

3. Реляционная модель базы данных

Полное название СУБД, средствами которой создается БД.

Microsoft SQL Server 2008 R2, Среда SQL Server Management Studio.

Набор команд SQL для создания структуры БД в указанной СУБД.

CREATE TABLE Договор

НомерДоговора integer NOT NULL CHECK (НомерДоговора > 0),

Название varchar(20) NULL ,

Реквизиты varchar(20) NOT NULL ,

Срок_Начала datetime NOT NULL ,

Срок_Окончания datetime NOT NULL,

PRIMARY KEY(НомерДоговора)

CREATE TABLE Квитанция1

НомерКвитанции integer NOT NULL ,

Реквизиты integer NOT NULL ,

Аванс integer NOT NULL ,

Дата datetime NULL ,

НомерДоговора integer NOT NULL REFERENCES Договор (НомерДоговора),

PRIMARY KEY(НомерКвитанции)

CREATE TABLE Прайс

СерийныйНомер integer NOT NULL ,

Цвет varchar(20) NOT NULL ,

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

Цена_Двери integer NULL ,

Цена_Установки integer NULL,

PRIMARY KEY(СерийныйНомер)

CREATE TABLE Услуги

СерийныйНомер integer NOT NULL REFERENCES Прайс (СерийныйНомер),

НомерДоговора integer NOT NULL REFERENCES Договор (НомерДоговора),

Монтаж integer NOT NULL CHECK (Монтаж IN ('1','0')),

Кол_во integer NOT NULL ,

PRIMARY KEY(СерийныйНомер, НомерДоговора, Монтаж)

CREATE TABLE Двери

ИнвентарныйНомер char(18) NOT NULL,

СерийныйНомер integer NULL REFERENCES Прайс (СерийныйНомер),

Брак bit NOT NULL,

PRIMARY KEY(ИнвентарныйНомер)

CREATE TABLE Рабочие

НомерРабочего varchar(5) NOT NULL ,

ФИО nvarchar(30) NULL ,

Номер_Телефона varchar(10) NULL ,

Дата_Рождения nvarchar(15) NULL ,

Номер_Паспорта nvarchar(15) NULL ,

PRIMARY KEY(НомерРабочего)

CREATE TABLE Акт_Приема

НомерАкта nvarchar(20) NOT NULL ,

ДатаАкта datetime NOT NULL ,

НомерДоговора integer NOT NULL REFERENCES Договор (НомерДоговора),

PRIMARY KEY (НомерАкта)

CREATE TABLE Приемка

ИнвентарныйНомер char(18) NOT NULL REFERENCES Двери (ИнвентарныйНомер),

НомерАкта nvarchar(20) NOT NULL REFERENCES Акт_Приема (НомерАкта),

ДатаПриема datetime NOT NULL,

КачествоДвери bit NOT NULL,

КачествоРаботы bit NULL ,

НомерРабочего varchar(5) NULL REFERENCES Рабочие (НомерРабочего),

PRIMARY KEY (ИнвентарныйНомер,НомерАкта,ДатаПриема)

CREATE TABLE Квитанция2

НомерКвитанции integer NOT NULL ,

Реквизиты integer NOT NULL ,

ОстСумма integer NOT NULL ,

Дата datetime NULL ,

НомерАкта nvarchar(20) NOT NULL REFERENCES Акт_Приема (НомерАкта),

PRIMARY KEY (НомерКвитанции)

Схема базы данных

Набор команд SQL для ввода данных.

INSERT INTO Договор VALUES ('001', 'ООО "Астра"', '0001387', '01-02-2016','01-03-2016');

INSERT INTO Договор VALUES ('002', 'ОАО "Транко"', '0002908', '21-02-2016','21-09-2016');

INSERT INTO Договор VALUES ('003', 'ООО "Окна"', '0011043', '10-03-2016','01-05-2016');

INSERT INTO Договор VALUES ('004', 'МАОУ "СОШ №21"', '0009023', '16-03-2016','16-04-2016');

INSERT INTO Договор VALUES ('005', 'ООО "Инвестторг"', '0008812', '28-03-2016','28-07-2016');

INSERT INTO Договор VALUES ('006', 'ООО "ПолисГруп"', '0120046', '30-03-2016','30-05-2016');

INSERT INTO Договор VALUES ('007', 'ОАО "Лондон"', '0059113', '02-04-2016','02-06-2016');

INSERT INTO Договор VALUES ('008', 'ООО "Капитал"', '0079244', '04-04-2016','04-08-2016');

INSERT INTO Договор VALUES ('009', 'ООО "Эльф"', '0000987', '10-04-2016','10-06-2016');

INSERT INTO Договор VALUES ('010','ООО "Газпром"', '0498252', '20-04-2016','01-12-2016');

INSERT INTO Квитанция1 VALUES ('1', '0001387','', '25-01-2016','1');

INSERT INTO Квитанция1 VALUES ('2', '0002908','', '20-02-2016','2');

INSERT INTO Квитанция1 VALUES ('3', '0011043','', '01-03-2016','3');

INSERT INTO Квитанция1 VALUES ('4', '0009023','', '21-03-2016','4');

INSERT INTO Квитанция1 VALUES ('5', '0008812','', '27-03-2016','5');

INSERT INTO Квитанция1 VALUES ('6', '0120046','', '01-05-2016','6');

INSERT INTO Квитанция1 VALUES ('7', '0059113','', '16-03-2016','7');

INSERT INTO Квитанция1 VALUES ('8', '0079244','', '02-04-2016','8');

INSERT INTO Квитанция1 VALUES ('9', '0000987','', '10-04-2016','9');

INSERT INTO Квитанция1 VALUES ('10','0498252','', '11-04-2016','10');

INSERT INTO Прайс VALUES ('085', 'Белый', 'Дерево', '2100', '2000');

INSERT INTO Прайс VALUES ('120', 'Дуб', 'Дерево', '4300', '2000');

INSERT INTO Прайс VALUES ('136', 'Груша', 'Металл', '7000', '3000');

INSERT INTO Прайс VALUES ('590', 'Венге светлый', 'Дерево', '3500', '1500');

INSERT INTO Прайс VALUES ('098', 'Венге темный', 'Дерево', '2700', '2000');

INSERT INTO Прайс VALUES ('099', 'Миланский орех', 'Дерево', '2700', '2000');

INSERT INTO Прайс VALUES ('034', 'Итальянский орех','Дерево', '1500', '1500');

INSERT INTO Прайс VALUES ('121', 'Темный орех', 'Дерево', '4300', '2000');

INSERT INTO Прайс VALUES ('070', 'Темный кипарис', 'Металл', '11000','3000');

INSERT INTO Прайс VALUES ('312', 'Темный кипарис', 'Металл', '12000','3000');

INSERT INTO Прайс VALUES ('111', 'Итальянский орех','Дерево', '6000', '2500');

INSERT INTO Прайс VALUES ('112', 'Бук', 'Дерево', '6000', '2500');

INSERT INTO Прайс VALUES ('208', 'Белое серебро', 'Металл', '15000','4000');

INSERT INTO Прайс VALUES ('801', 'Белое золото', 'Металл', '15000','4000');

INSERT INTO Услуги VALUES ('121', '001', '1', '40');

INSERT INTO Услуги VALUES ('099', '001', '1', '30');

INSERT INTO Услуги VALUES ('312', '002', '0', '21');

INSERT INTO Услуги VALUES ('312', '003', '1', '5');

INSERT INTO Услуги VALUES ('801', '004', '1', '25');

INSERT INTO Услуги VALUES ('120', '005', '1', '55');

INSERT INTO Услуги VALUES ('120', '005', '0', '6');

INSERT INTO Услуги VALUES ('312', '006', '1', '5');

INSERT INTO Услуги VALUES ('208', '007', '1', '3');

INSERT INTO Услуги VALUES ('111', '008', '1', '15');

INSERT INTO Услуги VALUES ('112', '009', '1', '9');

INSERT INTO Услуги VALUES ('098', '010', '1', '70');

INSERT INTO Услуги VALUES ('099', '010', '1', '38');

INSERT INTO Услуги VALUES ('099', '010', '0', '7');

INSERT INTO Двери VALUES ('1', '085', '0');

INSERT INTO Двери VALUES ('2', '085', '0');

INSERT INTO Двери VALUES ('3', '120', '0');

INSERT INTO Двери VALUES ('4', '136', '0');

INSERT INTO Двери VALUES ('5', '590', '0');

INSERT INTO Двери VALUES ('6', '590', '0');

INSERT INTO Двери VALUES ('7', '098', '0');

INSERT INTO Двери VALUES ('8', '099', '0');

INSERT INTO Двери VALUES ('9', '034', '0');

INSERT INTO Двери VALUES ('10','121', '0');

INSERT INTO Двери VALUES ('11','121', '0');

INSERT INTO Двери VALUES ('12','121', '1');

INSERT INTO Двери VALUES ('13','070', '0');

INSERT INTO Двери VALUES ('14','312', '0');

INSERT INTO Двери VALUES ('15','111', '0');

INSERT INTO Двери VALUES ('16','112', '0');

INSERT INTO Двери VALUES ('17','208', '0');

INSERT INTO Двери VALUES ('18','801', '0');

INSERT INTO Рабочие VALUES ('1', 'Алиев Магамед Русланович', '632-708', '10-12-1985', '4909256825');

INSERT INTO Рабочие VALUES ('2', 'Борисов Андрей Юрьевич', '712-489', '14-15-1990', '4914719579');

INSERT INTO Рабочие VALUES ('3', 'Муха Георгий Викторович', '645-021', '21-10-1986', '4910132550');

INSERT INTO Рабочие VALUES ('4', 'Ушаков Никита Александрович','684-295', '16-02-1979', '4901013176');

INSERT INTO Рабочие VALUES ('5', 'Коваль Сергей Игоревич', '782-102', '14-05-1973', '4911712785');

INSERT INTO Акт_Приема VALUES ('1815/1N', '01-03-2016', '001');

INSERT INTO Акт_Приема VALUES ('1816/1N', '20-04-2016', '004');

INSERT INTO Акт_Приема VALUES ('1817/1N', '10-09-2016', '002');

INSERT INTO Акт_Приема VALUES ('1818/1N', '01-04-2016', '003');

INSERT INTO Приемка VALUES ('10', '1815/1N', '01-03-2016', '1', '1', '1');

INSERT INTO Приемка VALUES ('8', '1815/1N', '01-03-2016', '1', '1', '2');

INSERT INTO Приемка VALUES ('14', '1818/1N', '05-04-2016', '1', '1', '3');

INSERT INTO Приемка VALUES ('18', '1816/1N', '19-04-2016', '1', '1', '4');

INSERT INTO Квитанция2 VALUES ('1', '0001387','', '20-03-2016','1815/1N');

INSERT INTO Квитанция2 VALUES ('2', '0002908','', '30-09-2016','1817/1N');

INSERT INTO Квитанция2 VALUES ('3', '0011043','', '02-05-2016','1818/1N');

INSERT INTO Квитанция2 VALUES ('4', '0009023','', '20-04-2016','1816/1N');

Набор команд SQL для создания и выполнения представлений.

1) По инвентарному номеру определить кто из рабочих ставил дверь.

CREATE VIEW Кто_Ставил_Дверь

AS

SELECT р.ФИО, п.ИнвентарныйНомер

FROM Рабочие р JOIN Приемка п

ON р.НомерРабочего = п.НомерРабочего

WHERE п.ИнвентарныйНомер = '10'

GO

SELECT *

FROM Кто_Ставил_Дверь

2) Найти сколько заключено договоров в период с 01.02.2016 по 01.04.2016.



CREATE VIEW Кол_Договоров

AS

SELECT COUNT(Срок_Начала) AS "Кол-во договоров"

FROM Договор

WHERE Срок_Начала BETWEEN '01-02-2016' AND '01-04-2016'

GO

SELECT *

FROM Кол_Договоров

3)Найти должников, которые вовремя не оплатили остаток суммы.

CREATE VIEW Должники

AS

SELECT а.НомерДоговора

FROM Акт_Приема а JOIN Квитанция2 к

ON а.НомерАкта = к. НомерАкта

WHERE (а.ДатаАкта + 30) < к.Дата

GO

SELECT *

FROM Должники

4. Реализация ограничений БД с помощью хранимых процедур и триггеров

Описание транзакций

Транзакция - это последовательность операторов манипулирования данными, выполняющаяся как единое целое (все или ничего) и переводящая базу данных из одного целостного состояния в другое целостное состояние.

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

В данном случае транзакция выступает в роле триггера, так как выполняется автоматически. Если во время записи в таблице "Договор" произойдет какой-то сбой, то запись в таблице "Квитанция1" не будет создана.

Описание триггеров и хранимых процедур

1. Описание процедуры: Расчет квитанции1, то есть аванса.

Квитанция1 рассчитывается из расчета заказа заказчика. Цена двери из таблицы "Прайс" умножается на кол-во таких дверей, если есть установка, то прибавляется цена за установку. Так высчитывают полную стоимость заказа умноженное на 0.2. Это и будет стоимость аванса.

2. Описание триггера: см. пункт 4.1

3. Описание триггера: При добавлении новой записи в столбце "Договор" триггер не дает создать запись, если клиент находится в черном списке.

Набор команд для создания хранимой процедуры (transact SQL)

CREATE PROCEDURE РассчитатьАванс

@номердоговора INT

AS

BEGIN

INSERT INTO Квитанция1

SELECT К1.НомерКвитанции*0, К1.Реквизиты,

SUM(У.[Монтаж] * П.[Цена_Установки]*0.2*У.[Кол_во] + П.[Цена_Двери]*У.[Кол_во]) Аванс,

К1.Дата, К1.НомерДоговора FROM Квитанция1 К1

INNER JOIN Договор Д ON К1.НомерДоговора=Д. НомерДоговора

INNER JOIN [Услуги] У ON Д. НомерДоговора =У. НомерДоговора

INNER JOIN [Прайс] П ON П.СерийныйНомер=У. СерийныйНомер

WHERE К1.НомерДоговора =@номердоговора

GROUP BY К1.НомерКвитанции, К1.Реквизиты, К1.Дата, К1.НомерДоговора

END

GO

Пример команды вызова хранимой процедуры (transact SQL)

EXEC РассчитатьАванс '1'

Набор команд для создания триггера (transact SQL)

CREATE TRIGGER Добавить ON Договор

FOR INSERT

AS

BEGIN

INSERT INTO Квитанция1(НомерКвитанции, Реквизиты, НомерДоговора)

SELECT НомерКвитанции, Реквизиты, НомерДоговора FROM inserted

END

CREATE TRIGGER ЧерныйСписок ON Договор

FOR INSERT

AS

DECLARE @Name varchar(50)

SELECT @Name=Реквизиты

FROM inserted

IF @Name='000111'

BEGIN

PRINT 'ОШИБКА, этот клиент в черном списке!'

ROLLBACK TRANSACTION

END

Пример команды, вызывающей триггер (transact SQL)

INSERT INTO Договор VALUES ('020', 'Яблочко', '000111', '01-02-2016','01-03-2016');

INSERT INTO Договор VALUES ('021', 'Голд', '000123', '01-06-2016','01-10-2016');

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

...