⚫ Each prescription is exclusive to a doctor. However, each doctor may compose a large number of prescriptions. Each patient or doctor is assigned a distinct address. However, many patients and doctors may have similar addresses. ⚫ Prescription payments can be made using various methods with unique IDs. Only one type of payment is accepted for each prescription. A patient's prescription may include multiple medications. However, each prescribed medication is specific to one prescription. Although each medicine in stock has a unique ID, it may be associated with many prescriptions. . In each prescription, the doctor may write several medications. 4. Transactions: A. How many prescriptions are stored in the database? B. List the name, and address, of the doctor with the highest number of patients. C. What is the most prevalent method of payment? D. List the details of medicines that have been written by doctors more than twice. E. List the names, addresses, phone No. of doctors with their total number of written prescriptions. F. List the names of customers and the number of prescriptions they have, along with the doctors name who wrote those prescriptions. G. List the top-rated medications in ascending order. H. What is the average number of medicines in each prescription? I. Which city has the most number of doctors? J. List and rank preferred methods of payment. K. List the names, phone No, and addresses of customers who were diagnosed by given doctors. L. Identify the total number of prescriptions of each type at all branches. M. List the Ids of prescriptions and their payment method name written by a given doctor. N. List the details of medicine which has been in stock for 2 months. O. List the details of the expired medicine. Now, I. Design the conceptual and logical database design with transaction validation. II. Build the database using MySQL, preserving the referential integrity. III. Insert at least 10 rows of data into each table. IV. Write MySQL that fetches each of the transactions. V. Formulate a stored procedure that takes the prescription ID and returns its cost. VI. Insert a new column named (Remaining days) into the table (Pharmacy Medicine), and formulate an insertion trigger that calculates the value of Remaining days. Where, Remaining days Date Expired- Date manufacture.

⚫ Each prescription is exclusive to a doctor. However, each doctor may compose a large number of prescriptions. Each patient or doctor is assigned a distinct address. However, many patients and doctors may have similar addresses. ⚫ Prescription payments can be made using various methods with unique IDs. Only one type of payment is accepted for each prescription. A patient's prescription may include multiple medications. However, each prescribed medication is specific to one prescription. Although each medicine in stock has a unique ID, it may be associated with many prescriptions. . In each prescription, the doctor may write several medications. 4. Transactions: A. How many prescriptions are stored in the database? B. List the name, and address, of the doctor with the highest number of patients. C. What is the most prevalent method of payment? D. List the details of medicines that have been written by doctors more than twice. E. List the names, addresses, phone No. of doctors with their total number of written prescriptions. F. List the names of customers and the number of prescriptions they have, along with the doctors name who wrote those prescriptions. G. List the top-rated medications in ascending order. H. What is the average number of medicines in each prescription? I. Which city has the most number of doctors? J. List and rank preferred methods of payment. K. List the names, phone No, and addresses of customers who were diagnosed by given doctors. L. Identify the total number of prescriptions of each type at all branches. M. List the Ids of prescriptions and their payment method name written by a given doctor. N. List the details of medicine which has been in stock for 2 months. O. List the details of the expired medicine. Now, I. Design the conceptual and logical database design with transaction validation. II. Build the database using MySQL, preserving the referential integrity. III. Insert at least 10 rows of data into each table. IV. Write MySQL that fetches each of the transactions. V. Formulate a stored procedure that takes the prescription ID and returns its cost. VI. Insert a new column named (Remaining days) into the table (Pharmacy Medicine), and formulate an insertion trigger that calculates the value of Remaining days. Where, Remaining days Date Expired- Date manufacture.

Operations Research : Applications and Algorithms

4th Edition

ISBN:9780534380588

Author:Wayne L. Winston

Publisher:Wayne L. Winston

Chapter20: Queuing Theory

Section: Chapter Questions

Problem 17RP

See similar textbooks