Citation
A Web-based Database Application Builder

Material Information

Title:
A Web-based Database Application Builder
Creator:
Chen, Lijun
Place of Publication:
(Gainesville, Fla.)
Publisher:
University of Florida
Publication Date:
Language:
English

Thesis/Dissertation Information

Degree:
Master's ( Master of Science (M))
Degree Grantor:
University of Florida
Degree Disciplines:
Computer Engineering
Committee Chair:
Newman, Richard E
Committee Members:
Hammer, Joachim
Dankel, Douglas D, II

Subjects

Subjects / Keywords:
Data tables ( jstor )
Databases ( jstor )
Image databases ( jstor )
Image files ( jstor )
Java ( jstor )
Navigation ( jstor )
Recordings ( jstor )
Spreadsheets ( jstor )
Tables of contents ( jstor )
User names ( jstor )
City of Gainesville ( local )

Notes

Abstract:
This thesis presents a design and implementation of a Web-based Database system (WEBDB) that was developed to meet the increasing demand for Web database application builders. The WEBDB system provides a user-friendly interface that allows the user to open a database and operate on an image map easily. It is a secure database application that integrates traditional database tables and records with unstructured graphical information in one system. The WEBDB system provides the user an easy-to-use method to create/access a database and to manipulate table data. A full line of database functionalities includes creating and modifying a table schema, inserting, updating, and deleting of multiple records, creating new tables from existing tables (views), searching information, and performing spreadsheet functionality. The WEBDB allows the user to operate on an image file freely. The server-side image map can be created through the browser by defining hot regions, and then linking hot regions to other sources, hence creating a two-way connection between the database and the image map. Apart from the links, the WEBDB also provides a navigation index tree to allow the user to navigate a given database more conveniently. It also allows the user to have a big picture about the database without being lost at any time.

Record Information

Source Institution:
University of Florida
Holding Location:
University of Florida
Rights Management:
Copyright Chen, Lijun. Permission granted to the University of Florida to digitize, archive and distribute this item for non-profit research and educational purposes. Any reuse of this item in excess of fair use or other copyright exemptions requires permission of the copyright holder.
Embargo Date:
5/4/2002
Resource Identifier:
51658145 ( OCLC )

Downloads

This item is only available as the following downloads:


Full Text

PAGE 1

A WEB BASED DATABASE APPLICATION BUILDER By LIJUN CHEN A THESIS PRESENTED TO THE GRADUATE SCHOOL OF THE UNIVERSITY OF FLORIDA IN PARTIAL FULFILLMENT OF THE REQUIREMENTS FOR THE DEGREE OF MASTER OF SCIENCE UNIVERSITY OF FLORI DA 2002

PAGE 2

Copyright 2002 by Lijun Chen

PAGE 3

Dedicated to My Family

PAGE 4

iv ACKNOWLEDGMENTS I would like to thank my committee chair, Dr. Richard Newman. Without his excellent guidance, patience, and support this thesis would not have been possible. It was a great pleasure to work with him and I gained valuable experience under h is direction. What I have learned from Dr. Newman during the thesis work will benefit me in my future career. I would also like to thank Dr. Douglas Dankel II and Dr. Joachim Hammer for being on my committee and the advice they gave me during my master’s s tudy. Many thanks go to Mr. John Bowers for being a very helpful graduate secretary. He was always there to answer my questions and offer suggestions. I would like to offer my appreciation to Dongqing Xie, my thesis partner. She was a competitor, teacher, and a great friend. This thesis is a product of our perfect cooperation. Finally, I would like to thank my husband for giving me unparalleled help and support throughout these years. His love and motivation are the sources of my strength and confidence in life.

PAGE 5

v TABLE OF CONTENTS page ACKNOWLEDGMENTS ................................ ................................ ................................ .. iv ABSTRACT ................................ ................................ ................................ ...................... viii CHAPTER 1 INTRODUCTION ................................ ................................ ................................ ........... 1 Description of the Problem ................................ ................................ ............................. 1 Overview of the WEBDB ................................ ................................ ............................... 2 Organization of Thesis ................................ ................................ ................................ .... 4 2 UNDERLYING TECHNOLOGIES ................................ ................................ ................ 5 Technologies Used ................................ ................................ ................................ .......... 5 Java Servlets ................................ ................................ ................................ ................ 5 JavaServer Pages (JSP) ................................ ................................ ............................... 6 Java Database Conne ctivity (JDBC) ................................ ................................ ........... 7 Image Map ................................ ................................ ................................ .................. 8 Some Commercial Web Application Servers ................................ ................................ . 9 ColdFusion 5.0 ................................ ................................ ................................ ............ 9 NetD ynamics 5.0 ................................ ................................ ................................ ....... 10 WebSphere 3.5 ................................ ................................ ................................ .......... 11 WebObjects 5.0 ................................ ................................ ................................ ......... 12 Overview of Some Related Systems in University of Florida ................................ ...... 13 Hive ................................ ................................ ................................ ........................... 13 SORDS ................................ ................................ ................................ ...................... 13 ESORDS ................................ ................................ ................................ ................... 14 Summary ................................ ................................ ................................ ....................... 16 3 SYSTEM WALKTHROUGH ................................ ................................ ....................... 17 Registration/Login Process ................................ ................................ ........................... 17 Menu Bar ................................ ................................ ................................ ....................... 18 Access Control Levels ................................ ................................ ................................ .. 18 Database Menu ................................ ................................ ................................ .............. 19 Ta ble Menu ................................ ................................ ................................ ................... 20 New Table ................................ ................................ ................................ ................. 20

PAGE 6

vi Construct New Table ................................ ................................ ................................ 21 Change Table Structure ................................ ................................ ............................. 21 Delete Table ................................ ................................ ................................ .............. 22 Data Menu ................................ ................................ ................................ ..................... 22 Open & Ed it Table ................................ ................................ ................................ .... 23 Import Data ................................ ................................ ................................ ............... 23 Search ................................ ................................ ................................ ........................ 24 Delete Record ................................ ................................ ................................ ............ 24 Spreadsheet ................................ ................................ ................................ ............... 25 Link Menu ................................ ................................ ................................ ..................... 30 Create Link ................................ ................................ ................................ ................ 30 Autolink ................................ ................................ ................................ .................... 30 Display Links ................................ ................................ ................................ ............ 31 Delete Link ................................ ................................ ................................ ................ 31 Image Menu ................................ ................................ ................................ .................. 32 Import Image ................................ ................................ ................................ ............. 32 Open Image ................................ ................................ ................................ ............... 32 Rename Image ................................ ................................ ................................ ........... 34 Clone Image ................................ ................................ ................................ .............. 34 Delete Image ................................ ................................ ................................ ............. 34 Permission Menu ................................ ................................ ................................ .......... 34 System Administration ................................ ................................ .............................. 35 Group Administration ................................ ................................ ............................... 35 Access Control ................................ ................................ ................................ .......... 35 Navigation Index Tree ................................ ................................ ................................ .. 35 Summary ................................ ................................ ................................ ....................... 36 4 IMPLEMENTATION DETAILS ................................ ................................ .................. 37 Installing Process ................................ ................................ ................................ .......... 37 Session Tracking ................................ ................................ ................................ ........... 39 Connection Pool ................................ ................................ ................................ ............ 39 Create/Open Database ................................ ................................ ................................ ... 40 Table Creation ................................ ................................ ................................ ............... 40 Delete Database/Table/Record ................................ ................................ ...................... 42 Construct New Table ................................ ................................ ................................ .... 43 Change Tab le Structure ................................ ................................ ................................ . 44 Open & Edit Table ....................................................................................................... 45 Import Data into Table ................................ ................................ ................................ .. 46 Spreadsheet ................................ ................................ ................................ ................... 47 Navigation Index Tree ................................ ................................ ................................ .. 49 Summary ................................ ................................ ................................ ....................... 50 5 TESTS AND RESULTS ................................ ................................ ................................ 51 Testing ................................ ................................ ................................ ........................... 51 Test1 Create a New Database ................................ ................................ ................... 51

PAGE 7

vii Test2 Create New Tables ................................ ................................ .......................... 51 Test3 Insert Data Into Tables ................................ ................................ .................... 51 Test4 Modify Data in the Table ................................ ................................ ............... 52 Test5 Import Records Into the Table ................................ ................................ ........ 52 Test6 Change the Table Structures ................................ ................................ ........... 52 Test7 Construct a New Table ........ ........................................................................... 53 Test8 Search for Information F rom the Database ................................ ..................... 53 Test10 Perform Spreadsheet Functionalities ................................ ............................ 54 Test11 Delete Database ................................ ................................ ............................. 55 Test12 Test the Navigation Tree ................................ ................................ ............... 55 Error Checking ................................ ................................ ................................ .............. 56 Summary ................................ ................................ ................................ ....................... 58 6 CONCLUSIONS ................................ ................................ ................................ ............ 59 Overview of WEBDB ................................ ................................ ................................ ... 59 Future WEBDB Improvements ................................ ................................ ..................... 60 REFERENCES ................................ ................................ ................................ .................. 62 BIOGRAPHICAL SKETCH ................................ ................................ ............................. 64

PAGE 8

viii Abstract of Thesis Presented to the Graduate School of the University of Florida in Partial Fulfillment of the Requirements for the Degree of Master of Science A WEB BASED DATABASE APPLICATION BUILDER By Lijun Chen May 2002 Chairman: Dr . Richard Newman Major Department: Computer and Information Science and Engineering This thesis presents a design and implementation of a Web based Database system (WEBDB) that was developed to meet the increasing demand for Web database application bui lders. The WEBDB system provides a user friendly interface that allows the user to open a database and operate on an image map easily. It is a secure database application that integrates traditional database tables and records with unstructured graphical i nformation in one system. The WEBDB system provides the user an easy to use method to create/access a database and to manipulate table data. A full line of database functionalities includes creating and modifying a table schema, inserting, updating, and de leting of multiple records, creating new tables from existing tables (views), searching information, and performing spreadsheet functionality. The WEBDB allows the user to operate on an image file freely. The server side image map can be created through the browser by defining hot regions, and then linking

PAGE 9

ix hot regions to other sources, hence creating a two way connection between the database and the image map. Apart from the links, the WEBDB also provides a navigation index tree to allow the user to navig ate a given database more conveniently. It also allows the user to have a big picture about the database without being lost at any time.

