Application of middleware in the three tier client/server database design methodology

Joseph Fong

Computer Science Department

City University of Hong Kong

Kowloon, Hong Kong.

e-mail: [e-mail]

Riemann Hui
Division of Computer Studies
City University of Hong Kong
Kowloon, Hong Kong.
e-mail: [e-mail]

Abstract:

With the popularity of personal computers and powerful workstations, today’s users are no longer satisfied with the traditional data processing. They demand eagerly to putting up graphics on existing applications, putting a lot of pressure in system re-engineering. The Client/Server architecture is a less risky and less costly approach in adding a Graphical User Interface for users. The developer has to determine data and program logic distribution among client, middleware server, and the top-tier server. Middleware is a kind of software that links programs running on different platforms, transmitting messages and data. It plays an important role in 3-tier architecture. There are two types of middlewares: the first connects client programs to server programs, and the second provides data access connections to heterogeneous data sources. The developer also needs to separate on-line transaction processing, project-oriented data processing, and history data from each other. In view of no existing standard method in developing a middleware server, this paper outlines a methodology to design a 3-tier Client/Server database system. It identifies the role of middleware as temporary storage for better performance and as database gateway for Database Management Systems connectivity. Case studies are used to illustrate the steps.

Keywords: 3-tier client/server system, heterogeneous databases, middleware, data distribution, logic distribution, on-line transaction processing project-oriented data processing, history data.

1 Introduction

Nowadays, information technology is moving in the direction of Client/server (C/S) computing, where multiple platforms of computing equipment are linked together, allowing users to make the best out of each piece of hardware efficiently, economically, and transparently. Application logic and databases are developed on client workstations, as well as the servers. Processing, though mostly initiated by a client and served by a server, could run in a peer-to-peer manner.

GUI applications demand huge computing resources. They offer user-friendly features such as windows, pull-down and pop-up menus, push buttons and point-and-click mouse device. Implementation of GUI directly to a traditional computer system could cause significant performance problems [1].

A C/S system increases its processing power by the distribution of its database and processing logic into different servers and client workstations, thus off-loading the workload on the database server. Such an approach meets users’ demands on the expansion of a system’s capability by incorporating GUI, desktop computing facilities, and moving towards the establishment of a global accessible database for various systems within an organization.

There are several kinds of C/S models. The most popular are the 2-tier C/S and the 3-tier C/S models. Although a multiple-tier C/S model is possible, the complexity in architecture, and the access time between the client and the top-tier discourage such approach. Their comparison appears in Table 1.

Table 1
Comparison of 2-tier and 3-tier C/S database systems

2-tier C/S model

3-tier C/S model

Clients can access all servers directly. The advantage of this model is that it is simple, and easy to maintain. Most of today’s small to medium-size Local Area Network applications are under this category.

Computing resources are distributed vertically at the top-tier host, the middleware server, and the underlying client workstations. The top-tier is usually a computer system containing the majority of a company’s data. The middleware server serves as a client to the top-tier, as well as a server to the underlying workstations. The rationale behind such architecture is to extend computing capability of an existing mature computer system, without major re-vamp to the databases, nor creating performance issues due to extra workload, while still maintaining database integrity, trace-ability, consistency, correctness, completeness and efficiency [2].

The 3-tier architecture is considered to be a second generation of client server architecture because it is extended from traditional 2-tier architecture. The 3-tier architecture adds an application server as a middleware tier between client and database servers. The middleware tier is a separate piece of software, typically running on a separate piece of hardware, that performs most of the application logic such as enforcing business rules and performing complex processing. When an application is broken into user interface, application logic and database management, it is 3-tiered.

In an ideal 3-tiered model, application logic are completely partitioned into a middle machine. The more the application design separates the interface, application logic and data service, the more easily one can change and maintain each domain without affecting the others. However, in reality, it is unlikely that the business logic can be completely partitioned to a dedicated machine. On the contrary, the application logic and business rules should be located where it makes sense.

