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