Project Part1
Clinic Management
DEADLINE: Monday, 15 April 2024 at 2pm
This is not an assessment that can be done at the last minute!
This is the first part of the project. The second part will be built upon this work. Please make sure you are working meticulously on this part. This carries a 10% weightage of the total grade.
This task might require you to submit both physical and digital copies. Ensure that the first page of the physical copy includes a filled-out cover page, which you can download as specified in the guidelines.
1 General Specification
(a) Construct the information model (templates are provided)
1. List all entities on the Entity Data Dictionary worksheet. Record the name, a
description and any aliases. In the Occurrence column describe how the entity exists in the organisation. List any dependent entities and indicate 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 describe 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 (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 their attributes on the Attributes Data Dictionary worksheet. For all attributes, give a description, an indication of the data type and length, whether it can be NULL (i.e. optional), and if it can be multi-valued.
Please use the templates that have been provided.
(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 which is also the recommended size of the page. The important criteria are consistency with part (a), clarity and readability. I highly recommend you draw the diagram on the draw.io website and then download an editable copy.
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 reference book. An example of entities is shown here:
Figure 1An example of what is required
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 assessment is:
Tasks (a) and (b): You are to submit finished (final) versions of the work-sheets and the associated ER diagram to either LMS or to hand-in the assessment to me (Dr Mehwish Nasim) in office 1.16. Printed assignments should be stapled or clipped together, please. A completed assignment cover sheet must be attached. You may also submit your assignment electronically as PDF files [Recommended].
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 facilitator.
3 Assessment
If you a working as a pair each member will receive the same overall mark.
The assignment is worth 10% of the total assessment. It will be marked out of 50.A guide to the breakdown of the marks over the parts is
(a) 20 marks (5 marks per worksheet)
(b) 30 marks (Marks allocated for entity, relationship, and attribute layout, multiplicities and general diagrammatic representation)
This is only a guide since the submissions are interdependent and will not be marked independently. Inconsistencies between them will lose marks.
4 Specifications
In the case-study description the following notation is used to indicate the type of data items:
(Nx) a digit string (integer) of length x
(Sx) a character string of length x
($ x) x dollar 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 may not represent a real-life scenario. However, it is simple enough to help you apply the concepts that you have learnt in the class and may still have a real world flavour (and gives students insight into the real world use of databases). Description is as precise as possible. Any solution that could be reasonably derived from the description will be accepted.
Ensure the design strictly follows the provided specifications, as they are essential requirements. Deviating from the specifications is considered unprofessional. If there are concerns about the validity of the description, it is important to promptly discuss them with me. I am open to clarifying any parts of the description that may seem unclear. Also, make sure to frequently check LMS for any updates or additional details
4.2 Clinic Management
The Sharp Vision Eye Clinic needs a management system to coordinate appointment scheduling and handle patient billing within its large-scale surgical facility.
Each treatment room in the clinic has a unique number (N 3). Similarly, each eye-surgeon 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 eye surgeon has one or two minor surgeries in which they work. Please note that more than one surgeon can use the same room at different times. However, a surgeon will only use one room at a time.
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 are their name (C), address (A) and one to three contact phone numbers (S 14). A customer is responsible for paying the bills for one or more patients, which may include their own bill or their family members’ bill. 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 Date of Birth (D) and their Medicare number (S 12) are also recorded, along with which customer is currently responsible for paying for their treatment.
There are various services available in the clinic. 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 surgeons who give this service. Most surgeons 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 surgeon. 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 surgeon.) The patient is allowed to schedule appointments with different surgeons on the same day if needed. However, the patient cannot have multiple appointments with the same surgeon on the same day.
At the end of an appointment, an invoice is generated. This invoice also serves as an entry in the patients' treatment history. Each invoice also has a unique invoice number (N 5), it identifies the patient and the surgeon 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.
Operations and Questions your model should be able to answer
. Ability to book a new appointment
. Who has booked the most appointments?
. Find the surgeon who provides the service X
. Which service is the most/least popular?
. Which surgeon has the most appointments?
. Which treatment is most popular?
. List all the appointments in a particular month
. Which eye surgeon has the least number of patients?
. Which service has made the clinic the most money?
. List the clients alongside their contact details who have outstanding amounts.
. Which surgeon has offered the maximum number of services?
. What service is required for appointment X?