The application logic which should be located in the client has the following features: it is very stable, data are not maintained by the database server, and it requires instantaneous response. The application logic which should be located in the application server has the following features: it is used by many applications, changes frequently, encodes sensitive information and uses data from heterogeneous data sources. The evolution of application architecture appears in Figure 1.

Figure 1 -
Application Architeture Evolution

A 3-tier architecture has many benefits in comparison with its 2-tier predecessors in performance, scalability, maintainability, presence of heterogeneous databases, and application development. It enables the system to work in a heterogeneous database environment for the following reasons:

• The 3-tier model can work best in a heterogeneous database environment where all the database servers and the interactions with these servers are of different types because the middleware server can be used for provision of data access connections to heterogeneous data sources as database gateway.

• The middleware server provides highspeed native access to multiple databases, so performance is improved over database connecticity solutions like Open Database Connectivity. That means one is not embedding SQL programs into client applications and not locking one´s products to specific database vendors.

• Implementing a 3-tier application using a database gateway as a middleware server can cause the application logic and businness rules achieve database independence. This means that you can use different data sources without modification of the application logic and business rules.

• Data access middleware software provides customers with a consistent interface to multiple data sources (relational DBMSs, nonrelational DBMSs, and file systems). Gateways can be thought of as one form of data access middleware [3].

The cooperative processing techniques in a client server architecture can be classified as shown in Table 2 [2].

Table 2
C/S processing techniques

Processing technique	Description
Data Passing	A communication link is established between the client and the top tier server. Data is passing through from one process to the other along three tiers, either through real-time connection oriented communication path, or through file transfer. Each tier has a process which continually monitors the incoming data, and trigger subsequent database update if deemed necessary.
Remote Execution	Database update at a tier and will invoke a program at other tier to update remote database. Sometimes parameters are required for the execution of the remote programs.
Interactive Execution	A connection oriented communication link is established between the client and the top tier. The program which updates the database in a tier will update databases in other tiers simultaneously. Databases being involved must support the same set of Data Manipulation Language. They need sophisticated transaction management such as two-phase commit [5] to ensure database integrity and consistency.

For example, [5] reported that in the information technology division of hospital authority in Hong Kong, a system CM is responsible for (a) hospital administration, such as add ward, bed and specialties in hospital; (b) admission, bed assignment, transfer and discharge of patients. Due to the team expansion and organization reason, the CM team will be divided into two teams called CM1 and CM2 respectively. The CM1 team is responsible for hospital administration while the CM2 team is responsible for admission, bed assignment, transfer and discharge of patients. The two teams may use different DBMS to store their data because of different project management. If the CM team is really divided into two teams, the CM1 team uses the Sybase DBMS and the CM2 team uses the Oracle DBMS. The existing ward system needs to retrieve and updates data for both patient movement and hospital administration. Moreover, certain application logic of the application will be changed by the two teams. As a result, three problems arise:

• Data integration of multiple heterogeneous database.

• Data Migration of a system from one DBMS to another causes serious maintenance problems.

• Modification of application logic of a system causes serious maintenance problem.

A solution can be provided by changing the existing 2-tier C/S system into 3-tier C/S system with the use of middleware server, OmniConnect, shown in Figure 2 [13].

Figure 2 -
A middleware server for heteregeneous database servers

2 Related Work

[8] pointed out that C/S systems place the application closer to the user. The result is increased performance and more sophisticated interfaces for users. One disadvantage of C/S systems is control; because of parallel processing, conflicts due to concurrency are more difficult to manage. [2] indicated that in the 3-tier C/S architecture, the top tier is usually occupied by the most powerful system and the source of corporate data, the mainframe. The second tier (middleware) contains LAN servers which act as top-tier clients sending requests to the mainframe, and as servers for the workstations residing in the third tier. It requires intra-tiered communication with inter-communication between hosts in the first tier, and between servers in the second tier. The resulting architecture becomes more complex to build and manage. However, the resulting computing capacity has increased in order of magnitude, and is scalable. As number of workstations grows, additional LANs and more servers can be put into operation.

