 Assalamu alaikum. Dear students, today we are going to discuss lecture number 39 of the database management system course. The course code is CS403. In the previous structure we were discussing about the indexes and as you know indexes are basically used to speed up the access of data from the database because if indexes are not there then whenever we want to access data on the basis of certain condition you know that we apply the condition when we want to access certain rows of a table not all of them but certain rows because if we want to access all the rows then we simply select give a select statement and that gives us all the rows from the table. But in case if we want to select certain rows then how do we distinguish how do we identify those particular rows that we want to access we distinguish them by giving a condition. And when we are accessing the data from the database on the basis of a condition then in the absence of indexes the only option is sequential access. And until now you must have understood that sequential access is very time consuming. It is not very efficient especially if the file size if the table size is large. Because when you have a small table size that is say 100, 200, 300, 1000 records sequential access is not so much that you feel that it is slow but in your real life applications normally you have too many records in the database tables and in thousands in lakhs you can go. So in such situations as a DBA expert or as a database expert these are the decisions these are the technical decisions that you have to make and when you take these decisions then you have a whole background knowledge behind it that why are we doing this what will be the benefit if we are doing this then what will be the overhead. So indexes are helpful to you at that time when your table size is too much there are a lot of records and you want to access the data from a specific condition. So if we do not have an index file then the only option is to search our required records sequentially i.e. we read all the records carefully and then check that the condition we want to access the data whether this particular record fulfills that condition or not. So if that condition fulfills then it is your required record if not then it is not but the thing is you are doing it sequentially so if your required record is somewhere in the beginning it can be in the end but if you create index it will help you to arrange the records based on that value and you will remember that I have read that in index files the records that you have are not their arrangement but they maintain the keys and the pointers are maintained from that key so that you can directly reach that record that you want. Obviously in the case of index some accesses are involved but the access through index is much more efficient as compared to the sequential access. So in the last lecture we read about indexes and we read that what are different approaches to implement the indexes and we read basically three major approaches. The three approaches that we discussed for the implementation of indexes were first of all we discussed the inverted files and you know that we use inverted files especially in the case of secondary key index that when we want to index on secondary key then the inverted file is that your secondary key attribute values are in sorted order and after that the relevant record or relative position in the database table is stored. So whenever you want to access the data on the basis of that key field for example you want to access the data on the basis of let's say the name of the employee. So what will happen is that if you have created an index what will happen is that first for that particular employee the name will be searched in your inverted file and from there the dbms will get the relative record number of the record that contains this particular name as value. So from there it will reach that record when it is required record. So this was the inverted file. After that we discussed the link list or pointer change. You also saw that from which class the secondary key is based on its value the chains are maintained and in the end we also saw that we can maintain the circular link list and also the double link list. So we can do these things in this way and the last approach we discussed was the tree. And we saw that in what way the trees are especially the b plus trees are used to implement the index. And in that the root node or intermediate node they just contain the pointers but the leaf nodes either they contain the actual data or they contain the key value and the pointer towards the record where it is stored. So this was a brief summary that we have discussed about the indexes in the previous one or two lectures. So today we will start our lecture. Let us go ahead. We were discussing the index classification. So different we had earlier that what was the basis of the implementation that is also one criterion to discuss the classification of indexes. Another criterion to discuss the classification is that whether the index is clustered or unclustered. We have a criterion that we can identify based on it. Now there is a simple difference that if your records are physically stored in the index order in the table then that index will be called the clustered index. If order of data records is the same as order of index data entries then it is called clustered index. And you might have noticed that because we said that normally our records are stored in the order of the primary key. So normally our clustered index that is created on the basis of primary key. You can change it, you can override it but this is what normally happens. And for this generally you do not need to give an explicit statement that you create clustered index. Whatever you declare for a table while creating the table then automatically the clustered index that is created by the AVMS. Then it says that a file can be clustered by the most one search key. Since we said that the clustered index that is created on the basis of the key that our records are stored in the same order then obviously you would like your data to be stored in the same order generally. Since our data is stored in the same order then our clustered index can be stored in the same order. On one hand we said that we can create a lot of indexes. There is a limit I think in the hundreds limit. You can create 200-300 indexes on one table. So the limit is much more than that but the thing is that there would be just one clustered index. That means on which you are having clustered index and that is generally the primary key. The order of your records would be the same as its key value as its primary key. It would be like we just talked about the leaf node. There are two possibilities in leaf nodes. One possibility is the key value and the record itself. The second possibility is the key value and a pointer towards the record with that value. This means that if we are having the clustered index then if we are implementing through the B plus trees then the leaf nodes would record themselves over there. But if you are having the non-clustered index then there are many possibilities. So what would happen if your leaf node is implemented through the B plus trees then the leaf node would have the key value and then the pointer towards the record with the key value. This is basically the difference between a clustered and non-clustered index. I have also said that you can have just one clustered index but the non-clustered index you can have many of them. Cost of retrieving data records through index varies greatly based on whether index is clustered or not. This is understood that the index will speed up the access from the database. Even among the indexes, your clustered index will have access very fast as compared to the unclustered index. So the moment you are having the B plus tree then when you want to access a record with a key value then when you search through the tree when you reach your required key your required record is also right there. So when you reach the key you do not have to go anywhere else to access your required record. That is why when you reach the key you reach the record. But on the other hand if you are having an unclustered index then what will happen is that an additional step is involved. On one side you said that you will reach your required key. When you reach there, there you will find the address of the pointer of your required record. So from here you will have to access one more record from the record to the record. That means there is additional access. Then there are other factors involved which you will see later. This diagram is showing you the difference with the clustered index and with an unclustered index. As you can see in the diagram when you are accessing through a clustered index what will happen is that when you reach the required key first you want a range of values. As you mentioned, the age between 17 and 20 or the employee ID between E101 and 3055. So the range or particular values you have given even if you have given the range in case of clustered index when you reach the first value now this is confirmed that the next values are the same. They are exactly the same. So if you start reading it sequentially when your leaf nodes are maintained in the pointer then you are accessing all the records. But if you talk about an unclustered index then the thing is that when you reach your required key from there you will find the next record of your pointer. Now although the next key first let's talk about the name. Did you say name? You said name between let's say Majid and Saeed. The names between Majid and Saeed when you reach Majid then you will find the pointer of Majid. Let's say after this you have the record of the name. Now the key of the name will be next to Majid. It will be next to Majid because the nodes in your name they store in the order in the sequence. But what will happen is when you reach the name there you won't find the entire record with the name value name. Again there you will find a pointer towards the record where it is practically stored. This means that you will be able to understand unclustered index although it is much much efficient as compared to the sequential access. But when you compare it between clustered and unclustered definitely your clustered index in that case the access will be very efficient as compared to unclustered index. I hope you have cleared this. Let's move on. Now we have covered most of our topics regarding the indexes. This is the final thought when to use indexes. I mean I have said that the option is there that you can create the indexes on which you want to create an index. But your concern as a database professional, as a DBA is that overall you should gain efficiency. It should not be like using a utility but overall you are generating an inefficient system or your system is working inefficiently. So here are the tips in which situations you should use indexes when they are helpful. The first thing is that it is useful on large tables. As I told you earlier if you have records in few hundreds these days machines and DBMS are so efficient that on few hundreds it won't make much difference if you create an index that may be inefficient because when you are maintaining the index the cost will not justify the efficiency which will give you an index. So what I would suggest unless you have got a few thousand records till then you shouldn't create the indexes explicitly. Normally it is there and as I told you it is created automatically so if you don't say explicitly it is created. It is useful for attributes appearing in where, order by and group by clauses. Because again the three clauses are that the restricts are accessing certain rows. So when you want to then if you have an index then definitely you will reach the required key value and the attribute value that you are searching for. And then if you want a range or a next then you will get the references or the record itself. But if you don't have the index then the only option is that you search your required records sequentially. Like order by, GB range and group by which also combines certain rows then you have to access certain rows. So in all the clauses common thing is that they access certain rows. So in that case if there is an index then the access to rows should be as much as we create the index. Significant variety in values. You have an attribute if in that let's say we say age group we are required to have employees and in that we have age group we are required to have three. We say boy, young and mature. Let's say we have three age groups and young and mature. Now if we have 10,000 records then the value of this attribute and the variety is only three times as much as possible. So if you see in two things that you will get many records with one value. If you distribute equally then at least 3,000 records will be of the same value. If you are able to search through or if there is a spread then it is not so useful. Unless we say that you have many different values and they are somewhere or less present. Out of the whole record, for example we have 10,000 records and when we search based on one value then what we will get will not be so much. The records will not be so much. It is only possible if the possible value of a field is more than 19,000. In this case an oracle recommendation is that if you have at least 30 different values then in that case you can use it and index it. Because again it is the same thing that if there are less values for example of this age group attribute then note that the arrangement of records is not so much because the tree will be a couple of nodes and after that you will have to follow the pointer so much that if you search the same sequence then it will be possible. And the overhead that you are maintaining will not be balanced by that. So that is why you should consider that if I create an index on a secondary key then there is an option that the variety of values in that attribute should be more. Check the limit of indexes with your DBMS. This will be a problem in extreme cases otherwise I told you that in hundreds still you should check that you are crossing that limit. Make your decision very much justifiable. Indexes introduce overhead. I told you before that nothing comes for free. So if you use indexes and they are giving you efficient access then along with that to maintain them you should use a story. Your effort, your processing is being used in that. So indexes cost you both in terms of storage and in terms of CPU time. So that is why you and the other decisions like the variety and so on you should check that otherwise you should not blindly say that I am happy because DBMS has given me a lot of limits to create indexes. So if you have all the fields and all the combinations then this will not be a wise decision. Now we summarize the discussion on indexes. A useful tool to speed up the data access. We have said this many times and I hope that you have already understood what is the benefit of indexes that they speed up the data access. Can be unique or non-unique. We have said that index and if you have a primary attribute that is a unique attribute then it will be a unique index but the secondary keys will be non-unique indexes. Can be implemented through different techniques. We have discussed that the three major techniques we have discussed were inverted files, linked lists and trees. Be careful. They involve overhead. What have we said? Dear students, like indexes, views are also a very important and technical topic. And again views are very useful things but you have to be careful and you have to be clear about the pros and cons of the views. So views are different types and we are going to discuss them. But the idea of views is basically till now. How? The thing that we discussed in the three-level architecture. When we talked about the external level of the three-level scheme, we said that the view of the data that is accessible, that is available to the end users that we call is the external view. We said that we consider it as the external level of the three-level scheme architecture. The views we are talking about are related to that. We will discuss the views in detail. The idea is that the actual data store, its actual definition, which we have defined in the middle layer, which we call the conceptual level or the scheme, the details or access to hide it from the external user, from the end users. So this concept of view is the advantage. We use views for different purposes and for different reasons. But the technicalities and I will try that the discussion on our views should be clear. So let's start the discussion of this important topic. A view is defined to combine certain data from one or more tables for different reasons. That is, from here we can see that we will take data from one or multiple tables and present it for any user, for any purpose. We will take it from there. One definition is this. The second definition is that a view is like a window through which we can see data from one or more tables. As you can see from both the definitions we can access data from one table or from multiple tables through a view. And it is not necessary that when we are getting a misconception that we will take all the data from multiple tables or tables. It is a window. The view we want we will create as many windows as we want on one or multiple tables. So the concept of view is that your actual table does not give access to it. We are creating another view creating a level and your end user is seeing through it. So the advantage of that is that the nature of your user group for which you are creating a view you can create a view you can open a window as per the requirements of that particular user or the user group. This advantage will be clear because why do we use views? Look if we do not use views then the second option is that we have a conceptual level we directly give access to it or through that we give access to the end users. If we do not have views but the question is if we do not have views then what is the damage to the user group? This means through the view show the data to a user group that is necessary required for them. This means through view you can restrict the user One is that you show the data that is required for example when we were discussing the definition of the database we read that in the same organization there are different user groups and each user group has their own interests and the concern of that user group is with their own requirements but you as a developer as a designer you have to cater to the requirements of all different user groups One thing is that you have to do it efficiently and when we talked about one of the strong features of one of the big benefits of the database approaches that you can share the data among different user groups in the database approach Now when you have designed the database for all user requirements there is some data that is required by multiple user groups but there is a certain part of the data that is required only by a particular user group like I gave you an example of any educational institution if we have a library system there and a registration system then what is that the student's name, his father name, his address may be these two systems are required the users of the library system and the users of our registration system but how many books have been published this is required for the users of the library this is required for example how many books have been published and how many are fine again this is the concern of the people in the library apart from this we have how many overdue books these are the concern of the library people on the other hand if we talk about our registration system students name, father name, address this is their concern but from where is their previous degree, what is their registration number which semester is it at this time how many dues have been paid and these are the concern of the people in the registration system now when we talk about views these are the common things name, father name, address we instead to store it multiple times we store it and the view defined for the people in the library system and for the people in the registration system now the interesting thing is that the user groups will understand that this is the data base it is not there and the technical thing is or the right thing is that they should not be concerned when they are getting the data and when they are receiving the correct data so their concern should not be that which data they are accessing if you talk about organization point of view for example the data about books this should be accessible only to the people in the library so you as a designer you will fix the access to that data only for the library people similarly the data which we have in the registration department you will show them the data only so the view helps you to define the data but the underlying data is being shared and you are managing it very efficiently so you can restrict every user group out of all available data to show the concern and the advantage of other user groups is that whether they are the users of one system different type of user so you can restrict their access through views that this user group can do only this you did two things one is you fixed their data so they can see this and the other through views you controlled the authorization of the user that they have to do only this they have to do this so in this way the efficient management of the data to handle it the views there are big help so as we discussed give all the necessary authorizations and no concern with the rest of the data and as I said no concern with any user group that what is the store and who is accessing it if your requirements are being fulfilled so no concern with the rest of the data why views another reason is efficiency part of a query that is frequently used define it as a view then the view definition will be stored and will be executed anytime where view is referred sometimes it happens that you have stored the data and you have done it for a particular user group when you have the requirements there are many requirements that are based on a specific type of data again in the table you have a lot of data on a specific portion you have multiple types of further queries and on that you have to apply those conditions for example we consider our student table in that we have a lot of applications that come on MCS students first you say among the MCS students who are going to keep in touch with Lahore among the MCS students whose CGPA is above 3 among the MCS students who have not done it on Dues among the MCS students whose overseas student is and like that there could be many other examples common thing is that these students you have to do this again and again this means that if you want to see that students of MCS living in Lahore the conditions in that city is equal to Lahore and program name is equal to MCS when we talk about CGPA CGPA is greater than 3 and program name is equal to MCS you see that this condition program name is equal to MCS it is being repeated in many applications so instead of you place this condition every time what you do you define a view for those students who are enrolled in the MCS program what will happen is that the definition of view will become a view and it will be available as a table the moment you want you have a table of students you have defined MCS students the table of students the view of MCS students when you want students of Lahore you do not need to write where city is equal to Lahore and program name is equal to MCS you simply say MCS students rather than giving the query on the student table you give MCS students view and simply say where city is equal to Lahore now because the program name is equal to MCS has become the part of the definition of the view so this is there automatically you do not need to specify this explicitly what will happen is that when you apply the query on the view MCS students will automatically apply because the definition that has been stored in the form of a view and the query applied is applied on the view and the second beauty is that the data that is not being stored repeatedly that is not being copied in the view in fact it is happening that the definition of your view is stored only in the definition so when you say that is a select star from MCS students city is equal to Lahore so what will happen is that where you said that MCS students in that place the DBMS automatically in this whole struct the definition will adjust that your query will be on the base table DBMS automatically will transform the query on the base table or on the table on which you defined this view automatically select star from student but that would be done by the DBMS where program name is equal to MCS and city is equal to Lahore so this means that the efficiency gain that has been done that you did not have to type did not have to write did not have to quote and secondly the beauty is that data is practically being fetched from the table on which you have created but only the definition that is being used to provide you an efficient access join columns from multiple tables so that they look like a single table aggregate information instead of supplying details I will explain this in detail see when you were designing the database at that moment during that time you had two major preferences one was to fulfill the requirements of the users and next very near to that was to do that efficiently so fulfilling requirements and efficiency so obviously what you have to fulfill the requirements that is your task but also you are looking at efficiency as you have done that you have especially throughout your design process and specifically when you reach the normalization stage you will remember that in the normalization process our focus was to have an anomalies free design in that anomalies especially what we are talking about was that redundancy that your data is not stored in your form that was also a form and on the basis of other reasons we basically split our data into multiple tables now I have told you that you split the data into different tables but when we look at the user's requirements you see it is very less that the user's requirements which the user wants the data in one table it is very less because I have told you that for a smart design it has been split into tables now the user's and the user has no concern about the design the user wants the required information in front of him for the efficiency purposes into multiple tables now this means that it is very common that the user's requirements for that data that would be lying in multiple tables it is very common now when you access the data from multiple tables if you remember what we read in Racial Algebra in the Racial Algebra in that when we access it from multiple tables there are different forms there can be a Union and a Reception but the most commonly we access is through products or the different forms and most commonly the form of the join is the Natural Join a common attribute on the basis of the same value, you join data from multiple tables. So, what I mean to say is that in your database development process, there are many times when you want to have combined data from multiple tables to fulfill the requirements of one user group. For example, if we talk about this exam system, the registration person will ask you to give me the student's name, his father's name, address and what courses are registered. See, from an end user point of view, all this information is related to one thing that is student. But for your efficient design, you have split it into the student table and the enrollment table. And the interesting thing is that the voice that you identify is a concept for you. But the end users are not interested in keys. They are generally interested in more meaningful data. That is generally the name, the title. So, your end user asks you to give me the student's name and give me the name of the courses that are enrolled in. Now, the user thinks that this is one place to study and you will give it to them. But you know that here you are basically referring to three different tables. One table of the student, from there you will take his name, you will take his father's name, you will take his address. One table of your enrollment where you will find out that which course this student is enrolled in and the third table of the course where you will take the course title. So, the user is saying in front of him what? The name, the father's name, the address and the course's name. Underline, you are accessing three different tables. But the user has no concern like this. But definitely this is your concern. So, all this means that you have to access the data from multiple tables in database development. And since the user's concern is the same data from multiple tables and you have to provide that data very frequently, then again here comes the concept of view. Rather than every time you write a long query, that the data is joined from multiple tables and it is giving you data X and after that you are doing further operation. After that, after joining, you are putting conditions on it. So, you can have the advantage that you simply define a view. That view will also be that, as I have given you an example, you are combining three tables and the user is feeling as if all this data is coming from one table. See, here I will explain a little bit more. We talked about users, we said that there are two types of users. End users, one we said naive and one we said casual. The naive user is the one who you will design a front-end. Through that menu, the user will access the data and there is no concern with the underlying structure. But the casual user is the one who has an idea of how to use the database and then you give him the rights to access the data directly. For this type of user, if you first say that your higher management is one of them, if I have to see the students and their enrollment data frequently, then you can do this for them, you can define this view. And even for yourself, that is the requirement of both types of users, how can your view be useful for both? The naive user, for whom you just have to design a front-end, for that, it is useful for yourself that you define a view and when the rest of the design is your front-end, your GUI, then you can do the designing through this view. So, you always give this statement, when you pick the tables from the data, you will be free from it. You know that I have this view, all the data that I have to place in this place, all the data that I am combining, then your recommendation will only be from the view, not from the underlying tables. When you know that the underlying tables are there, you know that. So, your user like this will help you with your view, it will help you personally. But the other type of user, who we have called casual user, how is it helpful in that case, that instead of going over the structure of the tables, and giving a general condition on that, you simply define a view. You say, this is our view, this is your, for example, what is the table? Because for the end user, it is not necessary to know whether this is the view or the table. You say, this is your required data, this is its field name. You click on it. It uses a special statement. And it is accessing the data, but basically, what is your statement, it applies to the view. And your end user will not even know whether it is necessary or not. That user is using this view just like a normal table. Similarly, there were times that you have to process some aggregate information. For example, you said that, you will define a view that the average of MCS students, CGPA, what is the total number? This means that by aggregating, by applying an aggregate function, you have to process something on that. So you have to define that in the form of a view. Instead of giving the query again and again, you have to give the query again and again. So you define that view and if you use the user group yourself, you give access to that view without using the underlying table. All your requirements fulfill that view. Underline is using the table. I hope you have been clear that what is the advantage of the view and why we use the view. This is further clear when we define the views and use them. The things we are going to read are the characteristics of the views. Not exactly the external views of the three levels scheme architecture. This is a debatable thing. Some people have the same thing. Some people say they are different. It can also make a difference when we talk about external view. As I told you earlier, some external views have that interface. In the interface, you have different fields at different places. The user is manipulating them using menus directly to access the view or the table. He is accessing the data through that interface. The external view has that interface. If you yourself and a casual user use that directly then the meaning of the view is the same as the external view. It is not that important but the view that we are not discussing is similar to the external view. In general meaning you give the same meaning as the external view. These are the four major types of views. Some of them are overlapping and you say that you get different views on different places. We have dynamic views materialized views partitioned views simple or complex views. We have different types of views. We will discuss them in detail. First of all we are going to discuss the dynamic views. Dynamic views are the same that we have been discussing so far about the views. Dynamic views are also called the virtual tables. Virtually the same as they behave almost like the tables but they are not practically physical tables. The definition of the tables is the same as that statement against which that dynamic view was defined. That statement is executed as much as you use the name of the dynamic view. Let us see. Data is not stored for the views. As I have mentioned definition is stored in the schema. The definition of the view becomes a part of your schema and executed every time views are referred. This is the text or here is the general statement to create a view. This is specific to the SQL server. It is possible that if you do in any other oracle example it will be different. The names pattern may be the same. Create view it is written in the capital you have to give it as such. If you have an optional in the SQL brackets you have to give the name of the database and the name of the owner. If you are doing it in your own account then you don't need to give this. The name of the view that you have to keep the view is the columns. The column name is the one that will be in the view. Not from here but from here I will give you an idea of the name attribute which can have entirely different name in the views. Here you will give the name of the attribute but you can leave it. It will have the same name which is in the table. We will see this in the example. After that with encryption or schema binding it is required. Then is the select statement that select statement for which the view will be created. The definition of the view is here in the form of the select statement. It has an optional thing with check option. This is a very good and important option. We will discuss it in detail later. Dear students in today's lecture there was a discussion on indexes. We did a wind up in which we discussed what is the difference in clustered and unclustered indexes. Then we saw some tips when should we use the indexes and when the indexes would be beneficial. After that I hope that the views are important because it is a very important tool to provide security and after that we just have touched the topic of different types of views. From that we have started the dynamic views and we have discussed the format of defining it. In the next lecture we will see the different examples and how different views are defined and used. Give me permission. Goodbye.