SIT103/SIT772: Database Fundamentals 3.1P: Entity Relationship Diagram
SIT103/SIT772: Database Fundamentals
3.1P: Entity Relationship Diagram
Overview
In this task, you will analyze a business scenario to understand its data requirements and develop a relational database model in terms of Entity Relationship Diagram (ERD) using Lucid Chart or Microsoft Viso.
Tasks to do
Read the business scenario given below carefully.
Prescription System for Barwon Health, Geelong
The prescription branch of Barwon Health is facing a rising cost and looking into measures that could help reduce operational cost. It has been decided that a new database system is needed. You have been hired to be their database consultant. After a few interviews with different stakeholders of the system, you gathered the followings.
Patients who visited Barwon Health are identified by their unique identifier called UR Numbers. The system should also store patients’ names, addresses, ages, contact details (email and phone) and their Medicare card numbers if available. Doctors on the other hand, are identified by their ID. For each doctor certified to make prescriptions, the system should also capture the doctor’s name, contact details (email and a phone number), their specialty, and the years of experience they have in their area of specialization.
Drugs are supplied by different pharmaceutical companies. Each company is identified by their name, address, and a phone number. For each drug, the system should record the trade name and the drug strength. If a pharmaceutical company is removed from the system, then all its product should also be removed from the database.
Later, you also found out that every patient has a primary doctor, and every doctor is assigned to at least one patient. A doctor could prescribe one or more drugs for several patients, and a patient could obtain prescriptions from several doctors. For each prescription, a date and a quantity are associated with it.
Based on the above scenario, do the following data modelling tasks.
Draw an Entity Relationship Diagram (ERD) of the proposed Barwon Health Prescription System based on Crow’s Foot notation using a professional drawing tool. Please note that you may have to make some assumptions to meet all business requirements. If you make any assumptions, do not forget to list them first in your submission.
- Identify all Entities, their Attributes, Constraints, and Relationships with Cardinalities
- Identify Primary Key (PK) in all entities.
- Implement and label all relationships using Primary/Foreign Key (PK/FK) or Associative Entities
- Consider using specialization/generalization relationships using supertype and subtype entities with disjoint/overlapping and partial/total completeness constraints if possible.
- Provide justifications (based on business rules or your assumptions) for entities, attributes, constraints, and relationships/cardinalities you considered
Submission Requirements:
Submission Due
The due date for each task has been stated via its OnTrack task information dashboard.
Submit one PDF/WORD file with your assumptions, an ERD (with Item#1-4 clearly shown) in Lucid Chart or Visio, and justifications (Item# 5) via OnTrack for Task 3.1P.
