ITC556 Database Systems - Assessment 2 - Modelling

Task

Organic Pastures Clinic (OPC) offers a variety of beauty treatments for all age groups in the Hunter region of NSW. OPC prides itself by providing organic, pain-free treatments that will leave you feeling fresh, comfortable, relaxed and re-energised, under the hands of highly qualified and experienced skin professionals. OPC employs 4 different therapists that are specialised in each type of treatment.

  1. Each therapist takes appointments with clients for a specific treatment, according to their speciality. Oftentimes, the same client makes several visits either to the same therapist or with another therapist depending on the type of treatment recommended. In some cases, a therapist may not have any appointments booked, but may have several appointments booked every 30 minutes on other days (especially weekends). (3 marks)
  2. Treatments are identified by a code and classified by type, the number of sessions (or visits) needed, and a price per session: for example, code 820 is Skin Rejuvenation, which is a treatment that may require up to 4 visits and each visit is charged at $90. (2 marks)
  3. When a client books a treatment, they are asked to pay a deposit, and the payment reference and amount of the deposit is recorded. They need to pay the remaining amount after completing each treatment in each session (or visit). (2 marks)
  4. OPC keeps records of the clients’ medical history, which includes their medical condition (if any) and their current medication (if they are taken any), as well as the name of the therapist who did their treatment/s in each visit.  (2 marks)

Your task

Create an ERD for each of the above statements that describe the current business rules for OPC. For each new statement being the business rule, extend the ERD to include the additional business rule in the ERD (created in the previous point for example; B will include A&B, C will include A, B & C, etc.)

Use Crows Foot notations and include all of the following in each part:

  • All entities with Primary key and any foreign key attributes;
  • The relationships between entities;
  • The cardinality and optionality of each relationship;
  • Any M:N relationships are be resolved to 1:M relationships
  • Include your student ID and full name under the ERD (in Draw.io) then copy the whole ERD as an image to your Word Document.

Additional 1 mark is given when following all the above correctly and producing a neat ERD with correct notations.

 

NOTE:

  • Use https://www.draw.io to create your ERD
  • Include the PKs where appropriate
  • Manually drawing the ERD is NOT acceptable.

NOTE: For guidance on how to draw an ERD based on business rules, watch this YouTube video: https://www.youtube.com/watch?v=YvJ4t9_2SWk

Rationale

This assessment task will assess the following learning outcome/s:

  • be able to model complex business rules using Entity Relationship Diagrams (ERDs).
Order Now