Views in DBMS

In DBMS, a view is one or more populated tables that contain information from one or more tables. However, it should be noted that, unlike what is stored in a regular table, a view doesn’t contain data physically. However, it contains only the blank cell, and its value is obtained from the underlying tables in terms of the defined query. This makes views an important concept in data systems to reduce data complexity to simple data access to increase data presentation security and flexibility.

Views are often used to:

Simplify Complex Queries: The creation of a view in rdbms helps users access data through simple operations such as joins and filters, which they may have written and tested multiple times, yet they should not have to rewrite the whole complex join, filter or aggregation.

Enhance Security: Views offer a method of limiting the use of certain columns and /or rows of a database. One can easily understand this by defining views that will enable the DBA to display only certain information to certain users or applications.

Present Data Flexibly: Holds work in the same manner as files but lets data be shown according to user preferences without changing the structure in the database. That is, one user may only require data summarisation for overall sales, while another may require actual transactions. Opinions can meet all these requirements.

Creating a view in DBMS means using an SQL statement to define a virtual table based on a query. Basically, a view can simplify complex data by combining information from one or more tables and showing it to the user. Once a view is created, it acts like a regular table, but it does not store data. Instead, it displays data based on the query that is asked. To create a view, the CREATE VIEW command is used, followed by the SQL query that selects the data you want to display.

Pseudocode to Create a View in DBMS 

Here’s how the pseudocode for views in DBMS goes to retrieve data from one or more tables with a specific query:

•  Define the ViewName for the view to be created.
• Specify the SELECT Query to choose columns from one or more tables, applying necessary conditions.
• Use the CREATE VIEW statement to create the view.
• Follow the statement with the SELECT Query to determine the data to be included in the view.
• The view allows users to access customised, simplified data without altering the underlying tables.

START
  DEFINE ViewName
  DEFINE SELECT Query (SELECT columns FROM table WHERE conditions)
  CREATE VIEW ViewName AS
    SELECT Query
END

When we define views in DBMS, they can be categorised based on their functionality and the way they interact with the underlying data. The main types of views in DBMS are:

Simple View 

A simple view in DBMS is derived from a single table that displays specific rows or columns based on set criteria. It’s lightweight, easy to maintain, and perfect for basic use cases. Since it involves just one base table, it doesn’t include joins, subqueries, or aggregate functions. Simple views show real-time changes made to the underlying table and don’t store data physically. They support basic updates and queries, making them ideal for scenarios that need minimal data abstraction, such as presenting filtered or restricted data views.

For example, here is a simple view that shows employees who belong to the HR department, including their employee ID, first name and last name:

CREATE VIEW HR_Employees AS  
SELECT EmployeeID, FirstName, LastName  
FROM Employees  
WHERE Department = 'HR'; 

Complex View 

A complex view in SQL DBMS is created from multiple tables and often incorporates joins, subqueries, or aggregate functions, making it ideal for handling sophisticated queries. Complex views can combine data from different sources, use advanced SQL features like grouping and aggregations, and may include computed columns. These views dynamically reflect changes in the underlying tables and are well-suited for reporting, analysis, and advanced data modelling tasks. They help summarise or analyse large datasets, providing a powerful tool for managing intricate data requirements.

Now let’s look at two complex views: 

Join Views

This join view combines data from two seperate Customers and Orders tables using a JOIN operation based on the CustomerID. It retrieves the CustomerID, Name, OrderID, and Amount for each order and gives a unified view of customer and order details.

CREATE VIEW OrderSummary AS  
SELECT Customers.CustomerID, Customers.Name, Orders.OrderID, Orders.Amount  
FROM Customers  
JOIN Orders ON Customers.CustomerID = Orders.CustomerID;  

Inline  View 

This inline view is a temporary subquery used within a larger query. It first selects CustomerID and Name from the Customers table where the country is 'USA'. The inline view is then joined with the Orders table to display orders from customers based in the USA.

SELECT * FROM ( 
	SELECT CustomerID, Name 
	FROM Customers 
	WHERE Country = 'USA' 
) AS USA_Customers 
JOIN Orders ON USA_Customers.CustomerID = Orders.CustomerID;

Materialised Views

A materialised view physically stores the results of a query in the database, which eliminates the need to recalculate the data each time the view is accessed. This makes it a useful tool for improving performance, especially for resource-intensive queries. Unlike regular views, materialised views occupy physical storage and require periodic refreshes to stay up-to-date with the underlying data. These refreshes can be done on-demand or scheduled, depending on the system's needs.

Materialised views are commonly used in OLAP (Online Analytical Processing) systems where complex aggregations and joins are frequent. By storing precomputed results, they significantly reduce the database workload, avoid repetitive calculations and speed up query execution for large datasets. 

