 Hello everyone, I am Mrs. Sunita Doho the topic covered is views. The logical model level means we have assumed that the relations in the collections we are given are the actual relations stored in the database. Any relation that is not a part of the logical model but is made visible to a user as a virtual relation is called a view. In this video we are going to see how to create the view for given relation learning outcome at the end of the session students will be able to define the view for the given relations. In some cases it is not desirable for all users to see the entire logical model in which all actual relations are stored in the database. Any consideration may require that certain data be hidden from the user. Consider the relation loan with the attribute loan number branch name and amount and the borrower relation with the customer name and the loan number. Consider a person who needs to know the customer name loan number and the branch name but has no need to see the loan amount. This person should see a relation described in the SQL by query that is select customer name comma borrower dot loan number comma branch name from borrower comma loan where borrower dot loan number is equal to loan dot loan number. Aside from the security concern we may wish to create a personalized collection of relation that is better matched to a certain user's institution than is the logical model. So a view provides a mechanism to hide certain data from the view of certain users. SQL allows a virtual relation to be defined by a query and the relation conceptually contains the result of the query. The virtual relation is not pre-computed and stored but instead is computed by executing the query whenever the virtual relation is used. Any relation that is not of logical model but is made visible to a user as a virtual relation is called view. It is possible to support a large number of views on the top of any given set of actual relations. Consider the view definition to define a view we must give the view name and must state the query that computes the view. The form of createView command is createViewV as query expression where query expression is any legal SQL expression. The view name is represented by V. View definition is not the same as creating a new relation by evaluating the query expression rather a view definition causes the saving of an expression. The expression is substituted into the queries using the views. Consider the view consisting of branch name and the name of customer who have either an account or a loan at that branch. So, we need the following four relations the account relation with the attribute account number branch name and balance the depositor relation with the attribute customer name and account number the loan relation with the loan number branch name and amount and the borrower relation with customer name and loan number. So, consider the four relation for writing the view definition assume that we want this view to be called all customer. We define this view as createViewAllCustomer 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 equals to loan dot loan number. The result of this view is the relation all customer with the attribute branch name and customer name. The view relation conceptually contains the tuples in the query result but is not pre-computed and stored instead the database systems store the query expression associated with the view relation and whenever the view relation is accessed its tuple are created by computing the query result. Thus the view relation is created whenever needed on demand. Once we have defined a view we can use the view name to refer to the virtual relation that the view generates. Consider query on the view definition considered on the previous slide. Using the viewAllCustomer we can find all the customer of the period branch. The result of viewAllCustomer is given on the slide query for all the brand all customer of period branches is select customer name from all customer where branch name is equal to period age. Result of this query contains two tuples that is Adams and Hayes. The view names may appear in a query any place where a relation name may appear. The attribute name of a view can be specified explicitly. Here the view definition that gives for each branch the sum of amount of all loans at that branch. For this view the relation loan is required. View definition is createViewBranchTotalLoan in parenthesis branch name comma total loan as select branch name comma sum of amount from loan grouped by branch name. Since the expression sum of amount does not have a name the attribute name is specified explicitly in the view definition. The resulting relation branch total loan is given on the slide. Intuitively at any given time the set of tuples in the view relation is the result of evaluation of the query expression that defines the view. Thus if a view relation is computed and stored it may become out of date if the relation used to define it are modified. When we define a view the database systems store the definition of the view itself rather than the result of the evaluation of the query expression that defines the view. Whenever a view relation appears in the query it is replaced by the stored query expression. Thus whenever we evaluate the query the view relation is recomputed. Next point is materialized views. Certain database system allows view relation to be stored but they make sure that if the actual relation used in the view definition change the view is kept up to date. Such views are called materialized views. The process of keeping materialized view up to date is called materialized view maintenance. View maintenance can be done immediately when any of the relation on which the view is defined is updated. Some database system perform view maintenance lazily when the view is accessed. Some system update materialized views only periodically. Some database system permit the database administrator to control which of the above method is used for the materialized views. Application that views a view frequently may benefit if a view is materialized. Application that demand the fast response to the certain queries that compute the aggregate over large relation can also benefit greatly by creating materialized views corresponding to the queries. In this case the aggregate result is likely to be much smaller than the large relation on which the view is defined. As a result the materialized views can be used to answer the query very quickly avoiding reading the large underlying relation. SQL does not define a standard way of specifying that a view is materialized but many database system provide their own SQL extension for this task. Some database system always keep the materialized views up to date when the underlying relations change while other permit them to become out of date and periodically recompute them. So till now we consider the view definition. 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 which gives for each branch the average of amount of all the balance at the branch. I hope all of you have completed this activity so the question was create a view which gives for each branch the average of amount of all the balance at the branch. View definition is create view branch average account in parenthesis branch name comma total loan as select branch name comma sum of amount from loan grouped by branch name. Result contain two attribute branch name and the total loan as shown on the slide. This is the reference used for preparing this presentation. Thank you.