View of Data in DBMS: Types, Examples, Limitations & Best Practices

Updated on April 16, 2026

•

19 min read

ARTICLE OUTLINE

What is View of Data in DBMS?The Importance of Data Views in DBMS Types of Views in DBMS External Schema in DBMS Multiple Views in DBMS – The Three-Schema Architecture How Data Views Work in DBMS View of Data in DBMS with Example – Extended SQL Scenarios Limitations and Challenges of Data Views Best Practices for Managing Data Views View Management in DBMS Conclusion People Also Ask FAQs

A citation-ready guide to data views, external schema, view management, and SQL examples

Understanding the view of data in dbms is fundamental to mastering how database systems manage complexity, security, and user-specific data access. A view of dbms defines how data is presented at different levels of abstraction – shielding users from underlying storage details while providing secure, tailored access to exactly the data they need.

This article covers the various view of data in dbms – logical, physical, and external – along with SQL examples, external schema, the three-schema architecture, view management, limitations, and best practices. Whether you are studying for an exam or designing a production database, this is a complete reference for view of data in dbms.

What is View of Data in DBMS?

A view of data in dbms is a virtual representation of data that presents information from one or more underlying tables without physically storing the data itself. Views are defined using SQL SELECT queries and act as saved query definitions – whenever a user queries a view, the DBMS executes the underlying query and returns the result.

The dbms view of data concept is rooted in the principle of data abstraction – presenting only what is necessary at each level of the system. The view of database in dbms allows different users (end users, administrators, analysts, applications) to interact with the same underlying data in different ways, without any one group needing to understand the full database structure.

Aspect	Description
Definition	A virtual table defined by a SQL SELECT query – stores no data itself
Storage	No physical data storage – data always sourced from underlying base tables
Purpose	Simplify complex queries, enforce security, provide personalised data perspectives
Creation	CREATE VIEW statement in SQL
Modification	CREATE OR REPLACE VIEW – updates the view definition
Deletion	DROP VIEW – removes the view definition (base tables unaffected)
Updatability	Simple views on single tables may be updatable; views with joins or aggregations typically are not

Note: A view is often described as a ‘window into the database’. Users see the data through the window, but the window itself contains no data – it simply frames what is visible from the underlying tables.

POSTGRADUATE PROGRAM IN

Multi Cloud Architecture & DevOps

Master cloud architecture, DevOps practices, and automation to build scalable, resilient systems.

The Importance of Data Views in DBMS

The view of data in dbms serves three primary functions: data abstraction, security enforcement, and query simplification. Together, these make view management in dbms one of the most impactful capabilities a DBA can leverage.

Data Abstraction

Data abstr action hides database complexity from users, presenting only the information they need. Through multiple views in dbms, administrators can create different simplified perspectives of the same underlying data – one for end users, one for managers, one for analysts – all from a single source of truth.

• Ease of User Interaction: Views abstract complex data structures, presenting users with only the relevant columns and rows they need

• Focus on Relevant Data: Users do not need to understand physical data storage or table relationships – the view handles it

• Reduced Cognitive Load: Complex multi-table schemas become simple, single-table-like views for non-technical users

Data Security

External view in dbms is a primary security mechanism. By exposing only specific columns and rows to specific users, views prevent unauthorised access to sensitive information – salary data, personal identifiers, financial records – without requiring separate physical tables for each user group.

• Controlled Data Access: Views restrict access to specific columns and rows – users only see what they are permitted to view

• Protection of Sensitive Data: Administrators create views that exclude restricted columns – salary, SSN, financial data

• Role-Based Data Exposure: Different views can be granted to different database roles, enforcing least-privilege access

Simplifying Complex Queries

Views store complex query logic once, making it reusable. Instead of every user writing multi-table JOIN queries with filters and calculations, they query the view with a simple SELECT – getting consistent, pre-defined results every time.

• Predefined Query Logic: Complex JOINs, aggregations, and filters are defined once in the view and reused across the organisation

• User-Friendly Access: Users retrieve data with a simple SELECT statement – no need to know the underlying schema

• Consistent Data Retrieval: The same business logic is applied uniformly – no risk of individual queries implementing it differently

