Database Management SystemDatabase System Architecture

Three-Level Architecture

LevelBeginner

Duration60 mins

TopicDatabase System Architecture

1 / 5

External Level (Views)

The User's Window into the Database

Imagine a large hospital with a database containing millions of patient records, financial transactions, medication inventories, staff schedules, and research data. A receptionist needs to see patient names and appointment times. A pharmacist needs medication dosages and allergies. A researcher needs anonymized health statistics. A billing specialist needs insurance and payment information.

The problem: How does one massive database serve such radically different users with radically different needs—while keeping each from seeing or modifying data they shouldn't access?

The solution: The External Level—the topmost layer of the three-level database architecture that presents customized, secure, and relevant views of data to different users and applications.

What You Will Learn

By the end of this page, you will understand what the external level is, why it exists, how views are defined and used, and why the external level is critical for security, usability, and flexibility in database systems. You'll see concrete examples of external schemas and understand how they relate to the underlying conceptual schema.

Understanding the External Level

The external level (also called the view level or user level) is the highest level of abstraction in the three-level database architecture. It represents the part of the database that is directly visible to end users and application programs.

Definition and Purpose

The external level consists of multiple external schemas (also called subschemas or views). Each external schema describes a specific portion of the database that a particular user group or application needs to access. It hides the complexity of the overall database structure while presenting data in a form most suitable for each user's requirements.

Formal Definition:

An external schema is a description of part of the database's logical structure, tailored to the data processing needs of a specific user or user group, while abstracting away both the physical storage details and irrelevant portions of the logical structure.

Key Characteristics of the External Level
Characteristic	Description	Example
User-Oriented	Each view is designed for specific user needs	Sales team sees customer orders; HR sees employee records
Customized Presentation	Data format and structure tailored per view	Dates shown as 'January 15, 2024' vs '2024-01-15'
Data Hiding	Irrelevant or sensitive data is not visible	Salary hidden from general employee directory view
Logical Derivation	Views derived from conceptual schema elements	Monthly sales summary computed from transaction records
Multiple Views Coexist	Many external schemas exist simultaneously	50+ different views for different departments

Think of It Like Different Lenses

The external level is like providing different lenses to view the same underlying reality. A marketing lens shows customer demographics and purchase patterns. A logistics lens shows inventory locations and shipping addresses. A finance lens shows transaction amounts and payment status. Same data, different views—each optimized for its purpose.

External Schemas in Detail

An external schema (or user view) is a logical description of a subset of the database as seen by a particular user or application. It acts as a virtual table derived from the base tables defined in the conceptual schema.

Components of an External Schema

An external schema typically includes:

View Definitions — Virtual tables created by querying and combining base tables
Column Specifications — Which attributes are visible and how they're named
Data Transformations — Computed values, aggregations, format conversions
Access Constraints — Which operations (read, insert, update, delete) are permitted
Presentation Formatting — How data appears to users

external_schema_examples.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
-- Conceptual Schema: The actual base table
CREATE TABLE Employee (
    employee_id     INT PRIMARY KEY,
    first_name      VARCHAR(50),
    last_name       VARCHAR(50),
    email           VARCHAR(100),
    phone           VARCHAR(20),
    department_id   INT,
    hire_date       DATE,
    salary          DECIMAL(10,2),
    ssn             CHAR(11),             -- Sensitive!
    performance_rating INT,
    manager_id      INT
);
 
-- External Schema 1: HR Manager View (Full Access)
CREATE VIEW HR_Employee_View AS
SELECT 
    employee_id,
    first_name || ' ' || last_name AS full_name,
    email,
    phone,
    department_id,
    hire_date,
    salary,
    CASE 
        WHEN performance_rating >= 4 THEN 'Excellent'
        WHEN performance_rating >= 3 THEN 'Good'
        WHEN performance_rating >= 2 THEN 'Needs Improvement'
        ELSE 'Under Review'
    END AS performance_status
FROM Employee;
 
-- External Schema 2: Employee Directory View (Limited Access)
CREATE VIEW Employee_Directory_View AS
SELECT 
    first_name,
    last_name,
    email,
    phone,
    d.department_name
FROM Employee e
JOIN Department d ON e.department_id = d.department_id;
-- Note: No salary, no SSN, no performance data
 
