INFO20003 S2 2019 Page | 1 INFO20003 Semester 2, 2019 Assignment 1: ER Modelling Due: 6:00pm Friday 30th of August Submission: Via LMS https://lms.unimelb.edu.au Case Study: CARLTON MEDICAL CLINIC Carlton Medical Clinic is located in the vicinity of The University of Melbourne and hence is busy throughout the year especially during the semester. You are required to design a database for an information system to be used by Carlton Medical Clinic. The clinic operates in a similar fashion to any general practice clinic with the following characteristics. PATIENTS The clinic requires patients to book an appointment in advance (if patients haven’t booked an appointment they can’t be admitted for a consultation). In order to book an appointment, patients need to register into the system by providing their full name, gender, home address (consisting of a unit number, street name and number, postcode and suburb), cellphone number, email address(es), date of birth and 20-digit Medicare number (if they have it). Each patient is issued a unique numeric patient ID used to identify each patient of the clinic. BOOKING APPOINTMENTS If the appointment is booked by calling in the clinic, an assistant will get the patient’s details to register them into the system. In addition to the registration details explained above, the system should record the date and time of the appointment and its status (scheduled/cancelled/completed) which will be updated accordingly. The patient can also book the appointment via online portal in which case they will fill in the online form for self-registration before their appointment is confirmed. If the appointment is cancelled by the patient in less than 48 hours prior to the scheduled appointment or without notification, a Non-attendance fee should be paid by the patient either during the next visit (if re-scheduled) or an invoice/bill will be sent to their home address. Patients may request a particular doctor to attend them for their appointment or will be allocated a doctor at the time of booking. INFO20003 S2 2019 Page | 2 DOCTORS AND CONSULTATIONS Thirty doctors currently work for the clinic. We anticipate that this number will go up to a hundred over the life of the clinic. We need to record when doctors arrive and leave work, so that we can pay them for the number of hours they work. Each doctor has a negotiated hourly fee with the clinic (which may differ from a doctor to doctor) and may work different number of hours each day. This tracking will help the system to figure out the number of doctors who are at work in order to manage the load accordingly and will be used when issuing monthly payments for individual doctors. It is assumed that doctors will arrive once (and do a check in) and leave once (and do a check out) on a given day. Each doctor is identified by their practitioner ID and patients can address them by their names. Since doctors might sometimes be running late with respect to the scheduled appointments, it is doctors’ responsibility to record the start and end time of each patient’s consultation. At the end of each consultation the doctor keeps a set of notes (approximately 200 words (i.e. 400 characters) for each consultation). By recording the end of a consultation, the doctor signals the system that they are free for their next patient. The data is analyzed statistically every few months to evaluate the busiest hours of the clinic over time. It is also helpful to see typically how long the patients have to wait before their consultation (where the waiting time is measured as a time difference between the scheduled appointment and the start of the consultation). During a consultation, the doctor may write prescriptions for the patient to obtain medication or may require tests to be performed for further evaluation if conditions are not clear. PRESCRIPTIONS Each prescription has an issue date and a date up to which the prescription is valid and can contain details of either one or more medications. For each medication, the doctor specifies a dose and frequency (e.g. aspirin, 20 mg, once a day). The doctors consult the clinic’s list of about six thousand medicines and prescribe one or more of medications from that list. A record of all prescriptions written should be stored. Though doctors write prescriptions usually during consultations, it is possible for a prescription to be extended by the patient in some circumstances. The patient can call the clinic to request an extension to their prescription. When a prescription is extended, a new prescription record is created which is linked to the original prescription issued by a doctor, with a new issued date and a new validity date. A prescription may be extended many times, INFO20003 S2 2019 Page | 3 every time creating a new prescription record that is linked to the original prescription issued by the doctor in the first place. MEDICAL TESTS During a consultation, the doctor may refer the patient to undergo further examination including one or more tests. There is a list of approximately 200 standard tests (such as “CT scan”, “chest X-Ray”) that doctors can choose from and refer the patient to carry out those tests. Some tests result in one or more medical images (each up to 20 MB in size) needing to be stored. Each image is accompanied with a short analysis of around 100 words (2000 characters). The system should keep record of when the test was carried out and when the results are received. For each test the results are received in the form of a piece of text (maximum of 500 words (10000 characters)). These results are stored in the system and messages are sent to the concerned doctor and patient, telling them that test results are available. Your database design needs to be able to solve the following business requirements: • How many patients did attend the clinic in July 2019? • What was the busiest hour in the clinic on the 20th of August 2019? • How many patients had to pay Non-attendance fee in year 2018? • Who waited the longest for their consultation on the 1st of August 2020? • How many times a certain prescription was extended? • For which test, the results are never sent back within 2 days of being carried out? • Which medication is the most frequently prescribed by a particular doctor? • Which doctor was paid the most by the clinic in June 2019? • Which test is most frequently carried out? NOTE: These requirements are listed for your guidance only. You do not need to submit any SQL queries or explanations of how these requirements are met. INFO20003 S2 2019 Page | 4 Instructions Assignment 1 is worth 10% of your final mark. The assignment will be graded out of 100 marks as described in the table below: ER Physical Model with assumptions 80% Conceptual Model in Chen’s Notation 20% .mwb Physical Model File Assignment Hurdle • You are to analyse this Business Case and design a Conceptual ER Model in Chen’s notation (can be hand drawn) and a Physical ER Model for a MySQL Relational Database in Crow’s foot notation (modelled with MySQL Workbench). • You may list any assumptions you have made about the model on a separate page. There is a 400-word limit for assumptions. Assumptions must not be used to simplify the assignment, but only to justify your decision about any ambiguity in the study. • As part of your submission you must submit your final MySQL Workbench .mwb file of your Physical ER Model. NOTE: Items 1 (Conceptual Model & Physical ER model) and 2 (Assumptions) must be submitted as a single PDF. The title of the PDF document must be your student id (e.g. 987654.pdf). Similarly, the title of your .mwb file should be your studentid.mwb (e.g. 987654.mwb) Assignment Submission You are to submit the assignment under the Assessment tab on LMS. Each section of the LMS Submission is clearly labelled and colour-coded in LMS for your convenience: 1. ONE PDF document named as your student id (e.g. 987654.pdf) containing a legible (hand drawn) conceptual model in Chen’s notation, a legible picture of your Physical ER Model and your assumptions (if you stated any). Submit this single PDF document under the BLACK submission link. NOTE: If you fail to submit legible models you will be penalised 10% of the mark for this assignment. 2. Your final .mwb MySQL Workbench file of the Physical ER model. Submit your .mwb file under the BLUE submission link. INFO20003 S2 2019 Page | 5 Requesting a Submission Deadline Extension If you need an extension due to a valid (medical) reason, you will need to provide evidence to support your request by 5pm, 29th of August. Medical certificates need to be at least two days in length. To request an extension: • Email Farah Khan (
[email protected]) from your university email address, supplying your student ID, the extension request and supporting evidence. • If your submission deadline extension is granted you will receive an email reply granting the new submission date. Do not lose this email! Reminder: INFO20003 Hurdle Requirements To pass INFO20003, you must pass two hurdles: • Hurdle 1: Obtain at least 50% (15/30) or higher for the three assignments (each worth 10%) • Hurdle 2: Obtain a grade of 50% (35/70) or higher for the End of Semester Exam Therefore, it is our recommendation to students that you attempt every assignment and every question in the exam. GOOD LUCK!
