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

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

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', 'ООО «Астра» ', '1 387', '01−02−2016','01−03−2016');

INSERT INTO Договор VALUES ('002', 'ОАО «Транко» ', '2 908', '21−02−2016','21−09−2016');

INSERT INTO Договор VALUES ('003', 'ООО «Окна» ', '11 043', '10−03−2016','01−05−2016');

INSERT INTO Договор VALUES ('004', 'МАОУ «СОШ № 21» ', '9 023', '16−03−2016','16−04−2016');

INSERT INTO Договор VALUES ('005', 'ООО «Инвестторг» ', '8 812', '28−03−2016','28−07−2016');

INSERT INTO Договор VALUES ('006', 'ООО «ПолисГруп» ', '120 046', '30−03−2016','30−05−2016');

INSERT INTO Договор VALUES ('007', 'ОАО «Лондон» ', '59 113', '02−04−2016','02−06−2016');

INSERT INTO Договор VALUES ('008', 'ООО «Капитал» ', '79 244', '04−04−2016','04−08−2016');

INSERT INTO Договор VALUES ('009', 'ООО «Эльф» ', '987', '10−04−2016','10−06−2016');

INSERT INTO Договор VALUES ('010','ООО «Газпром» ', '498 252', '20−04−2016','01−12−2016');

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

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

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

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

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

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

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

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

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

INSERT INTO Квитанция1 VALUES ('10','498 252','', '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', 'Темный кипарис', 'Металл', '11 000','3000');

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

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

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

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

INSERT INTO Прайс VALUES ('801', 'Белое золото', 'Металл', '15 000','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', '4 909 256 825');

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

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

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

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

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', '1 387','', '20−03−2016','1815/1N');

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

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

INSERT INTO Квитанция2 VALUES ('4', '9 023','', '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 Должники.