Database and Information Retrieval Assignment
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 for each contract, but the contract supervisor can change over the lifetime of the contract.
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.
Answer following questions.
Provide the following:
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.).
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 3 rd 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.