Views

VIEW AND MATERIALIZED VIEW IN DBMS 

In a Database Management System (DBMS), a view is a virtual table that is based on the result of a SELECT query. Unlike a physical table, a view does not store the actual data itself; instead, it is a saved query that can be referenced as if it were a table. Views are useful for simplifying complex queries, encapsulating business logic, and providing a layer of security by restricting access to specific columns or rows of a table.

Updatable and Non Updatable View

Updatable views allow modifications (inserts, updates, deletes) to the underlying base tables through the view, while non-updatable views restrict such operations. 

To be updatable, a view must:

• Be based on only one table (no joins).
• Include all required columns (especially NOT NULL columns with no default).
• Avoid aggregations (SUM(), AVG()), grouping, DISTINCT, or window functions.
• Avoid set operations (UNION, INTERSECT, etc.).
• Not include subqueries in the SELECT list.

Creating a View:

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

• view_name: The name of the view you're creating.
• column1, column2, ...: The columns you want to include in the view.
• table_name: The name of the table or tables from which the data is derived.
• condition: An optional condition to filter the rows.

Example:

CREATE VIEW employee_view AS
SELECT employee_id, first_name, last_name
FROM employees
WHERE department_id = 1;

Using a View:

Once a view is created, you can use it in SELECT statements as if it were a table.

SELECT * FROM employee_view;

Updating a View:

In some cases, you can update the underlying tables through a view, but there are restrictions, and it depends on the complexity of the view. Simple views based on a single table can often be updated, but complex views involving multiple tables or aggregation functions may have limitations.

Dropping a View:

DROP VIEW view_name;

Benefits of Views:

• Simplicity: Views simplify complex queries by encapsulating them into a single, easy-to-understand virtual table.
• Security: Views can provide an additional layer of security by restricting access to specific columns or rows. Users can be granted access to views without exposing the underlying tables.
• Abstraction: Views provide a level of abstraction, allowing changes to the underlying table structures without affecting the queries that use the views.
• Business Logic Encapsulation: Views are useful for encapsulating business logic, calculations, or data transformations, making it easier to maintain and update such logic in one place.

MATERIALIZED VIEW IN DBMS

A Materialized View is a database object that stores the result of a query physically, unlike a normal (virtual) view which only stores the query definition.

Characteristics of Materialized View 

• Physically stores data (unlike a regular view).
   
• Data is periodically refreshed (automatically or manually).
   
• Used to improve performance for complex queries.
   
• Supports indexes and can be queried just like a regular table.
   

Materialized views are used to improve performance, reduce computation cost, and support efficient reporting or analytics.

CREATE MATERIALIZED VIEW view_name AS

SELECT column1, column2, ...

FROM table_name

WHERE condition;

 

Difference between Normal View and Materialized View 

The main difference between views and materialized views is that views are dynamic and materialized views are static. This means that views always reflect the latest data from the underlying tables or views, while materialized views only show the data from the last refresh. Therefore, views are more suitable for queries that need real-time data, while materialized views are more suitable for queries that need precomputed data.

Another difference is that views are more lightweight and flexible, while materialized views are more resource-intensive and rigid. This means that views do not take up any storage space or require any maintenance, while materialized views do. However, views also depend on the availability and performance of the underlying tables or views, while materialized views do not. Therefore, views are more convenient for creating temporary or ad hoc queries, while materialized views are more reliable for creating permanent or recurring queries.