Database review 4-view and database Review

Source: Internet
Author: User
Tags dname

Database review 4-view and database Review
Database review the concept of CH6 view 6.1

ViewIs exported from one or more tables (or views)Virtual table, DBMS onlyData DictionaryWhile the view data is still actually stored in the exportedBase tableMedium

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

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

So what is the role of a view?

  • Provides security for hidden data, that is, shielding confidential data that is not expected to be seen by external users.
  • It provides a simplified method with a pre-defined view. In some cases, you can avoid writing complicated select compound expressions.
  • Allows the same data to be displayed for different users from different perspectives at the same time
  • ProvideLogical Data independenceThat is, when the schema of the conceptual layer (basic table structure change) changes, the schema of the external layer does not need to be changed. Therefore, external user programs do not need to be changed.
  • The view definition combines the external mode functions and the external layer-concept layer ing functions.
  • Adding scalability to the base table for a view
  • The view can also reconstruct the base table.
6.2 view operations

C. J. Date defines two views from a formal perspective: Retrieval and Update)

Define to execute Operation X on Database (table) d to get view V, that isV=X(D)To retrieve the V ViewR(V)=R(X(D))=X'(D)That is to say, retrieving a view is equivalent to retrieving its base table.

Note that ifV=X(D)After the implementation (Materializing), X (D) is a copy of the basic table, and the operations on V and D are independent. IfV=X(D)It is just a virtual table, so the operation on V will directly modify the basic table D (most DBMS adopt the latter solution)

If U is defined as an update operationU(V)=U(X(D))But we can find thatU(X(D))AndX(U'(D))It cannot be equivalent because V's definition Operation X may involve complex operations such as UNION operations on the set or JOIN operations on the from clause. In this case, the Update operation is ambiguous for the basic table and cannot be executed.

For exampleV=A UNION BTo update whether A tuples in base table A are updated, whether the tuples in base table B are updated, or whether the tuples in base table B are updated at the same time; for example, if the deduplication of a tuples does not meet the integrity constraints of B, but meets the integrity constraints of A, whether to insert only A or the entire insert operation fails.

There are many examples in the PPT and the book to illustrate this problem. The core we need to master is that there are some restrictions on the view update operation, not all views can be updated.

6.3 SQL view (1) create 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 legal select query expression, such as defining a "good vendor" View:

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

Query expressions can also include aggregate functions and set operations. SQL-defined View also supports keeping up with the attribute list behind the View as the attribute name (of course, you can also use select as for renaming ), for example, create a view for the employee sales statistics of each department:

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 search

The view search syntax is the same as the table search syntax. select statements are used, for example:

select name from dept_summary where avgsal > 2000;

This statement is equivalent to the following search for the base table:

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

Most SQL implementations (Oracle, MySQL, etc.) support single-linkSimple ViewUpdate,Simple ViewIs the following view:

  • This operation does not include JOIN, UNION, INTERSECT, and except t.
  • Does not contain keywords: DISTINCT
  • SELECT clauses can only contain simple table names (that is, they do not contain aggregate functions)
  • The group by clause cannot appear.
  • The subquery cannot contain references to the same table other than the query.

Note: The view update includes three operations: update, insert, and delete.

However, we find that even updating a simple view on a single link may cause problems, for example, updating the status value of a supplier in a supplier table:

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

DBMS translation

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

Execution, but the definition of view good_supplier is not changed. If this statement is executed, although the status value of vendor s1 in the base table is changed to 10, the vendor s1 is no longer good_supplier, an error occurs in logic (the view is updated but the tuples are deleted)

To prevent the above situation, SQL supports insert when the view is defined.with check optionCheck the above logical errors with the suffix. If the vendor is as defined below:

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

The update statement that generates a logical error as described above does not meet the check conditions, and the DBMS notifies the user that the execution fails.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.