Types of Views in DBMS

The types of views in dbms are determined by the level of abstraction they represent and the purpose they serve. The three primary types of views in dbms are Logical Views, Physical Views, and External Views. Each operates at a different layer of the database architecture.

View Type	Level	Primary Purpose	Who Uses It
Logical View	Conceptual / Logical	Abstract data structures for user interaction	End users, analysts, business teams
Physical View	Internal / Physical	Optimise storage and access performance	DBAs, storage administrators
External View	External / User	Provide customised, role-specific data access	Individual users, applications, external systems

Logical Views

View level in dbms at the logical layer presents data the way users conceptually think about it – not the way it is physically stored. Logical views abstract the underlying schema, pulling together data from multiple tables into a coherent, user-friendly representation.

• User-Centric Approach: Designed to align with how users think about data – not how it is stored in the physical schema

• Data Abstraction: Hides table relationships, foreign keys, and normalisation complexity from the user

• Multi-Table Combination: Can pull data from multiple tables through JOINs, presenting it as a unified virtual table

Example: An HR logical view combining Employee, Department, and Salary tables so HR staff see FullName, Department, and Salary without knowing the underlying table structure.

Physical Views

Physical views are concerned with how data is stored and retrieved at the hardware level. Unlike logical and external views, physical views are used primarily by DBAs and storage administrators to optimise performance and manage storage resources.

• Storage Optimisation: Organises data storage efficiently to enable fast retrieval and minimise wasted space

• Performance Enhancement: Pre-defined data access paths at the physical level speed up query execution

• Efficient Resource Management: Allows administrators to allocate storage resources to balance performance across the entire system

Example: A materialised view (a type of physical view) that pre-computes and stores the results of an aggregate query – monthly sales totals by region – for fast dashboard reporting without re-querying millions of raw records.

External Views

External view in dbms provides customised, role-specific access to data. This is the view level in dbms that end users and external applications interact with directly – it is their window into the database, showing only what they are permitted to see.

• Customised Data Access: Each user or application gets a view tailored to their role and information needs

• Security and Privacy: Sensitive data is hidden from users who do not require it – only authorised columns and rows are exposed

• Simplified Interaction: Users interact with a clean, relevant data subset without seeing irrelevant or sensitive information

Example: A Sales team external view shows customer name, order date, and order total – but hides internal cost price, supplier details, and margin data visible only to Finance.

External Schema in DBMS

External schema in dbms is the formal term for the collection of all external views defined for a database. In the three-schema (ANSI/SPARC) architecture, the external schema represents the outermost layer – the one that individual users and applications interact with directly.

The external schema in dbms is defined using Data Definition Language (DDL) and maps user-level view definitions to the conceptual schema. Each user or application group can have its own external schema – effectively its own personalised version of the database.

Property	External Schema in DBMS
Also called	View level, User schema, Subschema
Position in architecture	Outermost layer (closest to end users and applications)
Number per database	Many – one per user group, role, or application
Defined using	DDL (Data Definition Language) – CREATE VIEW statements
Maps to	Conceptual schema (logical layer beneath it)
Provides	Data independence – user views remain stable even when the logical schema changes
Security role	Enforces access control by exposing only permitted data subsets to each user group

A critical property of the external schema in dbms is external/conceptual independence – also called logical data independence. Changes to the conceptual schema (adding columns, restructuring tables) do not necessarily require changes to external schemas, as long as the view definitions are updated to reflect the new schema. This independence shields users and applications from disruptive database changes.

External Schema Example – Role-Based View Access

-- external schema in dbms - three role-based external views on the same tables

-- External View 1: HR Staff - see names, departments, hire dates (no salary)

CREATE VIEW HR_View AS

SELECT E.EmployeeID, E.FirstName, E.LastName, D.DepartmentName, E.HireDate

FROM Employees E JOIN Departments D ON E.DepartmentID = D.DepartmentID;

-- External View 2: Finance - see names and salaries only

CREATE VIEW Finance_View AS

SELECT E.EmployeeID, E.FirstName || ' ' || E.LastName AS FullName, E.Salary

FROM Employees E;

-- External View 3: Management - see full picture including department budget

