Database Operations 3 Concepts

Database Operations (Views)

A database view is a searchable object in a database that is defied by a query. Though a view doesn't store data, some refer to a view as a "virtual table", you can query a view like you can a table. A view can combine data from two or more tables, using joins, and also just contain a subset of information. This makes them convenient to abstract, or hide, complicated queries.

Views Example

How to create and use a database view

A view is created from a query using the CREATE VIEW command in the example below we are creating a PopularCodes view based of a query which selects all Books that have the IsPopular field checked. The Query is below:

                        
                            CREATE VIEW PopularCodes AS
SELECT ISBN, Title, Author, Type
FROM Codes
WHERE IsPopular = 1

Once a view is created you can use it then as you would any table in a SELECT statement. For example, to list all the popular book titles ordered by author you could write.

                        
                            SELECT Author, Title
FROM PopularCodes
ORDER BY Author

In general, you can use any of the SELECT clauses, such as GROUP BY, in a select statement containing a view.

Read Only Views Vs Updateable Views

Database practitioners can define views as read-only or updatable. If the database system can determine the reverse mapping from the view schema of the underlying base tables, then the view is updatable. INSERT, UPDATE, and DELETE operations can be performed on updatable views. Read-only views do not support such operations because the DBMS cannot map the changes to the underlying base tables. A view update is done by key preservation.

Some systems support the definition of INSTEAD OF triggers on views. This technique allows the definition of other logic for execution in place of an insert, update, or delete operation on the views. Thus database systems can implement data modifications based on read-only views. However, an INSTEAD OF trigger does not change the read-only or updatable property of the view itself.

Materialized Views

Various database management systems have extended the views from read-only subsets of data, particularly materialized views: pre-executed, non-virtual views commonly used in data warehousing. They give a static snapshot of the data and may include data from remote soures. The accuracy of a materialized view depends on the frequency of trigger mechanisms behind its updates.

Materialized views were introduced by Oracle Database, while IBM DB2 provides so called "materialized query tables" (MQTs) for the same purpose.

Advantages of Views

These are the advantages to using Database Views:

Enforce Business Rules - Use views to define business rules, such as when an items is active, or what is meant by "popular". By placing complicated or misunderstood business logic into the view, you can be sure to present a unified portrayal of the data. This increases use and quality.
Consistency - Simplify complicated query logic and calculations by hiding it behind the view's definition. Once defined the calculations are referenced from the view rather than being restated in separate queries. This makes for less mistakes and easier maintenance of code.
Security - Restrict access to a table, yet allow users to access non-confidential data via views. For examples, you can restrict access to the employee table, that contains social security numbers, but allow access to a view containing name and phone number.
Simplicity - Databases with many tables possess complex relationships, which can be difficult to navigate if you aren't comfortable using Joins. Use views to provide a "flattened" view of the database for reporting or ad-hoc queries.
Space - Views take up very little space, as the data is stored once in the source table. Some DBMS all you to create an index on a view, so in some cases views do take up more space than the definition.

Disadvantages of Views

These are the disadvantages of using Database Views:

Performance - What may seem like a simple query against a view could turn out to be a hugely complex job for the database engine. That is because each time a view is referenced, the query used to define it, is rerun.
Modifications - Not all views support INSERT, UPDATE, or DELETE operations. In general, in order to support these operations, the primary key and required fields must be present in the view. Complex multi-table views are generally read only.

Sources: (click on them to see them)

Database Operations: Views (Concepts)