Patient•PatidPatnameStreetCityStateZip~physician
Treatment•~Patid•~Illcode•~DrugcodeNumdrugs•Treatdate
Physician•PhysidPhysnameSpecialtyStreetCityStateZip~Supervisor
1
*
1
*
1
*
Illness•IllcodeIllnameTreatcost
Prescription•DrugcodePatnameDrugnameUnitcost
1
1
*
*
ProjectERD
Project Description
NOTE:If you follow my instructions (Please do) you will make a number of errors (I am a firm believer that all of us learn the most through our mistakes). All of these are easily corrected.
Because I anticipate that you will make mistake, be prepared to start over. You will need to use the statements:
First, you should probably need to create, and use, a new database. I created, a used, a database called medical, although you can create/use any database you want:
drop tabletreatment;drop tableillness;drop tableprescription;drop tablepatient;drop tablephysician;
create databasemedical;usemedical;
These can be dropped in any order
Why is the order of dropping important???
Project Description
NOTE:the order of dropping the tables is the opposite of the order of creating the tables:
create tablephysician;create tableillness;create tableprescription;create tablepatient;create tabletreatment;
These can be creates in any order
Why is the order of creating important???
Let’s start creating the tables:
create tablephysician(char(9),physnamechar(10),specialtychar(14),streetchar(20)statechar(2),zipchar(5),primary key(physid),foreign key(supervisor)referencesphysician(physid);
Houston, we have some problems!!!
Project Description
I would suggest you look at the data for physician. The first record to be inserted is:
INSERT INTOphysicianVALUES('123456789','Smith,Mary','Cardiology','123MainSt.','Arlington','TX','76019',null);
How does this compare with your table???
I have said that the order of the rows an the order of the columns doesn’t matter, they do matter with respect to how the data are laid out.
create tablepatient(patidchar(8),patnamechar(20),streetchar[18),citychar(14),statechar(2),zipchar(5),physicianchar(8,primary keypatid,foreign key(physician)referencesphysician.physid,);
Let’s create table patient:
There are some mistakes here
Project Description
create table(illcodechar(10),illnamechar(9),unique (illname),treatcostdecimal(10,2),primary key (illcode));
Let’s create table illness:
Whyunique(illname); ???
INSERT INTOillnessVALUES('OBA1234567','Broken LeftArm',159.99);
Try to create table prescription on your own. The data is given below:
INSERT INTOprescriptionVALUES('A11223344','Aspirin',6.27);
There is one mistake here, again, based on the data
(the159.99is the cost to treat aBrokenLeft Arm)
(the6.27is the unit cost (per pill) forAspirin)
Project Description
Here is the data for our final table (treatment)
INSERT INTOtreatmentVALUES('443322110','OPT7654321','A33445566',2,'2012-5-29');
The first entry is the patient
The second entry is the illness
The third entry is drugs given
The fourth entry is the number of drugs ordered
The fifth entry is the treatment date
Why???
I chose the primary key as the concatenation of (patient, illness, drugs, treatment date)
Project Data
I have put the data into aNotepadfile (which is available to you)
There are 5 physicians
There are 30 patientsBUT1 is a duplicate (delete it when you find it)
There are 22 illnessesBUTthere is 2 illegal entries (fix them – don’t delete)
There are 35 prescriptionsBUTthere are 3 illegal entries (fix them – don’t delete)
There are 189 treatmentsBUTthere are 2 illegal entries (fix them – don’t delete)
Patient also has 1 illegal entry (fix it – don’t delete)
Project Queries
Q1. Get a list of all patient names for Dr. Smith (physid= '123456789’) ordered by patient name.You should get 11 names
Q3. Get a list of all patient names, addresses (street, city, state) grouped by city, patient name who live in ‘TX’.You should get 6 names.
Q4. Get a list of all patient names, addresses (street, city, state) grouped by state, city, patient name who live in ‘TX’, ‘NY’, or ‘CA’You should get 15 names.
Q5. Get a list of illness names, illness costs ordered by illness cost (highest to lowest)You should get 30 items
Q6. Get the number of illnesses, maximum illness cost, minimum illness cost, and average illness costYou should get 1 item; the number should be 30; the max should be 45171, the min 1.99; the average 2736.295333
Single Table Queries
Q7. Get the same list of all drug names, drug unit costs ordered by drug cost (highest to lowest)You should get 35 items.
Q2. Get a list of all physicians whom Dr. Smith (physid= '123456789’) supervises ordered by physician name.There should be 2
Project Queries
Q10. Get the same list of all patient names, in descending order of patient name, for Dr. Smith. This time you don’t know Dr. Smith’sphysid. You only that her name is 'Smith, Mary‘, which is found only in table physician.You should get the same number of names as you did inQ1.
Q11. Get a list of all patient names, grouped byphysname,patnamefor Dr. Smith’ AS WELL AS for all the physicians Dr. Smith (physid= '123456789’) supervises.You should get 87 names.
Two Table Queries
Q9. Find out how many treatments Beethoven,L.V. (patid= '221100998‘) was seen (label count as as ‘Treatments’)You should get 7
Multiple Table Queries
Q8. Get the number of drugs, maximum drug cost, minimum drug cost, and drug illness cost (just as you did forQ7relative toQ6) BUT make your headings appear as ‘Number’, ‘Most Expensive’, “Cheapest’, and ‘Average Cost’You should get 1 item; the number should be 35; the max should be 2000.99, the min 2.34; the average 82.281714
Q12. Get a list of all the drugs, and when they were prescribed, that 'Smith, Mary‘ has ordered for her patients, ordered alphabetically by date prescribed.You should get 86.
Project Queries
Q13. You will notice that the previous query produced a number of duplications. Modify your to get a list of unique drug names (don’t worry about treatment dates).You should get 21.
Q14. Get a list of all the unique illnesses that ‘Smith, Mary’ has treated alphabeticallyYou should get 15.
Q15.Get a list of physicians (by name) supervised by Dr. Smith (ID = '123456789') and how many times each of them have prescribedCodeine‘ or'Percodan' to their patients.You should find that of the two physicians, one prescribed them 7 times, and the other 6
Q16.Get a list of all patient costs. The output should resemble the following list:
This is only for 1 person; you should have a list of 189 records. The list is ordered by Patient Name (ascending), and date (descending).
Project Queries
Q17.Similar to the previous list, get list of the numberoftreatments, number of drugs, total cost of all treatments, drugs prescribed, and overall total costs for each patient. For clarification sake, I produced the following table:
This table contains the total costs for only 2 people; your list will have a record for each of the 29 patients. Notice that I have ordered the output by Total Costs (descending). The lowest amount paid byany person was$501.72
Q18.Let’s look at the situation from the hospital’s perspective. They are interested in the overall revenues generated. Your output should appear as:
A quick aside: I don’t care about the formatting, but if you are interested in knowing how I was able to get the above outputInstead of finding ‘Treat $’ as:selectsum(treatcost)I usedselectformat(sum(treatcost),2)
Project Queries
Q19.Similar to the previous list, get list of the numberoftreatment $, number of drugs prescribed, total revenue from all treatments, drugs prescribed, overall total $, and % of total revenues thateachphysician produced. For clarification sake, I produced the following table:
This table contains the total revenues produced by 2 physicians; we know there are 5. Notice that I have ordered the output by % Total Costs (descending).For the ‘% Total Revs’:From the previous query, we know that the hospitalgenerated$454,725 in revenues. Divide the total revenues generated by each physician 454725.
0
Embed
Upload