[9] defined that computing applications can be characterized as consisting of a collection of individual components which can be categorized as

• User Interface Processing – Software components responsible for accepting input from users and for displaying or printing result.

• Function Processing – Software components responsible for performing the processing necessary to solve a particular business problem.

• Data Processing - Software components responsible for accessing data stored on external storage devices.

Any number of the three types of application components can be combined in various ways to create different configurations of C/S computing. Viewing computer applications as consisting of collection of components in the three categories listed above is called the three-tier model C/S computing.

[1] suggested that a C/S application can be designed for implementation across one, many or a combinations of tiers. A tier refers to a physical level within a C/S system. With each succeeding level, the architectural complexity expands significantly. Another way of structuring the application is through the definition of application layers. Layering the application refers to how the application is structured. The four basic layers of a C/S application are:

• Presentation – User interface view.

• Application Logic – End user’s business process requirements meet the limits and challenges of technology tools, for example, the logic of a procedural language.

• Business rules – The implementation and maintenance of business specification.

• Database – The access to the database.

The decision as to where these layers reside is influenced by performance requirements, maintainability issues, complexity and project team expertise.

[10] divided middleware products into two categories:

• Primitive middleware – The middleware layer that is able to tie together many different systems to create a single logical system, a virtual system, for example, Remote Procedural Calls, Message-oriented Middleware, Object Request Brokers and Distributed Computing Environment.

• Database-oriented middleware – It uses a primitive middleware to more information to and from a database, for example, ODBC and Java DataBase Connectivity.

[3] argued that gateways can be thought of as one form of database access middleware. One of the benefits of middleware is that a major rewrite would not be necessary if data were moved from one DBMS to another, because direct requests or calls to these DBMSs could be handled by the middleware for the purpose of reengineering. Therefore, it raises the level of abstraction from programmers by enabling them to avoid dealing with various environment-specific and data source-specific interface. However, middleware introduces a layer of software between the application and the target data source, which will cause additional instructions to be executed and can create overhead that may affect performance. In addition, if the middleware must interface to a production DBMS, the performance of that DBMS may be impacted as well. [6] concluded that the approach of a replication server provides major alternatives to a two-phase commit mechanism for designing on-line distributed databases. Among its advantages, it reduces network and data server load and reduces transaction times for users by providing local data. It improves availability, since processing continues when network links are down and supports real world problems including heterogeneous environment.

This paper outlines a C/S database design methodology using middleware and based on data processing techniques requested by the application to remotely access data and maintain database integrity. The techniques can be divided into:

• Online transaction processing – It requires current data update to reflect business operation, and applies data passing, remote execution and interactive execution in middleware and top-tier.

• Project-oriented data processing – It requires current data update and pass data files initially, followed by data passing, remote execution and interactive execution in middleware and top-tier.

• History related data processing – It processes statistical data by using data passing in middleware and top-tier.

3 Three-tier Client/Server Database Design Methodology

A stepwise procedure of using middleware in 3-tier C/S database design is recommended. To illustrate its application, a case study of using middleware for project oriented data processing is made as an example, along with the methodology.

EXAMPLE 1:

In the case study, a large container shipyard had a centralized computer system in operation for several years. The system was working 24 hours a day, 365 days a year to provide the following functions:

• Roadside container movement in and out the shipyard

• Railway container movement in and out the shipyard

• Vessel Operation including container discharging from and loading to vessels

• Ship Planning

• Other subsystems

The existing system allowed users to capture container data at various business operation stage, to prepare vessel stowage plan, and to create crane working programme. The system also allowed users to view container inventory, ship stowage plan, roadside tractor queue, vessel operation report, railway scheduled jobs, shipyard map, and various statistical reports. A shipyard map display showed a 6 * 4 grids, with each grid stood for a container. Each screen represented a cross-section view of a particular row inside a block of containers.

