The Prescriptions-R-X chain of pharmacies has offered to give you a free lifetime supply of medicine...

 

Transcribed Image Text:

The Prescriptions-R-X chain of pharmacies has offered to give you a free lifetime supply of medicine if you could fix their database design shown in the following figure. To fix this design you need to answer (in English, there is no need to re-draw the diagram) 1. In the current design, it says: "Each pharmacy sells several drugs and has a price for each. A drug could be sold at several pharmacies, and the price could vary from one pharmacy to another". How would this design change if each drug must be sold at a fixed price by all pharmacies? 2. In the current design, it assumed that, if a doctor prescribes the same drug for the same patient more than once, only the last such prescription needs to be stored. How would this design change if the design requirements change as follows: If a doctor prescribes the same drug for the same patient more than once, several such prescriptions may have to be stored. name 0000 start_date end_date text supervisor ssn age address Pharmacy Contract name Patient address phone_num Sell price Pri_physician Prescription Drug Make Pharm co name phy_ssn name Doctor date quentity trade_name ----- formula phone_num speciality exp_years The Prescriptions-R-X chain of pharmacies has offered to give you a free lifetime supply of medicine if you could fix their database design shown in the following figure. To fix this design you need to answer (in English, there is no need to re-draw the diagram) 1. In the current design, it says: "Each pharmacy sells several drugs and has a price for each. A drug could be sold at several pharmacies, and the price could vary from one pharmacy to another". How would this design change if each drug must be sold at a fixed price by all pharmacies? 2. In the current design, it assumed that, if a doctor prescribes the same drug for the same patient more than once, only the last such prescription needs to be stored. How would this design change if the design requirements change as follows: If a doctor prescribes the same drug for the same patient more than once, several such prescriptions may have to be stored. name 0000 start_date end_date text supervisor ssn age address Pharmacy Contract name Patient address phone_num Sell price Pri_physician Prescription Drug Make Pharm co name phy_ssn name Doctor date quentity trade_name ----- formula phone_num speciality exp_years