 Dear students, I hope all of you are fine, today we are in the lecture number 40 of the database management system course, the course code is CS403. In our previous lecture, we concluded our discussion on indexes and then we entered into a very important topic of the databases and that is views. In the previous lecture I tried to explain to you why are views important, why are they useful, why do we use views, briefly views are mainly used to provide a secure and efficient access to the data in the databases. By views we restrict or you can say we facilitate each user group to the data of his or her concern. In this way it helps us to manage the data properly because each user group will be accessing the data from the database through his or her view and by this way the user group or the user will have no concern with any of the data stored in the database. The whole concern of the user group or the people in the user group will be the data that is being provided to them through the view. At the end of the previous lecture, we studied the syntax of the statement to create view. Today we are going to see the examples of the creation of views. So let us start today's lecture. In the previous lecture, we have seen the general statement or the syntax or the format of the create view statement. We are going to start today's lecture by an example of the view and we also see some other examples as well. Well, here is the first statement as you can see we have given a create view and next is the name of the view. You can guess about the naming convention I have adopted here. You can follow this convention or you may devise some of your own or you may follow any convention but anyway what I have adopted here is that first two letter st they are showing the name of the table on which the view is being defined and then the view so that it is clear from the name that this is a view on student. And then I have placed one so that if I have to create multiple views on student then I can place 1, 2, 3, 4 and like that. Another possibility is that you can give some hint of the attributes being used in the view or the particular user group for which the views will be created. Anything any naming convention that is meaningful and convenient to you can adopt that. So, the convention that we have adopted in this course is that first is the name of the table the sort of abbreviation of the table name and then with underscore view and the number. So, this is the naming convention then is as that is required to be there and if you remember that in the syntax we studied that after the name of the view we can give the names of the attributes in the views as I told you that the names that you want to use in the view can be different from the names of the attributes in the table. This is also a sort of help because for in the table you may use the names that are more meaningful or convenient to you and when you are defining the views you can use the names that are more meaningful and convenient to the user who will be using it. If you yourself are using the views for creating let us say the user interfaces and obviously you are the user but if you feel that the view is going to be used by some end user as well then you can name you can use the meaningful name for them as for example see you may have your own naming convention like using abbreviation like that but when you are defining name for someone else maybe it is better that you use a full name rather than using the abbreviation because if you are using some abbreviation and that are not standard maybe the other people who are seeing at that they may be confused. So, well this is the option that you can give the names of the attributes that will be used in the view but they are optional. So, if you do not give the name of the attributes as we are not giving them here then the names of the attributes in the tables they will be used as the names of the attributes in the views okay after the as we have to give the select statement and here you can see that we have given the select st name comma st father name or program name. So, we have given these three attributes from student we have given the student name as well where program name is equal to MCS. Now obviously this is optional if you want to give the condition this is optional if you do not give the condition it means all the rows will be included but if you have given the condition as you have given here and that gives only those rows will be included in the view that fulfill this condition. So, here we have created a view named st view and the definition of the view is okay select student name student father name program name from student where program name is equal to MCS. So, this is your definition of the view okay view can be referred in SQL statements like tables. So, this is the view that you have given in the condition that these three attributes from the student table and those whose program name is MCS are in this. So, you can see that all the records in which MCS is its value program name are in front of you. So, you can see that in the form you used to use the table with the form clause but here you are the view name and it acts exactly the same as it acts in the case of the table name. Another option is with check option. With check option is that when you give a statement that the condition is related to the check option. This means that you have views. You can perform different operations on the views as well. So, as you delete or update your data in your tables, you can do the same thing through views. But there is a certain condition that we will discuss in detail in the future. Anyway, first you have defined a view, you have defined a view in which you said that you have those students whose program name is MCS. You want them to participate in that view. Now, one thing is that because the DBMSS, they allow the update operation through views. You can update your data through views. Now, in this updation, it is also possible that you can update that attribute and change its value in which your view is defined. For example, our last view that was defined on the basis of the program name is equal to MCS. Now, certain rows qualify this condition and they are included in this view. Now, whenever you access data from a table, those rows that fulfill this condition will be selected. If you add more rows in the table, now we are talking about the rows in the table, we will talk about the views later. So, let's say you add other rows as well with the program name MCS. So, after choosing it, when you access the data from the view again, you will see those rows as well. But at this time, we are talking about the check option. It is related to when you are updating the data through view. Operation is allowed with a certain condition. But that updation can also be done on the basis of which, as a view defined example, you change the name of a row in this view. If you change the name of the program in this view, what will happen is that your view is defined that your program is equal to MCS. First of all, you change the name of the program to BCS. First of all, you change the name of the program to BCS. Now, what happened is that the next time you execute this view, you will access the data from here. So, that row, that was a part of this view because the program name was MCS. Now, that row will not be included in the view. Why? Because you have modified the program name from MCS to BCS and now this row has gone out of the domain or range of the view. So, now what will happen is that the next time you will select this view, the row in which you have changed the name of the program, it will not be included in the view. Now, the with check option is that it makes sure that you do not make any change in the data through the view that excludes the membership of a row in the view. You cannot change the name of the program from MCS because if you do something else from MCS, it will happen that the row in which you have changed the name of the program will not be included in the view. If you change the name of the program, that would be allowed. But not the condition, not the value of that attribute that provides the basis for the definition of the view. If you change the name of a student through this view, then that will happen. But if you say the with check option, and after that, if you try to change the program name, then that change will not happen. Whereas, if you do not give the with check option, then in that case, if you change the name of a student whose program name was MCS and our view is defined by the same students who are MCS students, then if you do not give the with check option, then if you change the name of a student's program name from MCS, then it will be allowed. But as a result of that, that row is not the part of this view. So, this is the with check option. This is a good option. So, I have explained it to you. Now, I will show you an example of it. Here, you see, we have defined a view. We have defined another view on the same student table. And the name of this is ST and view2, because view1 was before us, which was MCS students. Here, we are using the same attributes, the same student name, father name, and program name. And this is not necessary that you use the same attributes. It is up to you which attributes are required. The condition given here is that program name is equal to BCS, fine. Then we said with check option. We gave this in the end. This optional thing we did not use previously, because in our ST view1, we did not use it. Now, what we did is when we said select star from ST view, you see here, we have got four records here because we had four people who have a student table. We have got four records in which the program name is MCS, fine. Now, what did you do? You did update ST view1. Now, you are not updating the table. You are updating the data of the table through the view. Now, you see this beauty that if you give this view to the user group, they are using this view just like a normal table. And they are understanding that as we are working on a table, but in reality, this is a view. And it is possible that another view of this table is with another user and they are also dealing with it as they are using a table. But underline is that multiple user groups are working on their own views. It is different that if you see below, the table is getting smaller. Anyway, here we said that update student view1, said program name is equal to BCS where student father name is equal to loving. So, in English we can say loving. So, now you have given a condition that and what you have updated the data in, in your view1, which was defined by the program name MCS. After that, we said select star from ST view1 because you will remember that we had 4 records in ST view1. Now, we have updated one. Now, when we take data from there, now we have got 3 records. Why? Because we have updated a record through the view that have cancelled the membership of one of the row from this particular view. Now, you see, in the table, you can see that it is not visible in this view. Because now the program name of that student, which we have changed, it is not MCS program. That is why it is not in this view. Now, if you see, select star from ST view2 which was defined on the basis of BCS, then you can see 2 records. And if you see one of them, the first record of Suhail Dar, in that, the father name is loving. This record was first in view1 because the program name was MCS. But now it has come in the second view, which was on the basis of BCS. Now, the interesting thing here is that what we defined in student view2, we defined that with check option. This means that when you have given student view2 once, you should not forget it again. Because the view2 is very determined to hold the program name. First, you feel that it was wrong to change the program name. Now, you try to change the name of the program from BCS back to MCS. Let's see what happens. You said update student view2 and you said set program name is equal to MCS where student father name is equal to loving. If you want to change the program name from BCS to MCS, now see, because our view is defined on the basis of program name. If you are changing the program name value, basically we are cancelling the membership of that row. So, be in the error. You will not be allowed to change this data. You must have realized that when I tell you that you can restrict users from changing the program name, then you can restrict the user from changing the program name. This is one way to restrict the user. The user says that you can change the program name from BCS to MCS. Now, if the program name needs to be changed, then yes you can do. There will be no problem directly with the table. Now, see how you have implemented your security. You give access to the user only to these views. And keep the access to the tables to yourself. When you want extra care while making changes in these data, then the access to changing the program name should be given to a senior or more responsible person. Otherwise, the rest of the user groups should be given permission. So, if you want to change the program name, then you can do through the table name. But the access to the table is not given to the user. So, the things that can be changed are the things that you want. The attribute names. As I told you, the attributes that you use in the views can be different. And as I told you, try to make them more useful or more meaningful for the users. For example, as we have created another view on the student, the student says that the view is on the student and the view is on the 3rd. So, it was directly given as clause and then it was written in the statement. What we have done here is that we have given the name of the views in the brackets. For example, what we have given is name. And the table name you had was student father name. And what was the abbreviation? ST for student, F for father and name. If you don't know the user, then for what ease did you name this view as father? Now, when you say father, he will understand that this is his name and this is his father's name. And after that, you have written a student address in the table. I have told him to know that. This is more meaningful. The second thing is that there is no direct access to the attribute names of the tables, underlying tables. In this way, you can keep different names as we have done here. Now, if you first give the statement that select star from student view 3. Now, what you are getting is the same records that are present in the table, in the student table. But see the names or the labels of the attributes. So, here name, father and know. Now, you have made this word meaningful. You can make them different from the actual names. Computed attributes. Another beauty of views is that you can show the user data as per his requirements. When you actually store as per your requirements or as per the efficiency requirement. I told you that efficiency is a high priority and it should be a preference. So, it is possible that in the format in which you feel that this is efficient. And in the format in which you want the user, you feel that it is not efficient. So, this gap can be covered by the views. So, you can store it in your format as per your requirements. But show it and you can use this gap to add something to the computed attributes in the view. So, the user will feel that this data is as such stored. So, you can use the underlying tables to share computed attributes in the views. Let's see how it can be. You can also perform the nesting of views. Nesting of views is that you can create a view on top of another view. This is called nesting of views. The views we have created on the base of tables is that you have got a view, you have defined a view and then create another view on it. So, this is called nesting of views. So, what will happen is that you defined the first view and let's say on the table by applying some condition on the table. Fine. Now, you define another view on this view by applying another condition on this view. Now, if you look at the table, the first view condition will be applied and also of the second view. So, both the conditions will be applied in the second view. Similarly, if you create another view on the second view, on the second view, the third view, then this view and the first view will be applied on the table. And like that. This is called nesting of views. I think you can nesting up to 32 levels on view, view, view and like that. Now, we have created a view here. An enrolment view. That means we created a view on our enrolment table. It is called enrolment view as select star from enrol. We have included all the attributes in it. Now, look at this. You have said here that create view. Now, we have said enrolment view. The second view on enrolment is select student ID, course code, session marks, midterm, session marks plus midterm. Now, look here, we have included a computed attribute. What we want is that the students we have are allocated to different subjects. There are some marks that the student is assigned during the semester. And there are some that at the end of semester when they give final exam, there are marks. We want to see that a student during the semester marks is for that, see, the thing is we have only stored the session marks and the quizzes marks. And the midterm marks are the numbers of the students. We have stored them. But the sum of the two countries is not there in the table. Now, we have defined the view. But in the user group, we want the number of the session marks and their sum. So, you create a view. For example, you have added student ID, course code, session marks, quizzes, midterm marks, and you have also added their sum. And to give it a name, you have used session. Now, look here, there can be two ways to name the attribute. For example, you have explicitly given the names of attributes of the view, after the view name in the bracket. As you have given the expression, that session marks plus midterm marks, you have given the session mark. That works as basically an alias. But it will also be named. And mind it, your computed attribute, which you will call as a derived attribute, you have to give a name in that case. This time, after the form, you haven't given the name of the table. Rather, you are giving the name of the view. Now, what will happen? In this case, you have created a view a little earlier on enrollment. You have defined another view on that view. Now, if you access the data from the second view, then you will get the data in front of you. You have said, look here, student ID is here, course code is here, its session marks, midterm marks, and in the end, you see an attribute, session. And in this, you can see that it is in front of both of them. See the beauty of the idea. Now, the user who has this view, he will understand that the table I have, because user will consider it as a table. He will understand that the student ID is in my table, course code is in the session marks, midterm marks, and the session store is in it. Whereas, if you look practically, the session marks are not a store. But the user will get this view. The user's understanding will give the user this view that this is a store in all the users. So, in this way, as I am telling you, if you manage your data, then you can do it efficiently. As I told you, in most of the applications, you do not only need one table, but you need multiple tables. And again, view is of great help in this situation. Instead of writing that statement for them, or you tell them that statement, you create a view for them. And then, the beautiful thing is that the user will understand that all the data stored in one table is accessible to us. See, practically, what is happening is that it is in multiple tables. Now, both the words are satisfied. The user is satisfied because their requirement is being fulfilled. And they are getting all the required data within the same table, which is basically a view. And you, as a designer, you are successful because you have managed your data efficiently in multiple tables. Let's see how we access data from multiple tables. View can include data from multiple tables through product or any form of join, generally join. And join, as I told you, the common attribute of the same value that you join every day. Now, let's see that we have created a view which we called create view. And because we are using two tables in this view, that is why we are using st underscore pr student program view as select st name comma st father name in f name And then see, student dot pr name This is called resolving a name, or resolving ambiguity. Why? Because the pr name program name, this attribute is defined as such, I mean with the same name in both the tables, in student as well as in program. So, in this case if you don't give the name of the table before the attribute, dbms may get confused that the program name I have to access from which table to stay? from student or program name. In such a situation, you must include the name of the table as well with the attribute name. And this is the way you give the table name dot the attribute name. After that comma pr credits in student name, in student father name, in program credits there is no confusion. Why? Because these attributes are included only in one table. So, dbms has such a smartness that it can guess, it can know from the attribute name or check the definition of the tables that which table is this attribute. So, with that attribute you do not need to give the name of the table. But obviously in the program name, there is a requirement. From now you are giving two table names program, student or student comma program where student dot pr name program name is equal to program dot program name. You are creating a join on the same value so you are transforming that into a view. Select star from student program view. Now here is the data. If you have given the join statement then you would have to get the data. But the beauty is that now you have combined the data from both tables and you have created a view. Now those user groups who are accessing this view student program view, they feel that we have all these things in one table. Now another example of multiple tables is that when we are accessing data from three different tables. As I told you in the beginning, when you are accessing the data from three different tables for efficiency, you have split the data to multiple tables and also you have established a link between them or the link established at different tables that is based on keys. So keys establishes the link between tables. But the thing is that most of the time users are not interested in keys. They are interested in values like name or titles or address like that. Now these things are lying in different tables. And the link between them is through keys. So in this situation, if you remember our exam system if you remember that we had three things we also had a student, a course and an enrolment. Now obviously what is happening is that the students we have a student table that is storing the data about to students the course we have a table that stores data about different courses. And the enrolment is that it stores which students enrolled in the course and in addition to that which is the performance of a student in a course i.e. its sectional marks, its midterm marks, its final marks its total marks, its gpa etc. all of those are stored in your enrolment table. Now curiously you know that the names of the students the courses that they enrolled in and in these courses you need students performance. Now in the enrolment you have student id, course code and all their performance. But the thing is that the user is not interested in course. The user wants name. This means that you have to base the enrolments and you have to link them with the student and you have to link them with the course. So by doing this enrolment you have to take the name of the student and you have to take the name of the course. And after that the remaining data the sectional marks, midterm marks you will take from the enrolment. And then there is the same thing because a user wants so rather than asking the user to remember this statement because three tables are involved or instead of that you do not want to give the access to the tables practically you do that you define a view. Now this view is gathering data from three different tables but when the view is formed then you are giving the impression to the users that as if all the data is stored in the single same table so let us see how this idea is implemented. Here you are basically seeing the create view statement and again because three tables are involved then you are seeing the name of the attributes and the attributes I have given for a while we have given recommendations for the proper attribute names we are ignoring them so we have given seven attributes names after that we said as select student name comma course name now student name is coming from the student table course name is coming from course table comma, midterm, sectional marks final marks and then we have added that 12 times midterm plus sectional marks comma total marks now we have given names of three tables student, course and role or where student dot student id is equal to enroll dot student id see now on this basis you will get the student name because it is enrolled center so you have taken student id from enroll you have matched it with student table's student id and the record you have matched with student name on the other side you said and course dot course code is equal to enrolled dot course code so the same record you have matched with course code so when you have matched you have picked the course name from there fine as I have told you they must have the name so in the first example we named the computer attribute that was through alias this time we did not give alias but since we have named it from a1 to a7 so the a6 that name will be assigned to this particular attribute midterm plus sectional marks so this is our defined student course and role enrollment view now we said that select star from student course enrollment view 1 now see here you have got the data see it is beautiful how you are giving so much ease to the user underline how big is the query it is running and that query is fetching data from 3 different tables but user is free all that hassle user is getting a beautiful thing that one is getting table user is considering it as a table at that time table all these things are in proper format and here only one thing is proper attribute name for example a1, a2 search may be you can guess but a3, a4 etc this is strongly recommended do not use names like this today if you are sure that you know what are the meaning of these symbols or you want to be confused if you are not confused today after some time you will forget you will get confused and then you will have to consider the definition so it is better to keep their name so I have given you a bad example so you will realize that I am not doing this so here you have defined view fetching data from 3 different tables placing all the things in the required format and when you give this view to the user then it is easy for the user that all the required things are in the required format but the standard line you yourself are handling it as a designer and as a developer updating data this means that updating data through the views as I have told you before that this is allowed but with certain conditions come and see what are the conditions if single table updation is the piece of cake but take care of with check option as I have given you an example when you have a check option you cannot updation based on which a row a membership of a view cancels otherwise if you have only one view defined on the table then you can update the table in the same way you can update data through view we can even insert data but you have to take care of first thing is not through computed attributes obviously if you have an attribute it is computed that is against 2 or 3 attributes or any other variable so if you want to insert data by inserting data the other attributes if you give value through view then the corresponding attribute will go in the same way but computed attribute can be underline 1 or more attributes or if there is one attribute also as an example as you have converted data worth in age when you have multiplied some value because its underline attribute is not name but an expression expression cannot guide you properly if you are giving a computed attribute then where do you have to place it because you cannot enter your computed attribute through view but otherwise you can enter those attributes through the view after that cannot miss not null attributes look when you want to insert a the first thing is that you can refer only those attributes through view refer means you can give value of those attributes by inserting which are included in the view for example as we are defining a view if in any view there is not a table's primary now when you have a view the first thing is the one who has access to the view does not know what is the primary or what is the primary and even if you know if you are retentering through view then you can access only those attributes that are the part of the view definition that means when you are retentering through view then you have to consider that the attributes not null defined in the table they need value because ultimately the row is going to be entered into the table you are entering in the view but you have to enter the data in the table now you have to enter the primary and if your view is not included in the definition do you have three attributes in the view if you give the value of those three attributes and you say insert where is the value of the primary key and if you do not specify the value of some attribute it is assigned a null value and you know that the primary key cannot have null value in this situation when the primary key is not part of the view you cannot retent it not only that first of all in the table there is another attribute that is not included in the view definition so again the same problem will come that if you are retenting the primary key but there is another attribute that is not included you cannot give that attribute because it is not included in the view again that would be a problem so this means that through view you can enter the data yes but you cannot skip any not null attribute the primary key is definitely included there is no other attribute that is not null you cannot ignore that that is one of the multiple tables this means that if your view has been defined on multiple tables like we have just defined on two tables and three tables in that case you can enter the data but during one statement or through one insert statement only in one table there is no similarity so let us do the FRS if you have a view where there are two tables in one table in two tables not in case of aggregate functions if your view contains some aggregate function there is an aggregate function so if you know that aggregate function it provides it performs some aggregate function it provides some function on a collection of rows on the attributes of a number of rows now there is an attribute averagecgpa averagecgpa is an aggregate function and it is included in the view the view is being seen as a single attribute the underline is an aggregate function now if you try to enter a value in this attribute there is an average function which can be applied on 10, 15, 20, 100, 100, 100, 100 rows and you get one value obviously you cannot enter the value you cannot insert the record in case of the aggregate function as well here I am showing you some examples in which way you can enter data through the view now this definition which we have seen before is created view select in this I have included the STID because STID is the primary key and without including the primary key into the view you cannot enter data through the view so this time STID is included name, father name, program name from students where we have given this and then we have seen that insert into ST view 1 this time we are entering through the view values because we have given all attributes so we have given straight away values then we have given the student ID S1042 Ahmed Ali Ali Hussein father name and BCS program name so we have given the attribute value now look at this if you look at the last record then you can see the attribute which you are seeing last video 9th record is S1040 this is what we have given then we have said that Ahmed Ali student name Ali Hussein father name now address because we did not include in the view we cannot enter the value even if we like because that is not the part of the view so there is null similarly because student phone is also not the part of the view that is why it is also null program name that is BCS we entered now one thing you might think that why we have a value 1 in the current semester see as per rule because current semester is not the part of the view definition so that we cannot give the value so from where it came default value you will remember that when we defined the student table then we declared the default value of the current semester as 1 and because the data that we are entering through the view is going ultimately into the table that is why we specified 4 values through the view but when they reach to the table these 4 values there at their place but the current semester it automatically got the 1 because 1 was defined as the default value for the current semester that is why we are saying 1 there now in this what I am going to show you is that we defined this view again and we defined it with the Altar view you can modify the view through the Altar view and then you can define it again and you can change it for example the previous view we defined but this time with check option fine what we defined was that program name is equal to MCS this is the part of the definition and also said with check option now the same record another record we are going to enter what is happening this time we gave a new house ID we gave a student name but the program name was BCS mind it what was the condition MCS with check option that is what is the program name now what will happen it is not being taught but the red line that red line always means danger this means there is error that the DVMS did not allow you to enter this early because you have placed with check option when you have done with check option you will change anything you will insert it you will delete it because of which any row membership is affected or it is not being formed then you will not be allowed Dear students our interesting discussion is going on views we have seen our discussion is going on dynamic views which we call virtual tables we have seen how we can define views on single tables we can define views on multiple tables we can even update the data through the views and even we can insert the new records through the view as well so I hope the benefit of views you must be understanding when you will practice create views through astral and do different operations then this concept will be clear apart from this the online help of SQL Server there is a lot of information about views read from there as much as you practice it will be clear practice makes even A students perfect thank you, Allah Hafiz