Hospital Billing System Erd

Only available on StudyMode
  • Download(s) : 957
  • Published : March 9, 2011
Open Document
Text Preview
HOSPITAL BILLING SYSTEM

[pic]

TASKS

1. Create Patient and Patient Account tables. Please bear in mind that you will be additionally tested for deciding on sensible data types and field sizes. (Assuming that all other tables are already created).

2. (a) Create a sequence for Patient’s ID which should start from 1000, should increase in steps of 1, and should not exceed the value 99999. (b) Insert a sensible record in Patient’s table by using the sequence created in (a) within an anonymous block.

3. Using PL/SQL structure, list the name of Insurance Company in which the patient ‘Eric Forrester’ is registered as a policy holder.

4. Write an anonymous block that will increase the fees of all the procedures by a given percentage. This value should be entered from the keyboard.

5. List the patient’s names along with names of the patient’s physicians, but list only those physicians whose name begin with ‘K’ & end with ‘M’, and those patients who see dermatologists, psychologists, and ophthalmologists. Ensure that your query contains all these conditions.

6. Create Procedure and Patient Bill tables. Please keep in mind that you will be additionally tested for deciding on sensible data types and field sizes. (Assuming that all other tables are already created).

7. (a) Create a view that should list the basic details of the Patient along with his basic Insurance details. (b) List this view in an anonymous block but showing merely the details of ‘Erin Brokovich’ in a good and understandable format.

8. (a) Add constraints that the Patient’s name should be unique & not null. (b) Add a constraint that Fee should not be less than US$100 and can be null.

9. Write up an anonymous block showing the Patient name and his/her total Fees. The patient’s name should be provided from the keyboard.

10. List the patient’s names along with names of the patient’s physicians, but list only those physicians who have letters ‘O’ & ‘U’ in the 3rd & 4th position respectively of their names, and those patients who do not see dentists & psychiatrists. Ensure that your query contains all these conditions, and that your output should be in capital letters and in descending order of patients’ names.

11. Create Insurance Company and Insurance Claim tables. Please keep in mind that you will be additionally tested for deciding on sensible data types and field sizes. (Assuming that all other tables are already created).

12. Write up an anonymous block showing the Patient’s name and the total amount payable against his/her bill. The patient’s name should be provided from the keyboard.

13. Code an anonymous block in such a way that should get the patient’s name and then ask for adding some medical information to the medical history; commit your work. When this is processed, the “DATA RECORDED” message should be displayed.

14. (a) Create a Synonym for the Physician’s table.
(b) In a PL/SQL structure, change the specialty of ‘Dr.Dumdum’ with that what the user would want to enter. You will have to use the synonym that you created in (a).

15. (a) List the total fee of the procedure that a physician undertakes. The physician name should be provided from the user. (b) How many physicians are on the system that practice Dentistry, Pathology & Cardiology? Your output should be displayed using sensible aliases.

SOLUTION

1.
Create table Patient ( Pid Number(10) PRIMARY KEY, Name Char(25), Medical_History Varchar2(500), Drug_History Varchar2(500) );

Create table Patient_Account (Pid Number(10) PRIMARY KEY, constraint patient_acc_pid_fk FOREIGN KEY (Pid) REFERENCES Patient(Pid) );

2.
(a)Create Sequence patient_seq
Start with 1000
Increment by 1
Maxvalue 99999;
b) BEGIN
Insert into Patient (Pid, Name, Medical_History, Drug_History) Values (patient_seq.nextval, ‘Micheal Smith’, ‘Chicken Pox’,...
tracking img