Group Title: enhanced secure online relational database application
Title: An Enhanced secure online relational database application
CITATION PDF VIEWER THUMBNAILS PAGE IMAGE ZOOMABLE
Full Citation
STANDARD VIEW MARC VIEW
Permanent Link: http://ufdc.ufl.edu/UF00100809/00001
 Material Information
Title: An Enhanced secure online relational database application
Physical Description: Book
Language: English
Creator: Kong, Dan
Publisher: University of Florida
Place of Publication: Gainesville, Fla
Publication Date: 2000
Copyright Date: 2000
 Subjects
Subject: Web databases   ( lcsh )
Computer and Information Science and Engineering thesis, M.E   ( lcsh )
Dissertations, Academic -- Computer and Information Science and Engineering -- UF   ( lcsh )
Genre: bibliography   ( marcgt )
theses   ( marcgt )
government publication (state, provincial, terriorial, dependent)   ( marcgt )
non-fiction   ( marcgt )
 Notes
Summary: ABSTRACT: This thesis presents a design and implementation of the Enhanced Secure Online Relational Database System (ESORDS). Besides having all the advanced features of its predecessor, SORDS, such as open database connectivity, cross-platform, secure access control, table locking for concurrent transaction and easy-to-use methods for data operation through a user-friendly interface, ESORDS makes every effort to meet the user's needs by adding multiple table views and table transposing. In ESORDS, a variety of tools can be used to create a view, which increases opportunities to cater to different tastes. View creation is limited to the creator of a specified database without bothering normal users, achieving a more user-friendly interface. Table transposing is applied to satisfy the special requirement that multiple parallel tables in the underlying database be viewed at a different angle. This is especially important when statistical analysis needs to be done in the transposed tables but is not allowed in the base tables. In ESORDS, a column can be added in a table at any position instead of the right most position, making table creation much more flexible. Additionally, ESORDS enables a formula to use absolute cell reference, a portable and efficient spreadsheet capability.
Summary: KEYWORDS: web database, online database, multiple table views, table transposing, spreadsheet, absolute cell reference
Thesis: Thesis (M.E.)--University of Florida, 2000.
Bibliography: Includes bibliographical references (p. 66-67).
System Details: System requirements: World Wide Web browser and PDF reader.
System Details: Mode of access: World Wide Web.
General Note: Title from first page of PDF file.
General Note: Document formatted into pages; contains x, 68 p.; also contains graphics.
General Note: Vita.
Statement of Responsibility: by Dan Kong.
 Record Information
Bibliographic ID: UF00100809
Volume ID: VID00001
Source Institution: University of Florida
Holding Location: University of Florida
Rights Management: All rights reserved by the source institution and holding location.
Resource Identifier: oclc - 47682062
alephbibnum - 002678731
notis - ANE5958

Downloads

This item has the following downloads:

PDF ( 5 MBs ) ( PDF )


Full Text











AN ENHANCED SECURE ONLINE RELATIONAL DATABASE APPLICATION


By

DAN KONG










A THESIS PRESENTED TO THE GRADUATE SCHOOL
OF THE UNIVERSITY OF FLORIDA IN PARTIAL FULFILLMENT
OF THE REQUIREMENTS FOR THE DEGREE OF
MASTER OF ENGINEERING

UNIVERSITY OF FLORIDA


2000




























Copyright 2000

by

Dan Kong



























Dedicated to my husband, Wanpeng Cao, and
my son, Michael Cao















ACKNOWLEDGMENTS

First of all, I would like to thank GOD for always leading me through my life, and

needless to say this thesis. Then I would like to express my sincere gratitude to my

advisor, Dr. Richard Newman. Without his suggestions, patience and support, this thesis

would not have been possible. I also would like to thank Dr. Dankel and Dr. Wilson for

serving on my committee and always providing feedback when asked. Especially I want

to thank Dr. Dankel for telling me the source of this thesis, SORDS. In addition, I am

very grateful to Tom Davies and Joel Manner for giving me much help in the beginning

of this work. Finally, I would like to express my love and gratitude to my parents, and my

husband, Wanpeng Cao. Without their help and encouragement, it would have been

impossible for me to finish this thesis.
















TABLE OF CONTENTS

page

A C K N O W L E D G M E N T S ................................................................................................. iv

LIST OF FIGURES .................................................. ............... ........ vii

A B STR A C T ......... ............. .................................................................... .......ix

CHAPTERS

1 INTRODUCTION .................................. .. ... ... ........ ... .............. 1

D description of the P problem ................................................. ....................................... 1
Overview of ESORDS ...... ............. ..... ............ ...................... 2
O v erview of th e C h apters ............................................................................................... 5

2 UNDERLYING TECHNOLOGIES ............................................ ........................... 6

Technology U sed .................... ............................................................. 6
O v erv iew o f P erl ................................................................................. ..... 6
Overview of CGI ........... ......... ... ................7
O verview of D B I..................................................................... 8
Overview of Views ......... .. .................... ............... ...... 9
O overview of Spreadsheets......... ................. ................. ....................... .............. 10
Some Commercial W eb Database Products............................... ......................... 11
ColdFusion 4.5 ......................................... 11
N etD yn am ics 4 .0 ..... .............................................. ...................... .. ............ 12
Sapphire/W eb ...................................................... ......................................... 13
Overview of Some Related Systems in UF ...................................................... 14
A p ia ry + + ................................. .................................................... 14
H iv e ................................. ........................................................ 1 5
S O R D S ........................................................................................................ 1 6
S u m m a ry ........................................................................................................ 1 8

3 A WALK THROUGH THE SYSTEM ............................................... ............19

Registration/Access Level............................ .............. 19
D database M enu............................. ............. ...... 20
T ab le M enu ..................................................... 2 1
T able T ransposing ..................................................... 27


v









Main Menu ...................... ...... .............. 30
In sert R o w s ................................................................... 3 2
Function C olum ns .... ........................ ........ .............................. .............. 34
D elete R ow s ..................................................................................................... 35
U update R ow s ..................................................... 35
Add a Column ......................................... 36
D rop a C olum n .................................................................................................. 38
L o g o u t ..................................................... 3 8
S u m m ary ..................................................... 3 8

4 IMPLEMENTATION DETAILS ..................................................39

In stalling P process ..................................................... 39
Table Creation............................. ........... .............. 41
M ulti-table V iew ing ..................................................... 42
Table Transposing ................................. .......................... .................. 45
Add/Drop Columns ......................................... 48
T able/V iew D displaying ..................................................... .............. 48
S u m m a ry ........................................................................................................ 5 2

5 TESTS AND RESULTS ......................................... .................. ..........53

School G reading System ........................................................................ 53
M ulti-T ab le V iew s ................... ...................5...................4..........
Add/Drop a Column ................................. ........................... .... ...... 58
Proposal Evaluation System ...................................................................... ......... 58
Individual Cell A dressing ......................................................... 60
E rror C checking ..................................................................................................... 60
C create a V iew ............................ ............... ..... 60
A dd a C olum n .................................................. 6 1
C reate a Transposed T able.................................................................... ........ 61
S u m m a ry ........................................................................................................ 6 1

6 C O N C L U S IO N S ....................................................................................................... 6 3

Summary of ESORDS ......... ............................... 63
Some Ideas to Improve ESORDS .................................... .............. 64
S u m m ary .................................................................................................. . . 6 5

R E F E R E N C E S ................................................................66

BIOGRAPHICAL SKETCH ................................. ........................... ........68
















LIST OF FIGURES



Figure Page

1-1. Schem atic Drawing of Table Transposing .............. ............................. ..................... 4

2-1. CG I architecture using Perl script [3] .............................. ........................................ ...7

2-2. The A architecture of D B I [5] ............................................................................ ...... .8

2-3. An Example of Using Relative Cell Reference and Absolute Cell Reference.................10

3 1. L o g in S c re en ............................................................................................................. 2 0

3-2. Database M enu............. ...... ........................... ........... 21

3-3 T able M enu ......................................................................... 22

3-4. C reate T able Screen........................ .......................... .. ........... ....... .. 23

3-5. Create View Screen .................................... .. .......... ............... 25

3-6. Schematic Drawing of Table Transposing ......................... ...... .............................. 27

3-7. Create Transposed Table Screen ............................................................................28

3-8. Permission Menu .................... .................... ...................30

3-9 M ain M enu ......................................... ............................................ 3 1

3-10. Insert R ow s Screen ....................... .... .............. .............. ............. ... 32

3-11. U update R ow s Screen ......... .... ........ ....................................... ........ 36

3-12. Add Colum n Screen ............. .................. ......... .... .. .. .. ............. 37

5-1. Flow Chart of How "totalGrades" View is Obtained from the Base Tables
"hwGrades," "examGrades" and "pjtGrades"......... .................................54

5-2. Input Parameters for Creating View "hwPcts" .......................................... ...............55




vii









5-3. Input Parameters for Creating View "totalGrade".................... ....................57

5-4. Tests of Adding and Dropping a Column at the Second Position.................................57

5-5. Five Evaluator V iew s ............................................ .. .. ............. ......... 58

5-6. A Transposed Table "p2" .......................................................................................59

5-7. A Transposed Table "C5" with Some Special Rows................... ........................... 59















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 Engineering

AN ENHANCED SECURE ONLINE RELATIONAL DATABASE APPLICATION

By

Dan Kong

December 2000


Chairman: Richard Newman
Major Department: Computer Information Science and Engineering

This thesis presents a design and implementation of the Enhanced Secure Online

Relational Database System (ESORDS). Besides having all the advanced features of its

predecessor, SORDS, such as open database connectivity, cross-platform, secure access

control, table locking for concurrent transaction and easy-to-use methods for data

operation through a user-friendly interface, ESORDS makes every effort to meet the

user's needs by adding multiple table views and table transposing.

In ESORDS, a variety of tools can be used to create a view, which increases

opportunities to cater to different tastes. View creation is limited to the creator of a

specified database without bothering normal users, achieving a more user-friendly

interface.

Table transposing is applied to satisfy the special requirement that multiple

parallel tables in the underlying database be viewed at a different angle. This is especially









important when statistical analysis needs to be done in the transposed tables but is not

allowed in the base tables.

In ESORDS, a column can be added in a table at any position instead of the right

most position, making table creation much more flexible. Additionally, ESORDS enables

a formula to use absolute cell reference, a portable and efficient spreadsheet capability.














CHAPTER 1
INTRODUCTION

This chapter begins with a description of the problems issued within this thesis,

followed by an overview of the ESORDS that is developed to solve these problems. The

chapter concludes with an outline of the chapters organized in this thesis.


Description of the Problem

The success of the World Wide Web (WWW) over the last few years has not only

created possibilities for offering universal and immediate access to information, but also

transformed the Internet from a dull, technical tool into an interactive, user-friendly

neighborhood. One of the most noticeable achievements to make this possible is the web-

based or online database.

The current implementation of online database applications requires a web page

to be customized to meet users' specific requirements instead of just displaying the data

in the same form as they are stored in the underlying database. Given that tables are the

basic schema to store the data in the relational databases, users may just want to see some

particular data in the underlying tables. In some special situations, users would like to see

the data of parallel tables from a different angle. This requires the system be able to

provide a way to generate a front view, vertical view and side view of these tables. The

current commercial online databases are either too complicated or too general to meet

