1.1 --- /dev/null	Thu Jan 01 00:00:00 1970 +0000
     1.2 +++ b/src/university/setup.sql	Tue Oct 18 01:17:49 2011 -0700
     1.3 @@ -0,0 +1,155 @@
     1.4 +create table professors (
     1.5 +--     All the professors in the school.  The school uses the SSN of
     1.6 +--     each professor as an identifying unique number. Each row of the
     1.7 +--     table represents a unique professor.
     1.8 +       name varchar(255) not null,
     1.9 +       surname varchar(255) not null,
    1.10 +       email varchar(255),
    1.11 +       faculty varchar(255),
    1.12 +       telephone varchar(255),
    1.13 +       ssn decimal(9, 0) zerofill primary key)
    1.14 +       ENGINE INNODB;              
    1.15 +
    1.16 +create table students (
    1.17 +--     All the students in the school.  Each student has a unique ID
    1.18 +--     number assigned by the school.  Each row of the table
    1.19 +--     represents a unique student.
    1.20 +       name varchar(255) not null,
    1.21 +       surname varchar(255),
    1.22 +       email varchar(255),
    1.23 +       faculty varchar(255),
    1.24 +       year int,
    1.25 +       id decimal(9, 0) zerofill primary key)
    1.26 +       ENGINE INNODB; 
    1.27 +
    1.28 +create table courses (
    1.29 +--     Set of all classes offered by the school.  each row represents
    1.30 +--     a different unique class.
    1.31 +       name varchar(255) primary key,
    1.32 +       professor_ssn decimal(9,0),
    1.33 +       -- might be null, in which case a professor
    1.34 +       -- will need to be assigned at some later time.
    1.35 +       subject varchar(255),
    1.36 +       credits int not null,
    1.37 +       foreign key (professor_ssn) references professors (ssn)
    1.38 +              on update cascade
    1.39 +       	      on delete set null)
    1.40 +       ENGINE INNODB;
    1.41 +
    1.42 +create table enrollments (
    1.43 +--    Record of all student-class pairs which represent the current
    1.44 +--    enrollment of the school.
    1.45 +      coursename varchar(255) not null,
    1.46 +      student_id decimal(9,0) not null references students (id),
    1.47 +      foreign key (coursename) references courses (name)   
    1.48 +      	      on update cascade
    1.49 +	      on delete cascade,
    1.50 +      foreign key (student_id) references students (id)   
    1.51 +      	      on update cascade
    1.52 +	      on delete cascade,
    1.53 +      unique (coursename, student_id))
    1.54 +      ENGINE INNODB;
    1.55 +
    1.56 +
    1.57 +-- -----------------------------------------------------------------------------
    1.58 +-- Stored Procedure Section.
    1.59 +-- -----------------------------------------------------------------------------
    1.60 +
    1.61 +-- what do we need to be able to do?
    1.62 +
    1.63 +-- 1 - new professors, students, and courses     
    1.64 +-- 2 - assign a professor to a course.           
    1.65 +-- 3 - get a professor owning a course.          
    1.66 +-- 4 - get the details of prof, student, course  
    1.67 +-- 5 - remove a student from a course.           
    1.68 +
    1.69 +create procedure add_professor (in name varchar(255),
    1.70 +       		 	       	in surname varchar(255),
    1.71 +				in email varchar(255),
    1.72 +				in faculty varchar(255),
    1.73 +				in telephone varchar(255),
    1.74 +				in ssn decimal(9, 0))
    1.75 +	insert into professors values(name, surname, email, faculty, telephone, ssn);
    1.76 +
    1.77 +create procedure add_student (in name varchar(255),
    1.78 +       		 	      in surname varchar(255),
    1.79 +			      in email varchar(255),
    1.80 +			      in faculty varchar(255),
    1.81 +			      in year int,
    1.82 +			      in id decimal(9,0))
    1.83 +	insert into students values(name, surname, email, faculty, year, id);
    1.84 +
    1.85 +create procedure add_course 
    1.86 +       -- courses are initially created without any professor attached.
    1.87 +       	  	      	    (in name    varchar(255),
    1.88 + 			     in subject varchar(255),
    1.89 +			     in credits int)
    1.90 +	insert into courses values(name, null, subject, credits);
    1.91 +
    1.92 +create procedure assign_professor 
    1.93 +       		 (in course varchar(255),
    1.94 +		  in professor_ssn decimal(9,0))
    1.95 +	update courses 
    1.96 +	       set professor_ssn = professor_ssn
    1.97 +	       where name = course;
    1.98 +
    1.99 +create procedure enroll_student (in course varchar(255),
   1.100 +       		 		 in student_id decimal(9,0))
   1.101 +	insert into enrollments values (course, student_id);
   1.102 +
   1.103 +create procedure get_students (in course varchar(255))
   1.104 +       select name, surname from students where students.id in 
   1.105 +	      (select student_id from enrollments
   1.106 +	       where enrollments.coursename = course)
   1.107 +       order by surname asc, name asc;
   1.108 +
   1.109 +create procedure get_professor (in course varchar(255))
   1.110 +       select name, surname, ssn from professors 
   1.111 +       where professors.ssn = 
   1.112 +       (select professor_ssn from courses where courses.name = course);
   1.113 +
   1.114 +create procedure course_details (in course varchar(255))
   1.115 +       select name, credits from courses where name = course;
   1.116 +				
   1.117 +create procedure professor_details (in ssn decimal(9,0))
   1.118 +       select name, surname, email, faculty from professors
   1.119 +       where professors.ssn = ssn;
   1.120 +
   1.121 +create procedure student_details (in id decimal(9,0))
   1.122 +       select name, surname, email, faculty, year from students
   1.123 +       where students.id = id;
   1.124 +
   1.125 +create procedure remove_student (in id decimal(9,0), course varchar(255))
   1.126 +       delete from enrollments where 
   1.127 +       enrollments.coursename = course AND
   1.128 +       enrollemnts.student_id = id;
   1.129 +
   1.130 +create procedure replace_professor (in ssn decimal(9,0), course varchar(255))
   1.131 +       update courses 
   1.132 +       set professor_ssn = ssn
   1.133 +       where name = course;
   1.134 +
   1.135 +-- --------------------------------------------------------------
   1.136 +-- initialiaze database to test
   1.137 +-- --------------------------------------------------------------
   1.138 +call add_professor ('A', '1', 'email', 'CS', '012-345-6789', 0);
   1.139 +call add_professor ('B', '2', 'email', 'CS', '012-345-6789', 1);
   1.140 +call add_professor ('C', '3', 'email', 'CS', '012-345-6789', 2);
   1.141 +call add_professor ('D', '4', 'email', 'CS', '012-345-6789', 3);
   1.142 +
   1.143 +call add_student ('a', '1', 'email', 'CS', '1964', 0);
   1.144 +call add_student ('a', '2', 'email', 'CS', '1964', 5);
   1.145 +call add_student ('b', '2', 'email', 'CS', '1964', 1);
   1.146 +call add_student ('c', '3', 'email', 'CS', '1964', 2);
   1.147 +call add_student ('d', '4', 'email', 'CS', '1964', 3);
   1.148 +
   1.149 +call add_course ('course A', 'science', 5);
   1.150 +call add_course ('course B', 'science', 5);
   1.151 +call add_course ('course C', 'science', 5);
   1.152 +
   1.153 +call enroll_student ('course A', 0);
   1.154 +call enroll_student ('course A', 5);
   1.155 +call enroll_student ('course A', 1);
   1.156 +call enroll_student ('course A', 2);
   1.157 +
   1.158 +call assign_professor ('course A', 0);