Here’s a materialised view of the sales summary that stores the total sales for each product: 

CREATE MATERIALIZED VIEW SalesSummary AS  
SELECT ProductID, SUM(SalesAmount) AS TotalSales  
FROM Sales  
GROUP BY ProductID;

Now let’s look at the view in DBMS with the example: 

Consider a table called Employees with the following structure:

Now, let's create a view to display the employees from the "HR" department only:

CREATE VIEW HR_Employees AS
SELECT EmployeeID, FirstName, LastName, Department, Salary
FROM Employees
WHERE Department = 'HR';

How it Works: 

• CREATE VIEW HR_Employees: This statement creates a new view named HR_Employees.
• SELECT EmployeeID, FirstName, LastName, Department, and Salary: This selects the columns we want to display in the view.
• FROM Employees: Specifies the table from which the data will be fetched.
• WHERE Department = 'HR': Filters the records to only show employees who work in the HR department.

Once this view is created, you can query the view just like a table:

SELECT * FROM HR_Employees;

It will return: 

To create views from multiple tables, consider two tables: Employees and Departments:

Departments Table:

View Creation Example:

We want to create a view called EmployeeDetails that shows each employee's name, salary, and department name by joining the Employees and Departments tables.

CREATE VIEW EmployeeDetails AS
SELECT E.emp_name, E.salary, D.dept_name
FROM Employees E
JOIN Departments D ON E.dept_id = D.dept_id;

How it works: 

• CREATE VIEW EmployeeDetails: This command creates a new view named EmployeeDetails.
• SELECT E.emp_name, E.salary, D.dept_name: We're selecting the employee's name and salary from the Employees table and the department name from the Departments table.
• FROM Employees E JOIN Departments D ON E.dept_id = D.dept_id: We're joining the two tables on the dept_id field, ensuring we get the correct department name for each employee.

Once the view is created, you can query the EmployeeDetails view like a regular table:

SELECT * FROM EmployeeDetails;

It will return:

Here’s an example of updating and deleting an existing view using the same employees and departments tables as in the last example: 

Updating a view

If you need to update an existing view to include additional information or change the existing columns, you can use the CREATE OR REPLACE VIEW command. For instance, let's say we want to include the employee's emp_id in the EmployeeDetails view. Now let’s look at an example of updating the view to include emp_id:

CREATE OR REPLACE VIEW EmployeeDetails AS
SELECT E.emp_id, E.emp_name, E.salary, D.dept_name
FROM Employees E
JOIN Departments D ON E.dept_id = D.dept_id;

How it works: 

• CREATE OR REPLACE VIEW EmployeeDetails: This command replaces the existing EmployeeDetails view with the updated definition.
• SELECT E.emp_id, E.emp_name, E.salary, and D.dept_name: We're now including the emp_id along with the employee's name, salary, and department name.

Rules to Update Views in SQL:

Once executed, the updated view will include the emp_id:

Deleting a View

If you no longer need a view, you can delete it using the DROP VIEW command. For instance, if you want to remove the EmployeeDetails view, here's how you would do it:

DROP VIEW EmployeeDetails;

How it works:

DROP VIEW EmployeeDetails: This command deletes the EmployeeDetails view from the database. After executing it, the view no longer exists, and you can't query it anymore.

Using the same EmployeeDetails view from the previous examples, let’s see how to drop a view: 

To remove the EmployeeDetails view from the database, you can use the DROP VIEW command

DROP VIEW EmployeeDetails; 

How it works: 

DROP VIEW EmployeeDetails: This command completely removes the EmployeeDetails view from the database. Once executed, you will no longer be able to query or access the EmployeeDetails view.

Once the view is dropped, any attempts to query it will result in an error, like:

SELECT * FROM EmployeeDetails;

This will return an error message:

ERROR: view "EmployeeDetails" does not exist

Consider the following Employees table:

Now, we create a view that shows only employees from the HR department:

CREATE VIEW HR_Employees AS 
SELECT EmployeeID, FirstName, LastName 
FROM Employees 
WHERE Department = 'HR';

To Insert a Row into the HR_Employees View:

INSERT INTO HR_Employees (EmployeeID, FirstName, LastName) 
VALUES (103, 'Alice', 'Johnson');

The code inserts a new row into the HR_Employees view. The view is based on the employees' table and is filtered by the HR department. Therefore, the inserted data will be stored in the Employees table in the HR department. In this example, a new employee (EmployeeID = 103, FirstName = 'Alice', LastName = 'Johnson') is added. The row will automatically appear in the HR_Employees view because it meets the requirements for belonging to the HR department.

Resulting View

For this example, consider two tables: Customers and Orders.

Customers Table:

Orders Table:

