Prescriptions-R-X

PART 1:

Student to prepare a Poster containing the following:-

• Minimum of 5 possible Business Rules existed in the scenario.
• Normalize the data with and explanation of the process.
• Data Modelling with Entity Relationship Diagram (ERD)
  • Identify all the entities and attributes from the given scenario.
  • Show the relationship between the entities and attributes. Define the connectivity, cardinalities, weak and optional relationships.
• Data Dictionary

PART 2:

Student to submit a softcopy of Database System containing details as below:

• Database Implementation and Loading. Create the database, tables and populate tables with sufficient data for testing queries. (To include all the CREATE TABLE statements, INSERT INTO statements and SELECT ALL statements).
• Database Testing To design and construct all the queries required for supporting the functions of the database. Provide screen dumps to show the results of executing each of the queries. Implement your queries using SQL under the MYSQL environment.

SINGLE TABLE Write ONE (1) select statement involving ALL below syntax.

• where
• update
• delete
• sum
• count
• avg
• max
• min
• not
• between
• group by
• having
• distinct
• order by
• like
• in
• and
• or
• alias
• any ONE (1) SQL function (e.g: ISNULL, etc)

MULTIPLE TABLES Write ONE (1) select statement involving ANY of the following syntax below:

• JOIN between two tables.
• JOIN between three or more tables.
• Sub-query involving IN, ANY, ALL

Scenario 1:

The Prescriptions-R-X chain of pharmacies has offered to give you a free life time supply of medicines if you design its database. Given the rising cost of health care, you agree. Here is the information that you gather.

• Patients are identified by SSN, and their names, addresses, and also ages.
• Doctors are identified by an SSN, 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 reordered. Each drug is sold by a given pharmaceutical company, and the trade name identified a drug uniquely from among the products of that company. If a pharmaceutical company is deleted, you need not keep track of its products any longer.
• 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.
• Pharmaceutical companies have long-term contracts with pharmacies. A pharmaceutical company can contract with several pharmaceutical companies. For each contract, you have to store a start date, and end date, and the text of the contract.
• Pharmacies appoint a supervisor for each contract. There must always a supervisor for each contract