Views in DBMS

Views in DBMS

What is a view? Is it a some kind of a scenario??  What would you guess out of that??

Simply a view is a virtual table.It can be also described as a stored SELECT query which is used to represent  data in a physical database table. 

There are several  advantages of views:

•    For Joining data so that users can work with it easily. In order to maintain somekind of a huge database we can use normalization, separating  the whole database into several tables. Though it looks very easy from programmer’s point of view considering maintainability reason , it is not always easy to work with. When retrieving all the data you need in a result set,you may have to query more than one table at a time. At such scenario using view you can join tables and provide programmer with an easy interface to query.

•  For partitioning data so that users can work with it easily. Considering on practical databases on real world you find large number of records , but for a particular purpose there would be unwanted data. Thus partitioning  data would leads to performance enhancement extracting required amount of data. Therefore using a view the programmer can be provided with a selected subset of data from the table.

• For aggregating data so that users can work with it easily. For example we can take a situation of top management is asking details of the overall sales of the first quarter of the year. In such situation database users require aggregate result on the that sales table. So by creating a view containing the aggregate value the programmers will be eased.

•  Customize data to user’s needs .We can customize the columns  in the data set using views. For instance in physical database would contain two columns as first_name and  last_name, but if the both columns came together real meaning would pop out rather than using two. Using view we can define a single column consist of  first_name and  last_name .

• Hide underlying column names from users.  Developers often use column name that are not particularly intuitive for the users.In such cases, you can build a view that aliases column names.Therefore users will not see the underlying column names.

•   Easily secure data. You can grant access rights to views without granting rights to underlying tables. For an example, in an Employee table there will be non sensitive information like first_name, last_name, id and sensitive information like salary, age, account_number etc.Whenever there are sensitive information that you don’t need the users to see, you can create a view containing only the non sensitive data and grant access to the users.

Drawbacks of the view

        Even though views can be a great tool for securing and customizing data, they can be slow. When querying we have to go through an additional step, because it doesn’t directly referring to the physical database table. Indeed, they are not any faster than the query that defines them.

       Views can especially degrade the performance if they are based on other views. Therefore, it is recommended NOT to create views based on other views. All views should be created against base tables.