So, the next problem is that sometimes you cannot translate updates uniquely. So, this is a particular problem which joins you. So, here is a view instructor info which joins instructor with department. So, what is the point of joining this? Because, you want not only the ID and the name of the instructor, you want to know which department they are in. Again, in this view of the world instructors are in one department, departments are in one building. May not be realistic, but we are keeping our life simple and it is often useful to show the department sorry the building in which a person is working along with other information about the person. So, this view can be pretty useful. So, we have instructor info. Now, supposing we insert into instructor info the same kind of values ID, name and this time there is no department. We had ID name in building. So, we added a building Taylor. Taylor is a name of a building and white is a name of the instructor. So, this is what we wanted to insert into this view instructor info, but how do we translate this into inserts on the underlying relations? What if there are multiple departments in the Taylor building? What can we do? Supposing the music department is a Taylor building, but also the history department. We can insert this instructor in the history department and then this update will happen successfully, but the music department is also in Taylor. So, we could have even made this instructor in the music department. Note that the view does not have the department name. So, we can cook up the department name, but there are two problems. Say how do you cook up a name? So, in this case there are many ways of cooking up a name or department and we do not know how to choose between them. Even if it is unique does it make any semantic sense to just cook up a department? So, that is not good. So, in general you do not want to allow this. In fact, it can become worse if there is no department in the Taylor building. Then not only do you have to create a department name for this new instructor, we also have to create a new department and put it in the Taylor building. All this is totally arbitrary.  So, obviously you cannot do this and so most SQL implementations will only allow updates on simple views which are on a single relation no joint as you can see joints cause immediate problem. The select clause only contains attribute name does not have expressions, aggregates, distinct and so on all these cause problems. Any attribute not listed in the select clause can be set to null or it can have a default value. The query should not have group by or having clause these also cause trouble. So, very simple queries, very simple view definitions rather are the only things which SQL implementations will allow updates on. And in fact there is another issue related. So, take this query create view history instructors as select star from instructor where department name is history. So, this view contains only history instructor, but note that the view in this case does have a department name, but so supposing we do the following. We insert some ID name department biology salvi 100000 into history instructors. Is this possible? This is a selection on instructor. So, we could actually insert this same tuple directly into instructor not a problem, but then the view requires department to be history. So, if you check the tuple it does not satisfy department name history. So, there is no way to insert this tuple into this particular view. It is impossible. So, this can lead to trouble and in fact what happened is that this particular view definition actually satisfies the conditions that we have here. It does it is a single relation. It has only attribute names no expression. All the attributes are specified in the select clause. There is no grouping of having clause. Everything here is satisfied, but still this particular insert makes no sense on this particular view because it cannot appear in the view result. So, the SQL standards people realize this and then they created more complex rules. They went crazy on this and they said that you can specify a view definition as updateable with checks of this sort, which will check that if there is a selection condition the inserted tuple matches the selection condition. If not it is not inserted, but they also allow you to turn off the check, which means it will if you have the check off this will be allowed and it will result in an insert into the instructor relation, which is rather idiotic. So, anyway SQL lets you do many things. I do not know if implementation actually support all this, but the standard gives all kinds of features. So, here is a quiz question. Many people chose option B, which says can be done by a simple inserted instructor. So, let me retread. Yes, you can insert into instructor, but the insert into the view will not succeed in the sense that that tuple, which you inserted in the view will not appear in the view result. If you look at the contents of the view after the insert, the tuple is still not there. So, you are not successfully inserted into the view. Yes, you can insert into the instructor relation, but it does not succeed in logically inserting it into the view. So, the correct answer is it cannot be done, but I can see why many people chose option B. It can be misleading in the sense that yes, you can do an update to instructor, but no, that does not give you the effect that you wanted, which is to insert this person into history instructor. So, let us move forward. There are a few more topics. I will cover a few of these before the break. The first is the notion of transaction, which is a unit of work. We are going to look at this in a lot more detail in the database internals part, but at this point I just want to deal with it from an external view point. A transaction is just a unit of work and an atomic transaction either executes fully or is whatever it did is undone and the net result is as of it never executed. So, why is this relevant? Because the transaction may perform multiple updates and sometimes if it does one of the updates, but not another, the final state of the database will be inconsistent. So, you want either all the updates should occur or none of the updates should occur. So, how do you specify this in SQL? That is all that we are going to look at at this point. There are other issues of isolation from concurrent transactions and so on. We will look at it in more detail, but in the SQL standard, the transactions begin implicitly and are rendered by executing a commit work or rollback work. That is what the standard says. In reality, nobody follows the standard as such. What happens by default is that every single SQL statement, which you execute, executes fully and commits. Now, supposing an SQL statement had to insert 5 tuples, what is guaranteed is that it would not fail in the middle, leaving 2 tuples inserted and 3 tuples not inserted. That will not happen. Either that whole insert statement will succeed or any partial update it did in case there was a failure will be cleaned up and it will be as if it never executed. So, the default setup in most databases is a single SQL query is a transaction by itself, but multiple SQL queries are each separate transactions on their own. So, if you write 2 SQL updates, insert into R and then insert into S. The database may fail after the insert into R and before the insert into S and then if you recover the database after a failure, you will find the insert to R, but not of S. So, your original goal which was to update both R and S has failed and the database may be inconsistent as far as you are concerned. So, what you do about it? You can turn off auto commit using the database connection. There is an option for it and in SQL 99, you can use syntax called begin atomic end, which unfortunately nobody supports, but there are small variants of this which post SQL does support. We will see it later on. Then the few slides on integrity constraints. We already saw not null primary. There are couple more, unique and check, which I will cover quickly before the t break. So, unique simply says that a set of attributes are unique. That is no two tuples can have the same value for these, for all of these attributes. In other words, actually this thing says that the unique specification states that attributes form a candidate key, but in reality again this is what SQL standard says, but in reality there is no way for SQL to check if it is minimal. So, more realistically the unique specification simply says it is a super key, but there is one difference between unique and primary key and that is in primary key values cannot be null. Whereas, in a set of attributes declared as unique, the values can be null and SQL allows this. So, there are some several situations where you want to use unique without declaring it as a primary key. And the check constraint, this is a very big slide. Ignore everything down here, the definition of the table section. Ignore everything and come right to the bottom, which says check semester in fall, winter, spring, summer. What is this check clause doing? Whenever you insert or update the section table, it will check that the value for semester is one of these four strings, fall, winter, spring or summer. And if it is not one of these integrity constraint, violation will be raised and your insert or update statement will be rejected, rolled back. So, why is this useful? Well, in this case the motivation is obvious. What is less obvious is, why did we use check instead of using a alternative of foreign key? So, for this case we could have had a semester master, which lists the possible semesters, fall, winter, spring and summer are the four tuples in a semester master table. And here we could have said create table section and semester or wire care six foreign key referencing semester master. That actually would have been in some sense cleaner, because now I can add a new semester, if I want to introduce semester. In fact, this happened in IIT, because our MTech projects were decided to not actually coincide with any semester, regular semester, they created a new semester just for MTech projects. Nothing else would happen in those semesters, those semesters overlapped existing semester, etcetera, etcetera. So, if that had to happen, we would have had to with this approach, we would have had to go and insert new values into this list here. But in our schema, we simply added a new tuple to the semester master and that is it, nothing more needed to be done. So, that alternative is actually better in some sense, but we kept it simple, because we did not want more and more relation names in our schema. And the last topic before the tea break is going to be cascading actions in referential integrity. A few people had been asking me about this already. We already saw foreign key references. In this case, course has a foreign key department name referencing department. Now, here is a small variant of this definition. First of all, we said department name wire care 20, references department. We could take this specification and move it separately and say department name wire care 20 and then we can say foreign key department name references department. That is so far, it is exactly the same thing. This is a slightly different syntax. But now, there is a little bit more. What we have said is on delete cascade, on update cascade. What is this? Supposing a department is deleted, the on delete cascade does what? It deletes all the courses associated with the department. Now, this is actually a very crazy thing to do. You do not really want to go delete all the courses in the department. You lose a lot of historical information. But if this is what you want to do, well SQL allows it. There are other cases where this is a very meaningful thing. If I delete an order, for example, I want to delete all the line items in order. So, in order to understand this, you need a schema. So, let me use the whiteboard and explain that. We have a schema order, order number and then customer number a bunch of stuff there. Now, a particular order may have many items inside it. When I order something from the grocery shop, I give a list of items. So, I am going to have an order item, which will have order number, line number, item and other things. Maybe how many things I want, how many packets, how many kgs, whatever, other stuff can be in there. Now, if you see, order number in order item is really a foreign key referencing order.  If I delete the order, I need to delete all the corresponding tuples from order item, which reference that order. Otherwise, I have order items with no matching order. It makes no sense to retain them. So, in this case, it makes a lot of sense to use this clause, which we just saw. And that clause was on delete cascade. It will go delete it. There is also an on update cascade. Supposing I decide to change the department name, this has happened. In IIT Bombay, I think a metallurgy department changed at some point to metallurgical engineering and material science. So, maybe we want to change the department name, and then everything that references the department name should get updated. Actually, I am lying a little bit. In our schema, we do not use the department name as a primary key. There is a department code, because names are very long. So, we actually did not have to update anything. But if we had used department name as a primary key, then on update cascade would have been used. And there are other alternatives on update set null, on delete set null. So, if you want to delete the department, but keep the code, you could say on delete set null and so forth. So, I think I will stop here and maybe take one or two questions and then wrap up the last few parts of chapter 4 after the break. So, I will take a few questions now. L R D P Gandhi Nagar, do you have a question? How can we drop foreign key? How can we drop foreign key when we have not mentioned the constraint name? How can you drop a foreign key? I do not want to drop the column. Yes, I do not want to drop the column, but I want to drop foreign key. So, the question is, how do you drop a foreign key? And the SQL syntax lets you drop a foreign key by giving the name of the foreign key. Now, the SQL syntax also allows you to name all constraints. You can give a name to foreign key, primary key and other such constraints. So, if you give it a name, it is easy to go and say drop the constraint with that name. If you did not give it a name, what do you do? The database all automatically gives it a name. So, if you go browse the schema and using any tool for browsing the schema, you can find the constraints and you will find the names of those constraints. So, you can then go and say drop the constraint using that name which you just found. So, when you use PG admin, you will be able to browse the schema and go try it out. You can also look at the relation names, the attributes, the integrity constraints and so on. And even if you did not give it a name, the system will automatically give it a name and you can see the name and you browse it. Does that answer your question? Yes, sir. Second question is there, sir. Yes. Sir, is there any practical application of updatable view? Is there a practical application of updatable view? Yes. Because I have. So, some of the views which I showed which filter away information might still allow updates because maybe you want somebody to be able to update let us say your address, but not see your salary. So, maybe we will give a view to whoever is allowed to change your address and that view does not include your salary, but they are allowed to update the view and change your address in there. So, there are situations where this is useful, but I should mention that the use of views for security when the SQL standard was defined there was lot of motivation for this, but there are issues with the way SQL databases are used these days and I am going to come back to it at the end of chapter 4. So, I will discuss this just after the break. Any other question? Good morning, sir. Sir, this is related to the agent-oriented paradigm. Do we have any tool with the help of it that we can implement this paradigm into the database system, sir? Which paradigm? Agent-oriented paradigm, like object-relation... agent-oriented... I am not sure what you mean by agent-oriented... Is such kind of database systems are available? I am not aware of it. Object-relational mapping is there? Okay, sir. Thank you. Object-relational mapping is standard nowadays, but if you want to implement it on your database, you just use a tool, object-relational mapping, let us say hibernate. You can download hibernate and it sits outside of the database, it is a separate tool and you can use it to create a mapping first. You have to define the mapping and then you can write programs which are linked to the hibernate library and you have to give the database connection information to the hibernate configuration file and then when you run your program, it calls the hibernate library and that talks to the database. So, using ORMs is not very hard, can be done completely outside of the database. If people are interested, I can make information available about how to use hibernate as part of the Moodle site. I think there was some other question which I did not quite follow, but does this answer your question? Yes, but I have one more curiosity. Object-relational mapping can accelerate the moving of the data in the database automation. We can automate the data with the help of this, but another question is coming in my mind in terms of research on this. I am not sure what you mean by speed up data or whatever else you use for that. So, the point of object-relational mapping system is to make it easier to write application program and to make your application database independent. These are the two motivating factors. The first motivating factor which is to make it easier to write applications was why they were created in the first place. The second factor which is database independence kind of happened and today it is one of the motivating factors. So, that is the reason why they are there. Efficiency of execution was not one of the factors which led to their creation, but in the end are they more efficient than writing SQL? I think the jury is out on it. There are some issues with parallelization and so on. Maybe something may be faster in using an ORM because it does caching whereas a database does not do caching by default. On the other hand, something may be faster not using ORM directly using the database. So, I will just leave it at that and maybe I will take question from one other center. We are seeing MGM College of Engineering, Nanded. MGM, please go ahead. Hello, my question is regarding object-relational mapping again. So, can we use Hibernate query language instead of X query to map the objects of XML database? So, this is a question specific to Hibernate. So, Hibernate gives you this mapping and hides the SQL schema from the programmer. So, now if you want to write a query, what you do? You could write a query by writing nested loops and iterating over objects, but that is horribly inefficient. So, the Hibernate designers developed a query language which uses the class model which is exposed to the Hibernate programmer hiding the details of the underlying SQL implementation. So, that is something which people do use. Now, the other part of the question is research on this. So, there are certainly issues in how to implement the Hibernate query language efficiently. The problem is you can write things easily which you may then have to translate to SQL and make sure the underlying database can execute the SQL query reasonably efficiently. So, those are issues in implementing the Hibernate query language. So, it could be an interesting topic for research. I have not looked into it in too much detail, but yes, if you want to look at it by all means, how to do the translation could be and how to improve the thing. Obviously, they already have an implementation. The research angle would be how to make it better. So, the last few topics from chapter 4 after this we will move to chapter 5 are on integrity constraint violations, authorization and so on. So, we already looked at integrity constraints. Now, here is an interesting situation slightly artificial, but it illustrates a potential problem. Supposing we have a table person and as attributes in that table we have mother and father which are ideas of persons for the mother and the father. So, what happens here? We have a foreign key father represents person, foreign key mother represents person slightly artificial, but you can imagine an employee situation where you have a manager which is a foreign key referencing the person table itself. So, it is a very similar situation. So, now how do we insert a person tuple without causing an integrity constraint violation. So, the way to do it in this case will be to insert people in the order of their ancestry. So, first you before you insert a person you must insert their father or mother if you plan to set it. Now, both of these are foreign keys which can be set to null. So, another option is to initially insert tuples with father and mother set to null insert all the tuples that you want in the database and then for those tuples which do have a valid father or mother value you go back and update the tuples to set the values appropriately. Otherwise what will happen is if you do things out of order a person may have a father defined, but it is just that the father tuple was not inserted yet when you inserted this person tuple. So, if you sort the tuples you can do it correctly or you set null initially and then insert all the tuples and then go back and patch all the tuples to set it correctly. But, there is a third option which is useful in some context in particular when you have cyclic references neither of these sorry when you have cyclic references where you cannot set things to null neither of these works. So, what do I mean by cyclic reference? Let us say we have a spouse relation spouse could be an ID and two people are married to each other. Supposing I say that spouse cannot be null we only allowed married people in this club. So, spouse has to be present we only allow couples in here. Therefore, for both people there must be a spouse value which is a foreign key referencing the person table and we say that it cannot be null because we are insisting that the spouse be present again it is artificial, but it just illustrates the point. So, now what happens if I want to insert the husband I cannot do it because the wife is not yet inserted in the table and that spouse value will not be present in the person table. If I try to insert the wife first the husband's tuple is not yet inserted in the table. Therefore, the spouse value which is the husband's ID will fail the foreign key reference. So, what whichever order I try to do it it will fail and furthermore if I say that the value cannot be null then I mean big trouble neither of these two solutions works. So, SQL actually has a third solution which is to defer integrity constraint checking which is. So, you can say set constraints and give the name of the constraints and make it deferred which means it will not be checked at this point, but they are supposed to be checked at the end of a transaction. So, the idea is we start a transaction we insert multiple tuples with the father, mother or spouse attribute set as we want, but the foreign key constraint is not checked at immediately it is checked at the time you commit the transaction. So, initially I insert a husband tuple the spouse value is not yet present in the database, but it is not checked yet then I go ahead and insert the wife tuple and now the spouse attribute of the husband is correct and the spouse attribute of the wife is also correct because husband is already there. So, by postponing checking the constraint it has become satisfied later and the point at which it is supposed to be checked is at the end of a transaction. So, that is what this slide is about the spouse constraint is in here. So, immediate means as soon as the update is made deferred means at the time the transaction comes. There are also options provided by most databases to turn off constraint checking for a while regardless of transactions you do a bunch of stuff and then go back and we impose the constraint and it is checked at that time.  So, at this point the best that can be done is to check which tuple and run a separate transaction to clean up the relation and then reinforce the to enforce the integrity constraints again. So, deferred means end of transaction deactivate would be to turn it off for the near future. Now, there is another issue with check losses we saw that time slot in the section relation ideally ought to be a foreign key referencing the time slot relation. But, it cannot because the time slot relation does not has a primary key if you recall which has time slot ID day and start time the three attributes together from the primary key of the time slot relation. But, in the section relation there is a time slot value which ideally ought to be a foreign key. But, it cannot because in SQL a foreign key can only reference a primary key or anything which is unique and time slot is not unique in the time slot relation. So, it will be nice if we can create a check like this in the section relation we have a check which is check time slot ID in select time slot ID from time slot. So, how should this check work it is actually not supported by any database system that I am aware of currently although it is technically part of the SQL standard. So, how should it work if it works. So, first of all whenever I insert a couple into the section relation it should check that the time slot ID is present in this sub query. If I update the time slot ID it should check it. But, here is the hard part if I update the time slot relation and delete a time slot then I will have to go back and check all the referencing tuples to see if that time slot ID was used. Because, if it was then that tuple is now violating the integrity constraint that turns out to be expensive and that is the reason no database supports this. So, it is unfortunately not supported there are workarounds using triggers where you have to code a whole bunch of checks. So, whatever checks I just told you when I insert into section check this when I update section check it when I delete from time slot check it all of these checks can be done through triggers and we will look at triggers in a little bit. Finally, the SQL standard had something called assertion which was even more general, but nobody supports it. So, we will not talk about it. Now, here are a few time related types in SQL. We had date, time, time stamp each of these has its purpose. Date is when you do not want a time within the date it is just a date. Time is when you want a time within the date, but not the date. A time stamp which is a combination date plus time of date and the last one in this list is interval which is a time interval that is it is not saying that the time interval is between this time and that time. The time interval says 1 hour or 35 days or whatever. Now, the obvious application of this is in let us say fixed deposits in banks. So, the fixed deposit interest depends on the time interval it says 41 days is so much percentage. So, what you would do is when you create a deposit if you say this is the interval you want to represent that in the database as an interval 41 days and then add the 41 day interval to today to get the expiry date of the fixed deposit. So, that is where the interval comes. Note that 41 days cannot be represent as a date because the date is a particular date. Today is a date. Today plus 41 days is a date, but 41 days gap is an interval. Of course, interval could be at units of a day or even more fine grain hour, minute, second, millisecond whatever you want. So, here is a small quiz question that is an expression. So, the question is date plus date some value minus date some other value. By the way this is the SQL syntax for saying that something is a date. How do you specify date value? You have the keyword date followed by a string containing the date and the default is year, year, four characters for year hyphen, two characters for month hyphen, two characters for date. So, what does this expression do? Date minus another date. What is the type of this result when you subtract one date from another? It is an interval. We just discussed the interval type. Now, what is the result of adding an interval to a date? It is a date. So, the result of this is a date and it is valid. On the other hand supposing I did the following. Instead of minus here I put plus. Would it be valid? What sense does it make to add one date to another? It is completely meaningless. That would be a type error and the system would reject it if I said plus. So, the correct answer was A which is one which is valid and returns a date. So, the next very small topic is on index creation. What is an index? You use indices at the back of a book which given a word you can find out which page that word is defined or used in. An index for a database is given a value you can find which tuple has that value in a specified attribute. So, an example of a query select star from student where id equal to 1, 2, 3, 4, 5. It is a very common query. We look up student by id number. If you have to scan the whole student relation and look at every tuple to check if id is equal to this it will take forever. So, what we want is an index which lets us very quickly find which tuple has value 1, 2, 3, 4, 5. Now, the SQL standard does not talk much about it, but all databases do have syntax which looks more or less like this. Create index you can give it a name on relation name and then list of attributes. And usually you can give more options after this to say what is the type of the indexes and other parameters to it which are database specific. So, we will come back to indices when we cover internals. So, for the moment this is all you need to know about indices. There are a few more types large object types to show things like photos and text which is larger than. So, most databases will allow you wear care up to some limit few thousand kilobytes beyond that they will not allow wear care, but they will allow C log object which can be much larger. Then you can have B log for image. Movie B log 2 gigabytes is actually very optimistic in the following sense. Most databases prefer not to have very large object inside of the database and this is for some practical reasons. One of the reasons is that database dump and restore becomes very slow if you have too much or too very large images inside it. And typically things like movies really do not need database features why on earth would you want recovery and concurrency control on a movie makes no sense. So, the typical way in which this is handled is to store movies or large images in a file system not in a database. And in the database you store file name so that you can access that movie or the image from your database application. So, you store the file name, but you do not have to store the actual data in the database. In fact, some database systems which are integrated with the OS can actually prevent you from deleting the file you know without the database knowing and vice versa. Prevent you from creating a file name without the file existing. So, there are database file system integration systems which some databases provide. The last topic in this chapter is authorization. I have been hinting at this. So, authorization gives privileges to users and the grant statement is used for this. The general syntax is grant a list of privileges on relation of view name to user list. Where the user list can be a user ID or it could be public which means everybody or there is something called a role will come to it in just a moment. Now, it is worth noting that granting a privilege on a view like if I grant read on a view that does not mean I am granting read on the underlying relation. This is important for security. I said that we create a view and make the view accessible to somebody, but not the original relation. So, granting read on a view does not imply granting read on the underlying relation. And the person who does the grant somebody has to execute this statement typically it could be the owner of that item. The person who created the item or the database administrator, but in more generally you must have the privilege that you are granting and the right to grant. I will come back to it. So, what are the privileges? There is a select privilege which allows you to read. For example, grant select on instructor to these three users u 1, u 2 and u 3. They are some user IDs. Insert the ability to insert tuples, update the ability to update a tuple, delete similarly the ability to delete and all privileges means anything. Select insert update delete. Now, you can also revoke a privilege which was granted. So, revoke privilege list on relation of you name from user list. This is generally syntax. So, this says revoke select on branch. Well, branch is from a role schema. This is a bug. Think of this as the on the previous slide I think we had instructor. So, revoke select on instructor from these three guys. You can also say revoke all from this list of guys which means we are revoking all privileges from these guys. So, revoke all on branch on instructor from these guys and if you say revoke something from public what happens? Supposing I have granted some privileges to public and then I also granted specifically to a user. This user has this privilege in two ways. One because this user is also part of public. Public is everyone and two because it was granted explicitly to them. Now, supposing I revoke the same privilege from public. In the SQL definition, these users who are explicitly granted the privilege will continue to retain them. Revoke from public does not mean revoke from these guys also. Even though they get it twice, you are revoking one of which is the public. The other one is still there. It is not been deleted. So, they can still keep a privilege. In fact, there are some other complications that I do not want to get into. Now, among the interesting privileges, we saw a bunch, but there is one more kind of privilege which is called the reference privilege. The reference privilege gives you the ability to create a foreign key referencing a relation. Now, why is this relevant? So, here is an example. Grant reference on a reference and then the attribute name, department name on department to some user. What is the need for this? This allows the user to create a table with a foreign key referencing department name of department. So, if I just say foreign key referencing department, assuming department name is a primary key, this is default. But, if you had declared this unique, then I can actually say foreign key references department open parenthesis department name. So, if you do not want it referencing the primary key, you want it referencing some other attribute, you can do that explicitly as long as it is been declared as unique. So, anyway coming back, unless a user has this privilege, they cannot create a foreign key referencing department. Now, why is that? That is because the moment this user has created a foreign key, the person who owns the department table can no longer delete a department arbitrarily. Supposing this guy Mariano creates a table referencing the CS department. Now, I own the department table and I want to delete the CS department. I cannot because there is a another table which has a reference to a CS department and deleting CS will violate the foreign key constraint there. So, if I allow anyone to arbitrarily create references, I am in trouble. So, I need to grant a privilege to someone only then they can create a foreign key referencing. Now, you do not see this much because in your assignments, you just have one user who is creating the tables, all the tables owned by that user. So, you already have all privileges on all your tables, but if you access some other users tables, then these things are important. You can also do various other things. You can grant a privilege to somebody with grant option which means they in turn can grant that privilege to somebody else. You can rework a privilege from this guy cascade. What this cascade means is, if you had granted it to Amit with grant option, Amit in turn had granted select on department to somebody else. When you say rework cascade not only is the privilege withdrawn from Amit, but it is also withdrawn from anyone that Amit granted it to. So, recursively it is withdrawn. On the other hand, if you say restrict, if Amit had granted it to somebody, then this rework will actually fail and you have to first make Amit withdraw the grants and then you can rework it from Amit. Anyway, these are minor details. With that we end chapter 4. This is a good time to take a few questions. Mark Basiliyos, Kerala, please go ahead and ask your question. Sir, my question is regarding on views. So, can we create an index on view? Can we create an index of view? So, first of all, if it is a normal view, it has no data. So, you cannot index a view which does not have any data. However, if you are using SQL server, SQL server takes this thing. If you say create index on a view, SQL server takes that to mean that you want the view result to be materialized. That is, you compute and store the result of the view and build the index on it and then subsequently SQL server will keep the view up to date. Whenever you update a relation which is used in the view, the view result which it has computed will get updated immediately. So, creating an index on a view is a SQL server feature. I do not know if others allow it and if so what they do. Does that answer your question? So, one more question is there. Sir, can we do the operations like join on views? Like join on views? Yes. Views can be used in a query anywhere you want. You can do joins, you can do aggregates, you can do anything to it in any place where a relation could appear. In a query, you can use a view, no restrictions at all except for update. In a query which does not do any updates, you can use a view anywhere. Any other question? Thank you, sir. Sonar college Salem, please go ahead. Sir, we have a doubt from yesterday's class. You wrote a query using join yesterday. What is the difference between this natural join and join and in which case should we go for natural join instead of join? So, join and natural join are essentially the same thing except that you know you can always take a natural join and rewrite it using a regular join. Whatever columns are common, so let me use the whiteboard to explain that. Many people have this question. So, I think it is worth explaining. So, let us say I have a select, I will do it in the context of SQL something star from R natural join. So, let us take R to be of with a schema A B and S to have the schema B C for simplicity. So, R natural join S could equally be written as select and the star I will fill in in just a moment from R, S where R dot B equal to S dot B. Now, why did I leave these select things columns empty? I am going to fill it in now R dot A, R dot B. Now, I am not going to include S dot B because it is to make something which is equivalent to the natural join. In the natural join, the schema output is A B C, B does not appear twice. So, I will have to use S dot C, so that it has the same schema A B C. These two are exactly the same. So, a natural join can always be rewritten using an ordinary join. The question is when should you use one versus the other? It is a convenient. So, in some of the queries which we had, we had foreign keys and primary keys with many attributes. So, writing out long join condition equating every one of them is kind of tiresome. So, using a natural join or using a join using equivalently, this is actually equivalent to. I hope you saw the white board and got an idea of the different ways of writing between which are all equal. Now, how do you choose between these? It is a matter of taste and the natural join or the using is easier if you have a join on many attributes. So, for our university schema, we have to sometimes join on four different attributes. Section has the section ID, course ID, years and so there join using is very convenient rather than the other form, but it depends on your application and on your taste. Thank you, sir. Let us move to another center now. We have with Loyola Chennai. Please go ahead, Loyola. First of all, to introduction myself, I am Bob working as a senior lecturer in Sampita University. So, I am asking in three questions. The first one, does the SQL view exist if the table is dropped from the database first question? Second question, can you update the data using the view? It will be updating to the original table, the second question and third question, what is art and data? Let us start with the first thing that was, can you have a view and then drop the table which is used in the view definition? Now, obviously if you use this view subsequently, it is going to give you an error saying that the table is not available. Now, is this checked at the time you drop the table? I do not think so. Most databases I think do not actually check it immediately, but when the view gets used, at that point error is detected that the table no longer exists. So, that is the answer to your first question. The second question was, can we update the data using views? So, for that the answer is something which I discussed in a lot of detail earlier today, which is certain updates on views can be translated, others cannot and most SQL systems will restrict updates to views. It will allow it only on views which are very simple. They should be select views with few more conditions which I had done a slide. So, you can go back and look at the slides for this chapter and it gives the typical kind of conditions under which a view update is allowed by many database implementations. Some may not allow it on any views, others may allow it on a slightly bigger class of views, but what I have given you is fairly typical. And the last part is orphan data. In general orphan data would mean like master you know data like the order and line item which I used in example earlier today that I have an orders relation. Each order has multiple lines in that order. So, that is in a line item table. So, if there is a record in the line item table with an order number which does not exist in the order table, it is meaningless without the order. So, that would be orphan data and the foreign key constraint with ensures that the orphan data cannot happen and the on delete cascade ensures that you can actually delete the order without explicitly going and deleting the individual items. Does I answer your question? Thank you sir. Actually Narayana Gurukulam college, Kerala please go ahead. Sir, we have one question regarding view. So, the one question is sir can we create a new view from an already established view? Yes. So, in the view examples I showed you can define one view in using another view relation. So, you can create a whole hierarchy of these view A can be defined using view B, view B can be defined using view C and so on. That is absolutely fine. There is no restriction on that. Does that answer your question? Thank you sir. So, one more question. So, one different question regarding the query. So, while we are using count distinct on a particular attribute. Sir, is there any relationship, any difference between using in oracle and post SQL? On count distinct. No, the count distinct is part of the SQL standard and as far as I know oracle and post SQL implement it in the same way. So, I do not think there should be any difference on this particular aspect. There are other differences, but this particular thing I do not think there is any difference. So, one more thing. So, while we are using describe a particular table, while we are using it in post SQL, it is not running any result. Like they are showing that it is an error like that. Be a SQL that particular keyword is not working in post SQL. So, dac is describe is an oracle specific thing. It is not part of the SQL standard. Post SQL has an equivalent command slash d. If you run it from the psql, command prompt, but if you want to use, what I would suggest is most of the time, you might as well use GUI, pgadmin or some other GUI oracle has its own SQL developer. There are other GUIs stored for oracle and so on. So, if you are using those, it is actually easier to just browse the schema, double click on the relation name and get the information that you want without having to type this. So, if you use pgadmin, you can do that. If you are using psql, instead of describe, you can say backslash d and then table name, it will show you the schema of that table. Does that answer your question? Yes. Thank you, sir. Tehrna, please go ahead. Hello, sir. I had a question. This is regarding the earlier discussion on ORM. You had mentioned, sir, that in hibernate that we can have the queries we are not having access to. Like it is the queries are created by hibernate and they are fired directly by hibernate. But in an application, if you are facing some problem, is there a way of tweaking the queries, can we access the queries which hibernate is directly firing or is there a way of tweaking those queries for testing? So, what hibernate provides is a way to specify a mapping between your Java class and the underlying relational database. You can provide this mapping in a configuration file and after that hibernate generates this, you cannot tweak it without going and changing the hibernate code as far as I know. So, the mapping is described thus. So, you can have different relational schemas which can be mapped to the same Java classes, for example. So, as an example of the kinds of things you can do, in the Java side you can have, let us say, a student object and along with the student object you can have a collection of courses that the student has done. So, that is a collection of this. Now, in the relational database it is actually been normalized to the student relation and the takes relation. So, it is possible to specify the mapping such that when I create the student object, the SQL query also goes to the takes relation and fills in the collection which is accessible in hibernate. So, you can control some of these things and then hibernate will fetch the relevant, we will issue an SQL query to fetch the relevant data and populate the collection. Thank you, sir. Thank you, sir. There is one more question, sir. This is regarding views. So, where is the view exactly stored in the database? Like how it is different from tables? Where is it stored, sir? So, tables including views have two parts. There is a metadata about the table which includes things like, you know, what are the columns or the attributes of the table, what are the types of the attributes, what are the constraints on the table and so on. All of this is stored as part of what is called the data dictionary. So, this is all the, it is also called the metadata. Let me use the white board. So, these are two terms which are relevant here. The data dictionary is actually it is a set of relations in the database which keep track of what all tables there are, what are their attribute names, types and constraints and so forth. All of this data is also called metadata because it is data about data. So, now when you create a view, the name of the view, the types, the attributes and the types of the attributes of the view and the actual SQL query defining the view are all stored in the data dictionary. So, there is actually a table in the data dictionary which records what all views there are and what are their definitions and the types of their attributes. Just as there are also tables in the data dictionary which record what are all the tables in the database and what are their attribute types and constraints and so forth. So, all of this is part of the data dictionary. Thank you, sir. Good. So, now let me take a few minutes to cover some queries which I wanted to cover as part of chapter 4, but didn't include in this slide. So, yesterday we saw a query which was to find for each department, get the department name and the number of or this let us say the number of instructors in that department. So, one way to write the query which we saw was select department name count star from instructor by department name. So, this is one query we saw and we saw that there is a problem with this query in case there is a department with no instructor because it will not appear in the output. So, what do we do about it? One of the alternatives we saw was to use a sub query what did we do? We said select department name comma and then there is a sub query in the select clause select count star from instructor where let us give it let us call this instructor I where I dot department name equal to and I am going to have d over here from department. So, coming back here where I dot department name equal to d dot department name. So, this query which had a scalar sub query in the select clause will give account 0 for every department which has no instructors at all. So, now this is one way of writing it there is actually one more way of writing it using outer joint. So, goal is to do the following. So, I will come back to the select state thing from department left outer joint instructor on again I will just give it as department D instructor I where on d dot depth name equal to I dot department name. So, this is the left outer joint which is going to preserve departments even if they have no instructor. Now, I can do the following I can say group by department name and here select department name comma count now here I have to do something little different. So, I am going to say count of id this id is coming from the instructor. So, what is this variant of the query do the outer joint is going to have some departments which do not have any instructors. The outer joint result will have the department present, but the id attribute in this outer joint will be null for such departments. There will be only one tuple with the instructor id attribute being null. Whereas, for departments which do actually have instructors the department will appear will join with each such instructor and there will be many roles here. Now, when I do group by department name departments with no instructors will also show up with id being null. So, now count of id what does it do the id is null in this case there is only one tuple in the group for that department with id null and as I said for aggregates the null values are discarded. So, what do we get we get an empty set and count of an empty set as a set is special it gives 0. So, you will get that department name with count 0. So, this is alternative way of writing that query using outer joint. So, outer joint can be quite useful in such situations to make sure that you do not lose certain fields. So, it is not only useful for regular joint it is also useful for aggregates to make sure that you do not lose data you can always use scalar sub query, but this is an alternative which is quite useful in many cases. And the last point I want to make is many people tend to make this mistake forget this part of it just take the outer joint query here this part of the query. So, outer joint some people make a mistake of writing it like this select something let us not worry about that now from department D left outer joint structure I where D dot department name equal to I dot department name think about this query for a moment what does it do it is doing a left outer joint without any condition which means every department tuple will match with every instructor tuple and it is actually totally meaningless. So, fine first it matches everything this part the left outer joint is essentially a Cartesian product with the only thing that if department sorry instructor relation itself is empty all the department will still appear they would not appear in a Cartesian product, but here they would appear. So, it is almost the same as a Cartesian product after that you apply this condition D dot department name equal to I dot department name. And now what is going to happen if a department had no instructor you know it would be matched with every other department and all the sorry it will be matched with every instructor, but all of those matches will get filtered and in the end the department is actually removed by this joint condition. This is not going to give any tuple for a department with no instructor that department will be eliminated. So, this is actually a completely meaningless query it is not the same as this it is a completely idiotic query do not use it. So, moral is when I do an outer joint I can use a natural outer joint that is fine if I use left outer joint I need an on clause here with an appropriate condition and that condition should not be replicated in the wire clause. If you put in a wire clause you know I dot department name would have been null if D has no matching instructor and then such tuple will get filtered out and the net result will be a regular joint not an outer joint. So, do not use this the reason I mention this is that we had given some query in our assignment and several students gave the second form instead of the first form. And what is more surprising is that some T is missed this and marked it correctly they are correctly in a hurry and did not notice that this condition was not an on clause it was in the wire clause. So, this is an easy mistake to make you should be aware of it. So, that is it for outer joints.


