PROFESSOR: First of all, you finished all six exercises, two quiz and one midterm, so we are at a time where we can -- you can concentrate on the project and we have final. So except for final, you should concentrate on mini project now so you have a whole month to do that and I hope most of you can make it to the early bird deadline to earn the three extra points. Now, last week I talked about the first project. Well, of course, each time I try to sell a project to you. So at that time I say the first project is better because it's easier to win more points which is true and also everything is under your control. Now I have to sell the second project to you. Now the second project historically, more people pick this project, about 60 percent this project is interesting because it enables you to combine from a database course with internet programming, so it's a combination of the two. So I choose this web base for you because you can get a free version and also they have very good documentation that you can download. So the first requirement if you want to do this project is that you must have your own personal computer, I think that's not a problem. But you must have already installed on it windows, oracle 8, by now you have done that. We did run into some problem with oracle 8-I so for those you may be better off going back to oracle 8. I'm sorry didn't realize that. That it was a problem to install 8-I. Either personal web server, PWS or internet information system, IS which is the new version already installed on your PC. And enough ram and disk space to install the web base. So that's a given so that you can do everything. The idea is that do not have to rely on any other computer you can do everything on your own, on your own PC. That way will be the best. Then when you -- when a project is due, you bring your PC or notebook to the school so that you can give a live DEMO to me. This way there will be no last-minute surprises and everything is under your control. Just in case this not feasible, you have a computer that weighs a ton, you cannot weigh it, then you can do the following. You prepare the forms. We'll talk about it later. Basically web based programming is to develop the HTL forms. In the form, imbedded in the form there is a program. So basically there are a number of forms that you develop. Then have you to upload your form to a designated user and then you can -- you should be able to run your program there. Well, basically I use a browser and then type in the URL and I can test your program. However, if you do that, have you to be very, very careful that maybe it's better you turn on the light in the back. Yeah. That's good enough, thank you. That's okay because you have a copy in front of you. Basically you need to give your source, database source, the correct name. I already have the name here for you. Which is called LIBTEST. And in the oracle -- in the SQL statement have you to put in the user and pass word. They are both libtest 2 so you have to use those, which means when you test your program, on your own PC, you have to set up your source using the same way. So make sure you link them correctly and then you will be able to test your program, write your program but there is always a danger that things don't work so that's why I strongly recommend that you do it using your own computer. The last one is for those who are really good at setting up a system. You could set up your PC as a server with the modem. Then we'll have a computer here that can dial into your computer and test it that way. That's also doable but a little bit risky. Okay. So what you need to turn in will be -- first of all, I will schedule so that every one of you will give me a live DEMO and then you need to give me a diskette, as well as hard copy, containing all of the forms. And the readme file. The readme file is simple, just one page, telling people how to use your system. So the files, as you can see here, are in your notes, are called HTF because these are the forms and the top form is -- there are two primary forms you will develop called search.HTF and circumstance Las Vegas.HTF if you do the -- circulation.HTF. You need to give me diskette and hard copy of these files. If you are undergraduate student have you to write report, says on the bottom, but this does not apply to you. The requirements are very similar to the first project. What this project is about is to develop a stripped down version of an elaborate -- library information system. The library information system need to support patron, patron is name for user in library world so each patron can search library information system to find out the title that patron is interested in and be able to find out how many copies of book in the library and then if interested in checking out a book, he will talk to the librarian, so the librarian need to check out the book and later, maybe two weeks later the person returns the back, librarian returns the back to the library. If the book is overdue, then a fine is charged. All right? So this is the primary operation of the library. So, if you choose this project, you develop two forms. One is called a search form. This is to enable the user or the patron to search for a title in a library information system. The second form is the circulation form. This is for the user. In this case the librarian to check out a back, return a back and to charge a fine. -- book and return a book and to charge a fine. The system could be more complicated but I reduce it to the level you only have to implement these two forms. Now the optional part which I'll talk about later is very realistic. That is for you to write a program so that you can load so-called marked record into the library database system. Mark record is a record used by library of Congress, by OCRC, all of these agency, this primary format used -- MARC to encode a book title. It may not be a book title, it could be multimedia title. These days, CD-rom, videotape, they are all en coded as Marc record. The bonus part toys write a program so you can read in a Marc record and read it into the database. Okay? Now, let's start from the beginning. Here you see a it's kind of ER diagram which shows you how the database is structured. Now don't worry too much because I will provide the program written in PSQL, SQL plus. And that program will help you to generate and load this database. Okay? So, in a week or so I distribute this program by E-mail and you take that program and you run it on your own computer. You can set up the database. Okay? So, the database consists of six -- seven relations. Now, you will only use a couple of them. In other words, you don't have to worry about everything. The most important one are the following, the books relation stores information about a book title. So you can see a whole bunch of fields. Now the field marked by X these are texts -- tags from Marc record. Now the book record is the primary relation to store information about every book, every title. The primary key is called ISBN. Okay? ISBN is a number, is in digits that you need to identify any title published in whole wide world. ISBN. -- for example if you have your textbook you look at the inside page, there is this ISBN number. But most of the time users do not search by ISBN. You don't search by ISBN. You usually search by what? Either by the title or by the author's name. Right? So you can see that we have the author and the title there. Even for author, you shouldn't -- you type out whole name, your search may be D-E-O, DOE, in other words if you want to search for a title about database user can enter something like this, okay? This means that a title where there is an imbedded string. The same with name. So you search for John DOE. I can imbed it in the percent side. This means I search for any string, based imbedded sub string John DOE. Okay? Now, suppose we have a book in a library, our textbook. Do we have a single copy or multiple copies? For very rare book, maybe just a single copy. But more often than not, we will have multiple copies like this textbook. Usually the library will store several copies. This will increase the chance that people can loan one. So we have multiple copies. Okay? Then we are to identify which copy, copy one, copy two, copy three. So there is the purpose of the copy relation. So each book has ISBN number and if the library keeps three copies, they are called copy one, two, and three. Okay? So, the book ID is given by a particular library to a unique book so let's say this library has three copies of our textbook. Then we have to assign three unique book ID. See what I mean? Although the ISBN number is the same, because ISBN number is given by the library of Congress to a particular book, so every unique book have a unique ISBN number. But if we have three copies of the book, in my library I need to identify all three copies. So this is the book ID. So in the copy relation, we have the book ID and which copy it is and whether it is on loan or not. If it is on loan it is one, if it is not on lean it is zero. In fact a copy relation, the cardinality is the total number of books in the library. I say that again. The copy relation, if you have 4,000 copies in this library, we have 4,000 tuples here. But how many books, titles we have? Maybe just 2,000? So this may by 2,000. This may be because we have multiple copies of the book. And the circulation is when you check out the book. You check out back, you are a student -- book, you are a student, we put that in the user relation. Book ID. Let's say the book is due in two weeks, the due date is here. When it is checked out, the librarian will update this table, right? Some library does allow user to check out books automatically, not for the way you implement it, because it is always safer to have a safe keeper, the librarian. The librarian will let you check out book, then he will use a form to update this relation, namely there is a book checked out by so and so, he enters, book ID, and date it is due and user ID. Some time later the person returns the book. When he returns the book, librarian again goes back to update this relation and then enters the date of return. Within the due period, no fine is charged. If it is overdue, then I think there is a fine of 50 cents or something, which need to be calculated. Okay? So your task is to develop these two forms. One form to search the library, the other form to do the check-out and return. Any questions? Yes? STUDENT: Is there an existing examples of these forms that we could look at and see? PROFESSOR: I can provide some, sure. Basically the search form is -- the search form is this. You enter a title, author, ISBN number. STUDENT: I don't mean what it's supposed to look like. I mean how the actual code be Hynde hind it is working, existing examples of other HTF like forms so we can see how it works? PROFESSOR: Yes. I haven't started talking about web base. That will start on Thursday. Okay. If you have downloaded my notes, which I hope you have, that is in the notes. We'll start on Thursday and talk about that. Actual forms that you can use example. In fact, if you look into the form, you can almost use that as a template to construct these forms. Yes? STUDENT: The Marc -- PROFESSOR: Speak louder. STUDENT: The Marc books -- like exactly what -- PROFESSOR: This basically online copy of what the library received from the library of Congress of the Marc record. You don't have to worry about that. In the optional part -- okay. Basically how the library operates, I receive a physical book, okay. Let's say I got a book here, okay? I receive the book, the librarian will try to enter the information into his library system manually. In the meantime he will contact OCRC in Ohio, service center, saying I have this book, can you send me a Marc record so I can enter that into the computer system? This way everything is online. So he send E-mail to OCRC, the next day they say, look, I'm send sending you these titles. A He'll load that into his system. So the Marc book is online back up of what he receive from the central library system. Although this is a mini project, this thing is very realistic. This is how the real library operates. But in your case, you don't have to worry about that. Okay. It will note examples, but the relation you are to worry about will be one, two, three, four. The book's relation because that's what you have the title, that's why the user searches, the copy relation, because for every copy with have an individual relation. User relation, will have number of fake -- I will have a number of fake users here for you. Because according to this set up, only user, in this relation, can check out books, just like all library system, only you can check out books because have you student ID. So, and then finally the circulation database is where the circulation information is stored. Okay? So to develop the search form owe Kay. First how to create a database I will create for you, so you won't have to worry about that. I provide PSQL program, basically what it consists of are a number of database definitions. Declared relations and then just a couple of insert commands, insert a tuple into the database. This way it's all set up for you. Now, basically you just download the PSQL program and run it in your oracle database system and then you should have set up the database correctly. This program can be rerun because the first part is actually to delete existing relations so any time you messed up your database, you rerun this PSQL program, this is like reinitializing the database, so this program will remove existing relation and then create new relations for you. The reason I want to do, I want to avoid program of setting up databases, secondly this way we are all using same relation name, same attribute names, so this way later on if you want to export your program and run it you should have no problem. Okay. What you need to implement for this exercise will be just two forms. Search form. Looks like this. So you give the author name, title name, ISBN number. Any time user doesn't want to give something, he can just put in -- let's say I don't know the ISBN number and I don't know author name, I only know title. I can type in a title and leave the rest and then click on submit. Now these are all forms that you can access using a browser. So, the form actually in presentation is what? Is a web page. So the form from the user point of view or from the client point of view is actually HTML file. Okay? But HTML file is actually created by this middle ware. Okay? So this form, when the user enters the name, then a return will come back to your browser. Again, a web page. So the return page will contain the actual title and the author's and there will be additional information like how many copies in the library? Maybe there are two. How many copies available? Maybe there is one. Because maybe one copy has been checked out. So this will enable the user to know whether he can actually check out the book. Okay? So, the search form will present to the user HTML form for him to select then the program part will actually create a return form which contains the actual titles. Now once you can do that you realize of course everything about E commerce, stuff like that is the same. It's like this. That's why I think it's very valuable project for to you learn because this is basically the principal of E commerce. Any question about search form? Again, I show you the actual syntax and teach you how to do the programming next time. So this the search form. What is the other form? The other form is the circulation form. Now this form is for the librarian to fill out. So it goes by ISBN number and copy ID. So this book is going to check out by a certain user and the check out date, date out, and the date due. Okay. And if this book actually returned, date returned. And then there is radio button. That is the button where you can select one of them. You select either check out or return. In fact, there is another one, renew. So the lion librarian fill out this form and check whether check out the book, renew O return and then submit a form. The form goes in and update the database. If it is a return, somebody returns the book, the return form will say acknowledge that something, like acknowledge book has been returned and if there is a fine, print out fine due, how much is due so that the librarian can collect that amount from the patron. Okay. So these two forms are all there is to be done. The first form worth five points, this form three points. So let me first talk about grading. In the grading if you can do the search form, if you can set up database, make it work and be able to implement a search form, you already get five points. Because that's the major hurdle have you to go through, you know, install everything on your computer. This project is very much about system integration as well as programming. And then if you can get to the second form so you can update the database another three points. That's eight points, right? And then as usual, one point for handle user input errors. If you make a mistake we'll be able to catch that. and one point for quality of programming, mainly how nice your interface looks and how clean are the forms. So these are the ten basic points. Yes? STUDENT: In the radio button is that check out. The way radio button works is you have several options and you can choose one and exactly one, right? So the default could be checked off the so the is there and user can move it around. You fill in those parts and select one of those options and finally there is the submit, submit the form. Yes? STUDENT: Is oracle 8-I a problem? PROFESSOR: It's not a problem. It requires additional space, additional disk space and also it a student we found out, it doesn't work with win 95. With 98 and preferably the new 2000 it works very well. STUDENT: And this personal web server, is that on the CD? Is that an oracle product? PROFESSOR: No, that's Microsoft. It should be in there. And if you have 98 and above it's already installed. STUDENT: Okay. PROFESSOR: So that one works together with your oracle 8. STUDENT: What if you don't have 98 or above? PROFESSOR: Right that's why I said that, if you don't have this software it's better to work on first project. STUDENT: That should already be in windows. STUDENT: It's on the Microsoft home page somewhere. PROFESSOR: Make sure you have that software installed otherwise you will be very painful for you to make it work. It is still possible but it just takes more work. Yes? STUDENT: Does it matter if you use oracle 8 or 8-I? PROFESSOR: I think better off if you use 8. If you can make 8-I work, okay. In fact I shouldn't say that but let me say, if you use 8-I you don't need to use my middle ware anymore. Obviously oracle will provide the solution so you only need to use oracle product. At least learn how to use -- that is usually provided by third party it service as glue to serve something together and web base is example of middle ware. Now the big guys will try to squeeze out the middle. Their game is -- first they will say dangerous saying they make everything available so company flourish. Once they find there is lucrative they try to squeeze out little guys. Going back to your question about oracle 8, that's exact lit case with oracle. They don't like to see small company like web base take off a part of their market. Yeah? STUDENT: So then I'm not -- is exactly clear. Do we have to use web base or we don't? PROFESSOR: Yes. STUDENT: Yes, we do have to? PROFESSOR: Yes. STUDENT: No option to not. PROFESSOR: That's the requirement, yes. What I just said, that's why I say I open my big mouth. If you use 8-I you don't have to. STUDENT: But we have to, though. PROFESSOR: You have to, right. Again let me open my big mouth, again you are not allowed to do that, but how about Microsoft? Again, Microsoft, database server, they have -- you can do everything in Microsoft as well. Again for the project I would prefer everybody use this web base. STUDENT: I was going to mention Microsoft provides procedures for publishing HTML pages from database. You can use cursor. PROFESSOR: I know that. and generate some pages, you can make the same thing work, similar. But like I said, to this project, let's use web base. I already mentioned the reason. It's a good idea to learn, probably use them. Microsoft provide total solutions., What they call total solutions. Any questions about basic part of the project? Okay. This is very doable. Once you learn web base, next week, you probably can do the whole thing and if you set up everything, very quickly. And that's why the bonus part. Now the bonus part is very realistic. And because this is from the real world. When the library of Congress or library of Congress which all the libraries in the world look up to, because they also create this ISBN numbers. When the library of Congress provide titles, it will encode it in a certain magical format which is called this Marc format. This is universal format, previously it's used in taped medium. This means that the library of Congress or its service bureau, OCRC will send a tape to the library and they charge a bundle for that. And the library when it receives the tape will try to extract this record and load their own database. So this means if you choose to do this part have you to write a program to be able to decode a data structure. So that in itself is a very valuable experience, okay. So let me just explain to you the code format. I think I already went through the basic when I talked about the five structure but please look at your notes. So, this is very common later on you may encounter similar problems, especially when you deal with so-called legacy systems. That means there is an old system left over from day one, maybe several years ago, maybe 20 years ago. But you end up, you have to maintain that. It's not very pleasant but we all have to do that because nobody can afford to throw away all of the software that worked, okay? They always have database structures that are encoded. Now Marc record encode the following way. In coding format usually you have a header, following lowed by the fields. Now, for the library information there are many, many, many fields. In fact the encoding is from zero to 99. But not every record have all of the fields. Why? Because we don't need that. Maybe we just need ten fields out of the 999 fields. What do we do? Well, we have to select only those fields we want, right? And this is why you need a directory. So the header is always a fixed length. In fact a header, I believe is 24 bytes long. After the header of 24 bytes, then there is a directory, each entry in the directory is fixed length. In the 14 bytes of that, what we encode is the tag identified, like 001. How long it is like 0013. All light justified and the starting position. Start position means that from this base address what is the offset to the beginning first byte of the field? Okay? So you you'll need to know the following. First how long is this whole record? Because in the layout, in fact I'll give you only five record but they are going to run consecutively one after the other. If you don't note lengths, you don't know where the second one beginning. If I note length, I know where the second record begin, so on and so forth. And that is the in the first five bytes of the header. And then the bytes position 13 to 17 contains the base address. Remember your assembly language course or did you take it these days? STUDENT: Yeah. PROFESSOR: You do. So we have base address. Then we add displacement and offset. So the base address is here. In 13 to 17. And then comes the record directory. It has variable number of entries. Each entry corresponds to a field here. For example, if I have the tag 010, this happens to be an important tag which is the library of Congress control number. And then how long this field? And then what's the starting position? So the base address plus the starting position, give met address of this field. All right? The program, your program will do the following. It will decode, find out base address and offset and extract each field. And then just print it out. If you can print out then you can generate, this is just by pattern matching, just add something. Insert command. So the insert command is the actual command in the PSQL in SQL plus program that we can know the database. That's how the database is loaded. So we track Marc record and run it and insert new records into the database. Yes? STUDENT: Each Marc record is one book. PROFESSOR: Right or one title, rather. Because the medium is expanding dramatically. So it could be a book or CD or music, anything. So, I will give you test file containing five Marc record in a very long string so you have to chop it up. Based upon the length and then extract the fields. The language, again, I don't care. You can use C, C plus plus whatever, you can deliver the program plus execute it. So. In fact, for this project, the main thing is really the first part. To make it work. In other words to put everything together. Once you can make it work, it won't be too hard to program the two to forms which we'll discuss next time and add on part and this is for decoding and information structure of the any further questions. So for you now you can think about those projects and make a choice but do that quickly.