KIT 102: Introduction to Data Science-Database Assignment

Internal Code:MAS175

Data Science Assignment:

Question: 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 practitioners and 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: 1.Design an Entity Relationship Diagram using conventions taught in tutorial a. Identify the entities, attributes, relationships, participation and cardinality b. Show to your tutor to get comments. 2. Design a relational model by using conversion algorithm taught during tutorial