Description
In this assignment, you will be required to use PostgreSQL. Your solutions
should include the PostgreSQL statements for solving the problems, as well as
the results of running these statements. Turn in a file .sql with your solutions
(where necessary, include comments explaining your solutions). Turn in a file
.txt which illustrates how your solutions work. Consider a database with the
following relations:
Student(sid int, sname text, major text) sid is primary key
Course(cno int, cname text, total int, max int) cno is primary key
total is the number of students enrolled in course cno
max is the maximum permitted enrollment for course cno
Prerequisite(cno int, prereq int) meaning: course cno has as a prerequisite course prereq
cno is foreign key referencing Course
prereq is foreign key referencing Course
HasTaken(sid int,cno int) meaning: student sid has taken course cno in the past
sid foreign key referencing Student
cno foreign key referencing Course
Enroll(sid int,cno int) meaning: student sid is currently enrolled in course cno
sid foreign key referencing Student
cno foreign key referencing Course
Waitlist(sid int,cno int, position int) meaning: student sid is on the waitlist to enroll in cno, where
pos is the relative position of student sid on the waitlist
to enroll in course cno
sid foreign key referencing Student
cno foreign key referencing Course
1. (a) Notice the primary key constraints specified for these relations. Write
insert triggers to enforce these primary constraints.
(b) Notice the foreign key constraints specified for these relations. Write
insert and delete triggers on the appropriate relations to enforce these
foreign key constraints. Furthermore, write delete triggers on the
Student and Course relations that have cascading deletion effects
on the relations that hold foreign keys that reference the primary
keys of these relations.
(c) The HasTaken relation is special in the sense that only inserts are
permitted on it. HasTaken serves as a historical record of courses
that students have taken in the past. Furthermore, we require that
no new inserts are permitted on HasTaken from the moment that
students start getting enrolled in the Enroll relation. Write trigger
on HasTaken to enforce these requirements.
1
(d) The Course and Prerequisite relations are special in the sense that
only inserts are permitted on them. In addition, we assume that
Prerequisite and Course can no longer change from the moment
that enrollment records get inserted or deleted in the Enroll relation.
In particular, no new courses and/or no new prerequisites for courses
can be added or updated from that point on. The idea behind these
requirements is that course-data is very rigid temporarily relative to
students enrolling in such courses. Write triggers to enforce these
requirements.
2. Furthermore (1) inserts and deletes in the Enroll relation and (2) insert
and deletes in the Waitlist are governed by the following rules:
• A student can only enroll in a course if he or she has taken all the
prerequisites for that course. If the enrollment succeeds, the total
enrollment for that course needs to be incremented by 1.
• A student can only enroll in a course if his or her enrollment does
not exceed the maximum enrollment for that course. However, the
student must then be placed at the next available position on the
waitlist for that course.
• A student can drop a course, either by removing him or herself from
the Waitlist relation or from the Enroll relation. When the latter
happens and if there are students on the waitlist for that course,
then the student who is at the first position for that course on the
waitlist gets enrolled in that course and removed from the waitlist.
If there are no students on the waitlist, then the total enrollment for
that course needs to decrease by 1.
Write appropriate triggers to enforce these rules.
3. Consider a situation wherein we repeatedly, and randomly, throw 3 dice
d1, d2, d3. Clearly, the possible value for each such dice di
is between 1
and 6.
Define the random variable T which, when applied to a throw of 3 dice
d1, d2, and d3, returns the sum of the 3 dice values. I.e.,
T(d1, d2, d3) = d1 + d2 + d3.
Notice that the range of values for T is [3, 18]. For example T(2, 4, 5) =
T(3, 2, 6) = 11, T(1, 1, 6) = 1 + 1 + 6 = 8, T(1, 1, 1) = 3, etc.
The objective of this question is to compute, in an incremental way, an
approximation of the expected value E(T) of the random variable T, as
well as an approximation of the standard deviation V(T) of the random
2
variable T. By definition, the exact values for E(T) and V(T) are as
follows:
E(T) = X
18
v=3
v · p(T = v)
and
V(T) =
vuutX
18
v=3
v
2 · p(T = v) − E(T)
2
where p(T = v) is the probability that the outcome of T is the value v.
Incidentally, the theoretically correct value for E(T) is 10.5 and that for
V(T) is √
8.75 = 2.95803 · · ·.
Your solution requires the use of a trigger which maintains an approximation of E(T) and V(T). More specifically, each time a throw of 3 dice
is made, these approximations need to be refined in accordance with the
outcome of T for this throw. Furthermore, and this needs to be a core
part of your solution, the approximations are required to be maintained
in an incremental fashion. In particular, you can not store the set of all
throws and from that set find the approximations of E(T) and V(T).
To obtain a random value for a single dice, you can use the function call
floor(random()*6)+1
This works since the function call random() produces a random value in
the range [0, 1). Therefore a throw of three dices is a tuple of the form
(floor(random()*6)+1, floor(random()*6)+1, floor(random()*6)+1).
You need to experiment with different numbers of throws. To do this, use
the following function template:
create or replace function runExperiment(n int)
returns record
as
$$
declare i int;
begin
— code to initialize the experiment;
for i in 1..n loop
— code to call a trigger on
— throw (floor(random()*6)+1, floor(random()*6)+1, floor(random()*6)+1);
3
end loop;
return — (approximation for E(T), approximation for V(T));
end;
$$ language plpgsql;
Then execute for example
select runExperiment(1000);
This will be an experiment with n = 1000 throws of 3 dice each.
The following is a table with some values obtained for E(T) and V(T) for
different values of n:
n E(T) V(T)
10 10.6 3.16859
100 10.24 2.80756
1000 10.419 3.06356
10000 10.505 2.94929
100000 10.490 2.95849
4