Description
Read the following questions carefully.You will be asked to specify SQL queries to answer thesequestions.CASE STUDYRelational ModelDOCTORPATIENTAPPOINTMENTROOMDISEASEDIAGNOSEDDoctorIDPatientIDAppointmentIDRoomNoDiseaseIDDiagnosedIDSurnameSurnamedateOfAppointmentNameNameDiseaseIDGivenGiventimeOfAppointmentLevelClassificationIDAppointmentIDDobDobDoneFacility*SexSexPatientIDJoinedPhoneHomeDoctorIDResignedPhoneWorkRoomNoCLASSIFICATIONAddressPhoneFaxClassificationIDSuburbAddressNamePostcodeSuburbPhoneStatesupervisorIDPostcodeEntity-Relationship DiagramsupervisesPATIENTAPPOINTMENTDOCTORDISEASEhasattenddiagnosedCLASSIFICATIONYou will be working with a set of tables for a Doctor’s Clinic.You can access these tables byusing the CLINIC database on the mysql server (mo.bf.rmit.edu.au). You are to prepare 12 SQLquery statements that will provide answers to the following 12 requests for information.1 marks each1.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). Showthese appointments in date & time order.2.Create a view called “todays_appointments”. The view should list all appointments for thecurrent day. Include the appointment date and time, the doctor’s name, the patient’s nameand phone number, and done field. Order the view into doctor’s name, then order by theappointment time.3.List all appointments made by patients in the suburb RICHMOND, during the 2016 yearthat have not been diagnosed with any diseases. Show the name of the patient and the dateand time of the appointment.4.Display the name of the 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 timesthey have been used for an appointment (don’t count the appointments that have not beencompleted/done).6.Show number of patients in each suburb/postcode. Provide the SQL query and a geographicVISUALISATION of the data.7.List all disease names and a count of how many times each disease has been diagnosed atthe clinic. The list is of rarely identified diseases, so show only diseases that have adiagnosis count of less than 5. Note: Zero is less than 5. Provide the SQL query and a dataVISUALISATION of the results.8.Show all the patients that have visited the clinic more than 12 times. Show at least thesurname and given name of the patient.9.Need to create a Christmas card address list. Generate a query that includes both patientsand doctors data. Show the ID (patientID or doctorID), concatenate a “P” in front ofpatientIDs and a “D” in front of doctorIDs, address, suburb, postcode. Sort list into suburband then name order. Only select patients that have been a patient (have an appointment) atthe clinic in 2016, and only doctors that have seen a patient in the clinic in 2016.2 marks each10.List all doctors, that are currently active doctors of the clinic, that are female and joined inthe last four (4) years. Show the name of the doctor and the name of their supervisor.11.The following two queries are related.a)Create a view that shows a count of appointments that each doctor has attended. Theview should have the following fields: doctor’s id, doctor’s name, & count ofappointments.b)Using the view in the previous question, show the doctor that has the minimum andmaximum number of appointments. Show the name of the doctor.12.Produce a report of your own design, write a query to solve it and a visualisation.Markswill be awarded for report design (ie. How useful is the report), complexity of the query andoriginality and visualisation effectiveness in conveying the result. Provide BusinessQUESTION and SQL solution and data VISUALISTION of the results