The other videos that I have recorded provide lots of details about multi-level modelling and their use in research applications. In this video, I will provide a couple of important points to conclude the discussion on multi-level models. The first thing about multi-level models is that they are applied to models where you have multiple levels. So we have level one observations typically nested in level two observations, typically nested in level three observations. We could also have crossed random effects so that for example year observations would belong to firms and industries, and each firm would have multiple year observations, each industry multiple year observations, and there is no hierarchy relation between firms and industries. For example, if level one was investment, level two would be startups, level three would be venture capitalists, then that kind of cross structure would be useful. In the nested structure here, we have the multi-level model can be expressed in a mixed model. So we have the fixed part and the random part. This random part is the beef in multi-level modelling. And one important thing that you need to understand is that if you're only interested in this fixed part, you don't actually need to use multi-level modelling and using multi-level modelling could actually be undesirable. The reason is that if you're only interested in the fixed parts, you can just use cluster robust standard errors. In a normal regression model, the assumptions about the error term basically affect how the standard errors are calculated. And cluster robust standard errors are consistent for any structure of the random part. So cluster robust standard errors, as long as you have sufficient sample size, don't care about what kind of structure you have here. You can of course use cluster robust standard errors in multi-level model as well. Multi-level modelling on the other hand, the correctness of the standard errors, and in some cases the estimates as well, depends on this error part here being specified correctly. So this imposes quite a lot of structure on the data, on the error terms, whereas cluster robust standard errors don't assume any structure of these errors. So if you're only interested in what is the average effect of year, what is the fixed part gamma 00 of the effect of year, then you don't actually need this, and you can get more robust results more easily by not using multi-level model. There's a nice paper by McNeese in Psychological Methods, where he goes through applications of multi-level modelling in psychology, and concludes that most studies that applied multi-level models did so without a real need for the multi-level modelling, because they were not looking at these variance components in the random part. They just wanted to control for unobsert effects, and they were focused on these fixed effects, and that was the interest in the study. In that case, cluster robust standard errors does its job, in some cases better, but at least more easily. If you want to use multi-level modelling, then your interest should really be in variance components. So proper applications of multi-level models, you see this kind of tables that contains two sections, one for the fixed effects coefficients, one for the random effects, and then these random effects are actually interpreted. Like in this House Nets paper, they were focused on how much do trends in absenteeism vary between individuals and between work groups, and on what level does that, on what level is there variation? Are there trends that differ between different work groups, or is the trend same for all work groups? That was one of their research questions. So if this is not of interest for you, if you don't know what this means, then you probably should not be using multi-level modelling, but just use cluster robust standard errors and focus on the fixed part. Multi-level modelling is used like regression analysis. So interpretation is the same, random slopes interpreted like moderation. Then you have spaghetti plots that you can apply. Diagnostics basically the same, you have plots of residuals and predicted random effects, added variable plots can be done, various influence plots can be done. The difference between the diagnostics is that some of these diagnostics are slower to calculate, because they require re-estimating the model many, many times, or they require predicting random effects, which can be slow sometimes. And another thing to know is that the software support for these diagnostics is not as developed as for normal regression models. So for example, if you use data, you just can't type RVF plot after multi-level model, you have to actually do the predictions yourself using predict and then do plotting yourself, so you need to construct the plots manually. But if you have done that once, you can reuse the code multiple times in different papers, so it's not that big of a deal. Not all plotting can be automated, so that's what I just said. So that's something that I think prevents people from applying diagnostic plots or visualizations, is that they really need to think through what the random effects are and how they are related, how do you actually do the plot instead of pushing a button or using an Excel sheet that you find online to do the plot that you like. Two important takeaways are that all multi-level models, all longitude data sets are also multi-level data sets. When I teach people, I sometimes hear of econometrics-focused people telling me that they don't need to care about multi-level data because they only work with panel data. Well, a panel data is a special case of multi-level data and understanding multi-level modeling will allow you to expand your toolbox so you don't have to do only with the econometrics techniques. There tends to be, as documented by the McNeese paper that I cited before, there tends to be a trend that people or disciplines focus either on multi-level modeling or panel data econometrics are largely unaware of the other tradition. So if you know both, you have a larger toolbox. And the second important takeaway is the most important question, if you think about applying a multi-level model such as LME4 in R or mixed in Stata, is do you actually need a multi-level model or would a regression model with cluster over standards do? Quite often the answer is that yes, regression would actually do the job.