DjCY6Dv8

1--CREATE DATABASE [Lab2ASolution]
2
3
4
5USE [Lab2ASolution]
6
7
8
9GO
10
11
12
13-- Create DROP TABLE statements
14
15IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'JobSupply')
16
17	DROP TABLE JobSupply
18
19IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Supply')
20
21	DROP TABLE Supply
22
23IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'SupplyCategory')
24
25	DROP TABLE SupplyCategory
26
27IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'JobService')
28
29	DROP TABLE JobService
30
31IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Service')
32
33	DROP TABLE [Service]
34
35IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Job')
36
37	DROP TABLE Job
38
39IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'StaffTraining')
40
41	DROP TABLE StaffTraining
42
43IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Training')
44
45    DROP TABLE Training
46
47IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Staff')
48
49    DROP TABLE Staff
50
51IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'StaffType')
52
53    DROP TABLE StaffType
54
55IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Payment')
56
57    DROP TABLE Payment
58
59IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Client')
60
61    DROP TABLE Client
62
63
64
65
66
67-- Create Table Statements
68
69CREATE TABLE Client
70
71(  
72
73    ClientID            int
74
75        CONSTRAINT PK_Client_ClientID
76
77        PRIMARY KEY		
78
79		IDENTITY(1,1)						NOT NULL,
80
81    FirstName           varchar(50)			NOT NULL,
82
83    LastName            varchar(50)			NOT NULL,
84
85    Phone               varchar(14)			
86
87		CONSTRAINT CK_Client_Phone
88
89			CHECK (Phone LIKE '([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]')
90
91											NOT NULL,
92
93    Balance             money				NOT NULL
94
95)
96
97
98
99CREATE TABLE Payment
100
101(
102
103    PaymentID           int             
104
105        CONSTRAINT PK_Payment_PaymentID
106
107        PRIMARY KEY							NOT NULL,
108
109    [Date]              datetime			NOT NULL,
110
111    Amount              smallmoney			NOT NULL,
112
113    ClientID            int
114
115        CONSTRAINT FK_Payment_ClientID_Client_ClientID
116
117        FOREIGN KEY REFERENCES
118
119        Client(ClientID)					NOT NULL
120
121)
122
123
124
125CREATE TABLE StaffType  
126
127(
128
129    StaffTypeCode       int
130
131        CONSTRAINT PK_StaffType_StaffTypeCode
132
133        PRIMARY KEY		
134
135		IDENTITY(1,1)						NOT NULL,
136
137    [Description]       varchar(100)		NOT NULL,
138
139    Wage                smallmoney		
140
141		CONSTRAINT DF_StaffType_Wage
142
143		DEFAULT (20)	
144
145		CONSTRAINT CK_StaffType_Wage
146
147		CHECK (Wage >= 0)					NOT NULL
148
149)
150
151
152
153CREATE TABLE Staff
154
155(
156
157    StaffID             int
158
159        CONSTRAINT PK_Staff_StaffID
160
161        PRIMARY KEY							NOT NULL,
162
163    FirstName           varchar(50)			NOT NULL,
164
165    LastName            varchar(50)			NOT NULL,
166
167    TrainingCredits     smallint			NOT NULL,
168
169    Phone               varchar(14)			NOT NULL,
170
171    StaffTypeCode       int
172
173        CONSTRAINT FK_Staff_StaffTypeCode_StaffType_StaffTypeCode
174
175        FOREIGN KEY REFERENCES
176
177        StaffType(StaffTypeCode)			NOT NULL
178
179)
180
181
182
183CREATE TABLE Training
184
185(
186
187    TrainingID          varchar(20)     
188
189        CONSTRAINT PK_Training_TrainingID
190
191        PRIMARY KEY							NOT NULL,
192
193    [Description]       varchar(100)		NOT NULL,
194
195    Credits             tinyint				
196
197		CONSTRAINT CK_Training_Credits
198
199		DEFAULT (3)
200
201		CHECK (Credits <= 6)				NOT NULL
202
203)
204
205
206
207CREATE TABLE StaffTraining
208
209(
210
211    StaffID             int
212
213        CONSTRAINT FK_StaffTraining_StaffID_Staff_StaffID
214
215        FOREIGN KEY REFERENCES
216
217        Staff(StaffID)						NOT NULL,
218
219    TrainingID          varchar(20)					
220
221		CONSTRAINT FK_StaffTraining_TrainingID_Training_TrainingID
222
223		FOREIGN KEY REFERENCES
224
225		Training(TrainingID)				NOT NULL,
226
227	CompletionDate		datetime			NOT NULL
228
229
230
231		CONSTRAINT PK_StaffTraining_TrainingID_StaffTraining_TrainingID
232
233		PRIMARY KEY (StaffID, TrainingID)
234
235)
236
237
238
239CREATE TABLE Job
240
241(
242
243	JobNumber			int
244
245		CONSTRAINT PK_Job_JobNumber
246
247		PRIMARY KEY		
248
249		IDENTITY(1,1)						NOT NULL,
250
251	[Date]				datetime			NOT NULL,
252
253	[Address]			varchar(100)		NOT NULL,
254
255	City				varchar(50)			NOT NULL,
256
257	Province			char(2)				
258
259		CONSTRAINT CK_Job_Province
260
261			 CHECK (Province = 'AB' OR
262
263					Province = 'BC' OR
264
265					Province = 'SK' OR
266
267					Province = 'MB' OR
268
269					Province = 'QC' OR
270
271					Province = 'ON' OR
272
273					Province = 'NT' OR
274
275					Province = 'NS' OR
276
277					Province = 'NB' OR
278
279					Province = 'NL' OR
280
281					Province = 'YK' OR
282
283					Province = 'NU' OR
284
285					Province = 'PE')		NOT NULL,
286
287	
288
289	PostalCode			char(7)				
290
291		CONSTRAINT CK_Job_PostalCode
292
293			CHECK (PostalCode LIKE '[A-Z][0-9][A-Z] [0-9][A-Z][0-9]')
294
295											NOT NULL,
296
297	SubTotal			money				
298
299		CONSTRAINT CK_Job_SubTotal
300
301			CHECK (SubTotal > 0)			NOT NULL,
302
303	GST					money				NOT NULL,
304
305	Total				money				
306
307		CONSTRAINT CK_Job_Total
308
309			CHECK (Total >= 0)				NOT NULL,
310
311			
312
313	ClientID			int			
314
315		CONSTRAINT FK_Job_JobNumber_Client_ClientID
316
317		FOREIGN KEY REFERENCES
318
319		Client(ClientID)					NOT NULL,
320
321	StaffID				int			
322
323		CONSTRAINT FK_Job_StaffID_Staff_StaffID
324
325		FOREIGN KEY REFERENCES
326
327		Staff(StaffID)						NOT NULL
328
329
330
331)
332
333
334
335CREATE TABLE [Service]
336
337(
338
339	ServiceCode			varchar(15)	
340
341		CONSTRAINT PK_Service_ServiceCode
342
343		PRIMARY KEY							NOT NULL,
344
345	[Description]		varchar(100)		NOT NULL,
346
347	CostPerHour			smallmoney			NOT NULL	
348
349)
350
351
352
353CREATE TABLE JobService
354
355(
356
357	JobNumber			int
358
359		CONSTRAINT FK_JobService_JobNumber_Job_JobNumber
360
361		FOREIGN KEY REFERENCES
362
363		Job(JobNumber)						NOT NULL,
364
365	ServiceCode			varchar(15)
366
367		CONSTRAINT FK_JobService_ServiceCode_Service_ServiceCode
368
369		FOREIGN KEY REFERENCES
370
371		[Service](ServiceCode)				NOT NULL,
372
373	Notes				varchar(200)		NOT NULL,
374
375	[Hours]				int					NOT NULL,
376
377	ActualCostPerHour	smallmoney			NOT NULL,
378
379	ExtCost				smallmoney			NOT NULL
380
381
382
383		CONSTRAINT PK_JobService_JobNumber_JobService_ServiceCode
384
385		PRIMARY KEY (JobNumber, ServiceCode)
386
387)
388
389
390
391CREATE TABLE SupplyCategory
392
393(
394
395	SupplyCategoryCode	varchar(5)	
396
397		CONSTRAINT PK_SupplyCategory_SupplyCategoryCode
398
399		PRIMARY KEY							NOT NULL,
400
401	[Description]		varchar(100)		NOT NULL,
402
403	StorageRoom			varchar(5)			NOT NULL
404
405)
406
407
408
409CREATE TABLE Supply
410
411(
412
413	SupplyCode			varchar(8)
414
415		CONSTRAINT PK_Supply_SupplyCode
416
417		PRIMARY KEY							NOT NULL,
418
419	[Description]		varchar(100)		NOT NULL,
420
421	SupplyCategoryCode	varchar(5)		
422
423		CONSTRAINT FK_Supply_SupplyCode_SupplyCategory_SupplyCategoryCode
424
425		FOREIGN KEY REFERENCES
426
427		SupplyCategory(SupplyCategoryCode)	NOT NULL
428
429)
430
431
432
433CREATE TABLE JobSupply
434
435(
436
437	JobNumber			int
438
439		CONSTRAINT FK_JobSupply_JobNumber_Job_JobNumber
440
441		FOREIGN KEY REFERENCES
442
443		Job(JobNumber)						NOT NULL,
444
445	SupplyCode			varchar(8)		
446
447		CONSTRAINT FK_JobSupply_SupplyCode_Supply_SupplyCode
448
449		FOREIGN KEY REFERENCES
450
451		Supply(SupplyCode)					NOT NULL,
452
453	Quantity			smallint			NOT NULL
454
455
456
457		CONSTRAINT PK_JobSupply_JobNumber_JobSupply_SupplyCode
458
459		PRIMARY KEY (JobNumber, SupplyCode)
460
461)
462
463
464
465-- Alter Client to have Email
466
467ALTER TABLE Client
468
469	ADD Email			varchar(100)		
470
471		CONSTRAINT CK_Client_Email
472
473			CHECK (Email LIKE '%_@_%._%')	
474
475												NULL
476
477
478
479-- Alter Staff to have Avaiablility
480
481-- Default Y, can only be stored as Y or N
482
483
484
485ALTER TABLE Staff
486
487	ADD Available		char(1)
488
489		CONSTRAINT DF_Staff_Available
490
491			DEFAULT ('Y')
492
493		CONSTRAINT CK_Staff_Available
494
495			CHECK (Available LIKE 'Y' OR Available = 'N')
496
497											NOT NULL
498
499
500
501-- Alter Job to have default AB for Province
502
503ALTER TABLE Job
504
505	ADD CONSTRAINT DF_Job_Province
506
507		DEFAULT 'AB' FOR Province		
508
509
510
511-- Create indexes for all FK's
512
513CREATE NONCLUSTERED INDEX IX_Payment_ClientID
514
515	ON Payment(ClientID)
516
517CREATE NONCLUSTERED INDEX IX_Staff_StaffTypeCode
518
519	ON Staff(StaffTypeCode)
520
521CREATE NONCLUSTERED INDEX IX_StaffTraining_StaffID
522
523	ON StaffTraining(StaffID)
524
525CREATE NONCLUSTERED INDEX IX_StaffTraining_TrainingID
526
527	ON StaffTraining(TrainingID)
528
529CREATE NONCLUSTERED INDEX IX_Job_ClientID
530
531	ON Job(ClientID)
532
533CREATE NONCLUSTERED INDEX IX_Job_StaffID
534
535	ON Job(StaffID)
536
537CREATE NONCLUSTERED INDEX IX_JobService_JobNumber
538
539	ON JobService(JobNumber)
540
541CREATE NONCLUSTERED INDEX IX_JobService_ServiceCode
542
543	ON JobService(ServiceCode)
544
545CREATE NONCLUSTERED INDEX IX_Supply_SupplyCategoryCode
546
547	ON Supply(SupplyCategoryCode)
548
549CREATE NONCLUSTERED INDEX IX_JobSupply_JobNumber
550
551	ON JobSupply(JobNumber)
552
553CREATE NONCLUSTERED INDEX IX_JobSupply_SupplyCode
554
555	ON JobSupply(SupplyCode)