Create a view called todays_appointment_schedule (can only create views in your student database on the database...

      

Transcribed Image Text:

Create a view called "todays_appointment_schedule" (can only create views in your student database on the database server). The view should list all appointments for the current day. Include the appointment date and time (only show the hours and minutes), the doctor's name, the patient's name and phone number, and done status. Order the view into doctor's name, then order by the appointment time. Show a list of appointments for March 2023. 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. (Note: bonus 1 mark (not increasing total past 15 for this assignment) if you can cater for "any diagnosed" disease including no diagnoses for an appointment, i.e., no disease name) List all appointments made by patients in the suburb WINDSOR, during the 2022 year that have not been diagnosed with any diseases. Show the name of the patient and the date and time of the appointment. Display the name of the patient(s) and their age, who is/are the oldest and youngest on record in the clinic database who live in the state of Victoria. 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). Show the room number, name of room and the appointment usage count. 6. There has been some really bad appointment session at the clinic where multiple diseases have been diagnosed. Generate a list of all the appointments where the doctor has diagnosed more than 5 diseases. Show the full name of the patient and doctor (Add "Dr" title to start of doctor's full name), the date of the appointment, the room number in which the consultation was in and the number of diagnosed diseases during that appointment. Please show this data in doctor first name and then date of appointment order. 7. 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 10. Note: Zero is less than 10. 8. Show all the patients that have visited (i.e., a visit is ONE appointment) the clinic more than 150 times. Show the full name of the patient, also show the number of visits, number of different doctors seen, total number of tests ordered, unique diseases diagnosed and unique set of drugs prescribed for each patient. Please order it by surname. 9. The Victorian patients are being sent Easter cards. Clinic needs to create a Easter card address list. Generate a query that includes both patients and doctor's data. Show the ID (patientID or doctorID), concatenate a "P" in front of patientIDs and a "D" in front of doctorIDs, full name (add "Dr" title to start of given name for doctors), address, suburb, postcode. Sort list into suburb and then name order. Only select face2face patients that have been a patient (have an appointment) at the clinic in 2023, and only select face2face doctors that have seen a patient in the clinic in 2023. 10. List all female doctors, that are either active or have resigned, that have worked in the clinic between 2 and 10 years. Show the name of the doctor and the name of their supervisor and the years worked. 11. 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 and 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. 12. Choose one questions (from questions 1-11 of this assignment) and create a visualisation, using a tool like Excel, Power BI, Tableau or Orange. Attach the created image ONLY to your submission. The visualisation will be judged on how well it represents the question chosen, how clear the various axis and legends are label, and the appropriateness of the title of the visualisation. Feel free to tweak the output of the query to improve visualisation you want to generate. (3 marks) 13. Produce a report of your own design and write a query to solve it. Marks will be awarded for report design (i.e., How useful is the report), complexity of the query and originality. Please provide: a) Business question b) SQL query c) Visualisation (using a tool like Excel, Tableau or Orange) - attached image only to submission Note: you cannot use the MySQL LIMIT operator because it is NOT standard SQL. CASE STUDY Medical Doctor's SuperClinic (MDS) A SuperClinic locate in the northern suburbs has various information system solution to support the operation of the business. One of the information systems is a structured database that handles the day-to-day function of the clinic. It stores the information about appointment scheduled between doctors and patients, information about referrals, requests for test(s) to help diagnose medical conditions and some notes. The clinic has a reception and waiting area with a television showing the daily soap series. There are also 4 examination rooms to see patients, along with various storage areas, a staff room, toilets for public, patients and staff, and a general nurse/lab area. 31'0" DOE Business Storage -70' 0"- Drug & Supplies Triage Nurse Lab Patient RR Doctor Office Staff Bath Storage Employee Lounge 3 621-919) Exam Exam Exam Phones, Server, Exam Storage Relational Model DOCTOR DoctorID Surname Given Dob Sex Joined Resigned Address Suburb Postcode Phone SupervisorID PATIENT APPOINTMENT PatientID AppointmentID Surname Given Dob Sex Phone dateOfAppointment timeOfAppointment Done PatientID DoctorID Occupation RoomNo BloodType Address Suburb State Postcode Email ROOM RoomNo RoomName Level Facility* DISEASE DiseaseID diseaseName ClassificationID TEST TestID Name DRUG DrugID itemCode drugName longName Restriction Repeats manufacturerCode REQUEST RequestID AppointmentID requestType DiseaseID TestID DrugID CLASSIFICATION ClassificationID Name MANUFACTURER manufacturerCode Name Address Phone Entity-Relationship Diagram ROOM TEST ses PATIENT mas APPOINTMENT REQUEST DRUG belong CLASSIFICATION attend supervises BR: request type is T-test; D-diagnosis; P-prescription; these dictates which entity is connected TEST/DISEASE/DRUG DISEASE DOCTOR CLASSIFICATION Kas Create a view called "todays_appointment_schedule" (can only create views in your student database on the database server). The view should list all appointments for the current day. Include the appointment date and time (only show the hours and minutes), the doctor's name, the patient's name and phone number, and done status. Order the view into doctor's name, then order by the appointment time. Show a list of appointments for March 2023. 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. (Note: bonus 1 mark (not increasing total past 15 for this assignment) if you can cater for "any diagnosed" disease including no diagnoses for an appointment, i.e., no disease name) List all appointments made by patients in the suburb WINDSOR, during the 2022 year that have not been diagnosed with any diseases. Show the name of the patient and the date and time of the appointment. Display the name of the patient(s) and their age, who is/are the oldest and youngest on record in the clinic database who live in the state of Victoria. 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). Show the room number, name of room and the appointment usage count. 6. There has been some really bad appointment session at the clinic where multiple diseases have been diagnosed. Generate a list of all the appointments where the doctor has diagnosed more than 5 diseases. Show the full name of the patient and doctor (Add "Dr" title to start of doctor's full name), the date of the appointment, the room number in which the consultation was in and the number of diagnosed diseases during that appointment. Please show this data in doctor first name and then date of appointment order. 7. 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 10. Note: Zero is less than 10. 8. Show all the patients that have visited (i.e., a visit is ONE appointment) the clinic more than 150 times. Show the full name of the patient, also show the number of visits, number of different doctors seen, total number of tests ordered, unique diseases diagnosed and unique set of drugs prescribed for each patient. Please order it by surname. 9. The Victorian patients are being sent Easter cards. Clinic needs to create a Easter card address list. Generate a query that includes both patients and doctor's data. Show the ID (patientID or doctorID), concatenate a "P" in front of patientIDs and a "D" in front of doctorIDs, full name (add "Dr" title to start of given name for doctors), address, suburb, postcode. Sort list into suburb and then name order. Only select face2face patients that have been a patient (have an appointment) at the clinic in 2023, and only select face2face doctors that have seen a patient in the clinic in 2023. 10. List all female doctors, that are either active or have resigned, that have worked in the clinic between 2 and 10 years. Show the name of the doctor and the name of their supervisor and the years worked. 11. 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 and 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. 12. Choose one questions (from questions 1-11 of this assignment) and create a visualisation, using a tool like Excel, Power BI, Tableau or Orange. Attach the created image ONLY to your submission. The visualisation will be judged on how well it represents the question chosen, how clear the various axis and legends are label, and the appropriateness of the title of the visualisation. Feel free to tweak the output of the query to improve visualisation you want to generate. (3 marks) 13. Produce a report of your own design and write a query to solve it. Marks will be awarded for report design (i.e., How useful is the report), complexity of the query and originality. Please provide: a) Business question b) SQL query c) Visualisation (using a tool like Excel, Tableau or Orange) - attached image only to submission Note: you cannot use the MySQL LIMIT operator because it is NOT standard SQL. CASE STUDY Medical Doctor's SuperClinic (MDS) A SuperClinic locate in the northern suburbs has various information system solution to support the operation of the business. One of the information systems is a structured database that handles the day-to-day function of the clinic. It stores the information about appointment scheduled between doctors and patients, information about referrals, requests for test(s) to help diagnose medical conditions and some notes. The clinic has a reception and waiting area with a television showing the daily soap series. There are also 4 examination rooms to see patients, along with various storage areas, a staff room, toilets for public, patients and staff, and a general nurse/lab area. 31'0" DOE Business Storage -70' 0"- Drug & Supplies Triage Nurse Lab Patient RR Doctor Office Staff Bath Storage Employee Lounge 3 621-919) Exam Exam Exam Phones, Server, Exam Storage Relational Model DOCTOR DoctorID Surname Given Dob Sex Joined Resigned Address Suburb Postcode Phone SupervisorID PATIENT APPOINTMENT PatientID AppointmentID Surname Given Dob Sex Phone dateOfAppointment timeOfAppointment Done PatientID DoctorID Occupation RoomNo BloodType Address Suburb State Postcode Email ROOM RoomNo RoomName Level Facility* DISEASE DiseaseID diseaseName ClassificationID TEST TestID Name DRUG DrugID itemCode drugName longName Restriction Repeats manufacturerCode REQUEST RequestID AppointmentID requestType DiseaseID TestID DrugID CLASSIFICATION ClassificationID Name MANUFACTURER manufacturerCode Name Address Phone Entity-Relationship Diagram ROOM TEST ses PATIENT mas APPOINTMENT REQUEST DRUG belong CLASSIFICATION attend supervises BR: request type is T-test; D-diagnosis; P-prescription; these dictates which entity is connected TEST/DISEASE/DRUG DISEASE DOCTOR CLASSIFICATION Kas