Ship planners were responsible for planning containers discharging from and loading into a bay of a vessel. The container stacking criteria inside a vessel was determined by port of destination, the weight of the container, and the classification of cargo category. A bad stowage plan could cause unrecoverable sea disaster. To create a stowage plan, the ship planner required details on containers, shipyard location, and the bay structure of the ship. In order to optimize physical shipyard operation, the user used 2 terminals to acknowledge any change to shipyard position, or any additional containers. The process was time consuming, demanding highly skillful, and conscious staff.

An initial proposal was to revamp the system by replacing the vessel planning system with directly connected graphical terminals. The study team acknowledged this was high risk due to the complexity of the existing system, and the processing power of the existing system to support the number of graphical terminals. An alternative proposal is required.

Step 1 Feasibility Study

In a C/S system, the majority of application logics dealing with user interface are developed in the client workstation, while all shared resources activities such as printer or database access are to be dealt at the servers. Data are distributed among workstations, and servers at each tier. The database can either be partitioned, non-replicated, replicated, or partitioned with replication. Choice of approach can be reflected from the requirement of the application ---- the greater the flexibility, independence, and reliability, the more the cost, the integrity, and the security.

The way a user interface affects the allocation of programming logic among clients and upper-tier servers could also affect the distribution of data.

Whilst most 2-tier C/S run on one network operator system, 3-tier C/S usually do not. Not all communication networks support connection oriented access, resulting in restricted processing techniques (e.g. batch file transfer). Data concurrency control and transaction management are also critical issues for a distributed database system [12].

The result of the feasibility study should be able to justify the necessity of a 3-tier architecture by identifying the role of a client, the middleware server, and the top-tier server; how they could interact, and how much benefits can they gain from the implementation. The choice of which processing technique is dependent on the nature of the application, type of databases being involved, and the underlying communication network. For the nature of decision support system application, history related data processing is appropriate. For the project-oriented application, project-oriented data processing is a suitable choice. Otherwise, OLTP is adopted (refer to Table 3).

Table 3
Data processing in C/S system

Processing technique	Kinds of data being processed
On-line Transaction Processing	Reflects the current value of data which affects the business operation of the company and may use all three techniques in table 2 to maintain data consistency among all tiers.
Project-oriented data	Handles data which require instant value during the execution of a project, and is only interesting for a particular group of people inside the company. It requires the use of Data Passing-file transfer technique at project initialization, subsequently use either of the three techniques in table 2 to maintain up-to-date data information in all tiers.
History related data processing	Used to provide statistics data so as to support management decisions. It can use Data Passing-file transfer technique to update data at specific period.

EXAMPLE 2:

In the case study, the user requirement is to design a new C/S database to support GUI front-end processing, and improve information content for ship planning process. To achieve this, each ship planning process requires a pair of large size high-resolution graphics, one for the shipyard map, the other one for the stowage plan (bay plan) of a vessel. A planned container should disappear from the shipyard map screen, and appear at the bay plan screen. A ship stowage planning activity can be seen as a project-oriented task. During planning, the containers are still in the shipyard, and are looked after by the main system. The ship planning information is autonomous to the ship planner, until the user finishes planning and transfers the data back to the main system. Once the vessel operation is completed, the planning data are no longer needed at the user’s workstations. A 3-tier client server architecture is proposed.

Step 2 Determine Functional Requirements

All system requirements must be collected in detail. Conflict requirements should be resolved. The output is a requirement specification.

EXAMPLE 3:

In the case study, the system requirements are:

• Provide a user-friendly front-end system at the clients for each ship-planning process, minimum supporting 5 concurrent planning processes.

• Specify the global schema and data in the main system, and at middleware servers.

• Specify the fragmentation schema for the ship planning unit.

• Allocate preferential local data for middleware server (e.g. dedicated server for certain kind of vessels).

Step 3 Conduct data analysis

