 Assalamu alaikum. Dear students, today we are in the lecture number 32 of the database management system course. The course code is CS403. As you know for last some few lectures, we have been doing the SQL. SQL is the tool that is most commonly used to manipulate the relational databases. And we studied that there are three major components, major types of statements in SQL and they were the data definition language, the data manipulation language and data control language. We studied data definition language and data manipulation language in detail and for data control language I left it to you on. Data definition language we studied in detail. We also studied the alter statement and as far as the data manipulation language is concerned, the major statement that was used there was the select statement. We also read the insert statement. Dear students, we saw that the select statement is most commonly used statement of the SQL. And we saw that if we think about or if we compare it with the relational algebra statements, the operations. So, the selection cooperation or the projection cooperation are different forms of the joints right from the product to the national joint, the inner joint, semi-joint, outer joint. We used the select statement in SQL to perform all types of joints. And we also studied different clauses of the select statement. And in that we saw that from clause, where clause, we read the group, we read the order, we read the sub-curies. So, they were different flavors of the SQL. And as I told you earlier that to have a better command, to have a good command on the relational databases in general and SQL in particular, you need to have a very good practice of the SQL statements. So, what we have made a database for our practice, you apply the specific statements. And the more you practice, the more you become expert on it. And especially the different forms of the joints, you should be clear about them because you have clear concepts from them. And especially when you join yourself, like I explained about self-join, So, if you are clear about that, you have the advantage overall in SQL. So, the purpose of summarizing the SQL at the start of this lecture is that you practice more than this. We spent five or six lectures on SQL, but still, a lot of SQL is yet to be covered. So, you can take any book you want to practice as much as you want. So, the more you practice, the more you will have the advantage in relational databases and SQL. Now, the particular component or the particular part of our course that we are going to discuss today is the application programs. Now, application programs, as we have discussed earlier, the phases of the development of our database design, we have passed through different phases. In the first phase, as you will remember, we started with the initial study phase or preliminary study phase. It was objective that we study your existing system, understand it. After that, we see phase where we also gather the user's requirements. The different user groups interview or question-deliver, different means, we gather the requirements of the different user groups. The second was the analysis phase. We analyze it, we think about it, we use the tools through which we decide or we design how we are going to fulfill the requirements of the users. And when we talk about the database design process, the focus is on what is the form of data, what are exactly the relationships between different data items that are required to be stored in the database to fulfill the requirements of the user's groups. Now, the objective of the database design is that you design the database. Then you also identify different forms of constraints that are there in the environment, in the system. So, you identify those constraints and some of them, they are expressed in the database design. You have discussed the design of three major types of design, we have said conceptual database design and for that, we generally used a semantic data model. And in this course, we used the ER data model. We studied it in detail and we studied its extension. After that, we studied that there is a logical database design on which you said that in a conceptual database design, we transform it into the logical database design. The conceptual database design is independent of any particular data model in which we are going to ultimately implement our database. So, from there, we transform it into the logical database design or the logical database design have that data model in which we are going to implement the database. And after that, when we talk about the physical database design, it becomes even closer to the particular tool, the DBMS, that we are going to use ultimately. And when there is a physical database design, there are many other decisions in it, like we studied that we do denominalization in it, then we select indexes in it, about file organization. So, these are the decisions that are made during the physical database design. After that, we studied the portion of SQL in which we saw that once we have decided our physical database design, now the next stage is that we express it, we transform it into the tables. We have made that design, which we have finalized the physical database design, we then implement it using the SQL. SQL's statement, as it creates statement type, then if you define a different constraint, then you can add constraint to it. So, overall, the objective of all this activity was that right from your preliminary phase, you go through all the stages step by step. And the objective was that how you design your data, that is required to fulfill the user's requirements. And then through using SQL, as I told you, the databases, the DBMS, most of them they provide you the tool. And using those tools, you do not need to use the SQL, because you simply use the tools. And the underlying SQL statements, they are automatically controlled or generated by the tool. So, the DBMS we are using, that is the SQL server. And you have a tool available that helps you design tables, even the Microsoft Access House, and almost all the DBMS's are available. But as a database professional, you must know the SQL, that through which statements you actually create the tables. Now, the condition of our course is that we have reached that stage where we have created the tables through all those phases. And as a proper, as a true database professional, you must have the command. So, you should know all the technicalities of this theory. But keep in mind that if you only have the database design, then obviously it is of no use for the end user. The end user, they need the complete project, the complete package, in which all the requirements are there. And in fact, they may not have any concern with the database design. Their concern is that when the system they have developed, when they run, they are doing all the work for which they have asked you to do it. Now, underlying design, they are not interested in it at all. So, this means that we should say that the design process, although that is very important, and that your correct, your consistent, your efficient database system, which you ultimately develop, provides its base without a properly designed database that cannot have a good system, which is its functionality. But still, this part, this part of the user's interest, in which the application program is being built, they are performing the activities that the user had asked you to do. So, this part of our course, we are concentrating on how these application programs develop. In this, we will not be going in detail into this topic. Why? The reason is that in this, in order to develop a proper application, you need to use some language. So, the language that you have to study is obviously linked to some other course, or you have already studied this course. So, in this lecture and the next lecture, I will give you the idea of what do you mean by the application programs and what are the things that are included in it. And I will also give you a small demo of a smart one. But its in-depth application depends on you, that you are depending upon your other courses or in this course. Because in this course, studying a full-fledged language on your own, I do not expect that, but if you have already studied the language, then you can create all its functionality in this course. So, in today's lecture, what we will see is that you will get an idea of how you develop an application program. So, you will have to add the rest of the meat in it yourself. So, let us start today's lecture. Application programs. Programs are written to perform different requirements posed by the users or the organization. The concern of the developer is that when a big system is being developed, the designer and the developer consider two different things. The designer is one or two people, the developer can be a team, the DB is different. But if you talk about small or medium scale, the people who are involved in this shift. So, anyway, if it is the same person or even different people, then normally developers are considered to be responsible for the application programs. As I told you at the beginning, the development of application programs can be performed after you have designed the database or it can be performed in parallel. When you have made a guess about your requirements, when you have taken your conceptual data, then you can run the program in parallel. When you are doing your database final, then you can do a few things like if you feel like. Or there is another possibility that sometimes the different approaches of system development of database development, there is a prototype approach. In that, you can say a rough or tentative program is developed and they show users. If they say some changes, then they bring it again, they change it again. So, even in this approach or in any case, it means that the application programs can be done even after finalizing the database design. Or you can also run it in parallel with the design process. The things that you are assuming or where the database is not being used, you can build these in parallel as well. Tool selection is critical, but it is said that in this time, whenever you want to perform certain activity, there are different options available to you. So, most of the time that your organization is not interested in in which tool you are developing. The thing is that normally the developer in which he feels convenient, normally he selects that tool. As I said earlier, the same functionality can be performed using different tools. But obviously if the developer is familiar with something, then if he wants to develop it, he will feel more comfortable. That is why he will go for it. But do not always keep this your priority. If there is a very specific requirement somewhere and instead of that tool that you are familiar with, there are other tools available. So, whether you have to work hard to learn new tools or even if you have to hire someone else who is expert in that tool, then do this as well. But always do not keep your preference ultimate and do not look at the user requirement. Generally, the activities that are involved in the application program, they are very general here. You cannot plan it everywhere. But in most of the cases, activities like this are involved in the application program. For example, the first thing that is involved in it is data input programs. For example, you enter the data in the database like in different tables, in different relations, you input the data. And this program, they are very important. Why? Because any functionality that you have to develop later, the data that you have put into the database, that data provides you the basis for those activities. So, if the data that you have input was not right, it was not in the format, it was not technically right, according to the rules of that organization, if the data input was not right, then obviously, the activities that you are performing based on that data, there are many chances that it is quite probable that it would be a mistake and your overall system is not working properly. So, the data input programs, they are a very critical part of the application program. Editing, you have to change the data, you have to delete it, you have to modify it. So, all this is also called the part of editing. For this, you have the results on the screen in the free form of the display. Then, any different activities, for example, if we think about our exam system, in that we can think of different transactions when the activities are like an admission system, then you have to do a merit calculation. That is an activity for which you have to program. That activity will be performed based on the data that you have placed into the database. Similarly, if you think about a banking system, then you have to calculate profits on a daily basis, or monthly basis, or annual basis. That is an activity. Similarly, if you talk about any production system, then your daily production, or a particular department's production, you have to calculate it. These are different forms of activities for which you can write an application program in a particular database system. In addition, reports are also a sort of display format. Sometimes, you need reports on the screen, sometimes you need them on the printer, hardcopy. Reports are also a almost permanent feature of any database system. User interface. User interface means that what you call a screen, what you can call a computer, and what you can call a screen through which you communicate with the database and the user. That is what we are talking about. That computer screen through which an end user communicates with the database. That is the user interface. So, approximately all activities are performed through the user interface on the database. A gateway to the database world for end users. There is a user interface for the end users through which they access the database or any operation. The end users of users in an application it is crucial. Look, in the database, in the design of the database, the application program of the database to perform any activity, even if you have performed a very big task, you have run it. The end user will not have any knowledge of that. The end user will not be able to appreciate that. Why? Because that is a technical thing. Who knows how difficult it is to perform this particular activity in the tool that you have used? So, for that, in the programming, in the application program that you have done, any major work that you have done or any major work that you have done, you will not get any appreciation from the user. But if you have made an interface through which the user is interacting through the database, if that is your beauty, it is easy to use. The things that the user needs are there. So, the user can appreciate you so much on that basis that if there are any small mistakes in the working of your system, they can be ignored. In most cases, in most cases, the user interface is so important. However, it is possible that from a developer point of view, when you are developing yourself, you will feel that the work that is being done and its functionality is more important. But if you look at the user's point of view, if you look at the user's point of view, if the user is not happy with the interface, then it is possible that you will have to work through all the efforts that you have done that you have developed in your application, that is why you should not take the development of the user interface lightly. You should also work with as much concentration as possible on your database design. Because for the user, this is crucial, this is important. And if it is for the user, then obviously it is for you too because after using the interface, after seeing the interface, then the user will give you a certificate of satisfaction or dissatisfaction. So, if the user interface is not good, then it is very less likely that you will get an appreciation from there or they will approve your system. Normally, the user interfaces are text-based or graphical user interface. We also call them briefly GUI. And text-based user interfaces are not used that much nowadays. The most commonly used interfaces are graphical. And normally, we implement them and especially in the context of the database, they are usually called forms. So, if we say that we are designing a form, that means we are designing a graphical user interface. So, we are designing as a developer, as a designer, and when we are done with it, then obviously this will be used by the end user. So, this is an example of a text-based user interface. And I have said that these days, hardly you can see it. But normally, the idea is that in this way, you will get different options in the form of text. If you want to perform this activity, then press this. If you want to perform this activity, then press this. And then when you press one key, another menu will appear. So, today's system is not available. Forms are called or we call it a graphical user interface. Normally, the forms that are used today can be called two broad categories. One is browser-based. Browser-based is the layout and style of your web browsers. It is their style. So, you have to make it like this. The advantage is that in general, if you want to do it on a personal computer or on a single-use, then you will see the same style. And if you want to put it on the Netflix or you want to place it on the LAN, then you will see the same format. So, if the user is given this form of form, then it feels more friendly than that. It feels easy to use it because it is possible that while using the Internet, they are using different browsers because they are familiar with the browser already. If the form of your database is based on that browser, then the familiarity will make it easier. Normally, what is used in this can be used in HTML or any scripted language. The tool is used on the front page which helps you in designing. Non-browser-based can be used in many languages. For example, if you see that you get a developer with Oracle. If we talk about Microsoft Access, then it has its own form designer. But if we talk about SQL Server, then normally you use Visual Basic and you design your form and add functionality in it. Form, tool, support, simple functionality. You can use the form tool with any DBMS whether you use Oracle which is its own developer or you can use it in Access or any other form designer. Normally, the idea is that the form tool helps you in the layout of the form. And besides that, some basic functionality in that form helps you in the facilities to put those things as well. In a basic layout, how the form will look like. One or secondly, the basic functionality of your application program. For example, some constraints that you will put in your design. Some constraints through the form that you design, and the rest which is the major part of the application program. That you will be doing using any programming language. For example, if you talk about the developer using Oracle you will use the developer and first for the functionality it is possible that Oracle has its own language or any other language also. Similarly, if you talk about Microsoft product, there are different possibilities for example, if you are using VB with Secret Server then its controls and the rest of the functionality can be done through the Visual Basic programs. Or if you are using Microsoft Access form then some functionality which is missing that you can implement through VB or any other programming language. The idea is that there are different layers. In the first layer, you put some functionality in your database design, in the first stage. When you define the tables, you define the constraint, you define the primary key, you define the foreign key, you define the text. This was the first level of the forms. The third level is when you will be using a programming language ultimately. Your real applications it is very hard to develop such an application without using any programming language because the rules and activities to implement them, you will have to use some programming languages. As I have given you the name of that whatever you are using in DBA you will have different forms to use. With each form, you will have different options to use any programming language to implement the activities of the organization or system. This is an example of the graphical user interface or form. Different fields can be seen in it. You can use different controls to design it. You can use different controls to interact with the computer. There is a term commonly used in the development process, that is user friendly interface. What do we mean by a user friendly interface? The first thing is that we have got different types of users. The first category is beginner type. The user type is for them you have to put into your interface, into your form what to do and how to do. The user interface should be like this and guide them what to do and also guide them how to do it. The possible errors that they are learning they are sort of learning. Even if you have given them initial training even when you leave them on their own or when you observe them there are quite chances that they will make a mistake. When we say that you train them what to do and how to do or not only you tell them rather the interface tells them. For their guidance they should know what to do and how to do it. These two things. Plus if they misunderstand something or if they perform something wrongly in your interface they should be so much of wisdom that they should be absorbed so that they can absorb their mistakes. They should not create a blunder on their base rather they should guide them. So this is what should be there for a beginner level user. Apart from this we have some intermediate level users what should I do in this but they need to know how to do that. For that again you have to give them a less level of guidance to tell them how to do it. A little bit of training a tutorial a tour so that they can learn how to do this. By designing a user interface you will have to do something for such users. Apart from that experts they know what to do and how to do it. If they know both then there is one thing for them the use of this interface should be at least boring or laborious. You have a route for a beginner level user that you are telling them what to do and certain stages you have If you are telling them how to do this if all those stages are fixed for the expert level user as well then your expert user will get bored and he will not feel friendly and he will not be happy using it. That is why if on one side there are details that you are guiding the person in detail along with that there should be a possibility that if someone wants to do it quickly by pressing fewer keys then you should have an option for example if you see an expert user is working he has to perform some activity instead of that you raise your hand towards the mouse and take the mouse to the appropriate place where you press a button and then save it instead of that it is already on the keyboard so expert user will prefer that I keep my hand there instead of moving my hand I keep my hand there I do something and it works for me that means you should have both options let's say if you are a beginner then hold the mouse carefully find the proper place and press save button this should also be in your form but along with that an expert user will get bored so when we call a user friendly interface the mind it you will not get any type of user you can get any type of user then you have to see what type of the major user is so you have to keep a major focus on them but if you have other users do not ignore them because you have to keep all of them happy so obviously you will say it is not always possible to keep all people happy but it should be done for every type of user when you design user interface these are the tips as we have discussed then make users in charge one possibility is that you make your interface so perfect that it does not allow the user to move it forces the user exactly on what it wants to for example if you have 15 fields 15 attributes you have to do one attribute live on it and the other 14 they are dead user cannot access them so you have to get it in this and when the data is taken to another attribute turn it on and when the other 14 is dead you are holding your finger and it is so restricted that the user has to walk with closed eyes maybe with this the correctness the consistency the integrity you are protecting the user knowing the user because every type of user and any type of user do not feel closed as I said you should expect as an interface designer that your user will commit the mistakes and your design should have the tolerance that the user will make mistakes and there cannot be a big problem based on these mistakes it should not be as a result of the mistake of the user you have to delete it should not be like that means you should expect that the user will make a mistake and in that case if there is a problem then in recovering your interface design should be so deep that it can recover that is why it is said do not bound the user but give the user the feeling that if he is using the interface he is the in charge do not test the user's memory if you want to do this, press this if you want to do this, press this this is the code of this thing this is the code of that thing you have told so many keys in your help you have told so many codes that when the user is using your interface you are taking a test of the memory that can he do this or not and then he makes mistakes I have pressed the wrong command I have pressed the deleted command and like that means that if you expect something from the user make it minimum and do this in fact let them be a part of the user's memory by themselves do not force him to do something in fact it is a duty there is a menu or a button when you press it and it is written that this is his command so while using to do this, I can press this key so by using the data entry or using the computer do not depend on its memory nor test its memory that it can not be used without the computer for example if we talk about the codes the problem of codes one thing is that in an attribute you have to enter the code of something so you expect from the user that if they are all good then he will remember the code or he will put the code list on the table and when you want to enter the data then he will enter no the simple thing is you have to enter the code in the attribute click on it and when he will use the system the user will automatically remember that this is the code so after that he will keep doing it and if he remembers then you give him support for confirmation so that the chances of error can be reduced otherwise do not bring a great burden on the user that first you sit for 5-6 days then you start using my system do not do that because the user will not like it it will be inconsistent if you have adopted any convention or any approach then try to use similar situations in other places the user will be familiar with your system and overall the system should be processes based rather than the data structure based Dear students there was a database design process we created different entity types through relationships and then we transformed them into the tables into the relations and then we implemented those relations in the form of tables using any dbms now when you are getting the data entry obviously the data has to be stored in the tables obviously so do not do that you have to do the data entry to make them table oriented first you do the data entry then you do the table then you do the table A then you do the table C then you do the table C do not do that because it will create difficulty for the user that you have to remember that you have to link it to the table not like that if you have done the table A then you have to do the table C and if you have forgotten then you should not do that in fact your data entry should be processes based in an environment in a system as soon as a process runs from your design point of view if that process involves 3, 4 or 5 tables because I told you user have no concern with the underlying data structure that this is a form when I enter the data it is going in 1 table 2 or 3 or 10 it is coming from 10 user has no concern with that that is why do not keep the ease of your design in a environment in which you are entering the data you should give priority so that the user feels easy for example if we talk about in some place let us say we are entering the student center and in that we said in the courses we are enrolling it those courses should be there or those programs are enrolled as if you have to link with some program first of all a student is enrolled in such a program that we have not entered that course is being offered in the university but we have not entered the database now the user first of all he has entered the student's ID, student's name student's father name, address now when the program comes to enter this student enrol this program was not present in your database now it is not visible it cannot enter do not ask your user to cancel all the data that you have entered so far go back to the program table first enter this program then come here again the data that you have entered that all goes waste it should not be like that it should have such environment to enter the database or for the time being manage it in some way suspend it for some time and then the user will continue to enter a program that the user should not feel that I am stuck first I entered 6 fields so I stuck again I set again I entered all the fields again I stuck again all the data is cancelled document structure it could be single page versus multiple pages sometimes it happens that when you are against a process against any activity you are asking the user to enter the data that you are designing in that you can put all the data on one page second option you display the different parts on the page and then activate each one of them and manage them it is up to you that you do not complex the page that you have to enter 60 attributes instead you try to squeeze 60 but the form becomes so complex that it becomes very difficult to understand instead of that you can make multiple windows on one window or you can design one form and show the different parts in the shape of icons select one for that then minimize it and like that this is also a good approach that if you are not suitable then you can adopt multiple options we the concept of database design that was in the ER data model and in that case we said that the entities and their relationships we had formed in the tables but here we are talking with the same reference that if you have related entities if you want to enter the data what you should do in that one thing is that if you have a single entity then you have to enter the data one option is that you keep it on a single page and second thing is that in some cases you have hierarchies of entities like that in that case if the size is not that big that if it can come on one page then it can come on one page or it can come on one screen or it can come on multiple forms so if you see this is an example of a simple entity that if you have an entity or you call it a table then you have to enter the data so what you did is that you placed the different fields in front of it and the options that could be placed in front of it so in that you have stored one entity or you have represented it and the example of hierarchies that we have already seen if you see that on top we have a program which means that our institute is offering a different program we have the details of that so on top the program's data after that the related is that every student which courses are enrolled those courses are there now look at this at any level when you change the lower levels they will automatically change for example if you look at the program name where the program name selected that is BCS so the related you have the lower part in that you can see from the student table the record who are enrolled in the BCS program one thing and if we go further so the courses which are at the third level in which the student ID, course, course semester name is there in that there is a particular student in which the courses are enrolled you can see that now in this the level of the student where the student's table which is representing the student if you move here the relevant courses will automatically change for example if you the control is on student S1020 the courses are there you can see that the student ID in the courses is S1020 which means that your student is enrolled if you take this step first if you bring it to S103 at Shoaib Ali the courses they will also automatically change the courses of Shoaib Ali they will come so you need to have a link and this link the forms the tools they provide you a facility to establish links first if you if you change the control on the top level program level then if you change the program level then if it changes there then the student's level will automatically come the students who are enrolled in MCS because there is a link which will be at the top related to that you will see in the next level and similarly if there is a top level student the courses enrolled in that you will see that student Dear students when you place different data on a form to show the user or to receive something sometimes you take an option to see what the user wants to do or you take data so when you implement it the thing through which it is generally called control we see that we have different controls through which you create these forms or build these forms there are so many of them for logical values for example you can see in this you see you can also ask the check bosses that if you click on the value selected then it will be selected or if you press the toggle button then you will get this option set of values if you have to select some of the multiple values or you can select multiple values because in this case you are selecting one value at a time but if you want to select multiple values for that we have different controls available for example on the left side here you see combo boxes and list boxes you can use this terminology for example in visual basic or in Microsoft access you use this terminology similarly on the right side what you see on the screen we have an option button group where you can select one of them or we have a toggle button group where you can select one of them so this is when you have set of values and you want to select one of them now you can see here to create this table to create Microsoft access you can use set of values and you can simultaneously access multiple values and use them for numbers, dates and text normally you use text boxes again terminology of VB or Microsoft access but especially for dates for example VB in that case you get some special controls for design if you want to select a date instead of typing a date in text box it is possible that you will get a calendar and in that calendar you can select a year if you select a date you can select a date type attribute if we talk about time you can select time from here when we talk about text in text normally it is through text boxes but sometimes you can use input mask or check constraint you can implement rules for example you can limit the length if you want alphabets digits or mixers you can define them you can check numbers and you can check Dear students this is one of the most important activities in developing the interface you have to maintain the integrity of the data the data that is going in it should be 100% correct and accurate data and as we have discussed in two broad categories there are basic rules and we will say among the basic rules the rules that you have defined in the data model in the next stage you can say there are two refresh and integrity constraints or they are definitely the basic rules secondly when you define the data type or the different data base design they are among the basic rules and those rules as we have said for example production should not be that much or a manager should have this qualification or if we have the merit of that type of calculation these are all things they are generally called business rules so you implement them through application programs or through programming whatever the data that is going in the database should be correct you have to ensure 100% the integrity of the data Dear students we have started a discussion on the application program and the focus we had in today's lecture was on the user interface or on the design and we have studied why user interfaces are critical because the end user of the user the interaction of the user with the database is through interface through forms and after that we have seen what kind of controls can be used and in what situation should be used now we will do a different example of form design in our next lecture now I would like to say goodbye