Fluent Essays

Please follow Instructions INFO8880 – Database Applications Enterprise Content Management (ECM) Title Assignment #2 : INFO8880 Course Weight See Instructional Plan Instructions This is an independent project. Each student is responsible to maintain the integrity of their own work. Each student is required to upload: 1. A copy of your assignment in .SQL Format for the Transact-SQL statements you create during this assignment 2. A copy of your assignment output in Microsoft Excel (.xlsx) that will contain two Worksheets a. Sheet one will be called “Cover” and will include the coversheet information below b. Sheet 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 late · Patients have been asking for reminders to be sent to their email address · 3 new patients have been registered with the practice but aren’t in the database yet · Mary 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: [email protected] · Karen Clinton, DOB: Dec 5, 2000, Health Card 1234555, Address: 389 Erb St E, Waterloo, ON N4R 4O4, 519-588-1334, Email: [email protected] · Appointments 10013 and 10014 have completed · 10013 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 pm · 10014 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 pm · The 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 Reminder · Was 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 pm · 10016 for April 3 @ 10:30 am, Patient Karen Clinton, Visit Reason: Physical, Needs a Reminder · Was 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 pm · The 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 database · Add a new patient to the system · Add a new appointment when patients book · Change an appointment when a patient has to reschedule · Cancel an appointment when a patient cancels · Complete an appointment after the patient finishes the appointment · Bill an appointment at the end of the day Your Assignment: 1. Run the “Assignment 2 - Base Database.sql” to build your database 1.1. To 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) 2. 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 2.1. Add PatientEmail column to the Patients table 2.2. Add OnTime column to the AppointmentDetails table 2.3. Add new Stored Procedures to do the following (All Procedures must use Transactions and Try / Catch logic from Week 3, Class 1): 2.3.1. INSERT a new patient to the Patient table called “AddPatient” 2.3.1.1. This will set all of the fields in the Patient table 2.3.1.2. Make sure to look-up the ProvinceID from the Province table based on the Province value entered in the procedure 2.3.2. INSERT a new appointment to the Appointment table called “AddAppointment” 2.3.2.1. This will need to set all of the fields in the Appointment table 2.3.2.2. Make sure to look-up the PatientID from the Patient table based on the Health Card # 2.3.3. UPDATE an appointment in Appointments table taking in only the AppointmentNumber and AppointmentDate to change the date of the appointment called “ChangeAppointment” 2.3.4. DELETE appointment in Appointment table taking in only the AppointmentID to delete the appointment from the table called “DeleteAppointment” 2.3.5. INSERT appointment details upon completion of the appointment in the AppointmentDetail table called “CompleteAppointment” 2.3.5.1. Do not set the Billing fields in this procedure 2.3.5.2. Make sure you look-up the MedicationID from the Medication based on the name entered in the procedure 2.3.6. UPDATE billing details in the AppointmentDetail based on the AppointmentNumber called “BillAppointment” 2.3.6.1. Only set the Billing fields in this procedure 2.4. Add the three new patients from above using the new stored procedure 2.5. Complete and Bill appointments 10013 and 10014 from the above using the new stored procedures 2.6. Create, Complete and Bill new appointments for 10015 and 10016 using the new stored procedures 2.7. Write SELECT statements to find out (copy the output with Headers to the Excel output file): 2.7.1. The SUM of the billing for April thus far 2.7.2. The AVG of the visit length for all completed appointments 2.7.3. The COUNT of all prescribed medication (by ID) in descending order where one is was prescribed 2.7.3.1. Hint: 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-SQL · Execute Data Manipulation Language (DML) through Transact-SQL · Execute 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: 1. Execute your SELECT statement and the Results section will appear 2. Left-click the empty box in the top left portion of the Results section, this will highlight all rows in the result set 3. Right-click the highlighted area and select “Copy with Headers” options from the pop-up menu 4. Paste 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 Modifications · Your full name – Student # · Course number · Professor name · Date assignment posted · Date 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 Helpful Resources 1. Course book 2. Classmates / Teammates 3. Assignment 1 - Solution.xlsx 4. Assignment 1 – Raw Patient Data.xlsx 5. Assignment 2 - Base Database.sql Due Date See Instructional Plan Sheet1 Appointment # Patient First Name Patient Last Name Health Card # Patient DOB Patient Gender Patient Street Patient City Patient Province Patient Postal Code PatientPhone Appointment Date Reminder Required? Reason For Visit Visit Length Medication Prescribed Visit Notes Bill Amount Bill Date 10004 Jennifer Roberts 12345352 1-Sep-88 F 123 West St Kitchener ON N1N 1N1 519-555-1423 4/1/19 13:20 Yes Cold 20 Mins Antibiotics Heavy cough, she has had symptoms for more than a week $ 1,200.00 4/4/19 18:00 10005 Bob Smith 942384098 4-Aug-70 M 456 East St Kitchener ON N1N 1N1 519-432-4414 4/1/19 13:50 No Knee Pain 30 Mins Suggested stretching exercises and using heat at night $ 1,800.00 4/4/19 18:00 10006 Henry Stevens 948114 5-Jan-64 M 789 South St Cambridge ON N1N 1N1 519-456-5253 4/1/19 14:20 Yes Eye Infections 15 Mins Eye Drops Has abnomal redness in left eye. Complained about dryness for last month $ 1,000.00 4/4/19 18:00 10007 Susan Corners 9832098 22-Mar-88 F 774 North St Cambridge ON N1N 1N1 519-989-9342 4/1/19 14:50 Yes Physical 30 Mins Routine check-up. Is in great health and will be back next year for follow-up $ 1,800.00 4/4/19 18:00 10008 Harriet Spyers 764234 11-May-00 F 84 Main St W Waterloo ON N1N 1N1 519-758-0742 4/1/19 15:20 Yes Pregnancy Follow-up 15 Mins Folic Acid Patient is approximately 8 Weeks Pregnant $ 1,000.00 4/4/19 18:00 10009 Fred Sommers 62378213 1-Feb-94 M 99 Homer Watson Blvd Kitchener ON N1N 1N1 519-444-1435 4/1/19 15:50 No Headaches 20 Mins Migraine Medication Patient is experiencing migraines on a regular basis $ 1,200.00 4/4/19 18:00 10010 Ken Reid 14341441 1-Mar-01 M 100 River Rd Cambridge ON N1N 1N1 519-545-2435 4/1/19 16:20 Yes Ankle 15 Mins Tylenol 3 Patient injuried Ankle while playing sports $ 1,000.00 4/4/19 18:00 10011 Janet Wilson 124324234 4-Jun-55 F 342 Erb St Waterloo ON N1N 1N1 519-232-8584 4/1/19 16:50 Yes Flu 20 Mins Patient has had the flu for 3 days. Sent home to get rest with Doctor's note $ 1,200.00 4/4/19 18:00 10012 Shannon Terry 63634435 24-Mar-87 F 1004 Weber St N Waterloo ON N1N 1N1 519-145-8954 4/1/19 17:20 Yes Arm 15 Mins Tylenol 3 Patient injured arm while cleaning gutters $ 1,000.00 4/4/19 18:00 10013 Ted Winslow 3432414 6-May-87 M 3484 Conestogo Blvd Waterloo ON N1N 1N1 519-998-7432 4/2/19 9:20 Yes Hands 10014 Cheryl Carter 348230984 20-Apr-00 F 123 Doon Village Rd Kitchener ON N1N 1N1 519-525-1235 4/2/19 9:50 Yes Foot Sheet2 Sheet3