Data processing is the prime objective in application development. Data items can be described in semantics diagram by grouping them into records, and relations, forming an Entity Relational model. The analysis should reflect data volume, access rate and timing, retention period, import and export requirement. Data used for on-line real-time update should be different from on-line inquiry, which should be separated from data used for historical information [8]. Data only used for client user interface should not be replicated at top-tier server, although they may be duplicated at the middleware server in some circumstances since each client sends its requests to one top-tier through middleware, but a top-tier can serve more than one middleware.

EXAMPLE 4:

Figure 3 shows part of the E-R model for the proposed ship stowage planning system.

Figure 3
- Part of the E-R Model of Ship Planning System

Step 4 Perform functional analysis

Functional analysis can be achieved with the help of Data Flow Diagrams. In a three tier C/S, implicit functions should be incorporated to reflect access between tiers. Inconsistency found in the functional analysis and data analysis should be identified by now. Adjustment to data or functions can be made to align the two steps.

EXAMPLE 5

In the case study, figure 4 is part of the DFD of the ship stowage planning C/S system where the middleware server processes initial vessel data requests from a client workstation. If vessel data does not exist, the request is passed to the top-tier server to extract required vessel data, as well as the shipyard map containing the selected vessel containers. The middleware server identifies which workstation is dealing with the shipyard map, or which one is dealing with the bay plan. The data are transmitted to client workstations to prepare ship planning. The output plan is passed back to the middleware server to validate the stability. Valid plans are uploaded to the top-tier server for preparation of crane-working programme. At the end of ship stowage planning, data is uploaded to the top-tier server for planning the crane working programme.xxxxxxxxxxxx

Figure 4
- Processes of Ship Planning System

Step 5 Adentify Processing Technique

In a 3-tier C/S environment, not all systems are capable to implement interactive execution, partly due to incompatible databases, and partly due to the underlying communication network, and most important, it is the application to dictate the processing technique. The objective of selecting a particular processing technique is to allow applications to run remote data efficiently, while maintaining data integrity. Usage of data in a 3 tier C/S is categorized in Table 3.xxxxxxxxxxxxxxxxxx

EXAMPLE 6

In the case study, the Ship Planning Department is a separate unit, and the data is project-oriented; the technique used is Data Passing. When a planner initiates a ship planning for a vessel, all containers for the selected vessel are retrieved from the top-tier server, downloaded to the Middleware using file transfer technique. Subsequent changes of container information at the top-tier server are transmitted to the Middleware server using Data Passing technique. At the end of ship stowage planning, data is uploaded to the top-tier server for planning the crane working programme.

Step 6 Data distribution in a 3-tier Client Server

The objective of data distribution in a 3-tier C/S is to provide efficient processing of data by locating the data close to the source of processing, while maintaining sufficient data integrity. In general, data locality in a 3-tier C/S should reflect a functional split of a business unit within an organization, or an autonomous project within the organization. Refine the ER diagram according to the data distribution. The data allocation in a 3-tier C/S system is shown in Table 4.xxxxxxxxxxxxxx

Table 4
Data allocation in 3-tier C/S system

