Create table INS ( INS_CODE char(5), INS_NAME char(30), INS_ADDRESS char(30), INS_CITY char(30), INS_STATE char(2), INS_ZIP char(5), CONSTRAINT INS_PK PRIMARY KEY(INS_CODE)); CREATE TABLE PATIENTS (P_CODE CHAR(5), P_NAME CHAR(30), P_ADDRESS CHAR(30), P_CITY CHAR(30), P_STATE CHAR(2), P_ZIP CHAR(5), INS_CODE CHAR(5), CONSTRAINT PATIENTS_PK PRIMARY KEY (P_CODE), CONSTRAINT PATIENTS_FK FOREIGN KEY (INS_CODE) REFERENCES INS (INS_CODE)); CREATE TABLE MDS (MDS_CODE CHAR(5), MDS_NAME CHAR(30), MDS_ADDRESS CHAR(30), MDS_CITY CHAR(15), MDS_STATE CHAR(2), MDS_ZIP CHAR(5), CONSTRAINT MDS_PK PRIMARY KEY (MDS_CODE)); CREATE TABLE MEDICAL (MP_CODE CHAR(5), MP_DESCRIPTION CHAR(30), MP_PRICE DECIMAL(3,2), CONSTRAINT MEDICAL_PK PRIMARY KEY(MP_CODE)); CREATE TABLE TREATMENTS (P_CODE CHAR(5), MD_CODE CHAR(5), MP_CODE CHAR(5), DOT DATE, CONSTRAINT TREATMENT_FK FOREIGN KEY (P_CODE) REFERENCES PATIENTS (P_CODE), CONSTRAINT TREATMENT_FK1 FOREIGN KEY (MD_CODE) REFERENCES MDS(MDS_CODE), CONSTRAINT TREATMENT_FK2 FOREIGN KEY (MP_CODE) REFERENCES MEDICAL (MP_CODE)); 

Part I

1. Create a Table called INS containing the following:

Field name	Field properties
INS code	5
Insurance name	30
Address	30
City	30
State	2
Zipcode	5

Add the following records to the table:

Code	description
GHI	Group Health Insurance
BC	Blue Cross
AHS	American Health System

2. Create a Table called Patients containing information regarding patients in a Hospital:

Field Name	Field Property
Code	Character 5
Name	Character 30
Ins company Code	Character 5	Must be from INS table
Address line 1	Character 30
Address line 2	Character 30
City	Character 15
State	Character 2
Zip	Character 5

Add the following records to the table:

Code	Name:	Ins
P1	Frank (your last name)	GHI
P2	Rosi (your last name)	BC
P3	Alex (your last name)	BC
P4	Albert (your last name)	BC
P5	Sue (your last name)	GHI
P6	Richard (your last name)	AHS

3. Create a Table called MDS containing information regarding doctors and dentists:

Field Name	Field Property
Code	Character 5
Name	Character 30
Address	Character 30
City	Character 15
State	Character 2
Zip	Character 5

Add the following records to the table:

Code	Name	Address	(make up the rest of the data)
M1	Dr. Anthony (your last name)
M2	Dr. Jack (your last name)
M3	Dr. Jane (your last name)
M4	Dr. Louis (your last name)
M5	Dr. Kate (your last name)

4. Create a Table called Medical containing the following:

Field name	Field properties
Mp code	5
description	30
Price	Numeric with 2 decimal places

Add the following records to the table:

Code	description	price
1111	Throat culture	1.11
2222	Routine blood test	2.22
3333	Ingrown toenail removal	3.33
4444	Fill a cavity	4.44
5555	Dental x-ray	5.55
6666	Dental cleaning	6.66
7777	Nose bleed treatment	7.77
8888	Splinter removal	8.88

5. Create a Table called Treatments containing information regarding treatments received by patients.

Field name	Field properties
Patient code	5
Md code	5
Mp code	5
Date of treatment	date

6. You cannot have the same treatment twice in a given date for a specific patient

7. A patient can have the same medical procedure on different days.

8. There are 6 patients. The Patient codes are: P1, P2, P3, P4, P5, P6

9. There are 5 Doctors. The Doctor codes are: M1, M2, M3, M4, M5

For Patient P1	Add 2 treatments from one MD
For Patient P2	Add 5 treatments from 2 different MDs
For Patient P3	Add no treatments
For Patient P4	Add 4 treatments for one MD. Two of the treatments must be the same But on different days
For Patient P5	Add 5 treatments from 3 different MDs
For Patient P6	Add 1 treatment

Part II

Create a Query for each of the following questions and print out the result.

Query 1.- Print all INS records for State = NY

Query 2.- Print the cpt (Mp_code) record that has the highest price

Query 3.-Print the name of the patient that has the most treatments (# of records in the treatments file) Query 4.- Print the name of the patient that cost the most money (i.e. sum of all treatments costs was highest)

Query 5.- Print the name of the patient that did not have treatment 11111

Query 6.- Print the name of the INS company that had to pay the most money.

Query 7.- Print the name of the MD that prescribed the treatments that cost the most money.

Part III - Extra Credit ( 5 points Web pages connected to the database)

1. Generate the following report for all patients one report per patient.

Name of the patient

Address of the patient

Date of service name of md cpt cpt description price

Total

2. Generate the following report for all MDs one report per MD

Name of MD

Address of MD

Date of service name of patient cpt cpt-description		price
Total This report should be in DATE order- subtotaling after each date. 3. Generate the following report for all CPTs one report per cpt CPT code and description
Date of service name of patient name of MD		Price
Total

This report should be in md order subtotaling after each md.

4. Generate the following report for all INSURANCE companies one report per INS

Insurance Company name

Insurance Company address

Date of service name of patient name of MD cpt cpt description price