Friday Week 11 (See Course Description for further date and time). 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 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. 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. Load the non-populated tables with test data. You should create a script which will insert, , the following sample data – 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 shou
ld be structured in such a way that once it has been inserted and the “commit” SQL command run, that the database is in a 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 , the SQL scripts must correctly manage transactions and use sequences in the AUTO_INCREMENT option to generate primary keys (under no circumstances may a value be hard coded as a number). You should also ensure that any related data impacted by your script actions are correctly managed. Include this new patient and her appointment into the ZC database. 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. Include the above changes in the database. Note that that the results from this listing should be displayed in a single result output. Make use of the “UNION” relational operator as part of your SQL statement. 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 and the files described in and statements above. Your document should include: Database Model for Zen Chiropractic Clinic represents primary key which the attribute should be underlined based on the notation taught in this course. (FK) represents foreign key which the attribute should be italicised based on the notation taught in this course.