· 9 years ago · Dec 15, 2016, 02:28 AM
1-- lab01 Matthiesen Matthew
2
3SET NOCOUNT ON
4
5-- create new db every time
6use master
7if exists
8(
9 select [name]
10 from sysdatabases
11 where [name] = 'mmatthiesen2_lab01'
12)
13drop database mmatthiesen2_lab01
14create database mmatthiesen2_lab01
15go
16
17-- use new db
18use mmatthiesen2_lab01
19
20-- create new tables every time
21-- delete and create occur in reverse order ebcause of the dependencies between tables
22if exists
23(
24 select [name]
25 from sysobjects
26 where [name] = 'Locker'
27)
28drop table Locker
29go
30
31if exists
32(
33 select [name]
34 from sysobjects
35 where [name] = 'Student'
36)
37drop table Student
38go
39
40
41if exists
42(
43 select [name]
44 from sysobjects
45 where [name] = 'Program'
46)
47drop table Program
48go
49
50
51create table Program
52(
53 ProgramID nvarchar(4) not null constraint PK_ProgramID primary key clustered,
54 CommonName nvarchar(45) not null
55)
56go
57
58-- insert programs we want per spec
59insert Program(ProgramID, CommonName)
60values
61 ('CMPE', 'Computer Engineering Technology'),
62 ('CMPA', 'Computer Network Administrator'),
63 ('NETE', 'Network Engineering Technology')
64go
65
66create table Student
67(
68 StudentID int identity(100000000, 1) not null constraint PK_StudentID primary key clustered,
69 FirstName nvarchar(25) not null,
70 LastName nvarchar(25) not null,
71 ProgramID nvarchar(4) constraint FK_programID_Student references Program (ProgramID) on delete no action
72)
73go
74
75create table Locker
76(
77 LockerID nvarchar(7) not null constraint PK_LockerID primary key clustered,
78 StudentID int constraint FK_StudentID_Locker references Student (StudentID),
79 AssignedOn datetime
80)
81go
82
83
84-- populate lockers according to spec
85if exists
86(
87 select [name]
88 from sysobjects
89 where [name] = 'PopLockers'
90)
91drop proc PopLockers
92go
93
94create proc PopLockers
95@errorMessage nvarchar(25) output
96as
97
98declare @locker int = 0,
99 @floor int
100
101while @locker <= 49
102begin
103 set @floor = 1
104 while @floor <= 3
105 begin
106 insert into Locker(LockerID)
107 values('W' + convert(varchar(1),@floor) + '-' + format(@locker,'000') + 'A'),
108 ('W' + convert(varchar(1),@floor) + '-' + format(@locker,'000') + 'B')
109 set @floor = @floor + 1
110 end
111 set @locker = @locker + 1
112end
113set @errorMessage = 'OK'
114return 0
115go
116
117print 'Pop lockers'
118declare @outErrorMsg nvarchar(50)
119declare @outError int
120execute @outError = PopLockers @outErrorMsg output
121select @outError as 'Error', @outErrorMsg as 'Message'
122go
123
124-- procedure: AddLocker
125if exists
126(
127 select [name]
128 from sysobjects
129 where [name] = 'AddLocker'
130)
131drop proc AddLocker
132go
133
134create proc AddLocker
135 @locker nvarchar(7),
136 @errorMessage nvarchar(50) output
137as
138 if @locker is null
139 begin
140 set @errorMessage = 'Locker cannot be null'
141 return -1
142 end
143 if exists (select * from Locker where LockerID = @locker)
144 begin
145 set @errorMessage = 'Locker already exists'
146 return -1
147 end
148 if (@locker not like 'W[1-3]-[0][0-9][0-9][AB]')
149 begin
150 set @errorMessage = 'Formatting error: W[1-3]-[0][0-9][0-9][AB]'
151 return -1
152 end
153
154 insert Locker(LockerID)
155 values(@locker)
156 set @errorMessage = 'OK'
157 return 0
158go
159
160-- test AddLocker
161print '>>>>>>>>test AddLocker'
162declare @outErrorMsg nvarchar(50)
163declare @outError int
164declare @testLocker nvarchar(7) = 'W2-015B'
165
166-- delete tester
167if exists
168(
169 select *
170 from Locker
171 where LockerID = @testLocker
172)
173delete Locker
174where LockerID = @testLocker
175
176-- fail with null
177execute @outError = AddLocker null, @outErrorMsg output
178select @outError as 'Error', @outErrorMsg as 'Message'
179
180-- bad format
181execute @outError = AddLocker '42', @outErrorMsg output
182select @outError as 'Error', @outErrorMsg as 'Message'
183
184-- success
185execute @outError = AddLocker @testLocker, @outErrorMsg output
186select @outError as 'Error', @outErrorMsg as 'Message'
187
188-- exists
189execute @outError = AddLocker @testLocker, @outErrorMsg output
190select @outError as 'Error', @outErrorMsg as 'Message'
191go
192
193-- procedure: RemoveLocker
194if exists
195(
196 select [name]
197 from sysobjects
198 where [name] = 'RemoveLocker'
199)
200drop proc RemoveLocker
201go
202
203create procedure RemoveLocker
204 @Locker as nchar(7) = null,
205 @errorMessage nvarchar(50) output
206as
207 if @Locker is null
208 begin
209 set @errorMessage = 'LockerID cannot be NULL'
210 return -1
211 end
212 if not exists ( select * from Locker where LockerID = @Locker )
213 begin
214 set @errorMessage = 'LockerID does not exist'
215 return -1
216 end
217 if exists (select * from Locker where LockerID = @Locker and StudentID is not null)
218 begin
219 set @errorMessage = 'Locker must not be assigned'
220 return -1
221 end
222
223 delete Locker
224 where LockerID = @Locker
225 set @errorMessage = 'OK'
226 return 0
227go
228
229-- test RemoveLocker
230print '>>>>>>>>test RemoveLocker'
231declare @outErrorMsg nvarchar(50)
232declare @outError int
233declare @testLocker nvarchar(7) = 'W2-015B'
234-- fake student
235declare @fakeStudent int
236insert into Student(FirstName, LastName)
237 values('John', 'Scott')
238set @fakeStudent = @@identity
239
240-- test successful removal
241execute @outError = AddLocker @testLocker, @outErrorMsg output
242execute @outError = RemoveLocker @testLocker,@outErrorMsg output
243select @outError as 'Error', @outErrorMsg as 'Message'
244
245-- fail null
246execute @outError = RemoveLocker null, @outErrorMsg output
247select @outError as 'Error', @outErrorMsg as 'Message'
248
249-- doesn't exist
250execute @outError = RemoveLocker 'W3-999A', @outErrorMsg output
251select @outError as 'Error', @outErrorMsg as 'Message'
252
253-- is assigned
254execute @outError = AddLocker @testLocker, @outErrorMsg output
255update Locker
256 set StudentID = @fakeStudent, AssignedOn = GETDATE()
257 where LockerID = @testLocker
258
259execute @outError = RemoveLocker @testLocker, @outErrorMsg output
260select @outError as 'Error', @outErrorMsg as 'Message'
261go
262
263
264
265-- Procedure: AssignLocker
266if exists
267(
268 select [name]
269 from sysobjects
270 where [name] = 'AssignLocker'
271)
272drop proc AssignLocker
273go
274
275create proc AssignLocker
276 @student int,
277 @Locker nvarchar(7),
278 @errorMessage nvarchar(50) output
279as
280 if @student is null
281 begin
282 set @errorMessage = 'StudentID cannot be NULL'
283 return -1
284 end
285 if @Locker is null
286 begin
287 set @errorMessage = 'LockerID cannot be NULL'
288 return -1
289 end
290 if not exists( select * from Student where StudentID = @student)
291 begin
292 set @errorMessage = 'Student does not exist'
293 return -1
294 end
295 if not exists( select Locker.LockerID from Locker where LockerID = @Locker)
296 begin
297 set @errorMessage = 'Locker does not exist'
298 return -1
299 end
300 if exists(select * from Locker where LockerID = @Locker and AssignedOn is not null)
301 begin
302 set @errorMessage = 'Locker already assigned'
303 return -1
304 end
305
306 update Locker
307 set StudentID = @student, AssignedOn = GETDATE()
308 where LockerID = @Locker
309
310 set @errorMessage = 'OK'
311 return 0
312go
313
314-- test AssignLocker
315print '>>>>>>>>test AssignLocker'
316declare @outErrorMsg nvarchar(50),
317 @outError int
318-- fake student
319declare @fakeStudent int
320insert into Student(FirstName, LastName)
321 values('John', 'Scott')
322set @fakeStudent = @@identity
323
324--make a locker
325declare @testLocker nvarchar(7) = 'W2-015C'
326if not exists
327(
328 select *
329 from Locker
330 where LockerID = @testLocker
331)
332insert Locker(LockerID)
333 values(@testLocker)
334
335-- successful assignment
336execute @outError = AssignLocker @fakeStudent, @testLocker, @outErrorMsg output
337select @outError as 'Error', @outErrorMsg as 'Message'
338
339-- student null
340execute @outError = AssignLocker null, @testLocker, @outErrorMsg output
341select @outError as 'Error', @outErrorMsg as 'Message'
342
343-- locker null
344execute @outError = AssignLocker @fakeStudent, null, @outErrorMsg output
345select @outError as 'Error', @outErrorMsg as 'Message'
346
347-- bad locker
348execute @outError = AssignLocker @fakeStudent, 'W3-969', @outErrorMsg output
349select @outError as 'Error', @outErrorMsg as 'Message'
350
351-- already assigned
352execute @outError = AssignLocker @fakeStudent, @testLocker, @outErrorMsg output
353select @outError as 'Error', @outErrorMsg as 'Message'
354
355-- bad student
356execute @outError = AssignLocker '957485694', @testLocker, @outErrorMsg output
357select @outError as 'Error', @outErrorMsg as 'Message'
358go
359
360-- Procedure: ReleaseLocker
361if exists
362(
363 select [name]
364 from sysobjects
365 where [name] = 'ReleaseLocker'
366)
367drop proc ReleaseLocker
368go
369
370create proc ReleaseLocker
371 @Locker nvarchar(7),
372 @errorMessage nvarchar(50) output
373as
374 if(@Locker is null)
375 begin
376 set @errorMessage = 'LockerID cannot be NULL'
377 return -1
378 end
379 if not exists(select * from Locker where LockerID = @Locker)
380 begin
381 set @errorMessage = 'Locker does not exist'
382 return -1
383 end
384 if exists(select * from Locker where LockerID = @Locker and AssignedOn is null)
385 begin
386 set @errorMessage = 'Locker has not been assigned'
387 return -1
388 end
389
390 delete Locker
391 where LockerID = @Locker
392
393 set @errorMessage = 'OK'
394 return 0
395go
396
397-- test ReleaseLocker
398print '>>>>>>>>test ReleaseLocker'
399declare @outErrorMsg nvarchar(50),
400 @outError int,
401 @testLocker nvarchar(7) = 'W2-015C'
402
403-- null
404execute @outError = ReleaseLocker null, @outErrorMsg output
405select @outError as 'Error', @outErrorMsg as 'Message'
406
407-- bad locker
408execute @outError = ReleaseLocker 'A1-001A', @outErrorMsg output
409select @outError as 'Error', @outErrorMsg as 'Message'
410
411-- unassigned
412execute @outError = ReleaseLocker 'W1-005A', @outErrorMsg output
413select @outError as 'Error', @outErrorMsg as 'Message'
414
415-- success
416execute @outError = ReleaseLocker @testLocker, @outErrorMsg output
417select @outError as 'Error', @outErrorMsg as 'Message'
418go
419
420-- Procedure: AddStudent
421if exists
422(
423 select [name]
424 from sysobjects
425 where [name] = 'AddStudent'
426)
427drop proc AddStudent
428go
429
430create procedure AddStudent
431 @firstName nvarchar(50),
432 @lastName nvarchar(50),
433 @programID nvarchar(4),
434 @errorMessage nvarchar(50) output
435as
436 if (@firstName is null)
437 begin
438 set @errorMessage = 'First Name cannot be NULL'
439 return -1
440 end
441 if (@lastName is null)
442 begin
443 set @errorMessage = 'Last Name cannot be NULL'
444 return -1
445 end
446 if (len(@firstName) < 2 or len(@firstName) > 25)
447 begin
448 set @errorMessage = 'First Name bad length'
449 return -1
450 end
451 if (len(@lastName) < 2 or len(@lastName) > 25)
452 begin
453 set @errorMessage = 'Last Name bad length'
454 return -1
455 end
456 if exists (select * from Student where FirstName = @firstName and LastName = @lastName)
457 begin
458 set @errorMessage = 'Student exists'
459 return -1
460 end
461 if not exists (select * from Program where ProgramID = @programID)
462 begin
463 set @errorMessage = 'Program does not exist'
464 return -1
465 end
466
467 insert Student(FirstName, LastName)
468 values(@firstName, @lastName)
469
470 set @errorMessage = 'OK'
471 return 0
472go
473
474-- test AddStudent
475print '>>>>>>>>test AddStudent'
476declare @outErrorMsg nvarchar(50)
477declare @outError int
478
479-- fname null
480execute @outError = AddStudent null, 'Scott', 'CMPE', @outErrorMsg output
481select @outError as 'Error', @outErrorMsg as 'Message'
482
483-- lname null
484execute @outError = AddStudent 'John', null, 'CMPE', @outErrorMsg output
485select @outError as 'Error', @outErrorMsg as 'Message'
486
487-- fname length
488execute @outError = AddStudent 'Pablo Diego José Francisco de Paula Juan Nepomuceno MarÃa de los Remedios Cipriano de la SantÃsima Trinidad', 'Ruiz y Picasso', 'CMPE', @outErrorMsg output
489select @outError as 'Error', @outErrorMsg as 'Message'
490
491-- lname length
492execute @outError = AddStudent 'Pablo', 'MarÃa de los Remedios Cipriano de la SantÃsima Trinidad Ruiz y Picasso', 'CMPE', @outErrorMsg output
493select @outError as 'Error', @outErrorMsg as 'Message'
494
495-- bad program
496execute @outError = AddStudent 'John', 'Great Scott', 'NAH', @outErrorMsg output
497select @outError as 'Error', @outErrorMsg as 'Message'
498
499-- student exists
500execute @outError = AddStudent 'John', 'Scott', 'CMPE', @outErrorMsg output
501select @outError as 'Error', @outErrorMsg as 'Message'
502
503-- success
504execute @outError = AddStudent 'John', 'Great Scott', 'CMPE', @outErrorMsg output
505select @outError as 'Error', @outErrorMsg as 'Message'
506
507--Procedure: UpdateStudent
508if exists
509(
510 select [name]
511 from sysobjects
512 where [name] = 'UpdateStudent'
513)
514drop proc UpdateStudent
515go
516
517create procedure UpdateStudent
518 @student int,
519 @firstName nvarchar(50),
520 @lastName nvarchar(50),
521 @programID nvarchar(4),
522 @errorMessage nvarchar(50) output
523as
524 if (len(@firstName) < 2 or len(@firstName) > 25)
525 begin
526 set @errorMessage = 'First Name bad length'
527 return -1
528 end
529 if (len(@lastName) < 2 or len(@lastName) > 25)
530 begin
531 set @errorMessage = 'Last Name bad length'
532 return -1
533 end
534 if @student is null
535 begin
536 set @errorMessage = 'StudentID cannot be NULL'
537 return -1
538 end
539 if @firstName is null
540 begin
541 set @errorMessage = 'First name cannot be NULL'
542 return -1
543 end
544 if @lastName is null
545 begin
546 set @errorMessage = 'Last name cannot be NULL'
547 return -1
548 end
549 if @programID is null or
550 not exists (select * from Program where ProgramId = @programID)
551 begin
552 set @errorMessage = 'Program is null or does not exist'
553 return -1
554 end
555 if exists (select * from Student where StudentId = @student)
556 begin
557 set @errorMessage = 'Student exists'
558 return -1
559 end
560
561 update Student
562 set LastName = @lastName, FirstName = @firstName, ProgramId = @programID
563 where StudentId = @student
564 set @errorMessage = 'OK'
565 return 0
566go
567
568-- test UpdateStudent
569print '>>>>>>>>test UpdateStudent'
570declare @outErrorMsg nvarchar(50)
571declare @outError int
572
573-- student null
574execute @outError = UpdateStudent null, 'John', 'Scott', 'CMPE', @outErrorMsg output
575select @outError as 'Error', @outErrorMsg as 'Message'
576
577-- fname null
578execute @outError = UpdateStudent 123456789, null, 'Scott', 'CMPE', @outErrorMsg output
579select @outError as 'Error', @outErrorMsg as 'Message'
580
581-- lname null
582execute @outError = UpdateStudent 123456789, 'John', null, 'CMPE', @outErrorMsg output
583select @outError as 'Error', @outErrorMsg as 'Message'
584
585-- bad program
586execute @outError = UpdateStudent 123456789, 'John', 'Scott', 'DATBOI.EXE', @outErrorMsg output
587select @outError as 'Error', @outErrorMsg as 'Message'
588
589-- fname length
590execute @outError = UpdateStudent 123456789, 'Pablo Diego José Francisco de Paula Juan Nepomuceno MarÃa de los Remedios Cipriano de la SantÃsima Trinidad', 'Ruiz y Picasso', 'CMPE', @outErrorMsg output
591select @outError as 'Error', @outErrorMsg as 'Message'
592
593-- lname length
594execute @outError = UpdateStudent 123456789, 'Pablo', 'MarÃa de los Remedios Cipriano de la SantÃsima Trinidad Ruiz y Picasso', 'CMPE', @outErrorMsg output
595select @outError as 'Error', @outErrorMsg as 'Message'
596
597-- student exists
598execute @outError = UpdateStudent 123456789, 'John', 'Great Scott', 'CMPE', @outErrorMsg output
599select @outError as 'Error', @outErrorMsg as 'Message'
600
601-- Procedure: DeleteStudent
602if exists
603(
604 select [name]
605 from sysobjects
606 where [name] = 'DeleteStudent'
607)
608drop proc DeleteStudent
609go
610
611create procedure DeleteStudent
612 @student int,
613 @errorMessage nvarchar(50) output
614as
615 if @student is null
616 begin
617 set @errorMessage = 'Student cannot be NULL'
618 return -1
619 end
620 if not exists (select * from Student where StudentId = @student)
621 begin
622 set @errorMessage = 'Student does not exist'
623 return -1
624 end
625 if exists (select * from Locker where StudentId = @student)
626 begin
627 set @errorMessage = 'Student has locker assigned. Release locker first.'
628 return -1
629 end
630
631 delete from Locker
632 where StudentId = @student
633
634 set @errorMessage = 'OK'
635 return 0
636go
637
638-- test DeleteStudent
639print '>>>>>>>>test DeleteStudent'
640declare @outErrorMsg nvarchar(50)
641declare @outError int
642
643-- null
644execute @outError = DeleteStudent null, @outErrorMsg output
645select @outError as 'Error', @outErrorMsg as 'Message'
646
647-- non-existant
648execute @outError = DeleteStudent 111111111, @outErrorMsg output
649select @outError as 'Error', @outErrorMsg as 'Message'
650
651-- locker assigned
652declare @fakeStudent int
653insert into Student(FirstName, LastName)
654 values('John', 'Scott')
655set @fakeStudent = @@identity
656
657execute @outError = AssignLocker @fakeStudent, 'W1-001A', @outErrorMsg output
658execute @outError = DeleteStudent @fakeStudent, @outErrorMsg output
659select @outError as 'Error', @outErrorMsg as 'Message'
660
661execute @outError = ReleaseLocker 'W1-001A', @outErrorMsg output
662
663execute @outError = DeleteStudent @fakeStudent, @outErrorMsg output
664select @outError as 'Error', @outErrorMsg as 'Message'