Assessment 2: Case Study – Data Modeling and Databases
Submission Type: PDF/ZIP
Word limit: N/A (see instructions)
Due date: Week 9
40% of your overall grade
Overview
This assignment aims to measure your understanding of the basic concepts in the relational database model and use the entity-relationship model for database design. The assessment is in three parts: Basic ER Modelling, Database Management System Evaluation/Analysis, and Presenting findings.
Part A: Entity-Relationship Modelling (70 points)
- Using Entity-Relationship Model, design a conceptual model to represent a set of data requirements in the specifications and present the design to a high standard using UML notation through the diagramming tool LucidChart.
- Model the activities of a small business and present the model as an Entity-Relationship (ER) diagram. Then modify this ER diagram based on additional client requirements.
- Map an ER diagram into a relational database schema, showing every mapping step.
To complete this assessment, you must be familiar with LucidChart, covered during the Week 4/5 activities.
Part B: Selection and the Evaluation of a Suitable Database Management System (20 points)
Part C: Report/Entity-Relationship Model Presentation (10 Marks)
You may use no more than 2-3 slides, including the model, to highlight the report outcomes and the model design.
Assessment Criteria
This assessment will measure your ability to:
- Accurately interpret business rules (data requirements) in a specification document.
- Represent them using Entity-Relationship Model.
- Identify additional requirements a client stated later and modify an existing Entity Relationship Model to accommodate such new requirements.
- Convert an Entity-Relationship Model to Relational Database Schema.
- Application of integrity constraints in a Relational Database Schema.
Unit Learning Outcomes
This assessment is relevant to the following Course Learning Outcomes:
- ULO a: Discuss database management systems’ concepts, types, functions, and architecture.
- ULO b: Apply systems analysis and data modelling techniques to examine business processes and information requirements.
- ULO c: Design a logical database using modern development standards, database query language and tools.
Part A: Entity-Relationship Modelling (70 points)
You have just been employed as a database designer at SOFTLOGIC, a well-established software development firm. Your first job is to design and implement a database system for MEDI-CINE, a Medical Centre in Melbourne. Following the data-gathering process and the analysis of the current practices of MEDI-CINE, the following activities around their business practice have been identified.
- The system records information about the doctor’s work at the medical centre. Doctors have unique registration numbers. In addition to the registration number for each doctor, the system records the name, date of birth, gender, qualifications, and specialisations. Doctors can have multiple qualifications and multiple specialisations.
- The system keeps a record of each patient. When a new patient comes to the medical centre, the patient is given a registration form to be filled. The information gathered in this form will be stored in the system. When this information is entered into the system, a serial number is assigned to each patient. The registration form captures the following information: name, address, date of birth, gender, telephone numbers (multiple numbers are allowed), Medicare number (if any), private health cover number (if any), and private health cover company (if any).
- When a patient attends a consultation with a doctor, the following information is recorded:
consultation number, date, time, description, symptoms, and special remarks. Symptoms are a multi- valuedattribute.
- A number of prescriptions can be recommended after each consultation. For each prescription, the following information is recorded: name of the medicine, dosage, number of repeats, and special instructions. None of these attributes are unique. However, for a given consultation, medicine is prescribed only once.
- The doctors sometimes make referrals for other procedures, such as X-rays, MRI, and physiotherapy. The system should be able to record such referrals made at Computer Science and Information Technology consultations. For each referral, the following information is recorded: name of the procedure, short description, referred practitioner, and whether this procedure is essential or optional. Like prescriptions, a referral for a given procedure is only made once in a consultation.
- The system should be able to keep accounting information. In particular, the system should be able to store payments made for the consultations. There are three possibilities:
- Bulk-billing: Some Medicare patients (disabled and seniors) are bulk-billed. This means that the patient is not required to pay anything up-front. The medical Centre will bulk-bill Medicare (the government agency that deals with healthcare services in Australia) the costs associated with the consultation. The medical Centre will record the payment number, timestamp, amount, and Medicare reference number for such transactions.
- full up-front payments (cash or card): Patients with no Medicare benefits are required to pay the full cost of the consultation up-front. The medical Centre will record the payment number, timestamp, amount paid, and payment method for such payments.
- settling part of the invoice using a Medicare benefit and the balance in cash or card: Some Medicare patients must make a top-up payment (gap fee). That means part of the consultation fee is covered by Medicare benefits, and the patient pays the rest up-front. For such transactions, two payment records are created and stored. (They both are linked to one consultation). The first record will be similar to (1) above. The second is similar to (2) above.
Based on your gathered information, model your MIDECINE’s business activities and present your model as an Entity-Relationship (ER) diagram. Carefully state any assumptions that you make. In your ER diagram, you must adequately denote all applicable concepts, including weak or strong entities, keys, composite or multi-valued attributes, relationships and their cardinality and participation constraints.
If you cannot represent any of the information in the ER model, clearly explain what limitations in the ER model restrict you from representing your model.
You must draw your diagram using UML notation and the diagramming tool LucidChart. Your diagram must be drawn to a high standard with minimal clutter. You are not required to map the ER model to the relational model.
A special note: This is an open-ended question with many different models that can be derived. Your model is assessed based on accurately representing the business rules described above.
Part B: Selection and the Evaluation of a Suitable Database Management System (20 points)
MEDI-CINE also intend to upgrade their existing database management system. You will also examine the following aspects of MEDI-CINE’s future database system requirements.
- Smooth running of the new database of the MEDI-CINE operations system with enhanced Information Security
- Migrate databases of other systems, such as HR, Finance, Accounting, and Asset Management, to be managed by the same Database Management System.
- Use a Java-based web application front-end for customers to make reservations, payments, and cancellations.
- Support their intention to expand the business to other Australian state capitals, namely Sydney, Brisbane, Adelaide, Perth, and Hobart.
Give adequate justifications to support your arguments using credible and unbiased information sources, such as journal articles, white papers, and product-review articles from independent and Government sources.
Part C: Entity-Relationship Model and Report Presentation (10 points)
Present your findings in the class using no more than four PowerPoint slides (this includes two slides for the two E-R diagrams). The presentation should be no more than 10 minutes.
Referencing guidelines
Use the Harvard referencing style for this assessment. You must acknowledge all the courses of information you have used in your assessments.
Submission
You should submit one PDF document with all answers together. You may use LucidChart to work on Part A of your assignment. You may use Word or any other word processor to compile your submission. In the end, convert it into PDF format. Do not submit Word files!
Submit a 2-3 slide PowerPoint Presentation and the PDF document for Part B. You may combine both files into a single compressed (ZIP) file. The PowerPoint file must be the same one you intend to use in the presentation.
MMIS103 – Database Management Systems & Design