Public Health Management System: ER Diagrams and Relational Models | Nursing Assignment Help

Public Health Management System

Task Details:

Introduction

This assignment will help you evaluate your skills and also deepen your skills in designing a database (Conceptual and Logical Model) using business rules. You need to apply your skills in designing ER Diagrams and Relational Models.

Assignment Details

Part 1 Database Design

Please design a relational database for the following scenario using the ER diagrams and relational modelling techniques taught in classes and tutorials.

Scenario

NewTowny city council would like to create a database for their public healthcare system so that data can be managed, retrieved and updated easily. NewTowny has one public hospital which supports for health needs of all the residents. The city council provided following business rules:

Everyday hospital handles several patients and hosts several doctors (General practitionersand Specialists). The hospital has two department: General and Emergency. Each patient needs to be registered before he can be admitted to the hospital. During registration, several personal details of the patients are collected such as patient name, address, current medication, information about chronic disease, known allergies, and NewTowny resident identification number. During registration they are assigned to General practitioner with whom he can make one or more appointments. The patient should first make appointment with his general practitioner for any health issue unless it is an emergency which are handled by emergency department. Each appointment contains details such as time and venue including the communication medium using which the appointment is made. Appointments can be made by email, phone or in person. If kept, an appointment yields a visit with the general practitioner specified in the appointment. The visit yields a diagnosis and, when appropriate, treatment. A diagnosis may contain tests prescribed, result of tests and doctors comments. A treatment generally constitute the time duration of treatment (i.e. start and end date), diet plan as a blob of text, and prescribed medicines as a blob of text. General practitioner can refer the patient to one or more specialists with whom patient can make one or more appointments. However, Specialist can accept the appointments only if a patient got a reference from a general practitioner. Emergency cases do not require an appointment, however the details are entered in the patient record. During emergency, a patient’s situation is diagnosed by a general practitioner and then by a specialist who gives a treatment accordingly. Each patient visit creates a bill which contains information such as date of the visit, particular doctor’s fees, and service charge. If appointment is not kept, the patient will be billed a cancelation fee. Each bill must be paid. However, a bill may be paid off in many installment payments, and a payment may cover more than one bill. A patient may pay the bill directly, or the bill may be the basis for a claim submitted to an insurance company. If the bill is paid by an insurance company, the deductible is submitted to the patient for payment.

Steps in Design (HINT)

• Design an Entity Relationship Diagram using conventions taught in tutorial

  • Identify the entities, attributes, relationships, participation and cardinality
  • Show to your tutor to get comments.

• Design a relational model by using conversion algorithm taught during tutorial

Submission

You are expected to submit a word document which details the following:

1. Your Name and Student id on the first page.
2. An Entity Relationship diagram modelling the scenario using the ER conventions for KIT102 (given in your tutorial handout). This can be drawn on a sheet of paper, then scanned.
3. A Relational Model with all the conversion steps involved in converting from the ER diagram to the Relational model (Using the conventions given during tutorial).
4. All submissions will be via MyLO.

If you do not understand file submission instructions, seek help from your tutor or your lecturer at least one day before the assignment is due.

Please work on the assignment before the due date, do not wait till last moment to submit your assessment.

Entity Description