rlm@0: create table professors (
rlm@0: --     All the professors in the school.  The school uses the SSN of
rlm@0: --     each professor as an identifying unique number. Each row of the
rlm@0: --     table represents a unique professor.
rlm@0:        name varchar(255) not null,
rlm@0:        surname varchar(255) not null,
rlm@0:        email varchar(255),
rlm@0:        faculty varchar(255),
rlm@0:        telephone varchar(255),
rlm@0:        ssn decimal(9, 0) zerofill primary key)
rlm@0:        ENGINE INNODB;              
rlm@0: 
rlm@0: create table students (
rlm@0: --     All the students in the school.  Each student has a unique ID
rlm@0: --     number assigned by the school.  Each row of the table
rlm@0: --     represents a unique student.
rlm@0:        name varchar(255) not null,
rlm@0:        surname varchar(255),
rlm@0:        email varchar(255),
rlm@0:        faculty varchar(255),
rlm@0:        year int,
rlm@0:        id decimal(9, 0) zerofill primary key)
rlm@0:        ENGINE INNODB; 
rlm@0: 
rlm@0: create table courses (
rlm@0: --     Set of all classes offered by the school.  each row represents
rlm@0: --     a different unique class.
rlm@0:        name varchar(255) primary key,
rlm@0:        professor_ssn decimal(9,0),
rlm@0:        -- might be null, in which case a professor
rlm@0:        -- will need to be assigned at some later time.
rlm@0:        subject varchar(255),
rlm@0:        credits int not null,
rlm@0:        foreign key (professor_ssn) references professors (ssn)
rlm@0:               on update cascade
rlm@0:        	      on delete set null)
rlm@0:        ENGINE INNODB;
rlm@0: 
rlm@0: create table enrollments (
rlm@0: --    Record of all student-class pairs which represent the current
rlm@0: --    enrollment of the school.
rlm@0:       coursename varchar(255) not null,
rlm@0:       student_id decimal(9,0) not null references students (id),
rlm@0:       foreign key (coursename) references courses (name)   
rlm@0:       	      on update cascade
rlm@0: 	      on delete cascade,
rlm@0:       foreign key (student_id) references students (id)   
rlm@0:       	      on update cascade
rlm@0: 	      on delete cascade,
rlm@0:       unique (coursename, student_id))
rlm@0:       ENGINE INNODB;
rlm@0: 
rlm@0: 
rlm@0: -- -----------------------------------------------------------------------------
rlm@0: -- Stored Procedure Section.
rlm@0: -- -----------------------------------------------------------------------------
rlm@0: 
rlm@0: -- what do we need to be able to do?
rlm@0: 
rlm@0: -- 1 - new professors, students, and courses     
rlm@0: -- 2 - assign a professor to a course.           
rlm@0: -- 3 - get a professor owning a course.          
rlm@0: -- 4 - get the details of prof, student, course  
rlm@0: -- 5 - remove a student from a course.           
rlm@0: 
rlm@0: create procedure add_professor (in name varchar(255),
rlm@0:        		 	       	in surname varchar(255),
rlm@0: 				in email varchar(255),
rlm@0: 				in faculty varchar(255),
rlm@0: 				in telephone varchar(255),
rlm@0: 				in ssn decimal(9, 0))
rlm@0: 	insert into professors values(name, surname, email, faculty, telephone, ssn);
rlm@0: 
rlm@0: create procedure add_student (in name varchar(255),
rlm@0:        		 	      in surname varchar(255),
rlm@0: 			      in email varchar(255),
rlm@0: 			      in faculty varchar(255),
rlm@0: 			      in year int,
rlm@0: 			      in id decimal(9,0))
rlm@0: 	insert into students values(name, surname, email, faculty, year, id);
rlm@0: 
rlm@0: create procedure add_course 
rlm@0:        -- courses are initially created without any professor attached.
rlm@0:        	  	      	    (in name    varchar(255),
rlm@0:  			     in subject varchar(255),
rlm@0: 			     in credits int)
rlm@0: 	insert into courses values(name, null, subject, credits);
rlm@0: 
rlm@0: create procedure assign_professor 
rlm@0:        		 (in course varchar(255),
rlm@0: 		  in professor_ssn decimal(9,0))
rlm@0: 	update courses 
rlm@0: 	       set professor_ssn = professor_ssn
rlm@0: 	       where name = course;
rlm@0: 
rlm@0: create procedure enroll_student (in course varchar(255),
rlm@0:        		 		 in student_id decimal(9,0))
rlm@0: 	insert into enrollments values (course, student_id);
rlm@0: 
rlm@0: create procedure get_students (in course varchar(255))
rlm@0:        select name, surname from students where students.id in 
rlm@0: 	      (select student_id from enrollments
rlm@0: 	       where enrollments.coursename = course)
rlm@0:        order by surname asc, name asc;
rlm@0: 
rlm@0: create procedure get_professor (in course varchar(255))
rlm@0:        select name, surname, ssn from professors 
rlm@0:        where professors.ssn = 
rlm@0:        (select professor_ssn from courses where courses.name = course);
rlm@0: 
rlm@0: create procedure course_details (in course varchar(255))
rlm@0:        select name, credits from courses where name = course;
rlm@0: 				
rlm@0: create procedure professor_details (in ssn decimal(9,0))
rlm@0:        select name, surname, email, faculty from professors
rlm@0:        where professors.ssn = ssn;
rlm@0: 
rlm@0: create procedure student_details (in id decimal(9,0))
rlm@0:        select name, surname, email, faculty, year from students
rlm@0:        where students.id = id;
rlm@0: 
rlm@0: create procedure remove_student (in id decimal(9,0), course varchar(255))
rlm@0:        delete from enrollments where 
rlm@0:        enrollments.coursename = course AND
rlm@0:        enrollemnts.student_id = id;
rlm@0: 
rlm@0: create procedure replace_professor (in ssn decimal(9,0), course varchar(255))
rlm@0:        update courses 
rlm@0:        set professor_ssn = ssn
rlm@0:        where name = course;
rlm@0: 
rlm@0: -- --------------------------------------------------------------
rlm@0: -- initialiaze database to test
rlm@0: -- --------------------------------------------------------------
rlm@0: call add_professor ('A', '1', 'email', 'CS', '012-345-6789', 0);
rlm@0: call add_professor ('B', '2', 'email', 'CS', '012-345-6789', 1);
rlm@0: call add_professor ('C', '3', 'email', 'CS', '012-345-6789', 2);
rlm@0: call add_professor ('D', '4', 'email', 'CS', '012-345-6789', 3);
rlm@0: 
rlm@0: call add_student ('a', '1', 'email', 'CS', '1964', 0);
rlm@0: call add_student ('a', '2', 'email', 'CS', '1964', 5);
rlm@0: call add_student ('b', '2', 'email', 'CS', '1964', 1);
rlm@0: call add_student ('c', '3', 'email', 'CS', '1964', 2);
rlm@0: call add_student ('d', '4', 'email', 'CS', '1964', 3);
rlm@0: 
rlm@0: call add_course ('course A', 'science', 5);
rlm@0: call add_course ('course B', 'science', 5);
rlm@0: call add_course ('course C', 'science', 5);
rlm@0: 
rlm@0: call enroll_student ('course A', 0);
rlm@0: call enroll_student ('course A', 5);
rlm@0: call enroll_student ('course A', 1);
rlm@0: call enroll_student ('course A', 2);
rlm@0: 
rlm@0: call assign_professor ('course A', 0);