Different Database Views
Virtual Views
- A virtual view is a stored SQL query that presents data from one or more tables without storing the data itself.
- Characteristics:
- Dynamic: Data is retrieved in real-time from the underlying tables.
- No Storage: Only the query definition is stored, not the data.
- Use Cases: Ideal for simplifying complex queries and hiding data complexity.
Materialized Views
- A materialized view stores the result of a query as a physical table in the database.
- Characteristics:
- Static: Data is not live; it is refreshed periodically.
- Storage Required: The result is physically stored, which improves query performance.
- Use Cases: Ideal for complex aggregations and data that is queried frequently.
CREATE MATERIALIZED VIEW SalesSummary AS
SELECT ProductID, SUM(SalesAmount) AS TotalSales
FROM Sales
GROUP BY ProductID;
Materialized views require maintenance to keep data up-to-date, often through scheduled refreshes.
Hiding Data Complexity
- Simplification: Views abstract complex joins and aggregations, presenting a simplified interface to users.
- Example: A view can combine data from multiple tables, hiding the underlying SQL logic.
Data Consistency and Independence
- Consistency: Views ensure all users see the same data, derived from a single source.
- Independence: Views are decoupled from the underlying schema, so changes to the schema do not affect the view.
If the underlying data changes, virtual views automatically reflect these changes, maintaining data consistency.
Performance and Query Simplification
- Performance: Materialized views improve performance by storing precomputed results, reducing the need for repeated calculations.
- Simplification: Views encapsulate complex logic, making queries easier for non-expert users.
Use materialized views for read-heavy applications where query performance is critical.
Read-Only vs. Updatable Views
- Read-Only Views: Most views are read-only, meaning users cannot modify the underlying data through them.
- Updatable Views: Some views are updatable if they meet conditions like having a one-to-one relationship with the base table.
Views with aggregates or joins are typically not updatable.
Security
Access Control: Views help restrict access to sensitive data by limiting which columns and rows users can see.
A view can exclude salary information from an employee table so that only authorised users can access it.
CREATE VIEW PublicEmployeeInfo AS SELECT EmployeeID, Name, Position FROM Employees;
- What is the key difference between a virtual view and a materialized view?
- Why might you use a virtual view in a real-world application?
- How does a materialized view improve performance?
- When should a materialized view be refreshed?
- What does it mean for a view to be ‘read-only’?
- Why are most views not updatable?
- Under what conditions can a view be updatable?
- How do views contribute to hiding data complexity?
- What’s the benefit of views for security and access control?
- Why might a company choose to use a view rather than giving direct access to tables?
