 Hello everyone, I am Mrs. Sunita Role. The topic covered is views. In previous video, we consider how to create a view and what is materialized views. In this video, we are going to cover views defined by using other views and update of a view. Learning outcome. At the end of the session, students will be able to create the views defined by other views for the given relations. Consider the views defined by using other views. View relation may appear in any place that a relation name may appear. One view may be used in the expression defining another view. A view relation v1 is said to depend directly on view relation v2 if v2 is used in the expression defining v1. A view relation v1 is said to depend on view relation v2 if either v1 depends directly on v2 or there is a path of dependencies from v1 to v2. A view relation v is said to be recursive if it depends on itself. View expansion is a way to define the meaning of views defined in the in terms of other views. The procedure assumes that the view definition are not recursive thus no view is used in its own definition whether directly or indirectly through other view definition. For example, if view v1 is said in the definition of view v2, view v2 is used in the definition of view v3 and view v3 is used in the definition of view v1 then each of the v1, v2 and v3 is called recursive. Let view v1 be defined by an expression even that may itself contain uses of the view relation. View relation stands for the expression defining the view and therefore a view relation can be replaced by the expression that defines it. View expansion of the expression repeats the following replacement step. Find any view relation vi in e1 replace the view relation vi by the expression defining vi until no more views relations are present in e1. As long as the view definitions are not recursive, this loop will terminate. Thus an expression a containing view relation can be understood as the expression resulting from the view expansion of e which does not contain any view relation. Consider the view consisting of the branch name and the name of the customer who have either an account or a loan at that branch. Create view all customer as select branch name comma customer name from depositor comma account where depositor dot account number is equal to account dot account number union select branch name comma customer name from borrower comma loan where borrower dot loan number is equal to loan dot loan number. The result of this view is the relation all customer with the attribute branch name and the customer name. Consider the view to find all the customers of the perirage branch using the view all customer that is create view perirage customer as select customer name from all customer where branch name is equal to perirage. The result of this view is the relation perirage customer with the attribute customer name of the perirage branch. Consider the following expression select star from the perirage customer where customer name equals to atoms. Input relation to this expression is the perirage customer relation which is obtained using the all customer relation. The resulting table contains the customer name atoms for the expression on the previous slide the flow was view all customer the resulting relation all customer then all the customer of perirage branch using view all customer. The result is the perirage customer relation then the expression select star from the perirage customer where customer name is equal to atoms final resulting relation that contains the customer name atoms. Now the view expansion will be first expression that is select star from the perirage customer where the customer name is equal to atoms. The relation perirage customer in the from clause is replaced by select customer name from all customer where branch name is equal to perirage. The relation all customer in the from clause now is replaced by select branch name, customer name from depositor, account where depositor dot account number equals to account dot account number union select branch name, customer name from borrower, loan where borrower dot loan number equals to loan dot loan number. Till now we consider the view defined by using other views now pause this video and reflect on this question for a minute or two minute and write your response. Once you return the answer to this question then you can restart playing this video. The question is create a view to find all the customer and the branch name who have an account but no loan at the bank from the following relation. Find all the customer of Brenton branch. I hope all of you have completed this activity and the question is given on the slide. Consider first part that is create a view to find all customer and the branch name who have an account but no loan at the bank from the following relation. The query is create view all customer account as select branch name, customer name from depositor account where depositor dot account number is equal to account dot account number except select branch name, customer name from borrower, loan where borrower dot loan number equals to loan dot loan number. The relation all customer account with the attribute branch name and the customer name as a result of this view is shown on the slide. Now consider the second part find all the customer of the Brenton branch. The query is select customer name from all customer account where branch name is equal to Brenton. The result contains the customer name Johnson and Jones. In the view expansion the relation all customer account in the from clause is replaced by select branch name, customer name from depositor, account where depositor dot account number is equal to account dot account number except select branch name, customer name from borrower comma loan where borrower dot loan number equals to loan dot loan number. Consider the following view definition that is create view loan branch as select branch name comma loan number from the loan since SQL allows a view name to appear whenever a relation name is allowed. So we can write insert into loan branch values perinage comma L307. SQL represent this insertion by an insertion into the relation loan since the loan is the actual relation from which the view loan branch is constructed. So we must therefore have some values for the amount. This value is a null value thus a preceding insert result in the insertion of tuple that is L307 comma perinage comma null. Most SQL implementation allows the updates only on simple views. The from clause has only one database relation. The select clause contains only the attribute names of the relation and does not have any expression aggregates or the distinct specification. Any attribute not listed in the select clause can be set to null. The query does not have a group by or the having clause. Here the following view definition which is already explained on the previous slides. A modification is permitted through a view only if the view in the question is defined in terms of one relation of the actual relational database that is of the logical level database. Thus update insert and delete operation would be forbidden on this example view all customizable. This is the reference used for preparing this presentation. Thank you.