Mercurial > coderloop

comparison src/university/setup.sql @ 0:307a81e46071 tip

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