 Dear students, today we are in the lecture number 33 of the Database Minimum System course. The course code is CS403. From the previous lecture, we are discussing the user interface and we studied the importance of user interface. And we discussed that user interface is the all that is in front of the end user form the database. And we studied that it is the user interface that creates that makes an impact on the end user about your system, about the system that you have developed. If your interface is working, if it is providing the right convenience to the user, if it is up to the expectation of the end user and if it is user friendly, in that case user will going to like your system, user will appreciate your system. Or if the user interface is not right, if it is not correct, if it is not easy to use for the end user, then even if you have done a very good work in your system that you have developed, the user will not appreciate that. Then we studied different points that make a good user interface. And then we saw that there are different tools that we can use to develop a user interface. And after that, we especially studied different controls in the form of Microsoft Access. How do we use different controls in a user interface? In today's lecture, we are going to design a very preliminary form of user interface. But its basic objective is that you get an idea of what exactly do we mean by a user interface. Apart from this, how do we manipulate its basic controls and how do we use them in a database system? You will get an idea of that. Look, in this course, we are not going to study in detail this particular aspect of the database because this is just an aspect. The other thing is that in this, there is too much detail involved in it. Or perhaps you need an entire course on this topic. And another thing is that there are separate courses on this thing, like VB or Microsoft Access. There are independent courses. So to properly understand them, to properly study them, you have to study that course independently. In this course, we cannot discuss it in such detail or comprehensively and not do it. In the last lecture, we discussed that the interface we normally use to input our user interface is called form. And today, our discussion is mainly on the designing of forms. The last lecture we discussed in detail was that it has to be user-friendly, data integrity. Dear students, throughout this course that we have studied so far, and especially when we were studying the relational data model, I have been emphasizing on the fact that as a database designer, as a database developer, this is your prime concern to maintain, to take care of the integrity of the data, the integrity of the database. That means that the data in the database should reflect a valid, a real, a correct situation of the real-world system or the business that the database is representing. And what is very important in that is the integrity of the data. That means you have to make sure that the data that is going in the database, that is correct from all the respects. And what you have to pay attention to in that is that before moving the data to the database, you perform all the checks that are required to be formed on any piece of data. For example, when you design a form, in that form, it is obvious that you have different tables that you have defined during your design. You will get data in those tables, you will get data input. Now, until the form has your field, they are not yet moved to the database. They are on the form or you can say they are in the RAMs still. So, you have to make sure before moving the data into the database, that all the conditions and all the checks are implemented on them. That means in this way, you can see that the data that is present in the database should be completely clean, clean, and even clean. That means all the checks should be applied on it. Why? Because the functionality that you have to give to your system, the operations processes performed after inputting the data, all of them they base on the data that you have put into the database. If you have not inputted the data hook properly in the database, if you have missed some checks, if you have ignored some checks deliberately or by mistake, then the processing ultimately and after processing, the user, the organization, they would definitely be getting some reports, some outputs from the database. So, if you have inputted the data, if that is not correct, if you have missed some checks or some data that is not possible in that environment or in that system, which is not correct, then the processing and the results that you are giving, they will be wrong. And those results, they are important, because your organization, they make decisions on those results. So, when your results are wrong and when your decisions are wrong, then they are going to harm ultimately the business. That is why it is very important that the data that you are putting into the database, then you have to put in every kind of checks. What is every kind of checks? Every kind of checks is something that is implemented by the very nature of the data. For example, if we say that something is numeric, then the text type or the date type should not go to the data. And there are some things that they come from the design, the database design, for example, when we say that there is a referential dignity constraint, if you have defined it, and that was defined when you were designing your system. It is the first stage of this stage. So, that is another kind of check that has been controlled. Plus, there are still certain things that you have not come into the database design, then you implement them on this stage. So, right from the stage that when you defined an attribute and you associated a data type with it, that was the first stage. There was a special kind of checks or constraints that started to be implemented. Fine, that was the first thing. After that, it is possible that while defining the data, you defined some, let us say, referential dignity constraints. That was another kind of check. And it is possible that during the definition of it, you defined some check on that. So, that was also a kind of, or you defined a range. So, that was also another kind of constraint. But still, for example, we say that you said that our manager, his qualification should be master, his experience should be that much, etc. So, this kind of check is now available. As you are starting to design forms, now you accept data from the user from here, you will move that data into the database. So, I am emphasizing on this point very much that in your database development process, especially in the implementation phase, there is a very important point that data that you are moving to your database, that must be correct, correct from the standards of the database in general and correct especially from the perspective of, from the standards of the organization for which you are developing the database. Checks can be applied within the tables definition or through input form like we said earlier that they can be performed in different stages, in different types of checks. Right now, what you are seeing right now is the screen. In this, now let's see that we have developed our database, we have defined tables, etc. We have defined that using the SQL Server. Now, when we are going to design forms, normally the two options that are used in this environment, either they are used by VB, or as I am adopting a project here, the forms of Microsoft Access, I have selected them because it is quite easy to use them and according to my knowledge, as far as your background is concerned, you have not studied VB. Although VB's visual basic is also used here, but as much as I show you here, the functionality will be used without VB. And the people who want to further excel on this, it is better to use some programming language for the complete functionality. Now, the first phase on this, when we have defined the database in the SQL Server, and we are using the forms of Microsoft Access, and the first thing is how the link will be between them. For that, as you can see right now, the screen is that you will run Microsoft Access. And here, as you can see, you will select a new option from the file. When you take the new option, after this, it will ask you first whether the new database that you are creating will ask you its name. Although you have named your system in your SQL Server, but the form that you are creating here, in this Microsoft Access, you have to name it here. It doesn't make any difference. So, let's say, as you can see here, the file that I have given is an exam system. After doing this, when you see the option of creating, I will press this. After this, you will see this screen. First of all, in this screen, the portion on the right side, which is a small window, in which you can see the new and tablates options. In these, you can see that the five different options under the new will be the third one, Project Choosing Existing Data. You will press it. And you will press it because you have to use the existing database in the SQL Server. That is why you have to use this option. When you select this option, the big window on the left side, you will see this. It is written as Data Link Properties. In this, the connection tab, you have to fill in the different requirements here. For example, the first one is Select or Enter a Server Name. You will see an arrow here, a pop-down, which is a simple view. If you press this, all the server names on your system or in the access of your system will come. Otherwise, you can type the server or the instance name here. In this case, for example, the Vue here is either the name of the machine on which it is running or the domain in which it is joined. And the demo is the name of the instance of the SQL Server. When you do this, on number two, you have to mention that you have to use the login password and the login of the Windows to access the data. Or you have to use a special password or a username. These are the options that you can use with the installation that is why we have selected the first one. In the third option, all the databases that you have with the SQL Server instance will be listed. And the database that you want to create will be selected. Since we wanted to do an exam, we selected the exam. When we selected it, from here, there will be an OK or Create option and we will press it. After that, you will see this screen. Now here, the database that you have connected on the previous screen, you have given the connection information. So, the database that you have connected, all the tables of that database that you have defined, you will see on the screen. Here you can see course, course offered, enrol program, results, etc. These are all the tables that we have defined in our exam system. And on the left side, the area that you are seeing, there are different objects that Microsoft Access offers you. So, the tables are taken from there that you have defined in the SQL Server. All the tables have come from there. What you are going to do here is that you are going to create the forms. And for that, it is obvious that the forms option here, the object of the forms, you will select it. As you can see on the next screen, where you can see that you will select the forms option. So, on the right side, you have two options. One is Create Form by using wizard. As it is obvious that in the design view, you will select different things, different controls and create a new series of designs. Form design. Or the second option is that you will use wizard. A software called wizard that provides Microsoft Access, you want to create the form. So, the first option is using the design view. You should not do that in the beginning because that is meant for the experts. So, when you practice and after that you take that option. Otherwise, the easier one that is creating form using the wizard, you select it. When you do this, then your form wizard will be activated and it will start from here. The first step is that you can select the tables or tables on which you want to design the form. The table for which you want to design the form. For example, if you want to create an input form. So, first of all, you will select that table. So, if you are trying to enter the data into multiple forms into multiple tables using the same form, then you will select multiple tables. Initially, in our example, we are selecting a single table and we will create a form on it. When you have selected the table, then you have to mention the next thing from that table are the attributes that you want to place on your form. Now, on that table it depends on your requirement. In some cases, you would like to place all the attributes that are in the table. In some cases, for example, some of your attributes are computed or derived. For them, you do not need to enter the attributes specifically. Rather, you compute their value form, let's say some other attributes. Or you do not want to enter the base value. And you do not want to let this particular user enter this value. In fact, you want to do this in any other way, in any other form. So, whatever, you have to tell what are exactly the attributes that you want to place on your form. What you are seeing below is that you can move all the attributes of that table on the form. Or if you select the selected ones, they will come. So, on the left side, all the attributes and on the right side, those attributes will come. As you can see here, all the attributes that we have selected, will come on all the forms. After that, you will see the next button. You will click on it. Now, you have different layouts in front of you. You have access support. Layout means the form that you see in which form it will be. What will be the layout? There is a columnar table and when you select it, you will see the window on the left side. It will show you a style that you will see when you select this option. After that, select the next one. From here, you can select the style or background. There are different styles. You can select one of them. From there, we have selected the sandstone. Then, we will click on the next button. Finally, we have the form wizard and he is asking us his name. Initially, the wizard has placed the name of the table as the name of the form. You can change it. Anyway, we don't change the name of the form. We leave it like that. After that, we will press the finish button. If you want to change it, you can change it again through the back. We press the finish button. Initially, we will have the layout of the form. The form that you have seen on the screen is the design view. The design view is that you can see the design of the field and in this view you can change the design. We will see all the changes during the design view. We will do it in our form. Apart from this, you can see the same form in some other views. You will see those views on the next screen. This is the view called the form view. In the form view, you can see the form in the form view. You can see the space in the text area you can see the records and data. If you look at the last screen, you can see the label on the left side of the attribute. The space in the text area, you can see the name of the attribute but if you look at it in the data view, in the data view, you can see the name of the attribute in the labels. But in the text area, you can see the records. Under the screen, there is a pointer. If you use the pointer, you can move forward in the table. You can see the next record and press it. If you want to see the last record, you can move forward. This is your data view in which this is your form view. In this case, you can see the data from your table. In the design view, you can see the design of the form in which you can change. When you are in the form view, you cannot change the design of your form. Dear students, making forms or making reports is a sort of artistic job. And the students of computer science or scientists who have artistic sense or aesthetic sense, you can see the form in which you can make forms. In that, you can use your artistic sense or artistic touch. The idea behind it is that you can see the form attractive and the thing that you want to convey can be easily picked or easily found out. This means that the arrangement of the form has some previous lectures and some future lectures but ultimately what plays a major role is your thinking. So, use your thinking and you can see the form attractive. Now, we have made a basic form. So, what we are going to do in that is that as you can see the form, we have done in the form of a stack like all the attributes on the top and bottom. The first thing we have done is that we have changed the arrangement and we have spread it on the whole screen. Now, the whole screen seems to be balanced in the form and the rest of the screen is empty. It is spread on the whole screen and the form will be balanced. Now, when you defined the names of the attributes when you are designing your database at that time the perspective was that you are meaningful name but at the same time because when you are doing the programming you have to type the name again and again. Your objective is that it is meaningful but not so lengthy because there you used different abbreviations for the attributes as you can see STID, STF name ST address, ST form like this abbreviations are marked here and the form you can see the abbreviations but the thing is the form is going to be used ultimately by the end users and for them these abbreviations are confusing and the other thing is you don't have to type these things you have to do it just once so that is why the abbreviations that you have and the abbreviations and here in a normal English for a common man who has a practical name use them STID so now you know that ST you are abbreviating for student now don't do it write it completely write student write ID because ID is quite established these people know that ID has become a word or if you want you can write the identification ST name student name ST address address of student so you know that you have to use their labels they should be meaningful now what I have said meaningful names I have not implemented ultimately it will be implemented here one more thing is that you initially some attributes were included in your form if you want to delete it that is no problem at all and if you simply delete it that attribute will disappear so first of all if you want to add an attribute you can design a form in the form wizard when you were designing a form at that time you missed some attributes this is a possibility that after designing a form you have added an attribute and now you want to put that attribute in the form or it can be that you have deleted an attribute whatever if you want to add an attribute of the table into your form as you can see on the screen you go to view and first option is field list which is selected here when you select the field list so in this way the form portion where the active table is the attributes of that table will be seen in front of you first of all we added an attribute of student sex so we want to add that attribute on the form so select that particular attribute as you can see here and then simply the window here from here you drag it and drop it on your form so this like the rest of the attributes on your form this attribute will also appear now on your form similarly we have another option on our form we want to add an option group control the basic idea is you have multiple choices and you want to start one so in that case you use option group control see the combo box also gives you multiple choices and this option group also does this the difference is that the combo box the type of controls you use in that situation when you have that can change increase or decrease for example the program name attribute we change the type of the program name attribute as combo box because the value of the program name attribute will be taken will be picked from the program what is the logic the logic is when a user has to enter a data we are adding a new attribute we have added a different attribute when we want to enter the program name if we have left it if we would have left it as the text box in that case the user has to type the program name in that case the problem was that your user may have a wrong value type wrong value means that he type a program name that is not there in the program table and you know that we have defined a primary foreign key link between the student and the program table so that a user could not enter an invalid program name because if we declare the program name in the student as the foreign key it will have the same value which exists in the program name in the program table if you leave it as text box then it becomes the responsibility of the user to enter a valid program name if the user does not know what is the valid program name then it is just a matter of it and trial and the user does not even know what to do if we call it a commo box then it will be useful that when the user will enter the program name then when he will click on the program name attribute then there will be a drop down list in which all the programs we have at this time in the program table the program name will show from there and the user will select the work from there this was the situation the commo box you used that when you have the possibility of the program names it varies that whatever program name is in the program table whether it is 2 or 10 or 15 or 20 whatever happens but you are not sure and the number of options is not fixed when we have an option group we have fixed options for example like we talk about sex so sex has options and even if you consider other options then it is not fixed but it is not that it will vary so in such a situation when you have fixed options and you want to select one then this option group which you use for controllers which are statically or fixed and defined and you know that it will remain it will not change when commo box is in such a situation that when you have options it can vary so whatever option it will vary it will adjust the commo box and you can select it so this is what I told you as we talk about sex so you gave it the option of male-female if you want to enter any one of them if you leave it open then it is possible that if a user enters he does male he does female he does m he does f he does male he does dana in that there is no control you do not know precisely where you feel that you should bound you should bound it but it is not like you give it a small world where you feel that you should bound the user do it otherwise you give liberty to it now if you see that we have sex attribute for that we have option group control which we have defined and for that we selected this button from here which is on your left side which is a box from option box from control box you have selected that button which is showing option group so first of all it will ask you what are the different options you have said that I have to give a male and a female you have expressed then you will ask do you want one option to be default choice what is default value if you know then any of them will not highlight but if you default then the value which will declare default will be highlighted after that if you select any value then what is the return value that is male and female then you have made value 1 or 2 fine, next one after that the question you ask is you have made this option group when you operation then the value will generate which we have just fixed as 1 or 2 in which attribute should this value move we have defined the student sex attribute we have nominated it that its value should go in this attribute ok so in this way we have defined the option group control and in the end we will see how it is implemented or how we will see apart from this you have seen that the text written on your form you want to enhance its size if you want to enhance a particular attribute size then select it if you want to enhance the size of all then you select all in the edit option as you can see on the screen all your attributes will be selected after that you have the font size option whatever size you want we have it on the highlighted screen it is 14 if you select it then all your attributes will be enlarged but it is possible that as you can see here as you can see as if if the shirt color is tight as if it is stuck then you will have to do this that you have to go to everyone and enlarge it then you can drag it and you can enlarge it so this is what we have done with our form so far that you can see that whatever we have talked about for example in this we have done student ID we have attached the student name with sex now after all these activities which we have discussed after all these changes the shape of our form is this as you can see we have set all the attributes of the student name they have given a proper name we have converted the program into a commubox we have converted the sex into an option group and when we look at this form in the form view we have this data which is empty but you can see that you have an option group because the data we have already input the record we have is of a female so we have in the option group we have the button in which the selected button is female so this is a very simple form and we have seen the different treatments here now the next thing we are going to do with this form is we are going to add different command buttons into our form and the purpose of command buttons is that you want to perform the different activities in your form you do it through the command button and you can add the button we are going to add the simple buttons now the first thing the tool bar on the side you have selected the icon of the command button and when you have dropped it on your form the command button wizard has been opened now the categories of the command button record operations, form operations report operations, applications and miscellaneous first of these we select record navigation now in record navigation we have 5-6 different operations first we select go to next record when we press next it asks the button you see on your form do you want to place here an icon or do you want to place some text within that button if you want a figure or if you want a text let's say we want a picture or a figure and when we see you can see the sample on the left side after this on the last screen we ask what is the name of the command button first we select when we finish now look at the button you see on the left this is the next record and similarly the form you see here we have added some other buttons for example the other button with which we have an arrowhead or star this is the find next similarly on the left side the button you see on this we have clearly labeled for example find record, delete record, close form now this close form button you must have understood that we selected from the form commands category different type of categoristic command buttons from the form operations we selected from there and created a button on the top you can see we have added a label which shows what is the purpose of this form and purpose is add student record we have boldly written it so that the first prominent thing on the form is what we are doing after that if you see this form you will see this form again you see we have a record here we can see the record of Tahira Ija student and on this you can see all the data and in the case of sex because it is female again the female button is selected so this way you can see a simple form and you have seen how you make a form you select different options and adjust it and by this form you can see the data and by this form you can add data when you reach the end of the record or you can add a button add new record you can add a record this was a very simple form in which you have used just one table as I told you in your real life applications you have more applications that involve multiple tables in this form there was a table now the form I am going to show I have explained the first basic form in detail but very fastly and swiftly I am showing you how we have created the form this form it involves multiple tables and basically we have used 4 tables in this form now there are different ways to place multiple tables when you have a tool whatever you use VB or microsoft access any other tool there will be different techniques how you involve multiple forms on a form but what I have created is an example form I will explain how I have created this form and how it involves multiple tables the scenario of this form is that we want to enroll our students the situation is we have a program table which says MCS, BCS, BBA we have different programs after that in every program different students enroll similarly in every program different courses are offered different courses are included now when we have a program table we record all of them similarly related students come and after that different courses come we want to see one thing is in this program which students enroll one thing secondly we want to enroll this student in this program so down below the table you will see the programs and courses and from there you will select them and place them in the enroll you will enroll them and the second activity you will perform is you will place their marks as we have designed the enroll table and placed the midterm marks we will do the activity let us see now here we have at the top we have three attributes of the program table along with that we have created a form and we have created the student after creating the student in this in this program we have included it as a sub form this is the option to include along with that we have repeated the same operation with another form and the form of the courses that is we have created the student created the courses then we have started with the program after doing the program we have created the program we have included the student and the courses and then we have included the sub form then we have done along with that we have included another sub form enroll and in enroll we have added two buttons save and exit so this is our form basically we have the sub form and the table is on the same form now we have done that our program was on the top we have linked it with the student and with the courses in fact we should say that we have related the students and the courses that means whenever we will navigate the program table move one record to another then all the associated students and courses will automatically change but after that as we have to see in enroll there is a little bit of programming involved we have done that when we want to enroll a particular student in a particular course then it will be you will select that student and you will select that course after that you will enter the data and save it that student will enroll in that course now see if you see here unbound controls for enroll table you have added it now at this time you have this form we have data view form view now you are seeing that at the top program table MCS and the program credits are 64 when you see when we select this move it in the program table accordingly your student table and courses table will change here you see that when we selected a particular record from the student and selected a particular record from the courses then the related data in enroll which we have already feed it will automatically come here now first we have to enroll this student in another course so there can be many other ways but the way it happened that we will select the student from here and after that we will select any other course then it will automatically move in the enrollment and we will place the rest so this way your form will work again I told you that form is a complete course so if you want to read it in detail then you will have to give it a different time but in today's course I have shown you a simple form a complex form Dear students we will wind up this lecture in today's lecture you have seen that from lecture number 33 our discussion was going on on user interface we have continued that today we have studied about forms and we have seen how we link the form in Microsoft Access and how we start designing it and how we link the table after that we control it then you have seen that we created a form that was a simple form in the sense that it involved just a single table and in the end I have created a form that included multiple tables rather it included 4 tables so in this way using different tables you create your forms you state your forms you input them or you display them in the same way I hope I have got some idea and for further understanding you will have to work on this further now I am leaving Allah Hafiz