Database Design Project: Draw the initial Entity-Relationship Diagram or Databas

Question

Database Design Project:

Draw the initial Entity-Relationship Diagram or Database schema for the database specified below and normalize the said Diagram and give the final Tables needed for the Database

and perform the Queries at the end to test your Database

in this project we're desinging a database to model the information about the patients, doctors, pharmacies, and drug manufacturers and the connections between them.

the information below must be available in your database:

-- the patiants are identified by a national code and their name,address and age are registered.

-- the doctors are identified by a national code and their name,specialization, and years of service are registered.

-- each pharmaceutical company is identified by its name and hast a phone number registered aswell.

-- for each drug the brand and formula hast to be registered.each drug is sold by a its own pharmaceutical company and its brand is what identifies it from the other drugs that

company sells.if a company is liquidated, theres no need to store that companies drug information anymore.

-- each pharmacy has a name, address and a phone number registered,

-- each patient has only one doctor.

-- each pharmacy sells different drugs and has a price for each one.each unique drug can be sold in many pharmacies and have a different price in each pharmacy store.

-- doctors prescribe prescriptions for the patients. a doctor can prescribe one or many drugs for a patient and each prescription has a date and the amount of drugs prescribed.

-- pharmaceutical companies have long term contracts with pharmacies. a pharmaceutical company can have contracts with multiple pharmacies, also a pharmacy can have contracts with multiple pharmaceutical companies. for each contract there must be a start date, end date, and contract text registered.

QUERIES for the designed Database:

1.total number of drugs A that have been prescribed by doctor B for patiants of age 40 and above.

2.the avarage age of the patiants who have been prescribed drugs made by the pharmaceutical company A.

3.the list of drugs cheaper than $1000 that have not been prescribed for any patiants yet.

4.list of doctors who, in the past month, have prescribed prescriptions with a total price greater than $10000.

5.the name of all of the pharmaceutical companies and the number of contracts they've closed till now.

6.list of all the doctors who have never prescribed the drug A in any of their prescriptions.

Explanation / Answer

The database schema include various relations(table) and its being given below:

1) patiant_table(id, name, address, age, phoneno, doctor_id)
2) doctor_table(id, name, specialization, yearsOfService)
3) pharmacy_table(id, name, address, phoneno)
4) pharmaceutical_company_table(id, name, phoneno)
5) drug_table(id, name, price)

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.