PAGE 10

1 CHAPTER 1 INTRODUCTION This chapter begins with a description of the problem addressed within this thesis, followed by an overview of the Web based Database system (WEBDB) that was developed to solve this problem. The chapter concludes with an outline of the chapte rs organized in this thesis. Description of the Problem The popularity of the Internet and World Wide Web has resulted in a wealth of information being shared and exchanged throughout the world. The need for not only viewing material over the Web but also interacting with the Web server and manipulating data has become imperative. The Web based or on line database application has made it possible. In a typical Web based database application, the database is created on the server side by providing the user a n interface from the Internet browser. Among all current applications, however, they are either too complicated to have a user friendly interface, which is not good for ordinary users who do not have much database knowledge, or they do not have some featur es in which we are interested. For example, some applications do not provide spreadsheet functionality; some applications do not allow user to change the database schema, and so forth. In addition, the Internet offers not only ordinary text information but also some more file types, such as images. Users would like to define hot spots in an image file and link these spots to other sources. An image map is created in this way. Users may need to

PAGE 11

2 incorporate an image map into a traditional database and constru ct a connection between a database and an image map so that the user also can view graphical information and access a database. The WEBDB is developed just for this reason. The goal of the WEBDB is to create a simple, clean interface to help the non progra mmer user build a database application and integrate an image map into the database. This project was done as a two person project. My partner was Dongqing Xie, who completed the following tasks: rename an image, clone an image, define Regions of Interest (ROI), zoom in/zoom out on an image, link creation, autolink, system administration, group administration, and access control. I addressed database creation and deletion, table creation, deletion and update, change schema, construct new table, records ins ert, update and deletion, import data, spreadsheet and navigation index tree. The rest we did together. Overview of the WEBDB The WEBDB is a general database application developed to solve the problem previously described. It focuses on the users who do no t have much knowledge of databases and SQL technology. It provides a user friendly interface that allows the user to create a database and an image map, and make manipulations on the table data and image map as well. It is an integration system of traditio nal table/record and the unstructured graphical information. The system has a secure method to achieve a better control of the system and maintenance of the database. There are three levels of access control: System Administrator, First Class Member, and S econd Class Member. The system Administrator has the full right to access all databases and tables within the system. The

PAGE 12

3 First Class Member can do operations on an authorized database. The Second Class Member is like the common user and can only view data in the system. The WEBDB allows the user to create and modify the database schema. It also allows the insertion, update, and deletion of multiple records. It allows the user to import data from a local file to an existing table. This feature is very conve nient for those users who would like to insert a large amount of data into table. The user can search any information in the system. The system also allows the user to create a table view from two or more existing tables to obtain a different view of the d atabase. There is also a capability of performing mathematical operations within the WEBDB. Three types of spreadsheet operations, basic computation, weighted computation, and range computation, are developed to meet users’ different needs and apply to di fferent situations. WEBDB provides a capability for the user to manipulate an image file freely. The user can upload an image file from his local machine, and then create an image map by defining hot regions and linking these hot regions to other sources (either table information inside the database or a website outside the database). A two way connection between a database table and an image map is therefore created, which allows the user to manipulate the table data and an image file more freely. WEBDB a lso provides a navigation index tree in its interface. By clicking the nodes of the navigation tree, the user can view the table contents or open the image map. This feature not only allows the user to have a general idea about the system, but gives the us er another chance to interact with the system.

PAGE 13

4 Organization of Thesis This thesis consists of six chapters. Chapter 2 introduces some commercial database applications and provides an overview of some related work at the University of Florida. This chapter also discusses the technologies that are used to implement the WEBDB system. Chapter 3 outlines the WEBDB system by presenting an explanation of its functionalities. Chapter 4 discusses the implementation details from a programmer’s perspective. Chapter 5 addresses tests and results of the table/data manipulation part of the WEBDB system on the basis of a sample application, School Grading System. The chapter concludes with a discussion of some error checking. Conclusions and future work are presented in Ch apter 6.

PAGE 14

5 CHAPTER 2 UNDERLYING TECHNOLOGIES This chapter presents an overview of the underlying technologies used to implement the WEBDB application. This includes a brief review of Java Servlets, JavaServer Pages (JSP), Java Database Connectivity (JDBC), and Image Map. This chapter also discusses some commercial web database systems and some related systems at the University of Florida. Technologies Used Java Servlets Before Java Servlets, the Common Gateway Interface (CGI) was the most common choice for server side programm ing. CGI is a standard for interfacing external applications with information servers, such as HTTP or web servers. CGI was popular in the early history of the World Wide Web due to its universality because it can run Perl, command shell, or even C and C++ programs. CGI has some serious disadvantages, however, involving performance and scalability. When Java Servlets came out, because of their great advantages over CGI, they rapidly became the standard tool for building dynamic Web sites and connecting Web front ends to a database and applications on a server. As with CGI programs, Java Servlets are programs that run in a Web server, acting as a middle layer between a request coming from a Web browser or other HTTP clients and databases or applications on th e HTTP server. A servlet is a Java class that executes in a Java Virtual Machine (JVM) service. The servlet engine loads the class in the JVM. One instance of the servlet class is created at startup or the first time the servlet is requested. Each request for the servlet is then executed using one separate thread of the

PAGE 15

6 same servlet instance. The job of the servlet is to read any data sent by the user, process data (querying information from database), generate results, and send the document back to the cli ent. The servlet remains between requests and can keep persistent data in instance variables [1, 2]. Using Java servlets is perfect when some static documents are not sufficient and a page needs to be generated for each request. It is also very useful for some activities such as on line conferences because it is multithreading, and concurrent requests for a servlet are handled by separate threads. Since a servlet program can forward requests to other servers and servlets, the load among several servers also can be balanced [1 3]. JavaServer Pages (JSP) JSP is a technology that combines static HTML and dynamically generated contents. Unlike Java Servlets, JSP does not require compilation, but a JSP is translated into a servlet program the first time it is in voked by a client. The resulting servlet is a combination of HTML and dynamic contents specified by scripting tags. JSP has some predefined variables: request, response, session, and out. These variables are objects of HttpServletRequest, HttpServletRespon se, HttpSession, and PrintWriter, respectively. JSP also has some capabilities to include external pieces into a JSP document. For example, the “include” directive allows a page to reuse some code segments such as menu bars, tables, and other elements; usi ng “jsp:include” allows a page to include an existing file; and using “jsp:plugin” directive allows a page to insert a client side applet into JSP pages. These capabilities together with the predefined variables make it simple to create a dynamic web page because we do not need to write code repeatedly [1, 2].

PAGE 16

7 Java servlets and JSP are powerful server programming technologies. Each of them, however, has its own advantages over the other. Java servlets are excellent when the user’s application requires much programming to accomplish its tasks, such as using cookies, accessing databases, generating GIF images, and so forth. JSP simplifies the task of writing output to the HTML file, which would be tedious if using servlets. Furthermore, the results that JSP y ields will be dynamic while the output generated by servlets is hard to modify. Since it is convenient to forward a servlet request to a JSP page, or include a servlet segment in a JSP page, it is advisable to integrate both Java servlets and JSP when impl ementing a complicated application. Java Database Connectivity (JDBC) JDBC is Java’s version of the Database Interface (DBI) module in Perl 5. It provides a standard interface between Java applications, applets, and the database server. It is based on dyna mic SQL which is much more flexible than static SQL [4]. Just as with the Database Dependent (DBD) modules to connect DBI to multiple systems, JDBC is supported by a large set of JDBC drivers, including Sybase, Microsoft Access and Oracle DBMS. To access a database through JDBC, the first procedure is to open a connection to the database, resulting in a database object. A connection object represents a native database connection and provides methods for executing SQL statements. Figure 2.1 illustrates how a Java application communicates with a database system. As shown, a single application can communicate with more than one database system at a time. This is useful especially in the case of different database systems that are accessed in a whole system [2 ].

PAGE 17

8 Fig 2.1. Communication of Java Application with Database Systems Accessing a database is a time consuming activity because the database must allocate communication and memory resources, authenticate the user, and create the corresp onding security context. Establishing a connection once and then using the same connection for subsequent requests can therefore dramatically improve the performance of a database driven web application. The database connection pool is a straightforward so lution. A connection pool is a cache of database connections that are maintained in memory. By preallocating database connections and reusing them, performance can be greatly improved [1, 5]. Image Map An image map is a graphically selectable ("clickable") image that contains one or more hot regions, which correspond to URLs or other links. Selecting a point brings up the document specified by the corresponding URL or links. Three components are contained in an image map: an image, a set of map data, and an HTML host entry. Map data are descriptions of the hot regions within the image. The host entry is the HTML code that positions the image within the Web page and designates the image as having map functionality [6]. Since images could contain much more vi sual intuitive information than regular text, image maps are used extensively on the World Wide Web in various fields. Java GUI JDBC Driver Interface Manager Driver (Oracle) Driver (ODBC ) Driver (Sybase) Oracle DB MSAccess Sybase

PAGE 18

9 There are two types of image maps: a server side image map and a client side image map. Client side maps reside within the HTML document and are translated using the local user’s browser. Server side image maps are interpreted by the web server. When the image map is clicked by a user, a program running on the web server will catch coordinates from the browser and examine the map data to de termine the link. Both types of image map have their own advantages. The client side image map works more quickly than the server side image map and no Internet connection is needed. However, not all Web browsers support them. For a server side image map, an interesting feature is that the links and hot regions that the user defined can be saved on the server side so that this information can be retrieved when the user visits the image map later or by other visitors. The image maps used in the WEBDB system belong to the server side image map [6, 7]. Some Commercial Web Application Servers Application servers connect database information (usually coming from a database server) and the end user or client program (often running in a Web browser). They are a “mi ddleware” between data and the user. Among all application products that have emerged, each product has its own features and emphasis, depending on the different requirements of security, scalability, business logic management, and database connectivity. T he following list includes a brief review of some widely used application servers [8]. ColdFusion 5.0 ColdFusion 5.0 is a tag based enterprise level application server from Allire Corp. (that has merged with Micromedia). ColdFusion actually consists of two compoments: ColdFusion Server for deploying applications and ColdFusion Studio for creating applications. They form an integrated development environment (IDE) for the user to

PAGE 19