-- External Schema 3: Payroll System View (Financial Only)
CREATE VIEW Payroll_View AS
SELECT 
    employee_id,
    first_name || ' ' || last_name AS employee_name,
    salary,
    hire_date,
    CASE 
        WHEN hire_date < CURRENT_DATE - INTERVAL '1 year' 
        THEN salary * 0.05 
        ELSE 0 
    END AS annual_bonus_estimate
FROM Employee;
-- Note: Performance visible indirectly through bonus calculation

What Makes Each Schema Different

Notice how each view in the example above serves a distinct purpose:

View	Purpose	Data Exposed	Data Hidden	Transformation Applied
HR Manager	Performance management	Full employee details	SSN for compliance	Rating → Status label
Directory	Internal communication	Contact info only	Salary, SSN, performance	Department name joined
Payroll	Compensation processing	Financial data only	Contact, performance	Bonus calculation added

This is the power of external schemas: one underlying table, multiple useful representations.

Views vs. Copies

External views are not physical copies of data—they are dynamic windows into the base tables. When underlying data changes, views reflect those changes instantly. This is fundamentally different from creating separate tables for each department, which would lead to redundancy, inconsistency, and synchronization nightmares.

Why External Views Matter

The external level serves several critical functions in database systems. Understanding these purposes clarifies why the three-level architecture became the industry standard.

Core Functions of the External Level

•Data Abstraction — Users interact with simplified representations. A CEO doesn't need to understand table joins to see quarterly revenue. The complexity is hidden behind intuitive views.
•Data Security — Sensitive data is protected by exclusion. If a view doesn't include salary data, users of that view cannot see salaries—period. This is security by architecture, not just policy.
•Customized Presentation — Each user group sees data formatted for their needs. Finance sees currency symbols and decimal precision. Marketing sees percentages and trends. Same data, optimal presentation.
•Logical Independence — Changes to the conceptual schema (adding columns, renaming tables) can be absorbed by the external level. Views can be updated to maintain the same interface to users.
•Query Simplification — Complex joins, aggregations, and computations can be encapsulated in views. Users write simple queries against views instead of complex queries against base tables.

Without External Level

•Every user sees entire database structure
•Security depends on application-level checks
•Schema changes break all applications
•Users must understand complex data model
•Same complex queries repeated everywhere
•Sensitive data exposed accidentally

With External Level

•Each user sees only relevant subset
•Security enforced by DBMS at view level
•Views absorb schema changes transparently
•Users interact with intuitive structures
•Complex logic encapsulated once in views
•Data hidden by architectural design

Security Is Not Just About Views

While views provide powerful data hiding, they are one layer of defense, not the only layer. A comprehensive security strategy also includes authentication, authorization grants on views, row-level security policies, audit logging, and encryption. Views are necessary but not sufficient for security.

View Implementation Deep Dive

Understanding how views are implemented clarifies their capabilities and limitations. There are two fundamental approaches to view implementation, each with distinct trade-offs.

Virtual views store only the view definition (the SQL query), not the data itself. When queried, the DBMS dynamically executes the definition against current base table data.

How Virtual Views Work

User queries the view: SELECT * FROM Employee_Directory_View
DBMS retrieves the view definition (the stored query)
DBMS merges user query with view query (view expansion)
Combined query executes against base tables
Results return as if the view were a real table

Virtual View Characteristics
Aspect	Behavior
Storage	No data stored—only the query definition
Data Freshness	Always current (reads from base tables)
Query Performance	May be slower (joins computed on each access)
Maintenance	No refresh needed—always synchronized
Use Case	Real-time data, frequently changing bases

view_expansion_example.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- User Query:
SELECT full_name, department_name 
FROM Employee_Directory_View 
WHERE department_name = 'Engineering';
 
-- View Definition:
CREATE VIEW Employee_Directory_View AS
SELECT e.first_name || ' ' || e.last_name AS full_name, 
       d.department_name
FROM Employee e 
JOIN Department d ON e.department_id = d.department_id;
 
-- After View Expansion (what DBMS actually executes):
SELECT e.first_name || ' ' || e.last_name AS full_name,
       d.department_name
FROM Employee e 
JOIN Department d ON e.department_id = d.department_id
WHERE d.department_name = 'Engineering';

Choosing Between Virtual and Materialized

