DATABASE MANAGEMENT SYSTEM

 

 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. Here are key points about views in a DBMS:

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.

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.