Update Information in the underlying tables of a View

Views are helpful in reporting because they can automate your queries a bit and save you the trouble of constantly resubmitting complex queries. As we saw in the last exercise, you can quickly select from the view, which can filter and combine information from more than one table:

> In Oracle 12c, updating underlying tables using a view, especially when dealing with mandatory and optional entities like 'Customer' and 'Orders', requires a clear understanding of how views work and the relational integrity between the tables. Here's how to approach this task in an authoritative and informative style:

1. Understand the Relationship between Tables: The 'Customer' table is the mandatory entity, meaning each record in the 'Orders' table must correspond to a record in the 'Customer' table. This is typically implemented via a foreign key in the 'Orders' table that references the primary key in the 'Customer' table.
2. Create an Updatable Join View: Oracle allows you to create views that join multiple tables, and under certain conditions, these views can be updatable. To update both 'Customer' and 'Orders' tables via a view, create a join view that encapsulates the desired columns from both tables. For example:
   

   CREATE VIEW CustomerOrdersView AS
   SELECT c.CustomerID, c.Name, o.OrderID, o.OrderDate
   FROM Customer c LEFT JOIN Orders o ON c.CustomerID = o.CustomerID;
   

   
   Here, a LEFT JOIN is used to ensure all customers are included in the view, even if they don't have corresponding orders, reflecting the mandatory (Customer) and optional (Orders) relationship.
3. Ensure the View Meets Updatability Criteria: For a view to be inherently updatable, it must not contain any of the following: set operators, aggregate functions, DISTINCT clause, GROUP BY clause, JOIN operations (other than certain types of INNER JOINs), or subqueries.
4. Performing Updates Using the View: To update information, use standard `UPDATE` statements on the view. However, there are limitations. If you update a column that originates from the 'Customer' table, Oracle will apply the update to the 'Customer' table. If you update a column from the 'Orders' table, Oracle applies the update to the 'Orders' table. For example:
   

   UPDATE CustomerOrdersView
   SET Name = 'New Name'
   WHERE CustomerID = 123;
   

   
   This updates the 'Customer' table. However, updating optional entity data (like adding an order for a customer who doesn't have one) is more complex and might not be directly achievable through the view.
5. Consider INSTEAD OF Triggers for Complex Scenarios: If the view does not support direct updates (due to complex joins, for instance), you can use INSTEAD OF triggers. These triggers can be defined on the view to perform the necessary insert, update, or delete operations on the underlying tables. For instance, an INSTEAD OF UPDATE trigger can be written to handle updates in a way that respects the relationship between 'Customer' and 'Orders'.
6. Maintain Data Integrity: When updating data through views, it's crucial to maintain data integrity. Ensure that any update through the view does not violate the referential integrity between the 'Customer' and 'Orders' tables.
7. Test Thoroughly: Before implementing in a production environment, thoroughly test the view and any associated triggers to ensure they behave as expected and maintain data integrity.

By following these steps, you can effectively use a view to update information in the underlying 'Customer' and 'Orders' tables in Oracle 12c, considering the mandatory and optional nature of these entities. Remember, the complexity of your view and the relationships between your tables can impose limitations on what can be updated directly through the view.

CREATE VIEW
MyView AS
SELECT * FROM 
CustomerInfo, PhoneNumbers 
WHERE
CustomerInfo.CustomerID = PhoneNumbers.CustomerID

You create views as shown above and use them to update information in the underlying tables. There are rules that are enforced by your database engine, but generally speaking, views are created as either read-only or updateable. To understand the difference, you need to view this process through the eyes of your database engine. You need to understand what the engine has to do to fulfill an update request so you can to understand what makes a view updateable or read-only. To make a view updateable, you must have a SELECT statement that identifies the specific rows with which it is working. What does this mean? It means that when you send information to the database, the engine must be able to determine, very specifically, where that information should be stored.We will look at an example in the next lesson.

The problem of efficiently implementing a view for querying is complex. Two main approaches have been suggested. One strategy, called query modification, involves modifying or transforming the view query (submitted by the user) into a query on the underlying base tables. For example, the query QV1 would be automatically modified to the following query by the DBMS:

SELECT Fname, Lname
FROM EMPLOYEE, PROJECT, WORKS_ON
WHERE Ssn=Essn AND Pno=Pnumber
AND Pname='ProductX';

The disadvantage of this approach is that it is inefficient for views defined via complex queries that are time-consuming to execute, especially if multiple queries are going to be applied to the same view within a short period of time. The second strategy, called view materialization, involves physically creating a temporary view table when the view is first queried and keeping that table on the assumption that other queries on the view will follow. In this case, an efficient strategy for automatically updating the view table when the base tables are updated must be developed in order to keep the view up-to-date. Techniques using the concept of incremental update have been developed for this purpose, where the DBMS can determine what new tuples must be inserted, deleted, or modified in a materialized view table when a database update is applied to one of the defining base tables. The view is generally kept as a materialized (physically stored) table as long as it is being queried. If the view is not queried for a certain period of time, the system may then automatically remove the physical table and recompute it from scratch when future queries reference the view.
Updating of views is complicated and can be ambiguous. In general, an update on a view defined on a single table without any aggregate functions can be mapped to an update on the underlying base table under certain conditions. For a view involving joins, an update operation may be mapped to update operations on the underlying base relations in multiple ways. Hence, it is often not possible for the DBMS to determine which of the updates is intended. To illustrate potential problems with updating a view defined on multiple tables, consider the WORKS_ON1 view, and suppose that we issue the command to update the PNAME attribute of 'John Smith' from 'ProductX' to 'ProductY'. This view update is shown in UV1:

UV1: UPDATEWORKS_ON1
SET Pname = 'ProductY'
WHERE Lname='Smith' AND Fname='John'
AND Pname='ProductX';