Use virtual views when data must always be current and queries are simple enough to execute quickly. Use materialized views when queries are complex (heavy aggregations, multiple joins), data doesn't change frequently, or slight staleness is acceptable (like daily reports). Many production systems use both strategically.

Updatable Views and Their Constraints

A natural question arises: can you modify data through a view? The answer is nuanced—sometimes yes, but often with restrictions.

The Updatability Challenge

Views are logical projections of base tables. Updating a view means translating that update into operations on the underlying base tables. This translation is straightforward for simple views but becomes ambiguous or impossible for complex views.

View Updatability Rules
View Type	Updatable?	Reason
Simple single-table view (subset of columns)	✅ Yes	Clear 1:1 mapping to base table rows
View with computed columns	⚠️ Partial	Base columns updatable; computed columns not
View with aggregates (SUM, COUNT, AVG)	❌ No	No way to reverse aggregation to individual rows
View with DISTINCT	❌ No	Cannot identify which original rows to update
View joining multiple tables	⚠️ Partial	Key-preserving joins may allow updates on some columns
View with GROUP BY	❌ No	Grouped rows cannot be unmapped to source rows
View with set operations (UNION)	❌ No	Cannot determine which source produced which row

updatable_view_examples.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
-- ✅ UPDATABLE: Simple projection view
CREATE VIEW Active_Employees AS
SELECT employee_id, first_name, last_name, email, department_id
FROM Employee
WHERE status = 'active';
 
-- This works: clear mapping to base table row
UPDATE Active_Employees 
SET email = 'newemail@company.com' 
WHERE employee_id = 123;
 
-- ❌ NOT UPDATABLE: Aggregate view
CREATE VIEW Department_Salary_Stats AS
SELECT department_id, AVG(salary) AS avg_salary, COUNT(*) AS emp_count
FROM Employee
GROUP BY department_id;
 
-- This fails: what does it mean to update an average?
UPDATE Department_Salary_Stats 
SET avg_salary = 75000 
WHERE department_id = 5;
-- Error: Cannot update aggregate view
 
-- ⚠️ PARTIALLY UPDATABLE: View with computed column
CREATE VIEW Employee_Full_Name AS
SELECT employee_id, 
       first_name, 
       last_name,
       first_name || ' ' || last_name AS full_name
FROM Employee;
 
-- This works: updating base columns
UPDATE Employee_Full_Name 
SET first_name = 'Jonathan' 
WHERE employee_id = 123;
 
-- This fails: cannot update computed column
UPDATE Employee_Full_Name 
SET full_name = 'Jonathan Smith' 
WHERE employee_id = 123;
-- Error: Cannot update generated column

INSTEAD OF Triggers

Some databases allow defining INSTEAD OF triggers on views. These triggers intercept update operations and execute custom logic to modify base tables appropriately. This allows 'updatable' behavior on otherwise non-updatable views, but requires careful implementation to maintain data integrity.

External Schema Design Principles

Designing effective external schemas requires balancing usability, security, performance, and maintainability. Here are principles that guide professional database architects:

Principles for Effective View Design

•Principle of Least Privilege — Each view should expose the minimum data necessary for its intended use. Never include 'just in case' columns that aren't needed.
•Semantic Clarity — View names and column names should clearly describe what data is presented. Sales_By_Region_Monthly is better than Sales_View_3.
•Stable Interfaces — Views should provide stable interfaces to applications. If the underlying schema changes, update views to maintain the same external interface.
•Performance Awareness — Avoid views that perform expensive operations on every query. Consider materialized views for heavy aggregations accessed frequently.
•Layered Views — Build simple base views, then compose complex views from simpler ones. This promotes reuse and simplifies debugging.
•Documentation — Document each view's purpose, intended users, refresh schedule (if materialized), and dependencies on base tables.

layered_view_design.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
-- Layer 1: Base cleansing view (standardizes data)
CREATE VIEW v_Employee_Clean AS
SELECT 
    employee_id,
    TRIM(UPPER(first_name)) AS first_name,
    TRIM(UPPER(last_name)) AS last_name,
    LOWER(email) AS email,
    COALESCE(department_id, 0) AS department_id,  -- Default for null
    hire_date,
    COALESCE(salary, 0) AS salary
FROM Employee
WHERE status != 'deleted';  -- Soft deletes filtered out
 
