In this assignment, you’ll extend the toy EMR (Electronic Medical Record) system database demonstrated in class with additional features primarily aimed at identifying Adverse Drug Reactions - also known as ADRs. These occur when two drugs taken together have an unintended and possibly serious side-effect. Over two million ADRs are reported annually resulting in more than 100,000 deaths, so avoiding ADRs is a major healthcare priority.
1. Add a medications table and populate it with 10 made-up or real medications. The table should at minimum have the following attributes a. A unique identifier (primary key) b. Medication name c. Can be taken if under age 12? (Boolean) d. Can be taken if pregnant? (Boolean) e. Mg per Pill (Double) – milligrams per pill f. Max mg per 10kg (Double)
2. Add a drug interaction table. If two drugs (medications) are listed together, they should NOT be prescribed together.
3. Add a prescriptions table that links patient, doctor, and medication. Each entry should also list the # pills prescribed per day, and date of prescription.
4. Create a stored procedure that adds a new prescription with parameters: Patient Name, Doctor’s Name, Medication Name, and # Pills prescribed per day. The date assigned is the current date. For the prescription to be accepted, the patient must meet the guidelines of the medication. For example, if the patient is pregnant, don’t allow a prescription for a medication that shouldn’t be taken when pregnant. Make sure that the # pills prescribed per day is less than the max allowed. Also check the patient’s age and whether the patient is already taking any medications that might interact negatively. If the prescription is rejected, issue an appropriate message explaining why the prescription was rejected. Document your various CALL statements explaining how different features are being demonstrated.
5. Add a trigger that responds to a patient becoming pregnant. It should add a recommendation: “Take pre-natal vitamins”. If the patient is currently taking any medications that shouldn’t be taken when pregnant, also add a recommendation saying that “Medication X must be discontinued due to pregnancy.” Automatically remove these recommendations if the patient is no longer pregnant.
Download database script :
starter code github link