in pharmacies in Turkey, it was decided to use a shared database. You have been assigned to design a database. You are given the following information: Patients are identified by their personal number and you need a record of their first name, last name, address and age. Each patient is assigned a primary doctor (family doctor). Doctors check the patients. They are identified by their registry numbers. You also need their first name, surname, specialty and years of experience. Every doctor has at least one patient. Doctors prescribe medication for patients. A doctor may prescribe one or more medications for several patients, and a patient may receive prescriptions from several doctors. Each prescription has a date and an amount associated with it. If a doctor prescribes a medicine more than once for a patient, all prescriptions must be kept. Each pharmaceutical company is identified by name and has a telephone number. Trade name and formula should be recorded for each drug. Each drug is sold by a specific pharmaceutical company, and the trade name uniquely identifies a drug amongst that company's other products. If the pharmaceutical company is deleted from the database, you no longer need to track the product line. Each pharmacy has a name, address and telephone number. Each pharmacy sells several medicines, and each has a price. A medicine can be sold in several pharmacies and its price may vary from pharmacy to pharmacy. Pharmaceutical companies have long-term contracts with pharmacies. Pharmaceutical companies can contract with several pharmacies. A pharmacy can also contract with several pharmaceutical companies. For each contract, you need to record a start date, end date and contract text. Pharmacies appoint a controller for each contract. There should be a controller for each contract, but the controller may change over the duration of the contract. Question 1. Draw an Entity-relationship diagram containing the information given above? Question 2. Convert the Entity-Relationship Diagram you have drawn into tables? Question 3. Suppose that each drug should be sold by all pharmacies at a fixed price. So how should you change your design? Explain. Question 4. If the requirement for prescriptions changed as follows: "If a doctor prescribes a drug more than once for a patient, only the last prescription has to be kept". So how should you change your design? Explain.
in pharmacies in Turkey, it was decided to use a shared
Patients are identified by their personal number and you need a record of their first name, last name, address and age. Each patient is assigned a primary doctor (family doctor).
Doctors check the patients. They are identified by their registry numbers. You also need their first name, surname, specialty and years of experience. Every doctor has at least one patient. Doctors prescribe medication for patients. A doctor may prescribe one or more medications for several patients, and a patient may receive prescriptions from several doctors. Each prescription has a date and an amount associated with it. If a doctor prescribes a medicine more than once for a patient, all prescriptions must be kept.
Each pharmaceutical company is identified by name and has a telephone number. Trade name and formula should be recorded for each drug. Each drug is sold by a specific pharmaceutical company, and the trade name uniquely identifies a drug amongst that company's other products. If the pharmaceutical company is deleted from the database, you no longer need to track the product line.
Each pharmacy has a name, address and telephone number. Each pharmacy sells several medicines, and each has a price. A medicine can be sold in several pharmacies and its price may vary from pharmacy to pharmacy.
Pharmaceutical companies have long-term contracts with pharmacies. Pharmaceutical companies can contract with several pharmacies. A pharmacy can also contract with several pharmaceutical companies. For each contract, you need to record a start date, end date and contract text. Pharmacies appoint a controller for each contract. There should be a controller for each contract, but the controller may change over the duration of the contract.
Question 1. Draw an Entity-relationship diagram containing the information given above?
Question 2. Convert the Entity-Relationship Diagram you have drawn into tables?
Question 3. Suppose that each drug should be sold by all pharmacies at a fixed price. So how should you change your design? Explain.
Question 4. If the requirement for prescriptions changed as follows: "If a doctor prescribes a drug more than once for a patient, only the last prescription has to be kept". So how should you change your design? Explain.
Trending now
This is a popular solution!
Step by step
Solved in 6 steps with 3 images
