 Okay, good morning. Welcome to the third day of this DBMS course. Today we are going to be finishing up with SQL covering some more advanced features than what we have done today. And today's lab is also on SQL. From tomorrow we will move to other topics. So, so far yesterday we have covered pretty much all the basics of SQL and you can write pretty much any query which you want using things which we discussed yesterday. However, as with any language, a minimal language is not necessarily the best language. There are constructs which although they can be expressed using other things, it makes a lot of sense to include it in the language either to simplify the job of the programmer or in some cases to simplify the job of the optimizer so that the programmer can directly specify what they want to help the optimizer understand how best to evaluate the query. And this latter part was actually raised in one of the questions yesterday where somebody asked me, how do I find the second largest element? How do I find the nth largest element? And I had mentioned that this is covered in chapter 5 and if time permits we will cover it today. It's there in the slides actually. But in the meantime somebody had posted a good solution to that particular problem and I have actually forwarded it on Moodle. So, I hope all of you checking your email which is registered with the Moodle site. If you are not getting emails please contact your local coordinator and they can make sure your email address has been entered properly. So, you should be seeing the responses and for this particular question you should have seen that response if you have checked your email. But as I have mentioned in my follow up to that response the basic way of doing it is inefficient. Therefore, SQL has provided extra features which will help the optimizer do this task more efficiently. So, with that let's move on to the contents of today's talk. I will be covering two chapters from the book chapter 4 and chapter 5. Chapter 5 is actually a fairly big chapter. It has a lot of advanced stuff. We really don't have time to cover all the details. So, towards the later part of my talk I will give you a quick overview of what is in chapter 5. And the goal is not to that you will understand every single detail. But the goal is to tell you what is there so that you can go back and read it up on your own. So, in this chapter we are going to be covering joint expressions, views and then a bunch of stuff such as transactions, some stuff on indexing very briefly and then integrity constraints and authorization. So, we have already seen what is a natural joint operation which is basically a Cartesian product followed by you know equating attributes with the same value if with the same name rather across the relations which are being joined. Now the initial version of SQL did not support any explicit joint operation natively. You just had to list the relations from clause and put the joint predicates in. But after sometime people realize that it would be nice to have a natural joint built into the SQL syntax. But more importantly people realize that there is a need for a new operation called the outer joint. And what is that? We are going to see it in a bit if you do not know what it is. So, to illustrate the ideas of joint types and outer joints let us look at sample relation which is prerequisite and course. So, in this small piece of the course table and the prerec table we have three courses Bio 301 CS 190 and CS 315 those are the three courses which are here. Now courses may have prerequisites you are supposed to do this course only after you have done preceding course otherwise you will not understand what is going on here. So, for example, in IIT Bombay for the database course the prerequisite is the data structures course because we do cover certain data structures in the database course. So, you need to have a prior knowledge. So, here is a small sample table of prerequisites which says that Bio 301 requires Bio 101 as a prerequisite. Pretty much all computer science courses would require a basic programming course which is often called 101 CS 101. Similarly, other departments have this. And we are also recorded that CS 190 and CS 347 both depend on CS 101. So, those are the prerequisites. Now you will notice two things about this table. One of the courses CS 315 does not actually have any prerequisites. This can happen. There are CS 101 generally would not have a prerequisite and you will notice that also happening here CS 101 does not have a prerequisite. The other thing you notice here which is actually not very meaningful in a real database, but we are going to use it just to illustrate our points is that the course CS 347 which is here does not appear in the course table. Now, this is not going to happen in reality even in our schema because a prerequisite course ID is going to have a foreign key referencing the course table. But for the moment let us assume that is not there. We can construct more meaningful examples, but we decided to stick to this schema because you are all familiar with it. So, those are the two things which don't quite match here. So, forget the syntax up here, but if we join these two tables you will notice that join on what? For each course we want to find out what are the prerequisites of that course. So, Bio 301 over here is joined with the prerequisite table and it joins with this entry which says Bio 301 has prerequisite Bio 101. Similarly, CS 190 has prerequisite CS 101. So, those are the two rows which do match, but if you go back to our input tables there were three rows in each of these. Supposing our goal was to say list all the courses and for each course also list all its prerequisites. Now, if you just did a join here you will observe that CS 315 just vanished in the join result because it did not have a prerequisite. So, maybe that is not what the programmer wants. Maybe the goal is to show all courses regardless of whether they have a prerequisite or not. So, in terms of joining the rows from this table we do want to match up rows from the course table with matching rows from the prerequisite table. However, if a course in the course table does not have a match in the prerequisite table a normal join would simply throw it out. Whereas, what we want in this case is that that row must still be present in the output. Now, if it is an output what about values from the attributes which came from the prerequisite table and what is the prerequisite ID? What should it be? Now, there is no matching row. So, if you want CS 315 to appear in the output we have to put some value for the attribute from there and in this case we would put null, null is the default. So, that is what we have here in this example we have used the SQL syntax for natural joins combined with the syntax for left outer join. So, what we have done is course natural left outer join prereq. So, this has two components the natural join party of familiar with the common attribute is course ID. So, each row in course is matched with prereq rows with the same course ID which is fine for the first two rows, but for CS 315 there is no such course in a join it would have been eliminated, but because we said left outer join all the rows of the left input here the left input is course. So, all the rows of the left input are preserved. So, they do appear in the result in this case CS 315 does appear with the prereq ID set to null. Course ID itself is actually common to both the relations to course as well as prereq. Just like in natural join that attribute appears only once the in a natural join if you recall the two values are going to be equal. So, we just kept one copy. Now, here it is a little bit trickier you will observe that CS 315 did not actually match anything in the prereq table. Therefore, the course ID which comes from prereq is null the course ID which comes from course is CS 315 for this particular row. However, left outer join anyway preserves just one at copy of course ID does not keep two copies the one with null is basically thrown away. So, if you want to know that it did not match or any row you will have to look at other attributes in this case the prereq ID. So, that was left outer join. Now, the SQL syntax symmetrically supports right outer join we could have actually written the same the previous one as prereq right outer join course which would give you the same result except the order of the attributes in the table is flipped whatever was on the right now comes to the left and right. But this one is slightly different it says course right outer join prereq. So, this time the right outer join operation preserves all the rows of the right input in this case prereq. So, in this sample data there was a course in prereq a course ID which did not have any matching course and that would have vanished in a join whereas, in the right outer join it is preserved. So, CS 347 had as prerequisite CS 101 it would have vanished in a join, but in the right outer join it still appears with the remaining attributes which come from course set to null. So, that is the third row here CS 347 null null CS 101. So, that those were the two basic left and right outer joins there is also a full outer join which is basically the combination of left and right outer join. So, what it does is it does an inner join then any rows from the left which are not matched by any rows from the right all those left rows are output padded with nulls. Similarly, any rows from the right input which did not match rows from the left input are output padded with null. So, we will get both CS 315 with prereq ID null and CS 347 with title department name credits set to null. So, those were examples. Now, let us look at the SQL syntax for joining relations and the syntax basically has two parts there is a join type which is inner join which is a normal join it is called inner join to distinguish it from outer join the keyword inner is actually optional. So, you could just say join and it refers to inner join then you have left outer join right outer join and full outer join as we have seen. Then there is a join condition which says what is the condition for matching rows from the two inputs. Now, all these joints are binary joints. So, the join condition if you specify it as natural which is the one we just saw it forces all column names which have the same name in both the inputs to be equated. There is also an on predicate where you can specify any predicate you want and that is similar to a var class predicate, but not quite the same we will see in a moment because it is in an outer join. And then there is a using which we have again seen before which is just like natural join, but restricted to specific attributes which we list in the using class. And if you recall I told you that natural join is dangerous unless you know what you are doing if you are dealing with complex schemas and joints of many relations. If you are joining two relations it is not hard if you are joining three, four, five relations it is easy to make mistakes with the natural join operation. And then the using syntax is a lot safer because it lets you specify exactly what to join. So, here are some examples of the syntax to help you understand what these mean. So, the first one says course inner join pre-rec on course dot course ID equal to pre-rec dot course ID. In fact, this particular condition is basically the same as the natural join condition, but the difference is this is not a natural join. So, if you look at the output here course ID actually appears twice once from course and once from pre-rec down here. So, it is actually occurs twice. Now, in the inner join since they have been equated and non matching rows are not preserved it is very clear that the two values are going to be the same. On the other hand the next query is the same one, but instead of an inner join we said left outer join. So, all the course rows are going to be preserved. So, now if you look at the third row in here CS315 it did not have any prerequisite all its attributes appear here and then all the attributes of pre-rec appear at the right bottom with null. So, now you will notice that course ID appears in two columns in one of them the value is null for this last row and down in the left column it is not null. So, that is the difference between doing a natural join and using a join on a condition. The other thing I will note is that if you put the same condition in the where clause you are not going to get the effect of left outer join. Because once you put a condition in the where clause non matching rows are eliminated period. So, if you want rows to be preserved that condition has to be in the clause over here with the on condition for an inner join it does not matter for an outer join it does. So, we have here course natural right outer join pre-rec which we have seen already and then the next one is course right outer join pre-rec using course ID. Now what is the difference here? This if you notice has four rows here we are doing course full outer join pre-rec using course ID. In this case course ID is only common attribute. So, it is really equivalent to the natural join, but when there are repeated attributes which are not included in the using clause it is not quite the same. So, we are basically done with the join types. Now I have a couple of simple quiz questions. Do not answer the questions yet. As I told you earlier today please press the s-t keys right away and once I tell you to answer the question make sure you do not press the s-t key. First question is is r left outer join s and s right outer join r are they the same if we ignore the order of the columns in the result that is question 1 and the answer is yes no depends and none of the above. While the second question is which of the following have exactly the same results given relations r a b and r b c. What do I mean by exactly the same results? That includes the order of the columns. The order of the rows is kind of irrelevant that is does not really matter in a relation. So, the options here are a natural join s, r join s using b, r join s on r dot b equal to s dot b and the options are you know a and b are the same a and c are the same b and c are the same and all three are the same. So, we will go ahead with a few more topics and then come back to this particular quiz question. So, now the next topic is views. Now what is the view? It is basically a relation which is not actually stored in the database. It is a kind of a virtual relation whose contents are defined not by listing the tuples, but by defining a query using other relations in the database. So, these virtual relations are called views in SQL. The way you create a view in SQL is simple. You just take any query you want to turn into a view and say create view give a name for the view as and then give the SQL expression that we have been using. Now, you remember we used the width clause in queries. The width clause is a form of a view which is local to that query. So, we are defining a relation temporarily which is only available in the context of that query. Now, even the width clause does not mean it will actually get evaluated. It is not that the SQL engine will actually compute the relation and then use it. We will see how views can be evaluated in a little bit, but the only difference between a width clause and a regular SQL view is that the view is globally available and it is persistent. What does that mean? The moment I say create view as query expression whatever expression I give that definition is stored in the database schema and it is permanent until it is deleted or modified. So, now any query can come along and access that view just as if it is a regular relation. The only difference for the width clause was that particular relation is only accessible within that single query. Other queries cannot use it. So, if you have something which you just need temporarily for writing one query, it would go in the width clause. On the other hand if you have something which is likely to get used by many different queries across the system then you probably should create a view out of it. So, as I mentioned again a view definition causes the saving of an expression. It does not actually compute the query at that point. Supposing it actually computed the query at that point and saved it what could go wrong. Let us say my view was something like select the total salary from all instructors and I store that. Now comes along a sixth pay commission all the salaries increase suddenly the stored total salary is wrong. For that matter annual increment new instructors, added instructors quitting all these can change the underlying table instructor table and then the contents of the view which computes the total sum of the salaries of all instructors would be wrong the moment instructor is updated. Therefore, by default systems will not store the content of a view they will actually store the view definition and use it when required. It turns out systems can actually store the contents in case you do not want to spend the effort at query time to recompute it and there is a good performance reasons for doing this. But the catch is that if you do so you need a mechanism to update the contents of the stored view whenever the underlying data changes. So, if instructor added deleted updated then the stored total salary should also get updated correspondingly and how to do it well there are ways to do it and quite a few databases these days support views which can actually be computed and stored, but not all post-criscule for example, currently does not allow materialized views, materialized views are these stored views by the way. So, here are some example views they are used for multiple reasons. So, here is the first one which is create view faculty as select ID name department name from instructor what is this doing? You will note that instructor had a fourth attribute salary which has been removed. So, what is the benefit of giving a view like this well maybe there is a user or an application which should not be seeing instructor salaries that is confidential. In India our salaries in the government sector are not really confidential they are in fact put up on websites by Indian government law, but certainly in other places in private companies the salaries are usually confidential except for a few top employees. Therefore, you may give this view to people who need to look at who are all the instructors while hiding the salary attribute. So, that is a view which has been given to somebody some user that user can say select name from faculty where department name is biology. How does this query get evaluated? One way to evaluate it would be on the fly to compute the entire contents of faculty and then from it select out the employees who are in the biology department, but it should be clear to you that this is not a good idea we are going to compute the faculty rows for all departments not just biology and then throw them away immediately. So, more efficient way would be to actually go to the instructor relation find those whose department is biology then project away the salary column and give the rest of the result to the user and this is in fact what the SQL engine would do. So, now here is a second query which is similar to the total salary query I told you before, but it is by department. So, this says create view department total salary department name comma total salary. Now, this syntax if you will note let us give names to the columns here in the along with the view name as select department name comma some salary from instructor group by department name. So, this query breaks up the instructor rows into pieces based on the department for each department it computes the total salary and makes that available. So, that is a perfectly valid view. Now, this example is a slightly artificial view, but it illustrates a point which is a view definition can be used in a query, but it can also be used in another view definition. So, here is a sequence of two views the first view says create view physics fall 2009 as it is basically doing a join of course and section and getting out the course ID, section ID, the building and room number from this join where the course department is physics. So, it is getting out the section information for physics courses which have the semester as fall and year as 2009. So, maybe you know if you want to display in your physics website what are the courses offered in this particular year you may use a view like this. Now, the next one is actually taking a subset of that. Now, each of these courses course sections may be running in a different room and these rooms may be in different buildings. So, the next view is actually getting a more restricted view which is physics courses in fall 2009 whose sections are being conducted in the Watson building. So, how do we do this? Well, we just did a select from physics fall 2009 where building is Watson. So, we have applied a further selection on this view and from that we are selecting just the course ID and room number. Earlier we included the course ID building and room number here we may be the building is pointless because we know it is Watson. So, we can stack views like this that is perfectly legal in SQL. So, how does SQL actually evaluate a query with a view? The standard way of doing this is called view expansion. So, wherever a view occurs you simply replace it by a view definition. So, in our case let us say that this view physics fall 2009 Watson if you just go back to the previous slide you see here this is the view. So, what it could do is replace the use of this view by its definition. So, physics fall 2009 is being replaced by its definition. So, if you look back here it is a same view as before the same select loss in the from class where we said physics fall 2009 we have an entire sub query whose which was the definition of that view physics fall 2009. So, we have removed the use of the view and replaced it by an sub query which is the view definition. So, this is the expanded view where just that one view has been expanded. Now, if a query used this view physics fall 2009 Watson you would replace the use of this view by this big expression here which is the one defining the view. So, wherever there is a view you just replace it. Now, depending on the order in which you do replacement you may have a query which used this view physics fall 2009 Watson and maybe you first replace the use of that view name by this expression which is select from physics fall 2009 at this point you realize that physics fall 2009 is still a view. So, again you replace this by its view definition which is here. So, after two replacements you have a query which is exactly equivalent, but does not contain any views any longer the entire query is in terms of the underlying relations in the database. So, this is the algorithm for view expansion it takes a query finds any view relation in the query expression and replaces it by the sub query which defines that view. In the process this replacement you may have introduced a few new views and you continue this process you search in that expression for any view relation replace it. Now, as long as the view definitions are not recursive this procedure will eventually terminate and in real life it will terminate quite soon there are not that many complex views. Now, what do I mean by recursive it is possible even in SQL although we would not have the time to do it to define a view in terms of itself. So, I can do something like let us take an example of a recursive view which is supposing I give you a relation which stores for each person who is who are their children. So, I have person and children that is a relationship between two people that this person is a child of another. So, let us say I give you a relation which I will write here there is a person relation with maybe id name and few other attributes. And now I have a child relation which has parent id child id. So, this is a simple and natural thing you may actually want to extend this by saying whether this person is the father or mother if you wish although we can infer it from child. Now, a query may want to find who are all the ancestors of a particular person. Now, I am sure most of you must have seen ancestry tree which says you know who is the child of who in this tree can grow big. So, now if you want to find ancestors in there I basically have to go up that tree finding all the ancestors. In the opposite direction I may want to find descendants of a particular person then I can go down the tree and find all the nodes which are directly children and then the children of the children and so on. So, here is a tree actually it may not be a tree it is possible that you can even have a dag over here. This particular case assuming time travel does not happen it will not have any cycles you cannot have somebody who is their own parent or ancestor, but there are other graphs such as railway networks or flight networks where you can have cycles in there you can go from here to Delhi and then come back that is a cycle, but you cannot you know go through a parent relation and come back to yourself that is not possible. So, anyway this is a form of a tree if I want to know who is the ancestor of this person down here I will actually have to go up and find all the ancestors. In general a person has multiple parents. So, it may also branch off there and well person has two parents I assume not more than two unless you start if you have been following all the cases of surrogate parenthood life gets very complicated, but let us ignore that and stick to just two parents. So, now how do I find all the parents in here it turns out that it is not possible to write a simple SQL query which can find out all the ancestors of a person. If you know that the tree has at most three levels then I can write a simple SQL query using joints which can find the parents the parents parents and the parents parents parents three levels three joints n levels n joints, but supposing I do not know how big this tree can be you know maybe it goes back to thousands of generation I do not want thousands of joints. So, what do I do in fact it is possible to write this query using a recursive view definition which says roughly something like this do not worry about the syntax, but conceptually x is a parent of y means that x is an ancestor of y that is one case for x being an answer. So, we can say that x is an ancestor of y if either x is a parent of y or x is a parent of z and z is an ancestor of y. So, this is a simple recursive definition of who is a parent and who is an ancestor. So, I can write this intuitively in English and I can actually write this as a view in SQL provided the SQL implementation supports recursive queries. So, we would not get into that, but there is some discussion in the book if you are interested in recursive queries not all database is supported the syntax for recursive queries is a little bit different from the syntax for normal queries there is some little bit of extra stuff to say that it is recursive. Furthermore, some databases do not support the standard SQL syntax, but have their own syntax for recursive queries. So, we are not going to get into it, but the point I want to make is as long as a view definition is not recursive you can just expand it. If it is recursive you start expanding you will never finish because ancestor view will be replaced by a view containing ancestor you replace it again there is ancestor it will never end. So, there are obviously different ways of evaluating recursive queries. So, that is it for view expansion and the digression into recursive queries. Now, let us come back to some interesting issues with views. So, originally when views were defined people said we are going to use it for queries. So, we will which is what we have done so far, but then people also said we want to use it to hide information from people and as we saw, but then they say maybe we want to allow updates to this view maybe somebody is allowed to update their department or maybe their address probably not their department, but if you had an address field with instructor maybe an instructor well maybe not the instructor, but maybe secretary is sitting somewhere is allowed to update the address field of the instructor although they are not allowed to view the salary field. So, how do you permit this you allow them an update on the faculty view which hides the salary field. So, now what do we have we want to allow an update on a view, but there is a problem a view relation is not actually stored. So, you cannot actually update that relation you cannot update the view definition. So, how do you implement an update to an address on the faculty view given that faculty tuples do not actually exist it is actually a view on the instructor relation. So, it is clear that the only way to implement an update on a view is to go and do a corresponding update on the underlying relation. In our example, we can go to the instructor relation if there is an address field there I am assuming that both instructor and faculty now have an address field I can go update the address. So, in this simple case an update on a view could be mapped to an update on the underlying relation. There are other kinds of things I have insertions deletions and other kinds of updates in general what can we do in general there are actually quite a few problems. First let us take simple case if I insert into faculty some new tuple. So, I am creating a new instructor, but I have not defined the salary what do you do well I can still insert a row into the instructor table with salary set to null because I do not know what it is and that is what this shows here. So, you can implement this insertion by an insertion into instructor, but it is not fully satisfactory because salary is set to null. So, that at least kind of worked others can be made to work in one of several ways and you do not know which to choose. So, here is an example instructor information which joins instructor and department and gets out the ID the name and the building. So, instructor and department are of course joined on department name. So, we are getting the building in which the department of the instructor is. Now, supposing I want to insert into this view a particular record with some ID some name and a particular building observe that we do not have the department of the instructor in this view. Now, the only way to affect this particular insertion would be to insert a tuple into instructor and a tuple into department if it is not already there I could choose an existing department and add a new instructor to an existing department. But the question is which department supposing I said department name to null that is not going to join any tuple in the department table null does not match anything. So, putting the department name as null in instructor is not a valid option for translating this something well what are the other options well I could pick one of the departments I can decide this person is in the CS department and inserted it will work the salary of course would be said to null in this case and the department is CS. But why CS why not biology well it depends on which department is in the Taylor building. So, let us pick whichever department is in Taylor building let us suppose that computer science and biology are in the Taylor building. So, I could insert this new person into either computer science or biology and either would work, but which one it is not clear. So, there is no unique translation for this and in fact, we will see shortly there are updates which are impossible to translate. So, because of these problems SQL implementations do not allow updates of arbitrary views they only allow updates on simple views which are typically views on a single relation selection projection on a single relation although in some cases they allow a few other things the exact specifications are fairly complex and depends on the database system. But it should be clear if I do a simple selection or projection things ought to work well do they see an example which is create view history instructors as select star from instructor where department name is history. So, I am getting all the information about people in the history department. Now, supposing somebody tries to insert an instructor into this view history instructors, but with the department said to biology is this update even possible of course not. If I insert this instructor asses in with biology it will not appear in the view result because I am only looking at history instructors. On the other hand if I change the department name to history then I am inserting a different tuple not the one I was asked to insert. So, this is an example of an insertion which is impossible and our SQL implementations are the better ones this some of them will just say forget it I would not allow you to do such insert. Some of them will actually check the standard actually allows this they will check if the value you are inserting is safe. So, in this case on this view if you inserted an employee an instructor in the history department that can be done. If you try to insert an instructor with department biology it cannot be done. So, some implementation will actually at runtime they will say history go ahead we will do it biology sorry cannot do and raise an exception. So, that is how systems handle it. Now, the previous slide actually had several conditions which allow you to do simple updates and so amongst those are the queries on a single relation in the from clause. The select clause has only attribute names no expressions aggregates or distinct then the attributes not listed in the select clause can be set to null or actually it is a little more generic if the DDL gives a default value for it that is also it will be set to the default value and the query does not have group by having and so forth. So, there are several restrictions, but most databases do support some simple updates of views under these restrictions. So, that is I think completes views what I am going to do now is come back to our quiz. So, I hope all the centers are now ready and please press the s t button and be ready I am going to ask you to answer only quiz question 1 initially do not answer quiz question 2 until I tell you to do so. And now we are starting the first quiz question I hope all of you can see it on your screens this is quiz Q 1 please answer only that with A, B, C or D that timer has started and you have about 50 odd seconds now please answer quiz question 1 time is almost up and now it is up. So, that question is over now let us start on the next question do not answer it yet at this point you do not have to press the s t button because your clickers are still active you can start ok. So, today the numbers have improved though although many centers are still off the number of people who have successfully answered is now 197 out of 300 odd. So, that is definitely good news it is up quite a bit from yesterday. Thank you to everyone who managed to get it to work. Now, the answers which were given this time there are quite a few who have given different answers. So, the question was is r left outer join s and s right outer join r are they the same if you ignore the order. So, note that I have flipped r and s when I went from right to left. So, in the first query both the queries actually will contain the inner join result with the columns of course, order of the columns changed. The first of these two queries will preserve all the tuples in the left input whether they match or not the left input here is r. So, all r rows are preserved whether they match or not the second one will preserve all the rows of its right input which in this case is again r. So, this one also preserves all the rows in the right input which is r. So, they are actually doing the same thing the only difference is the order in which the columns appear in the table will be different, but in all other ways they are going to be identical. So, the answer is yes which happens to be a very slim majority it is not an absolute majority even it is merely the most frequent one. So, those of you who picked no or depends that is not the right answer. Now, let us move on to the quiz question 2 that is the sticky. Now, let us start the quiz just one minute do not answer yet ok. At this point look at your clicker's LEDs it should be blinking down to about 10 seconds left now. I hope all of you have entered your response by now time is up for this particular quiz question. Let us see what are the responses which have come in just a moment, but before we see the responses let us look at the question and see what are the options we have. The question was which of the following gives exactly the same result given these two relations the common attribute is B. So, as we have seen before R natural join S is exactly the same as R join S using B, because B is the only common attribute those two are going to be exactly the same as we have defined. What about the third one R join S on R dot B equal to S dot B. If you recall the natural join as well as the join using remove duplicate columns. Whereas, a join with an on condition preserves the duplicate columns therefore, C is not exactly the same because it has one extra it has two occurrences of B one is R dot B one is S dot B. The values are going to be the same, but the table has one extra column therefore, it is not exactly the same. So, the answer is option A which is A and B are the same, but the others are not. Let us see the results we have a slight improvement in the number of centers responding let us see how many clickers responded 202 slight improvement in the number of clickers responding and this time the audience lost the poll. So, as we just discussed A and C is wrong because C has an extra they are almost the same, but the question was exactly the same. And therefore, option 2 is wrong, option 3 is wrong, option 4 is also wrong, option 1 which is the correct answer actually is the least other than E few people have clicked E by mistake. So, please be a little more attentive in the because I just covered this a few minutes back when I discuss the difference between natural join and the on condition. So, please pay a little more attention. So, at least the majority of you get the right answer. So, now coming back to the third quiz question again press the S T key now read the question I will give you a little bit of time to read the question before you answer it. So, I want to rush you into it the insertion into the view which view the view above history instructors cannot be done by any update to instructor can be done by a simple insert to instructor can be done for any department other than history none of the above. Now, let me clarify the meaning of the question the insertion means the above insertion the one which is right above it is not for any arbitrary insertion the specific one which is inserting 2 5 5 6 6 brown biology 100000 the question is for this insertion. So, the question is this insertion cannot be done can be done can be done for any department other than in history and none of the above. So, please enable it I hope all of you have finished pressing S T now let us start the timer is it started the timer is started now you should be seeing the red LEDs blinking on your clicker then go ahead and press the option from A to D. So, please go ahead and respond you have about 10 seconds left now time is up on this quiz question ok. Now, let us see the responses to this question let me just tell you which centers did not upload anything successfully Anna University did not submit anything Kakinada, Manipal, IMCC, Maulana Azad, IMCC is fine Maulana Azad Bhopal, VNIT Nagpur, NIT Durgapur, NIT Jalandhar, Periyar Mani Ammai, Tanjavur, PSG Coimbatore, RC Patel Shirpur, Taldar Vallabhai, NIT Surat, SGSITS Indore and SOC and IPS Academy Indore all of you did not manage to get anything uploaded. So, please check again let us see the responses this time we had much lower response 126 only I do not know why these are going up and down. So, again the audience loses I hope people are awake maybe we should start the day with coffee because we just saw this particular example and I told you that this particular insertion cannot be done because the query is selecting department name equal to history. So, if you insert biology it is not going to appear in the result of the query it is impossible to do this this particular update. I also told you that if I try to insert somebody in the history department it can be done, but biology department cannot be done. And as I clarified the question was for this particular insertion and the answer is this cannot be done by any update or insert to instructor 2 is certainly wrong it says it can be done by an insert instructor no you can insert in the instructor, but that has no impact on the view the tuple will not show up in the view because the view is going to filter away anything which is other than history. The third one is says it can be done for any department other than history and in fact it is the opposite it can be done only for the history department it cannot be done in general and none of the above is wrong because one is certainly a varied option and the audience choice was option B and that is unfortunately the wrong answer. If I were Amitabh Bachchan I would take a break here before announcing what is the right or the wrong answer. So, maybe this is a good point to take a few questions. I am taking a question from Rajaram Bapu Islampur. Let me enable you. I can see you please go ahead and ask the question over to you. So, my question is can we add constraints using the alter table command? Can we add constraints using alter table? The answer is yes we can we saw alter table earlier we will see more on constraints in just a little bit, but we can do something like alter table add a constraint or alter table drop constraint. There is a standard syntax, but again different databases actually vary a little bit on the specific syntax. So, it can be done for PostgreSQL if you see the PostgreSQL docs it will give you the exact syntax which PostgreSQL supports, but all databases do support changing the constraints. Now, one of the things is if you have created a constraint how do you drop it and what happens is that databases give names to constraints. You can give a name the constraint specification in SQL lets you give a name to a constraint and then you can say drop the constraint with that name. If you do not give a name the database system will give its own name. If you browse the schema you can find out the name of the constraint and then say drop the constraint with that name. So, the way to drop a constraint is by using the name of that constraint which either you gave or it is an internal name. I hope that answered your question. Let us know if you have any follow up questions over to you. Yes sir if you have altered a table using the alter table clause will it affect will I have any effect on the views if you have created the views using the same table. That is a good question. What happens to a view definition if the schema of an underlying table changes? So, first of all if I have a view which uses a particular column let us say B and your alter table drops the column B. Now, that view is in fact syntactically incorrect. Now, what to do about this? Maybe the system can mark that view as invalid now that the schema has changed, but the system definitely needs to see what all views depend on the updated relation and at the least mark them as invalid and if you try to use the view it will say this view is now invalid. That is the minimum that they have to do. The other option is that they detect that some view depends on this relation and then they examine the view and see if it is affected and if it is affected it can say sorry I cannot do this change because the view is affected. So, either you should modify that view so it no longer depends or drop the view and only then do the update. This is roughly equivalent to what happens with a foreign key reference. You cannot update the referencing referenced table if somebody points to you you cannot just drop it. So, again different databases do different things. So, you have to check what your specific database does, but all of them will make sure that you know you cannot just go ahead and use the view quietly if you have changed something underneath which affects the meaning of the view. One way to do it which several databases do is say that you cannot drop a column sorry you just cannot drop it you have to drop the whole table which means the view itself has to be dropped if the table is dropped. Does that answer that question or do you have any follow? Thank you. So, let us see if anybody else has a question at this point. I see Somaya Vidya Vihar. So, let me go to them. Somaya, do you have a question I can see you please go ahead with your question over to you. Good morning sir. Can we create a view without existing base relation? Okay. So, the question is can you create a view without having an underlying relation? The answer is no. In SQL every query must have a relation in its from clause you cannot have an empty from clause. As a result any view has to use an existing table. So, I am not sure if that was the goal of that question maybe you had something else in mind if there was you please clarify if you had some other question back to you. Yes sir. I actually few days back I gone through Oracle help and in which they have clearly mentioned you can create view without existing of base relation but I could not understand how they can create that. I am not sure what Oracle documentation meant. Let us take that offline maybe you can send me email with a link to that and I can look at it and explain what it means. In Oracle you know if you want to write a query which really does not need to reference a relation. For example you want something to print the current time Oracle actually provides dummy relation for it. I mentioned it I think day before Oracle provides a dummy relation called dual which really does not have any data in it. It lets you write queries which do simple if I want to find 5 plus 10 I can say select 5 plus 10 from dual and I will get the answer. So, even for simple things like that Oracle is providing a dummy relation. So, I do not think it makes sense to have a view without any relation. Okay. So, if there is any other question please ask now otherwise I will switch. Last question on this topic from Baramati I am enabling you just one minute. Baramati I can see you over to you. Yes sir. Sir, I have a question. What is the materialized view and how to create it? So, the question was what is the materialized view and how do you create it? So, a materialized view as I briefly mentioned earlier is a view whose contents are actually computed and stored. Why would you do this? Why not just compute it on the fly? Semantically it should not matter whether you do it on the fly or you have pre-computed it. It is a performance issue. So, as an example supposing I have a very large relation and I want to frequently access some aggregate statistics about it. How many customers does the company have? How many how much sales happened in each of the months January, February, March over each of the past three years? There are a lot of queries like this which are needed for analysis which would take a long time to run if the amount of data is large. How large can the data be? If I have a company like let us say Chroma or even bigger Big Bazaar the number of transactions they have per day is enormous. If I want to find an aggregate on such a large number of transactions it is going to take a lot of time. So, for people who are doing data analysis on such large volumes of data they would like to pre-compute a lot of these statistics and store them and this is exactly what a materialized view provides. So, I can define a view which is you know group by maybe year month some of sales or maybe group by year month branch the specific branch of that shop some of sales and so forth. I can create many different views and then pre-compute them and store them. So, that is a materialized view. Now, if I do this by doing a create table as select you know query then that is going to be computed once and stored and that is it it is not going to be updated. On the other hand if I declare it as a materialized view which the system is going to maintain the system will take care of updating this either immediately when the underlying data changes or you can set up the policy for maintenance. For example, you can say maintain it once a day. So, the summary sales will be correct up to yesterday today new sales are coming in and tonight the view is again updated to include today's sales. So, the view is up to date up to one day it is not completely up to date that is one policy. Another policy is as soon as a sale happens and record comes into the database immediately update the aggregate. Now, for data analysis that is probably not essential, but for certain other applications of materialized views you do want it to be completely up to date. So, databases do support an immediate maintenance policy also and there are some issues in how to do this efficiently. So, for the immediate maintenance at least most databases have some restrictions on what are the kinds of views you can declare. For example, many databases will not support outer joints they may not support some other constructs and they may have some other restrictions, but within these restrictions you can say that a view is materialized and it will be maintained. Now, you are using PostgreSQL which happens not to have any support for materialized views internally. People have built stuff on top using triggers, but it is not intrinsic to PostgreSQL, but Oracle SQL Server DB2 all have some form of materialized view. The second question is what is the syntax for saying that a view should be materialized. Now, unfortunately the SQL standard did not address this. So, each database has its own way of specifying this. So, for example, I think Oracle do not remember the exact syntax, but one of the databases can say create materialized view or you can create a view and then say materialize it. In SQL Server you can create a view and then say index the view. SQL Server takes this as a hint that if I want to create an index on the view I can only do that if I materialize it in the first place. So, the syntax varies by database. So, I am going to leave it at that. It is not standard in SQL. So, that was a useful question and back to you if you have any follow up question. Yes, considering the view is created. For example, for the instructor table the view is for name and building or name and the department name. I am not considering ID in the view and I want to update a view by using insert statement as the attribute which has not considered in the view are inserted with the null values, but the problem is ID cannot be null. So, how it is handled in the different database systems. Okay, that is a good question. What happens to a view update if the attribute which is you know projected away excuse me happens to be a primary key attribute and in fact the conditions which I had listed to allow a view update said that any column which is removed from the view and has to be created a value has to be created you know automatically should be either nullable or should have a default value. So, for the case of ID it is a primary key I cannot set it to null. It does not have a default value. Therefore, this particular update will not be supported that if you try to update a view like this the database system will say sorry I cannot allow updates on this particular view. I cannot allow inserts on this particular view. So, that is how it happens handle each database has slightly different internal implementations. So, what is allowed to be updated what is not is defined in the SQL standard, but the definition is very complex as a result there is some variation between different databases. In general what I would say is you should not be updating using views. There are a few limited cases where it is required, but by and large you really do not need to do it and it can cause trouble. So, avoid it if possible that is the general model, but databases do support it because there are a few applications which need it that does not mean you should be using it unless there is a good reason to do so. Back to you if you have any follow up question. Thank you.