-- Layer 2: Enrichment view (joins with reference data)
CREATE VIEW v_Employee_Enriched AS
SELECT 
    e.*,
    d.department_name,
    d.cost_center,
    m.first_name || ' ' || m.last_name AS manager_name
FROM v_Employee_Clean e
LEFT JOIN Department d ON e.department_id = d.department_id
LEFT JOIN v_Employee_Clean m ON e.manager_id = m.employee_id;
 
-- Layer 3: Application-specific views (for end users)
CREATE VIEW HR_Dashboard AS
SELECT 
    department_name,
    COUNT(*) AS headcount,
    AVG(salary) AS avg_salary,
    MIN(hire_date) AS earliest_hire,
    MAX(hire_date) AS latest_hire
FROM v_Employee_Enriched
GROUP BY department_name;
 
-- Benefits of layering:
-- 1. Cleansing logic defined once, reused everywhere
-- 2. Changes to cleansing propagate to all derived views
-- 3. Each layer has a clear, testable responsibility
-- 4. Complex views become compositions of simple ones

Real-World Case Study: E-Commerce Platform

Let's examine how external schemas work in a realistic e-commerce platform scenario. The conceptual schema includes tables for Products, Customers, Orders, OrderItems, Inventory, Suppliers, and Payments.

Different stakeholders require different views of this data:

External Schemas by Stakeholder
Stakeholder	View Name	Data Included	Data Excluded	Transformations
Customer App	Customer_Order_History	Order dates, items, totals, status	Internal costs, supplier info, IP logs	Prices formatted with currency symbols
Warehouse	Pending_Shipments	Order ID, ship address, items, quantities	Payment details, customer email	Items aggregated by location zone
Finance	Revenue_By_Period	Order totals, payment status, refunds	Customer PII, shipping addresses	Aggregated by day/week/month
Marketing	Customer_Segments	Purchase history, categories, frequency	Payment info, addresses, SSN	RFM scores calculated
Supplier Portal	Supplier_Orders	Their products ordered, quantities	Other suppliers' data, pricing	Filtered by supplier_id

ecommerce_views.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
-- Customer-facing order history view
CREATE VIEW Customer_Order_History AS
SELECT 
    o.order_id,
    o.customer_id,
    o.order_date,
    o.order_status,
    '$' || TO_CHAR(o.total_amount, 'FM999,999.00') AS formatted_total,
    STRING_AGG(p.product_name, ', ') AS items_summary,
    COUNT(oi.item_id) AS item_count
FROM Orders o
JOIN OrderItems oi ON o.order_id = oi.order_id
JOIN Products p ON oi.product_id = p.product_id
WHERE o.order_status != 'cancelled'
GROUP BY o.order_id, o.customer_id, o.order_date, 
         o.order_status, o.total_amount;
 
-- Warehouse shipping view
CREATE VIEW Pending_Shipments AS
SELECT 
    o.order_id,
    c.shipping_address,
    c.shipping_city,
    c.shipping_zip,
    oi.product_id,
    p.product_name,
    oi.quantity,
    inv.warehouse_location,
    inv.bin_number,
    o.promised_ship_date
FROM Orders o
JOIN Customers c ON o.customer_id = c.customer_id
JOIN OrderItems oi ON o.order_id = oi.order_id
JOIN Products p ON oi.product_id = p.product_id
JOIN Inventory inv ON p.product_id = inv.product_id
WHERE o.order_status = 'paid' 
  AND o.shipped_date IS NULL
ORDER BY o.promised_ship_date;
 
-- Finance revenue materialized view (refreshed nightly)
CREATE MATERIALIZED VIEW Revenue_By_Period AS
SELECT 
    DATE_TRUNC('day', o.order_date) AS period_day,
    DATE_TRUNC('week', o.order_date) AS period_week,
    DATE_TRUNC('month', o.order_date) AS period_month,
    SUM(o.total_amount) AS gross_revenue,
    SUM(o.total_amount) FILTER (WHERE o.order_status = 'refunded') AS refunds,
    SUM(o.total_amount) - SUM(o.total_amount) FILTER (WHERE o.order_status = 'refunded') AS net_revenue,
    COUNT(DISTINCT o.order_id) AS order_count,
    COUNT(DISTINCT o.customer_id) AS unique_customers