10 develop powerful Web applications. ColdFusion 5 provides some new features over its previous versions. It has powerful business intelligence capabilities and delivers enhanced performance. ColdFusion is an excellent basis for electronic commerce. It is scalable and extensible with multiple integration technologies. It includes, for e xample, native drivers for Oracle, Sybase, and ODBC database connectivity, and also includes support for email protocols, directory services, and some other specific capabilities, such as connectivity to COM and CORBA objects [9, 10]. The uniqueness of Col dFusion is that it is not totally built around Java. It relies instead on a tag based server scripting language, ColdFusion Markup Language (CFML). CFML is combined with HTML to create applications consisting of pages and components, which are then run on the server. CFML is robust and is able to support XML, CORBA, COM, and C/C++ language. This feature of ColdFusion (lack of total support of Java but relying on proprietary tools such as CFML) also should be considered as a drawback of ColdFusion [11]. NetD ynamics 5.0 NetDynamics, a product of Sun Microsystems, is possibly the best application server on the market. It is a comprehensive solution for the development, integration, and deployment of enterprise portal applications, and it uses Java for server si de multitiered network applications. NetDynamics consists of five components, each with differing strengths and weaknesses. The three main components are NetDynamics Application, Command Center, and NetDynamics Studio. NetDynamics Application Server runs on a Java Virtual Machine. Command Center is the management center of the application server, managing either an individual server or a cluster of servers. It is used to create, modify,

PAGE 20

11 save, and activate multiple system configurations, and move components from machine to machine. NetDynamics Studio is a powerful IDE tool for building powerful Java applications. It includes an integrated debugger and full support for team development. A unique feature in NetDynamics is that it allows for mixed the use of HT ML and Java. This ability enables a user to take full advantage of the application according to his own situation [12]. NetDynamics 5 is the latest release of the NetDynamics application server platform. The most significant enhancements with the NetDynami cs 5 release include the support for Enterprise JavaBeans, performance improvements, new security features, and better interoperability with other systems [13]. WebSphere 3.5 The IBM WebSphere Application Server is a Java Servlet based application deployme nt environment for server side applications and JavaBeans. The server is comprised of two components: the runtime environment and the integrated tools environment. There are three editions of WebSphere Application Server are available. Standard Edition pro vides a complete environment by supporting Java Servlet, JSP, XML and XSL and it is mainly used for building personalized, dynamic Web content. Advanced Edition provides all the features found in the Standard Edition, as well as business related logic writ ten in Enterprise JavaBeans (EJB). It also provides the connectivity and integration capabilities with existing databases, transaction systems, and other applications. Advanced Edition is intended for medium to high level transaction environment. Enterpri se Edition provides the highest levels of security, performance, and availability. It combines distributed object and business process features with world class

PAGE 21

12 transaction processing features and can be used for the developing, deploying, and managing of the enterprise e business applications [14]. The WebSphere Application Server is an excellent Java based application server for enterprise level users that requires the highest performance. However, for some small to medium size businesses that do not ne ed high performance, this product is probably too expensive. WebObjects 5.0 WebObjects 5.0 by Apple Computer Inc. is the original object oriented application server. It provides a comprehensive suite of tools and object oriented frameworks that allow the u ser quickly to develop and deploy scalable, reusable Web and Java applications. There are four different visual development tools in WebObjects: the Project Builder is the main tool for editing, compiling, and debugging Java and C based application; the E nterprise Objects Modeler maps relationships between data sources and business objects in applications; the WebObjects Builder designs the HTML interface; and the Interface Builder creates applet layouts with JavaBeans and Swing UI elements. The user can c hoose either Java, Objective C, or WebScript for developing server side applications. Connecting WebObjects to the database is supported with Oracle, Sybase ODBC, and other connectivity drivers. One advantage of WebObjects is that it ships in four major se rver platforms for server side development: Windows NT, HP UX, Sun Solaris, and Mac OS X Server. The major weaknesses of WebObjects are lack of support for Enterprise JavaBeans and poor support for the load balancing and failover [15, 16].

PAGE 22

13 Overview of Some Related Systems in University of Florida Hive Hive, developed by Xiaoxi Cao [17], is also called Spatial Web Database System. It is an extension of Changlu Yang’s Apiary System [18] and integrates graphical information with Apiary System’s functionality o f building database applications on the Web. In the Hive System, the user can access the contents of a database, create a database schema, and do insertion, deletion, and update operations on the records. Hive also allows the user to define hot regions and to link them to the database to create an image map. Hive contains a two way connection between the database and an image map; the user can either access the database from an image map or manipulate image map from the database. There are three operation modes in the Hive System: the creator mode, the editor mode, and the user mode. The creator can create the database schema, set up access control, manage database records, create an image map and link the map to database tables; the editor can manage recor ds of authorized database tables over the Web (execute basic insertion, update, deletion, and retrieval of records); and the user mode allows users to query database records directly or view data through the image maps. SORDS Secure On line Relational Data base System (SORDS), created by Thomas Davis and Joel Manner [19, 20], is a more mature and secure web database application which allows the user to create databases, define tables, and manipulate data through the Internet via the browser.

PAGE 23

14 Three access co ntrol levels are implemented in SORDS: administrator, creator, and user. The administrator has unrestricted access to perform any operations on the database and its tables. The creator has full access to a specific database for which he has access control and full access to all tables belonging to this database. The user has the least access right and can perform only four potential operations: view, insert, delete, and update data in a table. One additional access policy specification mechanism supported i s the group, which may be formed by the administrator or the creator to allow some users abilities to perform operations on some databases and tables. There are three kinds of major menus in SORDS. The “Database” Menu allows three different database opera tions: create/select/drop database. The “Table” Menu provides table operations specific to each database within the SORDS system: these operations allow the creation, selection, and deletion of tables within the given database. It also allows the creation of view from one or two tables. The permission is set to each table through this menu. The other major menu is the “Main” Menu, which allows the user to manipulate an individual table, for example, insert, delete, update rows, add, drop columns, and so for th. ESORDS Enhanced Secure Online Relational Database System (ESORDS) is built by Dan Kong [21]. It is an improved web database system built on the base of SORDS. Besides having all the features of its predecessor, such as open database connectivity, cros s platform, secure access control, and a user friendly interface, the major improvements of ESORDS over SORDS include the ability to create views from multiple tables and being able to perform multiple transposition. Also, the spreadsheet function in SORDS is strengthened in ESORDS.

PAGE 24

15 In ESORDS, a view can be derived from multiple base tables, as well as from existing views, and regular operations can be performed on these views just like the regular tables. Table transposition is achieved by viewing several parallel base tables with identical column names from a different angle. The spreadsheet capability is strengthened by addressing individual cell values in the special rows using absolute cell referencing. In comparing these three systems, each has its st rengths and weaknesses. Hive allows the user to create a database and an image map and therefore establish a two way connection between them. However, it does not support view creating functionality or spreadsheet capability. It does not allow adding or dr opping columns either. That is, once the user created the database, he may not change its schema. Importing data from the user’s local files to the server side’s database is not supported in the Hive System, which would be inconvenient for the user who nee ds to insert a large amount of data. There is only image zoom in functionality but no zoom out capability implemented in the Hive System. Compared to the Hive System, SORDS/ESORDS is a much more mature web database system from the database point of view. They also have a user friendlier interface and fewer programming faults than Hive. However, though ESORDS makes some improvements over SORDS, there are still some limitations and some areas upon which further improvements could be made. For example, there is no graphical information integration in the ESORDS system. There is no way for the user to create an image map and to build a connection between database and an image map. The user may not change his password. The interface could be made cleaner and cou ld allow the user to

PAGE 25

16 have a big picture about the database (for example, a navigation tool could be added into the system). Summary This chapter covers the underlying technologies that are used to develop the WEBDB system, including Java Servlet, JavaServe r Pages, JDBC, and Image Map. The chapter also gives a brief review of some commercial application servers. Some related systems at the University of Florida are discussed as well. These systems, which include Hive, SORDS and ESORDS, are common in that the y are CGI applications and each has its strengths and weaknesses. The WEBDB system is using Java and other technologies to integrate their strengths and create a brand new interface such that the system is more powerful and portable.

PAGE 26

17 CHAPTER 3 SYSTEM WALKTHROUGH This chapter gives a high level overview of the system by providing a detailed explanation of the functionalities of the system and all its internal components. This includes an overview of some main menus in the system (database, table , data, link, image, and permission). Registration/Login Process The front page of the entire system is the Registration/Login page. A new user can register by entering his personal information, such as name, email, preferred user name, and password. The u ser is asked to repeat his password again to confirm the password. Some error checking is performed. For example, if the user selects a user name that is already used by somebody else, or if the passwords he entered twice do not match, or if he does not pr ovide an email address (email address is required in case at a later time the user may need to the change password so that the system can send an email to notify the user) or if there is some error in the email address (for example there is no “@” sign in the email address), the user will be notified and needs to try again. If everything is correct, the user is logged into the system by showing the welcome page in the left frame and the main menu in the right frame of the page. If the user already has an a ccount, he can login into the system using his username and password, which are checked to see if the username and password match. A special case is for the System Administrator, who can be logged into the system by using “sa” username and providing the pr oper password.

PAGE 27

18 Menu Bar After the user logs into the system, a menu bar appears on the screen. The menu bar contains seven menus: Database, Table, Data, Link, Image, Permission , and Logout . For each individual menu, a help information page is provided to i ndicate the main features of this menu. It also provides a brief description of how to use the menu. Each menu contains some submenus when the mouse moves over it. In the Databas e menu, there are “New,” “Open,” “Delete,” and “Close” submenus; Table menu co ntains “New Table,” “Delete Table,” “Construct New Table,” “Change Table Structure,” and “Close” submenus. Data menu includes “Open & Edit Table,” “Import Data,” “Search,” “Spreadsheet,” and “Delete Record.” These three types of menus consist of the basic database/table/data manipulation functionalities. In Link menu, four submenus are included: “Create Link,” “Display Link,” “Delete Link,” and “Autolink.” Image menu contains submenus such as: “Import Image,” “Open Image,” “Rename Image,” “Clone “Delete Image.” Operating on an image map and creating links between the image map and database can be achieved by using these two menus. In addition, the menu bar also provides a Permission menu, which allows the user to set group permissions, to do syst em administration, and to manage user accounts. The WEBDB system provides a safe way to allow the user to logout from the system by clicking Logout menu. Access Control Levels To achieve better control of the system and maintain security of the database an d at the same time allow as many users as possible to access the system, three access control levels are implemented in our system: System Administrator, First Class Member, and Second Class Member. The System Administrator has unrestricted access

