Scribd
Upload
8 views4 pages

Understanding Database Views in RDBMS

A view is a logical representation of a table that has no storage of its own. It acts like a virtual table that is defined by a query. Views can be used to simplify commands, hide complexity, and provide additional security by restricting access to rows and columns. There are different types of views including simple views from one table or complex views from multiple tables. Views also have certain restrictions on allowed DML operations depending on if they include functions, expressions, or not null columns.

Uploaded by

siva
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
8 views4 pages

Understanding Database Views in RDBMS

A view is a logical representation of a table that has no storage of its own. It acts like a virtual table that is defined by a query. Views can be used to simplify commands, hide complexity, and provide additional security by restricting access to rows and columns. There are different types of views including simple views from one table or complex views from multiple tables. Views also have certain restrictions on allowed DML operations depending on if they include functions, expressions, or not null columns.

Uploaded by

siva
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

VIEWS

A view is a database object that is a logical representation of a table. It is


delivered from a table but has no storage of its own and often may be used in the
same manner as a table.

A view takes the output of the query and treats it as a table, therefore a view can
be thought of as a stored query or a virtual table.

TYPES
1 Simple view
2 Complex view

Simple view can be created from one table where as complex view can be created
from multiple tables.

WHY VIEWS?

1 Provides additional level of security by restricting access to a


predetermined set of rows and/or columns of a table.
2 Hide the data complexity.
3 Simplify commands for the user.

VIEWS WITHOUT DML


1 Read only view
2 View with group by
3 View with aggregate functions
4 View with rownum
5 Partition view
6 View with distinct

Ex:
SQL> Create view dept_v as select *from dept with read only;
SQL> Create view dept_v as select deptno, sum(sal) t_sal from emp group by
deptno;
SQL> Create view stud as select rownum no, name, marks from student;
SQL> Create view student as select *from student1 union select *from
student2;
SQL> Create view stud as select distinct no,name from student;

VIEWS WITH DML

1 View with not null column -- insert with out not null column not possible
-- update not null column to null is not possible
-- delete possible
2 View with out not null column which was in base table -- insert not possible
-- update, delete possible
3 View with expression -- insert , update not possible
-- delete possible
4 View with functions (except aggregate) -- insert, update not possible
-- delete possible
5 View was created but the underlying table was dropped then we will get
the message like “ view has errors ”.
6 View was created but the base table has been altered but still the view was
with the initial definition, we have to replace the view to affect the
changes.
7 Complex view (view with more than one table) -- insert not possible
-- update, delete possible (not
always)

CREATING VIEW WITHOUT HAVING THE BASE TABLE

SQL> Create force view stud as select *From student;


-- Once the base table was created then the view is validated.

VIEW WITH CHECK OPTION CONSTRAINT

SQL> Create view stud as select *from student where marks = 500 with check
option
constraint Ck;
- Insert possible with marks value as 500
- Update possible excluding marks column
- Delete possible
DROPPING VIEWS

SQL> drop view dept_v;

Common questions

Powered by AI

A view might become invalid if the base table is altered because the structure or data the view relies upon may change. If the base table changes significantly, the view must be replaced or redefined to align with the updated structure to function correctly .

Views are used in database systems primarily to provide an additional level of security by restricting access to a predetermined set of rows and/or columns of a table, to hide the complexity of data from users, and to simplify commands for users .

Views improve data security by allowing database administrators to restrict user access to specific rows and columns of a table. Since views are a logical representation without physical storage, they can be selectively presented to users, thus controlling what data can be seen and manipulated .

A simple view is created from a single table, while a complex view is derived from multiple tables. This distinction impacts their functionality, particularly with regards to supporting different types of DML operations .

Views will not allow DML operations in scenarios such as having views without a NOT NULL column from the base table, views with aggregate functions, views with GROUP BY clauses, or views that involve multiple base tables without clear join pathways (complex views).

A view that is created with a force option before its base table exists remains invalid until the base table is created. Once the base table is created, the view can then be validated and used .

Using aggregate functions in a view restricts its DML capabilities by making insert and update operations impossible, as aggregates summarize data in ways that do not correspond directly to individual rows, thereby preventing modifications to the original dataset through the view .

The 'WITH CHECK OPTION' constraint ensures that all insert and update operations on the view meet the conditions specified in the view definition. For example, in a view created with the condition that marks must equal 500, any inserted or updated records must satisfy this condition to be allowed .

When a base table of a view is dropped, the view becomes invalid and typically results in an error indicating 'view has errors.' To address this, the view needs to be recreated based on a correct base table if the structure and data requirements are to be met again .

When a view includes an expression, it may face challenges such as the inability to perform inserts or updates because expressions often derive values from calculations or transformations that are not straightforwardly reversible or directly editable .

You might also like