Medical practice data model assessment

Scenario

You will use the medical practice data model provided for this assessment. The data model for this assessment consists of the Medical practice database data dictionary and the Medical practice database crow’s foot entity-relationship diagram (ERD).

Part 1: Create the database

To complete this part of the assessment, you are required to complete the practical tasks as described. These tasks require you to develop the Structured Query Language (SQL) code that addresses the requirements of each task.

These practicals may be observed by your Assessor. The code developed by you in each task will be used by your Assessor to assess you, and you must submit your code as evidence for this assessment.

Download and unzip the resource folder (Cl_Database_AE_Sk3of3_Appx1.zip) to access the files you will need for this part.

Note: You will create one script file for Part 1: Task 1, then append to the same file the SQL script for tasks 2 and 5.

1. Develop a script using Data Definition Language (DDL) elements of Structured Query Language (SQL) to create a database with the name MedicalPractice. Save your script file as yourFirstname_yourSurname_CreateMedicalPracticeDatabase.sql.
2. Using DDL elements of SQL, write the script to create each of the tables described in the medical practice data model, appending to the end of the script file. Your script must precisely match the details specified in the data model and must include the creation of the:
   1. tables, using the table names specified by the data model
   2. columns, using the column names specified by the data model
   3. column data types using the data types and sizes specified by the data model
   4. primary key constraints specified by the data model
   5. foreign key constraints required to implement the relationships specified by the data model
   6. unique index constraints required to implement the alternate keys specified by the data model. Note the following alternate keys exist in the data model:

Table 4 Alternate Key

3. Execute your script on the Database Server. Check and ensure that your script executes without errors and that it creates the database, tables and constraints as defined by the Medical Practice Data Model provided in the table. If the database has been created successfully, you can now proceed to the next task and populate the database with data. Otherwise, correct any errors in your script, before you proceed.
4. Test the database.
   • Create a test plan using the Software Test Report template (GE_Software-Test-Report_template.docx) to test the StationaryOrder sample database to ensure it matches the requirements. Include:
     • sample test data
     • expected outcomes.
   • Test and debug the database using your test plan and record the test results:
     • Correct any errors and include screenshots.
     • Write an outline of the errors that you corrected.
5. Write and execute the script to populate the database tables using the supplied data files:
   • AppointmentData.csv
   • AvailabilityData.csv
   • PatientData.csv
   • PractitionerData.csv
   • PractitionerTypeData.csv
   • WeekDaysData.csv.
6. When you have completed Tasks 1-5:
   • confirm with your Assessor that you have completed this part of the assessment
   • ask for feedback and make any changes as required
   • submit your Software Test Report to your Assessor
   • ensure your Assessor signs your completed Software Test Report. This may be printed and signed, an electronic signature or a reply email with sign off.

Submit the following for Part 1:

• the SQL file containing scripts for tasks 1, 2 and 5
• the completed and signed Software Test Report.

• Part 2: Query the database

  To complete this part of the assessment, you are required to complete the practical tasks described. These tasks require you to develop the SQL code that addresses the requirements of each task.

  These practicals may be observed by your Assessor. The code developed by you in each task will be used by your Assessor to assess you, and you must submit your code as evidence for this assessment.

  Task 1

  In Task 2, you will be building database queries. Based on recommendations from your Teacher/Assessor, confirm the necessary tools and environment/database platform that you are required to use to query the database.

  Table 5 Tools and environment

  Type	Tool/environment/platform
  Operating System	Windows 10
  Relational Database Management System	mysql
  Query tool	SQLyog

  Task 2

  Write the SQL query to provide the information requested for each of the following requirements.

  To check your results, you can make estimations of your answer using a suitable tool, such as a spreadsheet or calculator and compare your estimate to the answer from your query.

  Note: You will create one script file that includes all the queries for Task 2. Save your script file as yourFirstname_yourSurname_Queries.sql.