these special needs, what we need is some tools that can be easily used while generate

accurate results.









Additionally, it happens frequently that users want to manipulate the data instead

of just viewing them. Besides the operations such as insertion, deletion and update,

adding a column in a table is a common request. Although the latest added column is

always placed on the most right position in the underlying table, what is really expected

is that the column can be added in any position when the table is displayed.

Displaying the data in a tabular form has an advantage of making spreadsheet

capabilities possible. On a table, users can perform statistical analysis as well as some

special computation requests based on existing values. However, not many on-line

databases possess all the features of a true database and spreadsheet capability at the

same time. ESORDS was developed to solve these problems based on its predecessor,

SORDS. Its goal is to respond the user's specific questions as effectively and as

accurately as possible.


Overview of ESORDS

ESORDS (Enhanced Secure On-line Relational Database System), as the title

indicates, enhances SORDS (Secure On-line Relational Database System) by

strengthening existing functionality and adding new features. They are multi-table

viewing, multi-table transposing, flexible column addition, and individual cell

addressing.

A regular user usually likes to see just a subset or a projection from a table with

some constraints, a new arrangement with attributes from different base tables, or a

complete new look with computational attributes based upon values in multiple base

tables. From an administrator's or a creator's point of view, the information in a table

may be partly forbidden for users. One way to satisfy these specific requirements is to









create new tables, which is not a good idea because it will result in data redundancy and

wasted time. The best way is to create multi-table views, which is one of main

achievements in ESORDS.

In ESORDS, a view can be derived from multiple base tables as well as existing

views. Two fields from any two different tables can be joined based on conditions

including equal to, not equal to, less than, less than and equal to, greater than, and greater

than and equal to. More restricted conditions can be entered in a text entry field, which

allows the selection based on a given attribute being less than a constant number. Further,

a view can have different column names from those in the base tables and may have

computation columns based on the values in the base tables. All of these conditions

provide users a variety of choices to define a view.

ESORDS allows a column to be added in any position instead of the last position

in a table. Thus, a user can order the columns in the way he/she likes. This functionality

is necessary when there is a functional column whose result is based on the values of the

columns to its left and the formulas use the values of the added column.

Another major implementation in ESORDS is multi-table transposing. A

transposed table is obtained by viewing several parallel base tables with identical column

names from a different angle. As illustrated in Figure 1-1, the base tables might be

represented by the Z or vertical axis. This results in two classes of transposed tables. In

one class on the X axis, a table has base table names becoming row identifiers and row

identifiers becoming column names with one of original column names as the table name.

The other kind, on the X axis, keeps original columns with base table names becoming

row identifiers and row identifiers becoming table names. Because views can not satisfy









such a special requirement, new tables have to be introduced. An easy-to-use interface is

provided with two drop-down menus. A user can see the transposed information by just

selecting a table name and clicking the submit button.

xl x2 3 x4 x5
2 y2 4












Figure 1-1. Schematic Drawing of Table Transposing



Spreadsheet capability is strengthened in ESORDS by addressing individual cell

values in the special rows using absolute cell referencing. This breaks the limitation that

the special rows can only give summary information of a particular column using several

built-in functions as in SORDS. Therefore, a user can create his/her own mathematical

formulas using values not only in the regular rows but also in the special rows.

Additionally, two build-in functions, standard deviation (STD) and spread ( SPD, the

different between maximum and minimum) are created in the ESORDS, giving more

tools for users to analyze the data in a table.

As you may have seen, all the improvements in ESORDS are trying to provide

better service for the users to satisfy their specific requirements. Therefore, we can say

ESORDS is a user-driven web database system.









Overview of the Chapters

This thesis consists of six chapters. The next chapter discusses three popular

commercial web database applications and three related on-line database systems used at

the University of Florida. This chapter also presents the technologies used to implement

ESORDS. Chapter 3 consists of a walkthrough of ESORDS from a user's perspective.

Chapter 4 discusses the implementation details of the creation ESORDS. Chapter 5

presents tests and results to illustrate how two applications can be built using the

functionality in ESORDS. The final chapter presents conclusions and proposes the future

work to extend ESORDS.














CHAPTER 2
UNDERLYING TECHNOLOGIES

This chapter presents an overview of the core development tools used to

implement ESORDS. This includes a brief review of Perl language, the CGI (Common

Gateway Interface) and DBI (Database Interface) modules, and the use of concepts of

views in relational databases and spreadsheets. Additionally, this chapter also discusses

some related online database systems, which include three commercial applications and

three systems developed at University of Florida (UF).


Technology Used

Overview of Perl

Perl stands for Practical Extraction Report Language. It is an interpreted high-

level programming language developed by Larry Wall. Originally Perl was used to

generate reports that tracked errors and corrections to a software. After the WWW was

introduced, Perl has become the premier scripting language of the Web, because Perl was

built to process text and most CGI programs process user input and return HTML text

pages. However, Perl is widely used as a rapid prototyping language and a "glue"

language that makes it possible for different systems to work well together. Perl is

popular with system administrators who use it for an infinite number of automation tasks.

Perl's roots are in UNIX but you will find Perl on a wide range of computing

platforms. Because Perl is an interpreted language, Perl programs are highly portable

across systems, and its coding and testing process is much easier and faster [1, 2].









Two modules using Perl, DBI, and CGI are established already. They allow

programmers easily to connect the database and use the functionality of CGI. Thus, it is

ideal for writing CGI programs in Perl languages.

Overview of CGI

The Common Gateway Interface (CGI) is a standard method for a WWW server

to interact with programs running on the remote computer. This interface allows the

WWW server to run remote programs, and to pass data to and from those programs.

Examples include performing database searches, doing numerical calculations, generating

new images, and processing HTML forms and imagemap output. Figure 2-1 displays how

the web browser, web server, and the application program communicate with each other

using CGI.

