Read the following questions

School of Business I.T. Semester 2 – 2018
Business Data Management and Analytics Page 1 of 3
SCHOOL OF BUSINESS I.T. & LOGISTICS
Business Data Management and Analytics
Assignment 1 – SQL Queries – Part B
Due Date: Thursday 6th September, 2018
SPECIFICATIONS
Read the following questions carefully. You will be asked to specify SQL queries to answer these
questions.
CASE STUDY
Relational Model

DOCTOR	PATIENT	APPOINTMENT	ROOM	DISEASE	DIAGNOSED
DoctorID	PatientID	AppointmentID	RoomNo	DiseaseID	DiagnosedID
Surname	Surname	dateOfAppointment	Name	Name	DiseaseID
Given	Given	timeOfAppointment	Level	ClassificationID	AppointmentID
Dob	Dob	Done	Facility*
Sex	Sex	PatientID
Joined	PhoneHome	DoctorID
Resigned	PhoneWork	RoomNo	CLASSIFICATION
Address	PhoneFax	ClassificationID
Suburb	Address	Name
Postcode	Suburb
Phone	State
supervisorID	Postcode

Entity-Relationship Diagram

super	vises
DOCTOR

PATIENT

APPOINTMENT

DISEASE

has
attend
diagnosed
in

CLASSIFICATION

ROOM uses
School of Business I.T. Semester 2 – 2018
Business Data Management and Analytics Page 2 of 3
QUESTIONS
You will be working with a set of tables for a Doctor’s Clinic. You can access these tables by
using the CLINIC database on the mysql server (mo.its.rmit.edu.au). You are to prepare 11 SQL
query statements and 3 visualisations that will provide answers to the following 12 requests.
(1 mark)
1. Show a list of appointments for June 2016. Provide the doctor and patients full name,
disease name (if any diagnosed), date & time of appointment together in this format
‘YYYY-MON-DD HH:MM’, consultant room number used, and status (i.e. Done). Show
these appointments in date & time order.
2. Create a view called “todays_appointments”. The view should list all appointments for the
current day. Include the appointment date and time, the doctor’s name, the patient’s name
and phone number, and done field. Order the view into doctor’s name, then order by the
appointment time.
3. List all appointments made by patients in the suburb “VIEWBANK” (sometimes it has been
entered as “VIEW BANK”), during the 2016 year that have not been diagnosed with any
diseases. Show the name of the patient and the date and time of the appointment.
4. Display the name of the FEMALE patient(s), age and sex of who is/are the oldest and
youngest.
5. Show a list of the consultation room numbers, along with a count of the number of times
they have been used for an appointment (don’t count the appointments that have not been
completed/done).
6. List all disease names and a count of how many times each disease has been diagnosed at
the clinic. The list is of rarely identified diseases, so show only diseases that have a
diagnosis count of less than 3. Note: Zero is less than 3. Provide the SQL query and a data
VISUALISATION of the results.
7. Show all the patients that have visited the clinic more than 12 times. Show at least the
surname and given name of the patient.
8. Need to create a Christmas card address list. Generate a query that includes both patients
and doctors data. Show the ID (patientID or doctorID), concatenate a “PA” in front of
patientIDs and a “DO” in front of doctorIDs, address, suburb, postcode. Sort list into suburb
and then name order. Only select patients that have been a patient (have an appointment) at
the clinic in 2016, and only doctors that have seen a patient in the clinic in 2016.
9. List all doctors, that are currently active doctors of the clinic, that are female and joined in
the last four (4) years. Show the name of the doctor and the name of their supervisor.
School of Business I.T. Semester 2 – 2018
Business Data Management and Analytics Page 3 of 3
10. The following two queries are related.
a) Create a view that shows a count of appointments that each doctor has attended. The
view should have the following fields: doctor’s id, doctor’s name, & count of
appointments.
b) Using the view in the previous question, show the doctor that has the minimum and
maximum number of appointments. Show the name of the doctor.
(2 marks)
11. Choose two questions (from questions 1-10 of this assignment) and create a visualisation,
using Orange. Attach the created image ONLY to your submission.
Note: Make a good choice here, choose a question which is more appropriate/suited to
generate a visualisation.
(3 marks)
12. Produce a report of your own design and write a query to solve it. Marks will be awarded
for report design (ie. How useful is the report), complexity of the query and originality.
Please provide:
a) Business question
b) SQL query
c) Visualisation using Orange: attached image(s) (PLAN and OUTPUT) only to
submission.
REQUIREMENTS
11 SQL queries that answer the questions asked, based on the data model and
implemented database (on mo.its.rmit.edu.au) provided.
3 visualisations, using Orange, will be created and an image will be submitted
ASSESSMENT
Assessment of the data model will be based on the following areas (by the tutor):
• How well the query answers the questions, in relation to the case study provided.
• Understanding of data structure
• Efficiency and simplicity of resulting query
DEMONSTRATION
Selected students will be required to attend a demonstration session where they will be
asked to demonstrate and explain the queries they have written and to write several new
queries for the same database. Failure to explain the queries written or the inability to
write new queries will result in a FAIL mark being recorded for assignment 1.
SUBMISSION
• SQL queries (output not required) only.
• Screen dump of Orange PLAN and resulting Visualisation (can be in a word document
or separate image files)
• Assignment will be submitted online using CANVAS.