PAGE 28

19 to any d atabase and any tables. Not only can he view the contents of any database, but he can perform any operation on the database/table: drop database, create/delete table, construct new table and change table structure, and insert/update/delete data in the tabl e. He also can give authorization to users and add/delete an account. A First Class Member has more restriction than System Administrator. By default, the person who creates a certain database is the First Class Member of this database, and he has full acc ess to this database and can assign different authorizations to the group members. A Second Class Member only has the right to view the contents of a database. An enhancement to access control in WEBDB system is that if System Administrators or the First C lass Members would like a certain database to be open to the public, that is, allow every user to access this database, he can add “public” to the Second Class Member list. This way every other user has the same permission rights as the other Second Class Members. Database Menu The database menu contains four submenus. “New Database” allows the user to create a new database by prompting him to enter a name for the new database. As long as this name is valid and does not conflict with an existing database name that he created before, the new database is created. Our system allows different users to have the same database names, but each user must have unique database names. To select an existing database, the user must click “Open Database” and select a dat abase name from the drop down list. If the user is a system administrator, the list will display all the databases existing in the entire system; for other users, the list will give all database names in which the user is a First Class Member or Second Cla ss Member, including any database that is open to the public. “Delete Database” will only list the databases that the user has permission to delete. At any time user can open only one database, so if in the middle of

PAGE 29

20 the session the user would like to open or create another database, he must first close the current database, causing all information displayed in the screen to disappear. Table Menu Under this menu, the user can create new tables, construct a new table from existing tables, change the table s tructure, and delete an existing table. However, all these operations can be performed only by First Class Members of a given database or the System Administrator. If a Second Class Member tries to perform these operations, an error message will be shown o n the screen. New Table This menu prompts the user to enter a table name that must not conflict with an existing table name in the current database. Once the table name’s uniqueness is verified, the user enters the prototype information for this new table, that is, he creates a table schema refer to Figure 3.1. The table schema includes fieldname, data type, length, constraint, and primary key information. All incorrect input and errors are checked and the user is notified, for example, if no length input is given for a varchar or a char type attribute, or if there is a duplicate primary key, and so forth. An improvement of our system over ESORDS is that it does not require the user to know how m any columns he needs to create in advance. Instead, each time after the user enters prototype information for one column and clicks the “Next” button, the next screen will provide the user an empty line to enter information for the next column. This is mor e convenient for the user. After the user clicks the “Finish” button, all information is saved and a new table is created in the current database.

PAGE 30

21 Figure 3.1 Entering Prototype Information for A New Table Construct New Table Constructing a new table is actually creating a view from existing tables. The user need s to provide a name for this new table that does not conflict with other table names. All tables belonging to this database are then displayed for the user to select, followed by all column names of those tables that the user chooses. Finally, all contents of this new table will be displayed on the screen. By joining some columns of tables, the user can have a different view of tables in this database. Change Table Structure The “Change Table Structure” menu allows the user to change the table schema. Two options are included: add a column and drop a column. If the user wants to add a column, he needs to enter the prototype information for this new added column. Following the same procedure as used when creating a new table, the information of fieldname, data type, length, and constraint is required. All default values for this column

PAGE 31

22 are null, which the user can update later in the “Open & Edit Table” menu. Dropping a column involves the user selecting a table name, selecting a column name and checking fo r primary key (since user cannot drop a primary key column). Once the column is dropped, all data in this column and in associated columns (for example, in views) are removed from the table. This page is shown in Figure 3.2. Figure 3.2 Change Table Struc ture Page Delete Table If a user indicates that he wishes to delete a table from the database, he is given a confirm delete message. If the answer is yes, the selected table is removed from the database, including all data in this table and all informatio n depending on this table. Data Menu This menu provides functionalities to manipulate data in a given table. It allows the user to open a table and to edit table data, to import data from a local data file, to search data from table(s), to do spreadsheet operations on the table(s), or to delete records

PAGE 32

23 from a table. As before, only the System Administrator (SA) and First Class Members of a given database can edit data and import data, while opening table, searching data and computation operation can be per formed by any user with database access. Open & Edit Table The “Open & Edit” menu, shown in Figure 3.3, combines two operations: viewing the contents of a table and editing data in a table. An empty line is provided at the bottom of the displayed records s o at any time the user can insert data into a table conveniently. The same holds true for entering prototype information for a new table: the user is not required to know how many rows (records) he needs to insert in advance, which is more friendly than ES ORDS. If user would like to modify some data, he needs to select the checkbox after a certain record, and then the next screen shows up. The user can change any column’s data of this table except the primary key. An error message will notify the user if he tried to modify the primary key. Import Data Once a user has successfully created a table schema in a database, he may want to populate his table with significant amounts of data. Inserting one record at a time, as the “Open & Edit Table” menu provides, is tedious work. Therefore our system provides another option that allows the user to “bulk load” a large amount of data from the user’s local data file, as long as the format of the data file matches the table definition and there are no duplicate primary key values in the data file with table existing records. The user needs to specify the path of the local data file from his local machine through the “Browser” button and click “Submit,” then all data from the file is in serted into the given

PAGE 33

24 Figure 3.3 Open & Edit Table Page table quickly and automatically. If the user tries to import data with duplicate key or the format of data file doesn’t match the definition of the table, an error message will show up and notify the user. Search The “Search” menu fu nctions as a search engine and allows the user to query any word from one or all tables that belong to the current database. Once a match string found, the information of this record, including in which table the word is found will be displayed on the scre en. Delete Record The “Delete Record” menu provides the user the capability to delete a certain record and allow multiple deletions conveniently. The user is provided a series of “select” checkboxes with each checkbox followed by the contents of each reco rd. The user needs

PAGE 34

25 to select the checkbox before the record that he would like to delete and click “Submit” button, then this record will remove from this table. Spreadsheet The “Spreadsheet” menu allows the user to perform three kinds of computation func tionalities: basic computation, weighted sum computation, and range computation. The user selects a table name from the drop down list and chooses one of the computation options from the radio buttons. The user can decide whether he wants to see the conten ts of the table because in some cases there are many records in the table and the user may not want to display all of them. Two computation options are provided for basic computation. One option is selecting some columns for all records and performing MAX, MIN, AVG, COUNT, and SUM operations. In the Figure 3.4, which shows spreadsheet option1, there are five checkboxes on the top of table “grade” contents with each checkbox followed by one of these operation names: MAX, MIN, AVG, COUNT, or SUM. At the botto m of each table column, a checkbox is provided for the user to choose the specific column(s). For example, if the user selects MAX, MIN, AVG, and SUM checkboxes, and selects checkboxes below proj1, proj2, exam1, exam2, and final, and clicks “Submit,” then the next page will give the computation results: the maximum, minimum, average, and sum of all records’ proj1, proj2, exam1, exam2, and the final grade will be displayed on the screen. For all spreadsheet functionalities, we assume that only “integer,” “fl “money” data types can have computation performed. So if the user also clicked checkboxes under “name” and “studentID” whose data type both are varchar, then a database error would have occurred, and an error message will notify the user to go bac k to check the data types of selected columns. The other option for basic computation is

PAGE 35

26 selecting one record that satisfies a certain constraint, and then perform MAX, MIN, AVG, or SUM operations for some columns in the records. For example, if the user clicks “MAX, MIN, SUM” of columnName=”exam1, exam2, final” where “studentID=12345,” then the next page will show the maximum, minimum, and sum of exam1, exam2, and the final grade of the student whose ID is 12345. Figure 3.4 Basic Computation Page The we ighted sum computation functionality is very useful. For example, in a school grading system, the instructor may wish to provide the student the percentage distribution of a final grade that is comprised of homework, project, exam, and so forth. In a finan ce system, users may wish to give a fund amount’s different distribution rating for a different budget. In some cases, users would like to provide different computing algorithms. All these algorithms should be saved in a database so that users have freedom to choose from any of them to compute the final results at the time of processing the data

PAGE 36

27 or in the future. Usability is our intention in implementing weight computation. After the user selects a certain table name, as before, he has a choice whether or not to see the full contents of the table. Then he can click the “option2” radio button to select weighted sum computation. On the next screen, the user is prompted to enter a weight name in the first text area followed by a few more text forms for enterin g different weights. As before, we assume only those data of type “integer,” “float,” or “money” can be selected for computation, so the user needs to know which column can be selected, otherwise, a database error will occur. The Figure 3.5 is an example: the user enters: .1, 0.1, 0.2, 0.2, 0.4” five different weights for proj1, proj2, exam1, exam2, and final exam grade, which is composed of the first computing algorithm (named weight1), and also enters .1, 0.1, 0.2, 0.2, 0.4” for the second algorithm ( weight2). After he clicks “Submit,” the next screen will look like Figure 3.6. The user can select a record by specifying a constraint, choose one or more table column(s) of the record, and then select a weight name and a set of weights that match the sele cted columns. Once “Submit” is clicked, the inner product of the selected columns of a particular record with the selected weights of a certain weight name is computed and displayed on the next screen. Figure 3.7 shows the results of weighed sum computation when the user selects “proj1, proj2, exam1, exam2, final” columns and .1, 0.1, 0.2, 0.2, 0.4” weigh values. Sometimes we would like to view records whose att ribute values satisfy a particular constraints, for example, get information from a certain number of records which are largest or smallest among all records, or get information, such as a person’s grade which is in a certain range. That is why we add a range computation option to spreadsheet functionalities. For range computation, the user also has two options: first

PAGE 37

28 Figure 3.5 Weighted Sum Computation Page (1) Figure 3.6 Weighted Sum Computation Page (2)

PAGE 38

29 Figure 3.7 Weighted Sum Computation Page (3) group option and second group options look like those shown in Figure 3.8. The user can select one operation of “MAX, MIN, AVG, SUM” of a certain number of “3, 5, 10 or one half”, either “smallest” or “largest” of a table column and list all the records’ contents which satisfy these constraints. For example, the user’s command for the first group o ption can be “ Select MAX of 5 Smallest of ‘final’.” And for the second group computation, the user can give a command such as: “ List the contents of records whose ‘final’ in the range from 60 to100 order by ascending.” Then the next screen would display th e computation results accordingly. Link Menu The Link menu is used to perform link operations from tables in a database. There are four types of operations: “Create Link,” “Delete Link,” “Autolink,” and “Display Links.” The System Administrator and First Class Membe rs can perform all these

