 Assalamu alaikum. Dear students, today we are going to discuss lecture number 6 of the database management course. In today's lecture we are going to continue our discussion on the data flow diagrams. After that we will move to the next phase of the database design process and that is design phase. We will study what are the different design types and we also study different types of tools that we use for the design purposes. So let us start our discussion today. In the previous lecture we were discussing context diagram that is here is a context diagram and you can recall your concepts your thoughts about this because there is a central process a big process showing the entire system then there are different external entities and there are different data flows that are providing data to the system or receiving data from the system. Another data flow diagram is here again the symbol used for the central system or the process is different otherwise rest of the concepts are the same. In the previous lecture we also discussed about the level 0 diagram or level 0 diagram which is here this is also a level 0 diagram. This diagram represents the system as a whole and also note the process numbers. I suggested in the previous lecture that the process number in the level 0 diagram are 1.0, 2.0 but in this diagram you see that the numbers are 1 point and there is no number after the point. This is also perfectly fine different authors use different notations for this purpose but both are correct but the main thing is there is no non-zero number after the decimal either if it is 0 or there is no digit but in the level 0 diagram this is also 1, 2, 3 whether 1.0, 2.0, 3.0 or 1.2, 3.0. So now we are going to discuss the detailed DFD. This is the third type of data flow diagram. The detailed DFD is an optional thing you may need the detailed DFD in some system and you may not need in some of them mainly you use the detailed DFD in the system that are relatively large size. Again the large or small it depends upon the person who is drawing the diagram for one person system could be small for other it could be large. So for the same system one person could be drawing only the context diagram and the level 0 diagram but another person for the same system could be drawing context diagram, level 0 diagram and also the detailed diagram is totally up to the people up to the person up to the team drawing the diagram. Every perspective every approach is correct. So mind it this is up to you whether you need the detailed diagram in a system or not. In the detailed diagram you show the processes in the successive level of detail that is if there is a process that needs explanation or further detail that we have shown in the level 0 diagram then you will show the detail of that process in the detailed DFD. Again the same thing it shows major sub processes it means the process for you for which you think that this process involves other sub processes you will draw the detailed DFD for only those processes. The numbering is of this type as you can see 1.1, 1.2 when you place a number 1.1 in a process it means certain things 1 it is a level 1 detail of a process that was in the level 0 diagram because 1 dot the one before the decimal point it shows the main process of which this detailed this detailed diagram or this sub process is the explanation and point 1 shows this is the first sub process of the process 1. Likewise when you have 1.2 it means again that it is the second sub process of the process 1. So if you are elaborating a process number 1 then you will use the process numbers 1.1, 1.2 and as many numbers as you want to show the sub processes in this detailed DFD. Likewise if you are giving the detailed DFD of a process that is number 2 then you will give the number to the sub processes as 2.1, 2.2, 2.3 mind it. The sub process is called the sub process because it is in the detailed DFD otherwise it is just like a normal process. The only difference being the process in the level 0 diagram it involves too much you can say steps activities or processes actions and you want to explain that you are going to explain those processes those steps in the detailed DFD. The same process can continue up to as many depth as you like or as may the size of the system. For example if you can have further details of the sub process 1.1. So if you are going to give the detail of the process 1.1 then you will make another detailed DFD and that will contain the process number 1.1.1 it means this process is the explanation of is the sub process of the process 1.1 which was the detail of the process 1.0. Likewise if you want to give the explanation of a process number 3.3 then you will number it in the detailed DFD that would be detailed DFD of level 2 you would say 3.3.1, 3.3.2 and that like that. So this is how you number the processes in the detailed diagram and as you have known by now that detailed DFD can be of different levels of level 1, level 2, level 3. How can you know that of what level this particular detailed DFD is? You can also guess it from the process number that you are writing in the process symbol. For example if you look at 1.1.2 it contains 3 digits this process number it means that it is a detailed DFD of level 2 likewise if you write 3.2.1.4 it means this is a process in the detailed DFD of level 3. Let us move to the next slide. These are some recommendations that you apply to the detailed DFD and these also apply to the other levels of DFD as well like on the level node diagram and on the context diagram. First thing is limit number of processes to 7 to 9 per page or diagram for clarity. It is the same rule that I discussed before that you not make your diagram too much congested or not too much simple. It should be easy to grasp easy to understand if you put just one site on the diagram it conveys some meaning to you and as you keep on thinking on that as you focus on that it gives more and more information to you. So it should not be too complex it should not be too simple. All elements must be named all elements means the processes the external entities the data flows all of them even the data stores all of them should be named mind it. This applies to both the level node diagram and detailed diagram that all processes should have the input as well as output. There is no use of sending just an input to a process when there is no output. Why are you sending the data to that process obviously for certain processes for certain activity but after that activity the data must change its form. So this means that a process should have a data flow of at least one and one should go outside. Obviously output should be different from input as soon as the data is coming it is coming then what is the process doing? And another thing is that when you are saying that we have a level node diagram it is a process. It has some input or some output. If you are giving the detail of that process next time we are giving the diagram then that thing should also be reflected in the detailed diagram. So the input and output of your main process are the sub processes in which you are going to give the details and that input and output should be shown. That is further. So what you are seeing on this screen is a detailed DFT example. The level node diagram that we saw in the student registration system has a process number 1.0 that was a request process. Now this is a detailed DFT. In this you are seeing that the student external entity has a data flow of a class that means the student will request that I should be enrolled in a class. In this we have a process 1.1. You can call it a sub process because it is included in the detailed DFT. And 1.1 is showing that this is a sub process of the process 1.0 that was in the level node diagram. After that obviously when your added request sub process it will definitely the people that they would check the form whether it is filled in rightly or not. Or if it is correctly filled in if the students are eligible for the admission. So they would pass that data as valid request to the next sub process that is 1.2 and check counts. This means that in this process you will check whether we have space whether we have place in the class or not. The class for which the student has applied for the admission. Obviously the people in the working in this process working in this area or department they would need some data and what that data will be that data will be old class data and that they will fetch from the data store called class registration. In this data store we have the different classes. Their data stored means how many students are in every class. From that data store the data fetch will be in our 1.2 sub process. And after that they will see whether this request of the student can be accepted or not. They would transfer as accepted request to another process 1.4. What the 1.4 sub process is doing is that it is doing the data of your class because now recently you have given admission to another student. So the class in which first there were let's say 35 students now you have included another student in that class. Obviously you will have to modify. You will have to update your class data. So what will happen is that this process will be activated and your class registration data store will update the data. With this your 1.2 will update the student's data. This student's record will be included in the student's registration data store. In this you can see that these are some details that are mentioned in this diagram. But obviously there are many things that you can yourself think that you can add into this diagram. For example, the 1.1 is added request. The valid request they are moved to the sub process 1.2. But what about invalid request? As we have discussed before that the DFT it will not discuss what is the decision base. On what basis you are making a decision. But the thing is the data will flow towards the sub process 1.2 if the request is valid. But what will happen if the request is invalid? Obviously there could be many solutions to that. For example, at least you would inform the student that your request was not valid. Same is the case in case of the accepted student. First khe jo uski application thi wo valid thi. Application of that student to the check counts process. Let us suppose there is no place in the class. What would you do? Agar to thi ke klas mein jaga thi. Aap ne usko jaga bhi de di. Aap ne uska data jo hai wo student ke store mein bhi add kar diya. Aap ne class case mein bhi add kar diya. But what about if the student cannot be admitted because there are no room in the class. U situation mein kya hoga. Again you will have to inform the student. To khe nahi nga matlab hi hai ke iss diagram hai wo basically iss ka purpose juh hai wo sahi aagalat ki baat nahi hai. Asal chis ji hai ke jis level ki depth, jis level ki detail you need to develop a system to understand a system, rather wo aap se shamil karein. There is no question of being it right or wrong. I hope you have got my point. Data dictionary is a repository. It is a place where you store the information. Aur iss me juh database ka design hai, uske baare aap ke paas information hoti hai. Database ko juh design hai, juh database ka structure hai that we have already called the schema. Iska matlab hi hai ke aap yeh kaya sakte hai ke the data dictionary, it contains the schema. Iske lau iss me aur bossa information bhi ho sakti hai. Data dictionary ki mukhlef types hoti hai. It could be integrated or it could be freestanding. Let us see what is the difference between the two. The integrated data dictionary is generally a part of a DBMS. It means as we have already discussed the DBMS deals at three levels with the data and every level is described with the help of a schema. To juh DBMS hai usko apne functioning ke liye it needs schema. It stores the structure of the database in the schema and that schema is in the data dictionary. Jisko hum kayaateh integrated data dictionary that is a part of the DBMS juh kisko usko isthimal karta hai different operations ke liye different accesses bgara ke liye. Dekin iss ke lau ba, huma rahe paas ek aur tarah ki juh data dictionary hai wo aaj kal isthimal hoti hai aur wo unne hum kayaateh hai they are like case tools. Case se banta hai computer assisted rather computer added software nearing. Aap ke paas market mein ase tools available hai ke juh aap se different data juh hai wo liye different type of information jis system ke liye aap database design karne jaar hai develop karne jaar hai. Us system ke baare mein different aap se information liye aur wo aapko help karthi hai. They assist you in designing a database. Balki juh smart kisame ki tools hai wo aapko application development mein bhi help karthi hai. So, juh data dictionaries hai wo hi do tarah ki hoti hai. Basic purpose juh hai data dictionary ka wo yahi hai ki it stores the metadata. Chahe wo integrated aapki data dictionary ho chahe wo freestanding ho. Let's move forward. Cross reference matrix. Cross reference matrix juh hai wo data dictionary mein isthimal hone wala ek tool hai aur jihaan meri juh data dictionary se murad wo ek integrated data dictionary nahi hai balki jis ko haum ne khata freestanding data dictionary isse meri murad wo hai. Ye juh iska purpose wo ye hai ke ye juh mukhulif cheezin database design ke duran ya system study ke duran isthimal hoti hai unko aapas mein link karthi hai. Misal ke tohre pe When you are gathering the requirements of the users aapko padaj ala ke mukhulif users hai unka ya ek user group hai unka taluk in-in transactions ke saath hai in-in reports ke saath hai ya wo in-in documents ke wo deal krta hai. Aap ne ek apne juh hai understanding ke liye ek chizoh ko link karne ke liye you want to see ke ye juh ek particular transaction hain ya particular report hain isme korn korn se attribute se involve hain korn korn se isme information chahiye to isna se aap ek matrix maalate hain jisme ek side par ke let's say column side pe aap kain hain ke hain hain juh different transactions hain ya reports hain ya documents hain juh different activities hain juh iska row wise hain iske aap kya karthi hain aap isme different attributes aap ne mohaap identify kyi hain aap identify nahin kyi hain maal ke har juh aapka user group tha usne bataya ke muze ish report me flan flan chizi. usne bataya ke naam chahiye uski date of birth chahiye uski juh experience aap wo chahiye aur last job juh usne ki thi mujhe wo chahiye to these are the items these are the attributes that are required for this report aap se hi ek aur ko transaction hain let's se ek user group hain wo ne employers ka data add krta so that would be a transaction adding a new employee wo usme employer ki tamam personal detail, wo usme hosakta 10-15 muthif data items ho isse hi tra employer ki koalifikation hain uski 10-15 nara item ho and like that to iska ra se ye tamam juh jitne bhi chahiye aap ko chahiye juh transactions thi reports thi, documents thi aur iss traf jobs pe data item aap usko likke uske baat pe aap usko matrix aap wo apir ho raha hai usse cheezon ko aap ko link kar rahe me madad milti hain aur aap ko se ye aandaza hota hain aap ko kis jaga pe kis screen pe kya chis chahiye juh aap ki ye cross reference matrix hain it cannot be used only with the help of a data dictionary baat ke usko aap manually bhi kr sakta hain ho usakta hain aap agli screen pe me aap ko juh dikhaha raha ho iss hi traf ki cross reference matrix hain juh ke basically hain transactions, reports, and documents ke against aap leh rahe hain different data items ko iss hi traf ki cross reference matrix hain wo bina sakta hain different functions ko different entities kr sakta hain iss hi traf ki different active functions hain wo kithne critical hain iss hi aap relate kr sakta hain to agli page bhi dekhahi iss screen pe agar aap dekhahi to you are seeing a matrix here the rows in the matrix contain the attribute names or different data items and the columns they contain the names of different reports or transactions or documents this is recommended that you place the attributes or data items in the alphabetical order or it is easy to arrange if you look here the attributes first are books to faculty members i.e. a particular report or user that how many faculty members have been book issue similarly, one more is books to students another data item attribute is faculty name one more is faculty address number of faculty members number of students or student name these are some example attributes data items that different user groups want on their reports or transactions in the columns you see there are different items like books issued class list courses offered or all faculty these are some example activities this step dekhahi jasem ne kaha ke books to faculty or books to students they are ticked this means that the report or the transaction they need this information that how many faculty members have been book issue and how many students have been book issue similarly you see a class list in this class list faculty name which teacher will teach number of students and student names and same is the case with courses offered and all faculty different attributes are ticked different attributes are used and conversely what attributes are required the cross reference matrix can be used as I said business functions to entity i.e. functions which entities are used like business critical success factors the different activities that are being done in your organization which are critical for business nature so that you be more careful while designing the system and while developing the database this is a tool for you that can help you identify different critical items attributes and entities and use it in the next phase dear students till now what we have read about the database design process the first phase which we have called analyze user requirements till now we have covered or another scheme preliminary study plus requirement analysis both phases the basic objective is to understand our existing system and the requirements of the users to understand that the system is developing what are the expectations of each user group we have read that it is recommended to use the tools wherever possible whether it is case tools and we have read the DFDs i.e. the data flow diagrams and i have also recommended to understand the user requirements to understand the different activities they will help you in the later stage what you see on the screen the first phase is its summary now let's move on to the next phase database design phase dear students when you look at different books in that in the same environment in the same situation different terms are used I want to use the terms which we will use in this course in the reference of design to clarify what we mean the first thing for our purpose design and model means the same thing in fact database design and database model they both mean the same thing there is a difference between the database model you should be clear about this I will explain it to you later but you should have in mind that these are two different things but database design and database model both mean the same thing let's move on database design or the database model it represents the logical structure of the database when we talk about logical structure why we call it the logical structure we have discussed in the definition how do the things relate there is some logic there is some reason there is some context behind that puts the things together the structure of the database due to that context the design of the database represents it you must have heard a lot about which they represent the structure of the database for example in the reference of the schema in the reference of the data dictionary in the reference of the design these all things are related to each other for example design is the structure but that is stored in the schema and schema is practically stored in the data dictionary so you relate these things in the database database modeling the process of creating a logical representation of the structure of the database obviously the thing we are going to develop the database for that thing the need of the data the need of the data design and structure the process is called database modeling this is the most important most technical part in the development process be careful very clear about the steps in this process again like we discussed in one definition a database must mirror the real world the system of the real world that we are going to represent only then can it answer questions about the real world the expectations of users the things that they want from the system the reports, the transaction the output, the activities the user's expectations so even you can give them when you write an application when you are going to implement it when the related data is present in your design and it is properly linked so you have to be careful again the same thing the emphasis of data modeling is on representing reality fine ideally it should be represented graphically as I told you earlier the data flow diagram that through symbols through graphic symbols through figures you can communicate things more precisely because the descriptive thing they can have different meanings different people because they involve the perception of the person receiving or listening if something is written even if two people read the things described in the natural language they can interpret it according to their mind according to their understanding so it is recommended in the design that you represent it graphically because that has got the precise meaning so when you read different tools or different 3k of database design you will see that the first design the complete design we will make it graphically then we will transform it then it will go to other things or will be implemented but the first design that would be a graphical representation the goal is to identify the facts to be stored in the database this is also clear and the process of data modeling is not that the designer the developer will sit in a room and turn off the light and concentrate in the darkness and think what should I design because it involves the interaction between the user who are using the system and obviously the designer or the analyst database design is created using a data model when I said that in the database model or in the data model you have to be clear about that what is a data model data model is basically a set of tools that constructs that you use to design to develop a database or you can say to make a database or design you use a data model as you can see as you have a carpenter a big one he has his own tools he has his own tools he has some things he uses these things he can make a base he can make a chair he can make a door so you have to understand that the tools they resemble the data model and the end product that he is designing is the database design this means that you can use a data model to design any database so again so that you can see the difference that we use a data model to develop a database design or database model let's go ahead what are the components of a data model the first component is the structures the structure means that the data model provides you what structures that means how you can store the data how you can store the data the second component of a data model is manipulation language manipulation language means that if you use this data model and you represent the data then how you can manipulate it meaning storing and retrieving the data obviously you need some mean for that that tool that means basically the manipulation language similarly the third component is integrity constraints that means your data model supports integrity constraints integrity constraints what does it mean integrity constraints have such rules such checks that ensure the correctness of the data when you apply them then the data will be in that form which is being governed under those rules and this becomes the responsibility of the data model to provide you how you can maintain the integrity of the data but keep in mind that what we have said is that these three components of the data model do not get confused with it because all the data models that we will study and those that we will not study and may be in some book in some place you will not find these three components in each data model because this definition or some data models that I will tell you that on the basis of which you have dbms so about those data models you will clearly see these components but on all the data models you will not see these components so keep in mind that these three components I will also tell you about a data model that these three components are present but do not try to find all these three components in every data model because you will not find it do not get confused what is the significance of the data model Dear students understanding the data model is very important that means the data model basically gives you guidelines gives you facilities gives you constraints that when you are in the process of designing the data model then what things you should do what things you should not do what is available what is not so the data model is important because this is the tool that you have to use that is the most important point every dbms is based on a data model mind it this is one of the basic things that I always tell you that you should not forget this should stick in your mind that every dbms is based on a data model what does this mean we have told about the data model that it provides you set of constructs that is what we talk about that dbms is based on a data model what does this mean this means that those constructs those tools that are available in a particular data model that supports the data model dbms will basically provide you implementation of those structures similarly when we say there are structures manipulation language integrity constraints so the dbms when a data model is based the same structures the same manipulation language and the same constraints will be basically implementing because you have to use dbms and it is based on a data model this strategy is a principle the advantage is that you have got the guideline in the form of data model based on these guidelines you can have as many dbms as you like there are different companies there are different software companies every company is building dbms so when we have a standard data model now whatever company this dbms this is based on this data model the definition of the structure is really defined so from now whatever dbms is based on what should I expect in this dbms what structures what manipulation language and what integrity constraints so this is the advantage of data model and dbms and you should understand this so we will read the data model but when we read the data model all the dbms we should get familiar with them because they will be based on the same data models let us move on Dear students there are two major types one of them is semantic data models and the examples are written in front of you entity relationship data model and we will call it ER data model and this is the data model which we will read in detail in this course the other is object oriented data model these two data models are examples of the semantic data models what do we mean by the semantic data model because we came to know that the data model is a set of tools and constructs which are used for database design so what is the semantic data model in this the semantic data models that provide you more tools or more constructs we can say that they are richer in number of constructs and tools what is the benefit of this you can understand in your normal life that you need a tool set to do any work you need some equipment if you want to do any work for that if you have a better tool set in which different tools are available then what will be the benefit that whatever work you do you will do it in a good way for example you have to repair an electronic equipment now you have to open it first to open it each electronic equipment has different nuts and screws to open it if you have a single page which is flat you can open a small size if it is a cross you can open it sometimes in real life if we do not have a screwdriver if we do not have a page we can open it with a spoon when it comes to work when it comes to work we can work in any way but one thing is to work in a better way work more easily if you want to put a key you can lift it if there is nothing else if you have a key you can put a key if you do not have a proper handle you can put a key so this is the difference in keeping a rich tool set and in just passing when you are going to design a database of any environment there are different situations there are different links between things to represent these different situations to represent it you have got your data model that provides you a set of tools if you have a data model that is richer in constructs that will help you when you have different situations to represent those situations you have got an appropriate tool when you represent it your design becomes more beautiful it becomes more meaningful it becomes more explanatory when you put a view on it you will get more information out of that apart from that if you do the same thing with a data model that is not so rich in constructs you can design that too but since there are less constructs you will use less constructs to represent different situations you will use more constructs like you have opened a screw with a spoon you have opened a screw so that will help you to make your design a relatively less rich data model to get you a mini to understand that you will have to dive into the design it will not be so clear it will not be so explanatory so these are our semantic data models both our ER data model and our object oriented data model both are semantically rich means they provide you more constructs let us move on the other major type of data models are record based data models and the three major data models are hierarchical network and relational the data models are not as semantic the first difference is the second difference is that this is recorded oriented and the data entry will be record type whereas our semantic data models are not like that also when we have the entity relationship or object oriented data model the data entry that is not in the form of records we will talk about the second thing about record based data models that the record based data models handle data or deal with data on almost all three levels i.e. our external level, conceptual level and internal level but our semantic data models they generally deal only at the external level and at the conceptual level. Generally, our semantic data models are used just for the design purposes. i.e. our ER data model is a very famous model for designing your database, for making your first design your comprehensive design. In fact, we call it conceptual data based design. It is used for that purpose but to practically implement that design, we don't use the ER data model because it is so well known, rather it has no practical implementation or we don't have any kind of DBMS that is based on ER data model. Whereas we have the object-oriented data model, hierarchical, network and relational. We have DBMS based on all of these. Let's move on. There are different types of database design. As you can see on the screen, the first type of database design is conceptual database design. We generally design or make this design using a semantic data model that we have read earlier. Apart from this, we have logical database design. And remember that our logical database design is generally in that data model where we ultimately have to use tools, which we have to use with DBMS. Generally, the conceptual database design is that we model using the ER data model. Whereas the logical database design is always in the relational data model. Why? Because we generally use DBMS based on relational data model. So our logical database design will be in our relational data model because our DBMS will be relational. Apart from this, we have physical database design. This is the type of design that we will do in a particular DBMS. Why do we have these three designs? Why not? Directly when we implement the physical database design, then what is the need to be taken first? The beauty of this concept is that the conceptual database design is done in that data model, which is semantically rich. For example, your ER data model. Why? Because this is the stage, this is the first design you are going to make. Your conceptual database design is your first design. For that, you need a rich data model. So that all the situations that you want to model, all the data, all the links, all the properties, all the constraints that you want to represent in your design, your conceptual database design, you will want to include all of them. For that, obviously you need a semantically rich data model. After that, the logical database design will be that you will do that data model in which your DBMS will be. The beauty of this is that the logical database design that you want to model, your conceptual database design will not change. What does this mean? This means that your conceptual database design will be in any data model, it will provide a base for all of them. This means that with your conceptual database design you can transform any data model, whether hierarchical, network, relational, object oriented. You can transform your conceptual database design in all of these. You do not need to change that. This is the concept that follows. The logical database design is in that data model in which your DBMS will be. The interesting thing is that you can have many DBMS available in one data model. The logical database design that would be independent of the particular DBMS that you will use. This means that first you made your logical database design and then based on that data model you have used a DBMS. If you want to change your DBMS then what you will do is you will go one step back, you will catch your logical database design and implement it on another tool. This means that it will be fixed. It will be general. You can use it using any DBMS. I hope that you have understood why do we have three different types of database design. One more thing is that our conceptual database design if we ultimately use the tool it is object oriented. In that case, our conceptual database design will be our logical database design. If we have a sequence that we first designed in the ER data model and then transformed it into a relational data model then our conceptual database design will be different and the logical database design will be different. But if we ultimately use the tool of object oriented or the DBMS of object oriented then in that case our conceptual database design will be our logical database design. So this step is to transform the conceptual in the logical design and it will be omitted. Dear students, in today's lecture we completed the discussion on the two diagrams. We discussed the detailed DFT. After that we discussed the second phase of the database design process which is the database design. We discussed the design phase and then we discussed the design types of the designs and for that we use the tool of data model. What is the significance of data model with the DBMS? In our next lecture we will study the ER data model which is a tool to make a conceptual database design. Give me your leave. Allah Hafiz.