RECENT ASSIGNMENT

COMP1711
Data Modelling Assignment Part I Feel Good Clinic
DEADLINE: 11.59pm, Friday of First Week of Mid-Semester Break (25th Sept.)
This is not an assignment that can be done at the last minute!
This assignment submission is electronic (PDF) via FLO.
You are encouraged to work in pairs for this assignment, however it is not mandatory that you do so.
WORTH: The assignment is worth 30% of the assessment.
1 General Specification
(a) Construct the information model
1. List all entities on the Entity Data Dictionary worksheet. Record the name, a description and any aliases. In the Occurrence column described how the entity exists in the organisation. List any dependent entities and indicated in the occurrence which entity it depends on.
2. List all relationships on the Relationship Data Dictionary worksheet. Record the name, a description and any aliases. In the Occurrence column described how the relationship exists in the organisation.
3. List all relationships and entities on the Relationship-Entity Data Dictionary worksheet. For each participating entity, give the multiplicity (both the participation and cardinality). If the relationship is greater than binary then list other entities on subsequent rows
4. For each identified entity and relationship list its name and its attributes on the Attributes Data Dictionary worksheet. For all attributes, give a description, an indication of the data type, length and domain, whether it can be NULL (i.e. optional), and if it can be multi-valued. If it can be multi-valued then list the range of values.
It is recommended that you use copies of the data dictionary work-sheets available as part of this specification on FLO.
I would advise that you step away from the computer when doing this.
(b) Using your answer to (a) draw up and Entity-Relationship Diagram (ERD). The diagram can be hand drawn. It should be at least A4 size. The important criteria are consistency with part (a), clarity and readability.
Entities in the ERD should fully represent the information contained in the data dictionary. That is, include all the attributes and any other necessary information. It should also conform to the Unified Modelling Language specification discussed in lectures and the textbook. Using a different modelling language will result in a grade of Fail with a mark of 1, which allows for resubmission with a maximum mark of 50%. Using software that does not use UML is not an excuse.
An example of entities is shown here:
2 Submissions
The submission of any work will be taken as your claim that it is your own work (or that of you and your partner if working in pairs), i.e. that you cognitively and physically created it. It also signals your agreement to re-do it or similar work under supervision if any doubts are raised about your authorship of the work.
The required submission for Part I of the assignment is:
Tasks (a) and (b): You are to submit finished (final) versions of the work-sheets and the associated ER diagram electronically on FLO as a single or set of PDF files. If you to choose the paper option it should be submitted to the appropriately labelled submission box.
You may also optionally submit a document (a PDF) explaining any assumptions you have made about the specifications. This may have to do with particular multiplicities you assign to relationships and entities, different types of attributes, entities, relationships versus entities. Essentially any piece of information you thought was ambiguous and you think requires additional explanation beyond tasks (a) and (b). This could include information that was clarified by a tutor.
3 Assessment
If you a working as a pair each member will receive the same overall mark.
The assignment is worth 30% of the total assessment. It will be marked out of 60. A guide to the breakdown of the marks over the parts is
(a) 10 marks (2.5 marks per worksheet)
(b) 50 marks (Marks allocated for entity, relationship, and attribute layout, multiplicities and general diagrammatic representation)
This is only a guide since the submissions are inter-dependent and will not be marked independently. Inconsistencies between them will lose marks.
If you receive a mark in the range 1-49% (less than 30 out of 60) then resubmission of the assignment is allowed up to one week after it is returned with a maximum mark of 50% to be awarded.
4 Specifications
In the case-study description the following notation is used to indicate the type of data items:
(N x) a digit string (integer) of length x
(S x) a character string of length x
($ x) x dollar digits and 2 cent digits
(C) a combination of family name (S 20) personal name (S 20)
title (S 4)
(A) a combination of street address (S 45)
postcode (S 4)
(D) time and date or either
4.1 General details.
The description is unrealistic in its simplicity and stripped down details but hopefully it has a real world flavour (and gives students insight into the real world use of databases). I have tried to make the description as precise as possible. Any solution that could be reasonably derived from the description will be accepted. For instance, it can often be argued whether a minimum participation should be 0 or 1.
PLEASE base the design on the description, that is, treat the description like requirements. Failure to adhere to the description is very poor professional practice. (If you really believe the description is invalid, please discuss it with me as soon as possible.) I am quite happy, in consulting, to explain any part of the description that is problematic. Also keep up to date on FLO for any clarifications.
4.2 Feel Good Clinic
The Feel Good Clinic requires a system to manage the scheduling of appointments, in its megasurgery complex, and the billing of patients.
Each treatment room in the clinic has a unique number (N 3). Each doctor has a unique identifier code (S 2) recorded along with their name (C), contact phone number (S 14) and one to three qualifications (each S 10). Each doctor has one or two surgeries in which they work. More than one doctor will use the same room at different times (a doctor only uses one room at a time.) but a doctor will have a preferred room.
Each patient or customer is uniquely identified by a client number (N 8) which is assigned to them when they first become associated with the clinic and is used for them thereafter. Also recorded is their name (C), address (A). A customer is responsible for paying the bills for one or more patients. Usually, but not always, this will include themselves. For instance, a parent might be a customer without being a patient if they pay the bills for their child who is a patient.
For each actual patient, their birth-date (D) and their Medicare number (S 12) are also recorded, along with which customer is currently responsible for paying for their treatment. The responsible customer also has one to three contact phone numbers (S 14) and an email address (S 50).
For each service available, the prescribed code (S 4) is recorded along with a description (S 20) and the current service fee ($ 3). Also identified are those doctors who give this service. Most doctors give a large number of services but not all of them.
When a patient requires some treatment, one or more appointments are scheduled; each appointment is for a particular date and starting time (D) with a specific doctor. Appointments are made in hour blocks; on the hour. (At any one time there is only one appointment for a particular surgery and a particular doctor). A patient does not have to be seen by the same doctor across different appointments but each appointment is with a single doctor.
At the end of an appointment, an invoice is generated. Each invoice also a unique invoice number (N 5), it identifies the patient and the doctor involved, as well as the customer billed. It records the relevant date (D), a clinical comment (S 200) if one is provided and the total fee ($ 4). The invoice initially has a status (S 1) of ‘C’ (“completed”). At some later stage, the status is changed to ‘B’ (“bill sent”) and eventually the status is changed to ‘P' (“paid”). Each of the invoice's include one to five lines that identify the services given during the appointment.
4.3 Operations and Questions
The “Operations and Questions” are here to help verify/validate you design. You do not need to provide answers to questions for the assignment.
• Add a new appointment
• Who has booked the most appointments?
• Find the doctor who gives the service X • Which service is the most/least popular?
• Which doctor is used the most?
• Which treatment is used the most?
• List all the appointments for the month of X
• Which is the dcotor has the smallest number of patients?
• Which service has made the clinic the most money?
• Which clients have not paid and what is the outstanding amount and the contact details?
• Which dcotor has the most services offered?
• What service is required for appointment X?