CS348 Project 3 ( Part 1 )

Hospital Information System

Part 1 Outline
In this part of the project, you will be designing an API (Application Programming Interface) to query an XML-based Health Care Record database. The database consists of XML documents for patients and policies. The patient document contains the demographic information for patients, and patient history. The policy documents contain policies related to the privacy and authorization of patient records. Privacy policies are supplied by the patient and the authorization policies are supplied by the hospital, and the purpose of both policies is to restrict the access of physicians to patient records based on a set of rules.

Part 1 Documents
The following are the XML schemas for the documents used in the project:

1. Schema for Patient Document
2. Schema for Privacy Policy Document
3. Schema for Authorization Policy Document

These are the only three documents you will need to design the API for this project.

Prerequisites
In this part of the project, you will need to have knowledge of the following:

• Oracle XML SQL Utility for PL/SQL
• XPath

Database Setup
Your database will be setup as follows:

1. One table corresponding to the Patients schema. It will store patient instance documents. Name this table as prTable.                                       

prTable (patientRecord XMLType)

2. One table corresponding to the Privacy Policy schema. It will store privacy policy instance documents. Name this table as privacyPolicyTable.

privacyPolicyTable (privacy XMLType)

3. One table corresponding to the Authorization Policy schema. It will store authorization policy instance documents. Name this table as authPolicyTable.

authPolicyTable (auth XMLType)

Note : Date format will be ‘YYYY MM DD’ for this project.

Instructions
The API will comprise of a set of procedure calls. Each procedure call will invoke a SQL query involving an XPath syntax to query XML data and will print values of the matching elements in the XML documents. The XPath expressions will be designed to act on XML documents conforming to the XML schemas provided to you. Therefore your queries will be evaluated against any set of XML instances conforming to these schemas. For your practice, some information about how to do queries with XML instances is provided in the "Resources" section below. You must, however, test your queries on the various instances possible for the given schemas.

Deliverables
Your API should include the following procedure calls:

The first two calls are based on the authorization policy of the physicians defined according to the Authorization Policy schema. Note that the authorization policy schema allows one to state that a physician is authorized to view the record if:

• the record was created after a date X
• the diagnosis was of a kind Y

Using this policy and the provided schema, answer the following queries:

   1     GetNumAuthorizedRecords(p_id):Prints the number of records that a physician with an id p_id is authorized to view.

2. GetMostAuthorizedPhysician():Prints the id of the physician(s) authorized to view the most number of records.

The next two calls are based on the privacy policiy of the patients defined according to the Privacy Policy schema. Note that the privacy policy allows one to state that a patient permits a physician to view the records of the patient if:

• the physician has seen atleast one patient since a date X

 (treat the date a patient was seen as the date when the record was created)

• the physician has seen atleast Y patients overall

Using this policy and the provided schema, answer the following queries:

3. GetPermittedPhysician(p_id):Prints the id of the physician(s) permitted to view the records of a patient with an id p_id.

4. GetLeastConditionPatient():Prints the name of the patient who permits a physician with the least "miminum patients seen" requirement. (In other words, the privacy policy of this patient has a value of the condition that is least among all the patients' privacy policies.)

The final query combines both policies:

5. GetAllowedRecords(p_id,d_id):Prints the id of the record(s) of a patient with an id p_id that a physician with an id d_id is both authorized and permitted to view.

Resources
You can create your own XML instances to practice the queries that you have designed. Note that the "id" attribute must be unique for each patient, record and physician in the instance documents that you create. You will need to setup a procedure to store your XML instances in a database table as an XMLType column. A very simple set of instance documents for the respective schemas are provided for your practice as follows:

• patients p1 and p2
• privacy policy of patients p1 and p2
• authorization policy of physicians d1 and d2

Sample procedure to populate your data base can be found here

Submission
You will submit following files.

• Lab3.sql containing all the queries. Make sure that you print query number as well along with the output.
• Dropall.sql to drop all the tables procedures or functions (if any).

When you are ready to submit part 1 of your project, you should have a directory PRJ3P1 where your files are. Go to the directory which contains the directory PRJ3P1, run the following UNIX command

· turnin -c cs348 -p proj3p1 PRJ3P1

Your whole directory will be submitted for grading. You can check the submitted files with

· turnin -c cs348 -p proj3p1 -v 

TO GO TO PROJECT3 PART2 PLEASE CLICK ME

GOOD LUCK !