INDBS Noter

Tags:conceptSQLSQLViewsdatabase Status:🟩

SQL Views

Summary

SQL views are virtual tables that present the results of a query on one or more base tables without storing data. They provide a way to encapsulate complex queries, offer a layer of data abstraction, and can enhance security by restricting access to specific data. Unlike physical tables, views do not store data but dynamically generate results from underlying tables. Views can be updated if they meet certain criteria, and they can be materialized for performance optimization.

Details

A view is a virtual table in SQL that displays the results of a query on one or more base tables without storing data itself. As a schema element, it is an integral part of the database structure.

Creating a view:

CREATE VIEW topstudents
AS SELECT id, name FROM students
WHERE grade > 10

Accessing a view:

SELECT * FROM topstudents;

A view is conceptually similar to a table but is not a physical table. It is a virtual table that does not store data itself. Instead, a view represents the results of a query on one or more base tables and generates its content dynamically when accessed. Unlike a SELECT statement, which provides immediate results for that query alone, a view encapsulates the query logic so that it can be reused to produce results on demand. The view definition is saved in the database, but the data is not physically stored with the view; it is retrieved from the underlying tables each time the view is queried.

View filtering

-- When calling this query
SELECT * FROM topstudents WHERE city = 'Copenhagen'
 
-- It is translated to
SELECT id, name FROM students WHERE grade > 10 AND city = 'Copenhagen'

Query Modification

RDBMS does not automatically update the view when records are added, removed, or modified in the base tables. However when a user queries the view, the RDBMS modifies the query to operate on the base tables, and this query will naturally return the most up-to-date data.

View Materialization

Instead of modifying the query each time, some DBMS create a materialized view. This is a physical table from when the view is first queried. To keep the materialized view updated, the RDBMS synchronizes it with the base tables either:

  • Immediately: when the base tables are updated (immediate view maintenance).
  • Deferred: just before the view is queried (deferred view maintenance).

Updatable Views

Some views allow data to be updated through them, and those changes will be applied to the base tables. However, the view must meet certain conditions for updated to work.

  • No DISTINCT, GROUP BY or aggregate function like SUM().
  • The view must only query one table (no joins, subqueries etc.) If those conditions is not met, the view is read-only.
UPDATE topstudents
SET name = 'New Student Name'
WHERE id = 64;

View Update Errors

If the RDBMS can’t determine a clear way to apply an update to the base tables, the update will fail.

With Check Option

The WITH CHECK OPTION ensures that any updates or inserts made through a view will follow the view’s conditions. If the update violates the view’s conditions, it will be rejected. In practice the RDBMS will generate an error and the specific update will not be executed. If there are multiple updates and the rest are allowed by the view, they will be executed.

CREATE VIEW topstudents
AS SELECT id, name FROM students
WHERE grade > 10
WITH CHECK OPTION

Advantages of views

  • Hiding complex queries, such as join queries or correlated queries from users.
  • Provide data protection by hiding columns or tuples from unauthorized users.
  • Allow for logical data independence, which makes them a key component in the three-layer database architecture.

Example

Examples for defining views

-- Defining a view from a select statement and a where clause
CREATE VIEW topstudents
AS SELECT id, name FROM students
WHERE grade > 10
 
-- Defining a view from a view and a where clause
CREATE VIEW topstudents_cph
AS SELECT * FROM topstudents
WHERE SUPCITY = 'Copenhagen'
 
-- Defining a view from a join and an aggregation function.
CREATE VIEW enrollment_overview(course_id, course_name, total_enrolled)
AS SELECT C.course_id, C.course_name, COUNT(E.student_id) AS total_enrolled
FROM course AS c LEFT OUTER JOIN enrollment AS e
ON c.course_id = e.course_id
GROUP BY c.course_id, c.course_name;

Once views have been defined, they can be used in application or other queries, like this:

SELECT * FROM topstudents
SELECT * FROM topstudents_cph
SELECT * FROM enrollment_overview

Graph View

Backlinks