Data allocation	Description
Centralized data	All data access are referring to central server(s) at the top-tier, the middleware server(s) serves as a communication server(s), with limited application logic. The client performs the function of formatting data into desired user interface. The advantage of this approach can provide excellent data integrity. However, it requires justification against a 2-tier C/S approach which is less complicated, and less costly.
Distributed data among servers	Data are distributed among the top-tier servers and the middleware servers. The client performs the functions of formatting data into desired user interface. The database at the mid-way server could be different from the top-tier, allowing the accommodation of different database platforms. The aim of this approach is to reduce database access-time and to provide flexibility in large volume data environment. It can also match with business organization structure, where autonomous data by individual business unit is required, with the top-tier database providing referential data, and maintaining data history. In order to maintain data integrity, remote execution or interactive execution techniques are used between the middleware server and the top-tier servers, and is transparent to clients.
Distributed data among servers and clients	Data are distributed among clients and servers. The databases at each platform can be different to satisfy specific requirement. This is best for autonomous business unit’s data resident at the middleware server, with the top-tier server provides reference and any up-to-date information. The client workstations contains static data. The middleware server keeps update data for all client workstations, allowing them to process inter-related information. Another usage is for project-oriented task where data is initially extracted from the top-tier server into the "project server" in the middle-tier. Subsequent update at the top tier will invoke remote execution at (or passing data to) the middleware server, which contains static data, or data required for pair-wise client process (i.e. a user needs at least 2 workstations to process information). The client-stations maintain instance of data and perform GUI activities. Inter-client related data are passed to the middleware server. Data is passing to top-tier by request, or at the completion of a project using data passing technique.
Virtual database in heterogeneous environment	Its 3-tier architecture provides the ability to create a "virtual database" that ties together multiple different databases. The benefits of this functions is a virtual database that contains data from geographically distributed databases can be created. Since the virtual database effectively hides the differences in accessing data in multiple databases, it enables users and application programmers to perceive heterogeneous data source as a single homogeneous data source. Users and applications don’t have to track the details and locations of different databases. Instead, they only access their virtual database, which tracks all of this information for them.

EXAMPLES 7 AND 8

In the case study, partition of data is split by business unit function as shown in Table 5.

Table 5
Partition of data by tier