PAGE 39

30 operations, and the Second Class Member and any other user entering the system as a “public” user can only view links by clicking “Display links.” Figure 3.8 Range Computation Page Create Link There are three ways for the user to create links. The user can select to link a text string, which may or may not reside in the database, to another table in the database, to an image file, or to a URL address. By doing this, whenever the table contents are displayed in the syste m, for any field that matches the string, a hypertext link would be displayed, as in a regular HTML file. The user can then follow the link to different table, image file, or web page by clicking the link. This capability allows users to navigate the datab ase more conveniently. This menu is shown in Figure 3.9. Autolink “Autolink” is a special case of creating links. If two columns in tw o different

PAGE 40

31 tables have the same data type and have match text strings, a link can be created between two tables by selec ting two table names and then selecting one column name from each Figure 3.9 Create Link Page of these two tables. If matched data are found, a link would be created automatically without the user’s defining links one by one. This would release the user from redundant work. Display Links “Display Link” is used to display all link information that exists in the given database. It will show some information about links, such as the text string used to link to a table, the table name, the image files, or th e web site. This information would be very helpful for the user to navigate the database and it would provide the user an opportunity to look at a database in a broader view. Delete Link The user can choose to delete the link of one or more tables. The nex t time when the user opens the contents of the table, the hyperlink(s) would no longer exist.

PAGE 41

32 Image Menu The Image menu provides five functionalities that are used to process image files. The System Administrator and First Class Member can do all these o perations (import/open/clone/rename/delete image). However, for the Second Class Member, he can perform only one operation, that is, open an image to view it. Import Image “Import Image” is used to introduce an image file from user’s local machine. The use r would be provided a browser interface in which a window pops up to let the user select a file from the local machine. After the file path is selected and the button “Load image from my local drive” is clicked, the local image file would be read into the server side and saved to the database. If the user tries to import an image file that has the same name as an image file already in the database, he will be asked to overwrite the previous image or cancel the operation. Once the image file is imported, the user can perform operations on this image file. Open Image To open an image file, the user needs to select an image file name from the drop down list. After the image is displayed on the screen, it appears as in Figure 3.10. As shown in Figure 3.10, the information right below the main menu is general help information. Below that the user will see different help information when the mouse of operations can be performed on this page. Region of Interest mode allows the user to define the Region of Interest (ROI), in which the user is interested, and the user can select different ROI shape (Ellipse or Rectangle) and different ROI edge color (yellow, blue, gray, red, or black). To define an ROI, press the mouse’s left button and d rag the mouse.

PAGE 42

33 Figure 3.10 Open Image Page Once an ROI is defined, the information about this region will appear on the top box, which includes the region name, its shape, width, height, and its start coordinates. The user also can delete a hot region by clicking the “Delete ROI” rad io button and then press the mouse’s left button on the region that he would like to delete. Zoom Mode allows the user to zoom in and zoom out on an image by pressing the mouse’s right button and clicking. This is an improvement over the Hive System, which lacks zoom out functionality. The other mode is Define Link and Save ROIs Mode . Defining links is similar to creating links in the Link menu. The difference is that here the user can define links from any ROI to some tables in a database or a website. The user can get more underlying information about this ROI. For example, by creating a link from a CISE third floor hot region in an image file to the CISE building layout table, user can get information such as the faculties’ office distribution, some facil ities in their office, phone

PAGE 43

34 number, computer management, and so forth. The links from image to table or from table to image consist of a two way connection, which allows the user to access a database from an image map and operate on an image map from a da tabase. The user also can create link from an ROI in this image map to ROIs in another image map. Saving ROIs is useful if the user would like to see all ROIs he created in the last session. This is done by saving all ROIs into our system. (Note: the Save ROIs function is not available temporarily due to the CISE departmental computer usage policy.) Rename Image “Rename Image” can be used when the user would like to give another name to an image file. This provides the user an option to avoid an image file being overwritten due to having the same name. Clone Image If the user would like to get another copy of an image file in the server side without importing it repeatedly from the client side, the user can “Clone Image” to accomplish that by just selecting the image file name and giving the cloned file a new name. Delete Image Image deletion is simply removing an image file from the database, and the user will be given a warning message that he would lose all information about this image if he chooses to de lete it. Permission Menu As described in Access Control Levels, there are three permission control levels implemented in the WEBDB system: System Administrator, First Class Member, and

PAGE 44

35 Second Class Member. In addition, three submenus provide other function alities as follows. System Administration “System Administration” is for the System Administrator to manage the system. This includes: edit user information, add/remove other users to “SA” group, and reset user’s password. Group Administration “Group Admi nistration” is used to manage the group member of a given database. The First Class Member can add other users to the First Class Member or Second Class Member lists. Once the a user is added to these lists, he would have the same access rights to this dat abase as other First Class Members or Second Class Members. Access Control Every user has some access control rights such as: change his own profile data (including name and email address) change password, and resign account. After the user changes his ini tial password, an email will be sent to the user to notify him of the new password. Resigning from an account will cause the removal of this user’s account from the system. Navigation Index Tree The navigation tree is a unique interface feature of the WEB DB system. The purpose of creating the navigation tree is to display the basic contents of the given database, including the table names, and an image file names in this database. Every time a user first creates a new table, opens an existing table or impo rts an image file, the index tree will appear in the left frame of the entire page. The tree root is the database name that is currently opened and the nodes of the tree represent either table names or image

PAGE 45

36 file names. The user can click any node at any t ime to view the contents of the table or open an image map. Any change of tables in the current database will be reflected in this navigation tree structure. A “Refresh” button is located at the bottom of the left frame. The user may click it to see the mo st recent contents of the given database. The navigation tree provides a useful tool for the user to navigate the system and at any time it can give the user a big picture about how the database basically looks. Summary This chapter gives an overview of the WEBDB system. It begins with the description of the Registration/Login process and access control levels, followed by the explanation of the major menu in the system that are used to achieve the multiple functionalities. These include creating databas e/table, manipulating data and an image map, creating a two way connection between database and an image map. This chapter expounds that the WEBDB system processes the capability that not only allow the user to manipulate traditional database and table dat a, but also integrate graphical information in the system. The security issue is also discussed in this chapter. It is achieved by assigning authorization to the user and performing access control in three different levels. A unique interface feature of th e WEBDB system is the navigation index tree. The user can get a big picture of the database by using the index tree.

PAGE 46

37 CHAPTER 4 IMPLEMENTATION DETAILS This chapter discusses the underlying implementation details from a programmer’s perspective. These include setting up the Tomcat server in Unix and the implementation methodologies of major features of the system. These major featu res include using JavaScript and JSP to create a navigation tree, using servlet chains to call different files, applying JDBC connection pool to improve the performance of accessing database, and so forth. Installing Process The first step to building the WEBDB system is to install servlet engine and to set all parameters in the system configuration file. The servlet engine is the tool that loads the servlet class when the servlet is first requested and handles multiple requests until the servlet engine is shut down. Some popular servlet engine options are Apache Tomcat, JavaServer Web Development Kit (JSWDK1.0.1), Java Servlet Development Kit (JSDK2.1), Sun’s Java Web Server, and so forth [3]. In our system, we use the Apache Tomcat server (version 3.1), w hich is installed in the CISE department Unix machines. Setting parameters in the system configuration file involves the following steps. 1. Make a working directory that contains all application source codes and class files. 2. In the working directory, make bin, conf, lib, logs, webapps, and work directories. 3. Create a shell script file used to start the server.

PAGE 47

38 4. Under the webapps directory, make two directories. One directory contains all information from /usr/local/java/tomcat/webapps/examples, and the other is the programmer’s working directory that contains all source codes and class files. 5. Edit the server.xml file that resides in conf directory, change the home in “contextManager” to the working directory, and change the port number to an unused one. Aft er installing the servlet engine and setting parameters successfully, the next step is executing the SQL statement and creating some system tables to maintain all user tables in an organized and manageable way. It begins with the connection to Sybase throu gh a database driver handler, and then entering the username and password to log into Sybase. There are five system tables maintained in the database: “sys_usr_db,” “sys_usr_tb,” “sys_usr_register,” “sys_usr_img,” and “sys_usr_sa.” The table “sys_usr_regis ter” is used to keep track of all information of the user accounts, each of which includes the user’s name, email, his preferred username, password, and the date when the account was first created. The “sys_usr_db” table is used to save all database inform ation that the user created. This includes the database name, First Class Member list, and Second Class Member list. The “sys_usr_tb” table records all tables the user created. A field in this table “DBBELONGTO” provides information to which database this table belongs. The “sys_usr_img” is a system table that is used to keep all records of image files that the user imported into the system. Finally, the “sys_usr_sa” system table keeps information of all system administrators. Not only is the name of the sy stem administrator saved in this table, but also the information of who added this user. In order for the system to be executed and to be accessed through the browser, the servlet engine needs to be started at the background.

PAGE 48

39 Session Tracking HTTP is a “st ateless” protocol, and the server does not automatically maintain information about a client. This lack of context causes a number of difficulties because sometimes we need to remember some of the user’s information between sessions. Session tracking is a method to maintain state information about a series of requests [1, 2, 22]. In the WEBDB system, session tracking is used in various situations: when the user first logs in, the user’s information as with the username is saved into a session and later t his information is retrieved for various usability. After the user opens/creates a database or opens a table, the database or the table name needs to be saved. To use session tracking, first get a session using HttpSession object, then use putValue()/getV alue() or setAttribute()/getAttribute() methods to store or get information from a session. When the current session is done, completed or abandoned sessions are discarded by invalidating all session values or by removing a certain session value when neces sary. Connection Pool The connection pool is an important technology used in the WEBDB System to improve the performance of accessing a database. Connection pool initially preallocates a number of connections, and each servlet or JSP can get a connection from the pool. Once a database access is accomplished, it releases the connection so the connections are available for other requests. Finally, all connections are closed. First, we need to load the driver, and define the URL for the connection pool, which are “com.sybase.jdbc2.jdbc.SybDriver" and “jdbc:sybase:Tds:Fountain.cise.ufl.edu:9455," respectively, and then give the username

