I'm facing a problem understanding a part of the case we've been asked to draw a diagram about .
This is the question :
Patients are identified by SSN, and for each patient, the name and address is recorded. Doctors are identified by SSN, and for each doctor, the name, specialty and years of experience must be recorded. Each pharmaceutical company is identified by name and has a phone number. For each drug, the trade name and formula must be recorded. Each drug is sold by a given pharmaceutical company, and the trade name identifies the drug uniquely from among the products of that company. Each pharmacy has a name, address, and phone number. Every patient has a primary
physician. Every doctor has at least one patient. 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. Doctors prescribe drugs for patients. A doctor could prescribe one or more drugs for several patients, and a patient could obtain prescriptions from several doctors. Each prescription has a date and a quantity associated with it. You can assume that if a doctor prescribes the same drug for the same patient more than once, only the last such prescription needs to be stored.[u]Pharmaceutical companies have long-term contracts with pharmacies. A pharmacy can contract with several pharmaceutical companies. For each contract, a start date, an end date, and the text of the contract are given. Pharmacies appoint a supervisor for each contract. There must always be a supervisor for each contract.
I have underlined the part that i didnt understand , and this is the question :
How would your design change if the specification changes as follows: If a doctor prescribes the same drug for the same patient more than once, several such prescriptions may have to be stored separately.
how would this affect my diagram ? Do i have to create another relation or make an attribute to the "prescribed drugs " relation between the Doctor and Patient ?
I have draw the diagram and it is attached but i'm not quiet sure about its accuracy .
I hope i can find some explanation here
thanks in advance
This post has been edited by dreamygirl: 09 March 2009 - 11:33 AM