yBfGNKA9

1USE master
2GO
3
4--IF  EXISTS (
5--	SELECT name 
6--		FROM sys.databases 
7--		WHERE name = N'Melentyeva'
8--)
9--DROP DATABASE [Melentyeva]
10--GO
11
12CREATE DATABASE [Melentyeva]
13GO
14
15USE [Melentyeva]
16GO
17
18IF EXISTS(
19  SELECT *
20    FROM sys.schemas
21   WHERE name = N'Библиотека'
22) 
23 DROP SCHEMA Библиотека
24GO
25
26CREATE SCHEMA Библиотека 
27GO
28
29CREATE TABLE [Melentyeva].Библиотека.Книги
30(
31	BookId int IDENTITY(1,1) NOT NULL, 
32	Название nvarchar(60) NULL, 
33	Издательство nvarchar(40) NULL,
34	Город nvarchar(40) NULL,
35	Год int NULL,
36	Объем int NULL,
37	Шифр nvarchar(20) NULL,
38	Цена money NULL,
39	Экземпляров int NULL
40    CONSTRAINT PK_BookId PRIMARY KEY (BookId) 
41)
42GO
43
44 INSERT INTO [Melentyeva].Библиотека.Книги
45 (Название, Издательство, Город, Год, Объем, Шифр, Цена, Экземпляров)
46 VALUES 
47 (N'Малое собрание сочинений', N'Азбука', N'Москва', '1990', 680, N'Д01', 710, 3),
48 (N'Война и мир. 1 том', N'Лениздат', N'Ленинград', '1989', 360, N'А7', 380, 2),
49 (N'Лолита', N'Азбука', N'Москва', '1998', 230, N'07', 270, 1),
50 (N'Прощай, оружие', N'Эксмо-Пресс', N'Калиниград', '2000', 230, N'С2', 170, 2),
51 (N'Три товарища', N'Эксмо-Пресс', N'Калиниград', '1997', 300, N'К34', 290, 1),
52 (N'Конец прекрасной эпохи', N'Лениздат ', N'Ленинград', '1990', 600, N'И30', 360, 3)
53GO	
54
55CREATE TABLE [Melentyeva].Библиотека.Авторы
56(
57	Имя nvarchar(40) NOT NULL, 
58	BookId int NULL
59	CONSTRAINT FK_Имя FOREIGN KEY (BookId)
60	REFERENCES Библиотека.Книги(BookId) --  связали с книгами
61	ON UPDATE CASCADE
62)
63GO
64
65 INSERT INTO [Melentyeva].Библиотека.Авторы
66 (Имя, BookId)
67 VALUES 
68(N'Бродский И.А.', 1),
69(N'Бродский И.А.', 6),
70(N'Лев Толстой', 2),
71(N'Владимир Набоков', 3),
72(N'Эрнест Хемингуэй', 4),
73(N'Эрих Мария Ремарк', 5)
74GO	
75
76CREATE TABLE  [Melentyeva].Библиотека.Читатели
77(
78	Номер_билета nvarchar(40) NOT NULL, 
79	Фамилия nvarchar(80) NULL, 
80	Имя nvarchar(80) NULL, 
81	Отчество nvarchar(80) NULL, 
82	Адрес nvarchar(100) NULL,
83	Телефон nvarchar(20) NULL
84    CONSTRAINT PK_Номер_билета PRIMARY KEY (Номер_билета) 
85)
86GO
87
88 INSERT INTO  [Melentyeva].Библиотека.Читатели
89 (Номер_билета, Фамилия, Имя, Отчество, Адрес, Телефон)
90 VALUES 
91 ('1', N'Заумкина', N'Анастасия', N'Данильевна', N'ул. Пятая, д. 1', '+79500000009'),
92 ('2', N'Недоумкин', N'Мария', N'Петровна', N'ул. Первая, д. 2', '+795555555553'),
93 ('3', N'Знахова', N'Алина', N'Александровна', N'ул. Первая, д. 4', '+79533333331'),
94 ('4', N'Кальсин', N'Игорь', N'Сергеевич', N'ул. Вторая, д. 7', '+79999999999')
95GO	
96
97-- все записи о выдачи книг
98CREATE TABLE [Melentyeva].Библиотека.Картотека
99(
100	CardId int IDENTITY(1,1) NOT NULL, 
101	BookId int NULL, 
102	Номер_билета nvarchar(40) NULL,
103	Экземпляр_взят date NULL,
104	До date NULL,
105	Экземпляр_возвращен nvarchar(10) NULL
106    CONSTRAINT PK_CardId PRIMARY KEY (CardId)
107	CONSTRAINT FK_BookId FOREIGN KEY (BookId)
108	REFERENCES Библиотека.Книги(BookId)
109	ON UPDATE CASCADE, 
110	CONSTRAINT FK_Номер_билета FOREIGN KEY (Номер_билета)
111	REFERENCES Библиотека.Читатели(Номер_билета)
112	ON UPDATE CASCADE 
113)
114GO
115
116 INSERT INTO [Melentyeva].Библиотека.Картотека
117 (BookId, Номер_билета, Экземпляр_взят, До, Экземпляр_возвращен)
118 VALUES 
119(1, '1', '2017-03-23',  '2017-04-01', N'да'),
120(4, '1', '2017-12-12', '2018-01-02', N'нет'),
121(2, '3', '2017-11-02', '2017-11-02', N'да')
122GO	
123
124-- бронирование
125CREATE TABLE [Melentyeva].Библиотека.Бронь
126(
127	OrderId int IDENTITY(1,1) NOT NULL, 
128	BookId int NULL, 
129	Номер_билета nvarchar(40) NULL,
130	Дата_заказа date NULL
131    CONSTRAINT PK_OrderId PRIMARY KEY (OrderId)
132	CONSTRAINT FK_BookId_order FOREIGN KEY (BookId)
133	REFERENCES Библиотека.Книги(BookId)
134	ON UPDATE CASCADE, 
135	CONSTRAINT FK_Номер_билета_ord FOREIGN KEY (Номер_билета)
136	REFERENCES Библиотека.Читатели(Номер_билета)
137	ON UPDATE CASCADE 
138)
139GO
140
141 INSERT INTO [Melentyeva].Библиотека.Бронь
142 (BookId, Номер_билета, Дата_заказа)
143 VALUES 
144(3, '2', '2017-03-23'),
145(6, '1',  '2018-01-02'),
146(6, '3', '2017-11-02')
147GO	
148
149
150-- +дописать с бронью
151CREATE TRIGGER Библиотека.Книга_в_библиотеке
152ON [Melentyeva].Библиотека.Картотека
153AFTER INSERT
154AS
155BEGIN
156	DECLARE @idBook int
157	SET @idBook=(
158		SELECT TOP(1) BookId
159		FROM Библиотека.Картотека
160		ORDER BY CardId DESC
161	)
162
163	DECLARE @countBook int
164	SET @countBook=(
165		SELECT Экземпляров
166		FROM Библиотека.Книги
167		WHERE BookId = @idBook
168	)
169
170	DECLARE @countBookOnHands int 
171	SET @countBookOnHands=(
172		SELECT COUNT(*)
173		FROM Библиотека.Картотека
174		WHERE BookId = @idBook and Экземпляр_возвращен = 'нет'
175	)
176	IF @countBook > @countBookOnHands  RETURN; -- 
177
178	RAISERROR ('Все экземпляры на руках', 10, 1) --?
179	ROLLBACK TRANSACTION;
180RETURN
181END
182GO
183---- Взять "Лолита", которая уже взята другим читателем (она всего одна)
184-- INSERT INTO [Melentyeva].Библиотека.biblioSystem
185-- (BookId, ReaderTicket, GivenDate, ReturnDate)
186-- VALUES 
187--(3, '3456790', '2017-09-25', '2017-10-05')
188--GO
189
190---- Взять "Прощай, оружие", которых всего 2, и обе у читателей
191-- INSERT INTO [Melentyeva].Библиотека.biblioSystem
192-- (BookId, ReaderTicket, GivenDate, ReturnDate)
193-- VALUES 
194--(4, '3456790', '2017-09-26', '2017-10-06'),
195--(4, '1237652', '2017-09-28', '2017-10-08')
196--GO
197
198GO
199
200-- Вывод сведений о книгах, взятых определенным читателем
201CREATE PROCEDURE Библиотека.Книги_читателя
202	@читатель nvarchar(40)
203AS
204SELECT *
205FROM Библиотека.Авторы INNER JOIN
206    Библиотека.Книги ON Библиотека.Авторы.BookId = Библиотека.Книги.BookId INNER JOIN
207    Библиотека.Картотека ON Библиотека.Книги.BookId = Библиотека.Картотека.BookId
208WHERE Библиотека.Картотека.Номер_билета = @читатель
209GO 
210
211EXEC Библиотека.Книги_читателя '1'
212GO 
213
214
215-- Сведения о читателях, у которых находится определенная книга
216CREATE PROCEDURE Библиотека.Книга_у_читателя
217	@book int
218AS
219SELECT *
220FROM Библиотека.Читатели INNER JOIN 
221Библиотека.Картотека ON Библиотека.Картотека.Номер_билета = Библиотека.Читатели.Номер_билета INNER JOIN
222Библиотека.Книги ON Библиотека.Картотека.BookId = Библиотека.Книги.BookId
223WHERE Библиотека.Картотека.BookId = @book
224GO
225
226EXEC Библиотека.Книга_у_читателя 5
227GO 
228
229-- Сведения о читателе, прочитавшем за определенный интервал времени максимальное количество книг
230CREATE PROCEDURE Библиотека.Читатель_max_книг
231	@startRead date,
232	@endRead date
233AS
234SELECT TOP(1) *
235FROM (
236	SELECT Номер_билета, COUNT(*) as countBooks
237	FROM Библиотека.Картотека
238	WHERE Экземпляр_взят > @startRead AND До < @endRead
239	GROUP BY Номер_билета
240) AS a INNER JOIN Библиотека.Читатели ON a.Номер_билета = Библиотека.Читатели.Номер_билета
241ORDER BY a.countBooks DESC
242GO
243
244EXEC Библиотека.Читатель_max_книг '2017-01-01', '2018-01-15'
245GO 
246
247
248-- Сведения о книге, количество экземпляров которой больше всего
249CREATE PROCEDURE Библиотека.Книга_с_max_экземплярами
250AS
251SELECT TOP(1) *
252FROM Библиотека.Авторы INNER JOIN
253    Библиотека.Книги ON Библиотека.Авторы.BookId = Библиотека.Книги.BookId 
254ORDER BY Экземпляров DESC
255GO
256
257EXEC Библиотека.Книга_с_max_экземплярами
258GO 
259
260
261-- Сведения о книге, на которую заказов больше всего
262CREATE PROCEDURE Библиотека.Самая_востребованная_книга
263AS
264SELECT TOP(1) *
265FROM (
266	SELECT BookId, COUNT(*) AS countBookings
267	FROM Библиотека.Бронь
268	GROUP BY BookId
269) AS b INNER JOIN Библиотека.Книги ON b.BookId = Библиотека.Книги.BookId INNER JOIN
270Библиотека.Авторы ON Библиотека.Авторы.BookId = Библиотека.Книги.BookId
271ORDER BY b.countBookings DESC
272GO
273
274EXEC Библиотека.Самая_востребованная_книга
275GO 
276
277
278-- Сведения о книге, которую читают за определенный интервал времени максимальное количество читателей
279CREATE PROCEDURE Библиотека.Самая_читаемая_книга
280	@startRead date,
281	@endRead date
282AS
283SELECT TOP(1) *
284FROM (
285	SELECT BookId, COUNT(*) as countReaders
286	FROM Библиотека.Картотека
287	WHERE Экземпляр_взят > @startRead AND До < @endRead
288	GROUP BY BookId
289) AS a INNER JOIN Библиотека.Книги ON a.BookId = Библиотека.Книги.BookId INNER JOIN
290Библиотека.Авторы ON Библиотека.Авторы.BookId = Библиотека.Книги.BookId
291ORDER BY a.countReaders DESC
292GO
293
294EXEC Библиотека.Самая_читаемая_книга '2017-11-01', '2018-01-15'
295GO