PAGE 49

40 and password for accessing the database. The next step is to initialize the pool and specify the size of the connection. Once the pool is initialized, add it to the global ServletContext, which is a common pool and can be shared by all servlets on the server, so that each servlet or JSP page that needs to talk to the same database does not have to create a connection pool itself . To access the ServletContext, the getAttribute() method is used, which returns a reference to the ConnectionPool. When each servlet has done its job, it will release the connection. Create/Open Database Creating a new database is simply the process of re quiring the user to enter the database name. The name would be checked to make sure there is no duplicate database name existing in the system that belongs to the same user. After the name is verified, the database name and the user name for “First Class M ember” would be saved in the “sys_usr_db” system table with the user being “First Class Member.” To open a database, a drop down list is provided from which the user may select. The list consists of all database names that the user has permission to open. The user can be a First Class Member, Second Class Member, or even a “public” user of these databases. If user is a System Administrator, the list should give all database names existing in the system. This is achieved by querying all records in the “sys_ usr_db” system table and then processing the resulting set. After the database name is specified, it is saved as a session value for later use. Table Creation To create a new table, the user only needs to give the table a name of that does not conflict wi th previous table names this user has created. An interesting feature is that user does not need to know how many columns and how many rows are needed in advance.

PAGE 50

41 Based on the prototype information the user enters, a user table schema is created by execu ting an SQL statement formed by collecting all parameters (using getParameter() method of HttpServletRequest) from the browser. If any errors are detected the user is notified. In the Sybase backend, this user table is actually named by prefixing the user name, database name (retrieved from session values), and the table name that the user entered. By doing this, every user table would be unique for each user and database, that is, different users can use the same database name and same table name. After th e user table is created, a new record is inserted into the “sys_usr_tb” system table. A supplemental table will be created after the creation of the user table is done. This table keeps all prototype information of the user table. For each column in the us er table, its fieldname, length, constraint, and primary key state combine as a row and are recorded in this information table. The information table is needed to support table structure change and error checking. This table is named by postfixing the user table’s name by “_info,” that is, in the form of “userName_databaseName_tableName_info.” In addition, another supplemental table named “username_databaseName_tableName_link” is created for the sake of convenience when the user creates and displays links in the Link menu. Another task after creating a new table is constructing a new JavaScript file for the navigation tree. This is done by using an object of FileWriter class to generate a new file and move this file from the default directory to the directo ry where the related HTML file resides. To do this we need to use a Java Runtime object to execute a Unix command.

PAGE 51

42 Delete Database/Table/Record Before the deletion is actually performed, the user is given a warning message that all information about the d atabase/table/record will be removed from the system. Table deletion involves several tasks: the user selects from the list a table that he has permission to delete; drop the prototype information table and the link table which are associated with this use r table; and then drop the user table and all its data. Since the table is deleted and the index tree is supposed to be changed, we need to generate a new JavaScript file by searching all table names in the “sys_usr_tb” table and write results to the new j s file. To delete a database for which user has delete permission, an SQL statement needs to be executed to search all tables that belong to this database from the “sys_usr_db” system table and then do a deletion operation one by one just as is done in tab le deletions. The program must also delete the database name from the “sys_usr_db” table, and delete the related HTML file and the JavaScript file that are associated with the database and used to create an index tree. Also, we need to check if the deleted database is the current opened database or not. If so, the database name in the session needs to be removed and the left frame (index tree) must be changed to a welcome page. Otherwise, the left index tree remains unchanged. Deleting a record is actually a straightforward process. However, since some JDBC features are not supported by Sybase (for example, Sybase does not support deleteRow() and getTypeName() methods), more additional supporting work has to be done here. The first task is to get the paramet ers of the records the user would like to delete. The table’s prototype information is then retrieved from the “_info” table, which is associated with the user table, and is saved into a container, such as a vector. By

PAGE 52

43 checking the different data types of different columns, a deletion statement is generated for each record. The final step is to execute these SQL statements one at a time. Construct New Table Constructing a new table is actually creating a view from existing one or more tables. The user needs to enter a name for the new table. The user is provided a list of all tables belonging to the given database from which to choose. After the user selects the table name and clicks “Submit” button, the next page displays all column information of tables th at the user selected, so that user can select some columns to create a view. The system must first find out how many tables and what columns the user selected. The number of tables that the user selected is saved as a session value so that in the following servlet file, tables are traversed depending on this session value. A new table (view) is created by combining all the columns’ data that the user selected. Finally, the information about this new table is displayed on the screen. The newly created table is actually a virtual table and there is no “_info” supplemental table or user table associated with it. However, this table is treated as the regular table except that this table cannot be modified. Its contents are supposed to be displayed as a regular table. What if the user needs to view its contents later on? This is considered as a special case. We use a global hashtable object to save the information of querying view table. What is stored in the hashtable is the SQL statement used to display all con tents of the view table. Whenever the user selects to open this view table, the hashtable is searched to find the matching SQL statement and the SQL statement is then executed. Finally, a new record about this view table information is inserted in the “sys _usr_tb” system table. Since a new table is created, the navigation tree structure is

PAGE 53

44 changed also. We need to generate a new JavaScript file so that the tree can reflect the most recent change about the given database. Change Table Structure Two kinds o f different operations are involved in changing the table structure: add a column and drop a column. If the user selects to add a column, a form will be provided for the user to enter the fieldname, data type, and length. After the user click “OK” to submi t data, the servlet is responsible for getting the parameters from the browser and constructing an SQL statement, which is in the form of “ALTER TABLE tableName ADD fieldname length null.” The SQL statement is then executed. The result of the execution is that the table schema would be changed. The “null” value in the statement above is used to give all records of the table default values for the newly added column. All these null values can be modified and inserted with actual data through the “Open & Edi t Table” menu. As in the creation of new table, some error checking needs to be done, for example, the fieldname cannot be the same as other fieldnames that exist in the table, and if the data type is “varchar” or “char,” then the length of this fieldname cannot be null. Any error is reported. As described in the “Table Creation” section, a supplemental table is created accompanying every new table’s creation. Therefore, when the process of adding a column is done, we need to insert a new row, which contai ns prototype information of the newly added column, into this information table. The other operation of changing the table structure is dropping a column. The page will show all contents of the table the user selected, and at the top of each column, the re is a checkbox that is used for the user to select to drop. Once the user’s selection is submitted, the servlet will get the parameter from the previous page and know exactly which column(s) the user would like to drop. In a loop that executes drop state ments (the

PAGE 54

45 statement is in the form of “ALTER TABLE tableName drop fieldname”), the column(s) the user selected would be removed one by one. In any table, the primary key values cannot be deleted, so we also need to do error checking here to check whether or not the column that the user selected is the primary key column (this is done by checking the prototype information table). If so, a warning message appears on the page to notify the user that he cannot drop the primary key column. Finally, we need to del ete the related record in the information table accordingly. Open & Edit Table The “Open & Edit Table” combines two operations together in one menu. The user can insert new records into the table and at the same time modify the existing data. Apart from all contents of the table being displayed on the screen, an empty line is provided at the bottom of contents and an “Insert Record” button located below the empty line is a submit button. Each time after the user inserts data in the text area of the empty line , and clicks “Insert Record” to submit it, the next page will display the contents of the table that includes the newly inserted record together with another empty line. In this way, the user can insert records into a table at any time conveniently, withou t needing to know how many records he wants to insert into a table in advance, which is a user friendly feature in WEBDB System. The underlying programming needs to have two servlets: one is display contents of table by querying all records in the user tab le. The form’s action will go to another servlet that executes an insert statement into the database. The page will then forward the HTTP request to the previous servlet. This page forwarding action can be done by using HttpServletResponse’s methods: encod eRedirectURL(“URL”) and sendRedirect(“URL”).

PAGE 55

46 To update data in a specific table, a checkbox is provided for each record. As long as the user clicks the checkbox and submits the request through the “Edit Record” button, the servlet is able to get paramete rs of which record the user selects, and the next page displays the contents of records that the user selected with each record followed by an empty text form for the user to enter new data. After the modified data are entered and the request is submitted, another servlet file is called to execute the actual update. Some error checking needs to be done, for example, to see that the user inputs match the table definition (data type, length etc), and to verify that the user does not try to update the primary key value. All these errors would be reported and the user notified. The Link Menu allows the user to create links from one table to another source, which includes another table, an image file, or a web site residing outside the database. Therefore whenev er the contents of a table are displayed, the system is supposed to display all hyperlinks also. By doing this, the user has a chance to access other sources by clicking these links. In the backend, this is implemented by searching all records of the “user name_databaseName_tableName_link” table and saving all link information into a hashtable. When displaying contents of a table, the table data in the hashtable is searched to see if there is a matching value. If so, then it adds a hyperlink under the data. Import Data into Table To import data into a table, the user needs to select a table name from the list for which he has permission, and then call a JSP file that resides in the “webapps/project/jsp” directory that contains all JSP files. This JSP file is responsible for displaying a file upload interface to allow the user to specify a file path through the “browser” button. Once the “browser” button is pressed, a window appears listing the user’s local file directory. To do this, we need to specify the inp ut file type being “file” and add a unique

PAGE 56

47 file uploading parameter in the regular HTML form code such as: . At the same time, we need to save the table name as a JSP’s session value so that the next servlet can retrieve the table name from the session. The actual data importing work is done by code in another servlet file, and involves several tasks: upload the user’s local data file into the server side; read the data file one line at a time; check the prot otype information table and construct an insert statement according to the different data types of the data; execute the insert statements and bulk insert data into the database. Spreadsheet “Spreadsheet” provides three kinds of different functionalities : basic computation, weight computation, and range computation. Basic computation allows the user to perform two types of operation. In the first group option, perform MAX, MIN, AVG, and SUM of all records of some columns. The second group option allows t he user to do computation of some columns of the current record that satisfied a certain constraint. The servlet gets all parameters from the browser. These parameters include checkboxes for MAX, MIN, AVG, SUM, and checkboxes below table contents in the fi rst group option. These parameters specify for what columns and what operations. Also included are some parameters the user entered in the second group option. The SQL statement is generated and executed. The computation results are stored in a hashtable, and the results are retrieved from the hashtable and displayed on the screen. If the user clicks the “Weight Computation” radio button in the first page of “Spreadsheet,” the next page will display all contents of the specific table (if the user chooses). At the same time, a text form is provided for the user to enter the weight name

