A database view is a subset of a database that is based on a query that runs on one or more database tables. Database views are saved in the database as named queries and can be used to save complete queries that are frequently used.
Static and Dynamic Views
There are two types of database views: dynamic and static views. Dynamic views contain data from one or two tables and automatically include all columns of the specified table or tables. Dynamic views can be updated dynamically when related or extended objects are created or modified. Static views contain data from several tables and the necessary columns of these tables must be specified in the SELECT and WHERE clauses of the static view. Dynamic views can be updated manually when related objects or extended objects are created or modified.
When creating a dynamic view with data from two tables, you must ensure that both tables have the same PRIMARYKEYCOLSEQ columns or contain unique indexes with the same column name in the same order.
In a multitenant environment, the global administrator creates initial database views, which are part of the default data that is provided to tenants. The tenant ID must be added to the SELECT and WHERE clauses of static views to ensure that specific tenant views are created. Dynamic views should be used to create tenant-specific database views for tenants with extended attributes. Static views do not support extended attributes.
Database views are filled based on the object on which they are based. For example, if you add or delete an attribute of the WORKORDER object, the attribute is added or removed from the dynamic view based on the object. When you modify an attribute, not all changes are applied to the associated database view. For example, if you change the data type of an attribute, the change will be applied to the database view. However, if you change or add a domain to the default value of the WORKORDER object, the change will not be automatically applied to the database view. Instead, you must apply this change to the database view.
Importance of Views
To the database, the View is the same as a real table for a user with the set of column names and row data. SQL creates a custom view by giving the View the same name as a table name and store a definition of the View in the database.
A view is used for security purposes in the database and acts as an intermediate between real tables schema and programmability. It also restricts the user from viewing specific columns and rows; Views always represent custom output, which is mentioned in the query and returns that data defined in the query at the time of creation.
Views also provide these specific features:
Consistency
Views always represent the same presentation, even when we perform normalizations to the Tables schema or if we rename the tables or restructure the table.
Data Integrity
When a data is viewed or entered by a View in a database, the DBMS will automatically check the data to ensure that it meets the specified integrity constraints.
Security
Views provide security from unauthorized access to data. Each user is given permission to access the database from a View that contains a specified data that a user is authorized to see.
Structural Unity
Views provide a custom view of database structure, which represents a database same as a set of a virtual or custom table that is useful for a user and presents only relevant data.
Data Independence
Views provide data independence to an application as the application depends on a view but not on a real table. Therefore, any change in the table will not affect the application, and an application will always be independent of the table schema design.
Contact Soaring Eagle Consulting for a Free Database Evaluation Today
Getting started is simple. Click the button below to request your free one-hour database assessment from the DBA experts at Soaring Eagle Consulting®.
