 Assalamu alaikum dear students, today we are going to discuss lecture number 5 of the database management system course. As usual in the beginning, we will briefly review the topics that we covered in the previous lecture. In the previous lecture, we completed the discussion on the three-level architecture of the database. Then we discussed an outcome of the three-level architecture that was data independence. We also studied two different types of data independence which are logical data independence and physical data independence. After that, we discussed different functions of the DBMS. And at the end of the previous lecture, we discussed different environments of the DBMS, the environments in which DBMS is used. Today we are going to discuss the database application design approach or the design process. So let's start with today's lecture. The first topic that we are going to discuss is the database application development process. Dear students, let me explain a little bit about the database application development process. We can briefly talk about the database development process as well. The second topic is that the process or approach that I am going to talk about here in this course, this is not the only approach. This is not the only process. You will see that in the different books of the database, this process is not discussed in detail. The reason for this is that the main emphasis of the database course is to guide you on the tools and the methodologies that are directly related to design. And the different activities of the database are discussed. The books of the database do not discuss the development process of the database, the application development process. Because it is a very different topic and it has a different subject for it, which is software nearing. And software nearing is one or two or three subjects. It is included in the normal curriculum of BCS and MCS. But what I am telling you here in the process is also a valid and correct process. But the better processes are read in the course of software nearing, in the books. The processes that you read in the software nearing of the methodologies, the part that is related to the database development is read here. The emphasis of this course, the database course, is associated with the part of the system development where the database development is concerned. But for the completeness of this course, I will discuss the traditional approach of the system development so that all our courses are complete. Otherwise, you will keep in mind that this is not the only process. This is not the only approach. There are other approaches. They are better than this. But this is also valid. Another thing is that what do we mean by database application development process? This means that we develop the database for any system. And you will remember that the definitions that we discussed in the beginning were that a database model is a real-world system in the computer in the form of data. So the process is that when you represent the real-world system in the form of a database, this lecture will review the phases and steps of it. And the phases in which the database is directly related to the subject will be discussed in great detail. We will touch on the rest in detail. In today's lecture, we will discuss the first phase of the development process in today's lecture in the preliminary study of the system. What are the things involved in the database application development process? Let us look at it. Database design, application programs, and its implementation. These are the three things that I have just told you in the beginning. The books of the database are generally concerned with the emphasis on the first thing that is called database design. The things that are involved in your tools in this process are the emphasis on the books of the database. After that, the general characteristics of the database or the functions involved in it are discussed. The books of the database generally do not discuss the application programs and the implementation in such detail. Why? Because they are covered in the software engineering topic. Obviously, when we say that our database implements a real-world system in the form of a computer, if you have just the data of any system, obviously, it is of no use for the end-user, for the company, for the organization, for the system. Obviously, for the company, the application program that they are performing is of no use to them. That is more useful to them. The underlying database design that is being used does not have any concern for the end-user. Therefore, this is our database design. This is purely the concern of the designer or the DBA. In this course, as I have told you, for the sake of the completion of the course, the other two phases, the application program is a part of the implementation, I will discuss that as well. But again, our emphasis in this course would be on the database design thing. Now that we will study the database development process, basically, these three things are included. But our emphasis will be on those phases that discuss the first phase of the database design. Let us see. These are different things that we have already studied. There is a sort of a recap. A database design is a model of a particular real-world system. As I have given you the definition, I am giving you the reference that your database system designs a real-world system, models it, represents it, portrays it into the computer. It provides a picture of reality. The same thing should be simple and self-explanatory. This thing is being said about the database design. It should be that when someone sees it, it gets a picture of the system that it is representing. Another thing is that organizations keep coming and going. Let us say at some stage, if a DBA designer has designed something, it may be that the DBA designer is not there, and later on, someone who joins the organization wants to see it, understand it, or change something in it. For that, the design should be simple and self-explanatory so that they can understand it and use it. Let us move on. Now we will go to the database development process. In this, I am showing you a special approach to the database development process. The first is the preliminary study. The purpose of this phase is to understand the system, understand it, which you are designing and developing. This is the first and the basic step because without understanding the system, its functionality, process is involved. Without that, there is no question that you can develop a system. Obviously, this is the first step of any system development process. After that, once you have understood the working of the system, then you ask your user, your customer, your organization, what do you want from the database system? What output do you need from it? What are their requirements? What are their expectations? Understand those requirements and expectations and now you analyze them. Then you feel that to fulfill these requirements, what data and what relationship between that data should be represented? That helps you to build your database design. The application programs, activities they have to perform. But the data that is required to run those application programs, that would be provided, that would be fetched from the database. The database design should be the way that all the users and all their requirements meet efficiently. So, this process will develop an idea that will be through the requirement analysis phase. Next is database design. To fulfill those requirements, you will design the type of data you want. And our emphasis of this course is basically on this phase. How do we design the database and what are its different forms? In this course, our main emphasis is on this phase. But as I have said before, for the completeness of this course, I will discuss the rest of the phases. After that, what you have designed you have to implement on some DBMS. And that is what we call physical design. So, as its definition is clear, physical design that depends upon the particular DBMS or the implementation of your database. Implementation. Implementation is its definition and its description. But from our course reference, we will use implementation to write the application programs within this phase. Because we have already designed our database. We have designed it physically by adopting, by choosing different phases, making use of that data in different programs to perform those activities that are required by the users of the system or of the organization. So, implementation phase is basically we write the application programs. And finally is maintenance. Obviously, like your electronics or your vehicles or mechanical things, every machine, every system needs maintenance. As you can see, your database system needs maintenance. What does maintenance mean? It means that you observe its day-to-day working. And this is something that we have studied in the functions of the DBA. That the activities for which it is written and its requirements are being properly fulfilled or not. And secondly, if you want to make a new application because there is always demand of new applications. So, whenever the user says to write this application, that is part of maintenance. It is different that for a new application you have to run the entire process again. So, this is a particular approach which is the database development process. Similarly, a similar approach I will show you briefly in the next slide. We will analyze the requirements of the user again. The existing system and the requirements of the user are also included in these two things. After that, you develop a conceptual model. This is something that was not discussed in the previous slide. But this step, developed conceptual model, which I said in the design phase in the last screen, this thing is included in it. You have to understand that the first two phases which I said preliminary study and the second was analyze user requirements. They both were included in the first phase, analyze user environment. The database design is included in the development conceptual model. There are some things that are showing here that your conceptual model when you develop it, it is quite possible that during this phase you have to go back to the previous phase. You meet the user again and ask them what are your requirements? Explain them or discuss them again. That is why an arrow has been shown from again the development conceptual model on the environment analysis. Next phase is map conceptual model to the logical model. Again, this phase is also a part of the database design phase of the previous slide. This means that what we said in the database design was two phases. One was conceptual model and one was logical model. And these are the things that we are going to discuss in very detail because this is the crux of this course. This is the core of this course. How do we develop a conceptual model? What happens? How do we develop a logical model? After that you select a DBA. In the last slide you will see straight away physical design. As I told you earlier physical design depends upon the particular DBMS. The phase is shown explicitly that after developing a logical model you select a particular DBMS. Next is again developed physical design. The DBMS you have selected you will use your database for physical design. In this process there may be some requirement there may be need to go back to the conceptual model level to make the things confirm or to clarify any other ambiguity that may arise during the physical database design. Next is that you implement your system and again it involves writing the application programs. And in that you can do this activity in parallel. By using your DBMS you are designing your physical database in parallel with some dummy data with dummy design to start the application program. But it often happens that this activity is after the physical database design. Finally you test the system and after the test when you found the system working properly then you implement the system which you developed. But as I said the routine maintenance is the same thing that if there is any problem when you deploy it or implement it then again you may have to face it quickly or thoroughly from the first phase. Secondly, if there is a requirement of a new application then your existing system is running your developed system is running and to enhance it to change it you may have to go to the first phase again and from there you have to restart this process. So here we have briefly discussed the development process and mind it this process is correct it is applicable but the better the process you will study other courses and you will adopt it as a development process but the design part that we are studying here will be applicable there only. Let us go to our next slide. In this now we are going to discuss the first phase of our database development process and that is that you have to analyze the existing system. What do we mean by existing system? Existing system means that system that has been told to you to computerize that system that has been told to you for example you are told that there is a bank you make the entire database there can be many different systems for example there is a different system of loans the same way your accounts are different these are all different systems. Now the possibility is that either the company or the organization or your client can say that we design a particular system for example when we open our client's handling system we just make the database. The second possibility is that we automate the entire database. So either way the first thing is you have to understand how the existing system is working. Another possibility of this can be that the first part he was working on first he made two or three systems he said that he wants all of them. So did you want to patch them or attach them or make all of them Anyway in any case you have to understand the existing system and its working. So the first phase is the same objective that you understand its working and after that you have to understand the user's requirements because on this basis you have to understand it. Now we are going to discuss the preliminary study phase which tool we use and how we use it. But the first question is why do we use the tool? The tool we use basically is the data flow diagram and briefries are also called DFD. Through DFD the existing system you can use it to describe the functioning or the flow of the data Why is it needed? Why do we show it graphically? The reason is when you represent something through graphics or symbols those graphics and symbols they have got some specific meaning they are simple figures very few in number and you use them to describe your system and by keeping it in front of you you discuss the working of the system with the user of that system with the organization because it is necessary that you both agree on the things that is what the system is doing they have explained it to you but we have to make it sure that you have understood what they have described that is why they describe in the form of paragraph they may mean different to different people that is what I am talking about that can mean something else that is what I want to convey but it can mean something else to the listener especially in technical matters if we want to convey what we want to convey if we want to convey then if we keep moving then overall the picture will be very different from the actual picture that is why this is recommended that the understanding of the system is expressed using these graphical symbols we will keep them in front of us and while looking at them the user who is using the system will tell them that this system has this process how these people are involved and one thing is that this tool the DFD is so simple even the user a common user can understand it that very well and if he cannot understand then there is no need to understand it you draw it make it explain the working of the existing system using this tool and while keeping it in front one benefit of this is that it gives you a discussion point it gives you a central point on the basis of which you discuss your understanding with the user of that system one thing is this second benefit of this is that if someone comes to your place to do a job they will take over and they want to change that system so it will have to understand the working of this system so the DFD this will be able to explain it working of this system so this is the reason this is the need to use the tool to explain the working of the system let us see what the DFD is data flow diagrams the DFD represents the flow of data between different processes within a system any system you take different activities are performed different processes are involved now the process is the precise definition in the terminology of DFD we will read ahead but in general you can think that the activities that are performed in any system if you talk about a banking system then the account opening is working there there is a system of account handling to open the account to get money from the bank or to collect money these are different processes that are involved in the system in the banking system so the data flow diagram the DFD tells that in a system different processes are involved and in that people are involved and the data that flows here the information that flows here how does it work these are different characteristics of the DFD it is written that simple and intuitive not focusing on details is simple, there are not many symbols so that it does not get too complex and it describes what users do rather than what computers do, the processes in which the users are involved mention them the limitations of this are that it only focuses on the data flow the basis on which you decide that there is a decision for example, there is a process or this process you will show that after this process there will be one of these two processes but what is the decision or that one what is the decision that you do not represent in the DFD after that the different symbols of the DFD are there which symbols do you use the first symbol is data flow what is the purpose of this these are the pipelines through which packets of information flow and data flows are represented with the help of arrows arrows are labeled with the name of the data that moves through and this is the symbol of a data flow there is a head on which the arrow's head the data is moving in this direction from the tail of this arrow the data is moving towards the head of the arrow and the second thing is you will label this arrow label it so that the data flow is what is the data the second symbol is data store data store is basically a store a collection a repository where the data is stored static data means that this data is not frequently changed it is fixed this means that the data stores you store the data that you want to use for processing if you use it only in a process if there is no future use you will not store it but the data that is likely to be used in future for further processing you will store it in data stores or files and this is the name which is normally a noun and this is the symbol that you use for the data store after that your process the process the process could be very little very small activity or it could be a huge big process so any activity where the data changes from one form to another form and it is not necessary that there is a big change in the data for example if your data reaches a form in a process somewhere in a section and in that section they only checked if the form is correct or not if the form is correct they transferred the form to another section although this section did not add anything did not change the form but at least they have verified that the data is correct and the data you had before now it has become a verified data so at least it has changed this can also be called a process it is not necessary that the process changes or adds any activity, any process on the data where the data changes its form this is a process and how do we represent it we represent it either with a circle or a round an edge rectangle as you can see you can use any symbol but normally it is not recommended that you make a diagram and use both of them it is not possible you can choose one of them which is convenient for you and use the same symbol in all the diagrams when you use this process symbol in DFT in that each process has a number a number is given on it what is the principle of that number how do we do the numbering we will read this later in that you do not add any name it can be a verb or a small phrase but obviously the whole story cannot be there it could be 2 or 3 or 4 words and that is all so you have to identify the process and when you have a high level system then you can mark it as non we will look at this example and this is an example how we write the number in a process symbol and the name of the process where the process is written you will write the name of the process and where the number is 1.0 you will write the number of the process but we will read the numbering technique later apart from this there is another symbol in DFT which is called external entities external entities we call the source and destination of data that is your external entity either generates data or accepts data apart from this there is another question why do we call them external entities in that normally we suppose by making DFT we consider the system as something that is serving a particular purpose for example if we talk about an educational system then the activity processing of the admission system we consider it as a system after that anything interacts with it whether it receives data from it or any part of the system provides the activity of that system we call them external entities this means the word external entity does not mean that it will be something outside of your system or outside of that organization anything it can be an entity it can be something else it can be another system which is going to understand our system and ultimately what we have to develop is that data either provides or accepts data that is called an external entity the symbol we use is a rectangle apart from this another symbol used in DFT is a collector collector does not have any processing it only uses where we have to show multiple directions and from that it only goes in one direction there is no processing involved it just collects data from multiple directions from multiple places from multiple symbols and then it transfers the data towards in a single direction and the symbol used for this is this the round side on this side you will place multiple arrows maybe 2, 3 or more and the flat side or any other entity will go from here the same way separator does no processing but if you want to accept data from one side and want to place it into multiple directions then you use the separator symbol and this is the same the flat side it will receive data from one side and the round side will transfer the data then it is a ring sum operator this is a operator that will show where if there are multiple directions and from those directions it will flow data in one direction in which it will flow that is now the concern in the DFT but where it shows that these are multiple directions and for example it can be placed in front of a separator it can be placed in front of a separator but if it is going in multiple directions but if it goes in one of those multiple directions then along with that you place this symbol the same way the AND operator means that you show multiple arrows or data flows and when you want to show that data will be moved in both the directions so between those arrows you place this symbol that these are the two directions and from these the data will flow dear students so far we have studied different symbols that we use in the DFT now we are going to discuss what are different types of DFTs there are basically three types of DFTs and obviously there is a specific purpose of developing each one of them the three types are level 0 level 0 level 0 and the third type is the detail diagram now we are going to study what are these diagrams what are the purposes and how do we make them context diagram context diagram has a single process or a single system or a single process depends on the system or you can understand that the whole system as a whole, as a single process or a single circle or single bubble is used and no process is involved no data store no symbol is involved but a single process single system and its interaction with the external entities the context diagram shows the system as a whole plus its interaction with the external entities no processes no data stores in the context diagram and the name is written as a noun or a phrase it shows context only input and outputs but the inputs and outputs are only from the external entities obviously external entities are not on the data storage and one thing is when you are developing the context diagram then you will not show any data flow between the external entities themselves every external entity it will interact only with the system any data flow will be out of an external entity to the system or system to the external entity but not between two external entities here is an example of a context diagram as you can see there is a symbol which is a process symbol here you are seeing the name of the system that is being discussed that has to be developed this system is your purchasing system apart from this there are three different entities which are suppliers suppliers that could be a single person material planning department the entire department as an entity is shown here it is not a person it is an entity the receiving department is not a person it is a complete department it means anything that provides data to the system or receives data from the system is considered as an external entity in the terminology of the DFT apart from this you will be seeing data flows now your external entity is either providing the system or receiving it for example if we look at supplier entity supplier entity provides an invoice this is a form of data that flows from the external entity supplier to the system the system is shown as a complete system as a whole the system provides to the supplier it orders the second thing is that it generates payment from the system one thing if you look at where the purchasing system orders it and the payment is going but the purchasing system does not receive items from the supplier that means the supplier will give the purchasing system but the supplier will not supply the items although the supplier is receiving order from the purchasing system but it is not supplying the items to the purchasing system the payment to the supplier is being made by the purchasing system when you study the system you will realize the particular system that we are considering now what are the functionalities that are performed by this system if you see here it is shown as receiving department now receiving department it shows confirmation of receipts it gives the purchasing system what does it mean the supplier that supplies items to the receiving department and the receiving department we said that the two entities cannot come directly to the data flow in the context diagram so it was not shown here but it is possible that you are getting the understanding of this system that your receiving department that will give a confirmation of receiving to the purchasing system and on the basis of that the purchasing system will generate the payment will issue the payment to the supplier if you see the activity of the purchasing system from where your material planning department that is the department that receives the requirements from different departments and that the purchasing system sends the requirement of the material and after that the purchasing system starts its activity with the supplier this was an example of the context diagram let me show you another example of the context diagram in this diagram a university's admission system as you can see through a process symbol or bubble you have shown the system itself apart from that external entities interact with the system for example if you look at the dean entity the dean entity asks for faculty load sheets i.e. what is the workload of each faculty member similarly the dean entity requires the class count the class in which you have admission how many students are there if you look at the faculty members that is also an entity so from that admission system it needs the list of the class the class in which the faculty member has to teach how many students are there your admission system will supply the faculty members if you look at the registrar he will tell the admission system that which course we are going to offer in this semester and similarly the students' data will provide them similarly the students will request your admission system to enroll in the class and similarly your admission system will be accepted by the students so these are the two examples of the context diagram and you can see the basic purpose of the context diagram is that the functionality of the system from this point of view the external entities from which it interacts and the data flows between the system and entities Dear students we have studied the context diagram after this we will discuss the second type of context diagram called level 0 or level 0 diagram it represents the entire system and remember the system in which you make the DFT level 0 diagram will be included because this entire system represents the working level 0 diagram it is possible that there is a system that does not involve any detailed diagram but the context diagram and the level 0 diagram in the very extreme cases I won't recommend it but really if you have to avoid you can avoid context diagram even but without the level 0 diagram there is no DFT now the thing is that the level 0 diagram it represents the entire system its processes its entities all of these are represented now there is a technical question the depth the detail in the level 0 diagram what should be two things 1. the size the volume of the system it depends on that 2. on that designer who is developing this diagram because if if you have a diagram of the system for two different people the level 0 diagram the depth the detail the same if there are two different systems and the same person is developing the level 0 diagram for two different systems the level of depth will be different you have to maintain a balance what do you mean that if you have a very big system and if you try to put too much detail in the level 0 diagram this will become very complex then it will become difficult to understand on the other hand if you reduce it there will be two losses one loss is that the diagram will not portray enough information it will be much useful the other thing is that against the detail diagram you will have to keep a lot of depth so there is really no hard and fast rule that the level 0 diagram how much detail should be how much material should be what should be the size it is totally dependent on the system understanding that diagram let us discuss what are the ways to make the level 0 diagram what is its methodology the first phase to make the level 0 diagram is that the diagram you are going to make you can identify different modules and you start showing those modules repeatedly then in the appropriate places there will be a language in those modules how can that link be created it can be through entities, processes through data stores it can be in any way so the choice you make is that you first draw the entities involved after that write the processes involved and then link them and in this way the data stores involved you can make them and when a module is represented after that you take the second module the third one in addition what we have to keep in mind is that what we write the process number in the symbol of processes there is a special format its format is you give it a number like 1, 2, 3, 4 you give it a number and after a decimal you give it 0 that is your level 0 level 0 diagram the process you have always 1.0, 2.0, 3.0 in this way there will be a numbering it depends on whatever process you have represented in level 0 diagram there will be some processes that you have shown but the detail is too much if you show the detail of that process in level 0 diagram it will have the same problem that the diagram will be very complex which will be difficult to understand so you do that the big process with a single bubble with a single circle you show your level 0 diagram but the detail of that process you explain it further in the detailed diagram and one more thing is that you can have just one context diagram for any system you can have just one level 0 diagram but you can have as many detailed diagrams as you like as you need in a system as you told that you have to label your data flows and one thing is that whatever data flows you show you will never show external entity to external entity normally it will be that you generate data from an external entity it goes into some process and if data is transferred to another external entity or any data store it can be that you can transfer data from one process directly to another without sending it to any other external entity this is a general rule which I have explained how we make a level 0 diagram this diagram is an admission system of the University we make a module for this we identify application or applying one module the second module we identify is processing of the applications and the third module we identify is generating the admission list so we identify these three modules first we represent application process module as you can see we represent this module applying application process this module we are showing how our processes are involved and finally this module which displays or issues and how different modules are linked at different places and they have represented a whole system and especially the number of processes note that it is from 1.0, 2.0 and in the level 0 diagram the process number will always be in the same format Dear students I have shown you the example of level 0 diagram you must have understood about the level 0 diagram or in general about DFDs keep in mind that there are no rules or criteria for DFDs the rules to make DFDs whatever diagrams you have the basic objective is that they are self-explanatory first you are self-clean you are representing what you can understand about the system the second thing is that if you see any other person or any other person you should understand what you have shown the purpose of DFD is to explain the processes of the system in today's lecture we have discussed the processes we discussed them keep a special process in mind apart from that the first phase we discussed the requirement analysis and the tool DFD we discussed the two types of diagrams context diagram and level 0 diagram in the next lecture we will discuss the DFDs see you in the next lecture Allah Hafiz