CS336 Principles of Database Management Systems
Problem Set 2

Due: February 28, 2002

1.

The following relations are part of the billing database for a hospital (primary key is underlined):

• Patient(PatientID,Name,Address,InsName)
• Doctor(EmpID,DocName,SuperID)
• Insurer(InsName,InsAddress)
• Treatment(PatientID,EmpID,TCode,Date)
• Fee(TCode, TDesc, Fee)

For each of the following, write queries in all of: (1) the Relational Algebra; (2) The Tuple Relational Calculus; (3) SQL.

(a)

Write a query that returns the patient ID and address of patient ``Bob Smith''.

(b)

Write a query that returns the names of all patients insured by ``HMO Black''.

(c)

Write a query that returns the names of all doctors supervised by Dr. Phoux. (The SuperID is the EmpID of a doctor's supervisor.)

(d)

Write a query that returns the names of patients treated by Dr. Phoux in August, 2000 who were not treated by Dr. Barre in July, 2000. (For the RA and TRC, assume you can write a predicate to test a date for month and year)

(e)

Write a query to report the PatientIDs of any patients that have been treated by every doctor in the database at any time.

(f)

Write a query that returns the names of all Physicians who head departments (that is, they supervise supervisors).

(g)

Write a query that returns the description of the least expensive treatment(s).

2.

(a)

Write an SQL query to report the name of the physician responsible for the greatest total billing revenue.

(b)

Why can't the preceding query be written in the Relational Algebra or the Tuple Relational Calculus (as presented in class)?

About this document ...

This document was generated using the LaTeX2HTML translator Version 98.1p1 release (March 2nd, 1998)

Copyright © 1993, 1994, 1995, 1996, 1997, Nikos Drakos, Computer Based Learning Unit, University of Leeds.

The command line arguments were:
latex2html -no_navigation -show_section_numbers -split 0 hw2-spr02.tex.

The translation was initiated by Jack Keane on 2002-02-19