Database Management: Views II

Database Management Series

Apart from the various versions of SQL ANSI standards, each RDBMS vendor takes the liberty to implement its own solutions in accordance with the problems it’s trying to address – performance, parameterization, partitioning, distribution or whatever other problem might arise. There are several types of views evolved from such attempts, several other names being mentioned in database literature:

Union view – view based on the union of one or more database objects.

Join view - view based on the join of one or more database objects.

Hierarchical view – view based on hierarchical relationships processing techniques.

Base view – view consisting of all of the rows from a table and a subset of columns.

Derived view – view created from a base view by selecting only a subset of rows.

Projection view – in NetWeaver, view used to hide the fields of a table, containing one table and all its rows [1].

In-line view – it’s a “pseudo view” and refers to (in-line) self-contained subqueries used inside of other queries, without actually defining a view. The query can be run independently from the query in which is contained.

Updatable view – views that allow data changes on the tables they are based on.

Maintenance view – in NetWeaver, view that allows viewing and updating data from a set of tables that form a logical unit [1].

Object view - virtual object table based on user-defined types that allow conversion of relational tables into object-relational tables, facilitating thus the use of object-oriented programming techniques without converting existing tables, and the consumption of relational data by object oriented applications [3].

Federative view – view in a federated database system, a meta-database which integrates multiple autonomous database systems.

Consolidated view – view which provides data in a form more frequently required by users [4].

Indexed view or materialized view – is a virtual table that allows storing the output of a query, being ideal for aggregating data across multiple rows, the values being updated and materialized, being queried without continuous recalculation.

Regular view – view that stores no data, as opposed to an indexed view.

Parameterized view – is a view which, much like a function or stored procedure, accepts a set of parameters that dictates the output. In SQL Server parameterized views are implemented with the help of inline table-valued functions.

Partitioned view – a view formed by the union of more tables with the same structure and found within one or multiple autonomous SQL Server instances.

Distributed-partitioned views (DPV) – are portioned views working across multiple instances

System view – refers to the views used by RDBMS vendors to expose metadata about their systems. For example MS SQL Server exposes several types of system views:
• catalog views – return Database Engine related metadata, according to BOL they are the “most efficient way to obtain, transform, and present customized forms of this information” [2].
• information schema views - "provide an internal, system table-independent view of the SQL Server metadata", which “enable applications to work correctly although significant changes have been made to the underlying system tables” [2].
• compatibility views – views implementing backwards compatibility functionality, implemented previously within tables.
• replication views – views based on replication system tables used for implementing SQL Server data replication.
• dynamic management views - "return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance" [2].

System internal views - "views that make available low-level storage metadata for the SQL Server Database Engine" [2].

Help view – implemented in NetWeaver, a help view it’s not a typical database view, being based on a primary table and a search table used in NetWeaver’s online help system.

Conceptual view – concept-based view in a conceptual data model which maps the concepts and relationship between them.


References:
[1] SAP. 2009. SAP NetWeaver 7.0 Views. [Online] Available from: http://help.sap.com/saphelp_nw70/helpdata/en/cf/21ecc5446011d189700000e8322d00/frameset.htm (Accessed: 2 November 2009)
[2] MSDN. 2009. SQL Server Books Online. [Online] Available from: http://msdn.microsoft.com/en-us/library/ms130214.aspx (Accessed: 4 November 2009)
[3] Oracle. 2000. Chapter 14: Object Views. Oracle 8i Concepts. [Online] Available from: http://www.cis.unisa.edu.au/LearningResources/oracle/server.815/a67781/c13obvw.htm (Accessed: 4 November 2009)
[4] Sybase. 2009. Consolidated views. [Online] Available from: http://infocenter.sybase.com/help/topic/com.sybase.help.sqlanywhere.11.0.1/dbreference_en11/views-s-4117677.html (Accessed: 2 November 2009)