A combined medical and herbal center wants to create a Prescription database to keep track of its medics, patients, herbal medicine, diagnosis, 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; a diagnosis will be recorded for a line of textual description, the date of the diagnosis, and the relevant patient and medic; and a prescription will be recorded for its date, the patient and medic, and all the herbs included in the prescription. We note that a unit for a herbal medicine could be a capsule, a gram, a kilogram, a piece, a bunch, a box, or a bottle, etc. Also bear in mind that a medical prescription here may or may not be directly related to a particular diagnosis.

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. You must use the same notation scheme for the ER diagram as the textbook, and the ER diagram should be strictly in the sense the textbook uses. List the schemas for all the derived tables for this database, marking out the primary keys and foreign keys if any.