Assignment 2
Database Implementation and Queries
Due Date:
Friday Week 11 (See Course Description for further date and time).
Objectives:
To analyse and comprehend a provided ER diagram and Database Schema
To implement a database based on the provided ER diagram and Database Schema
To manipulate the data in the database
To write required SQL statements to query the database
Project Specification
The Case Study: Zen Chiropractic Clinic (ZC2)
After reviewing all the designs, defining the scope and long deliberations with Dr Zen Fuller, the design of the database has been finalised. In this assignment you will use a ‘simplified’ database model as depicted by the ERD in Figure 1.
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 ZC2 and 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. (15 marks)
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. (4 marks)
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. (6 marks)
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 marks)
5. Record that Cecilia’s consultation is completed at 11 am. (2 marks)
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. (3 marks)
10. Display the full details for the cheapest service(s) provided by ZC2. (4 marks)
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. (5 marks)
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. (5 marks)
13. Report the average number of patients Zen sees per day. (5 marks)
14. Provide a list of all patients which are scheduled for tomorrow’s consultations.
(5 marks)
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. (6 marks)
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. (6 marks)
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”. (6 marks)
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. (8 marks)
Note: There are some general requirements when defining your select queries:
You are required to adhere to the following output formatting conventions:
• All monetary values should be printed with a dollar symbol ($), two digits after the decimal point, and with space for 7 digits before the decimal point
• You must use consistent and legible formatting in laying out your SQL queries. Include (brief) comments for any query or procedure that uses an -unusual- approach.
What to submit
An electronic copy of your assignment should be submitted through Moodle and should include a copy of your report, completed according to the Federation University Australia General Guide for the Presentation of Academic Work and the three files described in Insert (Q1), Manipulate (Q2-5) and Query (Q6-18) statements above.
Your document should include:
• A copy of the SQL statements required to:
o create the database and tables;
o insert sufficient sample data into each table to demonstrate that your queries work.
o a report of the results from running the SQL queries by using Copy/paste of their output.
• A bibliography containing a list of all resources used to complete the assignment. If no resources, apart from the course materials, have been used please indicate this.
Assessment Criteria
• How clear and well organised your presentation is. On the front page of your report you should include a list of acknowledgements of all people who have assisted you with this assignment including fellow students, along with a statement of completion.
• Adherence to our standards. How clear and well organised your presentation is. You should write all the queries in consistent style and use indent format.
• Data correctness and quality. Please use appropriate data for your examples (e.g. do not use inappropriate person names)
• Joining of data from multiple tables should be completed using a WHERE statement only. JOINs are not to be used within any of the SQL statements. Use of any JOINs will result in 0 (zero) marks being allocated for each SQL statement that utilizes them.
• Please refer to the provided marking guide (below) to see the distribution of marks.
Assignment Resources:
• The Standard ER Diagram
• Relational Database Schema
Hint: you need to decide the order that tables need to be created; and the order of tables in which data need to be inserted into.