· 4 years ago · Dec 11, 2020, 07:20 PM
1/*
2 * creation DB for lab
3 */
4
5use [master]
6drop database if exists [Lab1];
7create database [Lab1];
8use [Lab1];
9
10
11create table [Animator] (
12 [ID] integer identity(1,1),
13
14 [Number] char(10) not null check([Number] like('[A-Z0-9][A-Z0-9][-][A-Z0-9][A-Z0-9][A-Z0-9][-][A-Z0-9][A-Z0-9][A-Z0-9]')),
15 [FirstName] char(20),
16 [MiddleName] char(20),
17 [LastName] char(20),
18 [Status] bit not null default(1),
19 [Hired] date not null default(getdate()),
20 [Fired] date null,
21
22 constraint [CHK_Fired] check ([Fired] >= [Hired] or [Fired] is null),
23 constraint [AK1_Number] unique ([Number]),
24
25 primary key([ID])
26);
27create table [Nickname] (
28 [ID] integer identity(1,1),
29
30 [Nickname] varchar(20) not null,
31 [Description] varchar(255),
32 -- Вычесляемое поле. Занята ли кличка или нет.
33 [Status] bit not null default(0),
34
35 constraint [AK1_Nickname] unique([Nickname]),
36
37 primary key([ID])
38);
39create table [AnimatorsNic] (
40 [AnimatorID] integer not null,
41 [NicID] integer not null,
42
43 [Reason] varchar(50),
44
45 foreign key([AnimatorID]) references [Animator]([ID]),
46 foreign key([NicID]) references [Nickname]([ID]),
47
48 primary key([AnimatorID], [NicID])
49);
50create table [Health] (
51 [AnimatorID] integer not null,
52
53 [Max] smallint not null check([Max] >=0 and [Max] <= 100),
54 [Current] smallint not null,
55 [MaxSatiety] smallint not null check([MaxSatiety] >=0 and [MaxSatiety] <= 100),
56 [CurrentSatiety] smallint not null,
57 [LastMeal] datetime default(getdate()),
58
59 constraint [CHK_Health] check ([Current] <= [Max] and [Current] >= 0),
60 constraint [CHK_Satiety] check ([CurrentSatiety] <= [MaxSatiety] and [CurrentSatiety]>= 0),
61
62 foreign key([AnimatorID]) references [Animator]([ID]),
63
64 primary key([AnimatorID])
65);
66create table [Scenario] (
67 [ID] integer identity(1,1),
68
69 [Name] varchar(50) not null,
70 [Scenario] text not null,
71
72 primary key([ID])
73);
74create table [Service] (
75 [ServiceID] integer identity(1,1),
76 [ScenarioID] integer not null,
77
78 [Name] varchar(50) not null,
79 [TargetAge] varchar(5) not null default('6+'),
80 [Duration] float not null,
81 [Price] money not null,
82 [Description] varchar(255) not null,
83
84 foreign key([ScenarioID]) references [Scenario]([ID]),
85
86 primary key([ServiceID], [ScenarioID])
87);
88create table [Specialization] (
89 [AnimatorID] integer not null,
90 [ServiceID] integer not null,
91 [ScenarioID] integer not null,
92
93 [Grade] smallint,
94 [Sertificat] bit not null default(0),
95
96 foreign key([ServiceID], [ScenarioID]) references [Service]([ServiceID], [ScenarioID]),
97 foreign key([AnimatorID]) references [Animator]([ID]),
98
99 primary key([AnimatorID], [ServiceID], [ScenarioID])
100);
101create table [Client] (
102 [ID] integer identity(1,1),
103
104 [FirstName] varchar(20) not null,
105 [MiddleName] varchar(20) not null,
106 [LastName] varchar(20) not null,
107 [Phone] char(14) not null,
108 [Email] varchar(25) not null,
109
110 constraint [AK1_Phone] unique([Phone]),
111 constraint [AK2_Email] unique([Email]),
112
113 primary key([ID])
114);
115create table [Order] (
116 [ID] integer identity(1,1),
117
118 [ClientID] integer not null,
119 [Price] money not null default(0),
120 [OrderDate] datetime not null default(getdate()),
121 [ExecutionDate] datetime not null default(getdate()),
122
123 constraint [CHK_Price] check ([Price] >= 0),
124 constraint [CHK_OrderDate] check ([ExecutionDate] >= [OrderDate]),
125
126 foreign key([ClientID]) references [Client]([ID]),
127
128 primary key([ID])
129);
130create table [ServiceOrder] (
131 [ServiceID] integer not null,
132 [ScenarioID] integer not null,
133 [AnimatorID] integer not null,
134 [OrderID] integer not null,
135 [Status] bit not null default(1),
136
137 foreign key([ServiceID], [ScenarioID]) references [Service]([ServiceID], [ScenarioID]),
138 foreign key([AnimatorID]) references [Animator]([ID]),
139 foreign key([OrderID]) references [Order]([ID]),
140
141 primary key([ServiceID], [ScenarioID], [AnimatorID], [OrderID])
142);
143create table [Payment] (
144 [ID] integer identity(1,1),
145
146 [OrderID] integer not null,
147 [FDP] char(10) not null check([FDP] like('[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')),
148 [FullPayment] bit not null default(1),
149 [Approved] bit,
150 [Date] datetime not null default(getdate()),
151
152 foreign key([OrderID]) references [Order]([ID]),
153
154 primary key([ID])
155);
156create table [Food] (
157 [ID] integer identity(1,1),
158
159 [Name] varchar(20) not null,
160 [Price] money not null,
161 [Calorific] float not null,
162
163 primary key([ID])
164);
165create table [FoodPayment] (
166 [FoodID] integer not null,
167 [PaymentID] integer not null,
168
169 [Date] datetime not null default(getdate()),
170 [Count] integer not null default(1) check([Count] > 0),
171
172 foreign key([FoodID]) references [Food]([ID]),
173 foreign key([PaymentID]) references [Payment]([ID]),
174
175 primary key([FoodID], [PaymentID])
176);
177create table [Journal] (
178 [ID] integer identity(1,1),
179
180 [ScenarioID] integer not null,
181 [ServiceID] integer not null,
182 [OrderID] integer not null,
183 [AnimatorID] integer not null,
184 [Date] datetime not null default(getdate()),
185
186 foreign key([ServiceID], [ScenarioID], [AnimatorID], [OrderID]) references [ServiceOrder]([ServiceID], [ScenarioID], [AnimatorID], [OrderID]),
187
188 primary key([ID])
189);
190create table [Limetation] (
191 [ID] integer identity(1,1),
192
193 [Description] varchar(255) not null,
194 [CreatedAt] datetime not null default(getdate()),
195 [UpdatedAt] datetime not null default(getdate()),
196
197 constraint [CHK_Updated] check ([UpdatedAt] >= [CreatedAt]),
198
199 primary key([ID])
200);
201create table [ScenarioLimetation] (
202 [LimetationID] integer not null,
203 [ScenarioID] integer not null,
204
205 [Date] datetime not null default(getdate()),
206 [Status] bit not null default(1),
207
208 foreign key([LimetationID]) references [Limetation]([ID]),
209 foreign key([ScenarioID]) references [Scenario]([ID]),
210
211 primary key([LimetationID], [ScenarioID])
212);
213create table [OrderLimetation] (
214 [LimetationID] integer not null,
215 [ScenarioID] integer not null,
216 [JournalID] integer not null,
217
218 [Date] datetime not null default(getdate()),
219
220 foreign key([LimetationID], [ScenarioID]) references [ScenarioLimetation]([LimetationID], [ScenarioID]),
221 foreign key([JournalID]) references [Journal]([ID]),
222
223 primary key([LimetationID], [ScenarioID], [JournalID])
224);
225create table [Dump] (
226 [ID] integer identity(1,1),
227
228 [Name] varchar(20),
229 [Address] varchar(50) not null,
230
231 primary key([ID])
232);
233create table [Registration] (
234 [AnimatorID] integer not null,
235 [DumpID] integer not null,
236
237 [Status] bit not null default(1),
238
239 foreign key([AnimatorID]) references [Animator]([ID]),
240 foreign key([DumpID]) references [Dump]([ID]),
241
242 primary key([AnimatorID], [DumpID])
243);
244create table [FoodList] (
245 [FoodID] integer not null,
246 [DumpID] integer not null,
247
248 [OwnerID] integer not null,
249 [Count] integer default(1) check([Count] >= 0),
250 [Fresh] datetime not null,
251
252 foreign key([FoodID]) references [Food]([ID]),
253 foreign key([DumpID]) references [Dump]([ID]),
254
255 primary key([FoodID], [DumpID], [OwnerID])
256);
257create table [Matherial] (
258 [ID] integer identity(1,1),
259
260 [Name] varchar(20) not null,
261 [Description] varchar(255),
262
263 primary key([ID])
264);
265create table [MatherialList] (
266 [DumpID] integer not null,
267 [MatherialID] integer not null,
268
269 [Count] integer default(1) check([Count] >= 0),
270
271 foreign key([MatherialID]) references [Matherial]([ID]),
272 foreign key([DumpID]) references [Dump]([ID]),
273
274 primary key([DumpID],[MatherialID])
275);
276create table [Costume] (
277 [ID] integer identity(1,1),
278
279 [Name] varchar(20) not null,
280 [Description] varchar(255),
281 [Durability] integer not null,
282 [Status] bit not null default(1),
283
284 primary key([ID])
285);
286create table [Receipt] (
287 [CostumeID] integer not null,
288 [MatherialID] integer not null,
289
290 [AsembleCount] integer default(1) check([AsembleCount] >= 0),
291 [DisasembleCount] integer default(1) check([DisasembleCount] >= 0),
292
293 constraint [CHK_Count] check ([AsembleCount] >= [DisasembleCount]),
294
295 foreign key([MatherialID]) references [Matherial]([ID]),
296 foreign key([CostumeID]) references [Costume]([ID]),
297
298 primary key([CostumeID],[MatherialID])
299);
300create table [ServiceCostume] (
301 [CostumeID] integer not null,
302 [ServiceID] integer not null,
303 [ScenarioID] integer not null,
304
305 [Count] integer default(1) check([Count] >= 0),
306 [Damage] integer default(1) check([Damage] >= 0),
307
308 foreign key([ServiceID], [ScenarioID]) references [Service]([ServiceID],[ScenarioID]),
309 foreign key([CostumeID]) references [Costume]([ID]),
310
311 primary key([CostumeID],[ServiceID],[ScenarioID])
312);