Execute Data Definition Language (DDL) through Transact-SQL

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: cmcmann@rogers.com

· Karen Clinton, DOB: Dec 5, 2000, Health Card 1234555, Address: 389 Erb St E, Waterloo, ON N4R 4O4, 519-588-1334, Email: kclinton@hotmail.com

· 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)

  1. 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

  1. Classmates / Teammates
  2. Assignment 1 – Solution.xlsx
  3. Assignment 1 – Raw Patient Data.xlsx
  4. Assignment 2 – Base Database.sql

Due Date See Instructional Plan

Calculate the price of your order

550 words
We'll send you the first draft for approval by September 11, 2018 at 10:52 AM
Total price:
$26
The price is based on these factors:
Academic level
Number of pages
Urgency
Basic features
  • Free title page and bibliography
  • Unlimited revisions
  • Plagiarism-free guarantee
  • Money-back guarantee
  • 24/7 support
On-demand options
  • Writer’s samples
  • Part-by-part delivery
  • Overnight delivery
  • Copies of used sources
  • Expert Proofreading
Paper format
  • 275 words per page
  • 12 pt Arial/Times New Roman
  • Double line spacing
  • Any citation style (APA, MLA, Chicago/Turabian, Harvard)

Benefits of our college essay writing service

  • 80+ disciplines

    Buy an essay in any subject you find difficult—we’ll have a specialist in it ready

  • 4-hour deadlines

    Ask for help with your most urgent short tasks—we can complete them in 4 hours!

  • Free revision

    Get your paper revised for free if it doesn’t meet your instructions.

  • 24/7 support

    Contact us anytime if you need help with your essay

  • Custom formatting

    APA, MLA, Chicago—we can use any formatting style you need.

  • Plagiarism check

    Get a paper that’s fully original and checked for plagiarism

What the numbers say?

  • 527
    writers active
  • 9.5 out of 10
    current average quality score
  • 98.40%
    of orders delivered on time
error: