MediRAY chain of pharmacies database design & implementation with Queries

MediRAY Chain of Pharmacies

The MediRAY chain of pharmacies has decided to develop a database system to store information of their prescriptions, patients, doctors and related details . They have given the development to your company and your manager has handed over the task to you.

Below are the requirements

• Patients and doctors are persons identified by the personal identification number. The person has address, email and phone number.
• Patients have patient number (the hospital and the patient number used here ), and their names, addresses, and ages must be recorded.
• Doctors have doctors registration number. For each doctor, the name, specialty, and years of experience must be recorded.
• Each pharmaceutical company is identified by name, address, email 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 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 pharmacies, and a pharmacy can contract with several pharmaceutical companies.
• For each contract, you have to store a start date, an end date, and the text of the contract.
• Pharmacies appoint a supervisor for each contract. There must always be a supervisor fo

Use the description provided above about MediRAY. You can make your own assumptions based on
the requirements given. However, the assumptions should be clearly indicated / defined.

Reports & Queries

1. Identify business rules. Business rules are important to define relevant constraints in the organisations. Some organisations have specific rules only practiced by the organisation. (e.g. A business rule for a car sales company may be “for each car a separate invoice should be issued.”). You can make any appropriate assumptions and mention them in your answer.
2. Design the database using Extended Entity Relationship (EERD) Diagram. Clearly indicate entities, attributes, relationships, cardinalities, primary keys and other special types of attributes and relationships (e.g. multivalued attributes/ ISA relationships, etc.). Note: Use correct symbols. If the symbols are incorrect your diagram cannot be considered as correct. You could use either crow’s foot or Chen’s EERD rules. However, do not mix both.  And you should draw the EERD diagram using a Computer Aided Tool. You may use Lucidchart, word MS Visio or a similar tool.
3. Develop relational schemas. Relational schemas should be derived using the EERD. You should map cardinalities correctly from EERD to Relational Schema. You should clearly indicate the referential integrity constraints (primary and foreign key relationships) using arrows. Clearly indicate datatype for each attribute.
4. Perform Normalization (including 3rd Normal form). In each normal form mention what you would consider and clearly show how would you remove redundancies. You should clearly indicate how the relational schemas are changing at each stage, and how you would adjust the referential integrity of the schemas.
5. Write 5 queries with where clause that you think would be used by the MediRAY. You are required to clearly indicate the functionality of each query. You will not be given marks if you have not explained the functionality of each query.

Get Project Solution by contacting us

- via WhatsApp: +92-324-7042178
- via email: codelogixstudio@gmail.com