• List the first name and last name of female patients who live in St Kilda or Lidcombe.
• List the first name, last name, state and Medicare Number of any patients who do not live in NSW.
• List each patient's first name, last name, Medicare Number and date of birth. Sort the list by date of birth, listing the youngest patients first.
• For each practitioner, list their ID, first name, last name, the total number of days and the total number of hours they are scheduled to work in a standard week at the Medical Practice. Assume a workday is nine hours long.
• List the Patient's first name, last name and the appointment date and time, for all appointments held on 18/09/2019 by Dr Anne Funsworth.
• List the ID and date of birth of any patient who has not had an appointment and was born before 1950.
• List the patient ID, first name, last name and the number of appointments for patients who have had at least three appointments. List the patients in 'number of appointments' order from most to least.
• List the first name, last name, gender, and the number of days since the last appointment of each patient and 23/09/2019.
• List the full name and full address of each practitioner in the following format exactly.
• Dr Mark P. Huston. 21 Fuller Street SUNSHINE, NSW 2343
• Make sure you include the punctuation and the suburb in upper case.
• Sort the list by last name, then first name, then middle initial.
• List the patient id, first name, last name and date of birth of the fifth oldest patient(s).
• List the patient ID, first name, last name, appointment date (in the format 'Tuesday 17 September, 2019') and appointment time (in the format '14:15 PM') for all patients who have had appointments on any Tuesday after 10:00 AM.
• Create an address list for a special newsletter to all patients and practitioners. The mailing list should contain all relevant address fields for each household. Note that each household should only receive one newsletter.
• Submit the following for Part 2:
• this assessment document with Task 1 completed

• Part 3: Use views and stored procedures

To complete this part of the assessment, you are required to complete the practical tasks described. These tasks require you to develop the SQL code that addresses the requirements of each task.

These practicals may be observed by your Assessor. The code developed by you in each task will be used by your Assessor to assess you, and you must submit your code as evidence for this assessment.

Note: You will create one script file that includes all the queries for Part 3. Save your script file as yourFirstname_yourSurname_ViewsSP.sql.

Tasks 1

• Create a view (called vwNurseDays) with the name and phone details of any nurse (registered or not) and the days that they work. Execute the SQL statements to create the view.
• Using your view, write a query to retrieve the name and phone number details of all nurses who are scheduled to work on a Wednesday.
• Create a view (called vwNSWPatients) that contains all patient details for patients whose address is in NSW. Execute the SQL statements to create the view.
• Create a stored procedure (called spSelect_vwNSWPatients) to retrieve all records and columns from vwNSWPatients in postcode order ascending. Execute the stored procedure.
• Create a stored procedure (called spInsert_vwNSWPatients) to insert a new record into vwNSWPatients, using parameters for all relevant data. Execute the stored procedure inserting a record for a new patient named Mr Mickey M Mouse from 1 Smith St, Smithville, NSW 2222.
• Create a stored procedure (called spModify_PractitionerMobilePhone) using the Practitioner table to change a practitioner’s mobile phone number, using the Practitioner ID and the new mobile number as parameters. Execute the stored procedure to change Hilda Brown’s mobile number to 0412345678.
• Run a query to verify that the record has been updated in the Practitioner table.
• Create a stored procedure (called spModify_PractitionerMobilePhone) using the Practitioner table to change a practitioner’s mobile phone number, using the Practitioner ID and the new mobile number as parameters. Execute the stored procedure to change Hilda Brown’s mobile number to 0412345678.
• Manipulate the Patient table to add a new column that will store a date value which is the last date they made contact. The default value should be the date of record creation. Name the new column LastContactDate. Execute the statement to create the new column.
• Create a trigger on the Appointment table that will update LastContactDate on the Patient table each time a new record is added to the Appointment table. The value of the LastContactDate should be the date the record is added. Name the trigger tr_Appointment_AfterInsert.
• Delete the view vwNurseDays from the database.
• Delete the stored procedure spSelect_vwNSWPatients from the database.

• Submit the following for Part 3:

• the SQL file containing scripts for Task 3.