How could a refresh of materialised view be done? Discuss the advantages and disadvantages of your chosen method of refresh over the other techniques.

PART ONE, Entity Relationship diagram PART ONE, All DDL commands, test data [use SELECT * FROM…] per table
PART TWO, All query SQL syntax [and SQL*PLUS syntax] and results- evidence of the statements running in the Oracle DBMS. [Use SPOOL command to demonstrate]
PART THREE, All SQL Syntax and results pertaining to the view, triggers – evidence of the statements running in the Oracle DBMS [Use SPOOL command to demonstrate]
PART THREE, 150 word answer to part 3 (c)

PART ONE

Use a simple Entity Relationship Diagram (ERD) to show the Hampton Medical Practice database design.  The diagram should use the type of labels and notation taught in Computing Modules in your undergraduate course.  You may use a simple drawing package or word processor software or Oracle Modeller, or a hand drawn diagram or another suitable tool.  Appropriate relational database rules for cardinality and suitable constraints should be included.  INCLUDE ALL YOUR ASSUMPTIONS.

PART TWO

Basing your answer on your ERD in part one and using appropriate attribute types to create these tables using Oracle’s SQL*Plus. Your answer must include all primary keys, foreign keys and any additional constraints that are applicable.

Answer the following queries in Oralce SQL ONLY. If your implementation does not match your design, this will be penalised in the marking process. [WARNING : Do NOT auto-generate your SQL DDL/code- marks will be deducted if you fail to follow this instruction].

Complete the following:

1. Create the tables in the Oracle DBMS using SQL create table statements. For all data relating to names or addresses set up uppercase constraints in your tables (e.g. patient surname, address of the practice).

1. Enter representative test data into the tables. Enter the first record in the patient table using your own name. Ensure you have at least 12-15 rows of data to yield at least a few rows of data per query, as appropriate.  Take care with data, specific datasets are required to satisfy the query-based
2. c) Produce answers to the following queries in SQL:

1. Create a query that will display the total number of doctors (gps) and of that total the number who were hired in 1994, 1996, 1998, and 2000. Give appropriate column headings. [hint: you will need to use the Oracle DECODE function and remember to format the output].
2. Give a list of patients seen from a given G.P. practice showing the

practice name, practice address, patient number, surname, initials, and G.P. number surname and initials. The list should be ordered first on G.P. surname and initials and then patient surname and initials.

1. List the name and address of the patients who have been diagnosed as having “Asthma”. Each patient should occur only once in the result even though they may have been given that diagnosis a number of times (e.g. through a recurring illness).
2. List the names and addresses of all GPs who work in the same city (i.e. you choose the city for example ’BIRMINGHAM’). Show the results of a number of different cities.
3. Find out how many patients are seen by certain GP (i.e. you choose which GP)
4. Use the MINUS construct as you have covered in the module, to find practices which do not employ a GP (include the city where they are located).
5. Repeat the previous query (query 6) using the NOT EXISTS construct.

8      Write a query to display gps who have one or more colleagues in their practice with later hiredates but higher salaries.

PART THREE

1. Develop a view that shows details of all patients on record and their details of diagnosis.  Display the contents of the view.
2. Carry out separate updates on each individual column within the view.   Carry out INSERTS of new rows into the view .Deletes of rows from the view.  Ensure that patients based in Wolverhampton cannot book a routine appointment on a Sunday

Create triggers that enforce the following business rules:

1. Ensure that appointments cannot be made for a Sunday.

1. If a patient makes a greater than FOUR appointments in one month (patient_id, patient_name, practice_name) must be placed in an audit table.

Hint: You will need to construct an audit table with the appropriate columns of correct datatype.

1. Ensure that patient appointments cannot be deleted if the date of the appointment is not later than the current date.

An essential part of this question is that having successfully compiled the triggers, you must construct a set of SQL statements and use them to test out the workings of your triggers.

For instance, if you have created an UPDATE trigger you will need to execute at least two UPDATE statements on the table on which the trigger is based – one that fires the trigger (and probably generates any error message from the trigger), and one that does not. In your testing, you must test all of your triggers, show their code and the output that they generate.

d.) Write upto 150 words to answer  “ How would you use a materialised view that can be used to enhance performance and undergo query re-write?”  Include the following in your answer:-

• Use an external source of reference from your reading
• Use an example to show how ‘Query Rewrite’  can be confirmed
• How updates on materialised view’s base tables can affect query rewrite
• How could a refresh of materialised view be done? Discuss the advantages and disadvantages of your chosen method of refresh over the other techniques.