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. 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.You can assume that patient number for this new patient is the increment of the largest patient number in the patient table by 1. (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)
NB’
Assignment 2: Question 12
Below is the Modified Question to make it more simple
12. Display the details of all consultations which products have been recommended to the
patients but not purchased by them. Display in patient number order within
consultation number order
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)
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-15) 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.