Prepare an er diagram for the database

Assignment Help Management Information Sys

Reference no: EM131230636 , Length: word count:1000

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.

Reference no: EM131230636

Questions Cloud

Explain how the process of cellular evolution occurs : Use your understanding of the basic B-cell immune response, and the data in Fig. 5.12 (right), to explain how the process of cellular evolution occurs via natural selection in B- cells.

What is the size of the activation record for the functions : What would the activation record look like if the program were run and the user typed a string of length greater than 10 characters? What would happen to the program?

Write a function pop that will pop an item from top of stack : Write a funtion Pus h (the declaration is provided) that will push the value of item onto the top of the stack. If the stack is full and the item cannot be added, the function should return a 1. If the item is successfully pushed, the function sho..

Draw conclusions about the future of managed care : Draw conclusions about the future of managed care in each program-will it grow or decline? What will be the challenges for success? Write a summary for each topic tying together the information learned about that topic.

Prepare an er diagram for the database : CSE1IS - Information Systems - To apply theoretical concepts of System Design and Implementation in a case study - Prepare an ER diagram for the database. Follow the correct notations for the diagram. All relationships must be labelled with cardinal..

Fill in the three underlined missing portions in the code : The function Balance d that follows, however, is missing a few key pieces of code. Fill in the three underlined missing portions in the code.

What is the return to the investor : Assume that Vogl stock is priced at $50 per share and pays a dividend of $1 per share. - If, after one year, the stock is sold at a price of $60 per share, what is the return to the investor?

How is a food calorie different from an energy calorie : How is a food "Calorie" different from an energy "calorie"? Restate the first law of thermodynamics in your own words. List two different units for energy, give SI units where appropriate.

Do you think a ban on short selling is effective : Why did the SEC impose a temporary ban on short sales of specific stocks in 2008? - Do you think a ban on short selling is effective?

User Account

All Pages