FROM Orders o
WHERE o.order_date >= CURRENT_DATE - INTERVAL '2 years'
GROUP BY DATE_TRUNC('day', o.order_date),
         DATE_TRUNC('week', o.order_date),
         DATE_TRUNC('month', o.order_date);

The Power of Isolation

Notice how each view is completely isolated from others. The warehouse team cannot access payment data. Marketing cannot see shipping addresses. The customer app cannot see internal cost margins. This isolation is enforced by the DBMS, not by application code—making it robust and auditable.

Summary: The External Level

We've explored the external level in depth. Here are the essential takeaways:

Key Takeaways

•The external level is the user-facing layer — It provides customized views of the database for different users and applications.
•External schemas hide complexity and enforce security — Users see only what they need; sensitive data is architecturally protected.
•Views are logical, not physical — Virtual views execute queries dynamically; materialized views cache results for performance.
•View updatability has constraints — Simple views can be updated; views with aggregates, DISTINCT, or GROUP BY generally cannot.
•Good view design follows principles — Least privilege, semantic clarity, stable interfaces, performance awareness, and layering.
•The external level enables data independence — Applications depend on views, not base tables, insulating them from schema changes.

What's Next:

Now that we understand the external level—the user-facing layer—we'll descend one level deeper to explore the conceptual level. This is where the complete logical structure of the entire database is defined, independent of how users see it or how it's physically stored.

Page Complete

You now understand the external level of the three-level database architecture. You can explain what views are, how they work (virtual vs. materialized), their constraints around updatability, and design principles for creating effective external schemas. Next, we explore the conceptual level—the heart of the database's logical structure.

1 / 5

Loading learning content...

Database Management SystemDatabase System Architecture

Three-Level Architecture

LevelBeginner

Duration60 mins

TopicDatabase System Architecture

1 / 5

External Level (Views)

The User's Window into the Database

The problem: How does one massive database serve such radically different users with radically different needs—while keeping each from seeing or modifying data they shouldn't access?

The solution: The External Level—the topmost layer of the three-level database architecture that presents customized, secure, and relevant views of data to different users and applications.

What You Will Learn

Understanding the External Level

Definition and Purpose

Formal Definition:

An external schema is a description of part of the database's logical structure, tailored to the data processing needs of a specific user or user group, while abstracting away both the physical storage details and irrelevant portions of the logical structure.

Key Characteristics of the External Level
Characteristic	Description	Example
User-Oriented	Each view is designed for specific user needs	Sales team sees customer orders; HR sees employee records
Customized Presentation	Data format and structure tailored per view	Dates shown as 'January 15, 2024' vs '2024-01-15'
Data Hiding	Irrelevant or sensitive data is not visible	Salary hidden from general employee directory view
Logical Derivation	Views derived from conceptual schema elements	Monthly sales summary computed from transaction records
Multiple Views Coexist	Many external schemas exist simultaneously	50+ different views for different departments

Think of It Like Different Lenses

External Schemas in Detail

Components of an External Schema

An external schema typically includes:

View Definitions — Virtual tables created by querying and combining base tables
Column Specifications — Which attributes are visible and how they're named
Data Transformations — Computed values, aggregations, format conversions
Access Constraints — Which operations (read, insert, update, delete) are permitted
Presentation Formatting — How data appears to users

external_schema_examples.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
-- Conceptual Schema: The actual base table
CREATE TABLE Employee (
    employee_id     INT PRIMARY KEY,
    first_name      VARCHAR(50),
    last_name       VARCHAR(50),
    email           VARCHAR(100),
    phone           VARCHAR(20),
    department_id   INT,
    hire_date       DATE,
    salary          DECIMAL(10,2),
    ssn             CHAR(11),             -- Sensitive!
    performance_rating INT,
    manager_id      INT
);
 
-- External Schema 1: HR Manager View (Full Access)
CREATE VIEW HR_Employee_View AS
SELECT 
    employee_id,
    first_name || ' ' || last_name AS full_name,
    email,
    phone,
    department_id,
    hire_date,
    salary,
    CASE 
        WHEN performance_rating >= 4 THEN 'Excellent'
        WHEN performance_rating >= 3 THEN 'Good'
        WHEN performance_rating >= 2 THEN 'Needs Improvement'
        ELSE 'Under Review'
    END AS performance_status
FROM Employee;
 
