A3.3.5 Describe different database views. (HL only)

📚 You can find additional information related to these learning standards in the course companion pages 209 to 211

The Big Idea

A database view is a stored query that acts like a virtual table. It doesn’t store data itself (except in the case of materialized views)—instead, it presents a predefined result set to users or applications. Views are a powerful abstraction mechanism in relational databases that help manage complexity, security, and performance.

There are two primary types of views:

1. Virtual views (standard views)
2. Materialized views (snapshot views)

Each has distinct roles in managing data access, query efficiency, and schema flexibility.

---

1. What Is a View?

A view is defined using a SQL SELECT statement and saved in the database under a name. When queried, the view acts like a table—even though it may not store any data of its own.

Example

CREATE VIEW active_students AS
SELECT student_id, name
FROM students
WHERE is_active = TRUE;

You can now run:

SELECT * FROM active_students;

This will always return the latest active students based on the underlying students table.

---

2. Virtual Views (Standard Views)

Definition

A virtual view is a non-materialized query. It does not store data, but simply re-executes its defining SQL query whenever accessed.

Characteristics

• Always returns up-to-date results
• Lightweight: uses no storage
• Slower for complex queries (recomputed each time)
• May or may not be updatable depending on query complexity

Use Cases

• Hiding complex joins
• Simplifying recurring queries
• Enforcing row-level security
• Providing column subsets of sensitive tables

---

3. Materialized Views (Snapshot Views)

Definition

A materialized view is a view whose result set is physically stored in the database as a table-like object.

Characteristics

• Stores a snapshot of query results
• Must be refreshed manually or on a schedule
• Faster query performance—especially for expensive joins or aggregations
• Requires storage space
• Can go stale if not refreshed

Syntax (varies by DBMS)

CREATE MATERIALIZED VIEW monthly_sales_summary AS
SELECT store_id, SUM(sales) AS total_sales
FROM orders
WHERE order_date BETWEEN '2025-05-01' AND '2025-05-31'
GROUP BY store_id;

Refresh Options

• Manual: refreshed on demand
• Automatic: refreshed periodically or after changes (depending on DBMS)
• Incremental: only updated rows are recalculated (if supported)

---

4. Benefits of Using Views

a. Hiding Data Complexity

• Abstracts away joins, filters, and formatting logic.
• Users can work with simplified views without needing to know underlying schema.

-- Complex internal schema
SELECT s.student_id, c.course_name, e.date
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
JOIN courses c ON e.course_id = c.course_id;
-- View hides this complexity
SELECT * FROM student_course_enrollments;

---

b. Data Consistency and Logical Independence

• Views present consistent access patterns, even if the underlying schema changes.
• Applications can query a view with the same name, even if the base tables are restructured.
• Views help support logical data independence, a core goal of DBMS design.

---

c. Query Simplification

• Reuse common queries without rewriting them.
• Promote modular design by creating views for specific use cases.

---

d. Security

• Restrict access to sensitive columns or rows.
• Combine with GRANT/REVOKE to expose views but not base tables.

-- Expose only limited fields to certain users
CREATE VIEW public_employees AS
SELECT name, department FROM employees;

---

e. Performance Optimization

• Materialized views improve performance by caching expensive query results.
• Useful in data warehousing for precomputed aggregations, summary tables, and OLAP operations.

---

5. Limitations of Views

Aspect	Virtual Views	Materialized Views
Up-to-date data	Always current	May become stale (requires refresh)
Storage cost	None	Consumes disk space
Performance	May be slow on complex queries	Fast reads; optimized for analytics
Updatability	May not be updatable	Not updatable in most systems

Updatable Views: A view is updatable if:

• It is based on a single table
• It does not contain aggregation, GROUP BY, DISTINCT, or joins
• It has a direct mapping to underlying columns

---

6. Summary Table

Feature	Virtual View	Materialized View
Stores data	No	Yes
Always up-to-date	Yes	No (needs refresh)
Performance	Slower on complex queries	Faster for repeated queries
Use case	Simplification, security, abstraction	Reporting, aggregation, data warehousing
Refresh required	No	Yes
Read-only or writable	Often read-only, may be updatable	Read-only

---

Conclusion

Database views—both virtual and materialized—are essential tools in advanced database design. They:

• Simplify query logic
• Enforce security
• Abstract complexity
• Boost performance for analytical workloads

HL students must understand the technical differences, architectural implications, and strategic applications of views, particularly in multi-user, large-scale, or read-intensive systems. Mastery of views allows database designers to build modular, secure, and high-performance systems that scale efficiently and support flexible data access patterns.