PAGE 57

48 and weight values for each column. Whenever the user finished entering weight name and weight values in one form, the next servlet file processes all these parameters and save s all information into a supplemental table named “username_databaseName_tableName_weight.” After that, the request is redirected to the previous servlet and to let the user input another set of weight names and weight values. As described in the “System W alkthrough” section, giving the user a chance to input different sets of weight names and weight values is provided because in some cases, the user would like to have different computing algorithms and would like to have all information saved for later use . Next time, when user open the same table under this menu, all the weight information will show up and the user still has chance to enter new weight information. The user can click the “Finish” button to start to do weight computation. Then the next page would prompt the user to select column(s) of a record that satisfied a certain constraint, and the weight values of a specific weight name would be processed and get the inner product of both. All information is stored into a vector so that computation can be performed to get the inner product of both. After the computation is done, the results are displayed on the screen. Range computation is straightforward. There are also two group options provided. The first group is like basic computations, the only d ifference is the target is not for all records of the table, but for some number of records that are either smallest or largest of some column values. The second group is to display those records whose creation column values are in a range by ascending ord er or by descending order. The servlet file needs to get all parameters from the browser, and make different SQL statements and execute

PAGE 58

49 them. The select file then selects those records that meet the condition that the user specified. An important point i n doing these computations is error checking. Since we assume only those data with integer, float, or money data type can be performed spreadsheet, we have to check whether or not the column(s) that the user selects are valid to do computation. If not, an error message would notify the user to check if the data types match. Some other error checking includes: seeing if user selects the options for any computation, if user specify the range “from” and “to” value, and so forth. Navigation Index Tree The navi gation index tree is an excellent interface improvement of the WEBDB System over Hive, SORDS, and ESORDS. The purpose of the navigation index tree is to give the user a big picture of what the database looks like and to lead the user to navigate among tabl es and image maps, and view the contents of table or image file as well. The tree structure is implemented by using JavaScript. The main JavaScript file consists of four main functions. To create the root folder, use command foldersTree=gFld(), which takes a name and optional URL as arguments and returns the folder. To place a folder inside another folder, use the function insFld([parent folder], [child folder]. To create a document link use the function gLink that takes three arguments: Doc type specifies the target frame of the documents, Title is the text to be displayed in the tree and Link is the URL of the document. To place the document inside the tree, use the function insDoc([parent folder], [document link]). A new JavaScript file will be created each time a new table is created or an image file is imported into a database from the user’s local machine. This is accomplished by using Java API’s FileWriter class to write new documents (either table name or image

PAGE 59

50 file name) into the file. What is associ ated with each document element (that is, document link) is a servlet or JSP page with the table name passed as a parameter. The JavaScript file is then inserted into a HTML file that shows up in the left frame of the entire page. By doing this, whenever t he user clicks the table or image node, the ensuing action will result in the contents of a table or an image map being displayed in the right frame or a new window. Due to some browsers’ refreshing problems, we need to add a "Refresh" button at the bottom of the tree so that the user can get the most recent information about the database. The “Refresh” button is also implemented by using JavaScript. Summary This chapter gives an overview of implementation details of the table/data manipulation part of the WEBDB system. Included are database/table creation, multiple records insertion/update/deletion, multi table viewing, information search, and spreadsheet. The basic procedure is getting the user’s requests from the browser, opening the underlying database, executing query or update, processing the results and sending the results back to the client. Session tracking is used to maintain state information about a series of requests. To improve the performance of accessing database, the connection poor is used in the WEBDB system by preallocating a number of connections and reusing them by servlets or JSP. Also included in this chapter is the explanation of using JavaScript to implement the navigation index tree.

PAGE 60

51 CHAPTER 5 TESTS AND RESULTS Testing In this chapter, tests on the table/data manipulation part of the WEBDB system and the results are described on the basis of a sample application, a school grading system. Some error checking is discussed at the end of the chapte r. Test1 Create a New Database Input “Courses” for the database name into the text field, click “submit” button. Results . The database name is saved into the system table. Test2 Create New Tables Input table names “COP5725” and “CDA5155” into the text fiel d. Then enter the prototype information for the new tables: “name, studentID, proj1, proj2, exam1, exam2, final” columns for “COP5725” and “name, studentID, hw1, hw2, proj, exam1, exam2, Results . two tables’ names are saved in the system table and two user table schemas (named “username_databasename_tablename”) are constructed and saved in the Sybase database. Test3 Insert Data into Tables Click “Open & Edit Table” menu, selecting either the “COP5725” or “CDA5155” table name, i n the empty text field, insert a few records for each table, respectively.

PAGE 61

52 Results . Each time after entering one record and clicking “Insert Record” button, a new record is inserted into the table. In the next page, the browser shows all contents of the t able, including the newly added data. Test4 Modify Data in the Table Suppose we want to modify the project demo and final grades of “COP5725” whose ID is 2, then click the checkbox behind this record and click “Edit Record” button. On the following page , enter the new grades. Results . After submitting the request, the browser shows that data update is finished. Check the results by opening this table and find that the modification is effective. Test5 Import Records into the Table Assume we have a data f ile that includes studentID, hw1, hw2, proj, exam1, exam2, final grades for CDA5155 course, and the format of the data file matches the definition of “CDA5155” table. We would like to bulk insert these data into the table. Then click the “import data” menu and “browse” button, specify the data file path from the pop up window, and submit the request. Results .The browser shows data being imported successfully. Go to the “Open & Edit Table” menu and select “CDA5155” from the list. We can see that all data in the data file have already been inserted into the table. Test6 Change the Table Structures Select the “CDA5155” table name from the list, choose either “Drop a column” or “Add a column” from the next page. In order to drop a column, we select the checkbox at the top of column name, for example, the “exam2” column. At the same page, enter a

PAGE 62

53 new field name “bonus” to add a new column, and input its data type, string length, and constraint condition. Results . Go to the “Open & Edit Table” menu and select “C DA5155” table. From the contents of this table, we found that the table schema had been changed: the “exam2” column and all its information were deleted and a new column “bonus” was added by inserting default “null” value to this column for all records. Th ese null values can be further updated to actual data using the “Edit” function. Test7 Construct a New Table Enter a name for the new table, and then select the checkboxes of “COP5725” and “CDA5155” tables. When the next page shows up, select the column n ames from these two tables. Results . The newly constructed table is actually a view and consists of the information from two columns of previous tables. This table name will also appear in any table name list, and its contents can be displayed as a regular table. Test8 Search for Information from the Database Enter a keyword in the text field, for example “lichen”. Select “CDA5155”, “COP5725” and “All Tables” in the table name list, respectively, and click “Search” button. Results . When clicking “COP5725” a nd “All Tables”, the result shows that the string of “lichen” is found in the “COP5725” table, and the information about this record is also displayed. The search by clicking “CDA5155” table results in “’lichen’ not found” information being displayed on th e screen.

PAGE 63

54 Test9 Delete Records/Table Delete records: click the “Delete Record” menu, select the checkboxes before the desired records and submit the request. Delete table: to click the “Delete Table” menu, simply select the table name from the drop down l ist. Results . After selecting from records from table “COP5725” to delete, check the results by opening this table. We can see that those records being checked are removed from the table. The result of table removal can be seen in any table list. It shows that the table that the user deleted does not exist anymore. In the underlying database, the record “COP5725” is removed from “sys_usr_tb” system table, user table (“lichen_Courses_COP5725”), and supplemental tables (“lichen_Courses_COP5725_info” and “lich en_Courses_COP5725_link”) are removed as well. Test10 Perform Spreadsheet Functionalities Test10.1 Basic Computation . For the table “COP5725”, select MAX, MIN, AVG and SUM of “exam1, exam2, final” for the first group. Results . The computation results: the maximum, minimum, average, and summation of “exam1, exam2, final” for all records are displayed on the next page. Test10.2 Weight Computation . For table “CDA5155,” we enter six weights .05, 0.05, 0.2, 0.2, 0.2, 0.3” for “hw1, hw2, proj, exam1, exam2, final" which consist of the first computing algorithm (weight1). Also enter another set of weights, which is the second computing algorithm (weight2): 0.25, 0.25, 0.15, 0.25, 0.25, 0.3. Then in the following page, select these six columns in which the student ’s ID is 12345 and weight2’s six weights and click “submit.”

PAGE 64

55 Results . The inner product of the above computation is given. Two computing algorithms are saved into the database, so when the user wants to do weight computation using the first algorithm next time, he still can retrieve it from the database. Test10.3 Range Computation . Two cases are tested: one is selecting “MAX, MIN, AVG, SUM” of Smallest” of “hw1, hw2, proj, exam1, exam2, final”; the other is listing the contents of records whose “final” grade being at the range from ” to ” order by “ascending.” Results . The computation results at the range from ” to ” are displayed on the following page. Test11 Delete Database From the drop down list, select the “Courses” database, and click “yes” to make sure we want to delete the database. Results . The database does not show on the database name list anymore. Both tables belong to this database (“lichen_Courses_COP5725” and “lichen_Courses_CDA5155”) are removed, two records are removed from “sys_usr_tb” system table and the “Courses” record is removed from “sys_usr_db” system table. At the same time, the “lichen_Courses_html” and “lichen_Courses_js” files are removed from the system as well. Test12 Test the Navigation Tree The functionality of the navigation index tree is checked in several ways: Test12.1 Open the database. Once an existing database is selected to open or a new database is created, and any one of the following menus is clicked, the navigation tree is supposed to work: New Ta ble, Open & Edit Table, Delete Table, Change Table Structure, Construct New Table, Import Data, Search Information, and Spreadsheet.

PAGE 65

56 Results . A navigation tree structure shows up in the left frame with the current database being the root and all tables and image files belong to this database, which are the nodes of the tree. When any of the table nodes is clicked, the table is opened and the table contents are displayed in the right frame or a new window. The same is true for an image map. Test12.2 Close t he current database . After the database is closed, it is supposed to be no navigation tree anymore. Results . The navigation tree disappears and a welcome page shows up in the left frame. Test12.3 Delete the database . The navigation tree will either disappe ar or be still there depending on the database being deleted is the current one or not. Results . If the database deleted is not the current opened one, the navigation tree remains unchanged. Otherwise, the index tree disappears and the welcome page shows u p in the left frame. Error Checking Since the WEBDB system is aimed at persons who are not database experts and do not have much programming experience, error checking is needed. Help information is given to the user accordingly. And the change does not take effect if am error occurs. Some cases of error checking and the resulting error message are listed as follows. 1) For a certain user and a certain database, a duplicate database name or duplicate table name is used. Error message : “You can't use dupli cate name for your database, please select another name” and “You can't use duplicate name for your table, please select another name.”