Tier	Data
Top-tier Server	Relation Yardmap ( yd_blk#) Relation Yardloc ( yd_blk#, container#, yd_stack, yd_row #, yd_tier#) Relation Bayplan (company_code, vessel_code, voyage#, bay#) Relation Bayloc ( bay#, container#, row#, tier#) Relation Container (company_code, vessel_code, voyage#, bay#, container#, size , yd_blk#, yd_stack, yd_tier#, weight, IMCO)
Middleware Server	Vessel_list (company_code, vessel_code, vessel_name) Vessel_structure (company_code, vessel_code, bay#, ondeck_underdeck, position) Vessel_bay_plan 1 (company_code, vessel_code, bay#, row#, tier#) temporary Active_vessel_containers 2 (vessel_code, voyage#, container#, size, yd_blk#, yd_stack, yd_row#, yd_tier#, weight, IMCO, bay#,row#,tier#) Active_Vessel_Status (vessel_code, voyage#, status, yard_map_workstation#, bay_plan_workstation#
Client Work-station	either Active_Vessel_Bay_Plan (company_code, vessel_code, voayage#, bay#, row#, tier#, container#, weight, IMCO, bay_operation_sequence) or Active_Yard_Map_List (yd_blk#, yd_stack, yd_row,yd_tier#, container#, vessel_code, voyage#, weight, IMCO, bay_plan_status)

Step 7 Application Logic Distribution in 3-tier C/S system

Similar to data distribution, application logic of a 3-tier C/S system can be distributed among the processors at each tier. Requests for DBMS access and response to requests are sent over the communication network using the processing techniques as described before. The main objective is to minimize data traffic over the communication network. The programming logic distribution between client and server are shown in Table 6 (based on [9]) and Table 7. Table 7 also shows the programming logic for each tier.'

Table 6
Logic distribution and its processing technique in C/S system

Tier	Logic distribution	Processing technique
Client	-screen input/output facilities such as editing or formatting -maintain low-interaction and quasi-static data -manipulate local data -process application logic -receive result from middle-tier server -format and present results -computation/condition	Application Program Interface should include proper communication software to talk to the mid-way server. Derived data should be processed close to client for user interface.
Middleware	(to client): -accept request from client - process database request - database integrity checking - maintain database overhead data - maintain fragments of business and database processing logic - concurrent access control - perform recovery - optimize query process - back-end DBMS processing - format/transmit data to client - format/transmit data to top-tier server. (to top-tier server): - receive data from top-tier server - process database update request - database integrity checking	For data which requires current update at the top-tier server, use data passing, or remote execution, or interactive execution technique to transmit update request to top-tier server. For project base data: - request initial download data from top-tier server - receive data from top-tier server using file transfer - set up project information for client process - at end of the project, transmit the whole project data to top-tier server using file transfer technique - freeze project data - purge project data at desirable period. Local autonomous non-derivable data should be processed close to the middleware server for performance reason. Triggers in procedures are executed automatically when certain event happens, e.g. database integrity check. Contains program routines for clients call up. Where possible, two phase commit [14] should be part of integrity enforcement command to ensure database synchronization.
Top-tier Server	- accept request from middleware server - process database request - database integrity checking - maintain database overhead data - maintain majority of the business processing logic - concurrent access control - perform recovery - optimize query process - back-end DBMS processing - format/transmit data to middleware server	For data which requires immediate notification to middleware server, use data passing, or remote execution, or interactive execution technique to transmit update request to middleware server. For project base data: - upon receiving data download request, transmit data to middleware server using file transfer technique. - maintain project status - upon end of project notification , receive project data from middleware server - set project status to ‘finish’. Global non-derivable data should be processed at the top-tier server. Triggers in procedures are executed automatically when certain event happens, e.g. database integrity check. Where possible, two phase commit should be part of integrity enforcement command to ensure database synchronization.

Table 7
Programming logic by tiers

Logic	Description
Client	- perform all input/output for ship planning activities. - initiate vessel data request - complete vessel data report - update changes of container info as sent by middleware server - create/update static vessel structure, and bay plan; pass data to update middleware server - submit backup request to middleware server - the GUI front end processing screen at each client station is shown in Figure 5 and Figure 6(note that every user requires 2 workstations to perform ship stowage planning duty)
Middleware	- receive vessel structure/bay plan data from client station, update database - submit request of initial ship stowage planning data download to top-tier server - receive initial download - identify client responsibility code(yard map or bay plan) - transmit static bay plan, and initial dowload containers to client workstations - receive changes of selected vessel container information from top-tier - transmit update container information to relevant client workstation - receive completed active bay plan from client workstation - validate active bay plans by calculating vessel stability - transmit active bay plan to top-tier server - receive deletion status of completed vessel operation from top-tier server - perform housekeeping routine (delete stowage planning data related to selected vessel) - backup client data on request
Top tier Server	- receive initial ship stowage planning data download request - transmit selected vessel containers to middleware server - set voyage_schedule project status to active - check changes made to active vessel containers, transmit them to middleware server - receive active bay plan from middleware server - set vessel status to planned - transmit deletion flag of completed vessel operation to middleware server - execute other system functions (unchanged)

Figure 6 -
Client Workstation 2 - Bay Plan Information

Step 8 Design Logical Database

Map the E-R diagram to the DBMS platform. Normalize the resulting relations (or sets) as a basis for logical database. Incorporate performance tuning if deemed necessary.

EXAMPLE 9:

After mapping the E-R model to global schema in the form of relations for middleware server and the client workstation, there are no redundancy in data attributes in tables. The tables can be used for logical database design. The data distribution has simplified the data integrity check between different tiers.

Step 9 Program Design

In a 3-tier C/S environment, programs should be developed by means of various tools. Client workstations should use their own data, or data available at the middleware server. Data required outside the middleware server should be restricted, or only a limited amount of data should be transmitted. When using interactive execution technique, programs should be divided into small programs, allowing fast recovery and error tracing, and should be stored in the tiers, as shown in Table 8.xxxxxxx

Table 8
Program storage in 3 tier C/S system

Program	Storage
OLTP programs	Should be sharable across 3-tier C/S by duplicate stored the programs at the top-tier server and the middleware server.
Project-oriented programs	Should be mainly stored in the middleware server for clients workstations sharable access.
History related programs	Should be stored at the top-tier server, with the graphical user interface programs reside in the client workstations

EXAMPLE 10:

In the case study, the ship planning activities are now processed at a pair of client workstations. With the GUI, application activities are carried out at the client workstation. The middleware server provides communication link between the clients and the top-tier server, while maintaining a backup copy of client data on request. Changes to container information at top-tier level will cause an update at the client through the middleware server. Table 9 shows the stored procedure for the top-tier server that obtains stowage planning data, transmits them to middleware server and sets project status to active.

Table 9
Stored procedure for top-tier:

Step 10 Physical database design

This step performs the database tuning operations such as indexing, clustering, database integrity rules, and physical allocation of data. It is in particular important at the middleware server.

EXAMPLE 11:

In the case study, in order to speed up the processing of searching active vessel containers, the physical database design of the main system introduces indices on the following keys: vessel, voyage, container_id, bay_location.

4 Conclusions

Middleware servers can be applied as a system solution in heterogeneous database environments, to serve as database gateways for virtual database connectivity. They can also be applied in meeting the data requirements in business units as temporary storage for better performance. The discussion in this paper has proposed a methodology to develop a 3-tier client/server application with enhanced graphic capability, to be applied to an existing system. The stepwise procedure suggests a standard to follow in developing a 3-tier C/S. The key task is to identify the nature of the application, the business entity and the existing heterogeneous databases environment. A case study is used to indicate how to reduce the complicated 3-tier C/S to a manageable environment. The approach does not restrict the use of a DBMS platform, allowing various data models to operate in the same C/S system. In addition, the use of the data passing technique does not affect the performance of client or the middleware server, while other tiers may be heavily involved in other activities. The practical value of the methodology is to identify the functions of middleware in developing a 3-tier C/S database system. Future research will be dedicated to using a 3-tier C/S system to implement a data warehouse system.

• [1] L. Barnes and D. Shimberg, Client/Server & Beyond: Strategies for the 21^st Century, Prentice Hall, 1997, pp. 211-213.
• [2] A. Berson, Client/Server Architecture, McGraw-Hill Inc., 1994, pp. 39-41,
• [3] C. Bontempo and C. Saracco, Database Management: Principles and Products, Prentice Hall, ISBN 0-13-380189-6, 1996, pp. 152-154.
• [4] S. Ceri and Pelagatti, Distributed Database Principles & Systems. McGraw-Hill Inc.1995, pp. 84-85.
• [5] B. Choy, A methodology for 3-tier architecture for client-Server System, Project prototype report of Department of Computer Science, City University of Hong Kong, 1997, pp. 1-107.
• [6] L. Duggan, Is two phase commit enough, Hong Kong Computer Journal, 1993, pp. 29-31.
• [7] J. Fong, and R. Hui, Three Tier Client/Server Database Design: A Practical Approach to in- corporate GUI into existing DBMS, Proceedings of 8^th International Database Workshop, Springer Verlag, ISBN 981-3083-54-9, 1997, pp. 317-331.
• [8] W. H. Inmon, Developing Client/Server Application in an Architectural Environment. QED Technical Publishing Group. 1991, pp. 73-92.
• [9] D. Kroenke, Database Processing Fundamentals, Design, Implementation, Macmillan Publishing Company, 1993, pp. 513-544.
• [10] D. Linthicum, David Linthicum’s Guide to Client/Server and Intranet Development, John Wiley & Sons, Inc., 1997. pp. 44-46.
• [11] J. Martin, and J. Leben, Client/Server Databases Enterprise Computing, Prentice Hall, 1995, pp. 97-99.
• [12] B. Siu, and J. Fong, Strategy for Adopting Client/Server Technology: Experience in the Hong Kong Setting, Proceedings of Database/Client-Server Conference China ’96, 1996, pp. 55-70.
• [13] Sybase, Sybase OmniConnect Gateway. 1994, Sybase, Inc.
• [14] T. Teorey, Distributed Database Design: A Practical Approach and example, ACM SIGMOD RECORD 18(4): 23-39, 1989

1

Move from top-tier server

2

intermediate storage from top-tier server to be download to client, or the completion of ship planning for selected vessel

Publication Dates

• Publication in this collection
  16 Dec 1999
• Date of issue
  July 1999