Database Review 4--View

Database review concept of CH6 view 6.1 view

A view is a virtual table that is exported from one or more tables (or views), and the DBMS only holds the definition of the view in the data dictionary , and the view's data is still actually stored in the base table where it was exported

The basic statements for defining views in Tutorial D are as follows:

<view_name> VIEW (<relational_condition>) {<property_list>};

So what does a view do?

• Provides security for hidden data, which masks some confidential data that you do not want outside users to see
• Provides a shorthand way to have a pre-defined view and sometimes avoids writing complex select composite expressions
• Let the same data display data from different angles at the same time for different users
• Provides logical data independence , that is, when the schema of the conceptual layer changes, the pattern of the outer layer does not need to change, so the external user program does not need to change
• The definition of the view combines the external mode function and the outer layer-the mapping function of the conceptual layer
• View adds extensibility for base tables
• The view can also perform refactoring of the base table

6.2 Operation of the view

C.j.date to define two operations for a view from a formal perspective: retrieval (retrieval) and updates (update)

Definition performs an operation on a database (table) d x Gets the view V, that is V=X(D) , the V view is retrieved, that is to R(V)=R(X(D))=X‘(D) say, the image retrieval is equivalent to its base table retrieval

It should be noted that if V=X(D) implemented (materializing), then X (D) is a copy of the basic table, the operation of V and D Independent, if it is V=X(D) a virtual table, then the operation of V will directly modify the basic table D (most of the DBMS is to take the latter scenario)

If you define u as the update operation, U(V)=U(X(D)) but we can find U(X(D)) and X(U‘(D)) is not equivalent, because the definition of V operation X may involve the collection operation union or the FROM clause of the join and other complex operations, when the update operation for the basic table is ambiguous, It can't be done.

V=A UNION BFor example, to update whether a tuple is updating the tuple in base table A or updating the tuple in base table B or AB while updating; for example, to insert a tuple that does not satisfy the integrity constraints of B but satisfies the integrity constraints of a, does it only insert a or the entire insert operation fails

PPT and book there are many examples to illustrate this problem, we need to grasp the core is the update operation of the view there are some limitations, not all views can be updated

6.3 SQL view (1) Creating a View

The syntax for defining a view in SQL is as follows:

create view <view_name> as <query expression>;

Query expression can be any legitimate select query expression, such as defining a good vendor view:

create view good_supplier as select S.s#, S.status, S.city from Swhere s.status > 15;

Query expression can also have aggregate functions, collection operations, and so on, and SQL definition view also supports following the attribute list as a property name (and, of course, a rename implementation with select as), such as creating a view for each department's employee sales statistics:

create view dept_summary(name, minsal, maxsal, avgsal)as select dname, min(sal), max(sal), avg(sal)    from EMP, DEPT    where DEPT.d# = EMP.d#    group by dname;

(2) View retrieval

The syntax for view retrieval is the same as for table retrieval, with SELECT statements, such as:

select name from dept_summary where avgsal > 2000;

This sentence is equivalent to retrieving the base table as follows:

select dname as name from EMP, DEPT where EMP.d# = DEPT.d#group by dame having avg(sal) > 2000;

(3) View update

Most implementations of SQL (Oracle, MySQL, and so on) support the updating of simple views on a single relationship, and the simple view is the following view:

• The following actions are not included: JOIN, UNION, INTERSECT, EXCEPT
• Does not contain keywords: DISTINCT
• Select clauses can contain only simple table names (that is, they do not contain aggregate functions)
• The GROUP BY clause cannot appear
• A subquery cannot contain references to the same table other than the query

Note: The view update described here contains update, INSERT, delete three operations

But we found that even if we were updating the simple view on a single relationship, we would have problems, such as update the status value of a vendor in a good Vendor table:

update good_supplier set status = 10 where S# = ‘s1‘;

DBMS translated into

update S set status = 10 where status > 15 and S# = ‘s1‘;

execution, and the definition of the view good_supplier does not change, then the execution of this sentence vendor S1 although the status value in the underlying table becomes 10, the vendor S1 is no longer part of Good_supplier, and there is a logical error (updating the view but deleting the tuple directly)

In order to prevent this from happening, SQL supports inserting suffixes during view definition with check option to check for similar logic errors, if a good supplier has the following definition:

create view good_supplier as select S.s#, S.status, S.city from Swhere s.status > 15with check option;

Then the UPDATE statement that generated the logical error above does not satisfy the check condition, and the DBMS notifies the user that the execution failed

Database Review 4--View