Kr1SBv1u

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