 Dear students, Assalamu alaikum. Today we are going to discuss lecture 3 of the Database Management System course. Before starting today's lecture, I will briefly review what we did in the previous lecture. In the previous lecture, we discussed some of the advantages of the database approach. Then we discussed different types of database users. And finally, we discussed different levels of data. And we also discussed the importance of the data for an organization. At the end of the previous lecture, I told you a brief history of the database architecture. Database architecture is going to be our topic today. So let's start today's lecture. The overview of today's lecture is briefly we are going to discuss two topics today. One is database architecture and second is the data independence. Both of these topics are among the basics of the database approach of this course. So I will suggest you to kindly pay very well attention of these topics. As we discussed in the previous lecture that there have been different efforts to standardize the database architecture. What is the need for this? What is the benefit of this? When something is standard-defined, the benefit of this is that whenever a person starts working on something, he knows what the structure of this thing will be. When I look at it, I will find various things on it. For example, when a body that is standard-defined defines the architecture of the database system, the benefit of this is that after that, many different companies will make database tools, that is, they will make DBMS. After that, whatever company will make DBMS, it will keep that architecture in mind. The benefit of this company will be that they will have the guidance that if we have to arm our product, our tool in the market to gain the popularity among the users, they will make it according to that architecture. And the users will also know that if they are told about something, that it is made up of standard architecture, the users will know that this will be necessary in this architecture. The way to use these things can be a little different. They can look different, but they will follow the basic architecture and its measurements. And this is an example of this. If you look at computers in real life, you look at television, you look at cars, then the first thing that will be made is the first standard architecture defined. The benefit of that is that the product, the car, the computer, is made up of one company. But when we talk about the spare parts, it is possible that you are making 10 different companies. So if they follow that standard, then it is certain that if you take the spare parts or the things that you are talking about, then they will fit in any other company. Why? Because the manufacturer also followed the standard and the spare parts that you are giving, they are also following the standard. So the standards, their definition and their acceptance are very important things. This is what all the efforts were going on, which we discussed in the history last time, that a standard architecture of a database should be defined. So in 1975, the recommendation of NC Spark was that it was accepted on the three-level architecture and that standard was accepted till date. And in today's lecture, we are going to discuss that same architecture. The three-level schema architecture explains this and defines that using a database, which level we look at the data, which level we define or which level we interact with. The three levels are not the concern of every user, because we had different types of users. Every user is concerned with their own level of data and its view. But the thing is that it has a lot of benefits that we will look at. What is the objective of defining three-level architecture? The basic thing is that it separates the user's views from the physical representation. Why? Why did you feel this? The reason for this is that the same data is seen by different users. For example, if you have a date of birth of a student or an employee, then how do you form a user group? We have read that different departments have different sections. Every department or section has its own users. We call them user groups. In fact, different groups are formed within a section. Depending on the job nature and authorization level, different groups are formed within a section. First, we have stored a student's date of birth. One user group wants a date to be two days, two months, and four years. One user group wants a month, a day, and a year. Another user group wants a year, a month, and a day. This means that the date of birth is the same. But if we talk about academic issues, the library section wants to see the same thing in one format. The examination also wants to see the same thing in another format. And the same is the case with the registration department. They want to see the same thing in another format. This means that we have different views of the same date. This is the reason. Secondly, when we have the same date with different views, what is the solution? Do we have to store each view separately? If we do this, we are once again going to the same basic problem that we found in the file processing environment. You are duplicating the data. And that can cause a lot of problems that we wanted to avoid. And that is why we shift it towards the database approach. So, your three-level architecture gives you an environment, a facility because of which you can store a consolidated view of all these different views. Practically speaking, if we talk about date of birth, you have stored it in one way. But when it comes to the different user groups, you do not force them to see a single view, a single type of date. Rather, you provide the facility in the format that you want to update. You look at it in the same format. And what we have divided in different levels of the database architecture, it hides it. And every user feels that the data is stored in the same way that I am looking at. But the underlying is that according to the requirements of all users, the data is stored in a single format. Apart from that, many times you have to change it in the data. You have to change it in the format of the data. What does this mean? It means that the format in which our users are looking at the data, if they look down, the way the data is practically stored in the database, you may need to change both of these things. Why? One reason may be that you have been given a new user group. And that user group demands the format of the same data in yet in another way. Or an existing user group changes its requirement. They say that they had seen the data before but that does not suit our requirement. So we want to change that format. Secondly, due to any reason, the data is practically stored, you want to change it. Again, let's say for the efficiency purposes, or maybe there is a new requirement according to which the format was not suitable. So you need to change the data the way it is practically stored or the way it is being seen by the user groups. By splitting into different levels, it is much more easier to make these changes. If those things are linked the way the store is being seen then if you change one thing to another, the effect would be on the other side. And the rest of the applications would be disturbed. This means that when we divide the three levels of architecture, all these manipulations become very easy. Let's move on to the next slide. On this screen, you can see the three-level architecture shown here. The bottom of the box is the database where the data is practically stored. These three layers are included in the three-level architecture. The bottom part is the internal schema. The internal schema handles the data practically how it is being stored. Its connection is with the physical implementation. And most of the time it is not the concern of the users. This thing is mainly handled by the DBMS itself. It is necessary that the DBMS can be handled in different ways. And DBA, mind it, only the DBA, not the end users. Your naive users or sophisticated users none of them but DBA has got the authority to guide the DBMS to tell the DBMS to adopt a particular implementation approach. But mainly it is the concern of the DBMS only. When we come to the conceptual schema this is a schema that presents an overall view of the database. And this is the level that provides a basis for all the different user views that are defined for the end users. And the schema on the top post level they are expressed in this diagram as different views. Because we have read earlier that different user groups are accessing the database according to their requirements. And it is concerned with the same data whether it is accessing or what it wants. The rest of the data is stored in which format and there is no connection with any user group. Now the three levels that we are seeing here are a logical partitioning, separation. One thing to keep in mind even in the books and during our discussion some terminologies will especially be heard interchangeably meaning on different occasions we are saying external view, external level external model and external schema in the same way if we apply this we will be using the terms like conceptual schema, conceptual view and conceptual model conceptual level they almost mean the same thing I mean when we say level, schema, model or view they all almost mean the same thing the minor set difference that you will realize in time and later when we use a word you will realize in context what our meaning is but otherwise the same meaning of all these terminologies we are talking about external view so external view is the way a user is looking at data external schema external schema is that is helping to implement the external view where the definition of the external view is stored when we are talking about external level we have that level where our users are viewing data when we are talking about external model this way we realize that our schema or view which is helping us to build external view so this means that all these terminologies they are used interchangeably or you should not be confused about the changing of the terms let's move to the next slide the architecture that consists of three different levels or layers it is depicted by different schemas that means we have internal schema we have conceptual schema and we have external schema keep in mind that you will feel that in some books the terminology used external schema is called external schema view level but when we are talking about middle layer conceptual and logical view or schema is used similarly when we come to bottom layer about that a physical or internal layer term is used in our discussion or during this course I will always use the conceptual schema for the middle layer for the bottom layer for most of the discussion you feel that internal and physical level or schema mean the same thing but there is a slight difference between them just to make your concepts clear I will discuss the difference between these two types of schemas here but for the general discussion you think you suppose that internal level or schema or physical level schema they mean the same thing this is very important point the architecture or the schemas in the architecture they refer to the permanent structure of the database or the intention of the database what do we mean by that see as I have discussed before there is one thing that we call the structure of the data and other is data itself and logically first you define what is the structure of the data and how do you define obviously the system that you are designing and developing in that system there are different actors there are entities they will look at each other they will model it from here you will identify what we have to store the data and in what format this is important this is called the intention of the database and this is something that is relatively permanent the schema of the database the intention of the database it does not change very frequently so first you define the intention of the database and then based on that intention on that structure then you feed the data into the database or we can say we populate the database and that populated database is called the extension of the database so now you know one is one thing is intention of the database that is relatively permanent and other is extension of the database in which changes are made very frequently and mind it this is not an uncontrolled nature of change no a change that you make even in the extension of the database that has to be controlled obviously you have to prove your authentication your authorization you have to have a solid reason to make a change in the database in the extension of the database but as far as intention of the database is concerned it is very serious because a slight change in the intention of the database may affect the entire database for example let us say you have got an organization database where let us say there are 10,000 records and in each record of the employee let us say we have stored 10 properties of the employees including name address and blah blah different things now we have got the intention where we define where we define where we said that we store name of type text we store age of type number we store date of birth of type date and like that this is intention then according to this intention we populated the data and we placed let us say 10,000 records in the database now this is a populated database where we got intention as well extension now see the difference between making a change in the extension of the database and the intention of the database let us say you want to change the experience of an employee let us say previously stored this experience was 10 years but then later he himself pointed out that my experience is not 10 years rather it is 12 years so you the person who is properly authorized for this purpose he opens the database and changes the experience from 10 to 12 this is the change in the extension of the database this will affect only one record whether legal or illegal but the thing is it has affected only one record out of let us say 10,000 now consider a change in the intention of the database as I said you before I told you before that we have defined 10 attributes for these employees now if you do the duty that you change in the intention what is the example of the change in the intention you do the duty you change the length of the property first you do the duty of the employee until 25 that means until 25 you store the character when did you feel that this 25 length is too much or it is too little for example you felt that you put it in 10,000 record and the maximum people in fact all the people in the name of 20 characters you said these 5 characters in each record these 5 spaces those 5 places you can understand that those 5 spaces are extra if you look at it then those 50 spaces are extra on this basis you decide that we do this that we reduce the length of this attribute instead of 20 the advantage is that you save 50,000 characters on 10,000 records now in the intention of the database you will change the length of this this change will be implemented on 10,000 records now in all records instead of the name 25 spaces are coming first you if you delete any employee's record on extension in that case only a single record will be deleted from the extension of the database but if you delete one attribute from the structure first you say that I delete the age then mind it 10,000 or 10,000 records will be deleted this means the purpose of telling you is that there is an extension of the database extension of the database or the intention of the database that is relatively permanent part of the database let's start discussing each level of the database architecture in detail the top post level of this architecture is the external level or external view external view represents the way each user group views the data this means that the different user groups are formed from two bases one is that there are different sections in the organization each department is formed by a different group and the second is that depending upon the job nature upon the responsibilities upon the role of a particular person or group of people different user groups are formed if we talk about a university then library examination and registration these are different departments and even within the library if you look at it then the librarian itself will have a different view the normal desk clerk will have a different view and mind it the views they do not only vary on what is included in the view but also on what type of oppression you can perform on the data for example the librarian and desk clerk have the same data for example we say the student's name, class name and how many books are issued they are included in the library and the desk clerk but there can be another difference that on this view on this data what type of oppression can be done and what type of oppression can be done for example if you want to delete a student first of all the books issued by a student there is no need or if you want to finish a fine imposed on a student generally the permission normally the people who are at the more responsible position you allow them people who are at the less responsible position you do not allow them so it is possible to change you do not give the desk clerk but you give the librarian if we talk about accounts it is possible to change even the right of a person to see you do not give the employee at a lower level but you give this account if we talk about our employees record then adding a new employee or removing a new employee it also depends on who you give this right so on this basis what data can be seen and what type of oppression can be done on this basis the views of different users are defined each user has a view of the database limited to the appropriate portion of the user's perspective of reality so the way a particular user group sees the data the way they want to see the way they are concerned the user view will be presented the rest of the database what is stored and who is accessing it there is no relation of a user group the user may have different views of the same data as I gave you an example of the date the name you have stored as a single string or as a single name for example, I said this data may have been stored but a user may say you show me the first name middle name and last name separately a user may say I only need the middle name the same data of different user groups you can give this facility because user views are represented independently in the three-level scheme of architecture virtual and calculated data this is also a very useful and important advantage of three-level architecture that the way the user wants to see the data it is not necessary to store the data in the same way for example a user needs the age of the employee now there are two possibilities one possibility is to store the age as such in the database you put an attribute of age and store the age the problem is an employee joins 2 years back and the age of the employee at that time is 32 years so 32 years was stored in the age attribute of the employee if you see that attribute, let us say after 3 years the age would still be there as 32 or you can change it but when and how would you change you will do it on a daily basis you will do it on a daily basis after a month, after a week whatever you do but the thing is today you have defined an additional activity for you either you are doing it yourself or there is some application program that you run periodically to make that change but the thing is there is an additional activity that you have to take care of the second possibility is instead of storing the date as such what you do, you store there as date of birth there are two advantages first, if there is a user group that wants to see the date of birth you simply show them the date of birth from the database second advantage is that a user group they want to include the age so you can do this when that user group accesses its view what you do you pick the age of date of birth from the database minor calculation what form of calculation according to today's date you calculate the age and show that age two benefits one advantage is that you do not have to store the age you store the date of birth for a user group the second advantage is that in this way you will always be getting the latest and most recent and the accurate age what is happening is that you are basically deriving the data or calculating from the data that is actually stored in the database so external views or three level architecture is also a benefit that you can show the derived or calculated data DBMS uses external views to create user interface for different user the facility and barrier yes that user group first you define views will define DBMS will define views for this user group this data will be included second thing is on that view you define an interface in that those attributes that are included in this view you place them in different ways the idea is that the end user that most of the time will be a naive user that user will not have problem that user will be friendly to understand that user to operate that user if you have much background in the computer in the use of computers it will not be a problem it will be nice to see this is a facility for the end user that you define an interface that is easily easily or in a good way that the eye and static will use but also it acts as a barrier that this user only can access this data if a user group if a librarian is accessing user's name, address, number of books you should find how many books you should find but also the data is in the same database that what is the CGPA what is the GPA how much is the fees how much is the fees how much is the fees all these facts although they are there in the database but because a user group is limited only to its own view it is stopping it from accessing it means that your external view it acts as a barrier that you cannot go ahead not only this but you can only because when I talk about the interface which is an application program I have defined for a user group the interface is related to a user group all the operations were there now when any user group has to do the operation then it will take the options given in the same interface to do different operations for example if the user group has a view then it is allowed to add a book how it will add the application program has an option in which if you want to add a book then use this option it will simply select that option and after that it will automatically guide it that if you want to add a book then take these stages it will assist you for example if there are some things that are fixed for example let's talk about topics we say what is the topic of a book when you enter a new book you have to enter this thing as well what is the topic and category of the book instead of saying that you remember all the categories of the book and you can see what categories to type what will happen that the programmer has created an interface there will be a list that this category is possible and the user's job will be to select the appropriate categories so overall if we sum up the user view it is a facility because it creates convenience for you as an end user and on the other hand it limits you that this is your boundary this is what you can see and this is what you can do or what you are saying user's external view is created after considering data access reports and the transaction needs i.e. all the requirements about data all of them create a view and the application program that is created all the requirements of that user group as i have given you an example that a student comes to issue a book to issue a book to return a book the data will not be defined in that view and using that data the application program will be shown if we say that we are a librarian we need a report that which books the student has received we need to see it on the screen and send it on the printer so your view will be defined with this point of view that the user's requirement will be defined by using this view it will be written in the program this means that your external view creates all kinds of data requirements in front of the user and the application program creates an interface for the user which your end user uses external schema evolves as user needs are modified over time it is not necessary that when you have defined the external view that is fixed forever because initially when you define the designer or the DBA is in the development process or the design process of the database at that time the designer or the DBA it interviews different user groups and keeping in view the requirements of all types the designer or the DBA it defines different views but the thing is the requirements may change after some time they may change or there may be some new requirements for the same user group for example if the first librarian defined his view he said that the name of the student is written by Congress later he felt that we should also know how much was fine on this student it is easier to estimate the student's attitude so he will say from the DBA or the designer to add this in our view a new attribute can be added still if there is any other requirement it can be added in this view which means if an external view is defined that is not fixed forever rather it can change as the requirements or the needs of the user group change in this slide I will try to summarize all the concepts that we have discussed so far about the external views in this slide you can see two users one of them is Selima and the other is Selima if you see the interface on the screen you can see both the users are looking at their views if you see Selima's view you have the heading which is Employee Data and it has First Name Last Name and Date of Birth Date of Birth is a typical format in which it is written 12th September 1970 similarly if you see Selima's view instead of breaking they have said give me the initial name and the main name that is why you can see Selima's view R.Slim Selima is looking Age in which it is written 25 years or 10 days and the third thing Selima is looking at Department these are the views in which it is written now the world of database is limited to this now Selima is not aware that Selima is breaking R.Slim and R.Slim or they are not aware that if they are looking 25 years or 10 days or years of date then Selima is looking at date of birth so both the users are concerned only with their own particular view and if you see the above is the external layer on which both the users are viewing and the block is basically a wall a layer there is a difference between the external layer and the layers below that means both layers conceptual layer or internal layer after this we discuss second layer that is called logical or conceptual view three level architecture both mean the same thing but we will use the conceptual view it is a complete description of the information content of the database i.e. these external views will describe the entire database and in one way this is the base of all external views i.e. all our external views come out of your conceptual view this represents the entire information structure and this is how the DBA sees the database see this shows the structure of the whole organization and most of the time it is only accessible and it is understandable only by the DBA because DBA is properly qualified technically skillful and secondly since it is part of the job nature it tries to have the view of all the data of the company the conceptual view is also called the community view of the data means overall view of the data the conceptual scheme includes all the entities all the attributes and the relationships are represented because your database design is the same because you are developing and designing all the entities are involved all the attributes and the relationships are to be identified and modeled when they are modeled and applied ultimately they are stored in the conceptual scheme so this means all the entities are shown in the conceptual scheme it contains record types representing entities data item types with their attributes relationship and constraints on data the record type we have already talked about that entities and their relationship and attributes there is a new thing that you can see on the slide it is constraints on data to design you impose different checks on the data and data will be valid data will be correct only if it is following those constraints for example we say if we have to declare a manager then the company needs the manager's age should be at least 40 years and the working experience should be at least 15 years now this thing will be stored in the schema and this will be as a constraint on this particular data item or attribute and then dbms will make it sure that whatever data you put in these attributes they fulfill these constraints so in one way the dbms provides you the facility it provides you the convenience to impose those constraints and those checks are also stored in the conceptual schema the conceptual schema contains semantic information about the data meaning security and integrity information like I discussed in the previous lecture that data itself may not be much useful to you the meaning content of the data what does it mean this thing is stored in the schema as I told you if you see that 5 is written then what does 5 mean 5 can be anything for example if 5 can be someone's age someone's anything can be so what is the meaning of the data this thing is stored in the schema similarly what we are talking about is security security with reference to data access that which user can access the data after access what operation can be done all this information is stored in your conceptual schema the things I have mentioned apart from these there are many other conceptual schema which we will read later but the things we are concerned about are only what we are discussing the conceptual schema is relatively constant designed with the present as well as the future needs of an organization we talked about that as far as external views are concerned there are chances that they change over time but conceptual schema is relatively constant there are few chances that you make changes in the conceptual schema the reason is when you decide when you design the conceptual schema this is basically the schema that is working for the entire organization that serves the needs of the entire organization and when you define the conceptual schema you do not only concentrate you do not only consider the current situation the current requirement of the organization but also future needs of the organization for example, next 5 to 10 years the requirements of the growth we also help them and in this regard an interesting example is the bug of Y2 you will remember before the arrival of the sun there were many such applications in which this problem was raised and after that it will not remain valid this is a problem that if a designer did not keep his conceptual schema in mind let's say there is a designer or a developer he developed a system in 1999 and at that time he did not keep in mind that in 2000 there will be such a situation that will be different so the designers who helped the future situation in the future their designs they were successful even after 2000 but in the designs there was no capacity in their designs they had to change and as I told you that changing the conceptual schema means a lot because one of its changes can cost you can affect you what does this cost mean when we read the data independence we will discuss it at that time but to understand at this time the concept schema there are no changes so as a designer this is also required from you as well that when you design the conceptual schema you should not only look at current requirements but also look at their future needs so once the database is developed deployed and populated at that time in the conceptual schema it could be very very crucial, very very difficult having discussed these points about the conceptual schema let's now summarize the functionality, the role of the conceptual schema and its relationship to the external schema that we have already studied once again we meet our Selim and Selima and their respective views now here we see the external layer and the conceptual layer the data is being fetched from the conceptual layer to the external layer and then shown to the users now if you see below the conceptual layer or at the conceptual layer this is how the data is being represented or being seen or you can say this is the practical view of the DBA this is how the database is actually being represented in the database here you see that we have stored name date of birth, depth means dependents and depth ID means department ID so if you see the Selima is seeing Rana Aslam as two different values first name and last name but practically we have stored Rana Aslam jointly so the dbms is picking is reading the data from the database as Rana Aslam but as per the requirement of the user the application program breaks it into two and presents it as Rana and Aslam Selim's view that it picks the Rana Aslam from the database and shows as R.Aslam to Selim then you see the date in the database we have stored date of birth in this format that we have stored first the day then month and then year but as per the requirement of the Selima what we did we picked 12009 70 from the database and when we showed it to the Selima we presented it as 12 September, 1970 and when you see towards the Selim's view we have shown there the age and practically age is not stored in the conceptual schema or in the database at all it is being derived it is being calculated from the date of birth another thing you see department attribute the department in the view of the Selim if you see practically in the conceptual layer in the record we have stored department ID and you will not find the department name in the record of Rana Aslam rather there is the ID of the department so basically it happens in the application program or through the external layer that we get the department ID from this record of Rana Aslam and then we access the department file and from there we pick the department name and show it in the Selim's view so this is how we relate different things at the conceptual view to the things at the external view. Dear students in today's lecture we discussed the database architecture the 3 level database architecture and we discussed in detail the top 2 layers of this architecture in the next lecture we will study the third or the bottom layer of the database architecture and then we will relate all these 3 layers together and following this 3 level architecture we will also study a very great benefit of this 3 level architecture that is data independence. I hope you have enjoyed today's lecture thank you very much and Allah Hafiz