Q1. Basic database design and queries (7 marks)
A combined medical and herbal centre wants to create a database to keep track of its medics, patients, herbal medicine, and herbal prescriptions. For simplicity, both a herbal medic and a patient will be recorded for his or her name, a herbal medicine will be recorded for its name, unit and the price per unit, and aprescription will be recorded for its date, the patient and medic, and all the herbs included in the prescription.
i. Design a minimum database (a database of only essential attributes) to fulfil this purpose, and draw the ER diagram for your design. Indicate on the ER diagram the primary keys and the relationship multiplicities. By minimum we mean that you don't need to add anything that is not explicitly stated in the requirements, unless it's one of your artificially generated keys. For instance, you don't need to include a telephone number or email address for the entity corresponding to a patient. (1.5 mark)
ii. Draw the Global Relation Diagram (GRD) corresponding to the ER diagram in the above, indicating all the primary keys and foreign keys. The GRD should be in a form similar to Figure 17.9 (page 554 or 516 for edition 5) of the textbook, but all the attributes should be kept there too. (1 mark)
iii. Write an SQL script (of statements) that generates all the tables for your designed database. (1 mark)
iv. Write an SQL script to insert sufficient records into your tables. Each table should contain no less than 3 records. At least 2 prescriptions should each contain 2 or more herbal items. Screenshots are required for the records of all the tables. (0.5 marks)
v. For a given prescription (number), write an SQL statement to list all the herbs there, their corresponding price per unit, and the number of units prescribed (screenshot required). (0.5 marks)
vi. For all prescriptions, list the patient name, prescription date, prescription number, and the corresponding doctor's name. Sort the output according to patient name alphabetically, and then to the prescription date in the reverse order (screenshot required). (0.5 marks)
vii. Write an SQL statement that lists the prescription (number), the corresponding date of the prescription and the total price of the herbs within the prescription, for all the prescriptions in the database (screenshot required). (0.5 marks)
viii. Extend the database to also keep track of the diagnoses of the patients, and bear in mind that a medical prescription here may or may not be directly related to a particular diagnosis. Draw the GRD for this extended database, and write an SQL script that would create all the tables for this extended database. The GRD and SQL script here should be given separately from those in the earlier subquestions. (1.5 mark)
Note that you must