CREATE VIEW Management_View AS

SELECT E.FirstName || ' ' || E.LastName AS FullName,

D.DepartmentName, E.Salary, D.Budget

FROM Employees E JOIN Departments D ON E.DepartmentID = D.DepartmentID;

Each external view is a separate external schema in dbms – all pointing to the same underlying Employees and Departments tables, but presenting different columns to different stakeholders. This is view management in dbms in practice.

82.9%

of professionals don't believe their degree can help them get ahead at work.

Multiple Views in DBMS – The Three-Schema Architecture

Multiple views in dbms are formally structured through the ANSI/SPARC Three-Schema Architecture – the standard model for how data is abstracted across three distinct levels in a DBMS. Understanding this architecture is essential for explaining various view of data in dbms in any DBMS course or interview.

Schema Level	View Type	Who Interacts	What It Defines
External Schema (Level 3)	External Views – view level in dbms	End users, applications	What each user/group sees – personalised data subsets
Conceptual Schema (Level 2)	Logical View – full database structure	DBAs, designers	What data exists – tables, relationships, constraints
Internal Schema (Level 1)	Physical View – storage structure	Storage systems	How data is physically stored – files, indexes, blocks

The view of database in dbms at each level is independent – changes at one level do not automatically propagate to others. This is the principle of data independence that makes modern DBMS systems maintainable and scalable. The multiple views in dbms model ensures that: storage changes (internal) don’t break logical queries (conceptual), and logical schema changes don’t break user applications (external).

Independence Type	What It Protects	Example
Physical Data Independence	Conceptual schema unchanged when internal schema changes	Migrating from HDD to SSD storage doesn’t change table definitions
Logical Data Independence	External schema unchanged when conceptual schema changes	Adding a column to Employees doesn’t break existing user views
View Independence	Users are unaffected by schema changes they don’t interact with	Finance view unchanged when HR adds a new department table

How Data Views Work in DBMS

To fully explain view of data in dbms with working examples, we use two tables – Employees and Departments – to demonstrate creating, querying, updating, and dropping views in SQL. This section provides the complete view of data in dbms with example reference.

Base Tables

Employees Table:

EmployeeID	FirstName	LastName	DepartmentID	Salary
1	John	Doe	101	60000
2	Jane	Smith	102	75000
3	Michael	Johnson	101	55000
4	Emily	Davis	103	80000

Departments Table:

DepartmentID	DepartmentName
101	Sales
102	HR
103	IT

SQL to Create Base Tables

-- Create Employees Table

CREATE TABLE Employees (

EmployeeID INT PRIMARY KEY,

FirstName VARCHAR(50),

LastName VARCHAR(50),

DepartmentID INT,

Salary INT

);

INSERT INTO Employees VALUES

(1,'John','Doe',101,60000),

(2,'Jane','Smith',102,75000),

(3,'Michael','Johnson',101,55000),

(4,'Emily','Davis',103,80000);

-- Create Departments Table

CREATE TABLE Departments (

DepartmentID INT PRIMARY KEY,

DepartmentName VARCHAR(50)

);

INSERT INTO Departments VALUES

(101,'Sales'), (102,'HR'), (103,'IT');

Creating a View in SQL

A view in SQL is a named SELECT query stored in the database. The following view of data in dbms with example creates a view combining both tables:

-- view of data in dbms with example - basic view creation

CREATE VIEW EmployeeDetails AS

SELECT

E.FirstName || ' ' || E.LastName AS FullName,

D.DepartmentName,

E.Salary

FROM Employees E

JOIN Departments D ON E.DepartmentID = D.DepartmentID;

-- Query the view (users interact with this - not the raw tables)

SELECT * FROM EmployeeDetails;

-- Output:

-- FullName | DepartmentName | Salary

-- John Doe | Sales | 60000

-- Jane Smith | HR | 75000

-- Michael Johnson | Sales | 55000

-- Emily Davis | IT | 80000

Updating a View in SQL

-- Add EmployeeID to the view using CREATE OR REPLACE

CREATE OR REPLACE VIEW EmployeeDetails AS

SELECT

E.EmployeeID,

E.FirstName || ' ' || E.LastName AS FullName,

