B561 Assignment 4-the primary key constraints Solved

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:

1. (a) Notice the primary key constraints specified for these relations. Write insert triggers to enforce these primary constraints.
   • Notice the foreign key constraints specified for these relations. Writeinsert 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.
   • 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 Write trigger on HasTaken to enforce these requirements.
   • 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 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) insertand 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 diced₁, d₂, d₃. Clearly, the possible value for each such dice d_i is between 1 and 6.

Define the random variable T which, when applied to a throw of 3 dice d₁, d₂, and d₃, returns the sum of the 3 dice values. I.e.,

T(d₁,d₂,d₃) = d₁ + d₂ + d₃.

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 variable T. By definition, the exact values for E(T) and V(T) are as follows:

E

and

V

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);

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: