· 6 years ago · Jun 06, 2019, 07:12 AM
1CREATE Database LibraryDb;
2
3USE LibraryDb;
4
5CREATE TABLE [dbo].[Speciality] (
6 [Id] int PRIMARY KEY IDENTITY(1,1) NOT NULL,
7 [Name] nvarchar(max) NOT NULL
8)
9
10CREATE TABLE [dbo].[Group] (
11 [Id] int PRIMARY KEY IDENTITY(1,1) NOT NULL,
12 [Name] nvarchar(max) NOT NULL,
13 [SpecialityId] int FOREIGN KEY REFERENCES [dbo].[Speciality](Id)
14)
15
16CREATE TABLE [dbo].[Student] (
17 [Id] int PRIMARY KEY IDENTITY(1,1) NOT NULL,
18 [Name] nvarchar(max) NOT NULL,
19 [RecordBookId] int NOT NULL UNIQUE,
20 [GroupId] int FOREIGN KEY REFERENCES [dbo].[Group](Id)
21)
22
23CREATE TABLE [dbo].[Book] (
24 [Id] int PRIMARY KEY IDENTITY(1,1) NOT NULL,
25 [Name] nvarchar(max) NOT NULL,
26 [Authors] nvarchar(max) NOT NULL,
27 [PublicationYear] int NOT NULL,
28 [PublicationPlace] nvarchar(max) NOT NULL,
29 [OtherCharacteristics] nvarchar(max) NOT NULL,
30 [IsRareBook] BIT NOT NULL DEFAULT 0
31)
32
33CREATE TABLE [dbo].[BookInstance] (
34 [Id] int PRIMARY KEY IDENTITY(1,1) NOT NULL,
35 [BookId] int FOREIGN KEY REFERENCES [dbo].[Book](Id)
36)
37
38CREATE TABLE [dbo].[Requirement] (
39 [StudentId] int FOREIGN KEY REFERENCES [dbo].[Student](Id),
40 [BookInstanceId] int FOREIGN KEY REFERENCES [dbo].[BookInstance](Id),
41 [IssueDate] date NOT NULL,
42 [EstimatedReturnDate] date NOT NULL,
43 [ActualReturnDate] date NULL,
44 PRIMARY KEY ([StudentId], [BookInstanceId], [IssueDate])
45)
46
47CREATE TABLE [dbo].[Penalty] (
48 [Id] int PRIMARY KEY IDENTITY(1,1) NOT NULL,
49 [StudentId] int FOREIGN KEY REFERENCES [dbo].[Student](Id),
50 [AccountRecordDate] date NOT NULL
51)
52
53CREATE PROCEDURE [dbo].[ClearTables]
54AS
55BEGIN
56 -- Чистка книг
57 BEGIN TRANSACTION tran_1;
58 BEGIN TRY
59 DELETE FROM
60 [dbo].[Book]
61 WHERE NOT EXISTS (
62 SELECT
63 [dbo].[BookInstance].[Id]
64 FROM
65 [dbo].[BookInstance]
66 INNER JOIN
67 [dbo].[Book]
68 ON [dbo].[Book].[Id] = [dbo].[BookInstance].[BookId]
69 )
70 END TRY
71 BEGIN CATCH
72 IF @@TRANCOUNT > 0
73 ROLLBACK TRANSACTION tran_1;
74
75 END CATCH
76
77 IF @@TRANCOUNT > 0
78 COMMIT TRANSACTION tran_1;
79
80 -- Чистка Групп
81 BEGIN TRANSACTION tran_2;
82 BEGIN TRY
83 DELETE FROM
84 [dbo].[Group]
85 WHERE NOT EXISTS (
86 SELECT
87 [dbo].[Student].[Id]
88 FROM
89 [dbo].[Student]
90 INNER JOIN
91 [dbo].[Group]
92 ON [dbo].[Group].[Id] = [dbo].[Student].[GroupId]
93 )
94 END TRY
95 BEGIN CATCH
96 IF @@TRANCOUNT > 0
97 ROLLBACK TRANSACTION tran_2;
98 END CATCH
99
100 IF @@TRANCOUNT > 0
101 COMMIT TRANSACTION tran_2;
102
103 -- Чистка Специальности
104 BEGIN TRANSACTION tran_3;
105 BEGIN TRY
106 DELETE FROM
107 [dbo].[Speciality]
108 WHERE NOT EXISTS (
109 SELECT
110 [dbo].[Group].[Id]
111 FROM
112 [dbo].[Group]
113 INNER JOIN
114 [dbo].[Speciality]
115 ON [dbo].[Speciality].[Id] = [dbo].[Group].[SpecialityId]
116 )
117 END TRY
118 BEGIN CATCH
119 IF @@TRANCOUNT > 0
120 ROLLBACK TRANSACTION tran_3;
121 END CATCH
122
123 IF @@TRANCOUNT > 0
124 COMMIT TRANSACTION tran_3;
125
126END