Details of this Paper

ITECH 5006 SEM 2 2004 - Assignment 2 - Database Implementation and Queries - Zen Chiropractic Clinic

Description

solution


Question

;;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 (ZC 2);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 ZC 2 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 ZC 2 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 ZC 2 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 ZC 2. (3 marks);10. Display the full details for the cheapest service(s) provided by ZC 2. (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 ZC 2 system, display details about the patients covered by insurance(s) and patients not covered by any insurances

 

Paper#65564 | Written in 18-Jul-2015

Price : $42
SiteLock