-- External Schema 2: Employee Directory View (Limited Access)
CREATE VIEW Employee_Directory_View AS
SELECT 
    first_name,
    last_name,
    email,
    phone,
    d.department_name
FROM Employee e
JOIN Department d ON e.department_id = d.department_id;
-- Note: No salary, no SSN, no performance data
 
-- External Schema 3: Payroll System View (Financial Only)
CREATE VIEW Payroll_View AS
SELECT 
    employee_id,
    first_name || ' ' || last_name AS employee_name,
    salary,
    hire_date,
    CASE 
        WHEN hire_date < CURRENT_DATE - INTERVAL '1 year' 
        THEN salary * 0.05 
        ELSE 0 
    END AS annual_bonus_estimate
FROM Employee;
-- Note: Performance visible indirectly through bonus calculation

What Makes Each Schema Different

Notice how each view in the example above serves a distinct purpose:

View	Purpose	Data Exposed	Data Hidden	Transformation Applied
HR Manager	Performance management	Full employee details	SSN for compliance	Rating → Status label
Directory	Internal communication	Contact info only	Salary, SSN, performance	Department name joined
Payroll	Compensation processing	Financial data only	Contact, performance	Bonus calculation added

This is the power of external schemas: one underlying table, multiple useful representations.

Views vs. Copies

Why External Views Matter

The external level serves several critical functions in database systems. Understanding these purposes clarifies why the three-level architecture became the industry standard.

Core Functions of the External Level

•Data Abstraction — Users interact with simplified representations. A CEO doesn't need to understand table joins to see quarterly revenue. The complexity is hidden behind intuitive views.
•Data Security — Sensitive data is protected by exclusion. If a view doesn't include salary data, users of that view cannot see salaries—period. This is security by architecture, not just policy.
•Customized Presentation — Each user group sees data formatted for their needs. Finance sees currency symbols and decimal precision. Marketing sees percentages and trends. Same data, optimal presentation.
•Logical Independence — Changes to the conceptual schema (adding columns, renaming tables) can be absorbed by the external level. Views can be updated to maintain the same interface to users.
•Query Simplification — Complex joins, aggregations, and computations can be encapsulated in views. Users write simple queries against views instead of complex queries against base tables.

Without External Level

•Every user sees entire database structure
•Security depends on application-level checks
•Schema changes break all applications
•Users must understand complex data model
•Same complex queries repeated everywhere
•Sensitive data exposed accidentally

With External Level

•Each user sees only relevant subset
•Security enforced by DBMS at view level
•Views absorb schema changes transparently
•Users interact with intuitive structures
•Complex logic encapsulated once in views
•Data hidden by architectural design

Security Is Not Just About Views

View Implementation Deep Dive

Understanding how views are implemented clarifies their capabilities and limitations. There are two fundamental approaches to view implementation, each with distinct trade-offs.

Virtual views store only the view definition (the SQL query), not the data itself. When queried, the DBMS dynamically executes the definition against current base table data.

How Virtual Views Work

User queries the view: SELECT * FROM Employee_Directory_View
DBMS retrieves the view definition (the stored query)
DBMS merges user query with view query (view expansion)
Combined query executes against base tables
Results return as if the view were a real table

Virtual View Characteristics
Aspect	Behavior
Storage	No data stored—only the query definition
Data Freshness	Always current (reads from base tables)
Query Performance	May be slower (joins computed on each access)
Maintenance	No refresh needed—always synchronized
Use Case	Real-time data, frequently changing bases

view_expansion_example.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- User Query:
SELECT full_name, department_name 
FROM Employee_Directory_View 
WHERE department_name = 'Engineering';
 
-- View Definition:
CREATE VIEW Employee_Directory_View AS
SELECT e.first_name || ' ' || e.last_name AS full_name, 
       d.department_name
FROM Employee e 
JOIN Department d ON e.department_id = d.department_id;
 
-- After View Expansion (what DBMS actually executes):
SELECT e.first_name || ' ' || e.last_name AS full_name,
       d.department_name
FROM Employee e 
JOIN Department d ON e.department_id = d.department_id
WHERE d.department_name = 'Engineering';

Choosing Between Virtual and Materialized

Updatable Views and Their Constraints

A natural question arises: can you modify data through a view? The answer is nuanced—sometimes yes, but often with restrictions.

The Updatability Challenge

