 Assalamu alaikum. Dear students, this is lecture number 41 of the database management system course. The course code is CS403. In our previous lecture, we were discussing the views. As you know by now that views are a very important topic in the database systems because number one, it is the part of the three level scheme architecture. It is it represents the top most layer of the three level scheme architecture. Secondly, this is the point, this is the layer where the database is accessible to the external world, to the end users. So, this shows their importance because in this case area, your users are manipulating your database. You are hiding that, you are covering that layer from the users, from the end users, you are saving the access to the conceptual database design by providing this external level layer to the end users. As we discussed in the previous lecture, the beauty of the concept is that the user need not to know that they are dealing with the views. This is something virtual. They are dealing with the virtual tables. The views give them the behavior just like the normal table. Both the MAM operations, both the MAM where you normally use the tables, views you have to provide a mechanism to implement the security. You can show, you can give access to the users only that data that they should access. And saying the same thing in the other words that you can hide the things from the user that you think they should not access. In the previous lecture, our main discussion was on the dynamic views. Dynamic views are the views that are basically not stored as yes. I mean the data for the dynamic views is not stored as such, rather the definition of the view is stored. And whenever a user refers the view name in a query, that query is executed against which the view has been defined. Or this is the job, excuse me, this is the job of the DBMS that whenever you refer a view, the DBMS places the definition of the query, sorry the definition of the view at that place. If you are referring the view in a query, then when the query is executed, the place where you have used the view, the DBMS will automatically place the definition of the view at that place. Or you will not know, the user will not know that the view is not being used as a table, rather it is a query. So, as a result of the use of the database, it also provides you with ease. In addition, I have told you that it also provides you with a security mechanism. And then it is that the actual design of it, and the way the user data is being accessed, you can create a difference between them. Why? As I told you, the user is interested in getting the data the way he wants to. But as a designer, you want to store the data efficiently. So, both these requirements are fulfilled by you are storing the data in the form of conceptual database design, the middle layer of the three level architecture. And when you want to show the data to the user, you define views for them. What will happen to that? How do you show them views in that way? As they want to see, how? By combining the data from one or more tables in the form of a view. So, the user's view is available in the same way as the table is available. Another thing besides this is that during the course of this lecture, we studied the three level scheme architecture. Then we also studied the data independence. And there we studied there are two types of data independence. One is the logical data independence and other is the physical data independence. So, your logical data independence that is implemented through the external view. These are your views on their basis. Here you have changed the conceptual design. So, if you have given access to the users, then if you change the conceptual level, then your existing views will be unaffected. There will be no difference on that. So, this is a sort of summary. What we have studied about the views so far. So, today's lecture will start with the continuation of the discussion on views. So, let us start today's lecture. In our previous lecture, we studied that we can perform many of the operations that we normally perform on the tables. We can also perform those operations on the views. And in that, we saw that especially when we talk about updating. And the difference is that you can change the data or insert the views on the table. And we studied and we saw that we can insert the views through the views. Actually, we are inserting the data into the view. But practically, it is going to be stored in the table, in the underlying tables. And we have studied that definitely there are some rules, there are some precautions that we have to take care of. So, in the previous lecture, we saw that through the views, the views that were based on a single table, we entered the views through the views. But we saw that the views were ultimately inserted into the tables. Today's lecture we are starting with is how we can update views that involve multiple tables. In this, we can update or we can insert the row through a view involving multiple tables. But the thing is, if we have to do it one at a time, we will enter the data into any table at a time. Now, this is an example in front of you in which we have defined a view that is based on two tables. It collects data from two tables. It shows that it is a student and a program. So, the view has been defined on the student table and the program table. Then we have given the names of the attributes in the views. Again, we have ignored naming convention for some time. And the names that we have used are A1, A2, A3 and A4. After that, as that is required, that is the part of the statement. After that, the statement that provides the basis for the view. We have given a select student ID, student name, program.programname. Again, because program name attribute is defined as such in both the student and the program, that is why we have to qualify the program name attribute with the table name. So, that is why we have called it program.programname. After that, it is comma program credits. This is the attributes of the program table. After that, we have called it from student comma program. These are the tables from which we are doing data fetch. Where student.programname is equal to program.programname. So, this is a joint statement. Now, here you see that the data we are entering, we are entering the data into the attributes of the view that correspond to the attributes of the program table. Because we have used A3 or A4. And because A3 is defined corresponding to the program name attribute of the program. That is the primary key of the program table. So, it means that we are fulfilling the requirements of entering the data through the view because I told you in the previous lecture that you cannot ignore the not null attributes. And definitely, the primary key is definitely a not null attribute. So, A3, A4, we gave two of our attributes. If you look at this, the values given are MSC. MSC is the program name. And next attribute is the credits. We have given it a value of 110. So, now when we have done this, you know that we are discussing the dynamic tables. Dynamic tables means that there is no data of their own. Now, when we have inserted the statement and obviously, we have done it through the view. But if you see that the data that is stored practically is in the table. So, where I have given the select statement, that select statement I have given on the program table. If you look at this, then here is our program table. You see that you have all the programs that we had in the program table. And the last record that we are seeing is MSE, which is the current record we have entered. And see, the total semester attribute, it contains a null. Why? Because total semester was not defined as part of the view that we just defined. What were the attributes in that? Program name and program credits. So, we entered the value of both of them. That is there. But because there was no total semester in this, or when an attribute is not a part of the definition of the view, then obviously, we cannot enter the value for that attribute through the view. Obviously, we can update it later on the table. But the view is that we cannot enter that attribute. If you look at the next slide, again we entered the data for the first two attributes and those attributes were from the student table. Or again, we have given the values for the not null attributes. If our view definition did not include any attribute, then we have declared as not null in the definition of the table. So, through the view, we cannot enter the data. Now, it is different because we had our student table. In that, we have got two not null attributes. One is primary, that is by default. And apart from this, when we defined the student table, in the definition, we also declared the name as not null attribute. So, we have given the value of both of them. And when we accessed the data from the student, now we see here that we have got the data. And if you look at the last record, in this, you will see that the record that we just entered is null in all the attributes. But the current semester contains a one. Why? Due to the default value, which we have kept for the current semester. Interesting thingy. There is no word for data base. We have made it a thing with love. So, let us see that the interesting thing is that if we select the view, if we access the data through the view, then the two records that we just entered are not visible. Why is that? Although, the thing to think about is that the records that we just entered, those we have entered through the view, are fine. And both are entered, that we have seen the tables. But when we accessed the data through the view, then the interesting thing is that those records are not visible, because we just entered through the view. And I hope that you have understood the reason that the view that we have defined is based on the joint condition. Because when we say that the two records that we just entered will enter, in which the program name is in the student table and the program name that we had in the program table, in which both the tables have the same value, those two will join in our view. Now, the situation is that we have entered the value of the program name in the program table because it was the primary key there. So, we have entered it there. But if you see that if you see that if you see that our student table is there, then we did not enter the value of the program name attribute there. In fact, we could not do it. Why? Because the program name attribute of the student table was not a part of the part of the the view, the definition of the view. Because it was not included in it, now we cannot enter it. So, the time you have accessed the data through the view, the two days that you have entered through the view, you cannot see those two days. Now, here a question is born that if we define this view with the check option, what would it be? Now, this is an assignment that I am leaving for you. You check it and then you establish its logic yourself. Clear your concept based on its reason that if we define this view with the check option, and then see that when we enter these two records that we have just entered, in that case, what will we get? As we have studied before that we can also use the aggregate functions in the definition of view. Rather, this was one of the basic reasons of defining the views, because when we have to apply the aggregate function that involves a huge number of rows to avoid typing that thing again and again, we can define the view. So, in this example, I am going to show you a definition of a view that involves an aggregate function. Again, we have given the name to the attributes. Select program name comma average CGPA. Now, average is an aggregate function that we are applying on the CGPA attribute of the program of the student table. Fine. And then after that you knew that when we are using an aggregate function in that case we cannot give the name of a normal attribute unless we use that attribute in the group by clause. So, in this situation we are using the program name in the select list but at the end of the statement we are giving the group by clause and we are giving the the program name attribute there. So, this is a legal statement and after that when we access the data through this view what we get? Well, this is the output. In this, you see that you have the headings, labels, attributes names in the view that is a1, a2 and again I have told you that you will define the name meaningfully. At the beginning you have a null value to CGPA, that is why it is null. And then if you look at the rest of the programs students have their program-wise the average CGPA that you are seeing through this view fine. Remember one more thing that we just entered the data of the MSE program in the program table. That you are not seeing here. Why? Because we do not have any students so far that has been registered in the MSE program. And because we are getting this view, we are creating this view on the student table. So, we are not getting the MSE data in this view. One more thing that you will immediately suspect that the average value is very long. So, reduce it. So, the thing is that if I do not do all the work then what you have to do is the professional part. And I will tell you as a hint that we have done this work for the first time in this course. With the previous slide our discussion on the dynamic views ends. Now we enter, now we start the next type of views and that is materialized views. And the basic idea of materialized views is that it is in a view. The basic idea of dynamic view is that it stores the definition of your view. But see in the real life applications there is a lot of data on the barstables. And when you define the view on that table or such views that involve join or the aggregate functions. Now it is obvious that you got a single step. You do not have to write that definition again and again because you have stored the definition of that view as a dynamic view. Fine. You can simply use the name of the view and the DBMS will automatically execute that definition of the view. Fine. This is one level of convenience. One level of facility. There comes a stage when even that is not sufficient. Why? Because the number of rows involved in natural join or in the aggregate function is so large that it consumes so much time. So although we have gained one level of facility one level of convenience but still we need more. So materialized views can help us another level of efficiency another level of convenience. What is that? Materialized views they store the data as well. Earlier you had only definition store of your view. Now that data that is being generated as the result of the view that data is also being stored. So then you can feel that in this all the curies this is interesting that all the curies which do not necessarily refer to the view but on the basis where this materialized view is being created if we refer to it then this materialized view will be referred to. Fine. Views are virtual tables and that is also being said about the dynamic views. Fine. Curie executed every time. For complex curies involving large number of join rows and aggregate functions it is problematic. Well, bucket full of tears. Solution is materialized views also called indexed views created through clustered index. Now here the terms we will talk about are indexed views or the way we create them they are clustered index. The clustered index is already studied but that was regarding the table. We said that the clustered index created on the table is one and it is generally primary key. The physical arrangement records are based on your primary key so your clustered index is created on that only. Now your indexed views our basic topic is indexed views but this too is created through clustered index. And the idea of clustered index is that it is based on the physical order of such indexed records. Creating a clustered index on a view stores the result set built at the time the index is created. Like we said that the table on which clustered index is created the table will be stored on that sequence and normally it is primary key. Similarly, when you create a view the result of the view which is generated when you create a clustered index that is when you create a clustered index on a view the data which is generated for the view is stored there. Before this when we read the dynamic views which we called virtual tables there was no such thing there was no stored data but the data was always fetched from the table but in this case the data will be fetched from the table when you create a clustered index on view the data will be stored physically. Next time you got another level of efficiency that you are not executing a query but you are getting data and indexed view also automatically reflects modifications made in the base tables after the index is created the same way an index created on a base table does. Dear students I have made you realize that facilities which you get from DBMS usually cost and in the sense of disk storage and secondly cost in the sense of execution time I am telling you about indexes whenever you create an index every index creates an index file and in that extra storage is involved and when you are using your base table we are using a term as a table as a table as a relation which is stored in the same base table so whenever you enter the base table because your data is going into the base table so when you enter the data the data you are entering will be placed on the appropriate place in the stable but also on the attributes you have created the indexes those indexes will be your updates the key every index which is defined the key value of every index will be adjusted to the appropriate place and the link of this new record will also be established it depends on how you have implemented the indexes this is the case with materialized views again the case is your data is going into the base table fine but also your materialized views it is not that we have only definition stored as I have told you the data generated in the heavyweight execution the data is also stored so the base table which you entered along with your materialized view the data will be placed on the appropriate place which means when you have created the indexes sorry when you have created the views the dynamic view of the views does not matter because it always has to be executed but when you have created indexes whether they are clustered indexes whether they are unclustered indexes whether they are indexes on the table whether they are indexes on the view when you enter the data that that data is adjusted not only in the base table but also all the relevant indexes are updated and this will be with your materialized view along with this when you have entered the data in the table when you access the data with your materialized view the data which you entered will be seen in that view at its appropriate place and there is a difference between the basic view and the materialized view dynamic view but in materialized view the data will be stored in that index because this is the basic difference between a dynamic view and a materialized view create indexes only on views where the improved speed in retrieving results outweighs the increased overhead of the indexes like I just told you whenever you modify whether you change the data whether you insert the data whether you delete the data that change has to be adjusted that change has to be reflected not only in the base table but also in the indexes whether they are clustered indexes whether they are unclustered especially in this case which is your materialized view in this case analyze these things that we are getting overhead in both cases in both cases in the storage case because the data is being duplicated along with the adjustment the proper placement the arrangement the overhead you are getting the benefit you are getting is more then you create don't go blindly for such decisions they are crucial decisions now we have another example we said alter view when we want to change a view we call it alter view and create it again we said ST view 1 our view on student with schema binding this is the option which we did not use on this view you want to create a clustered index or you say when you want to create a materialized view then in the SQL server this is specific with the SQL server if you are using an odd db then you see your documentation so in the SQL server when you are using it that view you will have to create it with this option with schema binding this means the view will bind with the schema this means the view based on which tables if you want to delete those tables then until you finish these views or you don't finish the binding then you won't be able to delete them so this is required for the materialized views that you create that view with this option whereas the other views we did not use this option it means our clustered views were not there we said select student name student father name program name from dbo.student this is a requirement by the SQL server that in that we did this till now we used the name of simply tables but for this particular option if you want to create this view for that name dbo that is a default owner in the SQL server dbo.student student is our table and when dbo is the owner's name where program name is equal to mcs now this is a materialized view whatever data is created what is the data these three attributes student name, student father name program name mcs students these three attributes the definition of this view now after this this is the statement through which we have a clustered index on the view that will be created so what we said create unique clustered index student view index one student view and it took index so it took index so this is a naming convention we adopted on st view one and then on the attribute base this index will be created we named that attribute now what you said that the data in this view the data in this view all the data will come will be physically stored and your index will be created on the base of student father name now in this if you first your student view one which is your materialized view without using it even if you use student father name in that case this will automatically be referred although this view is not mentioned but the dbms is smart they can identify this that on this attribute will be referred to this is how you create the materialized views the next type of views that we come across is the partition views partition views basically for this course i think out of course properly they are studied as a part of the next course on database management system and that is distributed databases basically this topic properly is of that subject but in this course to give you an idea i will discuss it briefly but its implementation on that i think you should not try it because this is too much anyway partition views idea is distributed databases over there the idea is that you have different locations and the data is placed on different locations it could be due to many different reasons but we suppose data is placed at different places and the objective is being served at the individual users at the individual sites they are accessing the data and their requirements are being fulfilled but there are certain requirements that require data not only from a single place rather from the multiple places suppose we have stored data at two different places her site copper they have their own users the data that is stored at a local site but there are certain users that require data from both the sites for example as an example we have got the branches of the banks let us say throughout Pakistan consider any bank and nowadays we have online services and you have ATM machines now the thing is banks can store all their data at a single place fine but that has got certain problems they do not do that what they do is they store the data region-wise first as an example those around the bank we store the data at a place in Hindi then again the branches or the users that are around Lahore we store data for them in Lahore likewise let us say in Sakhar and in Krashi now what happens that your users when you access your account then you do it from your area from your city sometimes you can do it from another city but that is anyway the requirements of the users are the local data where they are accessing and using them but first let us say the banks who have higher management they want such a query they want the one that spans which is applied on all the sites we want to see which customers which customers who have taken the loan or which customers who have deposited more than 5 million during last year now this is a query that cannot be only fulfilled from a single site but it has to be collected from all the sites so such a view that when your data is stored on multiple places and at each place it is serving its purpose but there is a certain requirement that requires data from all different places so in that case what we do is we create partitioned views what is partitioned that your data as we have mentioned that view is like a window you have created a window that is collecting data from multiple tables and from multiple tables we have accessed the data in views the only difference with the partitioned views is that the data in them is on a single server or on any dbms not in one dbms not in one location it is on the multiple locations on multiple locations your dbms is running well theoretically they can be on the same machine if you want to test your idea they can be on the same machine but in the real sense you have partitioned views in that they need to be on different computers they are running on dbms and those two computers are definitely linked to the network whether it is the local area or the wide area but they are linked to each other the data is being stored you are creating a view that is accessing the data from both these places and both are because I have two hands but I have multiple hands and anything can happen because the idea is that from multiple locations this is the idea of partitioned views we will not create them here and we will not discuss it further but you should have an idea that what partitioned views are let us not discuss it why? because it is difficult idea is data lying at multiple places and combined in a view as I have told you same table partitioned horizontally based on some condition all the scenarios especially the bank that is the scenario but this statement looks at the same thing from another perspective if you suppose that there are bank's clients bank's customers now the data that you have to store about your customer now the customer is a bank whether it is in any branch whether it is in Lahore whether it is in Jhelum whether it is in Peshawar when you store data in a bank what you have to store about any client it is the same when you talk about a bank it is almost the same 99% is the same may be 1% difference but that is also very less so the partition is the same if we store all the data in one place then all the client's data would be in one place but due to efficiency reason we have split the table you understand the table was the same but we have defined it in other places we have placed components there now every component at its own place it is serving its purpose if you look at the bank's point of view if there is a particular bank their requirements are in the branch of Karachi in the branch of Quetta in Peshawar all the banks are operating their own data the data store of Peshawar is based on the client's data but we have such applications there are such requirements that require data from all these places for that we have originally for efficiency purposes we have split the table but in another requirement for certain requirements we join it in the form of partition view this is the basic idea of partition views simple and complex we have discussed this just because we have read the same classification just to see that one form of classification view on a single table is simple while involving multiple tables is called complex view there is a lot more on views in this because this is our first course the basic course on DBMS so in this the idea of a thing is presented to you as deep as possible in the first course but on views there are many things that we have discussed the further depth of every topic so as much as we have covered in this course that is the base after that when you use a DBMS practically the DBMS you are using you go to the depth of its views and see how we implement the concepts and then further details the further technicalities discuss them Dear students we have concluded our discussion on views next topic that we are going to discuss is the transaction management transaction management is an interesting topic like in from one perspective student feel a bit bored in it so far anything that you have been doing your involvement was that you were doing those things the transaction management or the topic that we will read which is also called advanced topic or you are saying that they are more concerned with the activities that are performed by the DBA till now they were mainly concerned of the designer and if DBA is playing both roles because see the actual role of DBA when your database is developed in its maintenance and in its normal running in its day to day running it starts from there and there are different activities like backup of the database you have to down it this type of activity to monitor it then apart from that if you have to do some changes in the design of DBA but till now the activities that we have been doing that were related to designing they were the concern of the designer and DBA or it will be a designer or it will not be a designer so it happens that DBA on behalf of the organization for which you are developing it assists or it becomes a part of the team so the transaction management or the topic of this in that mainly they are handled by the DBA DBA itself its involvement in DBA it guides or it initiates the bus command but maximum its concepts are being underlined in DBA so students sometimes feel bad about it but there are other things that you hear about that it is implemented now you will know how it is implemented what is its theory and if it is not then what problems do you have in the functionality of the database so the important thing is that you understand the working of the database of DBA and the topic we are going to start the concern of transaction management is two major things one is database recovery and the other is concurrency control and we are going to read both the topics and you will find it very interesting the basic idea the recovery idea is that if your database is running daily whatever you have done the data entry the application is running the data access all the activity is running but sometimes it happens that your database crashes the problem comes from the crash the basic idea of the problem is that when you turn on or off the computer in the same way the database or DBMS when you deactivate or deactivate the database is that a particular DBMS is controlling or running handling or managing it ups and downs properly DBMS is like a computer system the database which is running under DBMS which is managing properly opens and ups and closes properly when we talk about the system crash this means that you cannot do that properly that is a bad situation and your recovery mechanism the responsibility is that when you crash when you up DBMS your recovery system will identify that last time there was a crash and the other thing is it will cancel the effects of that crash so this is the basic idea of the database recovery mechanism the other part which we call comprehensive management its basic idea is that your database multiple users are accessing at the same time when they are accessing at the same time because of this phenomenon that multiple users are accessing if we don't properly control it there could be problems so the comprehensive control mechanism its objective is that that problem which is being handled because of the concurrent access so these are the two major topics which we are going to talk about in the transition management so let's see how we can touch this topic well you have the transition management these are the two major parts the basis of transition management both of these activities recovery and concursive control both of the base on which these are so in this lecture in the last lecture we will only discuss the concept of the transaction so the transaction is the logical unit of work performed on the database may involve one or more operations look again that you have the transaction is that when you say this is a work, an activity as a whole that is called a transaction and in one transaction one or more operations operations you can call it operation simply you can understand the tool that you are using roughly so when you have a transaction it can be on one statement and it can be on multiple statements and then this logical unit of work depends on the system that in one system what do you mean by transaction for example if you take a bank example this is a transaction if you see a medical store reference then you go there and take the first medicine then that may be a transaction as a reference to the medical store if you talk about your examination system then there you see that enroll the subject enter the result of the paper compile the result generate the transcript all these could be the example of transactions to which you say that this environment by system this is a work, this is an activity that gives a logical framework that yes, this is the thing this is the work so the steps that you make your transaction they are called operations and overall they are jointly they will be called a transaction Dear students we will conclude here and today you have seen that we have finalized our discussion on views and in this what we have started was that how we can operate on multiple tables through a view but we said that at one time on one table after that we discussed the materialized views how do we handle them then after that we discussed a little on the partition views so that you have an idea of what they are and now we the next topic which is transaction management has been discussed and we have discussed the definition of the transaction so our discussion will continue in the next lecture Allah Hafiz