 Dear students, today we are going to discuss the lecture number 4 of the database management system course. Before starting today's lecture, let us review the previous lecture briefly. In the last lecture, we were discussing the three-level architecture of the database and out of three levels, we have discussed two. We discussed the external schema and the conceptual schema and we saw different examples where we saw the relationship between these two schemas and how do we get different views from the same conceptual schema. Today we will proceed with this discussion and we will discuss the internal schema. So let us start our lecture today. This is a review of today's lecture. Today we are going to discuss the three-level architecture. Then we will discuss the data independence and finally we will discuss different aspects of the database management system. Internal or physical view. Before discussing this view, let me give you a brief explanation. Generally, there is one thing that is required from the internal and physical level. When you do general discussion, you need to give the internal level or the internal view and give the physical view the same thing. But in your mind, there should be a little difference. So although you both will take the same, but I will discuss the difference with you today. The physical or internal schema is related to the actual representation of the data. The actual representation is the representation of the data on the disk. From here, we can see that the way the data is seen on the screen and the way it is stored on the disk, there is a difference. You may know that the things stored on the disk are stored in binary format. For example, the characters in English, figures, diagrams, or even in Urdu are converted to the form that is seen on the screen. Today we are going to look at the physical level of the database. The physical level is what it means and what happens there. In the internal view, the DBMS selects how the data is actually represented. This means that when you have defined a conceptual view, it is the format in which you actually want to store the data in the database. Now the thing is that there are different possibilities of storing the same thing into the computer or onto the disk. Here, based on different criteria, or sometimes the guidelines given by the DBA, the DBMS selects which format the data should be stored in in different possibilities. Another thing is that when we define things on a conceptual view, in the physical level, the DBMS also adds some other things. What are those things? This is not the concern of the common user. Because those things are required by the DBMS itself in order to access the data, to store the data, and to perform different processing on the data. So that is why they are not of much concern for the user of the database. But anyway, to complete the concepts of our understanding, we will definitely look at the type of data that goes to the physical level and adds to the DBMS data. Let us move on. What is ahead? In this, the DBMS itself plays the role of the DBMS. The internal level or the physical level is the concern of the DBMS. But in this, the interaction of the DBMS is with the operating system. Because the operating system is at a layer that is lower than the DBMS. And DBMS is the advantage of using the operating system of the operating system, that the devices that are being used, whether it is a disk, whether it is a printer, or any other device, to control it, to handle it, it does not have the responsibility of the DBMS. In fact, the DBMS uses the operating system as a facility. So here, on the lower level of the DBMS, it is associated with the operating system. As I told you, as general discussion, we will take the internal view and the physical view the same. But where I go to their difference, I will just talk about the internal view. The internal view is the level that the DBMS itself manages. That is, the formatting of the data up to the internal view is the responsibility of the DBMS. After that, that is the physical view. And mind it, the physical view is not included in the three-level architecture. In the three-level architecture, strictly speaking, our relationship, or the architecture relationship, is only up to the internal level. And the DBMS is only the internal level, conceptual level, and the external level is responsible or takes care of them. When we talk about the physical level, what it means is that in the 0-1 language, that is, in the binary language, the data stored practically on disk, that is not concerned of even the DBMS. That means that the internal level that is recorded, after preparing it, the DBMS that will hand over that record to the operating system. Obviously along with the appropriate guidelines, appropriate directions. And according to those directions, according to those commands, according to those guidelines, the operating system will practically store that data or that record on disk. So here it says internal record, a single stored record, that is, the final format of the record in which the record will be stored. And as I have just said, the data that we see at the conceptual level, the data at the internal level, it would be different. Why? Because the DBMS will add certain things into that record, and the record at that level will be called internal record. As I have just said, it will not only be the same thing that we see at the conceptual level, but it will also include some other data. As it is written here, that usually it is the same thing that we have given at the internal level. What happens is that it goes to the physical level, and the operating system converts the internal level of the record into binary format in order to finally place it on the disk or any storey device. It is the responsibility of the operating system. Before moving ahead, I would like to revise all these schemes in front of you that the external view is the view that your users and users are seeing. The conceptual view is the view that your DBA is seeing and this provides a basis for all the external views. Second, we say that this is the view of the whole organization. Rather, this conceptual view represents the database structure of the entire organization. It is below that of the internal level. The data that we have defined on the conceptual scheme and some other data that DBMS has added to it becomes your internal level. These three are the levels that are included in the three-level architecture. The physical level is below that of DBMS. It will handover to the operating system and the operating system prepares the physical layer and stores it on the disk. Now, there are three levels that we have. We have seen external, conceptual and internal. Now, we have seen how the link between these two is established. For example, as we have seen in the example that the store is a date but we are seeing that you can see the age there. This means that there is some conversion involved. Where is the conversion? How is it happening? Let's see in the next slide. The link between these two levels is established and maintained through inter-schema or inter-level mapping. For example, what happens in this mapping is that the data items of one level are linked and related to the other levels. For example, we are seeing age but age is not such a store but it is coming from an attribute from a property, date of birth. Basically, the date of birth is being accessed from the database. That is being converted. There is a formula applied and a calculation is being done. After that, we have age. This means that we have an external view on the external level and between the conceptual level, there is some link, there is some mapping. That is why we call it internal, inter-level or inter-schema mapping. Similarly, we have an attribute on the conceptual level and in that record, on the internal level, we have an attribute of a record. When you talk about the internal level, we have a complete record. In that record, every attribute has a physical position. The physical position is not so important on the conceptual scheme but it is on the internal scheme. Because a record is made in different houses. And every house has a value of an attribute. So, you have to relate the attribute on the conceptual scheme so that when we take the practical value, we will take the record from which house, which block and place it in that attribute. This means that we have also a mapping between the conceptual level or conceptual scheme and the internal scheme. This means that, as you can see on the screen, we have two types of mapping. One is external conceptual mapping and the other is conceptual and internal mapping. You also get to know about these two mappings and what is their purpose and use. Let's move on to the next slide. External conceptual mapping creates links between the things on the external level and the conceptual level. Similarly, the conceptual and internal mapping creates links between the things on the conceptual level and the internal level. Now, we will look at the three-level architecture again and we will see what are the new components added to it. This is the same diagram that we have already seen. You can see that you can see the three levels. The physical data organization on the bottom-most level, as I said earlier, this is not the concern of the DBMS and it is not included in the three-level architecture. Here, it is just shown for the sake of completion so that you can see that the data that is practically stored is stored on the internal level, the third level on the bottom-most level and that is being controlled by the operating system. These two are external conceptual mapping and conceptual internal mapping. Now, they are also being shown as a part of the three-level architecture. On this slide, what I am showing you is that what you have just read is that the three-level architecture will explain this slide in the form of data. We have the same pranae user Selima and her pranae views. By the way, there is no screen in it. You can see the data. You can see that Selima has its own view and Selim has its own view. You can see this shaded block. Basically, this is representing the mapping, the inter schema mapping. Here, what we have on the conceptual level is that the data that we are seeing is in the form of a table. It is in the form of a table. It is in a proper organized form. Again, the block that we are seeing is inter schema mapping. This is the block that we are seeing. This is a form of an internal record. What you are seeing is that the record is arranged in the form of a block. It is necessary to form it in the form of a block and the size of the record that the computer or the operating system practically reads or writes on the disk. So, that operation is not performed in the form of a single record. Rather, the operating system for its efficiency purposes or due to other reasons is written in the form of a block or read on the disk. Now, it is possible that your one record is very small. So, what happens is that in one block, multiple records are gathered and when the record of the size of the block is gathered to be written, then the operating system sends the entire block to the disk or the device. So, if you look at the screen, the B.H. Simrod is the block header. This is the thing that we mentioned before that DBMS adds some things adds few data in the data that we see at the conceptual level and that data is used basically DBMS itself for the processing. Now, here it is a record header. This is also added in it. These are some examples. In addition, there are some other things that can be added in DBMS and it has prepared a record of an internal level. Now, this thing, this data will be handed over to the operating system by the DBMS with appropriate instructions to store it, which organization to store it. When this command is given to the operating system, then it will be there in this format. Now, here you see the same data that you had before in a meaningful state which was in the form of normal characters or digits, now that has been converted into the binary format. This is basically performed by the operating system. When your internal level data, the records, the block, when it has been converted into binary, now this will be finally returned to the storage device. And when you start reading it, if you read the inverse process, then your binary format will give the operating system DBMS at the internal level. DBMS will convert it into an internal record, into an internal format. After that, after reading it, after converting it, it will display at that level that we are seeing at the conceptual level. And from here, that data will be transformed through inter-schema mapping. And it will be seen from that that your users are seeing Selima and Selim on the screen. Dear students, three-level architecture is a very important topic of the database. And it is the topic that I consider that I declare as the basic topics. And I suggest you that you pay proper attention to this topic. And all of you should be clear about this. From this, you will be able to understand the database course. And in general, using the database, designing it, implementing it, it will be very easy. After this, the topic we are going to discuss is data independence. Data independence is one of the big advantages of the database approach. One thing. And secondly, it is an outcome of the three-level schema architecture. Hence, data independence should be clear understanding. The biggest benefit from this is that you will be properly able to compare the file processing approach and the database approach. Or you will be able to understand the advantages of the database approach properly. So again, this is one of the basic topics. So let's start with data independence. Again, a major outcome of three-level architecture. I must tell you one thing here, that data independence generally is taken in two meanings. It is given in two meanings. We will discuss those two meanings here. The first one is written in front of you right now. The immunity of applications to change in storage structure and access strategy. What does this mean? It means that your application programs will be preserved with that effect if the DBMS changes in storage structure and access strategy. This is what we have discussed as program data independence. You will remember that in the file processing environment we studied there was a drawback that program and data were interdependent. And we said that the database approach is independent from the program and data. This is the same thing. If you have to change, if you have to make some change in the data, your application program will not have that effect. Dear students, this is a very important thing. Because as I have already said that changing in the schema is a very serious thing. Because when you have developed a database system, you have implemented it and the data has been populated into the database and there are many applications that are running there. After that if something changes in the data structure in the schema, it may prove even fatal. Because what your application is running from earlier, there may be a problem. What could be the problem? That it will stop running. It will start giving an error. And mind it, in a developed system, in a developed database system, if the application starts giving an error like this due to any change, then it will be difficult for you to follow that sequence. Because in a big developed system, it is not so easy to guess that a change could have an effect. Although with the proper documentation, you can keep an account of the link between the things. Yes. But then if you change it, then you will change it strategically along with the measurements. But then I will tell you again that in a developed system, where the database is popular, changing it can be a very heavy activity. So, when we talk about data independence, which is an advantage of three-level architecture, in that, data and program become independent. So, you can change it in the data, you can change it in its structure. And the fun part is that the application program will have no difference. And that is a great achievement for the database approach. This is the second perspective of the data independence. Or within this course, whenever we talk about data independence, this will be the perspective. The first one is not wrong. But generally, we mean by the data independence this aspect. In this, changes in the lower level do not affect the upper levels. Here, the three levels the levels of the three-level architecture. We say that if you change the lower levels, then the effect will not be on the higher levels. If you change it on the internal level, then the effect will not be on the conceptual level. And similarly, if you change it on the conceptual level, then the effect will not be on the external level. This is what is meant by data independence. Dear students, there are two precautions here. The first thing is when you define the data independence, then you define it that there is no effect of the change on the higher level that we made in the lower level. Or you say that the immunity of the higher levels on the changes in the lower level. But mind it. Do not understand that any change will not affect it. So you have to be careful. You have to be clear about what do we mean by this. The second thing is mind the direction as well. When we talk about this definition, we said that changes at the lower level have no effect at the higher levels. Changes at the higher level do not affect the lower level. No. In fact, the effect on the lower level is not on the higher levels. Look, one thing is that I have repeatedly said that the change in the scheme is a very serious thing. Now, why don't we say that the external level on the lower level or the external level will be affected by that. So, at the external level we do not allow to make any change in the schema. Mind it. I am talking about the schema. The external level is the concern of the end user. And you do not allow to make any change in the schema to the external users. The external users through the external view access and process the data. But defining the schema is not the job of the external user or end user. That is the job of the DBA or designer. So, you need to define the data independence as bottom to up. Low level changes affect the upper level changes. One thing is that you need to define the direction. The second thing is that we cannot say that as a result of data independence your higher levels are immune to all types of changes. They are saved from all types of changes. This is not possible even. We will see this in the next slide. When we look at the example of changes I will explain to you what this means. There are two types of data independence. Logical data independence and physical data independence. Let us see what do they mean. We can define this in two ways. These are the two definitions. The general definition of data independence you can convert it in logical data independence and in physical data independence. The difference is that now you name the levels and you say changes in the conceptual model do not affect the external views just you can say immunity of external level from changes at the conceptual level. Again caution changes of a certain level of certain type. You cannot say that no, let us see the example. Here are different examples of the types of changes that we can make at the conceptual level. For example, you can see adding a new file or index etc. Index may be you are not able to understand what do you mean by index but later you will be inshallah. Now you have developed implemented, populated now there arises a need that we define in our data let us take any file that is present we want to add another file in that database now because of data independence its beauty is that all your applications were running no one will know that another file has been added this is only due to the data independence similarly adding a new field in a file that you said that we have stored the employee's data and you did not have a data store in that file now let us say for a particular reason you need to know the number of children of every employee in that case you need to add a new field so you have added this field the beauty of the data independence is that of the three level architecture that your existing applications were running and there could be tens of them even hundreds of them they will run like before no one will care that you have actually added a new field in your database similarly you want to change the type of attribute first you did it let us say number string similarly you want to change the size of an attribute you defined the attribute name as of length 20 before and now you need to redefine it as 25 there would be no effect on the already existing applications beauty of the data independence and obviously of the three level architecture now deleting an attribute this is serious where I mean you cannot say that you can change any on the conceptual level on the external level no, why? because when you are deleting an attribute from the conceptual level there could be tens of applications that are somehow related to that attribute someone is reading it someone is using some calculation some application is printing it so if you delete an attribute at the conceptual level all the applications that were accessing that attribute when that application run now due to inter-schema mapping the attributes at the external level they will try to be connected with the attribute at the conceptual level that you have deleted the attribute at the conceptual level error those applications will fail now although the data independence it provides you if you make a change at the conceptual level due to the logical data independence the effect will not be on the external level but mind it the vast changes can still be effected for example you can say if you have to define the conceptual or logical data independence you can define there will be no unnecessary effect on the external level of the changes at the conceptual level let us move forward this is the second type of data independence that is physical data independence and these two definitions can be derived from the previous definitions as you can see changes in the internal model do not affect the conceptual model or immunity of the conceptual level from the changes at the internal level but again certain type of changes will still be effected on the conceptual level let us see some examples for example changing file organization the file organization can change dbms itself due to certain reasons may be efficiency or may be on the direction of the dba dbms will change the file organization similarly indexes or index files have different strategies like here I will give examples of hash trees you can implement the index when you can change the strategy of index or first change your storage medium change the size of hard disk change the type of hard disk you make these changes at the conceptual level there will be no effect of these changes and obviously if you give an attribute to the internal level then you cannot expect that effect obviously it will be effected on the conceptual level and when it is on the conceptual level then it will be transferred to the external level as well I hope you have seen the data independence on this screen here I am showing you three level architecture and this time for you the stage at which we are getting the conceptual and the stage at which we are getting physical data independence we have shown in both of them and mind it the type of data independence they are basically provided they are provided using the external conceptual mapping or using the conceptual internal mapping the changes that you make at any level they are at maximum adjusted within these mappings so in this way the three level architecture provides data independence Dear students our three level architecture discussion has been completed and I hope you have understood the three level architecture and the different levels have links and the three level architecture advantages you will understand this when you will use the databases when you will define the data on different levels then you will have three level architecture so let's go to the next topic the functions of dbms I have told you briefly that dbms performs two major functions one is managing the users and other is managing the data so in this slide or in the next slides we are discussing the functions of dbms in a little detail the objective of this is to understand what is the role of dbms and what are the links in database and dbms apart from this the major types of dbms and we will use some of them we will discuss about them so that you can understand more about dbms let's start first is data processing data processing means that the dbms does different operations as you have understood what does it mean right from starting the creation of data then putting the data into the database then arranging data in different forms then accessing the data placing it at different output devices all these activities are processing of data and all these activities perform dbms after that this is the responsibility of the dbms to maintain a user accessible catalog what is it catalog is a place in which all the information is stored when we talk about the scheme we have talked about the scheme of different levels all those schemes are available in catalog all the users are available in catalog the catalog is about the architecture and a lot of information about which you will be able to understand what is happening in catalog all that is stored in the catalog along with this especially the relational databases about which you will learn along with that a requirement or condition on dbms it is also imposed that these catalogs should be accessible to the users as well obviously there are two things when we talk about the user we do not mean by it the end user because the end user is not that technically strong that they can understand how to use catalog and the second thing is that you give them the right to access the catalog because again catalog means the structure of the database and that is serious that is intention if you don't allow the naive user to access when we talk about catalog generally the catalog is the dba or maybe in some cases your sophisticated user because they are placed at the higher position in the organization they usually have this authorization but on the other hand the kind of management or sophisticated user they are not familiar with the catalog structure therefore they don't use the catalog generally what they use is dba but the dbms has to provide the access to the catalog after that transaction support transaction is a unit which is called a work any work it can be anything it depends on the system how you define a work a transaction so dbms should give the support to declare a work that this is a transaction so dbms should do special operations take care of it what are the operations we will read later but it is enough to know what it provides then there is concurrency control services we have already read that multiple users access the database at the same time so we have also read that this is a very sensitive it is a very technical thing this is very nice this is very sweet that you are providing concurrent access but you have to be careful because it is inconsistent so dbms gives concurrent access but should also provide proper control next slide recovery services if by chance your database fails so dbms should have this feature that when you turn it on it will bring the database how? we will read it later but it is enough to know after that authorization services from the database only those who can access the database can access it and only those who are allowed this is called authorization services then there is support for data communication if if you want to communicate over a network then in that case that facility should be in dbms so that it can communicate over any medium then there is integrity services integrity services are that you place different checks on that so this should be the responsibility of the dbms that it takes care of those checks that is if you say we have in that record we have to keep the department's name so the name of the department should come there which is legal that is in that organization the department that is there should be the same department for the employee for example in an organization there is no public relationship department and when we see the data of an employee the department is written as the public relations so this means that the integrity of the data is not right so the responsibility of dbms is that the integrity checks that guarantee the correctness of the data should be provided to the community in these two slides we have discussed the general functions of dbms and the one that you will read later on we are going to discuss the different type of dbms so let's see Dear students we use dbms in different environments in different situations we can divide them one category is single user database management system environment and the other category is multi user dbms environment what is meant by single user dbms environment this means that when dbms is only one user who is accessing it is not about accessing because it can be that the environment is for multiple users but sometimes only a single user is accessing the database dbms this is a single user environment such environment can only support a single user at a time normally we call it desktop dbms also when you run dbms the database you will run the database you will use only one user at a time even if 10 people come to the computer in different situations or use dbms or use the database that is a different thing dbms the single user environment can record 10 people or use the database but the thing is at a time at a time the single user will be using it so this is called a single user dbms environment obviously the country is a multi user dbms environment in this we have different types of environments come and see which environments first is tele processing environment this was in the beginning in the start days of the dbms it was not that easy to use second is file servers still this is not being much used nowadays and third is client server is that environment that is being generally used nowadays in a multi user dbms environment so let us see what is the meaning of this and we will discuss it briefly this slide is showing you a tele processing environment in between this this is the machine on which basically dbms is running and the which are attached to it they are terminals and rather they are dumb terminals the meaning of dumb terminals is that there is no processing they are only used for seeing the results or typing something all the processing which is running from dbms on the computer or even in terminals the application that you are seeing or running they are also running at the central computer the mass processing the database general application run all of that is on the central computer and that is basically sent the output is sent to the terminal and terminal just displays it that this environment puts a lot of burden on the central computer because in this you are only seeing 3 terminals in dbms environment there can be many terminals so if a single computer is handling the processing of all those terminals then obviously there is too much load on that single computer so either this computer should be very heavy fast but as fast as it is doing so many applications it naturally slows down so this was the environment which was used in the early days of the dbms environment as popular now this is not that much used let us see what is ahead this is a file servers environment the special thing about this environment is that it is normally established on a local area network or for now it is established on a network so data management the data storage that was the responsibility of the file server but the first terminal that you had was workstations and workstations were also proper computers and every workstation was running a dbms so this means that a local copy of dbms is running on all the workstations now whenever a user needs a database the user will send that query from the workstation to the basic file server the file server is managing the database the major characteristic of this approach was that whenever a workstation request for some data rather than sending only that data the complete file was sent to the workstation because workstations are running dbms by themselves so it is just that the file server is managing the storage all the database all the files in the database they are being stored at a central place but there is no processing processing is done at the workstation if you need some data instead of that you just get the same data which you need the entire file for example you have 10,000 employees now in your database 10,000 employees are stored you need one employee in the file server environment you will send a request to the file server and file server will transfer the entire employee file all the records will be sent to the workstation wherever you come that query that application will run on this file and they will see and the interesting thing is if you want to make some change in the data do you want to read the data do you want to see how many working hours of the employee in this situation you just want to read the data when you apply for the data the entire file server you just saw the data and that is it but first if you want to change you want to change a single record first you said the scale of the employee was in grade 3 grade 4 so what will happen all these 10,000 records will move to the workstation you will change one record and then all these 10,000 records will go back so this means although your file server the file server is not that busy as it used to be in the tele-processing environment the problem here your network is now overburdened that means the network is so much because the files are being transferred so this was a feature of this environment let's see the next environment this is client server environment in this you have a server machine and you have clients the client your front-end or your application program so whenever you want to access some data so first the syntax of that query whether it is correct or not will be checked on your local machine and after that your request will go to your server server is the machine where actually dbms is running and mind it in the client server environment you do not have dbms running on the clients whereas in the file server environment every workstation was running a copy of the dbms here it is your clients are running just the application but dbms is running on the server now on your request dbms will receive that request you will see the request that has been requested is it legal or not whether data exists there or not if data exists the database the files and only that data will pass to the client from this you can see the beauty of this idea that the tele-processing your server is overburdened that all applications are running and on the other hand we do not have the file server environment drawback the network was too busy because the dbms is returning only the required data so this is the client server environment there are different types about which we will read in the next lecture dear students today we will finish our lecture and before going I will tell you the important points of today's lecture in today's lecture we completed the discussion of the three levels we discussed the three levels and how the link is established the three level architecture the major advantage of the database approach we discussed that and then we saw that there are two major types logical data independence and physical data independence and finally I told you about the different functions and the different environments of dbms the single user dbms and multi user dbms we discussed that in the evening and after that I have briefed you about the major characteristics of these environments I hope you enjoyed today's lecture goodbye