· 9 years ago · Nov 24, 2016, 09:34 AM
1--last edit: Nov24 11:30 Mono
2
3
4--fee broblem gat 3ala baly
5--el many to many relations elle mafeehash zero me7tageen constraint sa5eef
6
7/*
8CREATE DATABASE ms3g4b
9GO
10
11USE ms3g4b
12GO
13*/
14--USE DIFFERENT VARIABLE NAMES FOR THE RETURN VARIABLES!!
15--wa kad 2a3zar man 2anzar X')
16
17CREATE FUNCTION isElementary(@x VARCHAR(50)) RETURNS BIT
18AS BEGIN
19 DECLARE @r BIT
20 SELECT @r = is_elementary FROM Schools WHERE @x = email
21 RETURN @r
22END
23GO
24
25CREATE FUNCTION hasChildInSchool(@parentEmail VARCHAR(50), @schoolEmail VARCHAR(50)) RETURNS BIT
26AS BEGIN
27 DECLARE @r2 BIT
28 IF( EXISTS (
29 SELECT *
30 FROM Enrolled_Students es
31 WHERE es.school_email = @schoolEmail AND es.parent_email = @parentEmail
32 ))
33 SET @r2 = '1'
34 ELSE
35 SET @r2 = '0'
36 RETURN @r2
37END
38GO
39
40CREATE FUNCTION teacherTeachesStudent (@parentEmail VARCHAR(50), @teacherEmail VARCHAR(50)) RETURNS BIT
41AS BEGIN
42 DECLARE @r3 BIT
43 IF( EXISTS(
44 SELECT *
45 FROM Enrolled_Students es, Schools s, Employees e
46 WHERE @parentEmail = es.parent_email AND es.school_email = s.email AND s.email = e.school_email AND e.email = @teacherEmail AND e.is_teacher = '1'
47 ))
48 SET @r3 = '1'
49 ELSE
50 SET @r3 = '0'
51 RETURN @r3
52END
53GO
54
55CREATE TABLE Schools
56(
57 email VARCHAR(50) PRIMARY KEY,
58 vision VARCHAR(300),
59 main_language VARCHAR (30) ,
60 mission VARCHAR(300),
61 is_national BIT DEFAULT '0',
62 is_international BIT DEFAULT '0',
63 phone_number VARCHAR(20) UNIQUE,
64 home_page VARCHAR(100),
65 fees FLOAT ,
66 school_address VARCHAR(100) ,
67 general_information VARCHAR(300),
68 name VARCHAR (50),
69 is_elementary BIT NOT NULL DEFAULT '0',
70 is_middle BIT NOT NULL DEFAULT '0',
71 is_high BIT NOT NULL DEFAULT '0',
72 CONSTRAINT school_type CHECK (is_national<>0 OR is_international<>0),
73 CONSTRAINT school_grade CHECK (is_elementary<>0 OR is_middle<>0 OR is_high<>0)
74);
75CREATE TABLE Elementary_Supplies
76(
77 supply VARCHAR(50),
78 email VARCHAR(50),
79 PRIMARY KEY(supply,email),
80 FOREIGN KEY (email) REFERENCES Schools,
81 CHECK (dbo.isElementary(email) = '1')
82);
83CREATE TABLE Parents
84(
85 email VARCHAR(50) PRIMARY KEY,
86 parent_address VARCHAR(100),
87 home_phone VARCHAR(20),
88 first_name VARCHAR(25),
89 last_name VARCHAR(25),
90 parent_user_name VARCHAR(25) UNIQUE,
91 parent_password VARCHAR(25)
92);
93CREATE TABLE Parents_Mobiles
94(
95 mobile VARCHAR(20) PRIMARY KEY,
96 email VARCHAR(50),
97 FOREIGN KEY (email) REFERENCES Parents
98);
99CREATE TABLE Parents_Write_Review_Schools
100(
101 parent_email VARCHAR(50),
102 school_email VARCHAR(50),
103 reviews VARCHAR(200),
104 PRIMARY KEY(parent_email, school_email),
105 FOREIGN KEY(parent_email) REFERENCES Parents(email),
106 FOREIGN KEY(school_email) REFERENCES Schools(email),
107 CHECK(dbo.hasChildInSchool(parent_email, school_email) = '1')
108);
109CREATE TABLE Applicants
110(
111 ssn INT PRIMARY KEY,
112 name VARCHAR(50),
113 gender BIT,
114 birth_date DATE,
115 age AS (year(current_timestamp) - year(birth_date)),
116 parent_email VARCHAR(50),
117 FOREIGN KEY (parent_email) REFERENCES Parents
118);
119CREATE TABLE Applicants_Applies_Schools
120(
121 school_email VARCHAR(50),
122 child_ssn INT,
123 accepted BIT DEFAULT '0',
124 parent_email VARCHAR(50),
125 PRIMARY KEY(child_ssn, school_email),
126 FOREIGN KEY(child_ssn) REFERENCES Applicants,
127 FOREIGN KEY(parent_email) REFERENCES Parents,
128 FOREIGN KEY(school_email) REFERENCES Schools
129);
130CREATE TABLE Enrolled_Students
131(
132 ssn INT PRIMARY KEY, --IDENTITY(1,1),mayenfa3sh y'increment. da "social security number, zayy rakam el beta2a :c"
133 student_level INT,
134 name VARCHAR(50),
135 gender BIT,
136 birth_date DATE,
137 age AS (year(current_timestamp) - year(birth_date)),
138 student_password VARCHAR(20),
139 student_user_name VARCHAR(20) UNIQUE,
140 school_email VARCHAR(50),
141 parent_email VARCHAR(50),
142 FOREIGN KEY (school_email) REFERENCES Schools,
143 FOREIGN KEY (parent_email) REFERENCES Parents
144);
145CREATE TABLE Courses
146(
147 code VARCHAR(10) PRIMARY KEY,
148 name VARCHAR(50), --3ash :'3
149 level_of_school INT,
150 course_description VARCHAR(100)
151);
152CREATE TABLE Courses_Prequisites
153(
154 course_code_1 VARCHAR(10),
155 course_code_2 VARCHAR(10),
156 PRIMARY KEY (course_code_1,course_code_2),
157 FOREIGN KEY (course_code_1) REFERENCES Courses,
158 FOREIGN KEY (course_code_2) REFERENCES Courses
159);
160
161
162--di masalan mafeehash zeros. ne3melha ezzay dih :c
163--yemken ne3mel el constraint 3ala el insertion procedure?
164--2ayy
165CREATE TABLE Courses_Offered_By_Schools
166(
167 course_code VARCHAR(10),
168 school_email VARCHAR(50),
169 PRIMARY KEY (course_code, school_email),
170 FOREIGN KEY (course_code) REFERENCES Courses,
171 FOREIGN KEY (school_email) REFERENCES Schools
172);
173
174
175CREATE TABLE Employees
176(
177 email VARCHAR(50) PRIMARY KEY,
178 birth_date DATE,
179 employee_user_name VARCHAR(25) UNIQUE,
180 employee_password VARCHAR(25),
181 first_name VARCHAR(25),
182 middle_name VARCHAR(25),
183 last_name VARCHAR(25),
184 gender BIT,
185 employee_address VARCHAR(100),
186 salary FLOAT,
187 enrollment_year DATE,
188 years_of_experience as (year(current_timestamp) - year(enrollment_year)),
189 is_administrator BIT NOT NULL DEFAULT '0',
190 is_teacher BIT NOT NULL DEFAULT '0',
191 is_supervisor BIT NOT NULL DEFAULT '0',
192 school_email VARCHAR(50),
193 supervisor_email VARCHAR(50),
194 FOREIGN KEY (school_email) REFERENCES Schools,
195 FOREIGN KEY (supervisor_email) REFERENCES Employees
196 CHECK TeacherOnlyHasEnrollmentYear (is_teacher = '0' OR enrollment_year IS NULL)
197);
198CREATE TABLE Activities
199(
200 activity_date DATE,
201 activity_location VARCHAR(30),
202 activity_type VARCHAR(20),
203 activity_description VARCHAR(300),
204 administrator_email VARCHAR(50) NOT NULL,
205 teacher_email VARCHAR(50),
206 school_email VARCHAR(50),
207 PRIMARY KEY (activity_date, activity_location),
208 FOREIGN KEY (administrator_email) REFERENCES Employees,
209 FOREIGN KEY (teacher_email) REFERENCES Employees,
210 FOREIGN KEY (school_email) REFERENCES Schools
211);
212CREATE TABLE Equipment
213(
214 activity_date DATE,
215 activity_location VARCHAR(30),
216 equipment VARCHAR(50),
217 PRIMARY KEY (activity_date, activity_location, equipment),
218 FOREIGN KEY (activity_date, activity_location) REFERENCES Activities
219);
220CREATE TABLE Activities_Applied_By_Enrolled_Students
221(
222 activity_date DATE,
223 activity_location VARCHAR(30),
224 ssn INT,
225 PRIMARY KEY (ssn, activity_date, activity_location),
226 FOREIGN KEY (ssn) REFERENCES Enrolled_Students,
227 FOREIGN KEY (activity_date, activity_location) REFERENCES Activities
228);
229CREATE TABLE Clubs
230(
231 name VARCHAR(50) PRIMARY KEY,
232 purpose VARCHAR(50)
233);
234CREATE TABLE Clubs_Joined_By_Enrolled_Student
235(
236 club_name VARCHAR(50),
237 enrolled_ssn INT,
238 PRIMARY KEY (club_name, enrolled_ssn),
239 FOREIGN KEY (club_name) REFERENCES Clubs,
240 FOREIGN KEY (enrolled_ssn) REFERENCES Enrolled_Students
241);
242CREATE TABLE Clubs_Offered_By_Schools --eh offers da :P
243(
244 name VARCHAR(50),
245 school_email VARCHAR(50),
246 PRIMARY KEY(name,school_email),
247 FOREIGN KEY (name) REFERENCES Clubs,
248 FOREIGN KEY (school_email) REFERENCES Schools
249);
250CREATE TABLE Questions
251(
252 question_number INT IDENTITY,
253 course_code VARCHAR(10) NOT NULL,
254 question_itself VARCHAR(500), --3ash X')
255 question_answer VARCHAR(500),
256 askers_ssn INT,
257 PRIMARY KEY (question_number,course_code),
258 FOREIGN KEY (course_code) REFERENCES Courses,
259 FOREIGN KEY (askers_ssn) REFERENCES Enrolled_Students
260);
261CREATE TABLE Enrolled_Students_View_Questions
262(
263 ssn INT,
264 question_number INT,
265 course_code VARCHAR(10),
266 PRIMARY KEY (ssn, question_number, course_code),
267 FOREIGN KEY (ssn) REFERENCES Enrolled_Students,
268 FOREIGN KEY (question_number,course_code) REFERENCES Questions
269);
270CREATE TABLE Teachers_Response_To_Questions
271(
272 question_number INT,
273 course_code VARCHAR(10),
274 teacher_email VARCHAR(50),
275 PRIMARY KEY (question_number, course_code, teacher_email),
276 FOREIGN KEY (question_number,course_code) REFERENCES Questions,
277 FOREIGN KEY (teacher_email) REFERENCES Employees
278);
279CREATE TABLE Assignments
280(
281 assignment_number INT,
282 course_code VARCHAR(10),
283 teacher_email VARCHAR(50),
284 posting_date DATE,
285 due_date DATE,
286 content VARCHAR(500),
287 PRIMARY KEY (assignment_number, course_code, teacher_email),
288 FOREIGN KEY (course_code) REFERENCES Courses,
289 FOREIGN KEY (teacher_email) REFERENCES Employees
290);
291CREATE TABLE Assignments_Solved_By_Students
292(
293 ssn INT,
294 assignment_number INT,
295 teacher_email VARCHAR(50),
296 course_code VARCHAR(10),
297 grade FLOAT,
298 solution VARCHAR(3000),
299 PRIMARY KEY (ssn, assignment_number, teacher_email, course_code),
300 FOREIGN KEY (ssn) REFERENCES Enrolled_Students,
301 FOREIGN KEY (assignment_number, course_code, teacher_email) REFERENCES Assignments
302);
303
304
305--fee (one or)many side
306--7add yetsarraf
307CREATE TABLE View_And_Grade_Assignments
308(
309 viewer_email VARCHAR(50), --teacher_email ya3ny
310 assignment_number INT,
311 course_code VARCHAR(10),
312 creator_email VARCHAR(50),
313 PRIMARY KEY(viewer_email, assignment_number, course_code, creator_email),
314 FOREIGN KEY (assignment_number, course_code, creator_email) REFERENCES Assignments,
315 FOREIGN KEY (viewer_email) REFERENCES Employees
316);
317CREATE TABLE Attends
318(
319 enrolled_ssn INT,
320 course_code VARCHAR(10),
321 grade FLOAT,
322 PRIMARY KEY (enrolled_ssn, course_code),
323 FOREIGN KEY (enrolled_ssn) REFERENCES Enrolled_Students,
324 FOREIGN KEY (course_code) REFERENCES Courses
325);
326
327--fee (one or)many side
328CREATE TABLE Teaches
329(
330 teacher_email VARCHAR(50),
331 course_code VARCHAR(10),
332 PRIMARY KEY (course_code, teacher_email),
333 FOREIGN KEY (teacher_email) REFERENCES Employees,
334 FOREIGN KEY (course_code) REFERENCES Courses
335);
336CREATE TABLE Announcements
337(
338 administrator_email VARCHAR(50),
339 announcement_date DATE,
340 announcement_time TIME,
341 announcement_type VARCHAR(20),
342 title VARCHAR(25),
343 announcement_description VARCHAR(300),
344 school_email VARCHAR(50) NOT NULL,
345 PRIMARY KEY (administrator_email, announcement_date, announcement_time),
346 FOREIGN KEY (administrator_email) REFERENCES Employees,
347 FOREIGN KEY (school_email) REFERENCES Schools
348);
349CREATE TABLE Announcements_Visible_To_Parents
350(
351 administrator_email VARCHAR(50),
352 announcement_date DATE,
353 announcement_time TIME,
354 parent_email VARCHAR(50),
355 PRIMARY KEY (administrator_email,announcement_date,announcement_time,parent_email),
356 FOREIGN KEY (parent_email) REFERENCES Parents,
357 FOREIGN KEY (administrator_email, announcement_date, announcement_time) REFERENCES Announcements
358);
359CREATE TABLE Announcements_Visible_To_Enrolled_Students
360(
361 administrator_email VARCHAR(50),
362 announcement_date DATE,
363 announcement_time TIME,
364 enrolled_ssn int,
365 PRIMARY KEY (administrator_email,announcement_date,announcement_time),
366 FOREIGN KEY (enrolled_ssn) REFERENCES Enrolled_Students,
367 FOREIGN KEY (administrator_email, announcement_date, announcement_time) REFERENCES Announcements
368);
369CREATE TABLE Parents_Ratings_To_Teachers
370(
371 employee_email VARCHAR(50),
372 parent_email VARCHAR(50),
373 rating DECIMAL(2,1), --eh da? X) leh decimal tab? mesh int? madam zero to five
374 CONSTRAINT chk_Rating CHECK (rating >= 0 AND rating <= 5),
375 PRIMARY KEY (employee_email, parent_email),
376 FOREIGN KEY (employee_email) REFERENCES Employees,
377 FOREIGN KEY (parent_email) REFERENCES Parents,
378 CHECK(dbo.teacherTeachesStudent(parent_email, employee_email) = '1')
379);
380CREATE TABLE Monthly_Reports
381(
382 enrolled_ssn INT,
383 issue_date DATE,
384 comment VARCHAR(200),
385 writer VARCHAR(50) NOT NULL,
386 PRIMARY KEY (enrolled_ssn, issue_date),
387 FOREIGN KEY (enrolled_ssn) REFERENCES Enrolled_Students,
388 FOREIGN KEY (writer) REFERENCES Employees
389);
390
391--bardo (one or)many side!
392CREATE TABLE Monthly_Reports_Replies
393(
394 enrolled_ssn INT,
395 issue_date DATE,
396 parent_email VARCHAR (50),
397 reply VARCHAR(200),
398 PRIMARY KEY (enrolled_ssn, issue_date, parent_email),
399 FOREIGN KEY (enrolled_ssn, issue_date) REFERENCES Monthly_Reports,
400 FOREIGN KEY (parent_email) REFERENCES Parents
401);
402GO
403----------------------------------------------------------
404--sysadmin
405CREATE PROC CreateSchool
406@school_name VARCHAR(50),
407@address VARCHAR(100),
408@phone_number VARCHAR(20),
409@email VARCHAR(50),
410@general_information VARCHAR(300),
411@vision VARCHAR(300),
412@mission VARCHAR(300),
413@main_language VARCHAR (30),
414@type bit,
415@fees FLOAT
416AS
417 DECLARE @isNational bit SET @isNational = 0;
418 DECLARE @isInternational bit SET @isInternational = 0;
419 IF @type = 1
420 SET @isInternational = 1;
421 ELSE
422 SET @isNational = 1
423 IF @school_name is NULL or
424 @address is NULL or
425 @phone_number is NULL or
426 @type is NULL or
427 @fees is NULL
428 print 'One of the inputs is null'
429 else --wat za
430 INSERT INTO Schools(email,
431 vision,
432 main_language,
433 mission,
434 is_national,
435 is_international,
436 phone_number,
437 fees,
438 school_address,
439 general_information,
440 name)
441 VALUES(@email, @vision, @main_language, @mission, @isNational, @isInternational, @phone_number, @fees, @address, @general_information, @school_name)
442GO
443--di 7aga fadla m'el functions?
444/*CREATE TYPE Course_List
445AS TABLE
446(
447 course_code VARCHAR(10)
448);
449GO*/
450CREATE PROC AddCourse
451@course_code VARCHAR(10),
452@course_name VARCHAR(50),
453@course_level_elementary bit,
454@course_level_middle bit,
455@course_level_high bit,
456@grade int,
457@description VARCHAR(100),
458@prequisite_courses AS Course_List READONLY
459AS
460 IF @course_code is NULL or
461 @course_name is NULL or
462 @course_level_elementary is NULL or
463 @course_level_middle is NULL or
464 @course_level_high is NULL or
465 @grade is NULL
466 print 'One of the inputs is null'
467 else
468 INSERT INTO Courses(code, level_of_school, course_description, name)
469 Values(@course_code, @grade, @description, @course_name)
470
471 DECLARE @code INT
472 DECLARE MY_CURSOR CURSOR
473 LOCAL STATIC READ_ONLY FORWARD_ONLY
474 FOR
475 SELECT DISTINCT course_code
476 FROM @prequisite_courses
477 OPEN MY_CURSOR
478 FETCH NEXT FROM MY_CURSOR INTO @code
479 WHILE @@FETCH_STATUS = 0
480 BEGIN
481 if EXISTS (Select code from Courses where code = @code)
482 INSERT INTO Courses_Prequisites(course_code_1, course_code_2)
483 Values(@course_code, @code)
484 else
485 print 'Prequisite course does not exist'
486 FETCH NEXT FROM MY_CURSOR INTO @code
487 END
488 CLOSE MY_CURSOR
489 DEALLOCATE MY_CURSOR
490GO
491CREATE PROC AddAdmin
492 @first_name VARCHAR(25),
493 @middle_name VARCHAR(25),
494 @last_name VARCHAR(25),
495 @birthdate DATE,
496 @address VARCHAR(100),
497 @email VARCHAR(50),
498 @username VARCHAR(25),
499 @password VARCHAR(25),
500 @gender BIT
501AS
502 if @first_name IS NULL OR
503 @middle_name IS NULL OR
504 @last_name IS NULL OR
505 @birthdate IS NULL OR
506 @address IS NULL OR
507 @email IS NULL OR
508 @username IS NULL OR
509 @password IS NULL OR
510 @gender IS NULL
511 print 'One of the inputs is null'
512 else
513 declare @date as DATE set @date = CAST(GETDATE() AS DATE);
514 INSERT INTO Employees(email , employee_user_name , employee_password , first_name , middle_name , last_name , gender , employee_address , enrollment_year , birth_date , is_administrator , is_teacher , is_supervisor)
515 Values(@email, @username, @password, @first_name, @middle_name, @last_name, @gender, @address, @date, @birthdate, 1, 0, 0)
516GO
517CREATE PROC AssignAdminToSchool
518 @admin_email VARCHAR(50),
519 @school_email VARCHAR(50)
520AS
521 declare @school_type bit
522 IF @admin_email IS NULL OR
523 @school_email IS NULL
524 print 'One of the inputs is null'
525 ELSE
526 IF EXISTS (SELECT * FROM Schools s WHERE s.email = @school_email) AND Exists (SELECT * FROM Employees WHERE email = @admin_email)
527 begin
528 declare @salary int
529 SELECT @school_type = s.is_international FROM Schools s WHERE s.email = @school_email
530 IF @school_type = 1 --international
531 UPDATE Employees
532 SET school_email = @school_email, salary = 5000
533 WHERE email = @admin_email;
534 ELSE --national
535 UPDATE Employees
536 SET school_email = @school_email, salary = 3000
537 WHERE email = @admin_email;
538 end
539 ELSE
540 print 'School or Admin Do Not Exist'
541GO
542CREATE PROC DeleteSchool
543@school_email VARCHAR(50)
544AS
545 IF
546 @school_email IS NULL
547 print 'Input is null'
548 ELSE
549 DELETE FROM Schools
550 WHERE email = @school_email;
551 UPDATE Employees
552 SET employee_user_name = NULL, employee_password = NULL
553 WHERE school_email = @school_email
554GO
555----------------------------------------------------------
556
557-- Parent Procedures 1
558CREATE PROC InsertParents
559@email VARCHAR(50) ,
560@parent_address VARCHAR(100),
561@home_phone VARCHAR(20),
562@first_name VARCHAR(25),
563@last_name VARCHAR(25),
564@parent_user_name VARCHAR(25),
565@parent_password VARCHAR(25)
566AS
567IF
568@email IS NULL OR
569@parent_address IS NULL OR
570@home_phone IS NULL OR
571@first_name IS NULL OR
572@last_name IS NULL OR
573@parent_user_name IS NULL OR
574@parent_password IS NULL
575PRINT 'One of the inputs is null'
576ELSE
577INSERT INTO Parents(email, parent_address, home_phone, first_name, last_name, parent_user_name, parent_password )
578VALUES(@email, @parent_address, @home_phone, @first_name, @last_name, @parent_user_name, @parent_password)
579GO
580--2
581CREATE PROC Parents_Apply_Schools
582@ssn INT ,
583@name VARCHAR(50) ,
584@gender BIT,
585@birth_date DATE,
586@parent_email VARCHAR(50)
587AS
588 IF
589 @ssn IS NULL OR
590 @name IS NULL OR
591 @gender IS NULL OR
592 @birth_date IS NULL OR
593 @parent_email IS NULL
594 PRINT 'One of the inputs is null'
595 Else
596 INSERT INTO Applicants (ssn,name,gender,birth_date,parent_email)
597 VALUES(@ssn,@name,@gender,@birth_date,@parent_email)
598GO
599--3
600CREATE PROC Schools_Accepted_My_Children
601AS
602SELECT s.*
603FROM Schools s INNER JOIN Applicants_Applies_Schools a on a.school_email = s.email
604where a.Accepted = 'True'
605GO
606--4
607CREATE PROC ChooseSchool
608 @parent_email VARCHAR(50),
609 @child_ssn INT,
610 @school_email VARCHAR(50)
611AS
612 IF @parent_email is null or
613 @child_ssn is null or
614 @school_email is null
615 print 'Null Input'
616 ELSE
617 Begin
618 --Declare @table Table
619 Declare @name VARCHAR(50)
620 Declare @gender BIT
621 Declare @birth_date DATE
622 SELECT @name = e.name, @gender = e.gender, @birth_date = e.birth_date
623 From Applicants e INNER JOIN Applicants_Applies_Schools a on a.child_ssn = e.ssn
624 Where e.parent_email = @parent_email AND a.accepted = 1
625 Insert Into Enrolled_Students
626 (ssn , name , gender , birth_date , school_email , parent_email)
627 Values(@child_ssn, @name, @gender, @birth_date, @school_email, @parent_email)
628 DELETE FROM Enrolled_Students
629 WHERE ssn = @child_ssn
630 END
631GO
632--5
633CREATE PROC View_Reports
634@parent_email VARCHAR(50)
635AS
636SELECT r.*
637FROM Monthly_Reports r INNER JOIN Enrolled_Students e on r.enrolled_ssn = e.ssn
638WHERE e.parent_email = @parent_email
639GO
640--6
641CREATE PROC Reply
642@parent_email VARCHAR(50),
643@reply VARCHAR(200),
644@enrolled_ssn INT
645AS
646DECLARE @issue_date DATE
647UPDATE Monthly_Reports_Replies
648SET reply = @reply
649WHERE @parent_email = parent_email AND @enrolled_ssn = enrolled_ssn;
650GO
651--7
652CREATE PROC List_of_Children_Schools
653@parent_email VARCHAR(50)
654AS
655SELECT e.name + ' in ' + s.name AS 'List of my children schools'
656FROM Enrolled_Students e
657 INNER JOIN Schools s on e.school_email = s.email
658WHERE e.parent_email = @parent_email
659order by s.name
660GO
661--8
662CREATE PROC Posted_Announcments
663@parent_email VARCHAR(50),
664@Enrolled_ssn INT
665AS
666DECLARE @p10days TIMESTAMP = DAY(CURRENT_TIMESTAMP)
667SELECT a.announcement_description
668FROM Announcements_Visible_To_Parents ap
669 INNER JOIN Parents p ON p.email = ap.parent_email
670 INNER JOIN Announcements a ON a.administrator_email = ap.administrator_email
671 INNER JOIN Announcements_Visible_To_Enrolled_Students ae ON ae.administrator_email = ap.administrator_email
672WHERE ap.parent_email = @parent_email AND ae.enrolled_ssn = @Enrolled_ssn AND (DAY(ap.announcement_date) + 10) > @p10days
673GO
674--9
675CREATE PROC Rate_Teachers
676@parent_email VARCHAR(50),
677@teacher_email VARCHAR(50),
678@rating DECIMAL(2,1)
679AS
680IF @parent_email IS NULL OR
681 @teacher_email IS NULL OR
682 @rating IS NULL
683 print 'NULL INPUT'
684ELSE
685IF @parent_email IN
686 (
687 Select parent_email
688 From Enrolled_Students e
689 INNER JOIN Attends a on a.enrolled_ssn = e.ssn
690 INNER JOIN Teaches T on T.course_code = a.course_code
691 Where e.parent_email = @parent_email
692 )
693 INSERT INTO Parents_Ratings_To_Teachers(employee_email, parent_email, rating)
694 VALUES(@teacher_email, @parent_email, @rating)
695GO
696--10
697CREATE PROC Write_Reviews
698@parent_email VARCHAR(50),
699@school_email VARCHAR(50),
700@reviews VARCHAR(200)
701AS
702 IF @parent_email IS NULL OR @school_email IS NULL OR @reviews IS NULL
703 PRINT 'Incomplete input!'
704 ELSE IF NOT EXISTS(
705 SELECT e.parent_email
706 FROM Enrolled_Students e
707 Where e.parent_email = @parent_email AND e.school_email = @school_email)
708 PRINT 'You must have a student enrolled in this school'
709 ELSE
710 INSERT INTO Parents_Write_Review_Schools
711 (parent_email , reviews , school_email)
712 VALUES (@parent_email , @school_email , @reviews)
713GO
714/*
715CREATE PROC Write_Reviews
716@parent_email VARCHAR(50),
717@Enrolled_ssn INT,
718@reviews VARCHAR(200)
719AS
720UPDATE Parents_Write_Review_Schools
721SET reviews = @reviews
722WHERE parent_email = @parent_email AND @parent_email IN
723(
724 Select e.parent_email
725 FROM Enrolled_Students e INNER JOIN Schools s on e.school_email = s.email
726 Where name = @school_name
727)
728GO
729*/
730
731--11
732CREATE PROC Delete_review
733@parent_email VARCHAR(50),
734@school_name VARCHAR(50)
735AS
736 DELETE reviews FROM Parents_Write_Review_Schools
737 WHERE parent_email = @parent_email
738 AND @school_name IN(
739 SELECT s.name
740 FROM Schools s INNER JOIN Parents_Write_Review_Schools ps ON ps.parent_email = s.email
741 Where ps.parent_email = @parent_email)
742GO
743
744--12
745CREATE PROC Sum_rating
746@teacher_fname VARCHAR(25),
747@teacher_lname VARCHAR(25),
748@teacher_rate FLOAT OUTPUT
749As
750DECLARE @total INT
751DECLARE @count INT
752SELECT @Total = SUM(pt.rating), @count = count(pt.rating)
753FROM Parents_Ratings_To_Teachers pt
754 INNER JOIN Employees e ON e.email = pt.employee_email
755 WHERE e.first_name = @teacher_fname AND e.last_name = @teacher_lname
756SET @teacher_rate = @total/@count
757PRINT @teacher_rate
758GO
759--13
760CREATE PROC TOP_10_Schools
761@parent_email VARCHAR(50)
762AS
763SELECT s.name, COUNT(ps.reviews), COUNT(e.ssn)
764FROM Schools s
765 INNER JOIN Parents_Write_Review_Schools ps ON ps.school_email = s.email
766 INNER JOIN Enrolled_Students e ON e.school_email = s.email
767 WHERE ps.parent_email <> @parent_email
768GROUP BY s.name
769GO
770--14
771CREATE PROC Highest_International_School
772@schoolname VARCHAR(50) OUTPUT,
773@count INT OUTPUT
774AS
775SELECT @schoolname = s.name, @count = count(ps.reviews)
776FROM Schools s
777 INNER JOIN Parents_Write_Review_Schools ps ON ps.school_email = s.email
778GROUP BY s.name
779Having count(ps.reviews) = MAX(ps.reviews)
780GO
781---------------------------------------------------------------------------------
782-- System User
783--1
784CREATE PROC List_School
785@name VARCHAR(50),
786@address VARCHAR(100),
787@national BIT,
788@international BIT
789AS
790SELECT *
791FROM Schools
792WHERE name = @name OR school_address = @address OR is_national = @national OR is_international = @international
793GO
794--2
795CREATE PROC Schools_By_Levels
796@elementary BIT,
797@middle BIT,
798@high BIT
799AS
800SELECT name,is_elementary,is_middle,is_high
801FROM Schools
802WHERE is_elementary = @elementary or is_middle = @middle or is_high = @high
803GO
804--3
805CREATE PROC School_Info
806@name VARCHAR(50)
807AS
808SELECT *
809FROM Schools s
810 INNER JOIN Parents_Write_Review_Schools ps on ps.school_email = s.email
811 INNER JOIN Employees e ON e.school_email = s.email
812 WHERE s.name = @name
813GO
814
815----------------------------------------------------------------------------------------
816--adminstrator
817
818
819---------------------------------------------------------------------------------------------------------------------------
820
821
822--Student procedures
823--1
824CREATE PROC Enrolled_Student_Info_Update
825@ssn_in INT,
826@name_in VARCHAR(50),
827@gender_in BIT,
828@birth_date_in DATE,
829@password_in VARCHAR(20),
830@school_email_in VARCHAR(50),
831@parent_email_in VARCHAR(50)
832AS
833 IF @ssn_in is NULL
834 PRINT 'Please enter SSN.'
835 ELSE
836 UPDATE Enrolled_Students
837 SET
838 name = ISNULL(@name_in , name),
839 gender = ISNULL(@gender_in , gender),
840 birth_date = ISNULL(@birth_date_in , birth_date),
841 student_password = ISNULL(@password_in , student_password),
842 school_email = ISNULL(@school_email_in , school_email),
843 parent_email = ISNULL(@parent_email_in , parent_email)
844 WHERE
845 @ssn_in = Enrolled_Students.ssn
846GO
847
848--2
849CREATE PROC Enrolled_Student_View_Courses
850@ssn_in INT
851AS
852 IF @ssn_in = NULL
853 PRINT 'Please enter SSN.'
854 ELSE
855 SELECT t.grade, c.name
856 FROM Courses c INNER JOIN Attends t ON t.course_code = c.code
857 WHERE t.enrolled_ssn = @ssn_in
858 GROUP BY t.grade , c.name
859 ORDER BY c.name
860GO
861
862
863
864--3
865CREATE PROC Enrolled_Student_Post_Question
866@course_code_in VARCHAR(10),
867@question_itself_in VARCHAR(500),
868@askers_ssn_in INT
869AS
870 IF @askers_ssn_in = NULL
871 PRINT 'Please enter SSN.'
872 ELSE
873 INSERT INTO Questions (course_code,question_itself,askers_ssn)
874 VALUES (@course_code_in,@question_itself_in,@askers_ssn_in)
875GO
876
877
878
879
880
881
882
883
884
885
886--4
887CREATE PROC Enrolled_Student_View_Questions
888@course_code_in VARCHAR(10),
889@askers_ssn_in INT
890AS
891 IF @askers_ssn_in = NULL OR @course_code_in = NULL
892 PRINT 'Input Incomplete.'
893 ELSE IF NOT EXISTS
894 (SELECT *
895 FROM Enrolled_Students_View_Quesetions p --can use "Teaches" instead of this table. Redundant
896 WHERE @course_code_in = p.course_code
897 AND @askers_ssn_in = p.ssn)
898 PRINT 'You do not have the permission to view questions on this course.'
899 ELSE
900 SELECT q.question_itself, q.question_answer
901 FROM Questions q
902 WHERE @course_code_in = q.course_code
903GO
904
905
906
907
908
909
910--5
911CREATE PROC Enrolled_Student_View_All_Assignments
912@ssn_in INT
913AS
914 IF @ssn_in = NULL
915 PRINT 'Please enter ssn.'
916 ELSE
917 SELECT asst.assignment_number , asst.course_code , asst.posting_date , asst.due_date , asst.content
918 FROM Attends at
919 INNER JOIN Assignments asst ON asst.course_code = at.course_code
920 WHERE @ssn_in = at.enrolled_ssn
921/* SELECT t.assignment_number, t.course_code, t.posting_date, t.due_date, t.content
922 FROM Teaches t
923 --INNER JOIN Courses c ON t.course_code = c.code
924 INNER JOIN Assignments a ON a.course_code = t.course_code
925 WHERE t.enrolled_ssn = @ssn_in*/
926GO
927
928
929
930
931
932
933--6
934CREATE PROC Enrolled_Student_Solves_Assignment
935@ssn_in INT,
936@assignment_number_in INT,
937@teacher_email_in VARCHAR(50),
938@course_code_in VARCHAR(10),
939@solution_in VARCHAR(3000)
940AS
941 IF @ssn_in = NULL
942 OR @assignment_number_in = NULL
943 OR @course_code_in = NULL
944 OR @teacher_email_in = NULL
945 OR @solution_in = NULL
946 PRINT 'Incomplete Input.'
947 ELSE IF NOT EXISTS(
948 SELECT * FROM Attends t
949 INNER JOIN Teaches tt ON t.course_code = tt.course_code
950 WHERE t.enrolled_ssn = @ssn_in
951 AND t.course_code = @course_code_in
952 AND tt.teacher_email = @teacher_email_in)
953 PRINT 'This course is not assigned to you.'
954 ELSE
955 INSERT INTO Assignments_Solved_By_Students
956 (ssn,assignment_number,teacher_email,course_code,solution)
957 VALUES (@ssn_in,@assignment_number_in,@teacher_email_in,@course_code_in,@solution_in)
958GO
959
960
961
962
963
964
965--7
966
967CREATE PROC Enrolled_Students_Views_Assignments_Grades
968@ssn_in INT
969AS
970 IF @ssn_in = NULL
971 PRINT 'Please enter SSN.'
972 ELSE
973 SELECT s.course_code, s.assignment_number, s.grade
974 FROM Assignments_Solved_By_Students s
975 WHERE s.ssn = @ssn_in
976GO
977
978
979
980
981
982
983
984--8
985--student's school
986CREATE PROC Enrolled_Student_Views_Announcements
987@ssn_in INT
988AS
989 IF @ssn_in = NULL
990 PRINT 'Please enter SSN.'
991 ELSE
992 SELECT a.title, a.announcement_description
993 FROM Announcements_Visible_To_Enrolled_Students av
994 INNER JOIN Announcements a ON
995 a.administrator_email = av.administrator_email
996 AND a.announcement_date = av.announcement_date
997 AND a.announcement_time = av.announcement_time
998 WHERE @ssn_in = av.enrolled_ssn
999 --AND DAY(a.announcement_date) > (DAY(CURRENT_TIMESTAMP) - 10)
1000 AND DATEDIFF(DAY,a.announcement_date,CONVERT(DATE, GETDATE())) > 10
1001 AND EXISTS
1002 (SELECT *
1003 FROM Enrolled_Students es
1004 WHERE es.ssn = @ssn_in
1005 AND a.school_email = es.school_email)
1006GO
1007
1008
1009--9
1010CREATE PROC Enrolled_Student_Views_Activities
1011@ssn_in INT
1012AS
1013 IF @ssn_in = NULL
1014 PRINT 'Please enter SSN.'
1015 ELSE
1016 SELECT a.activity_date, a.activity_location, a.activity_type, a.activity_description, e.first_name, e.middle_name, e.last_name, e.gender, e.email
1017 FROM Activities a INNER JOIN Employees e ON a.teacher_email = e.email
1018 WHERE e.is_teacher = 1
1019 AND EXISTS (
1020 SELECT *
1021 FROM Enrolled_Students es
1022 WHERE es.ssn = @ssn_in
1023 AND a.school_email = es.school_email)
1024GO
1025
1026
1027
1028
1029--10
1030--PLEASE CORRECT THIS PROCEDURE
1031--"date is not recognised as a built-in function name"
1032CREATE PROC Enrolled_Student_Applies_To_Activity
1033@ssn_in INT,
1034@activity_date_in DATE,
1035@activity_location_in VARCHAR(30)
1036AS
1037 IF @ssn_in = NULL
1038 PRINT 'Please enter SSN.'
1039 ELSE IF EXISTS(
1040 SELECT * FROM Activities_Applied_By_Enrolled_Students ae
1041 INNER JOIN Activities a ON ae.activity_date = ae.activity_date AND ae.activity_location = a.activity_location
1042 WHERE @ssn_in = ae.ssn AND EXISTS(
1043 SELECT * FROM Activities sa
1044 WHERE sa.activity_date = @activity_date_in
1045 AND sa.activity_location = @activity_location_in
1046 AND sa.activity_type = a.activity_type
1047 AND sa.activity_date = a.activity_date
1048 ))
1049 PRINT 'Can not join two activities on the same date of the same type!'
1050 ELSE
1051 INSERT INTO Activities_Applied_By_Enrolled_Students
1052 (ssn, activity_date, activity_location)
1053 VALUES (@ssn_in,@activity_date_in,@activity_location_in)
1054GO
1055
1056--11
1057CREATE PROC Enrolled_Student_Join_Club
1058@ssn_in INT,
1059@club_name_in VARCHAR(50)
1060AS
1061 IF @ssn_in = NULL OR @club_name_in = NULL
1062 PRINT 'Incomplete input!'
1063 ELSE IF NOT EXISTS(
1064 SELECT * FROM Enrolled_Students es
1065 WHERE @ssn_in = es.ssn AND es.student_level > 9)
1066 PRINT 'You are not a high school student'
1067 ELSE IF NOT EXISTS(
1068 SELECT * FROM Enrolled_Students es
1069 INNER JOIN Clubs_Offered_By_Schools c ON c.school_email = es.school_email
1070 WHERE es.ssn = @ssn_in AND c.club_name = @club_name_in)
1071 PRINT 'Your school does not offer this club.'
1072 ELSE
1073 INSERT INTO Clubs_Joined_By_Enrolled_Student
1074 (club_name,enrolled_ssn)
1075 VALUES (@club_name_in,@ssn_in)
1076GO
1077
1078--yaaFaragEllaaaah
1079CREATE PROC Enrolled_Student_Course_Search
1080@ssn_in INT,
1081@course_code_in VARCHAR(10),
1082@course_name_in VARCHAR(50)
1083AS
1084 IF @ssn_in = NULL OR (@course_code_in = NULL AND @course_name_in = NULL)
1085 PRINT 'Incomplete input!'
1086 ELSE
1087 IF @course_code_in = NULL
1088 SELECT * FROM Courses c
1089 INNER JOIN Teaches t ON c.code = t.course_code
1090 WHERE @course_name_in = c.name
1091 ELSE IF @course_name_in = NULL
1092 SELECT * FROM Courses c
1093 INNER JOIN Teaches t ON c.code = t.course_code
1094 WHERE @course_code_in = c.code
1095 ELSE
1096 SELECT * FROM Courses c
1097 INNER JOIN Teaches t ON c.code = t.course_code
1098 WHERE @course_code_in = c.code AND @course_name_in = c.name
1099GO
1100
1101--heyya name = ISNULL(@name_in , name) tenfa3? 2aw fee 7aga zayyaha
1102
1103--------------------------------------------------------------------------
1104
1105
1106--TEACHERRRRRRRRRRRRRR
1107CREATE PROC Teacher_Sign_Up
1108@fname_in VARCHAR(25),
1109@mname_in VARCHAR(25),
1110@lname_in VARCHAR(25),
1111@birthdate_in DATE,
1112@address_in VARCHAR(100),
1113@email_in VARCHAR(50),
1114@gender_in BIT
1115AS
1116 IF @fname_in = NULL
1117 OR @mname_in = NULL
1118 OR @lname_in = NULL
1119 OR @birthdate_in = NULL
1120 OR @address_in = NULL
1121 OR @email_in = NULL
1122 OR @gender_in = NULL
1123 PRINT 'Missing Info'
1124 ELSE
1125 INSERT INTO Employees
1126 (first_name , middle_name , last_name , birth_date , employee_address , email , gender , is_teacher)
1127 VALUES (@fname_in , @mname_in , @lname_in , @birthdate_in , @address_in , @email_in , @gender_in , '1')
1128GO
1129--2
1130CREATE PROC Teacher_View_His_Courses
1131@email_in VARCHAR(50)
1132AS
1133 IF @email_in is NULL
1134 PRINT 'Please enter email!'
1135 ELSE
1136 SELECT c.name
1137 FROM Teaches t
1138 INNER JOIN Courses c ON c.code = t.course_code
1139 WHERE t.teacher_email = @email_in
1140
1141GO
1142--3
1143CREATE PROC Post_Assignments
1144 @assignment_number int,
1145 @course_code VARCHAR(10),
1146 @teacher_email VARCHAR(50),
1147 @posting_date DATE,
1148 @due_date DATE,
1149 @content VARCHAR(500)
1150AS
1151 IF
1152 @assignment_number is NULL OR
1153 @course_code is NULL OR
1154 @teacher_email is NULL OR
1155 @posting_date is NULL OR
1156 @due_date is NULL OR
1157 @content is NULL
1158 PRINT 'some data is null'
1159 ELSE
1160 INSERT INTO Assignments
1161 VALUES(@assignment_number,
1162 @course_code,
1163 @teacher_email,
1164 @posting_date,
1165 @due_date,
1166 @content )
1167GO
1168--4
1169CREATE PROC Students_Solutions
1170 @email_in VARCHAR(50),
1171 @course_code INT
1172AS
1173 SELECT a.ssn, a.solution
1174 FROM Assignments_Solved_By_Students a
1175 WHERE a.teacher_email = @email_in AND a.course_code = @course_code
1176 ORDER BY a.ssn
1177GO
1178--5
1179CREATE PROC Grade_Assignments
1180 @teacher_email VARCHAR(50),
1181 @enrolled_ssn INT,
1182 @course_code VARCHAR(10),
1183 @grade FLOAT
1184AS
1185 UPDATE Assignments_Solved_By_Students
1186 SET grade = @grade
1187 WHERE teacher_email = @teacher_email AND course_code = @course_code AND ssn = @enrolled_ssn
1188GO
1189--6
1190CREATE PROC Delete_Assignments
1191 @teacher_email VARCHAR(50),
1192 @course_code VARCHAR(10),
1193 @assignment_number InT
1194AS
1195DELETE FROM Assignments
1196WHERE teacher_email = @teacher_email AND course_code = @course_code AND assignment_number = @assignment_number
1197GO
1198
1199--7
1200CREATE PROC Write_Report
1201 @teacher_email VARCHAR(50),
1202 @enrolled_ssn INT,
1203 @comment VARCHAR(200)
1204AS
1205 UPDATE Monthly_Reports
1206 SET comment = @comment , issue_date = CAST(GETDATE() AS DATE)
1207 WHERE writer = @teacher_email AND enrolled_ssn = @enrolled_ssn
1208GO
1209--8
1210CREATE PROC View_Questions
1211@teacher_email VARCHAR(50),
1212@course_code VARCHAR(10)
1213AS
1214SELECT q.question_numbers, e.name, q.question_itself
1215FROM Questions q
1216 INNER JOIN Enrolled_Students e ON q.askers_ssn = e.ssn
1217 INNER JOIN teacher t ON t.course_code = q.course_code
1218 WHERE q.course_code = @course_code AND t.teacher_email = @teacher_email
1219GO
1220--9
1221CREATE PROC Answer_Questions
1222@teacher_email VARCHAR(50),
1223@course_code VARCHAR(10),
1224@answer VARCHAR(500),
1225@number int
1226AS
1227 UPDATE Questions
1228 SET question_answer = @answer
1229 WHERE question_number = @number
1230GO
1231--10
1232CREATE PROC ListStudents
1233 @teacher_email VARCHAR(50)
1234AS
1235 IF @teacher_email IS NULL
1236 PRINT 'Null Input'
1237 ELSE
1238 SELECT e.*
1239 FROM Enrolled_Students e
1240 INNER JOIN Attends a on e.ssn = a.enrolled_ssn
1241 INNER JOIN Teaches t on t.course_code = a.course_code
1242 WHERE t.teacher_email = @teacher_email
1243GO
1244--11
1245CREATE PROC StudentsWithoutActivities
1246AS
1247 SELECT ssn
1248 FROM Enrolled_Students
1249 WHERE ssn NOT IN(
1250 SELECT e.ssn
1251 FROM Enrolled_Students e
1252 INNER JOIN Activities_Applied_By_Enrolled_Students a on e.ssn = a.ssn)
1253GO
1254--12
1255CREATE PROC StudentWithMostClubs
1256AS
1257 DECLARE @maxC INT
1258 SELECT @maxC = COUNT(*)
1259 FROM Enrolled_Students e INNER JOIN Clubs_Joined_By_Enrolled_Student c on e.ssn = c.enrolled_ssn
1260 GROUP BY e.ssn, e.name
1261 SELECT e.name
1262 FROM Enrolled_Students e INNER JOIN Clubs_Joined_By_Enrolled_Student c on e.ssn = c.enrolled_ssn
1263 GROUP BY e.ssn, e.name
1264 HAVING COUNT(*) = @maxC
1265GO