D.DepartmentName,

E.Salary

FROM Employees E

JOIN Departments D ON E.DepartmentID = D.DepartmentID;

Deleting a View in SQL

-- Remove the view (base tables Employees and Departments are unaffected)

DROP VIEW EmployeeDetails;

Note: Dropping a view never deletes data from the underlying base tables. It only removes the view definition from the database schema.

View of Data in DBMS with Example – Extended SQL Scenarios

The following extended view of data in dbms with example scenarios cover the most common real-world view use cases – security filtering, aggregation, updatable views, and materialised views. These examples directly illustrate various view of data in dbms in production-relevant contexts.

Scenario 1: Security View – Hiding Salary from Non-Finance Users

-- dbms view of data - security-based external view

-- HR staff see employee info but NOT salary

CREATE VIEW HR_EmployeeView AS

SELECT EmployeeID, FirstName, LastName, DepartmentID

FROM Employees;

-- GRANT access only to HR role

GRANT SELECT ON HR_EmployeeView TO hr_role;

-- HR staff query: sees no salary column

SELECT * FROM HR_EmployeeView;

-- Output: EmployeeID | FirstName | LastName | DepartmentID

Scenario 2: Aggregation View – Department-Level Salary Summary

-- view of database in dbms - aggregation view for management reporting

CREATE VIEW DeptSalarySummary AS

SELECT

D.DepartmentName,

COUNT(E.EmployeeID) AS HeadCount,

AVG(E.Salary) AS AvgSalary,

MAX(E.Salary) AS MaxSalary,

MIN(E.Salary) AS MinSalary

FROM Employees E

JOIN Departments D ON E.DepartmentID = D.DepartmentID

GROUP BY D.DepartmentName;

SELECT * FROM DeptSalarySummary;

-- Output:

-- DepartmentName | HeadCount | AvgSalary | MaxSalary | MinSalary

-- Sales | 2 | 57500 | 60000 | 55000

-- HR | 1 | 75000 | 75000 | 75000

-- IT | 1 | 80000 | 80000 | 80000

Note: Aggregation views (with GROUP BY) are not updatable – they cannot be used to INSERT, UPDATE, or DELETE rows. They are read-only, analytical views by definition.

Scenario 3: Updatable View – Single-Table View with DML

-- Simple single-table view that supports UPDATE operations

CREATE VIEW SalesEmployees AS

SELECT EmployeeID, FirstName, LastName, Salary

FROM Employees

WHERE DepartmentID = 101;

-- This view IS updatable (single table, no aggregation, no JOIN)

UPDATE SalesEmployees SET Salary = 65000 WHERE EmployeeID = 1;

-- This UPDATE applies directly to the Employees base table

-- Verify the change

SELECT * FROM SalesEmployees;

-- Output: John Doe's salary now shows 65000

Scenario 4: WITH CHECK OPTION – Enforcing View Constraints

-- Prevent inserts/updates that would make rows disappear from the view

CREATE VIEW SalesEmployeesChecked AS

SELECT EmployeeID, FirstName, LastName, Salary, DepartmentID

FROM Employees

WHERE DepartmentID = 101

WITH CHECK OPTION;

-- This INSERT succeeds (DepartmentID = 101 is in scope)

INSERT INTO SalesEmployeesChecked VALUES (5,'Alice','Brown',101,58000);

-- This INSERT FAILS - DepartmentID 102 violates the view's WHERE clause

-- INSERT INTO SalesEmployeesChecked VALUES (6,'Bob','Green',102,70000);

-- ERROR: new row violates check option for view SalesEmployeesChecked

Limitations and Challenges of Data Views

While the view of data in dbms provides significant benefits, understanding its limitations is essential for view management in dbms at production scale:

