Development of an ER Diagram and Database Implementation

 (Not rated)
 (Not rated)


Assignment 1

Development of an ER Diagram and Database Implementation



To give you practical experience in using Entity-Relationship and Relational Database modelling techniques.


Due Date:

Monday of Week 7 (see Course Description for further dates and times).



Project Specification

Dr Zen Fuller is a chiropractor practicing in Churchill.  He started his practice in 2010, offering a variety of services including various types of adjustments, massages and health advices.  Due to Zen’s professionalism in offering effective and necessary treatments to his patients, his practice has grown substantially over the past few years.  To continue providing high quality services to his patients, Zen has decided to upgrade his current computer system, which is used for tracking the treatments for his patients and also other activities in his practice. 


At the moment, when a patient turns up for a scheduled appointment, his/her information are retrieved using the patient’s name.  If this is the patient’s first appointment, the clerk will request the patient to fill up his/her personal information (i.e. name, address, contact number and drug allergies) on a patient information form.  A unique patient number will then be allocated to the patient.  If this is not the patient’s first appointment, the clerk will check with the patient if there is any change in his/her personal details and if he/she is covered by any health insurance.  All Australia citizens/permanent residents are covered by a public health insurance while private health insurance is optional.


When Zen sees the patient, he first records the actual start time of the consultation.  After diagnosing the patient’s problem, Zen treats the patient with the appropriate service(s).  If required, Zen will also recommend some nutrition products to the patient.  If the patient is interested in purchasing these products from Zen’s clinic, the quantity of each product is specified.  If the patient is not interested, Zen will indicate the quantity as ‘zero’.  At the end of the consultation, all information on the diagnosis, treatment and products recommended to the patient are recorded.  The time, which the consultation is completed, is also recorded.  The patient is then directed to the clerk for payment.


The net amount to be paid by the patient is calculated based on the total price of the services/products acquired by the patient during the consultation, less the rebates on these services/products given by the health insurances of the patient.  Figure 1 shows a report detailing the information of a patient’s consultation.


You are required to design (using an E-R diagram) an entity-relationship model of the problem, convert the model into a relational model, and assess the normal form of each schema.







Patient Number


Consultation Number


Patient Name

Daren Anderson

Drug Allergy

þ Penicillins  ¨ Cephalosporins    þ Aspirin     ¨ Aztreonam


¨ Codeine      ¨ Sulfa Type Drugs þ Isoniazid  ¨ Nitrofurantoin


Tick all drugs which the patient is allergic to.

Consultation Date


Scheduled Start Time

10.20 am

Actual Start Time

10.25 am

Actual End Time

11.15 am


Service Code

Service Name

Health Insurance Classification

Unit Cost

Health Insurance  Covers *


Diagnosis Description



















Spinal Adjustment






Foot Massage







Neck Massage


































OZ Public


Medibank Private


HBA Private


Medibank Private


HBA Private


NIB Private


HBA Private


NRMA Private


















Back pain as a result of improper sitting posture in long period of studying





Back pain due to long term sports activities






Incorrect sitting posture


Recommended Nutrition Product




Unit Cost


Diagnosis Description








Nature Back Support


Organic Relax Massage Oil


Nature Wild Berry Herbal Tea















Nature Inc



Organic Inc



Nature Inc


Supplement for back pain



For back and neck massage



General exhaustion.  Tea to improve general health


*Note: All health insurances that cover that service are listed.


Figure 1: Report showing details of a patient’s Consultation



Assignment Submission

You should produce a single pdf file, created using Word, containing all of the requirements as specified within this document.   Your lecturer will provide details of how the file is to be submitted within lectures.


The design document should contain:


  1. A completed copy of the SITE Assignment Coversheet.
  2. An appropriate title page that includes an acknowledgement of all students you have spoken to about the assignment.
  3. A table of contents and automatically generated page numbers.
  4. An entity relation (E-R) diagram that models the problem which includes:

a.   all entities, relationships (including names) and attributes;

b.   primary (underlined) and foreign (italic) keys identified;

c.   cardinality and participation (optional / mandatory) symbols; and

d.  assumptions you have made, e.g. how you arrived at the cardinality/participation for those not mentioned or clear in the business description, etc.

The E-R should be completed using the standards of this course (crow’s feet).


  1. Normalisation of relations which identifies:
    1. dependency diagram for each relation
    2. the level of Normalisation achieved for each relation;
    3. the reasons for any relation that is maintained NOT in 3NF.


  1. Relational data structures that translate your E-R diagram which includes:
    1. relation (table) names,
    2. attribute (column ) names and field types (as required by WAMP),
    3. primary and foreign keys identified;

The data structures should be shown using the standards of this course.


7.  A bibliography, in APA format, containing all resources used to complete the assignment.   If no resources have been used please indicate this appropriately.


Your  assignment  should  be  completed  according  to  the  University  of  Ballarat  General  Guide  for  the Presentation of Academic Work. This is available online at the following web address:


Assessment Criteria



  1. How clear and well-presented your submission is.
  2. E-R diagram: adherence to our standard, assumptions made, inclusion of correct primary and foreign keys, appropriate entities, relationships, and attributes.
  3. Normalisation: appropriate interpretation of each normal form, arguments for leaving the schema in the normal form you consider optimal.
  4. Conversion of E-R diagram to relational schema: schema is a correct translation of the E-R submitted with appropriate tables, columns, and primary keys.



Assignment 1 – Marking Overview



ER Diagram

/ 50

Relational Structure




Relational Schema










    • 7 years ago

    Purchase the answer to view it

    • attachment
    • attachment
    • attachment