The Case Study: Zen Chiropractic Clinic

After reviewing all the designs, defining the scope and long deliberations with Dr Zen Fuller, the design of the database has been finalised.

For this assignment you will populate these tables with appropriate test data and write the SQL queries below. Penalties will apply to queries that use subqueries and views unnecessarily. The schema file for creating this model is available in the archive ZEN-ass2-schm1509.zip - this file creates the above tables, their keys (primary and foreign) and populates the studio, rating and genre tables - you should read this schema carefully so that you are aware of the meaning of the various attributes. You must not alter the schema file in any manner, it must be used as supplied. Use the data supplied in the INSURANCE, PATIENT, PRODUCT and CLASSIFICATION tables as loaded by this file, do not add further rows.

You may need to rerun the schema, especially when you have been experimenting with your solutions and may have corrupted the database unintentionally. If you suspect that there might be such problems, rerun the schema.

Implementation of the Database and Manipulation of Data

1. Using the supplied schema file create the tables for ZC2and insert values into the all non-populated tables.
Load the non-populated tables with test data. You should create a single script which will insert, as a minimum, the following sample data -

• 8 services.

• 15 records for the remaining non-populated tables.

The script should contain a single COMMIT statement as the last line of the script, i.e. all inserts should be treated as a single transaction. The data should be structured in such a way that once it has been inserted and the "commit" SQL command run, that the database is in a consistent state. Design the test data so that you get output for the SQL queries specified below. Queries that are correct and do not produce output using your test data will lose 50% of the marks allocated so you should carefully check your test data and ensure it thoroughly validates your SQL queries.

For the Tasks 2-5, the SQL scripts must correctly manage transactions and only use sequences in the AUTO_INCREMENT option to generate primary keys (under no circumstances may a primary key value be hard coded as a number). You should also ensure that any related data impacted by your script actions are correctly managed.

2. A new patient Cecilia Green of 26 Wellington Rd Clayton Victoria 3168, phone 041299032123 has made an appointment for consultation on 28 May 2014; Scheduled start time: 10 am.

Include this new patient and her appointment into the ZC2 database.

3. On the day of the consultation, Cecilia receives her consultation on the scheduled time. Zen gives Cecilia a spinal adjustment after diagnosing that her back pain as a result of improper sitting posture. Zen also recommends 2 new products - OzBee Royal Jelly (code: P007) and Organic Relax Massage Oil (code: P004) to Cecilia. She is happy to buy 2 bottles of each product.

Include the details of this consultation in the database. If the service described above does not exist in the service table, you will need to include it.

4. Just before Cecilia consultation is completed, Zen receives a call from his clerk telling him the supplier has just called back product P004 and the clerk will be removing this product from the database. Zen decides cancel Cecilia's order from the database.
Include the above changes in the database.

5. Record that Cecilia's consultation is completed at 11 am.

Querying of Database using SQL Statements

6. Display the full details for all patients - the name details (firstname and surname) should be shown in one column called 'Patient Name' and the address details (street, suburb, state and postcode) in one column called "Member Address'. (3 marks)

7. Display the full details for all patients who have a VIC or a QLD address. (3 marks)

8. Display the full details for all products in the ZC2 product table which have the word 'Nature' in their product name. (3 marks)

9. Zen is considering to increase the price of all the products by 2.5%. Display the code, name and the increased price of all products in ZC2.

10. Display the full details for the cheapestservice(s) provided by ZC2.

11. Display the code, name, classification number and classification description of services which are classified 1 or 3, and are cheaper than $100 per unit. Order the list such that the services which are more expensive are listed first.

12. Display the details of all consultations which products have been recommended to the patients but not purchased by them. Include the product code in the list and display in patient number order within consultation number order.

13. Report the average number of patients Zen sees per day.

14. Provide a list of all patients which are scheduled for tomorrow's consultations.

15. For all patients currently in the ZC2 system, display details about the patients covered by insurance(s) and patients not covered by any insurances:

• for each patient covered by insurance(s): display the string 'With insurance', the patient number, patient name, and the total number of insurances he/she is covered by, and

• for each patient covered by no insurance: display the string 'Without insurance', the patient number, patient name, and the total number of insurances he/she is covered by (as string ‘Not applicable').
Note that that the results from this listing should be displayed in a single result output.Hint: Make use of the "UNION" relational operator as part of your SQL statement.

16. List of all patients who had their consultation(s) more than 15 minutes later than their scheduled time. The list should show the patient number, name, consultation date, scheduled start time and actual start time. This display should be ordered with the longest waiting duration at the top of the list.

17. For a given patient, identified by a patient number and selected by the user, display the full details of all consultations that this patient has (patient name, consultation number, date, scheduled start date, actual start time, actual end time). If a consultation has yet to be completed at this point in time, it should be listed in the actual start time and actual end time columns as "incomplete consultation".

18. NC2 would like to reward its top product-purchasing patients by giving them discount of 20% for all products they will be purchasing in 2014. To assist the company in being able to identify these patients create a view of the patient details (patient number, patient name as one attribute, contact number and total amount spent on purchasing products) of those patients with a total product purchasing amount within 10% (inclusive) of the patient(s) with the maximum total product purchasing amount. The patients should be listed in descending order of the total product purchasing amount. For example if the total amount spent on purchasing products by any patient is $300, this report will show those patients who have a total amount spent between $270 and $300 inclusive.

Attachment:- zen-ass2-schm1509.rar