System design and implementation assignment
Objective
To apply theoretical concepts of System Design and Implementation in a case study.
The case study is a continuation of the Mercy Hospital Prescription System (MHPS) case used in Assignment 1. It involves designing parts of a computerised MHPS. In this assignment, you are required to develop a simple database that stores information needed in MHPS.
A database system needs to be designed to track medical prescriptions for patients. This database system should be able to show the names of drugs that a patient has been prescribed, as well as the name of the doctor who prescribed it. When designing this system, it should be noted that a doctor may issue multiple prescriptions for a patient. Similarly, a patient may have prescriptions from different doctors. (Hint: A drug is prescribed by a doctor for a patient).
Patients are identified by their admission number which is unique to each patient. Other information about patients which must be stored includes their name, gender, address, DOB and contact number. Doctors are also identified by a unique ID. In addition, a doctor's name, address and speciality must be recorded. Each prescription has a date and quantity (number of refills) associated with it. Information about a specific drug includes its name, type (oral or liquid) and cost.
Your tasks are listed below.
1. Entity-Relationship Diagram
Prepare an ER diagram for the database. Follow the correct notations for the diagram. All relationships must be labelled with cardinality information.
2. Table Schema
Create a schema for each table. Ensure that all fields/attributes are listed and the primary key is clearly underlined. Identify foreign keys where applicable. Below is a template of the schema
TableName(PrimaryKey¸ attribute1, attribute2, ..., attributeM, refAttribute1, ... refAttributeN)
where refAttribute1, ..., refAttributeN are foreign keys
3. Report Design
It is required that the system generates two reports. These are
(a) Prescription Report
At the end of each month, the billing department compiles a Prescription Report which includes every single drug prescribed within the month. For each drug, the report lists all prescriptions. Each prescription includes the admission number of the patient, the date of prescription, the number of refills and the cost. At the end of every drug listing, some summarized information is required. This includes the total number of refills and total cost for the drug. Statistical information is required at the end of the report, which includes the total number of prescriptions per month and the total cost of all prescriptions.
(b) Patient Medical History Report
A patient medical history report needs to be generated whenever needed. At the beginning of the report, the patient's name, address, date of birth (DOB) and gender should be displayed. For each year (in ascending order), each drug prescribed should be listed (also in ascending order). Under each drug listed, the date of prescription and the number of refills should be displayed. For each drug, for each year, a summary is to be displayed which includes the number of times the drug was prescribed during the year, and also the average number of refills for the drug during the year.
At the end of the report, some statistics are to be displayed for the patient -the number of years the patient was prescribed with drugs by the hospital and the total number of prescriptions prescribed to the patient for all years.
Your task is to draw paper design for both the Prescription Report and the Patient Medical History Report.
NOTE: A bonus mark is given if you can include these statistics at the end of Patient Medical History Report - the total number of distinct drugs prescribed and the most prescribed drug for the respective patient. The bonus mark is given only if you can show this in Task 6 also.
4. Implementation
Use MS Access to create the tables for the MHPS system. For submission, you need to provide screen captures that clearly shows the tables and their attributes, as well as the relationships.
5. Input Screen Creation
Use MS Access to create the input screen that can be used as an entry point for adding, modifying, deleting and viewing of patient prescriptions. For submission, you need to provide screen captures that clearly show the form.
6. Report Creation
Use MS Access to create the reports following your designs from task 3. You need to use the data stored in your tables (not hard-coded!). For submission, two system-generated reports should be provided.
HINT: You need to populate the tables with at least 20 rows/tuples each. It will ensure your reports show some information.