· 8 years ago · Jun 04, 2018, 02:10 PM
1DROP TABLE IF EXISTS [studentInfo]
2
3GO
4DROP TABLE IF EXISTS [users]
5
6GO
7DROP TABLE IF EXISTS [marks]
8
9GO
10DROP TABLE IF EXISTS [warnings]
11
12GO
13DROP TABLE IF EXISTS [students]
14
15GO
16DROP TABLE IF EXISTS [parkingPlaces]
17
18GO
19DROP TABLE IF EXISTS [classesTeachers]
20
21GO
22DROP TABLE IF EXISTS [timetable]
23
24GO
25DROP TABLE IF EXISTS [subjectsTeachers]
26
27GO
28DROP TABLE IF EXISTS [ads]
29
30GO
31DROP TABLE IF EXISTS [teachers]
32
33GO
34DROP TABLE IF EXISTS [classes]
35
36GO
37DROP TABLE IF EXISTS [subjects]
38
39GO
40DROP TABLE IF EXISTS [employeeInfo]
41
42GO
43DROP TABLE IF EXISTS [employees]
44
45GO
46DROP TABLE IF EXISTS [salaries]
47
48GO
49DROP INDEX IF EXISTS [usernameAllowNull] ON [users]
50
51GO
52DROP INDEX IF EXISTS [parkingPlacesAllowNull] ON [parkingPlaces]
53
54GO
55DROP PROCEDURE IF EXISTS [reset]
56
57GO
58DROP PROCEDURE IF EXISTS [deleteTeacher]
59
60GO
61DROP PROCEDURE IF EXISTS [addEmployee]
62
63GO
64DROP PROCEDURE IF EXISTS [addMark]
65
66GO
67DROP PROCEDURE IF EXISTS [updateMark]
68
69GO
70DROP PROCEDURE IF EXISTS [addStudent]
71
72GO
73DROP PROCEDURE IF EXISTS [updateStudent]
74
75GO
76DROP PROCEDURE IF EXISTS [promoteStudent]
77
78GO
79DROP PROCEDURE IF EXISTS [addTeacher]
80
81GO
82DROP PROCEDURE IF EXISTS [fillParking]
83
84GO
85DROP VIEW IF EXISTS [loggedIn24]
86
87GO
88DROP FUNCTION IF EXISTS [showStudentMarks]
89
90GO
91DROP FUNCTION IF EXISTS [showTeacherMarks]
92
93GO
94DROP FUNCTION IF EXISTS [showMarksFromSubject]
95
96GO
97DROP FUNCTION IF EXISTS [showWarnings]
98
99GO
100DROP FUNCTION IF EXISTS [showAds]
101
102GO
103DROP FUNCTION IF EXISTS [showMarksInfo]
104
105GO
106DROP FUNCTION IF EXISTS [showStudentTimetable]
107
108GO
109DROP FUNCTION IF EXISTS [showTeacherTimetable]
110
111GO
112DROP FUNCTION IF EXISTS [checkPES]
113
114GO
115DROP FUNCTION IF EXISTS [checkEmail]
116
117GO
118DROP FUNCTION IF EXISTS [showUserSettings]
119
120GO
121DROP FUNCTION IF EXISTS [showTeacherClasses]
122
123GO
124DROP FUNCTION IF EXISTS [checkTeacherSubject]
125
126GO
127DROP FUNCTION IF EXISTS [searchStudentBySurname]
128
129GO
130DROP FUNCTION IF EXISTS [searchStudentByCardNumber]
131
132GO
133DROP FUNCTION IF EXISTS [searchStudentByClassName]
134
135GO
136DROP FUNCTION IF EXISTS [searchTeacherBySurname]
137
138GO
139DROP FUNCTION IF EXISTS [searchTeacherByCardNumber]
140
141GO
142DROP FUNCTION IF EXISTS [searchTeacherBySubjectName]
143
144GO
145DROP FUNCTION IF EXISTS [studentsRanking]
146
147GO
148DROP TRIGGER IF EXISTS [addUserStudent]
149
150GO
151DROP TRIGGER IF EXISTS [addUserTeacher]
152
153GO
154DROP TRIGGER IF EXISTS [deleteStudent]
155
156GO
157DROP TRIGGER IF EXISTS [assignParkingOnInsert]
158
159GO
160DROP TRIGGER IF EXISTS [assignParkingOnDelete]
161
162GO
163DROP TRIGGER IF EXISTS [cannotDeleteParkingPlace]
164
165GO
166----------------------------------------------------------------------------------------------------
167CREATE TABLE [studentInfo] (
168 studentInfoId INTEGER NOT NULL IDENTITY(1,1),
169 studentId INTEGER NOT NULL,
170 PES CHAR(11) NOT NULL UNIQUE,
171 dateOfBirth DATE NOT NULL,
172 street VARCHAR(30) NOT NULL,
173 houseNumber VARCHAR(10) NOT NULL,
174 postCode CHAR(6) NOT NULL,
175 city VARCHAR(30) NOT NULL,
176 email VARCHAR(50) NOT NULL UNIQUE,
177 CONSTRAINT [PK_STUDENTINFO] PRIMARY KEY CLUSTERED
178 (
179 [studentInfoId] ASC
180 ) WITH (IGNORE_DUP_KEY = OFF)
181
182)
183GO
184CREATE TABLE [students] (
185 studentId INTEGER NOT NULL IDENTITY(1,1),
186 name VARCHAR(20) NOT NULL,
187 surname VARCHAR(30) NOT NULL,
188 cardNumber CHAR(10) NOT NULL UNIQUE,
189 classId INTEGER NOT NULL,
190 CONSTRAINT [PK_STUDENTS] PRIMARY KEY CLUSTERED
191 (
192 [studentId] ASC
193 ) WITH (IGNORE_DUP_KEY = OFF)
194
195)
196GO
197CREATE TABLE [users] (
198 userId INTEGER NOT NULL IDENTITY(1,1),
199 studentId INTEGER,
200 teacherId INTEGER,
201 accountType VARCHAR(10) NOT NULL,
202 username AS 'u' + RIGHT('000' + CAST(userId AS VARCHAR(20)), 3) PERSISTED UNIQUE,
203 usernameAlias VARCHAR(30),
204 password CHAR(32) NOT NULL,
205 lastLogin DATETIME NOT NULL DEFAULT GETDATE(),
206 CONSTRAINT [PK_USERS] PRIMARY KEY CLUSTERED
207 (
208 [userId] ASC
209 ) WITH (IGNORE_DUP_KEY = OFF)
210
211)
212GO
213CREATE TABLE [teachers] (
214 teacherId INTEGER NOT NULL IDENTITY(1,1),
215 employeeId INTEGER NOT NULL,
216 cardNumber CHAR(10) NOT NULL UNIQUE,
217 CONSTRAINT [PK_TEACHERS] PRIMARY KEY CLUSTERED
218 (
219 [teacherId] ASC
220 ) WITH (IGNORE_DUP_KEY = OFF)
221
222)
223GO
224CREATE TABLE [classes] (
225 classId INTEGER NOT NULL IDENTITY(1,1),
226 name CHAR(2) NOT NULL UNIQUE,
227 CONSTRAINT [PK_CLASSES] PRIMARY KEY CLUSTERED
228 (
229 [classId] ASC
230 ) WITH (IGNORE_DUP_KEY = OFF)
231
232)
233GO
234CREATE TABLE [classesTeachers] (
235 ctId INTEGER NOT NULL IDENTITY(1,1),
236 classId INTEGER NOT NULL,
237 teacherId INTEGER,
238 subjectId INTEGER NOT NULL,
239 CONSTRAINT [PK_CLASSESTEACHERS] PRIMARY KEY CLUSTERED
240 (
241 [ctId] ASC
242 ) WITH (IGNORE_DUP_KEY = OFF)
243
244)
245GO
246CREATE TABLE [subjects] (
247 subjectId INTEGER NOT NULL IDENTITY(1,1),
248 name VARCHAR(30) NOT NULL UNIQUE,
249 CONSTRAINT [PK_SUBJECTS] PRIMARY KEY CLUSTERED
250 (
251 [subjectId] ASC
252 ) WITH (IGNORE_DUP_KEY = OFF)
253
254)
255GO
256CREATE TABLE [marks] (
257 markId INTEGER NOT NULL IDENTITY(1,1),
258 studentId INTEGER NOT NULL,
259 teacherId INTEGER NOT NULL,
260 subjectId INTEGER NOT NULL,
261 value FLOAT NOT NULL,
262 valueStr VARCHAR(2) NOT NULL,
263 markType VARCHAR(20) NOT NULL,
264 weight INTEGER NOT NULL,
265 addDate DATETIME NOT NULL DEFAULT GETDATE(),
266 semester TINYINT NOT NULL,
267 comment VARCHAR(255),
268 CONSTRAINT [PK_MARKS] PRIMARY KEY CLUSTERED
269 (
270 [markId] ASC
271 ) WITH (IGNORE_DUP_KEY = OFF)
272
273)
274GO
275CREATE TABLE [employees] (
276 employeeId INTEGER NOT NULL IDENTITY(1,1),
277 employeeType VARCHAR(30) NOT NULL,
278 name VARCHAR(20) NOT NULL,
279 surname VARCHAR(30) NOT NULL,
280 CONSTRAINT [PK_EMPLOYEES] PRIMARY KEY CLUSTERED
281 (
282 [employeeId] ASC
283 ) WITH (IGNORE_DUP_KEY = OFF)
284
285)
286GO
287CREATE TABLE [salaries] (
288 salaryId INTEGER NOT NULL IDENTITY(1,1),
289 value MONEY NOT NULL,
290 CONSTRAINT [PK_SALARIES] PRIMARY KEY CLUSTERED
291 (
292 [salaryId] ASC
293 ) WITH (IGNORE_DUP_KEY = OFF)
294
295)
296GO
297CREATE TABLE [employeeInfo] (
298 employeeInfoId INTEGER NOT NULL IDENTITY(1,1),
299 employeeId INTEGER NOT NULL,
300 salaryId INTEGER NOT NULL,
301 PES CHAR(11) NOT NULL UNIQUE,
302 dateOfBirth DATE NOT NULL,
303 street VARCHAR(30) NOT NULL,
304 houseNumber VARCHAR(10) NOT NULL,
305 postCode CHAR(6) NOT NULL,
306 city VARCHAR(30) NOT NULL,
307 email VARCHAR(50) NOT NULL,
308 CONSTRAINT [PK_EMPLOYEEINFO] PRIMARY KEY CLUSTERED
309 (
310 [employeeInfoId] ASC
311 ) WITH (IGNORE_DUP_KEY = OFF)
312
313)
314GO
315CREATE TABLE [parkingPlaces] (
316 parkingId INTEGER NOT NULL IDENTITY(1,1),
317 teacherId INTEGER DEFAULT NULL,
318 CONSTRAINT [PK_PARKINGPLACES] PRIMARY KEY CLUSTERED
319 (
320 [parkingId] ASC
321 ) WITH (IGNORE_DUP_KEY = OFF)
322
323)
324GO
325CREATE TABLE [timetable] (
326 lessonId INTEGER NOT NULL IDENTITY(1,1),
327 subjectId INTEGER NOT NULL,
328 teacherId INTEGER NOT NULL,
329 classId INTEGER NOT NULL,
330 lessonHour INTEGER NOT NULL,
331 dayString VARCHAR(12) NOT NULL,
332 classroom VARCHAR(17) NOT NULL,
333 CONSTRAINT [PK_LESSONPLAN] PRIMARY KEY CLUSTERED
334 (
335 [lessonId] ASC
336 ) WITH (IGNORE_DUP_KEY = OFF)
337
338)
339GO
340
341CREATE TABLE [warnings] (
342 warningId INTEGER NOT NULL IDENTITY(1,1),
343 studentId INTEGER NOT NULL,
344 teacherId INTEGER NOT NULL,
345 title VARCHAR(50) NOT NULL,
346 content VARCHAR(255) NOT NULL,
347 addDate DATETIME NOT NULL DEFAULT GETDATE(),
348 CONSTRAINT [PK_WARNINGS] PRIMARY KEY CLUSTERED
349 (
350 [warningId] ASC
351 ) WITH (IGNORE_DUP_KEY = OFF)
352)
353GO
354
355CREATE TABLE [ads] (
356 adId INTEGER NOT NULL IDENTITY(1,1),
357 teacherId INTEGER NOT NULL,
358 target VARCHAR(20) NOT NULL,
359 title VARCHAR(50) NOT NULL,
360 content VARCHAR(255) NOT NULL,
361 addDate DATETIME NOT NULL DEFAULT GETDATE(),
362 CONSTRAINT [PK_ADS] PRIMARY KEY CLUSTERED
363 (
364 [adId] ASC
365 ) WITH (IGNORE_DUP_KEY = OFF)
366)
367GO
368
369CREATE TABLE [subjectsTeachers] (
370 stId INTEGER NOT NULL IDENTITY(1,1),
371 subjectId INTEGER NOT NULL,
372 teacherId INTEGER NOT NULL,
373 CONSTRAINT [PK_SUBJECTSTEACHERS] PRIMARY KEY CLUSTERED
374 (
375 [stId] ASC
376 ) WITH (IGNORE_DUP_KEY = OFF)
377)
378GO
379----------------------------------------------------------------------------------------------------
380CREATE FUNCTION [checkPES] (@pes CHAR(11))
381RETURNS BIT
382AS
383BEGIN
384 IF (LEN(@pes) != 11) OR (ISNUMERIC(@pes) = 0)
385 RETURN 0
386
387 DECLARE @weights AS TABLE (numberPosition TINYINT IDENTITY(1,1), weight TINYINT)
388 INSERT INTO @weights VALUES (1), (3), (7), (9), (1), (3), (7), (9), (1), (3), (1)
389
390 IF (SELECT SUM(CONVERT(TINYINT, SUBSTRING(@pes, numberPosition, 1)) * weight) % 10 FROM @weights ) = 0
391 RETURN 1
392
393 RETURN 0
394END
395GO
396----------------------------------------------------------------------------------------------------
397CREATE FUNCTION [checkEmail] (@email VARCHAR(50))
398RETURNS BIT
399AS
400BEGIN
401 IF (@email = '' AND @email NOT LIKE '_%@__%.__%')
402 RETURN 0
403
404 RETURN 1
405END
406GO
407----------------------------------------------------------------------------------------------------
408CREATE FUNCTION [checkTeacherSubject] (@teacherId INTEGER, @subjectId INTEGER)
409RETURNS BIT
410AS
411BEGIN
412 DECLARE @subjectIdTeacher BIT
413 SELECT @subjectIdTeacher = COUNT(subjectId) FROM subjectsTeachers ST WHERE ST.teacherId = @teacherId AND ST.subjectId = @subjectId
414 RETURN @subjectIdTeacher
415END
416GO
417----------------------------------------------------------------------------------------------------
418ALTER TABLE [studentInfo] WITH CHECK ADD CONSTRAINT [studentInfo_fk0] FOREIGN KEY ([studentId]) REFERENCES [students]([studentId])
419GO
420ALTER TABLE [studentInfo] CHECK CONSTRAINT [studentInfo_fk0]
421GO
422ALTER TABLE [studentInfo] ADD CONSTRAINT [studentInfo_ch0] CHECK (dbo.checkPES(PES) = 1)
423GO
424ALTER TABLE [studentInfo] ADD CONSTRAINT [studentInfo_ch1] CHECK (dbo.checkEmail(email) = 1)
425GO
426----------------------------------------------------------------------------------------------------
427ALTER TABLE [users] WITH CHECK ADD CONSTRAINT [users_fk0] FOREIGN KEY ([studentId]) REFERENCES [students]([studentId])
428GO
429ALTER TABLE [users] CHECK CONSTRAINT [users_fk0]
430GO
431ALTER TABLE [users] WITH CHECK ADD CONSTRAINT [users_fk1] FOREIGN KEY ([teacherId]) REFERENCES [teachers]([teacherId])
432GO
433ALTER TABLE [users] CHECK CONSTRAINT [users_fk1]
434GO
435----------------------------------------------------------------------------------------------------
436ALTER TABLE [teachers] WITH CHECK ADD CONSTRAINT [teachers_fk0] FOREIGN KEY ([employeeId]) REFERENCES [employees]([employeeId])
437GO
438ALTER TABLE [teachers] CHECK CONSTRAINT [teachers_fk0]
439GO
440----------------------------------------------------------------------------------------------------
441ALTER TABLE [classesTeachers] WITH CHECK ADD CONSTRAINT [classesTeachers_fk0] FOREIGN KEY ([classId]) REFERENCES [classes]([classId])
442GO
443ALTER TABLE [classesTeachers] CHECK CONSTRAINT [classesTeachers_fk0]
444GO
445ALTER TABLE [classesTeachers] WITH CHECK ADD CONSTRAINT [classesTeachers_fk1] FOREIGN KEY ([teacherId]) REFERENCES [teachers]([teacherId])
446GO
447ALTER TABLE [classesTeachers] CHECK CONSTRAINT [classesTeachers_fk1]
448GO
449ALTER TABLE [classesTeachers] WITH CHECK ADD CONSTRAINT [classesTeachers_fk2] FOREIGN KEY ([subjectId]) REFERENCES [subjects]([subjectId])
450GO
451ALTER TABLE [classesTeachers] CHECK CONSTRAINT [classesTeachers_fk2]
452GO
453ALTER TABLE [classesTeachers] ADD CONSTRAINT [classesTeachers_ch0] CHECK (dbo.checkTeacherSubject(teacherId, subjectId) = 1)
454GO
455----------------------------------------------------------------------------------------------------
456ALTER TABLE [marks] WITH CHECK ADD CONSTRAINT [marks_fk0] FOREIGN KEY ([studentId]) REFERENCES [students]([studentId])
457GO
458ALTER TABLE [marks] CHECK CONSTRAINT [marks_fk0]
459GO
460ALTER TABLE [marks] WITH CHECK ADD CONSTRAINT [marks_fk1] FOREIGN KEY ([teacherId]) REFERENCES [teachers]([teacherId])
461GO
462ALTER TABLE [marks] CHECK CONSTRAINT [marks_fk1]
463GO
464ALTER TABLE [marks] WITH CHECK ADD CONSTRAINT [marks_fk2] FOREIGN KEY ([subjectId]) REFERENCES [subjects]([subjectId])
465GO
466----------------------------------------------------------------------------------------------------
467ALTER TABLE [employeeInfo] WITH CHECK ADD CONSTRAINT [employeeInfo_fk0] FOREIGN KEY ([employeeId]) REFERENCES [employees]([employeeId])
468GO
469ALTER TABLE [employeeInfo] CHECK CONSTRAINT [employeeInfo_fk0]
470GO
471ALTER TABLE [employeeInfo] WITH CHECK ADD CONSTRAINT [employeeInfo_fk1] FOREIGN KEY ([salaryId]) REFERENCES [salaries]([salaryId])
472GO
473ALTER TABLE [employeeInfo] CHECK CONSTRAINT [employeeInfo_fk1]
474GO
475ALTER TABLE [employeeInfo] WITH CHECK ADD CONSTRAINT [employeeInfo_fk2] FOREIGN KEY ([salaryId]) REFERENCES [salaries]([salaryId])
476GO
477ALTER TABLE [employeeInfo] CHECK CONSTRAINT [employeeInfo_fk2]
478GO
479ALTER TABLE [employeeInfo] ADD CONSTRAINT [employeeInfo_ch0] CHECK (dbo.checkPES(PES) = 1)
480GO
481ALTER TABLE [employeeInfo] ADD CONSTRAINT [employeeInfo_ch1] CHECK (dbo.checkEmail(email) = 1)
482GO
483----------------------------------------------------------------------------------------------------
484ALTER TABLE [parkingPlaces] WITH CHECK ADD CONSTRAINT [parkingPlaces_fk0] FOREIGN KEY ([teacherId]) REFERENCES [teachers]([teacherId])
485GO
486ALTER TABLE [parkingPlaces] CHECK CONSTRAINT [parkingPlaces_fk0]
487GO
488----------------------------------------------------------------------------------------------------
489ALTER TABLE [timetable] WITH CHECK ADD CONSTRAINT [timetable_fk0] FOREIGN KEY ([subjectId]) REFERENCES [subjects]([subjectId])
490GO
491ALTER TABLE [timetable] CHECK CONSTRAINT [timetable_fk0]
492GO
493ALTER TABLE [timetable] WITH CHECK ADD CONSTRAINT [timetable_fk1] FOREIGN KEY ([teacherId]) REFERENCES [teachers]([teacherId])
494GO
495ALTER TABLE [timetable] CHECK CONSTRAINT [timetable_fk1]
496GO
497ALTER TABLE [timetable] WITH CHECK ADD CONSTRAINT [timetable_fk2] FOREIGN KEY ([classId]) REFERENCES [classes]([classId])
498GO
499ALTER TABLE [timetable] CHECK CONSTRAINT [timetable_fk2]
500GO
501----------------------------------------------------------------------------------------------------
502ALTER TABLE [warnings] WITH CHECK ADD CONSTRAINT [warnings_fk0] FOREIGN KEY ([studentId]) REFERENCES [students]([studentId])
503GO
504ALTER TABLE [warnings] CHECK CONSTRAINT [warnings_fk0]
505GO
506ALTER TABLE [warnings] WITH CHECK ADD CONSTRAINT [warnings_fk1] FOREIGN KEY ([teacherId]) REFERENCES [teachers]([teacherId])
507GO
508ALTER TABLE [warnings] CHECK CONSTRAINT [warnings_fk1]
509----------------------------------------------------------------------------------------------------
510ALTER TABLE [ads] WITH CHECK ADD CONSTRAINT [ads_fk0] FOREIGN KEY ([teacherId]) REFERENCES [teachers]([teacherId])
511GO
512ALTER TABLE [ads] CHECK CONSTRAINT [ads_fk0]
513GO
514----------------------------------------------------------------------------------------------------
515ALTER TABLE [subjectsTeachers] WITH CHECK ADD CONSTRAINT [subjectsTeachers_fk0] FOREIGN KEY ([subjectId]) REFERENCES [subjects]([subjectId])
516GO
517ALTER TABLE [subjectsTeachers] CHECK CONSTRAINT [subjectsTeachers_fk0]
518GO
519ALTER TABLE [subjectsTeachers] WITH CHECK ADD CONSTRAINT [subjectsTeachers_fk1] FOREIGN KEY ([teacherId]) REFERENCES [teachers]([teacherId])
520GO
521ALTER TABLE [subjectsTeachers] CHECK CONSTRAINT [subjectsTeachers_fk1]
522GO
523----------------------------------------------------------------------------------------------------
524CREATE UNIQUE NONCLUSTERED INDEX [usernameAllowNull]
525ON [users] (usernameAlias)
526WHERE usernameAlias IS NOT NULL
527GO
528----------------------------------------------------------------------------------------------------
529CREATE UNIQUE NONCLUSTERED INDEX [parkingPlacesAllowNull]
530ON [parkingPlaces] (teacherId)
531WHERE teacherId IS NOT NULL
532GO
533----------------------------------------------------------------------------------------------------
534CREATE PROCEDURE [deleteTeacher] (@teacherId INTEGER)
535AS
536DECLARE @employeeId INTEGER
537SELECT @employeeId = employeeId FROM teachers WHERE teacherId = @teacherId
538DELETE FROM employeeInfo WHERE employeeId = @employeeId
539UPDATE parkingPlaces SET teacherId = NULL WHERE teacherId = @teacherId
540UPDATE timetable SET teacherId = NULL WHERE teacherId = @teacherId
541UPDATE ads SET teacherId = NULL WHERE teacherId = @teacherId
542UPDATE subjectsTeachers SET teacherId = NULL WHERE teacherId = @teacherId
543DELETE FROM users WHERE teacherId = @teacherId
544DELETE FROM teachers WHERE employeeId = @employeeId
545GO
546----------------------------------------------------------------------------------------------------
547CREATE PROCEDURE [addEmployee] (@name varchar(20), @surname varchar(20), @employeeType varchar(20),
548@salaryId INT, @PES CHAR(11), @dateOfBirth DATE, @street varchar(30), @houseNumber VARCHAR(10),
549@postCode CHAR(6), @city VARCHAR(30), @email varchar(50))
550AS
551DECLARE @employeeId INT
552INSERT INTO [employees] VALUES (@employeeType, @name, @surname)
553SELECT @employeeId = MAX(employeeId) FROM employees
554INSERT INTO [employeeInfo] VALUES (@employeeId, @salaryId, @PES, @dateOfBirth, @street, @houseNumber, @postCode, @city, @email)
555GO
556----------------------------------------------------------------------------------------------------
557CREATE PROCEDURE [addMark] (@studentId INTEGER, @teacherId INTEGER, @subjectId INTEGER, @valueStr VARCHAR(2), @markType VARCHAR(30), @addDate DATETIME = NULL, @comment VARCHAR(max), @weight INTEGER = 0, @value Float = 0, @semester TINYINT)
558AS
559
560IF @addDate IS NULL SET @addDate = GETDATE()
561IF @valueStr = '1' SET @value = 1
562ELSE IF @valueStr = '2-' SET @value = 1.75
563ELSE IF @valueStr = '2' SET @value = 2
564ELSE IF @valueStr = '2+' SET @value = 2.5
565ELSE IF @valueStr = '3-' SET @value = 2.75
566ELSE IF @valueStr = '3' SET @value = 3
567ELSE IF @valueStr = '3+' SET @value = 3.5
568ELSE IF @valueStr = '4-' SET @value = 3.75
569ELSE IF @valueStr = '4' SET @value = 4
570ELSE IF @valueStr = '4+' SET @value = 4.5
571ELSE IF @valueStr = '5-' SET @value = 4.75
572ELSE IF @valueStr = '5' SET @value = 5
573ELSE IF @valueStr = '6' SET @value = 6
574
575IF @markType = 'Kartkówka' SET @weight = 2
576ELSE IF @markType = 'Sprawdzian' SET @weight = 3
577ELSE IF @markType = 'Odpowiedź ustna' SET @weight = 2
578ELSE IF @markType = 'Aktywność' SET @weight = 1
579ELSE IF @markType = 'Zadanie domowe' SET @weight = 1
580ELSE IF @markType = 'Projekt' SET @weight = 3
581
582INSERT INTO [marks]
583(studentId, teacherId, subjectId, value, valueStr, markType, weight, addDate, comment, semester)
584VALUES
585(@studentId, @teacherId, @subjectId, @value, @valueStr, @markType, @weight, @addDate, @comment, @semester)
586GO
587----------------------------------------------------------------------------------------------------
588CREATE PROCEDURE [updateMark] (@markId INTEGER, @valueStr VARCHAR(2), @value Float = 0, @markType VARCHAR(30), @weight INTEGER = 0, @addDate DATETIME, @comment VARCHAR(max))
589AS
590
591IF @valueStr = '1' SET @value = 1
592ELSE IF @valueStr = '2-' SET @value = 1.75
593ELSE IF @valueStr = '2' SET @value = 2
594ELSE IF @valueStr = '2+' SET @value = 2.5
595ELSE IF @valueStr = '3-' SET @value = 2.75
596ELSE IF @valueStr = '3' SET @value = 3
597ELSE IF @valueStr = '3+' SET @value = 3.5
598ELSE IF @valueStr = '4-' SET @value = 3.75
599ELSE IF @valueStr = '4' SET @value = 4
600ELSE IF @valueStr = '4+' SET @value = 4.5
601ELSE IF @valueStr = '5-' SET @value = 4.75
602ELSE IF @valueStr = '5' SET @value = 5
603ELSE IF @valueStr = '6' SET @value = 6
604
605IF @markType = 'Kartkówka' SET @weight = 2
606ELSE IF @markType = 'Sprawdzian' SET @weight = 3
607ELSE IF @markType = 'Odpowiedź ustna' SET @weight = 2
608ELSE IF @markType = 'Aktywność' SET @weight = 1
609ELSE IF @markType = 'Zadanie domowe' SET @weight = 1
610ELSE IF @markType = 'Projekt' SET @weight = 3
611
612UPDATE [marks]
613SET valueStr = @valueStr, value = @value, markType = @markType, weight = @weight, addDate = @addDate, comment = @comment
614WHERE markId = @markId
615GO
616----------------------------------------------------------------------------------------------------
617CREATE PROCEDURE [addStudent] (@name VARCHAR(20), @surname VARCHAR(30), @PES CHAR(11), @cardNumber CHAR(10), @dateOfBirth DATE, @street VARCHAR(30), @houseNumber VARCHAR(10), @postCode CHAR(6), @city VARCHAR(30), @email VARCHAR(50), @className VARCHAR(2))
618AS
619
620DECLARE @classId INTEGER
621DECLARE @studentId INTEGER
622
623SELECT @classId = classId FROM classes WHERE name = @className
624
625INSERT INTO [students] VALUES (@name, @surname, @cardNumber, @classId)
626SELECT @studentId = MAX(studentId) FROM students
627INSERT INTO [studentInfo] VALUES (@studentId, @PES, @dateOfBirth, @street, @houseNumber, @postCode, @city, @email)
628GO
629----------------------------------------------------------------------------------------------------
630CREATE PROCEDURE [updateStudent] (@studentId INTEGER, @name VARCHAR(20), @surname VARCHAR(30), @PES CHAR (11), @cardNumber CHAR(10), @dateOfBirth DATE, @street VARCHAR(30), @houseNumber VARCHAR(10), @postCode CHAR(6), @city VARCHAR(30), @email VARCHAR(50), @className VARCHAR(2))
631AS
632
633DECLARE @classId INTEGER
634SELECT @classId = classId FROM classes WHERE name = @className
635
636UPDATE [students] SET name = @name, surname = @surname, cardNumber = @cardNumber, classId = @classId WHERE studentId = @studentId
637UPDATE [studentInfo] SET PES = @PES, dateOfBirth = @dateOfBirth, street = @street, houseNumber = @houseNumber, postCode = @postCode, city = @city, email = @email WHERE studentId = @studentId
638GO
639----------------------------------------------------------------------------------------------------
640CREATE PROCEDURE [promoteStudent] (@studentId INTEGER)
641AS
642
643DECLARE @classNumber CHAR(1)
644DECLARE @classChar CHAR(1)
645DECLARE @className CHAR(2)
646DECLARE @classId INTEGER
647
648SELECT @classNumber = SUBSTRING((SELECT C.name FROM classes C JOIN students S ON S.classId = C.classId WHERE S.studentId = @studentId), 1, 1) + 1
649SELECT @classChar = SUBSTRING((SELECT C.name FROM classes C JOIN students S ON S.classId = C.classId WHERE S.studentId = @studentId), 2, 1)
650SET @className = @classNumber + @classChar
651SELECT @classId = classId FROM classes WHERE name = @className
652
653BEGIN TRANSACTION
654BEGIN TRY
655 UPDATE [students] SET [classId] = @classId WHERE studentId = @studentId
656 COMMIT
657END TRY
658BEGIN CATCH
659 RAISERROR('Uczen nie moze dostac promocji (nie ma juz wyzszej klasy). Mozna juz go tylko usunac.', 16, 1)
660 ROLLBACK
661END CATCH
662GO
663----------------------------------------------------------------------------------------------------
664CREATE PROCEDURE [addTeacher] (@name VARCHAR(20), @surname VARCHAR(30), @salary MONEY, @cardNumber CHAR(10), @PES CHAR(11), @dateOfBirth DATE, @street VARCHAR(30), @houseNumber VARCHAR(10), @postCode CHAR(6), @city VARCHAR(30), @email VARCHAR(50))
665AS
666
667DECLARE @salaryId INTEGER
668DECLARE @employeeId INTEGER
669
670SELECT @salaryId = salaryId FROM salaries WHERE value = @salary
671
672INSERT INTO [employees] VALUES ('Nauczyciel', @name, @surname)
673
674SELECT @employeeId = MAX(employeeId) FROM employees
675
676INSERT INTO [employeeInfo] VALUES (@employeeId, @salaryId, @PES, @dateOfBirth, @street, @houseNumber, @postCode, @city, @email)
677INSERT INTO [teachers] VALUES (@employeeId, @cardNumber)
678GO
679----------------------------------------------------------------------------------------------------
680CREATE PROCEDURE [fillParking] (@amount INTEGER)
681AS
682
683DECLARE @i INTEGER = 0
684
685WHILE @i < @amount
686BEGIN
687 INSERT INTO [parkingPlaces] (parkingId) VALUES (@i + 1)
688 SET @i = @i + 1
689END
690GO
691----------------------------------------------------------------------------------------------------
692CREATE PROCEDURE [reset]
693AS
694
695DELETE FROM [ads]
696DELETE FROM [marks]
697DELETE FROM [timetable]
698DELETE FROM [warnings]
699DELETE FROM [classesTeachers]
700GO
701----------------------------------------------------------------------------------------------------
702CREATE VIEW [loggedIn24]
703AS
704SELECT username, usernameAlias, accountType, lastLogin
705FROM users
706WHERE lastLogin >= DATEADD(day, -1, GETDATE())
707GO
708----------------------------------------------------------------------------------------------------
709CREATE FUNCTION [showUserSettings] (@userId INTEGER)
710RETURNS TABLE
711AS
712RETURN
713 SELECT U.userId, U.username, U.usernameAlias, U.accountType
714 FROM users U
715 WHERE U.userId = @userId
716GO
717----------------------------------------------------------------------------------------------------
718CREATE FUNCTION [showStudentMarks] (@studentId INTEGER, @semester TINYINT)
719RETURNS TABLE
720AS
721RETURN
722 SELECT S.name AS subjectName, ROUND(Sum(M.value * M.weight)/SUM(M.weight),2) AS average,
723 STUFF((SELECT ' ' + M.valueStr
724 FROM marks M
725 JOIN students ST ON ST.studentId = M.studentId
726 WHERE M.subjectId = S.subjectId AND ST.studentId = @studentId AND M.semester = @semester
727 FOR XML PATH('')),1,1,'') [marks]
728 FROM subjects S
729 JOIN marks M ON M.subjectId = S.subjectId
730 JOIN students St ON St.studentId = M.studentId WHERE St.studentId = @studentId AND M.semester = @semester
731 GROUP BY S.name, S.subjectId
732GO
733----------------------------------------------------------------------------------------------------
734CREATE FUNCTION [showTeacherMarks] (@subjectName VARCHAR(20), @className VARCHAR(2), @semester TINYINT)
735RETURNS TABLE
736AS
737RETURN
738 SELECT S.name + ' ' + S.surname AS student, S.studentId, Su.subjectId, Su.name AS subjectName, ROUND(SUM(M.value * M.weight)/SUM(M.weight),2) AS average,
739 STUFF((SELECT ' ' + M.valueStr
740 FROM marks M
741 JOIN subjects Su ON Su.subjectId = M.subjectId
742 WHERE S.studentId = M.studentId AND Su.name = @subjectName AND M.semester = @semester
743 FOR XML PATH('')),1,1,'') [marks]
744 FROM students S
745 JOIN marks M ON M.studentId = S.studentId
746 JOIN subjects Su ON Su.subjectId = M.subjectId
747 JOIN classes C ON C.classId = S.classId
748 WHERE Su.name = @subjectName AND C.name = @className AND M.semester = @semester
749 GROUP BY S.name, S.surname, S.studentId, Su.name, Su.subjectId
750GO
751----------------------------------------------------------------------------------------------------
752CREATE FUNCTION [showMarksFromSubject] (@studentId INTEGER, @subjectName VARCHAR(30), @semester TINYINT)
753RETURNS TABLE
754AS RETURN
755 SELECT M.valueStr, M.weight, M.markType, M.markId, FORMAT(M.addDate, 'dd.MM.yyyy HH:mm:ss') AS addDate, M.comment
756 FROM marks M
757 JOIN subjects S ON S.subjectId = M.subjectId
758 WHERE M.studentId = @studentId AND S.name = @subjectName AND M.semester = @semester
759GO
760----------------------------------------------------------------------------------------------------
761CREATE FUNCTION [showWarnings] (@studentId INTEGER)
762RETURNS TABLE
763AS
764RETURN
765 SELECT W.warningId, W.title, W.content, FORMAT(W.addDate, 'dd.MM.yyyy HH:mm:ss') AS addDate, E.name + ' ' + E.surname AS teacher
766 FROM warnings W
767 JOIN teachers T ON T.teacherId = W.teacherId
768 JOIN employees E ON E.employeeId = T.employeeId
769 WHERE W.studentId = @studentId
770GO
771----------------------------------------------------------------------------------------------------
772CREATE FUNCTION [showAds] (@target VARCHAR(20), @target2 VARCHAR(20))
773RETURNS TABLE
774AS
775RETURN
776 SELECT A.adId, A.title, A.content, FORMAT(A.addDate, 'dd.MM.yyyy HH:mm:ss') AS addDate, E.name + ' ' + E.surname AS teacher
777 FROM ads A
778 JOIN teachers T ON T.teacherId = A.teacherId
779 JOIN employees E ON E.employeeID = T.employeeId
780 WHERE A.target = @target OR A.target = @target2
781GO
782----------------------------------------------------------------------------------------------------
783CREATE FUNCTION [showStudentTimetable] (@studentId INTEGER, @dayString VARCHAR(12))
784RETURNS TABLE
785AS
786RETURN
787 SELECT SU.name AS subjectName, E.name + ' ' + E.surname AS teacher, T.classroom
788 FROM timetable T
789 JOIN subjects SU ON SU.subjectId = T.subjectId
790 JOIN teachers TE ON TE.teacherId = T.teacherId
791 JOIN employees E ON E.employeeId = TE.employeeId
792 JOIN students S ON S.classId = T.classId
793 WHERE S.studentId = @studentId AND T.dayString = @dayString
794GO
795----------------------------------------------------------------------------------------------------
796CREATE FUNCTION [showTeacherTimetable] (@teacherId INTEGER, @dayString VARCHAR(12))
797RETURNS TABLE
798AS
799RETURN
800 SELECT SU.name AS subjectName, C.name AS className, T.classroom
801 FROM timetable T
802 JOIN subjects SU ON SU.subjectId = T.subjectId
803 JOIN teachers TE ON TE.teacherId = T.teacherId
804 JOIN classes C ON C.classId = T.classId
805 WHERE TE.teacherId = @teacherId AND T.dayString = @dayString
806GO
807----------------------------------------------------------------------------------------------------
808CREATE FUNCTION [showTeacherClasses] (@teacherId INTEGER)
809RETURNS TABLE
810AS
811RETURN
812 SELECT C.name AS className, S.name AS subjectName
813 FROM classes C
814 JOIN classesTeachers CT ON C.classId = CT.classId
815 JOIN subjects S ON S.subjectId = CT.subjectId
816 WHERE CT.teacherId = @teacherId
817 GROUP BY C.name, S.name
818GO
819----------------------------------------------------------------------------------------------------
820CREATE FUNCTION [searchStudentBySurname] (@surname VARCHAR(30))
821RETURNS TABLE
822AS
823RETURN
824 SELECT DISTINCT S.*, SI.street, SI.houseNumber, SI.postCode, SI.city, SI.street + ' ' + SI.houseNumber + ' ' + SI.postCode + ' ' + ' ' + SI.city AS address2, SI.PES, FORMAT(SI.dateOfBirth, 'dd.MM.yyyy') AS dateOfBirth, SI.email, C.name AS className
825 FROM students S
826 JOIN studentInfo SI ON S.studentId = SI.studentId
827 JOIN classes C ON C.classId = S.classId
828 WHERE S.surname LIKE @surname
829GO
830----------------------------------------------------------------------------------------------------
831CREATE FUNCTION [searchStudentByCardNumber] (@cardNumber CHAR(10))
832RETURNS TABLE
833AS
834RETURN
835 SELECT DISTINCT S.*, SI.street, SI.houseNumber, SI.postCode, SI.city, SI.street + ' ' + SI.houseNumber + ' ' + SI.postCode + ' ' + ' ' + SI.city AS address2, SI.PES, FORMAT(SI.dateOfBirth, 'dd.MM.yyyy') AS dateOfBirth, SI.email, C.name AS className
836 FROM students S
837 JOIN studentInfo SI ON S.studentId = SI.studentId
838 JOIN classes C ON C.classId = S.classId
839 WHERE S.cardNumber LIKE @cardNumber
840GO
841----------------------------------------------------------------------------------------------------
842CREATE FUNCTION [searchStudentByClassName] (@className VARCHAR(2))
843RETURNS TABLE
844AS
845RETURN
846 SELECT DISTINCT S.*, SI.street, SI.houseNumber, SI.postCode, SI.city, SI.street + ' ' + SI.houseNumber + ' ' + SI.postCode + ' ' + ' ' + SI.city AS address2, SI.PES, FORMAT(SI.dateOfBirth, 'dd.MM.yyyy') AS dateOfBirth, SI.email, C.name AS className
847 FROM students S
848 JOIN studentInfo SI ON S.studentId = SI.studentId
849 JOIN classes C ON C.classId = S.classId
850 WHERE C.name = @className
851GO
852----------------------------------------------------------------------------------------------------
853CREATE FUNCTION [searchTeacherBySurname] (@surname VARCHAR(30))
854RETURNS TABLE
855AS
856RETURN
857 SELECT DISTINCT T.cardNumber, S.value AS salary, E.*, EI.street, EI.houseNumber, EI.postCode, EI.city, EI.street + ' ' + EI.houseNumber + ' ' + EI.postCode + ' ' + ' ' + EI.city AS address2,
858 EI.PES, FORMAT(EI.dateOfBirth, 'dd.MM.yyyy') AS dateOfBirth, EI.email
859 FROM employees E
860 JOIN employeeInfo EI ON EI.employeeId = E.employeeId
861 JOIN salaries S ON S.salaryId = EI.salaryId
862 JOIN teachers T ON T.employeeId = E.employeeId
863 WHERE E.surname LIKE @surname
864GO
865----------------------------------------------------------------------------------------------------
866CREATE FUNCTION [searchTeacherByCardNumber] (@cardNumber CHAR(10))
867RETURNS TABLE
868AS
869RETURN
870 SELECT DISTINCT T.cardNumber, S.value AS salary, E.*, EI.street, EI.houseNumber, EI.postCode, EI.city, EI.street + ' ' + EI.houseNumber + ' ' + EI.postCode + ' ' + ' ' + EI.city AS address2,
871 EI.PES, FORMAT(EI.dateOfBirth, 'dd.MM.yyyy') AS dateOfBirth, EI.email
872 FROM employees E
873 JOIN employeeInfo EI ON EI.employeeId = E.employeeId
874 JOIN salaries S ON S.salaryId = EI.salaryId
875 JOIN teachers T ON T.employeeId = E.employeeId
876 WHERE T.cardNumber = @cardNumber
877GO
878----------------------------------------------------------------------------------------------------
879CREATE FUNCTION [searchTeacherBySubjectName] (@subjectName VARCHAR(20))
880RETURNS TABLE
881AS
882RETURN
883 SELECT DISTINCT T.cardNumber, S.value AS salary, E.*, EI.street, EI.houseNumber, EI.postCode, EI.city, EI.street + ' ' + EI.houseNumber + ' ' + EI.postCode + ' ' + ' ' + EI.city AS address2,
884 EI.PES, FORMAT(EI.dateOfBirth, 'dd.MM.yyyy') AS dateOfBirth, EI.email
885 FROM employees E
886 JOIN employeeInfo EI ON EI.employeeId = E.employeeId
887 JOIN salaries S ON S.salaryId = EI.salaryId
888 JOIN teachers T ON T.employeeId = E.employeeId
889 JOIN subjectsTeachers ST ON ST.teacherId = T.teacherId
890 JOIN subjects Su ON Su.subjectId = ST.subjectId
891 WHERE SU.name = @subjectName
892GO
893----------------------------------------------------------------------------------------------------
894CREATE FUNCTION [studentsRanking] (@subjectName VARCHAR(30))
895RETURNS TABLE
896AS
897RETURN
898 SELECT ST.name, ST.surname, ROUND(Sum(M.value * M.weight)/SUM(M.weight),2) AS average, S.name AS subjectName
899 FROM marks M
900 JOIN students ST on ST.studentId = M.studentID
901 JOIN subjects S ON M.subjectId = S.subjectId
902 WHERE S.name = @subjectName
903 GROUP BY ST.name, ST.surname, S.name
904GO
905----------------------------------------------------------------------------------------------------
906CREATE TRIGGER [addUserStudent] ON [students] AFTER INSERT
907AS
908BEGIN
909 DECLARE @studentId INTEGER
910 DECLARE @login VARCHAR(20)
911 DECLARE @password CHAR(32)
912 DECLARE @decryptedPassword CHAR(15)
913
914 SELECT @decryptedPassword = CHAR(RAND()*26+65) + CHAR(RAND()*26+65) + CHAR(RAND()*26+65)
915 + CHAR(RAND()*26+65) + CHAR(RAND()*26+65) + CHAR(RAND()*26+65)
916 + CHAR(RAND()*26+65) + CHAR(RAND()*26+65) + CHAR(RAND()*26+65)
917 + CHAR(RAND()*26+65) + CHAR(RAND()*26+65) + CHAR(RAND()*26+65)
918 + CHAR(RAND()*26+65) + CHAR(RAND()*26+65) + CHAR(RAND()*26+65)
919 SELECT @password = LOWER(CONVERT(CHAR(32), HashBytes('MD5', @decryptedPassword), 2))
920 SELECT @studentId = studentId FROM inserted
921
922 INSERT INTO [users] (studentId, teacherId, accountType, password) VALUES (@studentId, NULL, 'Uczen', @password)
923END
924GO
925----------------------------------------------------------------------------------------------------
926CREATE TRIGGER [addUserTeacher] ON [teachers] AFTER INSERT
927AS
928BEGIN
929 DECLARE @teacherId INTEGER
930 DECLARE @login VARCHAR(20)
931 DECLARE @password CHAR(32)
932 DECLARE @decryptedPassword CHAR(15)
933
934 SELECT @decryptedPassword = CHAR(RAND()*26+65) + CHAR(RAND()*26+65) + CHAR(RAND()*26+65)
935 + CHAR(RAND()*26+65) + CHAR(RAND()*26+65) + CHAR(RAND()*26+65)
936 + CHAR(RAND()*26+65) + CHAR(RAND()*26+65) + CHAR(RAND()*26+65)
937 + CHAR(RAND()*26+65) + CHAR(RAND()*26+65) + CHAR(RAND()*26+65)
938 + CHAR(RAND()*26+65) + CHAR(RAND()*26+65) + CHAR(RAND()*26+65)
939 SELECT @password = LOWER(CONVERT(CHAR(32), HashBytes('MD5', @decryptedPassword), 2))
940 SELECT @teacherId = teacherId FROM inserted
941
942 INSERT INTO [users] (studentId, teacherId, accountType, password) VALUES (NULL, @teacherId, 'Nauczyciel', @password)
943END
944GO
945----------------------------------------------------------------------------------------------------
946CREATE TRIGGER [deleteStudent] ON [studentInfo] AFTER DELETE
947AS
948BEGIN
949 DELETE FROM [users] WHERE studentId IN (SELECT deleted.studentId FROM deleted)
950 DELETE FROM [marks] WHERE studentId IN (SELECT deleted.studentId FROM deleted)
951 DELETE FROM [warnings] WHERE studentId IN (SELECT deleted.studentId FROM deleted)
952 DELETE FROM [students] WHERE studentId IN (SELECT deleted.studentId FROM deleted)
953END
954GO
955----------------------------------------------------------------------------------------------------
956CREATE TRIGGER [assignParkingOnInsert] ON [teachers] AFTER INSERT
957AS
958BEGIN
959 DECLARE @teacherId INTEGER
960 DECLARE @i INTEGER = 1
961 DECLARE @amount INTEGER
962 SELECT @teacherId = teacherId FROM inserted
963 SELECT @amount = COUNT(*) FROM parkingPlaces
964 BEGIN TRANSACTION
965 WHILE @i < @amount
966 BEGIN
967 IF (SELECT COUNT(teacherId) FROM parkingPlaces WHERE parkingId = @i) = 0
968 BEGIN
969 BEGIN TRY
970 UPDATE [parkingPlaces] SET teacherId = @teacherId WHERE parkingId = @i
971 COMMIT
972 END TRY
973 BEGIN CATCH
974 RAISERROR('Nie ma dostepnego miejsca parkingowego.', 16, 1)
975 ROLLBACK
976 END CATCH
977 BREAK
978 END
979 SET @i = @i + 1
980 END
981END
982GO
983----------------------------------------------------------------------------------------------------
984CREATE TRIGGER [assignParkingOnDelete] ON [teachers] AFTER DELETE
985AS
986BEGIN
987 DECLARE @teacherId INTEGER
988 DECLARE @parkingId INTEGER
989 SELECT @teacherId = MIN(teacherId) FROM teachers WHERE NOT EXISTS (SELECT teacherId FROM parkingPlaces)
990 SELECT @parkingId = MIN(parkingId) FROM parkingPlaces WHERE teacherId IS NULL
991 UPDATE [parkingPlaces] SET teacherId = @teacherId WHERE parkingId = @parkingId
992END
993GO
994----------------------------------------------------------------------------------------------------
995CREATE TRIGGER [cannotDeleteParkingPlace] ON [parkingPlaces] INSTEAD OF DELETE
996AS
997BEGIN
998 RAISERROR('Nie mozna wyburzyc miejsca parkingowego', 16, 1)
999END
1000GO
1001----------------------------------------------------------------------------------------------------
1002SET IDENTITY_INSERT [parkingPlaces] ON;
1003EXECUTE fillParking @amount = 10;
1004SET IDENTITY_INSERT [parkingPlaces] OFF;
1005----------------------------------------------------------------------------------------------------
1006INSERT INTO [salaries] VALUES (2000), (2300), (2500), (2700), (3000), (3200), (3500), (3700), (4000), (4200), (4500);
1007----------------------------------------------------------------------------------------------------
1008INSERT INTO [classes] VALUES ('1a'), ('1b'), ('1c'), ('2a'), ('2b'), ('2c'), ('3a'), ('3b'), ('3c');
1009----------------------------------------------------------------------------------------------------
1010INSERT INTO [subjects] VALUES ('Matematyka'), ('Jezyk polski'), ('Fizyka'), ('Historia'), ('Jezyk angielski'), ('Wychowanie fizyczne'), ('Jezyk niemiecki'), ('Jezyk francuski'),
1011('Biologia'), ('Chemia'), ('Geografia'), ('Informatyka'), ('Wiedza o spoleczenstwie'), ('Podstawy przedsiebiorczosci'), ('Przyroda');
1012----------------------------------------------------------------------------------------------------
1013EXECUTE addTeacher @name = 'Jerzy', @surname = 'Middle', @salary = 4000, @cardNumber = 'ASDFG12345', @PES = '73092734713', @dateOfBirth = '27/09/1973', @street = 'Matematyczna', @houseNumber = '3/14',
1014@postCode = '87-321', @city = 'Macierzowo', @email = 'jerzymiddle@email.pl';
1015EXECUTE addTeacher @name = 'Paweł', @surname = 'Pablito', @salary = 4000, @cardNumber = 'SZXCV87642', @PES = '70021234216', @dateOfBirth = '12/02/1970', @street = 'Piłsudskiego', @houseNumber = '21',
1016@postCode = '21-987', @city = 'Wrocław', @email = 'pawelpablito@email.pl';
1017EXECUTE addTeacher @name = 'Ka', @surname = 'Åukasz', @salary = 2000, @cardNumber = 'POIUY12345', @PES = '77061007615', @dateOfBirth = '10/06/1977', @street = 'Warzywna', @houseNumber = '15',
1018@postCode = '24-213', @city = 'Gdańsk', @email = 'kalukasze@email.pl';
1019EXECUTE addTeacher @name = 'Andrzej', @surname = 'Szydłowski', @salary = 2500, @cardNumber = 'GHSDD12312', @PES = '58101141317', @dateOfBirth = '11/10/1958', @street = 'Spacerowa', @houseNumber = '98',
1020@postCode = '85-230', @city = 'Kartuzy', @email = 'andrzejszydlowski@email.pl';
1021EXECUTE addTeacher @name = 'Henryk', @surname = 'Grzejnik', @salary = 3000, @cardNumber = 'KJNMX12111', @PES = '80071797513', @dateOfBirth = '17/07/1980', @street = 'Szekspira', @houseNumber = '76/12',
1022@postCode = '15-250', @city = 'Gdynia', @email = 'henrykgrzejnik@email.pl';
1023----------------------------------------------------------------------------------------------------
1024INSERT INTO [subjectsTeachers] VALUES (1,1), (1,2), (2,1), (2,2), (3,3), (4,4), (4,5);
1025----------------------------------------------------------------------------------------------------
1026INSERT INTO [classesTeachers] VALUES (1,1,1), (1,1,2), (9,1,2);
1027----------------------------------------------------------------------------------------------------
1028EXECUTE addStudent @name = 'Piter', @surname = 'Rootkowski', @PES = '94053078901', @cardNumber = 'XLDOJLD765', @dateOfBirth = '30/05/1994', @street = 'Inzynierska',
1029@houseNumber = '5', @postCode = '82-451', @city = 'Olszyn', @email = 'piterrot@email.pl', @className = '1a';
1030EXECUTE addStudent @name = 'Marcin', @surname = 'Ruszniekiewiecz', @PES = '97031778901', @cardNumber = 'XLDOJLD321', @dateOfBirth = '17/03/1997', @street = 'Javowska',
1031@houseNumber = '23', @postCode = '53-211', @city = 'Szusz', @email = 'marcinekrusz@email.pl', @className = '1a';
1032EXECUTE addStudent @name = 'Bartosz', @surname = 'Wisnia', @PES = '94100790312', @cardNumber = 'XLDEJLD765', @dateOfBirth = '07/10/1994', @street = 'Pizzowska',
1033@houseNumber = '21', @postCode = '72-321', @city = 'Nowe Miasto', @email = 'wisnia@email.pl', @className = '1a';
1034EXECUTE addStudent @name = 'Mati', @surname = 'Brezowski', @PES = '94060686311', @cardNumber = 'ALDOJLD765', @dateOfBirth = '06/06/1994', @street = 'Spacerowa',
1035@houseNumber = '10', @postCode = '83-340', @city = 'Sierakowice', @email = 'breza@email.pl', @className = '1a';
1036EXECUTE addStudent @name = 'Roman', @surname = 'Wieżba', @PES = '00261001514', @cardNumber = 'XLDOJLD997', @dateOfBirth = '10/06/2000', @street = 'Osiemnasta',
1037@houseNumber = '18', @postCode = '18-181', @city = 'Gdansk', @email = 'wiezba@email.pl', @className = '2b';
1038EXECUTE addStudent @name = 'Kajeton', @surname = 'Paszczon', @PES = '96091879113', @cardNumber = 'XLDOJLP722', @dateOfBirth = '18/09/1996', @street = 'Kozacka',
1039@houseNumber = '2', @postCode = '85-320', @city = 'Gdynia', @email = 'paszczon@email.pl', @className = '2b';
1040EXECUTE addStudent @name = 'Jakub', @surname = 'Silded', @PES = '97073052810', @cardNumber = 'XLDOJLP999', @dateOfBirth = '30/07/1997', @street = 'Spacerowa',
1041@houseNumber = '4', @postCode = '83-340', @city = 'Sierakowice', @email = 'silded@email.pl', @className = '3c';
1042EXECUTE addStudent @name = 'Marjusz', @surname = 'Żepa', @PES = '97022091718', @cardNumber = 'XLDOJXD222', @dateOfBirth = '20/02/1997', @street = 'Wojska Polskiego',
1043@houseNumber = '34', @postCode = '83-340', @city = 'Sierakowice', @email = 'zepa@email.pl', @className = '3c';
1044----------------------------------------------------------------------------------------------------
1045DELETE FROM [users] WHERE studentId = 1;
1046DELETE FROM [users] WHERE teacherId = 1;
1047INSERT INTO [users] (studentId, teacherId, accountType, usernameAlias, password) VALUES (1, NULL, 'Uczen', 'uczentest', '912ec803b2ce49e4a541068d495ab570');
1048INSERT INTO [users] (studentId, teacherId, accountType, usernameAlias, password) VALUES (NULL, 1, 'Nauczyciel', 'nauczycieltest', 'd16fb36f0911f878998c136191af705e');
1049----------------------------------------------------------------------------------------------------
1050INSERT INTO [warnings] (studentId, teacherId, title, content) VALUES (1, 1, 'JD', 'Nieprawilne zachowania podczas lekcji');
1051INSERT INTO [warnings] (studentId, teacherId, title, content) VALUES (1, 1, 'XD', 'Nie da sie wygrac w lige jak cos');
1052----------------------------------------------------------------------------------------------------
1053INSERT INTO [ads] (teacherId, target, title, content) VALUES (1, 'Uczniowie', 'U', 'XDDsadasdasdasd')
1054INSERT INTO [ads] (teacherId, target, title, content) VALUES (2, 'Nauczyciele', 'N', 'XDDsadasdasdasd')
1055INSERT INTO [ads] (teacherId, target, title, content) VALUES (3, 'Wszyscy', 'W', 'XDDsadasdasdasd')
1056----------------------------------------------------------------------------------------------------
1057EXECUTE addMark @studentId = 1, @teacherId = 1, @subjectId = 1, @valueStr = '4', @markType = 'Sprawdzian', @comment = 'XD', @semester = 1;
1058EXECUTE addMark @studentId = 1, @teacherId = 1, @subjectId = 1, @valueStr = '3+', @markType = 'Aktywność', @comment = 'XD', @semester = 1;
1059EXECUTE addMark @studentId = 3, @teacherId = 1, @subjectId = 1, @valueStr = '4', @markType = 'Sprawdzian', @comment = 'XD', @semester = 1;
1060EXECUTE addMark @studentId = 4, @teacherId = 1, @subjectId = 1, @valueStr = '3+', @markType = 'Aktywność', @comment = 'XD', @semester = 1;
1061EXECUTE addMark @studentId = 1, @teacherId = 1, @subjectId = 2, @valueStr = '4-', @markType = 'Zadanie domowe', @comment = 'XD', @semester = 1;
1062EXECUTE addMark @studentId = 3, @teacherId = 5, @subjectId = 3, @valueStr = '4+', @markType = 'Sprawdzian', @comment = 'XD', @semester = 1;
1063EXECUTE addMark @studentId = 3, @teacherId = 4, @subjectId = 4, @valueStr = '2-', @markType = 'Aktywność', @comment = 'XD', @semester = 1;
1064EXECUTE addMark @studentId = 4, @teacherId = 5, @subjectId = 1, @valueStr = '3', @markType = 'Kartkówka', @comment = 'XD', @semester = 1;
1065EXECUTE addMark @studentId = 4, @teacherId = 4, @subjectId = 4, @valueStr = '6', @markType = 'Sprawdzian', @comment = 'XD', @semester = 1;
1066EXECUTE addMark @studentId = 5, @teacherId = 1, @subjectId = 1, @valueStr = '5-', @markType = 'Zadanie domowe', @comment = 'XD', @semester = 1;
1067EXECUTE addMark @studentId = 5, @teacherId = 4, @subjectId = 4, @valueStr = '5', @markType = 'Sprawdzian', @comment = 'XD', @semester = 1;
1068EXECUTE addMark @studentId = 6, @teacherId = 4, @subjectId = 4, @valueStr = '3+', @markType = 'Aktywność', @comment = 'XD', @semester = 1;
1069EXECUTE addMark @studentId = 6, @teacherId = 1, @subjectId = 1, @valueStr = '1', @markType = 'Projekt', @comment = 'XD', @semester = 2;
1070EXECUTE addMark @studentId = 1, @teacherId = 1, @subjectId = 1, @valueStr = '2', @markType = 'Sprawdzian', @comment = 'XD', @semester = 2;
1071EXECUTE addMark @studentId = 1, @teacherId = 1, @subjectId = 1, @valueStr = '3', @markType = 'Aktywność', @comment = 'XD', @semester = 2;
1072EXECUTE addMark @studentId = 3, @teacherId = 1, @subjectId = 1, @valueStr = '4', @markType = 'Sprawdzian', @comment = 'XD', @semester = 2;
1073EXECUTE addMark @studentId = 4, @teacherId = 1, @subjectId = 1, @valueStr = '3+', @markType = 'Aktywność', @comment = 'XD', @semester = 2;
1074EXECUTE addMark @studentId = 1, @teacherId = 1, @subjectId = 2, @valueStr = '4-', @markType = 'Zadanie domowe', @comment = 'XD', @semester = 2;
1075EXECUTE addMark @studentId = 3, @teacherId = 5, @subjectId = 3, @valueStr = '4+', @markType = 'Sprawdzian', @comment = 'XD', @semester = 2;
1076EXECUTE addMark @studentId = 3, @teacherId = 4, @subjectId = 4, @valueStr = '2-', @markType = 'Aktywność', @comment = 'XD', @semester = 2;
1077EXECUTE addMark @studentId = 4, @teacherId = 5, @subjectId = 1, @valueStr = '3', @markType = 'Kartkówka', @comment = 'XD', @semester = 2;
1078EXECUTE addMark @studentId = 4, @teacherId = 4, @subjectId = 4, @valueStr = '6', @markType = 'Sprawdzian', @comment = 'XD', @semester = 2;
1079EXECUTE addMark @studentId = 5, @teacherId = 1, @subjectId = 1, @valueStr = '5-', @markType = 'Zadanie domowe', @comment = 'XD', @semester = 2;
1080EXECUTE addMark @studentId = 5, @teacherId = 4, @subjectId = 4, @valueStr = '5', @markType = 'Sprawdzian', @comment = 'XD', @semester = 2;
1081EXECUTE addMark @studentId = 6, @teacherId = 4, @subjectId = 4, @valueStr = '3+', @markType = 'Aktywność', @comment = 'XD', @semester = 2;
1082EXECUTE addMark @studentId = 6, @teacherId = 1, @subjectId = 1, @valueStr = '4', @markType = 'Projekt', @comment = 'XD', @semester = 2;
1083----------------------------------------------------------------------------------------------------
1084INSERT INTO [timetable] VALUES (1, 1, 1, 1, 'Poniedziałek', 'Sala 112'),
1085(1, 1, 1, 2, 'Poniedziałek', 'Sala 112'), (3, 3, 1, 3, 'Poniedziałek', 'Sala 231'),
1086(1, 1, 1, 1, 'Poniedziałek', 'Sala 112'), (1, 1, 1, 2, 'Wtorek', 'Sala 112'),
1087(3, 3, 1, 3, 'Wtorek', 'Sala 231'), (1, 1, 1, 4, 'Wtorek', 'Sala 112'),
1088(3, 3, 1, 5, 'Wtorek', 'Sala 231'), (1, 1, 1, 6, 'Wtorek', 'Sala 112'),
1089(1, 1, 1, 1, 'Sroda', 'Sala 112'), (1, 1, 1, 2, 'Sroda', 'Sala 112'),
1090(3, 3, 1, 3, 'Sroda', 'Sala 231'), (1, 1, 1, 1, 'Czwartek', 'Sala 112'),
1091(3, 3, 1, 2, 'Czwartek', 'Sala 231'), (1, 1, 1, 3, 'Czwartek', 'Sala 112'),
1092(3, 3, 1, 4, 'Czwartek', 'Sala 231'), (1, 1, 1, 1, 'Piatek', 'Sala 112'),
1093(3, 3, 1, 2, 'Piatek', 'Sala 231'), (1, 1, 1, 3, 'Piatek', 'Sala 112');
1094----------------------------------------------------------------------------------------------------
1095
1096----------------------------------------------------------------------------------------------------