annotate src/university-output/setup.sql @ 0:307a81e46071 tip

initial committ
author Robert McIntyre <rlm@mit.edu>
date Tue, 18 Oct 2011 01:17:49 -0700
parents
children
rev   line source
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);