create table professors (

 --     All the professors in the school.  The school uses the SSN of

 --     each professor as an identifying unique number. Each row of the

 --     table represents a unique professor.

        name varchar(255) not null,

        surname varchar(255) not null,

        email varchar(255),

        faculty varchar(255),

        telephone varchar(255),

        ssn decimal(9, 0) zerofill primary key)

        ENGINE INNODB;              

 

 create table students (

 --     All the students in the school.  Each student has a unique ID

 --     number assigned by the school.  Each row of the table

 --     represents a unique student.

        name varchar(255) not null,

        surname varchar(255),

        email varchar(255),

        faculty varchar(255),

        year int,

        id decimal(9, 0) zerofill primary key)

        ENGINE INNODB; 

 

 create table courses (

 --     Set of all classes offered by the school.  each row represents

 --     a different unique class.

        name varchar(255) primary key,

        professor_ssn decimal(9,0),

        -- might be null, in which case a professor

        -- will need to be assigned at some later time.

        subject varchar(255),

        credits int not null,

        foreign key (professor_ssn) references professors (ssn)

               on update cascade

        	      on delete set null)

        ENGINE INNODB;

 

 create table enrollments (

 --    Record of all student-class pairs which represent the current

 --    enrollment of the school.

       coursename varchar(255) not null,

       student_id decimal(9,0) not null references students (id),

       foreign key (coursename) references courses (name)   

       	      on update cascade

 	      on delete cascade,

       foreign key (student_id) references students (id)   

       	      on update cascade

 	      on delete cascade,

       unique (coursename, student_id))

       ENGINE INNODB;

 

 

 -- -----------------------------------------------------------------------------

 -- Stored Procedure Section.

 -- -----------------------------------------------------------------------------

 

 -- what do we need to be able to do?

 

 -- 1 - new professors, students, and courses     

 -- 2 - assign a professor to a course.           

 -- 3 - get a professor owning a course.          

 -- 4 - get the details of prof, student, course  

 -- 5 - remove a student from a course.           

 

 create procedure add_professor (in name varchar(255),

        		 	       	in surname varchar(255),

 				in email varchar(255),

 				in faculty varchar(255),

 				in telephone varchar(255),

 				in ssn decimal(9, 0))

 	insert into professors values(name, surname, email, faculty, telephone, ssn);

 

 create procedure add_student (in name varchar(255),

        		 	      in surname varchar(255),

 			      in email varchar(255),

 			      in faculty varchar(255),

 			      in year int,

 			      in id decimal(9,0))

 	insert into students values(name, surname, email, faculty, year, id);

 

 create procedure add_course 

        -- courses are initially created without any professor attached.

        	  	      	    (in name    varchar(255),

  			     in subject varchar(255),

 			     in credits int)

 	insert into courses values(name, null, subject, credits);

 

 create procedure assign_professor 

        		 (in course varchar(255),

 		  in professor_ssn decimal(9,0))

 	update courses 

 	       set professor_ssn = professor_ssn

 	       where name = course;

 

 create procedure enroll_student (in course varchar(255),

        		 		 in student_id decimal(9,0))

 	insert into enrollments values (course, student_id);

 

 create procedure get_students (in course varchar(255))

        select name, surname from students where students.id in 

 	      (select student_id from enrollments

 	       where enrollments.coursename = course)

        order by surname asc, name asc;

 

 create procedure get_professor (in course varchar(255))

        select name, surname, ssn from professors 

        where professors.ssn = 

        (select professor_ssn from courses where courses.name = course);

 

 create procedure course_details (in course varchar(255))

        select name, credits from courses where name = course;

 				

 create procedure professor_details (in ssn decimal(9,0))

        select name, surname, email, faculty from professors

        where professors.ssn = ssn;

 

 create procedure student_details (in id decimal(9,0))

        select name, surname, email, faculty, year from students

        where students.id = id;

 

 create procedure remove_student (in id decimal(9,0), course varchar(255))

        delete from enrollments where 

        enrollments.coursename = course AND

        enrollemnts.student_id = id;

 

 create procedure replace_professor (in ssn decimal(9,0), course varchar(255))

        update courses 

        set professor_ssn = ssn

        where name = course;

 

 -- --------------------------------------------------------------

 -- initialiaze database to test

 -- --------------------------------------------------------------

 call add_professor ('A', '1', 'email', 'CS', '012-345-6789', 0);

 call add_professor ('B', '2', 'email', 'CS', '012-345-6789', 1);

 call add_professor ('C', '3', 'email', 'CS', '012-345-6789', 2);

 call add_professor ('D', '4', 'email', 'CS', '012-345-6789', 3);

 

 call add_student ('a', '1', 'email', 'CS', '1964', 0);

 call add_student ('a', '2', 'email', 'CS', '1964', 5);

 call add_student ('b', '2', 'email', 'CS', '1964', 1);

 call add_student ('c', '3', 'email', 'CS', '1964', 2);

 call add_student ('d', '4', 'email', 'CS', '1964', 3);

 

 call add_course ('course A', 'science', 5);

 call add_course ('course B', 'science', 5);

 call add_course ('course C', 'science', 5);

 

 call enroll_student ('course A', 0);

 call enroll_student ('course A', 5);

 call enroll_student ('course A', 1);

 call enroll_student ('course A', 2);

 

 call assign_professor ('course A', 0);