View Updatability Rules
View Type	Updatable?	Reason
Simple single-table view (subset of columns)	✅ Yes	Clear 1:1 mapping to base table rows
View with computed columns	⚠️ Partial	Base columns updatable; computed columns not
View with aggregates (SUM, COUNT, AVG)	❌ No	No way to reverse aggregation to individual rows
View with DISTINCT	❌ No	Cannot identify which original rows to update
View joining multiple tables	⚠️ Partial	Key-preserving joins may allow updates on some columns
View with GROUP BY	❌ No	Grouped rows cannot be unmapped to source rows
View with set operations (UNION)	❌ No	Cannot determine which source produced which row

updatable_view_examples.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
-- ✅ UPDATABLE: Simple projection view
CREATE VIEW Active_Employees AS
SELECT employee_id, first_name, last_name, email, department_id
FROM Employee
WHERE status = 'active';
 
-- This works: clear mapping to base table row
UPDATE Active_Employees 
SET email = 'newemail@company.com' 
WHERE employee_id = 123;
 
-- ❌ NOT UPDATABLE: Aggregate view
CREATE VIEW Department_Salary_Stats AS
SELECT department_id, AVG(salary) AS avg_salary, COUNT(*) AS emp_count
FROM Employee
GROUP BY department_id;
 
-- This fails: what does it mean to update an average?
UPDATE Department_Salary_Stats 
SET avg_salary = 75000 
WHERE department_id = 5;
-- Error: Cannot update aggregate view
 
-- ⚠️ PARTIALLY UPDATABLE: View with computed column
CREATE VIEW Employee_Full_Name AS
SELECT employee_id, 
       first_name, 
       last_name,
       first_name || ' ' || last_name AS full_name
FROM Employee;
 
-- This works: updating base columns
UPDATE Employee_Full_Name 
SET first_name = 'Jonathan' 
WHERE employee_id = 123;
 
-- This fails: cannot update computed column
UPDATE Employee_Full_Name 
SET full_name = 'Jonathan Smith' 
WHERE employee_id = 123;
-- Error: Cannot update generated column

INSTEAD OF Triggers

External Schema Design Principles

Designing effective external schemas requires balancing usability, security, performance, and maintainability. Here are principles that guide professional database architects:

Principles for Effective View Design

•Principle of Least Privilege — Each view should expose the minimum data necessary for its intended use. Never include 'just in case' columns that aren't needed.
•Semantic Clarity — View names and column names should clearly describe what data is presented. Sales_By_Region_Monthly is better than Sales_View_3.
•Stable Interfaces — Views should provide stable interfaces to applications. If the underlying schema changes, update views to maintain the same external interface.
•Performance Awareness — Avoid views that perform expensive operations on every query. Consider materialized views for heavy aggregations accessed frequently.
•Layered Views — Build simple base views, then compose complex views from simpler ones. This promotes reuse and simplifies debugging.
•Documentation — Document each view's purpose, intended users, refresh schedule (if materialized), and dependencies on base tables.

layered_view_design.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
-- Layer 1: Base cleansing view (standardizes data)
CREATE VIEW v_Employee_Clean AS
SELECT 
    employee_id,
    TRIM(UPPER(first_name)) AS first_name,
    TRIM(UPPER(last_name)) AS last_name,
    LOWER(email) AS email,
    COALESCE(department_id, 0) AS department_id,  -- Default for null
    hire_date,
    COALESCE(salary, 0) AS salary
FROM Employee
WHERE status != 'deleted';  -- Soft deletes filtered out
 
-- Layer 2: Enrichment view (joins with reference data)
CREATE VIEW v_Employee_Enriched AS
SELECT 
    e.*,
    d.department_name,
    d.cost_center,
    m.first_name || ' ' || m.last_name AS manager_name
FROM v_Employee_Clean e
LEFT JOIN Department d ON e.department_id = d.department_id
LEFT JOIN v_Employee_Clean m ON e.manager_id = m.employee_id;
 
-- Layer 3: Application-specific views (for end users)
CREATE VIEW HR_Dashboard AS
SELECT 
    department_name,
    COUNT(*) AS headcount,
    AVG(salary) AS avg_salary,
    MIN(hire_date) AS earliest_hire,
    MAX(hire_date) AS latest_hire
FROM v_Employee_Enriched
GROUP BY department_name;
 
