| Instructions | This is an independent project. Each student is responsible to maintain the integrity of their own work. Each student is required to upload: A copy of your assignment in .SQL Format for the Transact-SQL statements you create during this assignmentA copy of your assignment output in Microsoft Excel (.xlsx) that will contain two WorksheetsSheet one will be called “Cover” and will include the coversheet information belowSheet two will be called “Data Output” and will include the output from the required sections of your assignment Please submit your assignment based on the following scenario SCENARIO: Previously you helped the Family Doctor Practice normalize its data to be used in a database. From the normalized data, tables have been created with the initial data loaded into them. However since then more appointments have been booked, new patients have been taken in and billing has been completed. They need your help setting up tools to add patients, make, complete and bill appointments! What you know (since Assignment #1): Patients have been arriving late for appointments causing issues with next appointment and staff. The practice wants to track when the Patient is latePatients have been asking for reminders to be sent to their email address3 new patients have been registered with the practice but aren’t in the database yetMary Sue, DOB: Oct 4, 1969, Health Card: 129483447, Address: 184 King St S, Kitchener, ON, N2I 2M4, 519-884-8000, Email: (none)Chris McMann, DOB: Nov 11, 1981, Health Card 234144, Address: 38 Pinebush Rd, Cambridge, ON N2R 1M1, 519-667-8500, Email: cmcmann@rogers.comKaren Clinton, DOB: Dec 5, 2000, Health Card 1234555, Address: 389 Erb St E, Waterloo, ON N4R 4O4, 519-588-1334, Email: kclinton@hotmail.comAppointments 10013 and 10014 have completed10013 was on-time, took 20 minutes, Notes: “Hands were burnt during cooking accident, bandaged up the scars and sent home for rest.” Prescribed Tylenol 3. Billed $1,200, on April 4, 2019 @ 6:00 pm10014 was late, took 30 minutes, Notes: “Patient is complaining about a sore foot when walking in heels. Noticed quite a bit of swelling and ask patient to use different shoes.”, No medication prescribed. Billed $1,800, on April 4, 2019 @ 6:00 pmThe following Appointments have been booked and billed:10015 for April 3 @ 10:00 am, Patient Mary Sue, Visit Reason: Large Cut on Foreman, Needs a ReminderWas on-time, took 20 minutes, Notes: “Patient hurt herself during crafting. Stitched up her forearm and sent her home for rest.” Prescribed Tylenol 3. Billed $1,200 on April 5, 2019 @ 6:05 pm10016 for April 3 @ 10:30 am, Patient Karen Clinton, Visit Reason: Physical, Needs a ReminderWas on-time, took 45 minutes, Notes: “Patient’s blood pressure seemed high so ran a few tests and ran some blood work. Everything else in the normal range.” No prescription. Billed $2,400 on April 5, 2019 @ 6:05 pmThe Doctor has been asking for some information on how the practice is doing. They want to know the following:What is the total billing for April thus far?What is the average visit length?What is the #1 most prescribed medication?Reception has been asking for a way to input a few things into the databaseAdd a new patient to the systemAdd a new appointment when patients bookChange an appointment when a patient has to rescheduleCancel an appointment when a patient cancelsComplete an appointment after the patient finishes the appointmentBill an appointment at the end of the day Your Assignment: Run the “Assignment 2 – Base Database.sql” to build your databaseTo save your progress at any time, you can either Backup your database (and later restore it when you are ready to continue working on the assignment or install SQL Server on your home machine – see your professor for more detail)Complete the following in the listed order below and save in a .sql script file (I want to see the Transact-SQL statements that you used to for all of the points below) *Please refer to the “What you know section” above for the details that go along with each point. For example when I mention add the three new patients from above, I am referring to the patients Mary Sue, Chris McMann and Karen Clinton from the points above Add PatientEmail column to the Patients tableAdd OnTime column to the AppointmentDetails tableAdd new Stored Procedures to do the following (All Procedures must use Transactions and Try / Catch logic from Week 3, Class 1):INSERT a new patient to the Patient table called “AddPatient”This will set all of the fields in the Patient tableMake sure to look-up the ProvinceID from the Province table based on the Province value entered in the procedureINSERT a new appointment to the Appointment table called “AddAppointment”This will need to set all of the fields in the Appointment tableMake sure to look-up the PatientID from the Patient table based on the Health Card #UPDATE an appointment in Appointments table taking in only the AppointmentNumber and AppointmentDate to change the date of the appointment called “ChangeAppointment”DELETE appointment in Appointment table taking in only the AppointmentID to delete the appointment from the table called “DeleteAppointment”INSERT appointment details upon completion of the appointment in the AppointmentDetail table called “CompleteAppointment”Do not set the Billing fields in this procedureMake sure you look-up the MedicationID from the Medication based on the name entered in the procedureUPDATE billing details in the AppointmentDetail based on the AppointmentNumber called “BillAppointment”Only set the Billing fields in this procedureAdd the three new patients from above using the new stored procedure Complete and Bill appointments 10013 and 10014 from the above using the new stored proceduresCreate, Complete and Bill new appointments for 10015 and 10016 using the new stored proceduresWrite SELECT statements to find out (copy the output with Headers to the Excel output file):The SUM of the billing for April thus farThe AVG of the visit length for all completed appointmentsThe COUNT of all prescribed medication (by ID) in descending order where one is was prescribedHint: If you want to exclude NULL values you should use IS NOT NULL in the WHERE clause This assignment will evaluate your ability to: Execute Data Definition Language (DDL) through Transact-SQLExecute Data Manipulation Language (DML) through Transact-SQLExecute Data Query Language (DQL) through Transact-SQL STUDENT INFORMATION The focus of this assignment is to test your understanding of the three basic types of SQL Commands. The assignment will challenge you to think like both the receptionist and the doctor to think about real-life information requirements for both providing care and reviewing operations. You must provide solutions for this in a .sql script file for evaluation of the SQL commands that you’ve executed. Put all SELECT statement results into Excel format (column headers must be included). To copy from Microsoft SQL Server please follow these steps: Execute your SELECT statement and the Results section will appearLeft-click the empty box in the top left portion of the Results section, this will highlight all rows in the result setRight-click the highlighted area and select “Copy with Headers” options from the pop-up menuPaste the data in the Microsoft Excel spreadsheet (Data Output worksheet) Document should consider/follow Conestoga APA formatting guidelines. Document MUST include a cover page with the following details: Assignment #2 – Family Doctor Practice Database ModificationsYour full name – Student #Course numberProfessor nameDate assignment postedDate assignment due Each assignment must be upload via the eConestoga portal by the due date. Posted course evaluation and submission rules strictly apply. UPLOAD FILE NAME (Please name the file(s)): 8880-ASN2-{date}-YourName |
0 comments