 thing which you need to do today is set up PostgreSQL. Now, why set up PostgreSQL? We could easily have set up a common PostgreSQL for all of you. But the idea is that in December at your centers, you will be running this course. So, at that point you will have to set up PostgreSQL. Now, you do not have to do this recursively in the sense that the participants in the December course do not have to set necessarily set up PostgreSQL. Although, that is probably still a good idea because they are also teachers and they have to do it for their students. So, whatever we are doing today, you should see how to replicate it in your center if possible. But there may be resource limitations. It may be that you cannot actually give each one their own net book and set up a PostgreSQL. In which case you set up a common PostgreSQL and let them do their exercises on that. And the instructions are how to set up PostgreSQL provided anyway which they can go back and do. But today we just want to make sure that you are familiar with this. So, the first part is setting it up and then you have to create users in the database. So, that is the before setting up users even there is some initial configuration initializing a standard database then creating users and then running queries. Oh, there is one more step in there on creating a administrator password. So, all of those steps you can go through they are there in that sheet. So, once you set up PostgreSQL with a user, you can now connect to PostgreSQL as that user and run queries. You could connect as administrator also as a PostgreSQL login, but you are better off connecting as the user and then running the remaining things. So, how do you connect to the database? You could write a Java program and use JDBC, but we do not want to do that. Instead, there are two interfaces. One is a command line interface called PSQL. You could use that if you wish, but again there is some syntax and some details which you have to deal with if you are using that. So, it is a lot easier to use this graphical interface called PG Admin 3. So, one of the steps you will be doing today is setting up PG Admin also and then using that. So, you have to tell it which database to connect to and what is the login password. All that is part of a setting up a connection on from PG Admin. PG Admin internally uses ODBC to talk to the database and now you can type in a query. It executes the query. It gets the results back using the metadata features which we talked about. It figures what are the column names, what are the types and it displays the result for you. So, the later part of today's lab is actually running SQL queries. So, there are several problems which are specified in English all using the university database. So, where you have to run queries and get the result. Now, how do you make sure your query is running and getting something which looks reasonable? We need a data set also. So, we have also provided a data set for the university database. We have provided two things, two files. One is a file which creates all the relations in the university database schema. The second is a file which inserts sample data into those files, into those relations. So, the first step is to once you are connected as a user is to create the relations, load the sample data. Then you can start writing the SQL queries which we have specified. You can execute them and see if they look reasonable. Now, all the sample data actually corresponds, the slide which you have has some of that data. There is example data in the book. It is there in the book also. This sample data file which you have provided has exactly the same data. Now, when you are writing the SQL queries, you need to know what is the schema. Now, for that in your slides, go to chapter 2 of the slides. Towards the end of that chapter, there is a schema diagram which shows all the relations and the foreign keys and the primary keys. So, all these machines run Ubuntu. So, how many of you have used Ubuntu before? How many of you have not used Ubuntu or any other? How many of you have not used any version of linux? Few. So, on Ubuntu, there is a package manager which lets you install things very easily. The package manager has to be set up which has already been done. So, it has been set up to get new packages which are on demand from a machine called ftp.iatp.act.n. So, when you run this particular command sudo that is do as super user. This subsequent command requires administrator privileges. Therefore, you have to run this as sudo and then apt get is the package installer and you are telling it to install PostgreSQL. Now, it will ask you for a password. I think the default password on all these machines is 123. That is what you have to type once it asks you for a password. And it is going to go to connect to ftp.iatp download all the PostgreSQL files and then it is going to set up PostgreSQL. So, please run this from run this command from a terminal on your netbook. Your first step is just to do this. How many of you have successfully completed this step installing PostgreSQL? All of you. Anyone who is not? One or two. Please take help from I. So, now to configure it you have to look for the file pghpa.con. If you can find it. Now, this by the way is a very I think this is a very trusting way of doing things. So, what is this doing? Yes, you will not have permission to do an sudo and then g edit. So, all of this may have to be run as super user. So, sudo space a g edit space and then pghpa.con. That should work. Now, add this line as a host all all. Now, what this is saying is 10.0.0.0 slash 8 says that any host any IP which starts with 10. Anything beyond that. Accept connections from those. In IIT all the local in our intranet all the machines start with 10.0. So, in other words you are accepting connections from any of them. Let me write it here g edit. So, the trust means it just trusts blindly. I think it is that is not such a good idea. I think the better option is password or md5. Yeah, md5 is the safe thing. So, what I suggest is over here instead of trust, replace trust by md5 is same. Ident will work for local users. So, what ident does is it is actually talking to the operating system to identify you. So, if you are running as a particular user, the operating system can basically authenticate you instead of using a login password. That does not work across machines. Ident can be made to run across machines, but you have to trust the other machine to be telling the truth. Therefore, yes, that is right. It requires the same OS and database user and if you are connecting from another machine you have to trust that machine to be telling the truth. So, now those of you who are using the printout to do stuff, apparently the indentation is messed up in this printout. Therefore, some of the instructions which are meant only for source insulation, some of you are trying to do it because you thought it is part of it. So, what I suggest is do not read this. Connect to Moodle. I assume all of you are able to connect to IIT, IITB, IACIN, slash Moodle. It is there on the top of the bar, but in case you cannot read it, it is a private IP address. You can replace it by whatever the IP range in your place. Those are the two private IP ranges. 192 is a smaller range, 10 is a full class B range. So, replace it by whatever you use. Actually, 192, if you use that, you are supposed to use 192.168, 0.0 slash 16. That is the correct prefix if you are using that range. So, for those of you who did not hear that question, over here 10.00 is ok. When you go back to your institute, if your local IP address is in 10.0 range, you can use this. If your local IP addresses are in 192 range, please use this instead, 192.168.00 slash 16. Just listen up, take a short break from whatever you are doing. So, there are two files to edit. One is hpghpa.com, which I just discussed, except instead of trust, I suggest you use md file. The second is poscrsql.conf, which is also in the same directory. Edit that file and there is a line which says listen address equal to localhost. You have to go and modify that line to say listen address equal to star and it is normally has a hash at the beginning to comment it out. Do the hash. So, there is no hash and localhost has become star and replace localhost by star. Now, what is that doing? When you say localhost, it only accepts connections from the local machine. There are two parts to the security. One is pghpa, one is poscrsql.conf. So, you have to modify both of them to accept connections from other machines. So, actually you do not need to do it because each of you is running poscrsql on your own machine, but the reason we are doing this is because eventually you may need to allow people from many different machines access to a shared processor. So, you need to do this for that. To restart poscrsql, there is a pgctl restart. If anybody has trouble locating the file pg underscore hpa.conf, do the following. cd slash etc poscrsql 8.4 main and pghpa.conf should be there. The last step which is restart poscrsql, let me just get the path and put it up. Anyway, here is a slightly updated instruction for restarting poscrsql. You can do sudo etc initd poscrsql 8.4 restart. So, you can use this command at the bottom here. So, if you have it in your module, just reload the page. It will show you the updated one. How many of you have successfully completed all the instructions on this page? If you are done with those who are done with this, you can move on to the next page, installing poscrsql. The next one is administering poscrsql and creating users. So, go in there and follow those instructions. How to restart poscrsql? The instructions have just been added at the bottom of this here. sudo etc initd poscrsql 8.4 restart. So, please use module for all instructions. We are updating it with any missing instructions. IIT Bombay runs in the 10 dot segment. So, all machines inside IIT Bombay have a 10 dot something address. What this is saying is any machine in IIT Bombay can connect to this. You can restrict it. You can say only these two machines means the first, no, the first 8 bits have to match exactly, which is 10. The remaining 24 bits can be anything. Again, let me repeat. The printouts which you have are confusing because there is no indentation. Please do not read them. Please go to the Moodle page and read the instructions from Moodle. Do not read today's printout. Tomorrow's printout will be done properly, but today's there was a glitch. tg underscore hb. Tab should do it. Yeah. It will be different. It will be written. It will do a sudo. Cancel. sudo. Then the same thing. Again, for those of you who have finished the first step installation, the second step is configuration. If you go to the Moodle page, the next step in there is configuration. So, let me show it to you. So, after installation, administering poscrsql and creating usurps, how many of you have finished installation? If anyone is having trouble, please raise your hands now and get help if you are in trouble with installation. Please raise your hand. After this, go on to administering poscrsql. How many of you have finished this step? Quite a few of you. Good. So, this requires you to create users. Once you have done this, you can set up each pgadmin3 and connect up from there using the instructions here, using pgadmin3. So, please follow those steps. And when you are done with all of this, you can actually start on the SQL assignment for today. You have to install it. So, just like you installed poscrsql, type install pgadmin3. The same command. So, do apt-get install. Those of you who are having trouble installing stuff, this version of Ubuntu has a software center which simplifies your life quite a bit. This is Windows. I cannot show it here, but on your Ubuntu machines, if you have used that feature, you can use it for installation. You can use Synaptic. You can use whatever you want. apt-get is also fine, whichever thing you are comfortable with, use it to install pgadmin3. So, you are connected up? Yes. Yeah, now you have to connect. The instructions are there on the Moodle page. Listen up. Many of you are facing problems with connection getting dropped. If you unplug the cable and re-plug it, it seems to unplug wait maybe 10 seconds, then re-plug. I do not know why it is dropping the connection, but this restores it. Okay. People, please listen up. If you succeeded in connecting to and starting pgadmin, that is a problem that pgadmin's menu for adding a server goes beyond the bottom of the screen. There is a fix to it, work around, which is to do the following. Once you open pgadmin3, go to file, the file menu. Inside that there is options. Click on options. Once you get options, there is a preferences tab. Click on the preferences tab. There is a font. Go to the font and choose font size 9 instead of 10. After that, it fits in the window. There is no problem. Okay. So for those of you who are not paying attention, please listen. This is important. pgadmin3. Once you start pgadmin3, there is an add server option. That window is too big for this small net book. So these instructions here tell you how to reduce the font size so that it fits. So follow this sequence of menu items to fix it. Okay. So even if you have not finished the installation, please listen up here and you may do it later. So this is assignment 1. So let me start again from here. After installing and connecting, go to assignment 1. And this space install post-crisqlp. You have already done that. Now you can use this ddl.sql script to create the university schema and then this script to load data. So when you, you can save the link as and then load it or you can open it in new window with g edit. Well, I am using windows but you would be using g edit. And after that, there are few SQL queries here which you can try to make sure everything is working properly. Now how do you run these SQL queries? In pgadmin, there is a small icon which says sql. So you have to click on that to open an sql window and you can run these from within, you can run all of this from within the sql window of pgadmin. So once you are able to connect to the database using pgadmin, please start on this. And after checking that everything is okay, there are a bunch of exercises. In today's lab, the first set of exercises there are seven sql queries which you have to write. Then there is one more which you can do offline. You do not have to do here. If you get time today, you can do it. Otherwise you can do it later. Which is one more schema which is a railway schema along with data for it and then a bunch of queries on it. So this is for later. I do not expect you to do it today. If anyone is having problems opening ddl.sql, please raise your hand. How many of you are having problems opening it? For some reason it is not finding, it claims it cannot download it. I do not know quite why. But you can save the file locally and then open it. So if you are not able to open it directly, save the file and then double, in the downloads window, if you double click on it, it works. I have no idea why this is, but save the file and then open it. You can open it from the Firefox download window. Why do we want to use a PostSQL instead of MySQL? You can use MySQL. Anything. We just want uniformity here. We want everyone to be... MySQL also is open. The main reason for PostSQL SQL over MySQL? No, not that. MySQL support for SQL standard is weaker. Till some time back, it did not even do joins properly. Now it does all the basic stuff, but PostSQL supports a much larger subset of SQL than MySQL. So for the purpose of a course like this, where we are using standard SQL, it is better to stick to PostSQL. It is closer to the standard. I get confusing actually. When do you open the new query? Where? When do I need to open a new query? You can just... This is very small. No, but is it... Where can I get that record? What are the queries if you are... Because I am reading everything inside tables and schemas and everything. I do not see in... Everything is happening. So, yeah, we did not use an ID. Normally we should have used an identifier instead of a name. Where? No, no, this space is uniform. Com dot space psi dot. Again, in the real schema, we should have had an ID. We simplified a few things to reduce the work force. Yes. That is true. That is the trade-off. We could have had a department ID and department name. Tell them the thing which is... Any way to bring practice where it is... That is... This... Well, wherever you do not need the rest of the department information, your queries can avoid joining. Print the department name com psi. It is easier than if you print ID 5. You do not know what 5 means. So, it is basically simplified so that your queries are a little easier. You can see what is going on more clearly. We could have used ID... Actually, this is a good question that you are asking. I mean, there are two or three places where... My own... This portion of the semester I have created... One approach is I create semester ID. Yes. If you want to create an academic year, then autumn, winter, fall, whatever it is. Yes. So, these two things jointly... Every time we need to repeat, whenever... Whenever we need to submit a query or write a query. Yes. That force is offered in that semester. That semester means? Yes, you have to put it. Yes. Or we could give ID to them. Yes. So, similarly in this schema, there is a section which is currently identified by four things. These are year, course ID and section ID. So, one of the schemas we thought of, we tried out, was to have a single identifier which identified one integer which identified a section. So... So, for example, it takes relation, all those things. All the four things. You lose the semantics of the attribute thing. Yes. You have to do a join every time. And also, if you think in terms of dependencies and all those things. Many people who follow this philosophy that for every table, there will be a unique integer as the identifier, primary key. A surrogate key which we... Yeah. So, that is a philosophy which many people use, which seems to work. But if we follow that, then... Please, when you are learning SQL, it's a little more work. You have to every time join with more tables. And when you get a query result, you will get these meaningless numbers. You don't know what they mean. Sometimes, it works as whichever offered in a particular semester, say, autumn 2009. Yes. Then, the data inputs, the parameters I write is autumn 2009. I don't have semester code. First, I need to find out the semester code. By joining. Right. And then go back to the... Say, offer statement. Yes. It does not have academic year and semester... Correct. Columns, but it has only semester ID. Yes. First, I need to identify the ID for those given inputs. Anyway, I had the 2009. Yeah. You're getting my point. Yeah, yeah. So, you have to write a join and then a selection. So, code itself has a scientific number. Yeah. Exactly. Yes, that's one of the reasons we did this. Yeah. Exactly. No, no, no. This is exactly the reason we followed this scheme up. What you're saying is exactly right. It would have complicated queries. Railway department is optional. University is the one which... There are two assignments. Assignment one, let me show it on the screen here. So, this is assignment one, which you're all done, at least you're working on. You already run these queries and made sure they work. And then you're supposed to write all these queries. So, these I would like you to do by... If you have finished it here, great. But, you know, you have to write all these queries. So, these I would like you to do by... Here, great. But, quite likely you won't be able to finish all of this. So, you can work on it offline. The second thing in this same assignment is another railway schema. You may find it useful to get the DDL and set it up. So, you can work on it offline. But, you can do this at lower priority. These are... This is just one more schema for more exercise. So, if you don't get time to do this, we are not terribly bothered. What we would like you to do, though, more importantly, is also assignment 2, which was also scheduled for today. Now, we know that there is too much here to do in the lab. The idea is that each of these assignments in the eventual thing will be a three-hour lab. We have squeezed it into two of these into one three-hour lab. So, we don't expect you to finish all of this but the idea is that it will be a three-hour assignment in December. And we are giving it to you now, so that you know what is in there. So, the assignment 2 is using aggregation and primary way on aggregate queries, which we have covered today. So, here is a bunch of queries also on the university schema. And another bunch of queries, which again, I don't expect you to do here. If you have time, you can try it out or you can go back to your institution and try this out. Both the railway queries and this, you can do after you go back and perfectly fine with that. But the first part, I would like you to try offline tonight to make sure everything is okay. If you have problems with any of these queries, we will give you the solution, but we can also discuss it. We will give you the solutions tomorrow. We have all the solutions. Has it been given already? Is it public? Okay. They were supposed to be made visible later. I don't know how this happened. I have no idea why it is visible. Okay. Please don't read it. Please give it a good try before you. Yes. It is hidden, but I think everybody, how did that happen? Yeah. I am wondering if you can see the roles. What is going on? I can see what went wrong. Somebody set up a wrong role in Moodle. You are all non-editing teachers. Yeah, the activity is correct. The same Moodle, other teachers were coming. Yeah. So this should have happened later. Yes. Okay. There were questions about submitting the assignments. So you can upload the assignments whenever you are done with it. So tomorrow morning you could work on it today and then save the queries in a file and then upload the file tomorrow morning when you come in. Assignment 1 and 2. Again, the second part of the assignment you don't have to do. The railway thing and the extra part for the assignment 2. Both of those are optional. If you have time and are able to do it, otherwise don't bother. Do it after you go back. But the first part I would like all of you to do it.