Common
HTTP Gateway
Protocol Itrface SQL -
Web U _k Web flCGI [ V _- Ir i



Figure 2-1. CGI architecture using Perl script [3]



The following gives an explanation of how Common, Gateway, and Interface

actually apply [2].

The common Gateway Interface is common between web browser and web

server. All web servers and web clients communicate using HTTP request and response

headers. It acts as a gateway between web client and web server. The CGI program acts

as a bridge between the web client and web server, interpreting and responding to

dynamic and data-driven requests from the web client. The CGI program assists the web

server in returning dynamic web pages, built on the fly, in response to URL (Universal

Resource Locator) and data requests from the web client. Also, the common Gateway









Interface acts as an interface between web server and other applications on the server

machine. The CGI program understands the HTTP interface protocols required by the

web server and can act as an interface between other computer applications and web

server.

Overview of DBI

DBI, the Database Interface for Perl5, is designed to define and implement a

common interface to enable interaction between applications and various database

engines. The DBI allows the creation of database-manipulation scripts without regard for

the engine being used to service the requests [4]. Figure 2-2 provides a view of how

ESORDS uses DBI to interact with a given database.


P
D
E RDBMS

L

S
S DBD -
S RDBMS
C i
R t DBD
1 "-"i
P h RDBMS



Figure 2-2. The Architecture of DBI [5]



It is important to understand that DBI is just an interface, a thin layer of "glue"

between an application and one or more Database Drivers (DBD). DBI essentially acts as

a conduit for the DBD modules. The DBDs implement the methods defined in the DBI,

but implement them in a database-specific way. The programmer of applications never









even knows the DBD is there. All the programmer know about is the database-

independent methods defined by DBI [5].

Overview of Views

A view is an alternative way of looking at the data in one or more tables. You can

think of a view as a frame through which you can see the particular data in which you are

interested. That is why one speaks of looking at data or changing data "through" a view.

A view is derived from one or more real tables whose data is physically stored in

the database. The tables from which a view is derived are called its base tables or

underlying tables. A view can also be derived from another view.

The definition of a view, in terms of the base tables from which it is derived, is

stored in the database. No separate copies of data are associated with this stored

definition. The data that you view is stored in the underlying tables.

A view looks exactly like any other database table. You can display it and operate

on it almost exactly as you can any other table.

Views can be used to focus, simplify, and customize each user's perception of the

database. Views also provide an easy-to-use security measure. Through a view, users can

query and modify only the data they can see. The rest of the database is neither visible

nor accessible. If the view and all the tables and views from which it was derived are

owned by the same user, that owner can grant permission to others to use the view while

denying permission to use its underlying tables and views. In addition, Views can be

helpful when changes are made to the structure of the database and users prefer to work

with the database in the style to which they have become accustomed [6, 7].









Overview of Spreadsheets

A spreadsheet is a grid of rows (usually numbered 1,2,3...) and columns (usually

lettered A,B,C...). Each cell in this grid can contain numbers, text, or formulas, and it can

be referred to in terms of its column and row labels, for example C3, D17, etc. The

formulas can refer to the contents of other cells [8].

The essential quality of a spreadsheet is that in any cell you can enter numbers,

text, or formulas. These formulas will probably refer to the contents of other cells in the

spreadsheet. If 5 is entered in cell B12 and 6 in cell D15, and the formula B 12+D15 is

entered in cell F20, then that cell will display the value 11.

A function represents a shorthand way to perform mathematical and logical

operations, that is, by calling a function you have requested that the spreadsheet perform

a "prepackaged" operation. For example, averaging a series of numbers (AVERAGE);

calculating the standard deviation (STDEV) of a series of numbers. The basic format for

a function in a spreadsheet cell is: function name(argument offunction), where

function name is the name for the function to be performed. The "name" may be a whole

word or an abbreviation, the argument offunction represents the range cells to be

evaluated. For example: SUM(A1:A3) or AVERAGE(M4:M9).

The use of relative and absolute cell referencing is essential if the spreadsheet is

to be fully adaptable and efficient.

A B A B A B
1 4 1 4 1 4
2 11 44 2 11 = 4*A2 2 11 =A2*$A$1
3 12 48 3 12 = 4*A3 3 12 =A3*$A$1
4 13 52 4 13 = 4*A4 4 13 =A4*$A$1

Figure 2-3. An Example of Using Relative Cell Reference and Absolute Cell Reference









The meaning of the terms relative and absolute cell referencing can be difficult to

understand at first. Look at the three diagrams in Figure 2-3.

The left diagram shows the values as calculated by some formulas, which can be

shown in the middle and the right diagrams. In both cases, each formula refers to the cell

to its left, i.e., A2, A3 or A4. This is so called relative cell reference. In all cases of the

right diagram, each formula refers to the cell Al. Al is actually referred to as $A$1

which is the way to indicate an absolute cell reference. In the middle diagram, each

formula uses the constant number 4 directly instead of using the address indicating the

value in the cell Al. If the value in that cell is changed later, the formulas in the right

diagram remain same. But in the middle one the number 4 in each formula has to be

replaced by the new value. Obviously, using an absolute cell reference enhances

efficiency.


Some Commercial Web Database Products

Because of interactive, dynamic, and profitable benefits, more and more web

database application products emerge, each with its own specialties. Among them, Allaire

Corp.'s Cold Fusion, Spider Technology Inc.'s NetDynamics, and Bluestone Software's

Sapphire are the most widely used in businesses and professions. These three products

are briefly introduced here.

ColdFusion 4.5

ColdFusion 4.5 by Allaire Corp.is a web application server for building and

delivering scalable applications that integrate browser, server, and database technologies.

It includes visual programming, database, and debugging tools in an integrated

development environment. It enables team development in large projects with developers

and servers distributed across multiple locations. ColdFusion 4.5 provides a powerful,









comprehensive server-side scripting language (CFML) with a tag-base syntax that cleanly

integrates with HTML and XML. It delivers high-performance web applications that

scale to meet the needs of the most demanding sites. It features open integration with

databases, e-mail, directories, XML, and enterprise systems. The deployment platform

supports multi-server clusters with native load balancing and fail over to serve high

volume, transaction intensive applications. ColdFusion 4.5 offers database connectivity

including support for ODBC, OLE DB, and native database drivers for Oracle and

Sybase, and is extensible with technologies such as COM (Microsoft Component Object

Model) and CORBA (Common Object Request Broker Architecture). ColdFusion 4.5 has

security services on every level from development through deployment. [9, 10]

NetDynamics 4.0

NetDynamics is the first Enterprise Network Application Platform that integrates

visual development, a Java applications server, and WAN (Wide Area Network) -scalable

database access into a slick RAD(rapid application development) GUI (Graphic User

Interface) that allows users quickly to develop and deploy database applications on the

Web. Although ColdFusion offers fast Web-database application development, Spider

Technologies is the first to deliver a working example of the Java technology. While

there are several components in NetDynamics 4.0 package, three of them are more

appealing and are examined here.

One main component is NetDynamic Application server. It is a high-performance,

CORBA-based Java applications server. It delivers true distributed object processing,

state, and session management for both HTML and Java applications, data integration

with RDBMS or any type of enterprise data, and a comprehensive security framework

that addresses the complex security issues of net-based transactional computing.









NetDynamics Command Center enables real-time, local and remote management,

and monitoring of enterprise applications. It is the first vendor to provide a means of

active administration of a network application platform.

NetDynamics Studio is a full-featured IDE (Integrated Development

Environment) for developing powerful Java enterprise applications. It allows for mixed

use of HTML and Java. It includes full support for team development. It also includes an

integrated debugger that can debug remote applications [11].

Sapphire/Web

Sapphire/Web is a Unix/Windows NT Visual Action Builder that enables a

developer to integrate existing and new business applications and extend those

applications to the Internet, intranets, and extranets. The supported platforms include

95/NT, SUN OS, Sun Solaris, SGI, IBM RS/6000, H-P Series 9000, and DEC Alpha. It

supports open databases such as Oracle, Informix, Microsoft SQL Server, Sybase, DB2,

and any JDBC or ODBC databases [10].

Sapphire/Web uses CGI to interface with databases. Creating an application takes

five steps [12]:

1. A User Interface is made with HTML, using forms for data input and

requests, and templates are made to hold the data that is to be returned to

the client from the application.

2. The Application Logic is created with application objects from

Sapphire/Web. These can include procedures in the database itself;

dynamic SQL procedures; functions; executables; files (for both reading

and writing); and external CORBA objects.









3. The Interface Objects are bound to application objects using the Object

Bind Editor. The returned data will then be posted in the HTML templates.

4. The conditional processing code is added or modified in the populated

templates if necessary.

5. The CGI is generated in C or C++ code that is to be used in the HTTP

server's CGI directory.


Overview of Some Related Systems in UF

Apiary++

Apiary++, developed by Dan Yu [13], is a web database application builder. It is

an expanded system based on Apiary, created by Changlu Yang [14]. Apiary++ fixed

some of Apiary design and implementation problems. It provides a more friendly

interface using a main screen, which allows switching applications and tables/views as

well as selecting table operations. The main screen also shows current system status for

the application, the table/view and the function working on the table/view. Apiary++

allows multiple inserts, deletions, and updates. It also supports Sybase views, which can

be modifiable and editable in the same manner as regular tables. A view can be built

upon single or multiple tables. Supporting views is the major design improvement in

Apiary++.

Although Apiary++ made a big improvement, it has some of the same problems

as Apiary. For instances, a user can enter the system only after the creator manually adds

the user in the underlying database, and table locking is not supported for concurrent

insertion, deletion, and update.

In addition, there are some defects and errors in the system. First, there is no read-

only setting for the table. Viewing a table/view has to accompany an update operation.









Second, a column can not be added or dropped in an existing table. If a user wants to add

a column, the table has to be dropped first and then recreated. Third, a table can not be

created unless a primary key is provided. Fourth, when testing the system, one can not

create a view based on multiple tables as intended.

Hive

Hive, created by Xiaoxi Cao [15], is also called spatial web database system. It

applies Java Abstract Window Toolkit (AWT) and Java applets to provide a dynamic and

friendly interface. Hive supports a two-way connection between image maps and the

database. This allows a user to get tabular information of interest when invoking a hot

region, and a zoom-in image after its URL link field is clicked in the record.

Hive includes three operation modes: the creator mode, the editor mode, and the

user mode. The creator is responsible to assign authorization of the editors; define, label,

or delete hot regions of interest on the base image; and create database schema for the hot

regions including all necessary table operations, such as retrieval, insertion, deletion, and

update. Editors can manage records over the web of database tables for which they are

authorized. Different editors are assigned to manage different tables by the creator. The

normal users can view a zoom-in image and the database information by clicking on a hot

region. Users can also make a query to the database by inputting an attribute in the text

field. All the records satisfying the constraints will be displayed on the web browser.

Hive is a good tool for creating image maps and storing the map information into

database over the web. However, it does not support multi-table viewing functionality.

This is needed when a user issues a specific request or wants to know the relationship

information from multiple hot regions in a base image or multiple base images. Hive does

not possess spreadsheet capability. It does not allow adding or dropping columns either.









There are some programming faults when testing Hive as a regular user. The

denotation of the labels is not consistent with what the hot regions are meant to be. For an

example, the region denoting a room in the base image is marked as E355, but the red

label over the circle defined on the region is another number. When clicking on a hot

region, only a zoom-in image comes up, no tabular information related to the hot region

is displayed. Besides, when attempting to make a query, there is no response from the

system.

SORDS

SORDS (Secure On-line Relational Database System) is created by two graduate

students, Thomas Davies and Joel Maner [16, 17]. Compared with Apiray, Apiary++, and

Hive, SORDS is a more mature and secure web database system with a well-structured

user interface.

SORDS is an open database system and can be executed on both Unix and

Windows platform. Therefore, SORDS can connect with many natural databases, such as

Sybase, MS Access, Oracle, etc. SORDS is a reliable system because it supports table-

locking for concurrent transactions.

In SORDS, security is achieved by providing three access levels: administrator,

creator, and user. Administrators can access all the databases and the tables in the system,

and are free to do any operations on a table. Creators have full access in one specific

database. Users have basic access to the SORDS, which is limited to the operations of

view, update, delete, and insert. Each table in the SORDS has default permission setting

of these four operations. A user can be authorized to become a creator by an

administrator or another creator. SORDS also has another access level: group. A group is









made up of several users who have different default permission settings from viewing for

the tables in a database.

In SORDS, the tables in one application are separate from those in another

application through a database screen. After a database is chosen, a table menu including

four table operations is displayed, which consists of creating a table, dropping a table,

selecting a table, and creating a view. On the table menu screen, only the tables within

the selected database are given. SORDS has a main menu with a fixed function menu,

which contains all the data operations for one table even including adding and dropping a

column. SORDS supports multiple inserts, deletes and updates. The table-displaying

frame provides options for ordering a table by up to three columns.

Views can be created in the SORDS system from one or two base tables. They

are hidden from a user, displayed in the same manner as for regular tables. In a table,

some summary information such as summation, average, maximum, minimum, and count

can be obtained in functional columns and special rows. A user can create his/her own

mathematical formulas as well.

All in all, SORDS is a truly integrated and robust system that allows users to

manipulate databases and tables. However, there are still some improvements that can

increase the usability of the system. For examples, a view can be created based on

multiple base tables instead of one or two; joining two fields can be based upon some

other restricted conditions, not only equality; a column can be inserted in any valid

position instead of the most right in a table; functional fields can use any individual cell

value instead of all the values in a particular column.






18


Summary

This chapter covers the major developing tools used in this thesis. This includes

an overview of Perl language, CGI and DBI modules, and a brief review of views in

relational database and spreadsheet. The chapter also discusses three popular commercial

database applications as well as three on-line systems at UF.














CHAPTER 3
A WALK THROUGH THE SYSTEM

This chapter gives a detailed explanation of every aspect of ESORDS from a

user's perspective. It emphasizes the features of muti-table viewing, multi-table

transposing, individual cell addressing, and flexible column addition which SORDS does

not possess. By walking through the system, the user would get a general overview of the

system and its usefulness.


Registration/Access Level

The front page of the ESORDS system is the login page as displayed in Figure 3-

1. A user must provide a valid username and a password before entering the system. For a

new user, he/she must register into the system first with a username and a password.

To get a better control of system resources and maintain a consistent database,

three access levels are present in ESORDS: administrator, creator, and user. The

administrator has unrestricted access to all databases and tables within the system. A

creator has full access to a specified database and can perform any operations on all the

tables within that database. Upgrading from a user to a creator requires the permission of

the administrator or another creator. A user can have at most four operations on a table:

viewing, deletion, insertion, and updating. The default permission for a user is to view the

data in a table. If a user wants to get higher access than the default permission such as

deleting and updating the data in a table, a group with different default permission must

be created.











File_ E-J1 'v, .' C"l *7uiTot,ij rn .,' t, _-i H-
I--~

L'. Hn&. j. .r.P 1 lr s

Enhanced Secure Online Relational Database System

Please Login

U I








Cop:li..hr ':.O2 ". 'I 1 Erciqrcn rc



Figure 3-1. Login Screen




Database Menu

Following the login page is the database menu as seen in Figure 3-2. This screen

provides at most three operations: creating a database, selecting a database, and dropping

a database. Only administrators and creators are allowed to create and drop a database. A

user is only allowed to select a database if at least one table exists in the database.

Generally a user can view the data of all the tables in the existing databases because the

default permission setting for a user is viewing. To create a database, the information

required is a valid name as long as it does not conflict with any of the existing database

names. To go to an existing database, the user needs to select the database name from the

drop-down list. The table menu will be loaded after the "select database" button is

pressed. Dropping a database requires selecting the database name from the drop-down










list. After the "drop database" button is pressed, the database as well as all the tables

within the database is eliminated from the system.


F -"
FUI -riii; *^ *:.[ --


J |I r j r t i_.- E I
File E Jir _%i' ',a ,7" _,.i : r
a.:-1, ,- a
E.,:r Fr.r.n FU I ..] H.iT,t *:.-*,AIi ry;: 14-
Create Databasie
F Te,. :f .. rq c [



SeIlet Database
l i:,l t r ,[ i ::,L t-c Pnr oai u.F.tlo rt m



Dlrnp DaLahase
S r .o Drop PTroposJE'rTT eorxr --A

aDtj


1 :4; "b :30 j' ,. 11


Figure 3-2. Database Menu




Table Menu

The table menu in ESORDS as shown in Figure 3-3 provides five operations on

tables or views in a specified database. These operations are creating a table, selecting a

table, dropping a table, creating a view, and creating a transposed table. A user is only

allowed to select a table from existing tables. All other operations are limited to the

administrators and the creators.

A view is treated identical to a table in the system. For selecting and dropping a

table, view names are displayed in the drop-down list together with table names. Thus,

users can see the information of a view without being aware of its underlying structure.










Selecting a table and dropping a table are relatively easy operations. To enter a

table, a user selects its name from the drop-down list. Once the user presses the "go to

table" button, the main menu screen is shown. Dropping a table is similar. The table

name is selected from the drop-down list. After the "drop table" button is pressed, the

selected table or view is removed.

I Ner.t: iC EN%0L IJ -
I lb EEir Vtl- DQ CcrijMT rAMl H.ip

Z L,.a- m a Hn i sam a Ie FPlT aty wi Cli'
Crlme Tah l s V
UEtr2 TaflAGinumI rr w NYne
E.r-f fIJr 7 aflf +r ._ I ]















Figure 3-3. Table Menu












To create a table, the table name and the number of the columns initially
contained must be given. If the table name is already taken, the system alerts the user
_li-tGraJed _.'r;Pu J/ianarpda4 Je'trmP" J'p c _mi. ,Jp ftnI



























(administrator or creator) to choose another name. The "create table" button is clicked
and the create table screen is generated as shown in Figure 3-4.
J'lf. EB,,. ll: 7 lrt, c. hi, rna, ._ | -Ij Piiti= JE ,,ami-w -tiwp' tr.clgt _JIpl a n. .._irulh" r t i









Figure 3-3. Table Menu



To create a table, the table name and the number of the columns initially

contained must be given. If the table name is already taken, the system alerts the user

(administrator or creator) to choose another name. The "create table" button is clicked

and the create table screen is generated as shown in Figure 3-4.










FiI.- EJI '&w Cc _,_ -i,' r,-r.,9i1:, I-.fr.

E.;'f I-- *,V,.I F-I,. .',r 3-13, N ':'".,V: Fiikrrr *.*,.isv/ ,,r _'T',p


I-- Z E--






Set Default Pernuissons:



I- II -E- ,





Figure 3-4. Create Table Screen



The create table screen consists of the column attributes for the user to enter or

select. The attributes include column name, column data type, column field length,

constraint (null, not null, unique), and whether the column is primary key or not. A

column name can not be used more than once for a table. The create table screen also

contains options to set default permissions for a table.

Column attributes can make up many combinations, but there are some limitations

on the selection from one attribute to another. For an example, only one column field can

be chosen as primary key. Correspondingly, "not null" must be selected as the constraint.

Another example is that the length field must be given if the data type field is "char" or

"varchar." If the user picks up an invalid combination, the system alerts him/her to try

again. Only after the user fixes the problem is the main menu loaded.









In SORDS, a view can be derived from at most two underlying tables, and the

join is limited to equi-join only. In ESORDS, a view can be created by joining not only

the tables but also the existing views. The join includes six options, which are equal, not

equal, less than, greater than, less and equal than, and greater and equal than. The number

of the underlying tables/views is not restricted. The view names together with the table

names are displayed using a check-box list. If the user wants to select a table/view, he/she

must check the box besides the table/view name. In addition, the column names for a

view can be chosen freely by providing the option of computed column and the number

of computed column. Thus, a view can have columns with different names from the

existing ones, and it can also have computed columns based on the existing ones of the

base table/views.

To create a view, the view name must be provided. If the name conflicts with an

existing one, an error message is shown to alert the user to select another name. The user

needs to decide whether there are any computed columns or columns with different

names from original column names. If the answer is yes, the field for the number of the

computed columns must be filled. Otherwise, the system tells the user to go back and

enter a number. Before creating a view, the user needs to decide what base tables or

views should be used. It is required that at least one table or one view be checked. The

final step is to press the "create view" button. This will take the user to create view screen

as seen in Figure 3-5.










i-i N" .r.LLc E50FiD-3
Fib Edir ThVe L. Crrunirln He

I k Fr...rj FuI l in l i wt. Rtam Fi r i.tlly A ;'
Plear Idert dr ralnmu oum oan I In yar vile

omn-l n a! s f .'ii.'ri anli, [ l.

--L

." p I'P1 'FF' a l:L


S.r . Fa uP~
C L f Ituii i, '4f i. L .

I h>; r| i



Pleais den the Nlunins Ields you %n i J* Jod Ibyour view. hibey mm b me deu rype)


r...L. 1 I | ...I 1.. 1 0 I I P 1 n 1 I ,,,.

Enter irurlncdaondldona


Set Deftult Pcrmfsdus:




|___ __ C_ __ .i ] | n | 4..|..



Figure 3-5. Create View Screen




The create view screen is made up of five blocks stacked from the top to the

bottom, which provides the view constructs for the user to select or input parameters. The

top block lists all the column names of the selected tables and/or views. The columns

belonging to a table/view are scrolled down under the table/view name. If the view to be

created uses an existing column name, that name must be highlighted under the correct

header. In a view, the columns selected in the first table/view are arranged prior to the

columns selected in the following tables/views.









The second block is for the user to enter computed columns if any. Here the user

can change the column name of the underlying tables/views. For an example, if the user

wants to use "hwGrade" instead of "hwPct," he/she can put "hwGrade" in the field of the

left column labeled by "column name," the name "hwPct" should be entered in the

corresponding formula field. If there are more than one table/view having the same

column name "hwPct", the column name must be prefixed by the proper table/view name

followed by a period, such as "hwPcts .hwPct ". The computation field can be

constructed by an algebraic calculation formula in which the operators are "+," "-," "*,"

and "/" and the operands can be either constant numbers or the column names.

Parentheses can be used to indicate the precedence.

The third block is a list of pairs of all selected tables/views. It is to provide the

column fields for every pair of tables/views to join. The default field is "None" which

means there is no relationship between the two tables/views. For each pair of

tables/views, the fields to be joined must have same data type. The user can select a field

name through the drop-down menu. The middle drop-down menu labeled by "operator"

gives the user several options for two tables/views to join. The next block is a text field

for entering more restrictions. This demands that the user have some knowledge of SQL.

The conditions include some key words like "between," "and," "or," etc. The condition

of "hwPcts.studentID between 1111 and 2222" is one example.

The last block is used to determine the default permission setting of the view for a

user. Similar to the create table screen, four options are provided: viewing, insertion,

updating and deletion. Any mistake in the selection or the input in the create view screen









will result in aborting the creation of a view. The system may give a proper error message

to let the user know where the problem is.


Table Transposing

xl x2 x3 x4 x5
2 y2
y3
y44/





Sz23

z4



Figure 3-6. Schematic Drawing of Table Transposing



Creating a transposed table is a completely new operation in the ESORDS system

relative to the SORDS system. A transposed table is derived from several similar base

tables or views with the same number of columns, column names, and column data type.

A data container with three axes X, Y, and Z as seen in Figure 3-6, can be used to

describe how a transposed table is obtained. Suppose there are five base tables named

Zl, Z2, ... Z5 They exist in the system already. They have identical column names of

X1, X2, ... X5. The fields of the first column in the tables identify the rows. The

identifiers can be represented by Y1, Y2, .. etc. Although each table may have different

number of rows, the total number of identifiers is limited. If the user wants to view the

data from a different angle, such as Z Y dimension or Z X dimension, a transposed

table named Xl or X2, ... X5 or Y1 or Y2, ..., Y5 needs to be created. A transposed









table may have empty data field. For an example, if the row represented by Y2 in table

Zl is not present, the data field represented by row Z1 and column Y2 in any transposed

tables of X should be empty. In naming within the base tables, the columns except the

first one as well as the identifiers of the first column are required to be formated as

XXXX#. "XXXX" represents one of the combinations of alphabet letters from a to Z and

the underscore "-" in any number. "#" represents a positive number, through which the

columns and the rows in the base tables/views can be arranged in an increasing order.

All the tables and views in a given database are available through a check box list.

To create a transposed table, the user must check the boxes beside the selected

tables/views. Not all the tables can be used to create a transposed table. Only those with

same column names and data types are legal. The system alerts the user if an invalid table

is selected. Once the "create table" button is clicked, a screen for creating a transposed

table as seen in Figure 3-7 is displayed.

I- rJ t Er.nL, E P.-F.E D Ij
Fl,- EFJL tu.- ,- C -Trrrrur, 1:-.r H-Ip

E..~ vP n '.Ifd Fr eat l-ItE r' :l.f.:tl r Frli i S nril'
Create a Transposed Table
[i"-:m i! i* :::I "
[i:i li I : l


Set Default Permissions:

Fig".'re 7. I [eat UTr a s :"re | 'b e ereeI




ibu I I* I Tas oESe \b S

Figure 3-7. Create Transposed Table Screen









This screen provides two drop-down menus, from which the user can select a

transposed table name. For each submission only one table is created. As seen in Figure

3-7, the user can choose one table from a table list either under [eval] *[p] or under [eval]

*[C]. After the user sets the default permissions and hits on the "create table" button, the

transposed table will be created. A confirming message is shown on the following screen.

Then the main menu is generated followed by clicking on the "go to table" button.

At the bottom of the table menu there is a permission button. Once the button is

pressed, a menu of the permission options is loaded as shown in Figure 3-8. This menu

contains all the available permission options for a given database as well as all the default

permissions associated with each table. The permission menu is only accessible by the

administrators and the creators.

The permission menu has several functions that are group related. The first one is

"create group." This function needs to provide a group name and set the permission for

each table associated with the given database. A group can be removed as well through

"delete group." This requires picking a group name and pressing the delete group button.

A user can be added to or removed from a group using two buttons: "add user to

group" and "delete user from group." The two functions require selecting a group name

and a user name. To add a user in a group, the user name is chosen from a list of users

that are not in the group. To delete a user from a group, the user name is chosen from a

list of users that are in the group.

The permission menu also has three functions that are not group related. They are

"modify default permission," "add creator," and "delete creator". Modifying default

permission provides a means of editing default permission settings for each table when











created in a database. As a result, a user can have chance to insert, delete, or update a


table. The function "add creator" allows a normal user to be promoted to a creator. Once


upgraded, the creator can have full access to all the tables in a given database. Deleting a


creator is to downgrade a creator to a common user.


Eire L Jr I r .-,-rur., [ -II"
"^ c"3 a U r
".r r H... .... *: l..... ...

I r.Ph, m, I uI r. I ,:r.nr ,r .. I E.:.l: iu .... f ir
Delauill Ptnnssiois -
Create Group
T I In.Grades
Modify Group I

DOl'Oi Group ImP.Is


I I. exajiIPoiijor
Addj Us.rr 1
I : I I. I r ,

cDa T: ii... exiu Gi'rades
I 'II rG,, I r,. rI .,
Add Creator |

Delre Crrea '

SSelect ,.DBi' pjPui

Selecl Table
I I T .,_i, pjtr_ Ie________________________%



Figure 3-8. Permissions Menu





Main Menu

Main menu is composed of two frames as shown in Figure 3-9. The left frame is a


function menu which provides all the available operations associated with a table/view.


The right frame is to display the selected table/view.











The function menu consists of five operations for a specific table/view. They are


inserting rows, deleting rows, updating rows, adding column, and dropping column. The


function menu also provides two links back to the database and the table menu screens


and a link to logout from the system. A user can normally perform at most all these


operations except adding or dropping column depending on the default permission


settings.


-I
PdI.- L.JI ~- t .rfu,,


rj-t. I -


H. II


a Al am- Li me -a 1".
i ir.. r-l-. lf i[- :l-U -J1 F. I, v.] 1.. ,J .. Ftn, iJ
S ':T I i ... -

g'^"", mo"' Select the order to display the able in:
Sort I]. Onl1ti1 g
r'ow Ful: L',t 1 ....".t 1

U pda.e R.0 s d: 1"4 ,' ...... .

"IlHro: i Itoi -DaJ ".c-r-- -- I
c T)lhspis a. able t.lth imur I 1.: ? I









i- p -;p -
IT .: '- I '.

J I


9 rn



adO .Jten aflqt$
_____-'-- I:: '- | -''-- 1::
Li F't.. _____________-__ ____--__ '-"-__ ________
It [==ri iF I .I :gur '-' Main ,iI
Figure 3-9. Main Menu


The viewing frame on the right displays the information of a table/view. At the


completion of any table manipulation operations selected from the left frame, the











table/view is redisplayed. A table/view can also be sorted by selecting up to three

columns in either descending or ascending order. In ESORDS, each row of a table can be

labeled with a row ID in the front. The row ID can be viewed by selecting the option of

"display table with row ID?" and pressing the "redisplay" button. The row ID can help

the user to represent an individual cell together with the column name when the data

value of that cell is used in a functional field of the special rows. This is explained in

Chapter 4.

Insert Rows

-1i ... -..- i -ii
I a I H I- IT-I
FI: E.I, .- C-. C r -H, u.,-i T L,,-1I-I, Ir
1. ,J, I k s. r,,.






c" '.'i.d.-d c. k-.c to 'tri.,- tJic '. ,' 2 -.o+'.'.- M'l -J






I I I -';T.. i.r jt- -

Sntrr C-lu m n I [ :: I




^Saloct Tsable



--- -- call--
~ un


Figure 3-10. Insert Rows Screen









There are three options to insert data in a table: inserting into a regular row,

inserting into a special row, and inserting through a data file. The options are displayed in

the insert-rows screen as seen in Figure 3-10. For a view, only the second option, e.g.

inserting into a special row is allowed.

To insert data into a regular row, the user needs to provide the number of rows

and press the "insert rows" button. Then the insertion screen is displayed, which consists

of the column headers followed by the text entry fields for the user to enter values. Once

the user is done inputting the new data and clicking on the "insert rows" button, the

system checks the validity of the data. If there are no inconsistencies, the rows are

inserted in the table. Otherwise, no row is inserted and an error message is given.

Inserting into a special row is similar except that each row has an extra label field.

The label is used as a header to denote more information about the row. The special rows

are always put under the regular rows when the table is displayed. All the entry fields in a

special row are function fields with a "char" or "varchar" type. A "+" sign should be

always put in the function fields as the first character. There are seven built-in functions:

AVG, SUM, MAX, MIN, CNT, SPD (spread), and STD (standard deviation). If a built-in

function alone is entered in a function field, the calculated result returned from the

function instead of the function text is shown. This result is the summary of all the values

of regular rows in that particular column. In ESORDS, the user is also allowed to enter

any mathematical formulas referring to the values of other individual cells beyond the

functional field itself. This is very helpful when the user wants to obtain a computed

result based on two or more values instead of all the values in a particular column. For an

example in Figure 3-9, the row "std_percentage" contains the information of the









percentage of the student 0's homework, exam, projects, and final grades among the four

students' records. It is obtained by dividing the values in the row student 0 by the

corresponding values in the row SUM. The syntax to index a cell in a table is

"row#columnname", where # is the ID number of the row. Later on this pattern is

replaced by the value placed in that cell for the computation. For an example, the

function field of the homework percentage of student 0 in row "std_percentage" has the

formula as "+rowOhwPct/(rowSUMhwPct *0.01)", where "row0hwPct" represents the

value of 96.17, and "rowSUMhwPct" represents the value of 373.17.

To insert data from a file, the file path needs to be given, which can be searched

through the "browser" button. Once the button is pressed, a window appears, listing the

user's local file directory. After the file is selected, the delimiter to separate the data

column in the file must be specified as well. The delimiter can be either a tab or a

comma. Finally the button "insert from file" is pressed. If the file is found with proper

format of the data, the system places the data into the table similar to regular row

insertion. Otherwise, an error message is given.

Function Columns

Similar to the special rows where the functional fields are applied, a functional

column in a table can also use any mathematical formula as well as seven build-in

functions: AVG, SUM, MAX, MIN, CNT, SPD, and STD. Thus, it is necessary to set the

data type of the functional column "char" or "varchar". When the built-in functions are

used in the functional column, they are meant to apply to the values in the columns to the

left of the functional column in a particular row. The syntax is

"+function(start col::endcol)", where "function" represents the function name and

"start col" and "end col" represent the names of the beginning column and the last









column to which the functional column refers. Later on the system replaces the column

names with the corresponding values in that specific row. The function computes the

values to generate a result, which is displayed. Additionally, the functional column

allows the user to create any mathematical formula using addition, subtraction,

multiplication, and division. To illustrate this, an example is given here. Suppose there

are three lists of homework grades represented by the columns "hwl", "hw2", and "hw3"

in the table. A functional column "total" on the most right is to compute the summation

of the three homework grades. If the user wants to get the weight of "hwl" grade for a

specific row, another functional column can be added with the formula as

"+hwl/(total*0.01)".

Delete Rows

Deleting rows in a table is a relative simple function. Once the "delete rows"

button in the function menu is pressed, the table is redisplayed with a check box in front

of each row. After the row is selected and the 'delete' button is pressed, the row is

deleted from the table. In a view, only special rows can be removed.

Update Rows

Updating rows involve more steps than deleting rows. When the user selects

"update rows" button, the update rows screen is loaded as shown in Figure 3-11. The

table to be updated is redisplayed on the screen. Each row in the table is associated with a

check box on the left, and each column is associated with a check box on the top. If the

user wants to update a data field, the two related boxes should be checked. For

convenience, there are two check boxes located on the left corner of the table: "all

columns" and "all rows". Instead of checking each box of all the rows or columns, the

user can just select one. On the top part of the update rows screen, there are two drop-











down menus for the user to select whether selected rows/columns or all rows/columns are


to be displayed on the next screen. If selected rows/columns are chosen, the next screen


only shows the selected rows and columns to be updated. Otherwise, all rows and


columns are shown, but only the selected fields are updateable. Displaying all


rows/column helps the user to edit the selected field if the values in other fields are


needed.


-I ri- :. L- I 1
F I.: E, .- .a- .. r u : H ,Ir

E .r r J.I 1 ill1
_.'I r ... ..1.. :.-. r: ,- . .. . -,_-_, / I" I, .l .... -I
S. 1- I-
4 11.4 ~nl l *rT iiI1 1 II.I r- 1


aInorw Rows 1
O* cru~iv Rc~iwsr a


Qlpriatn RnwB

Add Column

Orop Calumn0

C Solert DE Y

,Clee TsbIO3



LU
rr-------w?
-^----r


Sisplay Options:

b F.n ni h


.A I _, '



I- 1-
.| fl ll II I I I N\ I li l I ..








-STD
"'TE 1E I -,lTE

Ix "J d' 1
J -- r i


Figure 3-11. Update Rows Screen




Add a Column

Adding a column requires inputting all the attributes of the column in the add


column screen as seen in Figure 3-12, where a single column is displayed. Similar to the


table creation, the column name, data type, length, constraint, and whether it is primary


key or not must be given. In addition, the position where the column should be added in


I









the table must be specified. This is one of the distinct features from SORDS, in which the

column is always placed on the right most of the table. To add a column in the enhanced

system, the user needs to decide first what the ordered number will be after the column is

added in the table. This number should be between one and the total number of the

columns plus one. If the number is beyond this range, an error message is given.



r: -- ," ,..n .~a n rr





I t Fuug i _al b l t'n ._ .at) 0. .i M










Figure 3-12. Add Column Screen






are used. For an example, suppose there are columns "hwl", "hw2, "and a functional
*' *j* jaL n -nLr ^ i'Ti r.tF IP i | .. il-IIrfirI| n 'IlI g .ftafl1SFJWCSI












column "total" in a table, the column "total" is used to sum hwl and hw2. If the user













wants to add "hw3" in the table, the corresponding functional column needs to be

updated as well. Since the functional column only works for the columns to its left, the

new column "hw3" must be added before the column "total" in order to get the correct
total result.
Figure 3-12. Add Column Screen









Drop a Column

Dropping column simply requires the user to select a column name from a drop-

down list containing all the columns in a table. Once the column is selected and the "drop

column" button is pressed, any data in the column and the column itself are removed.

Any functional column depending on the deleted column should be updated as well.

Logout

The last button on the function menu is the "logout" button. After the button is

pressed, another screen is displayed with an acknowledge statement and the information

that the user has been logged out.


Summary

This chapter is an overview of the ESORDS system from the users' point of view.

It begins with the description of registration and access levels, followed by the

explanation of every available operation in the database menu and the table menu. Finally

the chapter discusses all the table operations in the main menu for a specific database.

The emphasizes the features of muti-table viewing, multi-table transposing, individual

cell addressing, and flexible column addition which SORDS does not possess














CHAPTER 4
IMPLEMENTATION DETAILS

Chapter 3 is an overview of ESORDS from a user's perspective. This chapter

discusses the underlying details of the system from an administrator/programmer's

perspective. Starting with an introduction of the installing process in Unix, the chapter

explains the implementation methodologies of the enhanced features in ESORDS, which

consists of table creation, multi-table viewing, multi-table transposing, flexible column

addition, as well as table displaying including cell addressing in the functional fields.


Installing Process

The installing script is responsible to establish the system tables and set up a path

to the Perl interpreter for each file of the system using Perl. But before running this script,

all the parameters in the system configuration file must be specified. These parameters

are:

1. A database source variable "database name".

"CISEDATASERVER_0" is the currently used database name.

2. Database driver variable "driver". It is set to "Sybase" for Sybase or

"ODBC" for MS Access. The database name and the driver are used to

define the environmental variables for the database connection. They

should be updated properly corresponding to the selected database.

3. A variable alias to the SQL function file, called "SQLrequire". The SQL

function file generates all necessary SQL statements, which are used to









communicate with the database through DBI handlers. Although the SQL

format is not the same in different databases, a separate SQL function file

enables ESORDS to be used to port to any database. To connect to

Sybase, the file can be called "sybaseSQLfun.pl". If any other database is

used, "sybase" can be replaced with the proper name.

4. "dbusemame", which is set to the system owner's usemame into the

database.

5. "dbpassword", which is set to the owner's password to the database.

6. A variable defining the path to the Perl interpreter, e.g.

"/user/local/bin/perl".

7. A CGI path pointing to the directory where all of the ESORDS files are

located.

After the configure file is done, the installing script can be run. It begins with the

connection to the database through a database driver handler. Then it asks for the

administrator's usemame and password to log into the database on the command line.

Next, the SQL statements are generated and executed to create system tables, which

consist of "sys_admin", "sys table", and "sys_db". The table "sys_admin" is used to

keep information of all the users' username, password and access level. The value of

three in the access field indicates the user is an administrator, two indicating a creator,

and one indicating a normal user. The table "sys table" keeps records of all the tables

created in the system. Each row in this table stores a table name, a database name the

table is associated with, the table's creation time, and whether the table is a view or not.

There are only two fields in the table "sys_db", which keeps all the database names and









the user names who are the creators of the databases. The final step in the installing script

is to make all the Perl files having a path to the Perl interpreter so that the system can be

active. This is done by renaming each original Perl file as "temp.txt", putting the

interpreter path on the top line of the original file which is empty now, then appending

the content of the temporary file to the original file. Once the installing procedure is

completed, the system can be executed and accessed through the browser.


Table Creation

To create a table, the table name, the number of the columns and the column

attributes are required. After the system gets the table name, the associated database

name is prefixed the table name so that the table name is unique in the underlying

database. This allows different databases created in ESORDS to be able to have same

table names.

Based on the table name and the column attributes, a SQL statement is generated

by calling the function "createtable" in the SQL function file. The table is created after

the SQL query is sent to the database and executed. If any error occurs, this process is

aborted and the table is not created. The information of the newly created table as well as

its related database is then stored in the system table "sys table".

Whenever a table is created, it is requested that another two supplemental tables

be produced as well. One is the function table, which is used to store the special rows for

the primary table. The table has all the same column names as the primary table except an

extra label field. This label is to denote the special row and is also used as the row ID

when a table cell in that row is addressed. The data type of all the columns in the function

table is set to "char" or "varchar", because most likely functional formulas are entered in









the special rows. The other supplemental table is the column position table. In ESORDS,

it is allowed that a column can be added at any position instead of only the right most of a

table. Thus, once a primary table is created, the position of each initial column should be

recorded. This is implemented by creating a new table which has all the same column

names as the primary table, and then inserting the position numbers into the table in order

when the columns are first inputted. Since the data type of the columns in the primary

table can vary, the positions that are just integer numbers can not be always put into the

primary table. Although the function table can be used to store the positions if they are

entered as "char type", each time when the special rows are fetched, the first row of the

table keeping the column positions has to be skipped. The purpose to design three tables

is to ensure each table has a pure role. The primary table is to store regular rows, the

function table is to store special rows and the column position table is just to store the

column positions.

The permission setting for the table must also be specified. This permission

determines how much a normal user can manipulate a table. After the permission is set, it

is stored in the database as a four-character binary string representing viewing, insertion,

updating, and deleting, respectively. Whenever a table is retrieved, its permission setting

is checked first so that the corresponding table operations are displayed.


Multi-table Viewing

Multi-table viewing in ESORDS is one of the most advanced features compared

with its predecessor SORDS. In ESORDS, a view can be created based on multiple

tables and/or views instead of just one or two tables with computed columns and different

names from those in base tables. Moreover, a view can be generated not only by equi-join









but also by some other restricted conditions. Therefore, the user is free to create a view in

any form. He/she can pick up some of the original columns from one or more base tables,

or add new columns into the view. He/she can also retrieve subsets of rows from one or

more tables through restricted conditions. In a view, the user is not allowed to insert,

update, and delete regular rows. The user is also forbidden to add and drop columns. The

reason to set up these conditions is apparent. For an example, if a column is to be added

in a view, it is unlikely to decide to which parent table/view the column belongs. The

similar situation happens for the other restricted operations. But it occurs frequently that

the user would like to have computational columns in a view based on multiple base

tables. In SORDS, the only way to make it feasible is to add functional columns in the

base tables first, and then create a view. This usually involves several steps. If more than

two tables are needed, it becomes impossible. With the addition of computed columns in

a view in the current system, the functional columns in a view can be obtained by joining

more than two base tables/views through only one step.

The special rows, in the contrary, are not limited to be added, deleted, or updated

in a view. In the functional fields of the special rows, seven built-in functions and any

other mathematical formulas including addition, subtraction, multiplication and division

are applied, which is as same as the functional columns in regular rows. The syntax for

the functional formulas is explained in later section on table displaying.

After the input parameters for creating a view are selected on the table menu

screen (Figure 3-3), which consists of the view name, whether computed columns are

needed, the number of computed columns if applicable and the base tables, they are sent

to the appropriate part in the system through CGI and the web server to build the create









view screen. All the columns of selected tables are displayed using scrolling lists with

table names as the headers. The computational columns can be entered in a tabular form

providing text entries for entering column names and formulas. Joining conditions are

obtained by listing columns of every pair of all the combinations of selected tables/views

through the drop-down menus. Between each pair of tables there is another drop-down

menu, listing six possible conditions including equal, not equal, less than, greater than,

less than or equal to, and greater than or equal to.

Once the user submits create view page, the system receives all the parameters

and checks if there is any inconsistency among them. Then four arrays are defined

according to the SQL syntax of creating a view. The first array contains the view columns

that may come from two sources: the original columns of the base tables or views and

computed columns based on the original columns. When both sources are used, the

former columns are ordered prior to the later. Checking should be done if there are any

repeated column names in this array. The second array stores the original columns of the

base tables/views and the computational formulas if applicable. By way of pattern match

any column name in the formulas is replaced with it full name, which is in the format:

"tablename. column_name". In this way the column names in the SQL statement are

kept consistent. It is allowed for the user to enter only column names in the formula if the

names do not conflict. When the same column names are chosen from different base

tables, it requires the user to put the table name ahead of the columns name with a period

in between. The system checks for any inconsistencies. Every element in the second array

should correspond the elements in the view column array one by one. The third array is

the base table array that keeps all the selected table/view names in the order when they









are picked from the check-box list in the table menu screen. The last array stores all the

join conditions and other restricted conditions. Similarly, each column name in the

conditions should be prefixed with its corresponding table name. Then all the four arrays

are passed to the function "createView", where they are wrapped to make up a create-

view SQL statement. The statement is prepared and executed. If no error occurs, a view

is created. This new view is then inserted in the table "sys table" with the field "isview"

being set "1", Similar to the table creation, two other supplemental tables are created as

well for the new view. One is used to store the special rows, the other is to store the

column positions. This is because, for a normal user, a view is not distinct from a table

when displayed through a common function, in which the column position table is always

checked first. The permission settings for the view must be defined as well. It is handled

in the same way as for a table.


Table Transposing

Multi-table transposing is another great feature in ESORDS. It provides a means

for a user to view the existing tables at a different angle, catering to his/her specific

needs. A transposed table is created based on multiple base tables, on which some

constraints must be created. The base tables must be alike, which means their names must

be similar, and the number, the name, and the data type of the columns must be identical.

Therefore, it is required the base table name should be used in a format as "XXXX#",

where "XXXX" represents any combination of alphabetic letters from a to Z in any

number. This part of the name should be identical for every base table. "#" is a positive

number which is used to sort the base table names. The column names use the same

format as the base table names except the first one. In the base tables, each field of the









first column is the identifier of the row. These identifier names must also use the same

format as the base table names. The reason is that when a user wants to view a

transposed table, the identifiers in the first column may become the column names or just

the transposed table names depending on in which direction he/she looks. It is noteworthy

to mention that the base tables may have different number of rows with different row

identifiers, without affecting creation of a transposed table.

After the user selects the base tables from the check-box list, the first thing the

system does is to check whether these tables follow the constraints. Regular expressions

are used to do the checking. If no constraint is violated, the base table names, the column

names, and all the identifiers in the first column are stored in three separate arrays. The

identifiers are collected in an array by fetching every row in all the base tables. If an

identifier is not in the array, it is added, otherwise it is skipped. The arrays are then

passed to a function that does the actual job to create transposed tables.

The transposed tables are classified into two kinds. As illustrated in Figure 3-6,

the base tables have names of Zn with the columns of Xn and row identifiers of Yn. The

tables labeled by Z*X, are obtained by slicing the base tables along the rows with the

same row identifiers. They have all the columns of base tables, with the base table names

becoming the row identifies. Similar to the base tables, they may have a different number

of rows. The tables labeled by Z*Y are obtained by slicing the base tables along the same

columns. They use the row identifiers as the columns and the base table names as the row

identifiers. For such kind of tables, empty cells may exist due to missing rows of the base

tables.









In the create-transposed-table function, the base table names are sorted first

according to the number in their names so that they can be arranged in an ascending order

when used as row identifiers. For the tables labeled by Z*X, all the column names except

the first one, the column data type and the column length from the base tables are used to

generate a SQL statement. A transposed table is then created after the SQL statement is

prepared and executed. This table should have a name selected from a list of row

identifiers of Yn according to the user's choice. Correspondingly, the new table is

inserted in the system table "sys table", and two other tables are produced as well to

store the special rows and the column positions. The values to be inserted are obtained by

retrieving a row of values from every base table. This query is generated based on the

condition of the row identifier equal to the transposed table name. If the row is defined in

a base table, all the values in that row with the new identifier, which is the base table

name now, are inserted into the transposed table. For the tables labeled by Z*Y, the row

identifiers in the base tables become the column names. These columns must have the

same data type as that of the values in the base tables. The new table should have a name

from the column names of Xn. Similarly, once a transposed table is produced, its

information must be put into the system table "sys table", and two other supplemental

tables must be created. For the Z*Y tables, it involves more steps to fetch the values. The

SQL statement is generated by selecting every row of a base table with the constraint of

the column equal to the transposed table name. Each query gets only one value. The

values obtained from all the rows defined in a base table build up one row with the base

table name as the row identifier. Then the row is inserted into the new table. Since not









every row is present in the base table, empty cells result. After a transposed table is

created, its permission settings must be determined as well.


Add/Drop Columns

As mentioned in table creation, an auxiliary table containing the column positions

must be created following the creation of each primary table. This table is used to adjust

the column positions when a column is added or dropped. To add a column in a table, the

column attributes and its position must be given. After the system gets these input

parameters, the current column positions are fetched into an array from the column

position table. If the position of a column in the table is greater than that of the column to

be added, that position should be incremented by one, otherwise it is not changed. The

new column position is then appended to the end of the array. All three tables including

the primary table and its two supplemental tables should be updated by calling a function

"addcol" in the SQL function file. As a result, the new column is put at the right most in

the corresponding tables of the underlying database. Now all three tables have one more

than the original number of the columns. Finally the values in the position array are used

to update the column position table. Similar procedure works for dropping a column. The

difference lies in that the function "drop_col" is used to modify all three tables, and the

position value should be decremented by one if it is greater than that of the column to be

dropped.


Table/View Displaying

In ESORDS, a view is hidden from a standard user. Therefore, there is no

difference between displaying a table and displaying a view. This is achieved by calling a

common function "displaytable" whenever the main menu is loaded.









In the "displaytable" function, there are mainly two issues: the ordering of the

elements and the displaying of the actual content of a table. For the issue of the ordering,

the rows of a table/view can be sorted based on up to three columns in either ascending or

descending order. After a user selects the column and ascending or descending and

presses the redisplay button, the content of a given table is re-displayed by loading the

entire script. This is achieved by combining the column name and the ordering

information into a select statement, which is then passed as an input parameter whenever

a function button on the left frame of the main menu is invoked.

To display the actual content of a table, it involves fetching all the information

from the underlying database, such as column names, values and formatting this

information into a html table. The html table is basically made of two parts. The top part

is to store the regular rows with one row after another, and the bottom part is to store the

special rows. There is not much difference between the two except that there is a label

row above each special row.

Because the columns of a table in the underlying database are arranged in the

order of first-come-first-in, when the table is actually displayed the ordering of the

columns must be adjusted according to the positions initially given. To solve this

problem, the position values of the columns are fetched from the column position table.

Then the column names are stored in an array in the order of their position values, and

later displayed in the same order as the column headers of the html table.

Displaying the values of a table is more complicated than displaying the column

names. It involves re-ordering the values of all the rows corresponding to the column









positions. It also involves handling the special rows and the functional columns in the

regular rows.

Re-ordering the values of a row is similar to re-ordering the column names. After

a row, which may have different type of data, is fetched from the table in the underlying

database, the data in that row are placed in an array in the order of their corresponding

column positions. If the data is not a functional formula, its value is displayed directly.

Otherwise, the formula has to be computed first before its value is displayed.

The first character in the functional fields is always a "+" symbol. It is used to

denote the functional formula when the system checks the data field. A functional

formula may apply numeric values, other cell values, and pre-formatted functions

including SUM, AVG, CNT, MAX, MIN, SPD, and STD. The operators allowed to be

used are "+", "-", "*", and "/".

To get the value of a functional formula in a functional column usually involves

three steps. The first step is to calculate the value of the built-in functions if applicable.

The syntax for the built-in functions is pre-formatted as

"function(start column:end_column)," where "function" is one of the seven built-in

function names, and "startcolumn" and "endcolumn" are the starting and the ending

column names to which the functional column refers. It means that the function works on

the values from the starting column to the ending column within the same row as the

functional formula. The values are placed in an array first, which is then passed to the

corresponding function that does the computation. The calculated value returned from the

function is used to replace pre-formatted formula. The second step is to replace the

column name(s) if any in the functional formula with the corresponding values) in the









same row as the formula. After all the names in the formula are denoted with the values,

the formula is sent to the computation function, which is the third step. The final result is

then displayed in the html table. Therefore, it is concluded that the functional columns are

row-based, which means any values in the rows except the row where the formula is can

not be used.

However, the issue of looping formulas may be caused when a functional column

uses the value of another functional column that again uses the value of the first

functional column. To solve this problem, it is required that a functional column must use

the values of the columns that are to the left of the functional column, so that the

functional formula is guaranteed to be valued.

On the contrary, when the built-in functions are applied in the special rows, they

are column-based, which means that the function works on the values of the regular rows

in that specific column. The syntax for a built-in functional field is "+function". The

process to get the final value of such format only involves two steps: passing all the

values in the regular rows of the given column to the function and computing the values

to get the result. This result is then placed in the corresponding field of the special rows.

Additionally, a functional formula in the special rows can use a value of any

individual cell in the regular rows and the special rows that are above or prior to the

given formula. Therefore, in this situation, the special rows are not column based. To

index a value of an individual cell, the syntax of "row#columnname" is applied, where

"#" represents the identifier of the row in which the cell is located. For a regular row, it

represents the ordering number of the row. This identifier can be obtained by choosing

the option of "Display table with rowlD?", which is on the top part of the right frame in









the main menu. After the "redisplay" button is pressed, an extra column is placed on the

front of the table with the header of "rowed". The "column name" in a cell address

represents the column to which the cell belongs. As a result, with a means of addressing

individual cells in a formula, a user is free to get any computation information based on

existed values in the table.

It usually involves three steps to get the value from a non pre-formatted formula

in the special rows. The first step is to replace the cell address of the regular rows in the

formula with the corresponding value, which is initially put in a hash with a recognized

hash label. The second step is similar, which involves replacing the cell address of the

special rows with the corresponding value, which is initially put in a different hash. The

replacement is performed through the pattern matching of the regular expressions. The

third step is to pass the formula to the computation function, which computes the final

result for displaying.

Usually the functional formula gives a floating number with more than ten digits

after the decimal, which is not necessary when displayed. To make it shorter, a function

"truncate" is called whenever a value is returned from a computation function. This result

with at most two digits after the decimal for a floating number is displayed in a functional

field.


Summary

This chapter gives an overview of implementation details on the enhanced

features of the ESORDS system. Included are table creation, multi-table viewing, table

transposing, flexible column addition and table displaying with comprehensive functional

fields.














CHAPTER 5
TESTS AND RESULTS

In this chapter, tests on the features improved by ESORDS and the corresponding

results are described step by step. Basically, two sample applications are discussed,

which cover all the functionality to be examined, including multi-table viewing, table

transposing, flexible column addition as well as individual cell addressing. The first

application is a school grading system. The second one is a proposal evaluation system.

The chapter concludes with error checking by ESORDS.


School Grading System

In the school grading system, originally what we have is the raw grades of the

homework, exams, and projects for a particular course. Each homework, exam, and

project may have different total points. What we need is a dynamic grade spreadsheet that

keeps recording the percentage as well as some statistical results of the homework,

projects, and exams during the semester. Usually what a student cares is not only the final

grade but also the level he/she can be at among the total students at any point of the

semester, so that the student can evaluate himself/herself whether he/she should continue

to learn the course or not. Additionally, the teacher needs an efficient way to calculate the

grades and do the analysis. She/he can also determine the level of the difficulties of the

exams based upon statistical results. ESORDS applies multiple table views that require

only two steps to satisfy these requirements.










r:Tble

li '1
lF,-, Fl,2 F,3










:Tabl hv'dPiF
sI d nld" D I lh Il: l" h I
i 1 I | 1 I J? I| b






I : :T : ,





1U LO ^


t ble:
:air1PoUil-ir
et iu eim3


TIble e: -m In ids



I -I5 I-4 2




sa :blt e. : .tc>


01
1 ; I ii. I ( | ii'" :::

3 9"- r 2 "1 I "iii;


TE.bJe prPoints

pil pI p2 pl p4
0 0 F0- 0

Table.: jitGrcade
stIrdeitlD l pl1 p12 pl3 Ip.4
I o 1.5 1' Idc 30 o
I 1 | i I 17-r 30

I 14 19 2




studeiillD l pU i |Ipi2pi'pl pI p |pi4p'ip p'ijt''lPe'i

+ ) I ::
I 0 .1 :0 1 -j .,:.
\ 0 + 0 ^ 'L""
St


T:tle- hepWu h TIble ro.:,-rdes
]i xm- -j- O'suideIntlD IlkiAA2 examIL-Ae projectLA\g [(ial
hw examn pmiojeL'l' ---r- --- -i -
1 1 : 1 9- i2:: | 5 1


I -4 4 I JS 6E
I I : ',: I i' i: 5: "- |I

| ?F 4I. 5i J 5 1 5


Figure 5-1. Flow Chart of How "totalGrades" View is Obtained from the Base Tables
"hwGrades," "examGrades" and "pjtGrades"


Multi-Table Views

Initially, the base tables, "hwGrades," "examGrades," and "pjtGrades" were

created in the system already, as seen in Figure 5-1. They store four students' three

homework grades, three exam grades and four project grades, respectively. The students










are represented by the "studentlD" being the first column of each table. The tables,

"hwPoints," "examPoints," and "pjtPoints," keep the records of the total points of each

homework, exam, and project. There is another base table "hepWeight" which contains

the weight of homework, exam, and project for the final grade.

To get homework average, exam average, project average, and final grade for

each student, two steps are required based on the existing base tables.

Step 1: Create percentage views.

Three views, "hwPcts," "examPcts," and "pjtPcts," are created as seen in Figure

5-1, which represent homework percentage, exam percentage, and project percentage,

respectively. Because all of the three views are built in a similar way, only the procedure

of creating one view, "hwPcts," is explained here.

Please seect the colunins you want in your view:

Coluiir rlnai s 1'amiii li rlrying iabhles:


Ii' .

I.I

[ ColiulUi namui based on coiu.mpultion:

r-I.; I I,, r, i.. i r l 1 1 .. r I I









select the colunins fields you want to join for your view: (they must be sane data type)

S1 I 1= 1, I .-



Figure 5-2. Input Parameters for Creating View "hwPcts"









To get the view of "hwPcts" which stores the percentage result of each homework

and homework average from the tables of homework grades and homework total points,

four computational columns are needed. First, the view name "hwPcts" should be entered

in the text field on the table menu screen as displayed in Figure 3-3. Next, the option of

"Are there any computed columns?" is selected and the number of computation column is

input. Once the base tables, "hwGrades" and "hwPoints," are checked, and the "create a

view" button is pressed, the create view screen appears.

As displayed in Figure 5-2, the column name "studentID" from the base table

"hwGrades" is highlighted and the other four computational column names and their

corresponding formulas are entered. The last column "hwPct" represents the average of

the first three computational columns. Its formula is written as

"((hwGrades.hwl 100)/hwPoints.hwl + (hwGrades.hw2* 100)/hwPoints.hw2 +

(hwGrades.hw3* 100)/hwPoints.hw3)/3." The join condition for the two tables should be

"hwGrades.hwl <= hwPoints.hwl." After all these parameters are input and the create

view button is pressed, the view "hwPcts" is then created as seen in Figure 5-1.The other

two views are created in the same way as the view "hwPcts."

Step 2: Create view "totalGrades."

Creating view "totalGrades" requires the existing views "hwPcts," "examPcts,"

and "pjtPcts" and a base table "hepWeight." We may change the column names "hwPct"

in the view "hwPcts" to "hwAvg," "emPct" in the view "examPcts" to "examAvg," and

"projectPct" in the view "pjtPcts" to "projectAvg," by creating four computational

columns are chosen, as displayed in Figure 5-3. The final grade named "final" has the

formula as hwPcts.hwPct hepWeight.hw 0.01 + hwPcts.emPct hepWeight.exam







57


*0.01 + hwPcts.pjtPct hepWeight.project *0.01." The join conditions for this view are

"hwPcts.studentID = emPcts.studentID" and "hwPcts.studentID = pjtPcts.studentID." See

Figure 5-1 for the results in the view "totalGrades."

Plar Eelrrs e tclnmumyu wm rin jyor 'ilew

h.aa .l lHJ' FJ-m irklv ra.,d j.
i;~r pF .j i': r^-' nI r^ ft


I h '.. CI-,L I- h.i"
ihr.-"ri F Lp I
r.,..-r. k ,, u W, L, ,
hr.I .. i''





SCII. ** l ri. L r. l r' L i r I
J ii ,. F : i- ':'' en :L


i l lt ll


rid .l L.F .i ti 'r L I L Iu' N I -*- LP'.r


Plee ndec. ise aclnan lels ymu wt D Jon hr yur vlewA hey mun brmue dmia rype
lT T-r" -'r I *-.,'".'r-" hr.l'r' t'. I Frp"r" 1 r,.:r.-r- I. r.,-.r' I r.r .

FrI | j i | i i[ I |- l, .ijl, 1 _i- |- !- -s _w | F i- L Fr


Figure 5-3. Input Parameters for Creating View "totalGrade"


Add column "lastname"

Table '.-iGr:.de

|stineindtlD Ilaslnaime I I l l 2

| C | -i I
I i I
I I I


Table' hnwGrades
stulAenuliij hvl fiw2f hW3
I U F _5 7 -I R i?
I 1 li I13.5 | |46

I 2 I 0 I 50 0 4


li'k3
.i3-


Drop the column


Il i r h .'C rT -.
Add data sndeitlD Ilastimane i l 111h2 ikiu

| I | I .:'.-d | I 0 J: I -1=
I :i I "'r-' : |i 1 ", T1 :


Figure 5-4. Tests of Adding and Dropping a Column at the Second Position









Add/Drop a Column

In ESORDS, a column can be added in any position instead of the right most

position. Here the table "hwGrades" is used as an example. The test is as followings: a

column named "lastname" is added first in the second position, followed by entering the

data using update function, then the column is dropped. The result is shown in Figure 5-4.


Proposal Evaluation System

Suppose there are five evaluators who are in charge of grading proposals based on

five different criteria. The raw grades are stored in five base tables named "eval_l,"

"eval 2," "eval 3," "eval 4," and "eval_5," in which the column names "C ," "C2," ...

"C5" represent five criteria and the row identifiers "pl," "p2," ..., "p5" representing five

proposals. It is not required that each evaluator has to grade all the proposals. Therefore,

the base tables may have different numbers of rows. The total score for each criterion is

stored in a base table. Thus, we can create five views corresponding to five base tables.

They are named as "evall," "eval2," "eval3," "eval4," and "eval5." These views contain

the percentage scores based on the total scores in table "Cweigt". The contents of the

views are displayed in Figure 5-5.


T.bl e-.ll T:.!ie E~ci l T ble
Cl' 1l (1C2 JC4 CS Ip _1D FCl1tI U24 C CpID |c2 2 T 4 FUN pD1
Ii:|- S 0 | 5 pI -o- Iso Ip I101'o 1i4- 100 11"0
15 ":' 1 '" 1 -j 41 ;ji [o j1Ii I I


T ~ie V:,4 T:,bli, e..-;,I

I, IcPD I -' D : I- 1 j '2 1 ('41 ( -'

I I;:: 1| I| 1| 0 0 |_ I| I-
Figure 5-5. Five Evaluator Views









To create a transposed table, such as "p2," "C5," the five views "evall," "eval2,"

"eval3," "eval4," and "eval5" are checked first on the table menu screen (Figure 3-3).

Then a table name can be selected from either of the two drop-down menus "eval p"

and "eval *C" on the create-transposed-table screen (Figure 3-6). Finally, the content of

the created table is displayed on the main menu. Figure 5-6 and Figure 5-7 illustrate two

kinds of transposed tables, respectively.
T:.,ble 1.2
I ID ICl c11 2C3 c4 C5
I Fe":'i | | I F- F0 |o- i---|
IK-:.i- I-|o I-: I.oI Io ISO


Figure 5-6. A Transposed Table "p2"



In table "p2," the column names are kept the same as the base views, but the

base view names become the row identifiers. In table "C5," the proposal names act as the

column names and the base view names become the row identifiers. It is noticeable that

there are empty cells in this kind of transposed tables.

T::.bi C'.
i [ilDI ID I pI 1 2 I Iip4 p5


2 : 1 00 10 '' i
: "1 Fo I I- Iao

I c: I 100- I

AVG
.--,-.-- IC I-: 0 I d 6a FIo c
SPD
E I p I Ta i e10 z wit Sm e -Ri
STD

k,_el
ne. A |le C5"ii i0 I 10

Figure 5-7. A Transposed Table "C5" with Some Special Rows









Individual Cell Addressing

Note that in table C5 there is a special row with a label of"nel," which

represents the normalization of the row identified by "evall," For an example, the third

cell in the special row "nel" has formula written as "+rowOpl/(rowAVGpl*0.01)." The

reference of "rowOpl" represents the value in the third cell of the row identified by

"evall" which has the value of 100, and the reference of"rowAVGpl" represents the

value in the third cell of the special row identified by the row label "AVG" which has a

value of 86.67. The "rowID" numbers are listed in the first column after the option

"Display table with rowID?" is selected on the main menu. The built-in functions "SPD"

and "STD" are also tested in the special rows as displayed in the table (Figure 5-7). The

results returned from these functions can be used to make evaluation scores equivalent

for a particular proposal. If there is a big difference between the scores given by different

evaluators, that proposal needs to be re-examined.


Error Checking

When a user input incorrect information, the system checks and returns an

appropriate error message. This happens in several situations as below:

Create a View

1. The view name is not given for view creation.

2. The option "Are there any computed columns" is no, but the column

number field in entered with some number, vise versa.

3. No base tables/views are selected for creating a view.

4. A computed column name is given but with no corresponding formula.









5. The column names in a formula are not consistent. If the referred names

from the different tables are identical, they must be prefixed with their

corresponding table names.

6. The column fields from two tables for join must have same data type. If

one is chose "None," the other must also be "None."

Add a Column

1. A position number is not provided.

2. The position number is beyond the range, which is from one to the total

number of the columns plus one.

3. The column name is not provided.

4. If the column data type is "char" or "varchar," the length is not given.

Create a Transposed Table

1. The base tables/views for creating a transposed table must have identical

column names except the first one. All the table names, column names,

and row identifiers should be in a format XXXX#, where XXXX

represents any combination of alphabetical letters from a to Z, and #

represent a number.

2. The table/view name is conflict with an existing one.


Summary

This chapter examines all the features developed in ESORDS, which contain

multi-table viewing, table transposing, flexible column addition, and individual cell

addressing through two sample applications. The corresponding results are illustrated by






62


the figures, which prove all the functionality work as expected. The chapter also lists

some special conditions that the system automatically checks and gives error messages.














CHAPTER 6
CONCLUSIONS

This chapter highlights the enhanced features in ESORDS referring to its

predecessor SORDS and their significance to the end users. The chapter also issues some

limitations in the system and corresponding suggestions for the improvement.


Summary of ESORDS

Possessing all the advanced features of SORDS, such as database independence,

cross-platform, secure access control, table-locking for concurrent transactions, and easy

to use methods for all the operations of a database through a user-friendly on-line

interface, ESORDS is a more user-favored web database system with more efficient

spreadsheet capabilities. This is achieved by adding functionality of multi-table viewing,

table transposing, adding a column at ease, and absolute cell referencing in the formulas.

ESORDS provides a variety of tools for users to look at data in the underlying

database through a view. In ESORDS, a view can be derived from one, two, or more than

two tables or existing views. A view can have different column names from those in the

base tables/views and it can also have computational columns. Multiple joining

conditions are provided, and more restricted conditions can be used for view creation.

Although this appears complicated, creating a view is not at the access level for normal

users. It is the creator of a specific database who does the job. The creator is responsible

to collect the user's requests and construct the views. Users just see the information in a

view. However, they can insert, delete, and update specials rows of a view if the









permission is given. Introducing multi-table views does not make the system more

complex, on the contrary, it make users feel more portable and friendly.

Of all the relative systems, ESORDS is special in its ability to create transposed

tables. It is likely that multiple similar tables are built in the underlying database, just like

the sample application of Proposal Evaluation System, and the requests to look at the data

at a different angle can not be satisfied by the common SQL queries including views.

Therefore, it is necessary to create transposed tables to customize each user's particular

interest. Moreover, users can perform statistical analysis on their interests that are not

allowed in the underlying tables.

In ESORDS, adding a column in a table at any position makes the table more

alive, because the table can be easily built and recovered if the column is dropped by

accident. Otherwise, the table has to be recreated. The use of cell reference in the

formulas is essential for a spreadsheet. ESORDS applies absolute cell referencing method

in the special rows, which enable a formula to use any other cell values in the regular

rows as well as special rows.


Some Ideas to Improve ESORDS

Although ESORDS is a robust and user-driven system, there are some

improvements that can be made to increase the usability of the system.

1. Allow a user to change his/her password freely.

2. Provide a cleaner and more powerful interface for the operation of

creating a transposed table. Because not all the tables in a database can be

used to create a transposed table, only the group name of the valid tables

should be given. Also, provide a means for the user to automatically select









all tables whose name follow a certain pattern, and allow for transposed

tables other than the two currently supported (i.e. X*Y, Y*X, X*Z, Z*X,

Y*Z and Z*Y). Moreover, allow data slicing of more than three

dimensions.

3. Number formatting can be improved by giving users freedom to select the

number of digits after the decimal for a floating number when displaying a

table.

4. Add more built-in functions to increase spreadsheet capability.


Summary

This chapter presents the key functionalities, and the reasons to implement them

in the ESORDS. The chapter ends up with some system limitations that need to be

improved in the future.















REFERENCES


[1] O'Reilley & Associates, Inc., "What is Perl,"
http://www.perl.com/pub/qa/Whatis Perl?, 2000.

[2] Eric C. Herrmann, "Mastering Perl 5," SYBEX Inc., San Francisco, 1999

[3] Z. Peter Lazar and Peter Holfelder, "Web Database Connectivity with Scripting
Languages," http://www.w3joumal.com/6/s3.1azar.html, 1997

[4] Tim Brunce and J. Douglas Dunlop, "DBI Specification,"
http://www.etsetb.upc.es/mirror/programming/perl/dbi/doc/dbispec/1.html, 1997

[5] Tim Brunce., "DBI-A Database Interface for perl5,"
http://forteviot.symbolstone.org/technology/perl/DBI/doc/tpj5/index.html, 1999.

[6] C. J. Date and Andrew Warden, Relational Database Writings 1985-1989,
Addison-Wesley Publishing Company, Inc., Reading, MA, 1990.

[7] Electronic Book Technologies, Inc., "Transact-SQL User's Guide" in SyBooks,
1994.

[8] MathsNet, "MathNet homepage," http://www.anglia.co.uk/education/mathsnet/,
2000.

[9] Alliare Corporation, "Cold Fusion 4.5 Features,"
http://www.allaire.com/handlers/index.cfm, 2000.

[10] DevX.com, Inc., "Guide to Application Servers," http://www.sppserver-
zone.com, 2000.

[11] Sun Microsystems, Inc., "NetDynamics Product Features,"
http://www.netdynamics.com/product/features/nd400_features.html, 1998.

[12] Scott Clark, "Three Web-to-Database Connection Tools,"
http://www.webdeveloper.com/database/dbreviewswebconnectiontools.html,
2000.

[13] Dan Yu, "A Practical Web Database Application Builder," Master's Thesis,
CISE, University of Florida, 1998.






67


[14] Changlu Yang, "A Web Database Application Builder for Non-Programmers,"
Master's Thesis, CISE, University of Florida, 1998.

[15] Xiaoxi Cao, "Spatial Web Database System," Master's Thesis, CISE, University
of Florida, 1998.

[16] Thomas J. Davies, "A Secure Online Relational Database Application," Master's
Thesis, CISE, University of Florida, 1999.

[17] Joel Maner, "Secure Online Relational Database System (SORDS)," Master's
Thesis, CISE, University of Florida, 1999.















BIOGRAPHICAL SKETCH

Dan Kong was born in Haicheng city, Liaoning Province, P.R. China. She

received her Bachelor of Engineering degree in materials science and engineering from

Shenyang Polytechnic University, P.R. China, in 1991. She worked as a research

assistant in the National Key Lab of Solidification and Crystallization in the Institute of

Metal Research, Academia Sinica for about two years. She received her Master of

Science degree in materials science and engineering from the University of Florida in

1998. In the fall of 1998, she began graduate study in the Department of Computer and

Information Science and Engineering at the University of Florida for the Master of

Engineering degree in computer engineering. She will receive the degree in December

2000.




University of Florida Home Page
© 2004 - 2010 University of Florida George A. Smathers Libraries.
All rights reserved.

Acceptable Use, Copyright, and Disclaimer Statement
Last updated October 10, 2010 - - mvs