--MVC Complete Phases – Sample Output

SQL> --file name: mvc_hospital_Complete_Chen_Jason

SQL> --Created by: Jason Chen

SQL> --BMIS 441-01 Row 0

--Phase I.

--Place your ER-D here --

--Place your MVC_ERD2 here (Graphical Relation) --

--Phase II.

-- Place your phase II here (i.e., DROP, CREATE and INSERT SQL)

-- ***************************************************************************

SQL> --Spool of the MVC DROP, CREATE and INSERT (*.txt)

SQL> --***********************************************************************

SQL>*****************************************************************

SQL> --Scripts to create the tables and insert the data

**********************************************************************

SQL> --REVISED version

SQL> --file name: MVC_PhaseII_Chen_Jason.sql

SQL> --this version defines cpk of PT_TREATMENT including three fields

SQL> -- patient_no, physician_id and item_code

SQL> --script to create MVC Hospital Database

SQL> --revised 10/27/2017

SQL> DROP TABLE ITEM_SERVICE CASCADE CONSTRAINTS;

Table dropped.

…

…

…

SQL> DROP TABLE DOCTOR CASCADE CONSTRAINTS;

Table dropped.

-- Just shows you one TABLE and one INSERT (both are partially) as an example

…

…

…

--***** CREATE TABLE *****

CREATE TABLE PT_TREATMENT

(patient_no NUMBER(5),

physician_id NUMBER(4),

item_code NUMBER(4),

num_times_serviced NUMBER(3),

CONSTRAINT PT_TREATMENT_pk PRIMARY KEY (patient_no, physician_id, item_code),

CONSTRAINT …

CONSTRAINT …

CONSTRAINT PT_TREATMENT_item_code_fk FOREIGN KEY (item_code) REFERENCES item(item_code));

--**** INSERT DATA *********

INSERT INTO PT_TREATMENT

VALUES (3249, 4321, 307, 6);

…

…

…

INSERT INTO PT_TREATMENT

VALUES (6251, 2581, 413, 3);

Phase III.

Creating VIEWS

Service charges: room charges (SP, PR), TV, XR, LT, IC

Treatment charges: charged from doctor’s services (X-Ray, Lab-Test)

Merged view: merge two charge views into a single view with a new column title of ‘Charge’

See the following details for creating these three views. The SQL for creating service_charge_view and treatment_charge_view are provided; however, it is responsibility to figure out how to create the merged_charge_view (otherwise, how can you learn from the MVC mini case!).

1. Service_charge_view

patient_no / patient_name / item_code / service_charge

2. Treatment_charge_view

patient_no / patient_name / item_code / treatment_charge

3. Merged_charge_view (merged of two charge views - how?)

patient_no / patient_name / item_code / Charge

(Please note that I split patient_name into two fields: p_last and p_first. However, you may just name one field, patient_name)

SQL> --******************************************************************

SQL> --Spool off the MVC Queries

SQL> --*****************************************************************

SQL> --file name: mvc_hospital_queries_Chen_Jason

SQL> --Created by: Jason Chen

SQL> --BMIS 441-01 Row 0

SQL> --script to query MVC Hospital Database

SQL> --to complete the MVC easier, just need three views:

SQL> --service_charge_view, treatment_charge_view and merged_charged_view

SQL> -- service_charge_view that includes Patient_No and Patient Name

SQL>

CREATE OR REPLACE VIEW service_charge_view (Patient_No,Patient_Name, Item_Code, Service_Charge) AS

SELECT patient.patient_no, patient.p_first|| ' ' ||patient.p_last, item.item_code,

(item_charge*ITS.num_times_serviced)

FROM patient, item, ITEM_SERVICE ITS

WHERE item.item_code = ITS.item_code

AND ITS.patient_no = patient.patient_no

ORDER BY patient.p_last;

View created.

SQL> SELECT * FROM service_charge_view;

PATIENT_NO PATIENT_NAME ITEM_CODE SERVICE_CHARGE

------

3249 Mary Baker 333 10

3249 Mary Baker 200 1750

7325 Marie Kelly 423 240

7325 Marie Kelly 202 2100

6251 Ann Moore 333 10

6251 Ann Moore 202 2450

1837 V.M. Thomas 200 2000

7 rows selected.

-- treatment_charge_view that includes Patient_No and Patient Name

-- You need to create treatment_charge_view given by the example from

-- service_charge_view stated above

CREATE OR REPLACE VIEW treatment_charge_view(Patient_No,Patient_Name, Item_Code, Treatment_Charge) AS

SELECT …

…

FROM patient, doctor, PT_TREATMENT PTT, item

WHERE patient.patient_no = PTT.patient_no

AND …

AND …

ORDER BY patient.p_last;

View created.

SQL> SELECT * FROM treatment_charge_view;

PATIENT_NO PATIENT_NAME ITEM_CODE TREATMENT_CHARGE

------

3249 Mary Baker 307 270

7325 Marie Kelly 307 315

7325 Marie Kelly 413 105

6251 Ann Moore 307 405

6251 Ann Moore 413 105

1837 V.M. Thomas 307 405

6 rows selected.

-- It is your responsibility to figure out for the merged view

CREATE OR REPLACE VIEW merged_charge_view (Patient_No, Patient_Name, Item_Code,Charge) AS

…

…

…

View created.

SQL> SELECT * FROM merged_charge_view;

PATIENT_NO PATIENT_NAME ITEM_CODE CHARGE

------

1837 V.M. Thomas 200 2000

1837 V.M. Thomas 307 405

3249 Mary Baker 200 1750

3249 Mary Baker 307 270

3249 Mary Baker 333 10

6251 Ann Moore 202 2450

… …

7325 Marie Kelly 413 105

7325 Marie Kelly 423 240

13 rows selected.

SQL> --*****************************************************************

SQL> --Queries 1 to 7

SQL> --*****************************************************************

SQL> --Question 1 What patients have been charged more than $300 for item 307?

SQL>

PATIENT_NO PATIENT_NAME CHARGES

------

1837 V.M. Thomas $405.00

6251 Ann Moore ???

7325 Marie Kelly $315.00

SQL> --Question 2: What physicians have not treated Patient No. 3249?

PHYSICIAN_ID physician Name

------

2581 M.D. Walter

SQL>

SQL> --Question 3: How many procedures have been performed on Patient No. 7325? (IC is not included)

PATIENT_NO NUM_OF_TREATMENTS

------

7325 ??

SQL> --Question 4: How many (distinct) physicians have treated Patient No. 7325?

NUM_OF_PHYSICIANS

------

2

SQL> --Question 5: What physicians have treated Patient No. 7325?

PHYSICIAN_ID DOCTOR_NAME

------

2581 M.D. Walter

4321 M.D. Smith

SQL> --Question 6: What is the total charged of the Item 307 and 413 to each patient who has had both items billed to their accounts?

SQL>

PATIENT_NO PATIENT_NAME CHARGES

------

6251 Moore, Ann $510.00

7325 Kelly, Marie $420.00

SQL> --Question 7: What is the total charge for each patient?

PATIENT_NO PATIENT_NAME TOTAL_CHA

------

1837 Thomas, V.M. $2405.00

7325 Kelly, Marie $2760.00

3249 Baker, Mary ???

6251 Moore, Ann $2970.00

SQL> --*************** END OF MVC *****************************

SQL> spool off

MVC Sample Output, Page-4