Toad World® Forums

build an E-R diagram, i've a problem in the relations


I am beginner to learn it (build an E-R diagram), can you help me??

first,I’ve put 6 Entity types :(pharmacy + medicine+ medical company +contracts +supervisor + Consumers )

but, I do not know how to set a relationship do not know what?

We have been asked to analyze the data requirements to introduce a new service related to pharmacies in Bahrain, and thus the information will be public for both consumers and retailers.The database should include information about each pharmacy in USA. Each pharmacy has a unique name, address, and telephone number(s). Each pharmacy sells several medicines and has a price for each. A medicine could be sold at several pharmacies, and the price could vary from one pharmacy to another.For each medicine, we need to record the trade name; formula and the details of the medical company produced this medicine. The trade name identifies a medicine uniquely among the products of a given company. Each medical company has a unique name, unique telephone number, and country.A medicine can be improved over time to new advanced medicines. For each medicine being modified to a new one, the database should keep a record of the new trade name, date of modification, and the lab name of the medical company improved the medicine. A medicine can be improved by any medical company, not necessarily the owner companyEach pharmacy has several long-term contracts with different medical companies. For each contract, the start date, end date, pharmacy manager’s name and the text of the contract should be documented. In case, a medical company, for any reason, discontinued producing medical medicines, the company and its related products and contracts should be marked as ‘obsolete’ in the database. This should help the consumers to save their time in searching for non-existing medicines.Pharmacies need to keep track of their contracts, and thus pharmacies appoint a supervisor for each contract. Foe each supervisor, we need supervisor’s name and CPR. There must be always a supervisor for each contract, but the contract supervisor can change over the lifetime of the contract.Consumers should be able to search the database for any medicine in any pharmacy in the country, compare the medicine prices offered in different pharmacies and make orders of medicines. Once a consumer orders a medicine from a pharmacy, we need to record the order ID (unique), the CPR, name and telephone number of the consumer, the email of the pharmacy, and the date, quantity and the total price of the medicine being ordered. Each order should belong to exactly one customer, one pharmacy and for one medicine.

Message was edited by: vaclav


I think I can only recommend you to try to read some book about database modeling or tutorial. This would be the right starting point.

Have a great day and good luck,