University of Helsinki,

Introduction to Databases, August 31^st 2001, H.Laine

Please write on each separate paper your name, date of birth (if you have not registered, please give your personal number), the name of the course and the examiner, the date of the exam, and your signature.

1. Let us examine the relation A(a,b,c) (size n>0 rows), B(aàA, d,e) (size m>0 rows) and C(aàA,d,e) (size p>0 rows). Annotations : p projection, ´ cross product, |´| join, s selection and * natural join.

a) What is the join condition in the operation B*C?

B.a=C.a and B.d=C.d and B.e=C.e

b) May p_d(B) be empty?

NO

c) If, in relation A and B, there are the same amount of rows, n=m, then is p_a(A) = p_a(B)?

Not necessarily

d) If p_a(B) - p_a(C) is empty, is B – C also empty?

Not necessarily

e) Which conditions have to be filled to make A È B possible? (10p)

The domain for A.a must be the same as the domain of B.a (this is true because B.a is a foreign key. Domain for A.b must equal the domain of B.d and the domain od A.c must equal the domain of B.e.

Problems 2 – 4 refer to the following tables on medical prescriptions

doctor(id, name)

patient(personal_number, name, year_of_birth)

prescription(prescription_number, date_of_issue, issuer->doctor,

receiver->patient)

medication(prescription_number->prescription, medicine_id->medicine, dosage,

instructions)

medicine(medicine_id, name, main_purpose, under_observation)

observation_list(doctor->doctor,medicine->medicine,date)

The domain of the column under_observation is {’yes’,’no’}and it tells us whether the use of the medicine is under observation. The column dosage in the table medication expresses the amount of prescribed medicine in units. The function sysdate gives us today’s date.

2. Give the following SQL queries. Specify an appropriate order for the answers.

a) The names of medicines against high blood pressure.

select name

from medicine

where main_purpose=’high blood pressure’

order by name;

b) The names of patients who have been prescribed Burana 600 during June 2001.

select name

from patient

where personal_number in

(select receiver

from prescription p,medication m, medicine c

where p.prescription_number=m.prescription:number and

m.medicine_id=c.medicine_id and

c.name=’Burana 600’ and

p.date_issued between ‘1.6.2001’ and ’31.6.2001’)

order by name;

c) The doctors’ strike started on March 12th 2001 and ended on August 19th 2001. Give the names of the doctors who did not write any prescriptions during the strike. (12p)

select name

from doctor

where id not in

(select issuer

from prescription

where date issued between ’12.3.2001’ and ’19.8.2001’)

order by name;

3. Give the following SQL queries. Specify an appropriate order for the answers.

a) How many prescriptions with Viagra have been issued this year?

Select count(*)

From prescription

Where to_char(date_issued,’YYYY)=to_char(sysdate,’YYYY’) and

Prescription_number in

(select prescription_number from medication

where medicine_id in

(select medicine_id from medicine

where name =’Viagra’)

);

b) The names of doctors who have prescribed over 20,000 units of anti-depressants (main purpose depression) during this year, and the total amount of anti-depressants that each of these doctors has prescribed?

select doctor.name, doctor.id, sum(dosage)

from doctor, prescription, medication, medicine

where prescription.issuer=doctor.id and

prescription.prescription_number=

medication.prescription_number and

medication.medicine_id=medicine.medicine_id and

medicine.main_purpose=’depression’ and

to_char(date_issued,’YYYY)=to_char(sysdate,’YYYY’)

group by doctor.name, doctor.id;

c) Which medicine has been prescribed the most in terms of units? (12p)

select medicine.id, medicine.name, sum(dosage)

from medicine, medication

where medicine.medicine_id=medication.medicine_id

group by medicine.id, medicine.name

having sum(dosage)>=

(select sum(dosage)

from medication

group by medicine_id);

4. A doctor is placed on the observation list if s/he has prescribed a medicine that is under observation over 200 times during one month. The observation is dated on the last day of the month and stays in the list for a year. The observation list is updated on the 15^th of each month, when the previous month’s situation is considered. Which database operations have to be done at that point and how are they presented with SQL? Assume that the functions to deal with dates are LastOfMonth,(date) – which provides the last day of the month containing the date that has been given as parameter, and FirstOfMonth respectively. Sysdate gives the present date. [The date one year ago can be computed as sysdate – 365]. (8p)

/* remove the old ones */

delete from observation_list

where date <sysdate-365;

/* add the new ones */

insert into observation_list

select doctor.id, medicine.medicine_id, lastOfMonth(sysdate-30)

from doctor, prescription, medication, medicine

where prescription.issuer=doctor.id and

prescription.prescription_number=

medication.prescription_number and

medication.medicine_id=medicine.medicine_id and

medicine.under_observation=’yes’ and

date_issued between

firstofmonth(sysdate-30) and lastofmonth(sysdate-30)

group by doctor.id, medicine_medicine_id, lastOfMonth(sysdate-30)

having count(*) >= 200;

commit;

5. Describe briefly the concepts functional dependency and Boyce-Codd normal form (8p)