Wednesday, 9 May 2012

Data Integrity Using Views

Data Integrity is implemented in the databases using various Constraints and Triggers. Whereas Integrity Constraints are a non-procedural, declarative way of implementing data validation rules (refer Codd's rule #10), the triggers offer the procedural method to implement data validation for such rules where the constraints show certain limitations. However there is a third method for ensuring the validity of the data, that is through creating "views". Our discussion will be with reference to the Oracle as model database.

What are Views? -

Views are created over tables to provide an abstraction layer, to mask the actual source of data and provide a limited access or visibility to the data contained in the tables. Views provide a means to simplification by allowing to store a complex query as a view in the database (hence an abstraction layer) apart from implementing a security feature (by providing limited access and / or visibility).
View is technically a Query (select statement) stored in the database in parsed and compiled form with a name, which can be used as virtual source of data. The views are referenced in the SQL statements in place of tables; and at such reference the select statement associated with the view implicitly executes on the underlying tables to present the data to the operation or for viewing. As such, the views DO NOT store any data, but they exist only in the form of a definition.

The syntax for creating the plain view (without constraint implementation) is simple -

create [or replace] view <ViewName> as <Select Statement>;

The "or replace" clause is optional (indicated by presence of [], a universal standard to indicate optional part of the syntax), when used, it will replace an existing definition of the view with the same name. The "ViewName" may be any suitable name of user's choice, but of course, must follow all the naming conventions imposed by the DBMS. The "Select Statement" may be any select statement, inclusive of where, having, group by, order by, distinct, sub-queries, joins or relational algebra expressions like Union, Intersect or Minus.

Data Integrity Implementation Example -

Consider a company who has managers, each in-charge of multiple departments. So there is a manager M1 in-charge of departments (10, 20, 30), manager M2 in-charge of departments (40, 50, 60) .. and so on. The managers may only make appointments and transfers of employees for the departments under his/her control. How do you think you could implement this rule using Integrity constraints or triggers? There comes the convenience of using views for such requirements by using a clause "with check option". The mechanism of this clause comes into play in restricting the data manipulation only within the range of "where clause" of the view i.e. any such data manipulation on the view would NOT be allowed which would put the row(s) beyond the selection of the view. For example, to implement the above rule we create two views -

create or replace view empm1 as select empid, ename, job, sal, deptno from employee
where deptno in (10, 20, 30) with check option;

create or replace view empm2 as select empid, ename, job, sal, deptno from employee
where deptno in (40, 50, 60) with check option;

Some more such views may be created for other managers for the set of departments under their control. The respective managers will be granted access only to the view created for their set of departments and none other and the table. So the view "empm1" and "empm2" will be respectively available to managers "m1" and "m2". 

The following statements will be valid for manager m1 -

insert into empm1 values (101, 'peter', 'clerk', 900, 20);
update empm1 set deptno = 10 where empid = 101;

Since in these - the insert statement is inserting the employee's rows in department number 10, and the update statement is transferring an employee from department 10 to 20, so well within the scope of the view.

However, the following statements if fired by manager m1 will result into an error -

insert into empm1 values (102, 'robert', 'analyst', 2500, 60);
update empm1 set deptno = 60 where empid = 101;

since in these - the insert statement is trying to insert the employee's row in department 60, not within the scope of the view allocated to manager m1. Similarly, the update statement will fail for the same reason as after the update the row will tend to move outside the scope of the view "empm1".

This was not a complete tutorial on views, but we were only restricted to see the use of views to implement such data validation, restriction or business rules which may not be implemented through integrity constraints or triggers. Actually Oracle also allows to implement primary key, unique, rely, read only constraints through the views.

If you have liked this, Click Here for "concurrency & locking" (a one of the most liked topic by other readers).

No comments:

Post a Comment