Need help in advance Oracle, like partitioning, triggers, procedure, view, parallel SQL. -- 2

1) Identify 3 common queries that would need to be run frequently against the database (at least 2 must include a join, and one must include a transaction with multiple steps).

For each of the queries:
Produce the SQL to correctly produce the expected result.
Identify what indexes would help. Identify the type of index and columns that are used to build these indexes (justify your design).
Show the SQL commands for building these indexes in Oracle.
Show the query execution plans both before the index is added and after adding the index.
Explain how the index was utilised (or not) and why. What join algorithms were used? What changes would you need to make for the index to be properly utilised, or for a different join algorithm to be used instead? (Provide concrete details of the changes).


2) What partitioning strategies do you recommend (include details of the partitioning type and which columns/key should be used. You must justify your design decisions. Include details about which of the above queries it will improve the performance of and how it helps with concrete examples. (You must explain in clear terms – such as partition pruning, partition joins, and parallel SQL, applicable to each of these queries.)

3) Provide the SQL for implementing your partitioning strategy.

4) Write triggers to enforce the following rules for bids. New bid amounts must be greater than the current highest bid, and the bidno must be ONE more than the last successful bid, and the bid date cannot be after the items end date.

5) Write a stored procedure to identify the username of the winner of each auction item and the amount of the winning bid and the status of the auction (OPEN/CLOSED) - any auction item that is not bid on should have 'NONE' listed as the winner’s username and 0 of the amount of the winning bid. Your procedure must take in a date parameter which is used to identify the cutoff date (for example when testing you can pass in the current date, which should identify winners of auctions completed as of the current date/time – any that are still ongoing will be listed as OPEN, while auctions with end date past the provided date will be listed as CLOSED).

6) Create a view called AuctionView to reproduce the above table (including all null values) from the underlying normalised tables (order the results by itemID and bidNo).