 This is a good point to take a break and take some questions. I will go with live questions now and then I will look at chat. L R Tiwari, Meera Road, please go ahead. Sir, you spoke about the research methodology. Why do we need to have a search? This is the basic question I have. Well, research and search are two different things. The goal is to find something new and we saw the Oxford definition of research. There must be something novel here. Search is something different. If you mean web search, that is a different thing. Now, whenever you do research, you have to see what other people have done first because you do not want to reinvent the wheel. If you come up with something which somebody has already done, that is not novel. So, the first part is understanding what other people have done, how far they have reached and what are the gaps. Then you go around finding gaps and filling them. That is a major part of research. Sometimes research can be totally different in the sense when the web came up, the web was not exactly a research project. It was a development project which was done in CERN and it was a tool that was built to help physicists working on shared projects. But then it exploded. Its possibilities were amazing. What was initially just the tool exploded into something much bigger than anyone had thought about. Then there were so many research issues which came up. How to do web search? How do you type a few keywords and find information that you are looking for? Information retrieval is an old area, but it exploded when the web exploded. So, you can have research where there is a real life problem. You do not know how to do it and you figure out how to do it. That is certainly part of research. So, that is one of the two modes I have talked about. That is one mode. The second mode is when you look at what others have done and try to improve on what they have done. I hope that answered your question. Let us move on to at this point to questions related to storage. We have one more session on research. So, I will answer more questions on research tomorrow evening. We have Xavier's Catholic College, Tamil Nadu. Sir, actually you explained about the rate system. Yes. Yes, and you said multiple disks are used in parallel. Yeah. So, what should be the capacity of this individual disk that we should use? Should it all be of same size or different they can be? What should the capacity of the individual disk be? Is that the question? Yes. Yes, sir. For rate system. Yeah. So, the capacity of individual disk depends on the cost of the disk, the reliability of disk and so on. So, what happens is typically the newest disk of highest capacity tend to be a little more expensive. So, if you look at the price per gigabyte of a 3 terabyte disk today, it is higher than that of a 1 terabyte disk. So, depending on how much storage you need, your minimum rate system might support maybe 6 to 8 disks. So, if you need 3 terabytes of usable capacity, you might put in 6 1 terabyte disks and put in in raid 1 or if your data is cold, you might put in maybe 4 1 terabyte disks with one of them acting as parity disks, then your cost comes down. Your write performance may come down, but if your data is mostly videos which somebody is downloading, you are reading, but not writing that often, this is perfectly good. So, that depends on what your application is. So, this capacity which you choose again depends on that. It is more an economic choice than a choice which is based on technology. So, today if I want more capacity from the same box, I do not want to buy a new box, I might buy a 3 terabyte disk, pay a little bit more, but avoid the cost of one more box. Do you have any follow? And on the same topic, so what should be the physical arrangement that we should make if you want to have multiple disks in the raid schemes there? Actually, should it all be of hard disk, what is the real physical arrangement on this? So, typically how you build a raid system, if you buy a server box, that server box has space for a number of disks. Typically, you would put disks of exactly the same type and size so that the performance is uniform. You do not want to mix some slow disk with some hard disk some disk of higher capacity with some disk of lower capacity, you do not want to do that. You would pick identical disk and put them in an into the system. Now, the system will the raid software or the raid hardware, you can buy a hardware card which supports raid or you could do it in software. Software raid is acceptable for many applications, but not for critical databases. So, there is a small chance of data, some updates being lost. So, if you are a bank database, you would definitely not trust it to software raid, but if you have some student data which once in a way if you lose, it is not the end of the world, you might be happy with software raid. And whichever one you use, those 6 or 8 disks which you put in are organized in whatever form, raid 1, raid 5 as appropriate according to your choice. So, the raid systems will allow you to configure it. So, they will let you choose what raid type you want, how many disks and so forth. Any further questions? Thank you sir. Good morning, we have Bannari Aman, Satyamangalam, please go ahead. Good morning sir. So, what are the difficulties we could face while sustaining relationship among relations in a complex database sir? Relationships amongst relationships in a complex way, I am not sure what you mean by that. Database sir, while creating relationship relations in a complex database. What is the question? What is the issue? What are the difficulties we will be facing? Sir, a question like this is very loosely phrased, like what are the difficulties we will be facing. This is rather loose question in the sense that you know I can tell you that if you are a programmer, you will have difficulty doing joints and things like that. That is not the point. Why did you come up with that particular schema? It was driven by a design process which helped you choose that schema and if that schema was complex, well it is complex and you know that is what you needed, that is the kind of data you wanted to store. Which complex is complex? That is life and then if you have to do joints, that is life. Now, if you find performance problems because of that and you want to deal with that, there are ways around it. There are ways to create materialized views and so forth which can speed up performance in some cases and there are indices you can create which can speed up performance. But it is not driven by you know what are the difficulties? The difficulty is how to quantify? What are the performance issues? What are the overheads on programmers and stuff like that? You have to break it up into pieces and address these individually. Any follow up questions? Thank you sir. Jawaharlal, Institute of Technology, Madhya Pradesh. Sir, there are some other parameters for measuring the performance of a disk. It is exploding, seek time, average time. Can we measure the performance? Sir, the ones which I have given you are fairly comprehensive. There is seek time, access time, this rotational latency and together access time. Then there is the transfer rate for reads and for writes and then there is mean time to failure. Those are the primary metrics for measuring the performance of a disk. I am not aware of any other major ones. There may be some other less important metric, but these are the major ones. Information storage management is a part of database? What do you mean by information storage management? Database is storing data. How are you differentiating data from information? You know, that is something which is up to a higher level, but at the physical level, data is data. Whether data is a record or it is a document or it is an image, that makes a difference on whether you store it in a database or in a file system. So, that is a primary distinction. Good morning sir. Sir, what is the basic difference between land flash and north flash? So, there is a technological difference between it. I mean any of us who is familiar with hardware knows about NAND and NOR gates, but that is not the key thing here. It is not the NAND gates are very different from NOR gate. It is just a terminology and the primary difference between these two is that NOR flash is used to store data in a way which is byte address. So, it looks more or less like regular memory. You can give an address and read it very fast and this is used to store like ROM. Read only memory is often stored in NOR flash. So, you can actually execute a program by reading directly of NOR flash. It is possible. So, some boot loaders and so on may be stored in NOR flash. On the other hand, the byte addressability means that the per byte cost goes up. So, if you want to reduce the per byte cost of flash storage, you use this alternative which is called NAND flash. Again, do not worry about the issue of NAND gate versus NOR gate. Just think of it as terminology. Maybe NAND gates are used, but the key thing here is that it is not byte addressable. So, you can pack the thing very densely in a chip. You can store a lot more data in a particular number of gates with the NAND flash technology than with NOR flash. So, that is what is used for pen drive solid state devices. We have Ohm Institute, please go ahead. Here is Surinder Singh from Ohm OITM, Heshaar Haryana. My question is that what is the difference between server-based RAID and controller-based RAID? Server-based versus controller. So, I think what you are referring to is what is also known as software versus hardware read. So, controller-based RAID means there is a specific controller which is the RAID controller, which implements the RAID subsystem and server-based which I think is the same as software RAID. I am not 100 percent sure about whether this is the same thing, but I think it probably means the same thing. So, in software RAID, you have programs which run in your computer which manage the RAID system. So, the computer knows there are two disks and when you want to write to one disk, it will also write to the other disk. So, you can set up your OS to recognize that these two disks should be treated as one unit for read. So, you might think that both of these would do the same thing, but the key difference is that any good hardware RAID system would have a bit of non-volatile RAM to record in progress writes. Why is this important? It is important because if there is a failure in the middle of the RAID, the RAID system may be left in an inconsistent state. So, if there is a power failure in the middle of a RAID, if the system crashes in the middle of a RAID, you could land up in trouble. So, the good hardware RAIDs, again people say hardware RAID and when you look closer, it is a card, but it does not have non-volatile RAM. So, I would not really count them as proper hardware RAIDs. So, a proper hardware RAID would have this non-volatile RAM. So, that if there is a failure in the middle, you can recover from that failure. So, if you have important data, you should go with hardware RAID. On the other hand, hardware RAID systems also cost money. So, most of our computer systems which do not have such critical data, the last few updates, if you lose it, fine. You are not going to lose sleep over it. So, for all such computer systems, we tend to have software RAID. So, most of our computers in the CS department here run hardware RAID because that is sorry, run software RAID because it is cheaper and good enough for us. Now, if you buy Intel Biosys, many of them support RAID as part of the bios itself. Now, you might think these are hardware RAID, but at least the cheap boards do not really have non-volatile RAM and what they are doing is what the OS would have done. Just write to both copies, if there is a failure, well tough luck. So, you do not get the benefits of non-volatile RAM with the cheap cards. Let us get back to the slides now. Where we left off before taking questions, we saw this slotted page structure which lets us store variable length records in a page. Now, how do you decide which record goes where in a file? So, there are various organizations. In a heap organization, a record can be placed anywhere in the file where there is free space. So, when a record comes in, it stored at any location in the file where there is free space. A common thing is to just keep sequentially adding at the end of the file and once the record goes there, it stays there forever. That is a heap organization. Well, forever meaning until there is a physical reorganization done. Sequential on the other hand stores records in sequential order sorted based on some particular key. So, you choose a key and store it sorted on that thing. Now, the original sequential files would let you do this sorting initially when you create a particular relations file. Whatever records are there are sorted, but if you start inserting, updating, deleting records over a period of time, the sorting will tend to get spoiled and then you have to reorganize the file once in a while to bring it back to real sorted order. The last option is hashing where you compute some hash function on one or more attributes of the record and that hash function tells you where the record should go. These were supported in fact oracle I think still supports hashing, but it is no longer encouraged as much for reasons we will see later. Finally, there is a data dictionary storage which is the also called the system catalog which stores all the metadata. So, this is very important because you know we have stored data on disk. If I want to read data from a relation, how do you know where in disk this data is and that information is part of the data dictionaries and the data dictionary has several pieces of information. The first is the names of relations including the names, types and lengths of attributes of each relation. Then you have names and definitions of views, you have integrity constraints. So, these are all logical information about relations. Other logical information includes who are the users, what are their passwords and then the statistical data which is used for query optimization such as how many tuples are there in each relation, what is the distribution of values, how many distinct values are there and so forth. And the next part is the physical file organization information which is very important. How is each relation? So, sequential hash, heap whatever, where is this relation physically? Now, what do I mean by this? If the data is stored as operating system files, this is simply the names of the files containing the data about that relation. If it is stored directly on disk, then this would have to include disk blocks which store data for that particular relation. Then there is information about indices which is the next topic and so forth. Now, all of this information can be stored as relations themselves. So, this is a schema diagram showing the metadata which we saw in the last slide organized as a schema itself. So, you have relation metadata with primary key being relation name. It has number of attributes storage organization location. Then you have attribute metadata whose primary key is relation name and attribute name. And for each attribute type, the position within the record, the length if it is fixed length or maximum length if it is variable length. Then you have index metadata, view metadata, user metadata and so forth. Now, note here that view metadata, I do not have a foreign key to relation meaning I am not tracking which relations are used in the view. If I did the schema diagram would be a little different, but then people are asking me what happens to view an underlying relation is deleted. It would be detected if you had a appropriate schema key. And the last topic for this chapter is storage access. So far we have said how to store data, but how do you access that data? And the way most database systems do it is you do not go and read the data from this every single time you need it. Instead, a data which has been brought from disk is stored in a part of memory called the database buffer. So, when you want to read data, you first check if that particular piece of data is already in the buffer. Maybe somebody else read it and it is there in the buffer. If so, you read it from the buffer. If it is not currently in the buffer, then you read it from disk into the buffer, which of course means if somebody else is already filled the buffer, some other data is filled the buffer, you have to evict some data from the buffer. Operating systems do this too as part of the file system buffer. It is hidden from you and database systems do it for the database buffer. Now, how do you manage space in the buffer? Whenever space is needed for a block which needs to be read in, you have to evict something. And there are several different buffer replacement policies. Now, operating systems typically use least recently used or variants of LRU, approximations of LRU. In operating systems, the idea is that a block which is recently accessed is likely to be accessed again in the near future. Operating systems usually cannot do much better. Actually, they can these days. There is some hacks on top of that. But, by and large, they do not know what the application program is doing. So, they cannot really do more. But, a database system knows exactly what is going on. If you are running a query, the database system knows what the query is. They know what data that query is going to access. They know this query is going to read every single block of this 10 gigabyte relation. If you use LRU, you read the first block, you are reading the relation sequentially. You are not going to read that first block ever again till you finish reading the whole relation. So, LRU turns out to be a very, very bad idea for a relation scan. In fact, what it does is, when you have large relation scans, it evicts all the useful data and keeps a whole lot of useless data in the buffer. So, database systems tend not to use LRU naively, but to use alternative. So, what they do is, first while a block is being used, it is pinned or fixed in memory. So, we have been hearing a lot about spot fixing. So, I could not wait till we got to this slide on pinning or fixing data in the buffer, which has a completely different meaning. It is fixed in buffer. It cannot move out. So, while you are accessing a block, it has to be pinned and when you are done reading data from that block, you can unpin it. In this period, it cannot be removed from the buffer. Now, among those things which are not pinned, you have multiple replacement policies. If you are doing a relation scan, one block after another, as soon as you are done with the block, you might as well throw away that block, because you are not going to look at it again. So, for whatever block was fetched for this scan, can be tossed immediately. Of course, if that block was already there, because it was being used for something else, maybe you do not want to apply toss immediate as this. Toss immediate is closely linked to most recently used, which says that the system must pin the current block and as soon as you are done with it, you throw it out. It is basically the same thing. It is another name. There are some minor differences. And lastly, some pieces of the database are very highly frequently accessed. These include the data dictionary itself. So, what most systems will do is keep the data dictionary blocks in a main memory buffer or they may even read the data from those blocks and create in memory data structures, which are more efficient and then these blocks can be thrown out. And finally, buffer manager also supports forced output of blocks, meaning you can tell the buffer manager, I have updated this block. Now, please write it out to this. I need it, so that if there is a failure after this, the data is safe on this. I will take a few questions related to storage. D. Y. Patil Kolhapur. My question is, we have been talking about data storages and the question is, the recently the new field in data storage or data retrieval is coming on that is digital forensic investigation. So, sir, please would you throw some light on digital forensic investigation. So, digital forensics at the level of data storage, there is many aspects to digital forensics. But the idea is that people may have stored files on their hard disk and maybe they deleted them subsequently, but you still want to find out what they had stored because it is very easy to delete data from a hard disk apparently. You say, you know, delete or remove or depending on the OS you are using, but it turns out that when you delete data from a disk using whatever OS command, that actually just delinks all the blocks from the directory structure. The actual blocks are not overwritten at that point. So, the original data which you had there are there on the underlying blocks. The only catch is that you do not know which blocks are relevant, which blocks were in your file. So, there are, you know, some hacks which can help you partially recover files, even though you have deleted them. And that is important if the, you know, you are looking at a particular hard disk for evidence of some criminal activity, which the person has deleted maybe by, you know, applying these techniques. You can find out that, you know, this file was there, it was deleted, it was unlinked. But by scanning the hard disk, I, you know, find that what looks like part of the free space was actually the header of a file. From that, I can find the other blocks of the file. Some of them may have been overwritten, but at least I can find parts of the file and determine what had been stored there. So, this is part of digital forensics for storage. That is not a topic we are covering here. It is a very specialized topic. Most people will never get to it. However, the relevant part is if you are, if you really want to cover your trail, it is not enough to just remove the file and you are not done. The other part of it is, if you have stored passwords on your machine and your hard disk apparently fails as some problem, you give the hard disk away to a vendor. Well, if you are a high value target, they might, somebody might be able to put some effort and recover stuff which you had on your hard disk and maybe access your bank account. So, those are some risks which people should be aware of when disposing of hard disk. In particular, if you are disposing of hard disk from your college or home with some important information, you should be careful. Any other questions? So, I have one more follow up question. I would also like to know some research avenues if there are any in this field, that is digital forensic investigation. Research avenues in digital forensics. Now, I am not aware of work in this field. This is not an area I have looked at. So, I am not able to guide you on it. So, I am afraid I have to leave it at that. But I will tell you some work in the database area which is there. You can go read it up. There has been some work on how to clean up data thoroughly in such a way that there is no trail left behind. So, which is the opposite problem. It is to help you clean all traces of what you have done. The converse problem is given that somebody has not done such cleaning, can you recover data which was deleted. So, I am not aware of work on the recovery of data which is probably a very important problem even more than cleaning up. The only thing is that the recovery tends to be specific to specific databases, specific data structures and so forth. So, you have to understand those details in order to do any of it. So, when it goes down to very specifics, it is a little harder to do this without some intricate knowledge of the storage structure. So, you have to be prepared to get your hands dirty and understand stuff at a very low level. But yes, I think there are some avenues for doing it both at the level of files in the file system and also storage in a database because databases have tuples which have slotted page structure. So, on the things which we studied, you should be aware of it. So, if you deleted a record in a slotted page structure, if the other records in that page got moved, then there is no hope of to override the gap that it created, there is no hope of recovering it. But if something else happened, then maybe you can still recover it. So, many databases are multi version data, meaning they keep old versions of data around for some time. So, even after you have deleted it, you can recover it from the old version. So, there are some issues at the database level and how to recover data that has been deleted. So, my question is why RAID is so important for database and how to decide which RAID is suitable for which database? Yeah, you do not pronounce it as RAID, RAID. So, why is RAID important? RAID is very important because disks do fail. We have a cluster of 40 machines in our basement which we use for my colleague who has set it up. So, those 40 machines have I think about 120 plus disks and pretty much every month, one or more disks failed out of that 120. Now, if we did not have RAID, we would lose data every month and if you lose data recovering it, setting up the OS again, it is a big headache. It is a lot of manual work. You really cannot afford this kind of data loss in pretty much any setting today. So, RAID is the most widely used thing to prevent data loss when a disk fail. It is a given that disk will fail. So, you have to have RAID. Now, the choice is between different RAID types of performance. There is another kind of choice which is actually relevant in the context of file system that spans many computers. We will talk a little bit about it in the context of big data, but even before that it is worth mentioning that there are distributed file systems that offer distributed RAID. What that means is it is not that copy of the disk is made, but any file block which you keep, they will keep it in more than one location. So, if a disk fails in the overall distributed file system, they can still recover the data without having a one to one mapping. It is not like hardware or the RAID at the level at which we saw it, but similar concepts applied at a different level. So, does that answer your question? So, that is an active area. Sir, one more question related to attributes. How to implement the simple attribute, composite attribute, derived attribute in SQL? So, we saw how to take an ER diagram with these types of attributes and convert it to the normal, first normal form, flatten it out. So, that is pretty much what you would have to do. There are some rare cases where you could use the object relational features of database like PostgreSQL or Oracle, which let you store fields which are multi-valued, but that would be more the exception. You should not do that as a matter of course. Sir, my question was with respect to discrete performances. In Linux, we can increase our discrete performance with the help of block DEV command, that is blocking of IO controls. So, how this can be done with respect to Windows other than the options for disk fragmentation? I am not familiar with Windows at this level. So, I am unable to answer your question. So, the optimizations in the Linux context. So, by default, the Linux file system do a variety of things. So, when you read a file, they are supposed to keep track of the access time. When was the last time the file was accessed, which actually has a very big overhead in some cases. If you are doing a lot of reads, that turns into a lot of updates of the access times of the files. So, one of the optimizations which is available is to turn it off for a particular file system. So, access times are not tracked. And that is very useful in many cases because we do not care about access times. When was the last time you used the access time for a file. Whereas, the update time is something which is very important. There are a few other parameters like this which you can tune to improve the performance of a file system. The Windows equivalents of it, I am not familiar with, unable to answer that part of the question. Put him a college Rajasthan, please go. Sir, my question is how relational databases are stored on disks. In other words, how multiple relations are mapped into disk files. And is there a separate file for each relation of a particular database or multiple relations are stored in a single disk file? That is a good question. So, I showed an abbreviated version of chapter 10 slides. If you see the full version of the slide or if you go to the book for that matter, there is some discussion of this issue. So, restricting ourselves to systems which store data as OS files. The most common situation is that there is one or more files per relation. But two relations cannot share space in a single file. That is the most common kind of setting. So, if you have one relation, it may have 20 files. Another relation may have one file depending on how big it is. Typically, many of these systems will create a directory and start creating files inside the directory as the relation grows, they will create extra files. Now, the other part of the question is, can you have multiple relations stored in a single file? Logically, yes. And practically, some databases allow it. I think Oracle allows this. It is called multi-table clustering. So, if you have two relations which tend to get access together, it is actually more efficient to store the tuples of those two relations plus third together in the same page. So, supposing I have, let us say, a student and the courses that the student has registered for. And these are accessed very frequently together, may be transcripts. Courses registered for is slightly different. But let us say, a student and their transcripts. So, these are very often accessed together. So, it may make sense to store a student tuple with the transcript tuples in the same page. So, this is supported by some database system. There are trade-offs. It will help certain queries. On the other hand, it hurts certain other queries which just want lists of students without getting their transcripts. So, they pay a higher IO cost. So, there is a trade-off. So, that is the end of this chapter.