CS352 Lecture - Database Application DevelopmentLast revised 10/9/06Objectives:1. To discuss possible architectures for a client-server application that uses a database2. To discuss use of embedded SQLMaterials:1. Projectable of three and two tier architectures2. Projectable of book figure 8.73. Projectable of db2 create procedure4. Projectable of JDBC code example + variant using a prepared statement5. Projectables of sqlj example - sqlj + pure JavaI. IntroductionA. Thus far, we have used SQL as the means of actually accessing/modifying the database.B. Of course, the majority of people accessing information stored in a database don't do so directly using SQL. Instead, they run an application program. While the application program may store its data in various kinds of application-specific files (the file processing approach), frequently it stores its information in a “generic” (e.g. SQL-based) database. GenericDatabaseSQLSQLApplicationProgramvsGenericDatabaseExamples of the latter? ASKNumerous - many web-based ecommerce systems use a database to actually store the data; also bank tellers, insurance agents ...1II. Architectural AlternativesA. When a user interacts with an application program that stores its data in a generic database, there are three kinds of tasks that are performed:1. Database tasks - tasks related to accessing/modifying information in the database (e.g. tasks corresponding to SQL select, insert, update, delete or the equivalent in some other DML).2. “Business logic” tasks - tasks related to the actual logic of the application (which vary widely from application to application, of course.) Quite a few things might fall into this category, includinga) Carrying out the task(s) that the software is designed to do: displaying information, recording purchases/reservations/..., etc.b) Ensuring that the appropriate “business rules” are adhered too - e.g., for example, if a system is registering students for courses one important “rule” that needs to be enforced is that a student cannot be signed up for two different courses meeting at the same time (at all, or perhaps without some sort of special permission)c) Ensuring that users are properly authenticated if sensitive information is being made available or data is being modified. d) etc.3. User interface tasks - tasks concerned with presenting information to the user and accepting commands from the user. This could be via a command line interface or some kind of dedicated hardware (e.g an ATM), but is often done through a GUI.B. While it is certainly possible for all three kinds of tasks to be done by the same program, many applications make use of some variant of a client-server model, where the database tasks are performed by a “database server” and the user interface tasks are performed by a client computer - typically a PC or the like. Where there is considerable variation is in the matter of the placement of the business logic tasks.1. Thick-client architectures:2Database TasksUser Interface Tasks +Business Logic TasksClient SystemsServer System2. Thin-client architectures: in a thin client architecture, only the user interface tasks are performed by the client. The business logic tasks and database tasks are performed by server(s). Often (though not always), the client is a web browser. In this case, we need an additional server - the web server that delivers up html upon request from the client. A couple of configurations are common:a) It is possible to actually have three servers (though they might actually be separate processes on the same computer). In this case, when the client requests a computation, it is received by the web server and passed on to the application server which in turn accesses/updates the database as needed before passing a response back to the client via the web server.This is often called a “three-tier” architecture.PROJECT: Authors powerpointb) It is possible for the web server to actually perform the business logic computation, contacting the database server as needed to access/update the database.This is often called a “two-tier” architecture.PROJECT: Authors powerpointc) It is also possible for the database server to actually perform the computation on request from the web server, using stored procedures.3C. Each of these Architectures has advantages1. Thick client:ASKa) Much of the computation is done on the client systems, minimizing load on servers2. Thin Clienta) Easier to secure, since the business logic software is on server systems. (Business logic software on client systems is more easily attacked/spoofed; and the business logic software may need to contain some sort of authentication information that should not be generally available.)b) Updating the business logic software is easier, since it resides only on server(s).D. Software support for the various architectures1. Thick clienta) Business logic software needs to reside on the client. This can be an actual program installed on the client which contains both user interface and business logic code, communicating with the database server as needed.Example: The Video Store project in CS211 could be configured this way.Example: Your programming project in this course will be configured this wayb) Alternately, the business logic can be contained in code that is downloaded when needed - e.g. a Java applet or Java web start. (This is more appropriate when the client will run the system relatively infrequently.)2. Thin clienta) If a separate application server/program is used, typically, the URL sent by the client tells the web server to send a message to the application server that invoke the business logic. b) It the web server actually does the business logic tasks itself, then one of two approaches may be used:4(1) The web server may utilize small programs called “servlets”. These are similar to applets, except that they run on the server system, rather than the client system. In this case, the business logic is a Java program that runs on the server.(2) The web pages themselves may contain embedded scripts that the server executes while sending the page. (e.g. JSP, ASP, ColdFusion).In this case, the .html file that the server sends the client in response to a URL is actually generated from the html file stored on the server's disk. Some of it may be literal text that is just passed through to the client, while another part may be a script that the server executes whose