Limitation	Detail	Mitigation
Performance Overhead	Complex views with multiple JOINs, aggregations, or nested views re-execute the full query each time – potentially slow	Use materialised views for frequently-queried, complex views; apply proper indexing
Restricted Updatability	Views with JOINs, DISTINCT, GROUP BY, UNION, or subqueries are typically not updatable	Use INSTEAD OF triggers (Oracle, SQL Server) to enable updates on complex views
Maintenance Complexity	As base tables evolve (columns added/renamed/removed), view definitions must be manually updated	Document view dependencies; use information_schema to audit view-to-table mappings
Dependency Cascades	Dropping or altering a base table breaks all views that depend on it	Use CASCADE DROP carefully; maintain a view dependency inventory
Hidden Complexity	Views can mask performance issues – users don’t see the complex query running behind a simple SELECT	Use EXPLAIN/EXPLAIN PLAN to analyse view execution plans regularly
No Indexing on Standard Views	Standard (non-materialised) views cannot be directly indexed	Create indexed (materialised) views for performance-critical read paths

Best Practices for Managing Data Views

Effective view management in dbms requires following proven practices that balance usability, performance, and maintainability:

• Keep Views Simple: Avoid overly complex query logic in a single view – break compound logic into multiple simpler views or use CTEs

• Use Indexing Strategically: Apply indexes on columns frequently used in view WHERE clauses and JOIN conditions to optimise query performance

• Limit View Nesting Depth: Avoid views that are built on top of other views (more than 2 levels) – each nesting layer adds query complexity and performance risk

• Apply WITH CHECK OPTION: For updatable views, always use WITH CHECK OPTION to enforce data integrity constraints

• Use Materialised Views for Analytics: For frequently-queried aggregation views, convert to materialised (stored) views to avoid re-executing complex queries on every access

• Conduct Regular Audits: Periodically review the full list of views (via information_schema.views) to identify stale, redundant, or broken views

• Document View Purposes: Maintain a data dictionary entry for each view – what it shows, who uses it, what tables it depends on

• Enforce Role-Based Access: Use GRANT and REVOKE on views (not base tables) to enforce least-privilege access for different user roles

• Test Views After Schema Changes: After any DDL change to base tables, systematically test all dependent views – some DBMS engines silently invalidate views without errors at query time

View Management in DBMS

View management in dbms covers the full lifecycle of views – creation, querying, modification, auditing, and deletion. Effective view management in dbms is critical in any organisation where multiple teams access the same database with different data needs and security levels.

Auditing Views – Listing All Views in a Database

-- List all views in the current database (standard SQL)

SELECT table_name, view_definition

FROM information_schema.views

WHERE table_schema = 'your_database_name';

-- PostgreSQL: check view dependencies

SELECT viewname, definition

FROM pg_views

WHERE schemaname = 'public';

-- MySQL: show all views

SHOW FULL TABLES WHERE Table_type = 'VIEW';

View Dependency Management

-- Find all views that depend on a specific table (PostgreSQL)

SELECT DISTINCT v.viewname

FROM pg_views v

WHERE v.definition LIKE '%Employees%';

-- Before dropping a table, check its view dependencies

-- to prevent cascading failures across the dbms view of data layer

View Management Task	SQL Command	Notes
Create a view	CREATE VIEW name AS SELECT …	Stores query definition; no data stored
Query a view	SELECT * FROM view_name	Executes underlying query on the fly
Modify a view	CREATE OR REPLACE VIEW name AS …	Updates definition; existing grants preserved
Delete a view	DROP VIEW view_name	Only removes definition; base tables unaffected
List all views	SELECT * FROM information_schema.views	System catalog query
Grant view access	GRANT SELECT ON view_name TO role	Enforces external schema access control
Revoke view access	REVOKE SELECT ON view_name FROM role	Removes user access without dropping the view
Create materialised view	CREATE MATERIALISED VIEW name AS …	Stores result physically; requires REFRESH

Conclusion

The view of data in dbms is one of the most powerful and practical features of any database management system. From the external schema in dbms that gives users personalised data access, to the logical and physical views that manage abstraction and performance, view of dbms concepts underpin data security, query simplification, and system maintainability.

Understanding the various view of data in dbms – and knowing when to use logical views, external views, materialised views, and the full view management in dbms toolkit – is essential for any database professional. Properly designed and maintained views reduce complexity for end users, enforce least-privilege security, and create the data independence that allows databases to evolve without breaking dependent applications.

To advance your database skills, explore the Accelerator Program in Business Analytics and Data Science offered by Hero Vired in collaboration with edX and Harvard University.