Question

Subject: Database Design System & Management

Do not copy from internet/ web resouces or textbook. Write in own words. Minimum 200 words

Q: What are Views in database? Why use a View? Advantages and Disadvantages of Views? Provide an application example. (Describe an application scenario that you will create virtual views).

Answer 1

Answer)

Views are the object in the database. View are based on tables so they are called as window of a table or virtual table. When we create a view we have to run a select statement which is based on a table stored on view. We can use multiple tables with join to create a view.The table on which the view is created is called the base table.

Views are created to hide the data of the tables, or we can create a read only view so if the user access the view they cant update from the view. Also views are used for complex calculation based on a table and the user of the view is unaware about the fields in the table.

The advantages of views are

1) Views are objects in a database so they get a separate memory space for execution
2) Views can be created read only, so the underlying table remain unchanged when the views are executed.
3) We can store a complex select query in the view, and only the view is granted to the user, so user is unaware the fields involved in the select query.
4) Views are also created with the column alias name, so the users of the views are unaware obout the filed name which provides data security to the table.

Disadvantages of views are

1) In database more memory space is required to execute the views.
2) Execution time will be higher because first the view will execute, then the underlying table or base table will execute.

Example

Suppose there is a table called employee which has field eid,en,hra,da,pf, now on the employee table we create a view as salary which contains eid, en,sal as (hra+da)-pf

create table employee(eid int,en varchar(20),hra dec(10,2),da dec(10,2),pf dec(10,2));

create view salary as select eid,en,(hra+da)-pf as sal from employee;

select * from salary;

grant select on salary to user1;

-----Suppose the user admin has create the table employee and the view salary, and he grant the select on view salary to user1, now user1 cant know what is the view consist of, he is only permitted to know the details of the view, but from where or by which field the view is creates that is unknown to him. This is one of the advantage or usage of view.