PAGE 66

57 2) The user tries to open a database but there is no any database existing yet. Error message : “There is no any database existing yet or you don't have permission to open any database. Please go to 'New Databse' to create a database and enter table definition information first.” 3) In the WEBDB system, before the user is able to manipulates table data and operates on an ima ge map, it is supposed to have a database being opened or created first. So if the user tries to click any of submenus in Table, Data, Link, or Image , an error message is given to the user. Error message : "There is no any table existing yet. Please go to ' New Table' to create a table first." 4) The user defines a primary key in the table prototype, but did not enter a primary key data or give a null value for a field whose constraint is not null. Error message : “Please enter primary key data.” 5) The user d efines two or more field names for a primary key. Error message : “The primary key can only be unique.” 6) If the column data type is “char” or “varchar,” the length is not given. Error message : “Please give the length for char or varchar type column.” 7) T he user tries to drop a primary key column. Error message : “You can't drop the primary key column.” 8) When importing data into a certain table, the format of the data file does not match the table definition or in the data file, or there is duplicate prim ary key value with some existing data.

PAGE 67

58 Error message : "Some error occurred. Please check to see if the format of the local file match the table definition and see if there are duplicate primary key value with existing data." 9) In spreadsheet functionality , some columns or rows the user selected are not valid to do computation (that is, their data types are not integer, float, or money). Error message : "There is some database error occurred. Please check if the columns/records you selected are valid to do c omputation or not." Summary This chapter described the functionality test of table/data manipulation part of the WEBDB system on the basis of a sample application. The corresponding results are addressed which show that the system works well. The chapter a lso lists some special conditions that the system automatically checks. The corresponding error messages and help information are given to the user.

PAGE 68

59 CHAPTER 6 CONCLUSIONS This chapter reviews some key concepts addressed in this thesis. The important issues of the WEBDB system are discussed, and the future improvements over the current system are addressed as well. Overview of WEBDB The WEBDB is a secure, user f riendly database builder application that integrates traditional database tables and records with unstructured graphical information into one system. The system provides an easy to use method for the user to create and manage a database, table, and data, a nd create an image map and define hot regions in which the user is interested. At the same time, the capability of creating two way connection between table and image allows the user to have a broader view of the database and to be able to manipulate an im age map and tabular data more conveniently. The security mechanism in the WEBDB system is achieved by the implementation of password and access control. There are three types of modes in WEBDB system: System Administrator, First Class Member, and Second Cl ass Member. The System Administrator has an unlimited access to any database in the system. The First Class Member has access control to manage an authorized database, and its tables and image files, which include database/table creation, data insertion/de letion/update, and information search, spreadsheet operations, and some image operations, such as importing image, defining hot regions on the image and creating links between image and table. The Second Class Member can only view and query information fro m the system.

PAGE 69

60 In addition, the system also provides functionalities to manage and maintain the system administration, group account administration, and allow the user modify his account profile. The WEBDB provides a capability for user to manipulate an ima ge file freely. The user can create hot regions with different shapes and different shape edges, and link these hot regions to other sources (either table information inside the database or a website outside the database). Apart from viewing tabular data f rom these links, the user also can zoom in/zoom out the image map so as to manipulate more conveniently the regions in which the user is interested. The WEBDB also provides a unique interface feature from which the user can have a big picture about the dat abase, that is, the navigation index tree. The user can view any recent modification on the database by clicking the node of the tree, either table node or image node. Future WEBDB Improvements The WEBDB is a secure, user friendly database builder applica tion. It has a capability of extensibility that allows implementers to add new functionalities on the system. For example, more menus could be added into the current main menu. Implementer can add a “Simulation” menu to perform simulation functionality, or add a “Graph” menu to let user draw pie chart or other graphs on the basis of table data. Also some improvements can be made in future work so as to increase the usability and universality of the system. In the WEBDB system, the access control is set at the database level, not at the view or table level. This means the user has the same permission right to all tables that belong to the same database. This can be improved by setting permission to specific

PAGE 70

61 tables, or even further, setting permission at the row or column level so that the user has more control on the tables, The current system allows the user to create a view table from two or more existing tables. The resulting view table is treated as a regular table. However since it is just a virtual tabl e, it cannot be modified. It also cannot be used to construct another view table either. In the future, this can be improved by creating a view table on which the user can perform any manipulations, such as data update, schema modification, and so forth. The current WEBDB system mainly consists of table/data manipulation and static image operation. The future work can extend its capability to dynamic image operation and spatial database computation. This future work would involve, for example, comparing th e routes and the time taken from source to destination by using different transportation tools in an image map, and also, drawing a bar graphics or pie chart for the computation results. Some kind of simulation and constraint checking also can be incorpora ted into the system. The navigation index tree is an extensible interface structure. Currently it has two levels of subtree, that is, table name and image file. In the future, some other nodes and features can be added to the index tree, for example, if a “function” node is added in the tree, the user would get a general idea of functionality of the entire system.

PAGE 71

62 REFERENCES [1] Marty Hall, Core Servlets and JavaServer Pages, Prentice Hall, Inc., August 2000, Upper Saddle River, NJ [2] S. Allamaraju, K. Avedal, R. Browett, J. Diamond, J. Griffin, M. Holden, A. Hoskinson, R. Johnson, T. Karsjens, L. kim, A. Longsha w, T. Myers, A. Nakimovsky, D. O’Conner, S. Tyagi, G. Damme, G. Huizen, M Wilcox, S. Zeiger, Professional Java Server Programming (J2EE Edition), Wrox Press Ltd., 2000, Birmingham, UK [3] Sun Microsystems, Inc., Lesson: Overview of Servlets, http://java.sun.com/docs/books/tutorial/servlets/overview/index.html Accessed: 02/22/02 [4] Sun Microsystems, Inc., JDBC Basics Tutorial, http://java.sun.com/docs/books/tutorial/jdbc/basics/ Accessed: 02/22/02 [5] Hans Bergsten, Improved Performance with a Connection Pool, http://www.w ebdevelopersjournal.com/columns/connection_pool.html Accessed: 02/22/02 [6] 1Automata, Introduction to Image Map, http://www.cris.com/~automata/maps.htm Accessed: 02/22/02 [7] WebTools, Advantages and Disadvantages of Different Types of Image Maps, http://curry.edschool.virginia.edu/go/WebTools/Imagemap/home.html#adv Accessed: 02/22/02 [8] INT Media Group, Incorp orated, Application Servers: Overview, http://serverwatch.internet.com/appservers.html Accessed: 02/22/02 [9] Macromedia, Inc., Macromedia ColdFusion 5 Home Page: Product Overview, http://www.macromedia.com/software/coldfusion/productinfo/overview/ Accessed: 02/22/02 [10] Macromedia, Inc., Macromedia ColdFusion 5 Home Page: Product Overview — New features, http://www.macromedia.com/software/coldfusion/productinfo/new_features/ Accessed: 02/22/02 [11] Macromedia, Inc., Application Servers: ColdFusion, http://serverwatch.internet.com/reviews/app coldfusion.html Accessed: 02/22/02

PAGE 72

63 [12] Sun Microsystems, Inc. , NetDynamics Home Page: NetDynamics features, http://www.netdynamics.com/products/features Accessed: 02/22/02 [13] Sun Microsystems, Inc. , NetDynamics Home Page: NetDynamics overview, http://www.netdynamics.com/products/ overview.html Accessed: 02/22/02 [14] IBM Corporation, WebSphere Home Page, http://www4.ibm.com/software/webservers/appserv/about.html Accessed: 02/22/02 [15] INT Media Group, Incorporated, Server Watch Listing for WebObjects, http://serverwatch.internet.com/reviews/app webobjects.html Accessed: 02/22/02 [16] Apple Computer, Inc. WebObjects Homepage, http://www.apple.com/webobjects/ Accessed: 02/22/02 [17] Xiaoxi Cao, “Spatial Web Database System,” Master’s Thesis, Computer and Information Sciences and Engineering, University of Florida, 1998, Gainesvi lle, Florida [18] Changlu Yang, “A Web Database Application Builder for Non Programmers,” Master’s Thesis, Computer and Information Sciences and Engineering, University of Florida, 1998, Gainesville, Florida [19] Thomas J. Davis, “A Secure Online Relatio nal Database Application,” Master’s Thesis, Computer and Information Sciences and Engineering, University of Florida, 1999, Gainesville, Florida [20] Joel Manner, “Secure Online Relational Database System (SORDS),” Master’s Thesis, Computer and Informatio n Sciences and Engineering, University of Florida, 1999, Gainesville, Florida [21] Dan Kong, “An enhanced Secure Online Relational Database Application,” Master’s Thesis, Computer and Information Sciences and Engineering, University of Florida, 2000, Gai nesville, Florida [22] Sun Microsystems, Inc., Session Tracking (The Java Tutorial), http://java.sun.com/docs/books/tutorial/servlets/client state/session tracking.html Accessed: 02/22/02

PAGE 73

64 BIOGRAPHICAL SKETCH Lijun Chen was born in Jishou City, Hunan Province, China. She received her Bachelor of Science degree in veterinary medicine from Hunan Agricultural University, China, in 1992. She received her Master of Science degree in microbiology and immunology from Yangzhou University in 1995. She worked at the Shanghai Academy of Agriculture for three years as a research assistant on biological technology. In June 1998, she attended the University of Manitoba in Winnipeg, Canada, to pursue her d octoral degree in microbiology and immunology. She had to quit her studies in Canada five months later to be with her husband in Gainesville. She was accepted into the Computer and Information Science and Engineering Department at the University of Florida in January 1999. She will receive her Master of Science degree in computer and information science and engineering in December 2001. Her research interests include Web database application, object oriented software development, client/server computing, ne twork programming, and bio informatics.