CST 4301 Hospital DB

You have been hired for a summer position by The Ottawa Hospital. After full analysis of the processes in the hospital you identified the following possible entities:

• Patient - a person who is either admitted to the hospital or is registered in an outpatient program. Each patient has a patient number (identifier) and other given fields.
• Physician - A member of the hospital medical staff who may admit patients to the hospital and who may administer medical treatments. Each physician has a physician ID (identifier), name, and other given fields.
• Treatment - any test or procedure performed by a physician on behalf of a patient.

The hospital used an Excel workbook to gather and manipulate data for reporting and other planning. Use the Excel file named "Hospital_DB.xlsx" to create the tables as follows.

A - Create and populate the following tables with the data given in the Excel file named Hospital_DB.xlsx:

• Patient
• Physician
• Treatment

To create and populate the data from the excel sheets; you need to import it as follow:

• From the tab menu choose "EXTERNAL DATA"
• From the ribbon choose "Excel"
• Follow the wizard.

Additional Entries in the Tables:

Add your names as the doctors at the end of the Physician Table, and populate their related fields with make up addresses, phone numbers, etc.

B- Create relationship between the three tables. To do this, follow these steps:

• Select the "database tool" from the tab menu
• Select relationships
• Include all tables in the relationship area
• Build the relation by connecting the primary key to its corresponding foreign key

C- Design the following queries.

Note that the following sample results (Snap shot demo) is run with similar DB and may be not exactly similar to yours.

1. Dispaly all information for a physician, whose physician Id is given by the user ([Enter Physician ID])?

2. Display all the information of patients treated by given physician ID ( [enter Physician ID] ); include the name of the physician in the resulted table.

3. Find the total charges for each patient ID? The table should include total charge and Patient ID.

4. What is the total that is earned by each physician? The table should include total charge and Physician ID.

5. Total_Earned for a selected Physician ?

6. Total charges for a selected patient_ID?

7. Total charges for all patients and show patient information.

D- One form, for the patient (showing Patient first Name, Last Name, Address and Tel Number) table. Be creative in designing the forms. At the minimum, the form should provide an interface to enter a new record, delete a record, search for a record, and navigate among the records.