7. Views Flashcards
(8 cards)
View:
A virtual table derived from other base tables or views. It doesn’t necessarily store data physically. Behaves like a table for querying but has limitations for updating. Any query can be a view.
Purpose/Uses:
Provide data abstraction (external schema), customise user access, simplify schema/queries, implement security and access control (restricting columns/rows). Can rename columns.
Base Table vs View:
A base table is physically stored data. A view is a definition (saved query) over base tables (or other views).
(Impletation Method) Query Transformation (or View Expansion):
When a query is made on a view, the DBMS replaces the view reference with the view’s underlying query. The result is always consistent with base tables but regenerated on each query. Can be inefficient for complex views or frequent queries.
(Implementation Method) View Materialisation:
The view’s output is saved as a physical table. Queries are faster as they access the stored data. Requires storage space. Data may become inconsistent with base tables unless refreshed. Refresh can be manual or incremental (DBMS dependent). PostgreSQL has MATERIALIZED VIEW which requires manual REFRESH.
View Updatability:
Updating views (INSERT, UPDATE, DELETE on the view) is complicated and often restricted. Updates must be unambiguously mappable to changes in the underlying base table(s). Views defined on a single base table without aggregates are usually updatable. Views with joins, aggregates, GROUP BY, DISTINCT, set operations, etc., are often not updatable.
INSTEAD OF Triggers:
Can be defined on non-updatable views to execute stored code instead of the standard update operation, allowing complex updates to be handled correctly.
WITH CHECK OPTION:
Ensures that rows inserted or updated through the view satisfy the view’s defining WHERE clause, maintaining consistency between the view and base table.