-- Benefits of layering:
-- 1. Cleansing logic defined once, reused everywhere
-- 2. Changes to cleansing propagate to all derived views
-- 3. Each layer has a clear, testable responsibility
-- 4. Complex views become compositions of simple ones

Real-World Case Study: E-Commerce Platform

Different stakeholders require different views of this data:

External Schemas by Stakeholder
Stakeholder	View Name	Data Included	Data Excluded	Transformations
Customer App	Customer_Order_History	Order dates, items, totals, status	Internal costs, supplier info, IP logs	Prices formatted with currency symbols
Warehouse	Pending_Shipments	Order ID, ship address, items, quantities	Payment details, customer email	Items aggregated by location zone
Finance	Revenue_By_Period	Order totals, payment status, refunds	Customer PII, shipping addresses	Aggregated by day/week/month
Marketing	Customer_Segments	Purchase history, categories, frequency	Payment info, addresses, SSN	RFM scores calculated
Supplier Portal	Supplier_Orders	Their products ordered, quantities	Other suppliers' data, pricing	Filtered by supplier_id

ecommerce_views.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
-- Customer-facing order history view
CREATE VIEW Customer_Order_History AS
SELECT 
    o.order_id,
    o.customer_id,
    o.order_date,
    o.order_status,
    '$' || TO_CHAR(o.total_amount, 'FM999,999.00') AS formatted_total,
    STRING_AGG(p.product_name, ', ') AS items_summary,
    COUNT(oi.item_id) AS item_count
FROM Orders o
JOIN OrderItems oi ON o.order_id = oi.order_id
JOIN Products p ON oi.product_id = p.product_id
WHERE o.order_status != 'cancelled'
GROUP BY o.order_id, o.customer_id, o.order_date, 
         o.order_status, o.total_amount;
 
-- Warehouse shipping view
CREATE VIEW Pending_Shipments AS
SELECT 
    o.order_id,
    c.shipping_address,
    c.shipping_city,
    c.shipping_zip,
    oi.product_id,
    p.product_name,
    oi.quantity,
    inv.warehouse_location,
    inv.bin_number,
    o.promised_ship_date
FROM Orders o
JOIN Customers c ON o.customer_id = c.customer_id
JOIN OrderItems oi ON o.order_id = oi.order_id
JOIN Products p ON oi.product_id = p.product_id
JOIN Inventory inv ON p.product_id = inv.product_id
WHERE o.order_status = 'paid' 
  AND o.shipped_date IS NULL
ORDER BY o.promised_ship_date;
 
-- Finance revenue materialized view (refreshed nightly)
CREATE MATERIALIZED VIEW Revenue_By_Period AS
SELECT 
    DATE_TRUNC('day', o.order_date) AS period_day,
    DATE_TRUNC('week', o.order_date) AS period_week,
    DATE_TRUNC('month', o.order_date) AS period_month,
    SUM(o.total_amount) AS gross_revenue,
    SUM(o.total_amount) FILTER (WHERE o.order_status = 'refunded') AS refunds,
    SUM(o.total_amount) - SUM(o.total_amount) FILTER (WHERE o.order_status = 'refunded') AS net_revenue,
    COUNT(DISTINCT o.order_id) AS order_count,
    COUNT(DISTINCT o.customer_id) AS unique_customers
FROM Orders o
WHERE o.order_date >= CURRENT_DATE - INTERVAL '2 years'
GROUP BY DATE_TRUNC('day', o.order_date),
         DATE_TRUNC('week', o.order_date),
         DATE_TRUNC('month', o.order_date);

The Power of Isolation

Summary: The External Level

We've explored the external level in depth. Here are the essential takeaways:

Key Takeaways

•The external level is the user-facing layer — It provides customized views of the database for different users and applications.
•External schemas hide complexity and enforce security — Users see only what they need; sensitive data is architecturally protected.
•Views are logical, not physical — Virtual views execute queries dynamically; materialized views cache results for performance.
•View updatability has constraints — Simple views can be updated; views with aggregates, DISTINCT, or GROUP BY generally cannot.
•Good view design follows principles — Least privilege, semantic clarity, stable interfaces, performance awareness, and layering.
•The external level enables data independence — Applications depend on views, not base tables, insulating them from schema changes.

What's Next:

Page Complete

1 / 5