1 / 13

Patient • Patid Patname Street City State Zip ~ physician

Project ERD. Patient • Patid Patname Street City State Zip ~ physician. Treatment • ~ Patid • ~ Illcode • ~ Drugcode Numdrugs • Treatdate. Physician • Physid Physname Specialty Street City State Zip ~ Supervisor. Illness • Illcode Illname

hedy
Télécharger la présentation

Patient • Patid Patname Street City State Zip ~ physician

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Project ERD Patient •Patid Patname Street City State Zip ~ physician Treatment •~Patid •~Illcode •~Drugcode Numdrugs •Treatdate Physician •Physid Physname Specialty Street City State Zip ~ Supervisor Illness •Illcode Illname Treatcost 1 1 * 1 * * Prescription •Drugcode Patname Drugname Unitcost * 1 * 1

  2. 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. 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: create database medical; use medical; Because I anticipate that you will make mistake, be prepared to start over. You will need to use the statements: drop table treatment; drop table illness; drop table prescription; drop table patient; drop table physician; These can be dropped in any order Why is the order of dropping important???

  3. Project Description NOTE: the order of dropping the tables is the opposite of the order of creating the tables: create table physician; create table illness; create table prescription; create table patient; create table treatment; These can be creates in any order Why is the order of creating important??? Let’s start creating the tables: create table physician (char(9), physnamechar (10), specialty char (14), street char (20) state char (2), zip char (5), primary key (physid), foreign key (supervisor) references physician(physid); Houston, we have some problems!!!

  4. Project Description I would suggest you look at the data for physician. The first record to be inserted is: INSERT INTO physician VALUES ('123456789','Smith, Mary','Cardiology','123 Main St.','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. Let’s create table patient: create table patient ( patidchar(8), patnamechar (20), street char[18), city char(14), state char(2), zip char(5), physician char(8, primary key patid, foreign key (physician) referencesphysician.physid,); There are some mistakes here

  5. Project Description Let’s create table illness: create table ( illcode char(10), illname char(9), unique (illname), treatcost decimal(10,2), primary key (illcode)); Why unique (illname); ??? There is one mistake here, again, based on the data INSERT INTO illness VALUES ('OBA1234567','Broken Left Arm',159.99); (the 159.99 is the cost to treat a Broken Left Arm) Try to create table prescription on your own. The data is given below: INSERT INTO prescription VALUES ('A11223344','Aspirin',6.27); (the 6.27 is the unit cost (per pill) for Aspirin)

  6. Project Description Here is the data for our final table (treatment) INSERT INTO treatment VALUES ('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 I chose the primary key as the concatenation of (patient, illness, drugs, treatment date) Why???

  7. Project Data I have put the data into a Notepad file (which is available to you) • There are 5 physicians • There are 30 patients BUT 1 is a duplicate (delete it when you find it) • Patient also has 1 illegal entry (fix it – don’t delete) • There are 22 illnesses BUT there is 2 illegal entries (fix them – don’t delete) • There are 35 prescriptions BUT there are 3 illegal entries (fix them – don’t delete) • There are 189 treatments BUT there are 2 illegal entries (fix them – don’t delete)

  8. Project Queries Single Table Queries Q1. Get a list of all patient names for Dr. Smith (physid = '123456789’) ordered by patient name. You should get 11 names Q2. Get a list of all physicians whom Dr. Smith (physid = '123456789’) supervises ordered by physician name. There should be 2 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 cost You should get 1 item; the number should be 30; the max should be 45171, the min 1.99; the average 2736.295333 Q7. Get the same list of all drug names, drug unit costs ordered by drug cost (highest to lowest) You should get 35 items.

  9. Project Queries Q8. Get the number of drugs, maximum drug cost, minimum drug cost, and drug illness cost (just as you did for Q7 relative to Q6) 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 Q9. Find out how many treatments Beethoven, L.V. (patid = '221100998‘) was seen (label count as as ‘Treatments’) You should get 7 Two Table 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’s physid. 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 in Q1 . Q11. Get a list of all patient names, grouped by physname, patname for Dr. Smith’ AS WELL AS for all the physicians Dr. Smith (physid = '123456789’) supervises. You should get 87 names. Multiple Table Queries 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.

  10. 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 alphabetically You 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 prescribed Codeine‘ 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).

  11. Project Queries Q17. Similar to the previous list, get list of the number of treatments, 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 by any 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 output • Instead of finding ‘Treat $’ as: selectsum(treatcost) • I used select format(sum(treatcost),2)

  12. Project Queries Q19. Similar to the previous list, get list of the number of treatment $, number of drugs prescribed, total revenue from all treatments, drugs prescribed, overall total $, and % of total revenues that each physician 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 hospital generated $454,725 in revenues. Divide the total revenues generated by each physician 454725.

More Related