Now, let's create a view that combines data from both tables to show each customer's total order amount for the year 2024.

CREATE VIEW CustomerOrderSummary AS 
SELECT Customers.CustomerID, Customers.Name, SUM(Orders.Amount) AS TotalAmount 
FROM Customers 
JOIN Orders ON Customers.CustomerID = Orders.CustomerID 
WHERE Orders.OrderDate BETWEEN '2024-01-01' AND '2024-12-31' 
GROUP BY Customers.CustomerID, Customers.Name;

This view, CustomerOrderSummary, combines data from the Customers and Orders tables. It uses a JOIN to link customers with their orders and filter orders made in 2024. Then, the results are grouped by CustomerID and Name. The SUM function calculates the total order amount for each customer during the year 2024.

Resulting View:

The WITH CHECK OPTION clause is used in SQL to ensure that any data inserted or updated through a view must meet the criteria defined in the view's SELECT statement. If an insert or update operation violates the conditions set in the view, the operation will fail.

Let’s consider the following Employees table:

Now, we create a view that shows only employees from the HR department and apply the WITH CHECK OPTION clause so that no other department can be inserted through this view.

CREATE VIEW HR_Employees AS 
SELECT EmployeeID, FirstName, LastName 
FROM Employees 
WHERE Department = 'HR' 
WITH CHECK OPTION;

To Insert a Row into the HR_Employees View:

INSERT INTO HR_Employees (EmployeeID, FirstName, LastName) 
VALUES (104, 'Alice', 'Johnson');

Trying to Insert an Invalid Row (Fails Due to WITH CHECK OPTION):

INSERT INTO HR_Employees (EmployeeID, FirstName, LastName, Department) 
VALUES (105, 'Bob', 'Miller', 'IT');  -- This will fail because 'IT' is not part of the HR department

In this example, the HR_Employees view is restricted to only show employees from the HR department. The WITH CHECK OPTION won’t allow any insert or update through this view because it must also meet the Department = 'HR' condition. The first INSERT statement succeeds because it's inserting an employee from the HR department. However, the second INSERT statement fails because it attempts to insert an employee into the IT department which violates the condition defined in the view.

Here are some views in DBMS applications: 

• Simplify complex queries by providing a predefined result set.
• Enhance data security by restricting access to sensitive data.
• Improve query performance by materialising frequently used complex joins.
• Provide a layer of abstraction, hiding the complexity of underlying database structures.
• Allow different users to see customised views of data according to their requirements.
• Enable easier data aggregation and reporting.
• Simplify database maintenance by centralising logic in views instead of repeating it across multiple queries.
• Support data consistency by presenting a consistent interface to users while underlying data may change.

What view serializability does is to make transaction schedules produce results equivalent to a serial execution where transactions run sequentially. For a schedule to be view serialisable, it must satisfy three conditions. Firstly, transactions must read data from the same source as in the serial schedule. Second, the updates must yield identical results. Thirdly, the final write must match the serial order. It is useful when conflict serializability is too restrictive, enabling greater concurrency. However, it is harder to implement and verify, which makes it more complex than conflict serializability.

For creating a view in rdbms you can start by defining a virtual table based on a SQL query that retrieves data from one or more base tables. Consider the following example:

CREATE VIEW Employee_View AS
SELECT EmployeeID, FirstName, LastName
FROM Employees
WHERE Department = 'HR';

The Employee_View presents only the employees from the HR department. Whenever this view is queried, it pulls the relevant data from the Employees table.

Learning views in DBMS is crucial because they simplify complex data interactions, enhance security, and improve query performance. They allow you to work with abstracted data, reduce redundancy, and maintain consistency. Mastering views enables more efficient database management and ensures better data access control, making it an essential skill for anyone working with databases. To learn more, enroll in the CCBP Academy 4.0 program.

1. What is the purpose of using views in a DBMS?

Views provide a customised and controlled representation of the database. It allows users to access relevant data without exposing sensitive information. They simplify data access and enhance security by limiting what data is visible to different users.‍

2. Are views physically stored as separate tables in the database?

No, views are not physically stored as separate tables. They are virtual tables that generate content dynamically from the underlying base tables whenever they are queried.

3. Can views be modified like regular tables in a DBMS?

While views are generally read-only, some DBMS systems support updatable views with certain restrictions. Those allow limited modifications through the view itself.

4. How do materialised views differ from regular views?

Materialised views store the actual data, which makes them faster to query, but they require periodic refreshes to stay in sync with the underlying tables. Regular views, on the other hand, don’t store data and fetch it on demand whenever queried.

5. What is the difference between a virtual view and a materialised view?

A virtual view is dynamically generated each time it’s queried. A materialised view stores data persistently, requiring periodic refreshes to ensure consistency with the underlying data.