|
rlm@0
|
1 create table professors (
|
|
rlm@0
|
2 -- All the professors in the school. The school uses the SSN of
|
|
rlm@0
|
3 -- each professor as an identifying unique number. Each row of the
|
|
rlm@0
|
4 -- table represents a unique professor.
|
|
rlm@0
|
5 name varchar(255) not null,
|
|
rlm@0
|
6 surname varchar(255) not null,
|
|
rlm@0
|
7 email varchar(255),
|
|
rlm@0
|
8 faculty varchar(255),
|
|
rlm@0
|
9 telephone varchar(255),
|
|
rlm@0
|
10 ssn decimal(9, 0) zerofill primary key)
|
|
rlm@0
|
11 ENGINE INNODB;
|
|
rlm@0
|
12
|
|
rlm@0
|
13 create table students (
|
|
rlm@0
|
14 -- All the students in the school. Each student has a unique ID
|
|
rlm@0
|
15 -- number assigned by the school. Each row of the table
|
|
rlm@0
|
16 -- represents a unique student.
|
|
rlm@0
|
17 name varchar(255) not null,
|
|
rlm@0
|
18 surname varchar(255),
|
|
rlm@0
|
19 email varchar(255),
|
|
rlm@0
|
20 faculty varchar(255),
|
|
rlm@0
|
21 year int,
|
|
rlm@0
|
22 id decimal(9, 0) zerofill primary key)
|
|
rlm@0
|
23 ENGINE INNODB;
|
|
rlm@0
|
24
|
|
rlm@0
|
25 create table courses (
|
|
rlm@0
|
26 -- Set of all classes offered by the school. each row represents
|
|
rlm@0
|
27 -- a different unique class.
|
|
rlm@0
|
28 name varchar(255) primary key,
|
|
rlm@0
|
29 professor_ssn decimal(9,0),
|
|
rlm@0
|
30 -- might be null, in which case a professor
|
|
rlm@0
|
31 -- will need to be assigned at some later time.
|
|
rlm@0
|
32 subject varchar(255),
|
|
rlm@0
|
33 credits int not null,
|
|
rlm@0
|
34 foreign key (professor_ssn) references professors (ssn)
|
|
rlm@0
|
35 on update cascade
|
|
rlm@0
|
36 on delete set null)
|
|
rlm@0
|
37 ENGINE INNODB;
|
|
rlm@0
|
38
|
|
rlm@0
|
39 create table enrollments (
|
|
rlm@0
|
40 -- Record of all student-class pairs which represent the current
|
|
rlm@0
|
41 -- enrollment of the school.
|
|
rlm@0
|
42 coursename varchar(255) not null,
|
|
rlm@0
|
43 student_id decimal(9,0) not null references students (id),
|
|
rlm@0
|
44 foreign key (coursename) references courses (name)
|
|
rlm@0
|
45 on update cascade
|
|
rlm@0
|
46 on delete cascade,
|
|
rlm@0
|
47 foreign key (student_id) references students (id)
|
|
rlm@0
|
48 on update cascade
|
|
rlm@0
|
49 on delete cascade,
|
|
rlm@0
|
50 unique (coursename, student_id))
|
|
rlm@0
|
51 ENGINE INNODB;
|
|
rlm@0
|
52
|
|
rlm@0
|
53
|
|
rlm@0
|
54 -- -----------------------------------------------------------------------------
|
|
rlm@0
|
55 -- Stored Procedure Section.
|
|
rlm@0
|
56 -- -----------------------------------------------------------------------------
|
|
rlm@0
|
57
|
|
rlm@0
|
58 -- what do we need to be able to do?
|
|
rlm@0
|
59
|
|
rlm@0
|
60 -- 1 - new professors, students, and courses
|
|
rlm@0
|
61 -- 2 - assign a professor to a course.
|
|
rlm@0
|
62 -- 3 - get a professor owning a course.
|
|
rlm@0
|
63 -- 4 - get the details of prof, student, course
|
|
rlm@0
|
64 -- 5 - remove a student from a course.
|
|
rlm@0
|
65
|
|
rlm@0
|
66 create procedure add_professor (in name varchar(255),
|
|
rlm@0
|
67 in surname varchar(255),
|
|
rlm@0
|
68 in email varchar(255),
|
|
rlm@0
|
69 in faculty varchar(255),
|
|
rlm@0
|
70 in telephone varchar(255),
|
|
rlm@0
|
71 in ssn decimal(9, 0))
|
|
rlm@0
|
72 insert into professors values(name, surname, email, faculty, telephone, ssn);
|
|
rlm@0
|
73
|
|
rlm@0
|
74 create procedure add_student (in name varchar(255),
|
|
rlm@0
|
75 in surname varchar(255),
|
|
rlm@0
|
76 in email varchar(255),
|
|
rlm@0
|
77 in faculty varchar(255),
|
|
rlm@0
|
78 in year int,
|
|
rlm@0
|
79 in id decimal(9,0))
|
|
rlm@0
|
80 insert into students values(name, surname, email, faculty, year, id);
|
|
rlm@0
|
81
|
|
rlm@0
|
82 create procedure add_course
|
|
rlm@0
|
83 -- courses are initially created without any professor attached.
|
|
rlm@0
|
84 (in name varchar(255),
|
|
rlm@0
|
85 in subject varchar(255),
|
|
rlm@0
|
86 in credits int)
|
|
rlm@0
|
87 insert into courses values(name, null, subject, credits);
|
|
rlm@0
|
88
|
|
rlm@0
|
89 create procedure assign_professor
|
|
rlm@0
|
90 (in course varchar(255),
|
|
rlm@0
|
91 in professor_ssn decimal(9,0))
|
|
rlm@0
|
92 update courses
|
|
rlm@0
|
93 set professor_ssn = professor_ssn
|
|
rlm@0
|
94 where name = course;
|
|
rlm@0
|
95
|
|
rlm@0
|
96 create procedure enroll_student (in course varchar(255),
|
|
rlm@0
|
97 in student_id decimal(9,0))
|
|
rlm@0
|
98 insert into enrollments values (course, student_id);
|
|
rlm@0
|
99
|
|
rlm@0
|
100 create procedure get_students (in course varchar(255))
|
|
rlm@0
|
101 select name, surname from students where students.id in
|
|
rlm@0
|
102 (select student_id from enrollments
|
|
rlm@0
|
103 where enrollments.coursename = course)
|
|
rlm@0
|
104 order by surname asc, name asc;
|
|
rlm@0
|
105
|
|
rlm@0
|
106 create procedure get_professor (in course varchar(255))
|
|
rlm@0
|
107 select name, surname, ssn from professors
|
|
rlm@0
|
108 where professors.ssn =
|
|
rlm@0
|
109 (select professor_ssn from courses where courses.name = course);
|
|
rlm@0
|
110
|
|
rlm@0
|
111 create procedure course_details (in course varchar(255))
|
|
rlm@0
|
112 select name, credits from courses where name = course;
|
|
rlm@0
|
113
|
|
rlm@0
|
114 create procedure professor_details (in ssn decimal(9,0))
|
|
rlm@0
|
115 select name, surname, email, faculty from professors
|
|
rlm@0
|
116 where professors.ssn = ssn;
|
|
rlm@0
|
117
|
|
rlm@0
|
118 create procedure student_details (in id decimal(9,0))
|
|
rlm@0
|
119 select name, surname, email, faculty, year from students
|
|
rlm@0
|
120 where students.id = id;
|
|
rlm@0
|
121
|
|
rlm@0
|
122 create procedure remove_student (in id decimal(9,0), course varchar(255))
|
|
rlm@0
|
123 delete from enrollments where
|
|
rlm@0
|
124 enrollments.coursename = course AND
|
|
rlm@0
|
125 enrollemnts.student_id = id;
|
|
rlm@0
|
126
|
|
rlm@0
|
127 create procedure replace_professor (in ssn decimal(9,0), course varchar(255))
|
|
rlm@0
|
128 update courses
|
|
rlm@0
|
129 set professor_ssn = ssn
|
|
rlm@0
|
130 where name = course;
|
|
rlm@0
|
131
|
|
rlm@0
|
132 -- --------------------------------------------------------------
|
|
rlm@0
|
133 -- initialiaze database to test
|
|
rlm@0
|
134 -- --------------------------------------------------------------
|
|
rlm@0
|
135 call add_professor ('A', '1', 'email', 'CS', '012-345-6789', 0);
|
|
rlm@0
|
136 call add_professor ('B', '2', 'email', 'CS', '012-345-6789', 1);
|
|
rlm@0
|
137 call add_professor ('C', '3', 'email', 'CS', '012-345-6789', 2);
|
|
rlm@0
|
138 call add_professor ('D', '4', 'email', 'CS', '012-345-6789', 3);
|
|
rlm@0
|
139
|
|
rlm@0
|
140 call add_student ('a', '1', 'email', 'CS', '1964', 0);
|
|
rlm@0
|
141 call add_student ('a', '2', 'email', 'CS', '1964', 5);
|
|
rlm@0
|
142 call add_student ('b', '2', 'email', 'CS', '1964', 1);
|
|
rlm@0
|
143 call add_student ('c', '3', 'email', 'CS', '1964', 2);
|
|
rlm@0
|
144 call add_student ('d', '4', 'email', 'CS', '1964', 3);
|
|
rlm@0
|
145
|
|
rlm@0
|
146 call add_course ('course A', 'science', 5);
|
|
rlm@0
|
147 call add_course ('course B', 'science', 5);
|
|
rlm@0
|
148 call add_course ('course C', 'science', 5);
|
|
rlm@0
|
149
|
|
rlm@0
|
150 call enroll_student ('course A', 0);
|
|
rlm@0
|
151 call enroll_student ('course A', 5);
|
|
rlm@0
|
152 call enroll_student ('course A', 1);
|
|
rlm@0
|
153 call enroll_student ('course A', 2);
|
|
rlm@0
|
154
|
|
rlm@0
|
155 call assign_professor ('course A', 0);
|
