----------------------------------------
Top 5 query results:

Get the results table in a sorted order and programmatically return
the top 5.
---------------------------
Aggregate functions:

You can not combine multiple aggregate functions like  gsum (gsum(A)(table)).

The right way to do it is to assign the first sum to a table and then
use that in the second sum.

Each aggregate function results in a relation with the attribute on which
the aggregate function is performed, and any group by attribute. For
example,
Table1 = group by C gsum(A) as sumA (Table2)
Table3 = group by C gsum(B)as sumB (Table2)

Select (sumA/sumB) as fraction
From Table1, Table3
Where fraction > 0.5

You have to create Table1 and Table3 as Views.
---------------------------------
Query reports

By diagnosis, you can take it to mean, cancer, heart disease..

For Q1, you need to get the bill for any patient -- so using
your interface, I specify a patient's name -- your system should
respond with an account summary for that patient.
---------------------------------
User Interface and Basic Functions

> Dear Dr. Mundur,
>     I have a question about the Databases Project GUI.  What exactly is
> the function of the GUI.  Should the GUI be more or a admin for the
> database or a user end that they would actually use in the hositpal.  I
> guess what im trying to say is should we assume that some hositpal nurse
> will use the GUI to access the database, or someone with more techincal
> knowledge.  The project description says that we should demostrate the
> basic functionalities of the system.  What exactly are the basic
> functions?  Ability to add patients and visits and tests?  or Just view
> the tables?  We are just a little confused on what the GUI should do.

The interface (text-based or GUI) will collect the input to a query
from users (they could be nurses, doctors).
For instance, if I want to see all students in CMSC461-0101 using
MyUMBC, I will use the form interface and indicate the semester, course
number, section number and so on and it will display the table containing
the student data for that class. What I put in the form interface is the
input to my SQL query. Your JAVA frontend should provide an interface to
collect appropriate input which will be used to execute a query using
JDBC and Oracle and then display the answer to that query.
The interface may contain a list of options, each for executing a basic
function of the system.

The list of basic functions can be put together from the System Description (1st paragraph
of the Project Description). For instance, the following list gives a few of those:
1 admit a new patient and assign a bed (results in inserting data
to one or more tables)
2 Discharge a current patient (results in deleting data)
3 Assign a doctor and a nurse to a newly admitted patient
4 Get billing information for a patient
5 Get labtest info for a patient
(this list is not exhaustive)

Looking at the list, you can develop this interface for two main types
of users -- hospital administrators (1,2,3,4) and medical staff (5).
In generating the list of basic functions, think about how the users will
use this system based on their information needs; the 5 query reports can
also be set up as menu options on your interface.

-------------------------