10 Client-Server Architecture: Inventory Tracking 10.1. Analysis

Building most business applications requires solving a whole complex of problems concerning data storage, concurrent access to that data, its integrity and its protection. Off-the-shelf database management systems (DBMS) are usually employed for this purpose.

Of course, any DBMS requires adaptation to the conditions of a particular enterprise, and organizations often split this work into two tasks: data design is assigned to database specialists, while the software support for carrying out transactions is assigned to programmers. Implementing such an approach, which of course has its advantages, involves solving a number of serious problems. It must be frankly admitted that there are serious differences between the work of database developers and that of programmers, differences determined by their technology and their development skills. Database designers usually describe the problem domain in terms of "long-lived" monolithic tables of information, whereas programmers are used to perceiving the world in terms of flows of control.

If these two approaches cannot be reconciled within a single project, then achieving integrity in the design solution for a system of any appreciable complexity will be practically impossible. For a system in which data is the main thing, we must achieve a balance between the database and the application. A database developed without regard for how it will subsequently be used generally turns out to be clumsy and inefficient. An application developed in isolation, in turn, may impose impossible demands on the database, which leads to serious problems in maintaining the integrity of the information.

Not so long ago business applications ran on mainframes, which erected almost insurmountable barriers between the ordinary employee and the information he needed. However, with the advent of the personal computer the situation changed dramatically: affordable tools for processing and storing data, together with computer networks, made it possible to connect computers not only within an office but also between enterprises separated from one another by thousands of kilometers. One of the principal factors that contributed to this change was the introduction of the client-server architecture. As Mimno notes, "The sharp shift to client-server architectures based on personal computers was driven above all by business requirements. Faced with increased competition and an accelerated cycle of new product releases, there arose a need for faster time to market, an increase in the volume of services provided to customers, more responsive tracking of market development trends, and an overall reduction in costs" . In this chapter we will examine an example of a management information system (MIS) and show how object-oriented technology offers a unified concept for organizing a database and developing the corresponding application for a client-server architecture.

10.1. Analysis

Defining the Boundaries of the Problem

The requirements for the inventory tracking system are shown in the sidebar. This is a fairly complex software system, touching on every aspect connected with the movement of goods into and out of the warehouse. A real, physical warehouse serves to store the products, of course, but it is precisely the program that is its soul, without which it would lose its function as an efficient distribution center.

In developing such a system, the customers must partly rethink the entire business process and take into account the programs already in place so as not to lose the funds invested in them (see Chapter 7). And although some improvement in the productivity of the company's operations may be achieved simply by automating the existing "manual" system of tracking goods, a radical improvement can be attained only through a fundamental reconsideration of how the business is conducted. Questions of reengineering are bound up with systems planning and lie beyond the scope of our book. However, just as the architecture of a system determines its implementation, the overall vision of the business determines the entire system. Proceeding from this premise, we should begin by considering the overall plan for conducting inventory tracking. Based on the results of systems analysis, seven principal functions of the system can be identified:

It is not surprising that the system architecture will reflect the functional properties listed above. Figure 10-1 shows a diagram illustrating the makeup of the computing elements of the network. This structure is typical of most management information systems: groups of personal computers transmit information to a central database server, which serves as the central repository for all data essential to the enterprise.

Let us consider some details of the network structure. First, although we see in the figure that each computer belongs to only one functional group, this does not mean that other operations cannot be performed on it: the accounting department must, for example, be able to issue general queries to the database, and the purchasing department, in its turn, must be able to view accounting information concerning payments to suppliers. Furthermore, the company's management may redistribute computing resources among the firm's departments according to its own considerations. Information security requirements, however, impose certain restrictions on access to it: a stock clerk, for example, must not be able to send out payment documents. Control over access to data is usually implemented by means of security mechanisms common to the whole system.

Figure 10-1. The inventory tracking system network.

We assume the existence of a local area network (LAN) linking all the computers and supporting the operation of common mechanisms for user interaction: electronic mail, shared access to directories, output of information to a network printer, and communications. For our inventory tracking system the choice of network operating environment is not all that important, so long as it reliably and efficiently supports interaction among users.

The presence of portable personal computers in our scheme reflects the possibilities of advanced wireless communication technologies. The plan is to equip the stock clerks with such devices. As new goods arrive at the warehouse, they will promptly enter into the computer information about their quantity and location in the warehouse and transmit it directly to the server. When goods must be shipped from the warehouse, information about their quantity and location will be reported to the stock clerk, who will pass it on to the handlers.

The technological means mentioned are hardly comparable to space stations - all the hardware is standard. As for the software, we hope that a significant part of it will likewise be assembled from standard components. For solving many local subproblems it is advantageous to purchase off-the-shelf spreadsheets, accounting packages, and groupware. The main driving force of the system, however, must be the inventory tracking program that binds all its constituent parts into a single whole.

In applications of this kind, computation as such occupies very little space. The main task consists in providing storage, access and transfer of large volumes of data. Thus, most of the architectural decisions will be aimed at working with declarative information (which goods are present in the warehouse, what lies behind their designations, where they are located) rather than with procedural questions (in what manner the goods are moved). The development of the design will be based on the fundamental principle of the object-oriented approach: identifying the key abstractions that form the vocabulary of the problem domain, and the mechanisms that operate upon those abstractions.

The business process places an important condition on our system: it must be open to further modification. In the course of analysis we must identify the key abstractions that currently play an important role in the firm's activity: determine the types stored in the database; enumerate the reports that must be generated; learn to process queries and to carry out all the other transactions necessary in the company's operations. Currently, precisely because business is subject to constant change, the firm is continually looking for new areas in which to invest capital, and the information system must be easy to restructure in accordance with the modernization of the firm's strategy and/or its entry into new markets. An outdated software system may become the cause of business failures and lead to the unproductive expenditure of human resources. Thus, in designing the inventory tracking system we must provide for the possibility of introducing subsequent changes into it. Experience shows that the following elements of a program are the most subject to change:

• the kinds of data stored;
• the hardware of the system.

Each of the warehouses changes over time the assortment of goods it stores, begins to work with new customers and suppliers, sometimes losing old ones in the process. It may unexpectedly turn out that additional information about customers, not provided for by the system, must be stored [Consider, for example, the consequences of the introduction of new technologies that deliver interactive television services to every household. It may be supposed that in the future buyers will be able to place orders electronically and pay for them over the network as well. Since standards in these areas change almost daily, depending on which companies occupy the leading positions, it is impossible for a developer of end-user applications to predict exactly the protocol for interacting with such a system. The best we can undertake as system architects is to make sensible assumptions and encapsulate them in our system, so that we can adapt when the dust settles from the battle for dominance over the information highways - a battle in which the developer of an individual application means no more than a pawn. This circumstance brings us to the chief argument in favor of object-oriented technology: as we have already seen, the object-oriented approach makes it possible to create flexibly adaptable architectures, which is essential for survival in the marketplace]. Moreover, hardware technologies are still developing faster than software ones, and computers become obsolete within a few years. No one, however, is in a position to change large and complex software systems frequently; it is neither rational nor permissible, because the time and cost of building a new system often exceed the time and cost of buying and installing new computers. By introducing a new system merely for the reason that the old one looks outdated, you risk your business: stability and reliability of operation are a necessary property of software that must support the daily activity of a firm.

One conclusion, then, is that over time we can expect the user interface to change. In the past business applications had an ordinary text interface, and this was considered normal. However, the general decline in the price of computers and the wide spread of graphical user interfaces make the introduction of graphical applications necessary. It must be remembered that for an inventory tracking system the user interface is only a small (though important) part. The core of the system is the database; the user interface may be regarded as a shell around this core. For this system it is possible (and even desirable) to create several interfaces. A simple menu-based one for customers filling out requests for goods. A modern, Windows-style one for solving accounting tasks and also for planning and purchasing. Reports may be printed in batch mode, but some managers may need facilities for viewing charts on screen. The stock clerk needs a simple interface: windows and a mouse are not suited to work in factory conditions. We will not dwell in detail on questions connected with the user interface; practically any of the existing interfaces may be implemented in the inventory tracking system, and this will not affect its architecture.

Before discussing the problem further, let us note two important things. First, the development will use a standard relational database management system (RDBMS), around which the software application is built. Undertaking to create our own DBMS in this situation is simply pointless; we would have to implement most of the basic properties of a standard database, which would sharply increase costs, while the resulting product would prove to be functionally far less flexible. An advantage of a standard relational DBMS also lies in its relative portability. Most of the widely used databases have been adapted to various platforms, from personal computers to mainframes. Second, as is evident from Figure 10-1, we want the inventory tracking system to operate in a distributed computer network. We plan to place the entire database on one computer, to which all the computers on the network will have access. Such a scheme also realizes the client-server architecture; the computer on which the database is installed is the server. Several clients connect to it. The particular computer at which a user works is of no significance to the server. Thus, our application must run on any computer in the network, and the introduction of new hardware technologies will have minimal influence on the functioning of the system.

Client-Server Architecture

Although this section is not a detailed survey of client-server architecture, some remarks on the subject must be made, since they bear directly on the choice of architectural decisions for our system.

What may be assigned to the client-server category and what may not is still the subject of heated discussion [Just as is the question of what may be considered object-oriented and what may not]. In our case it will suffice to define client-server solutions as "a decentralized architecture that enables end users to gain guaranteed access to information in a heterogeneous hardware and software environment. Client-server applications combine the client's graphical user interface with a relational database located on the server" . The structure of such applications implies the possibility of collaborative work by users; at the same time, responsibility for performing one function or another falls upon various elements of an open distributed environment that are independent of one another. Berson further asserts that a client-server application can usually be divided into four components:

One of the principal questions in designing a system architecture consists in the optimal distribution of processing nodes across the network. Decision making here is complicated by the fact that the tools and standards for client-server architectures are being updated with staggering speed. The architect must come to grips, for example, with POSIX (Portable Operating System Interface), OSI (Open Systems Interconnection), CORBA (Common Object Request Broker), the object-oriented extension of the SQL language (SQL3), and a number of vendor-specific solutions such as Microsoft's OLE (Object Linking and Embedding) [It is for precisely this reason that good information system architects receive either enormous sums of money for their mastery or a great deal of pleasure from the very process of assembling many disparate technologies into one coherent whole].

But it is not only the abundance of standards that influences architectural decisions. Such questions as data security, system performance and its size also matter. Berson offers the architect several basic rules for designing client-server applications:

• The presentation logic component is usually installed in the same place as the input-output terminal, that is, on the end user's computer.
• Given the increased power of workstations, and also the fact that the presentation logic is installed on the client machine, it makes sense to place some part of the business logic there as well.
• If the database access mechanisms are bound up with the business logic, and if the clients support some low-level interaction and quasi-static data, then the database access mechanisms may also be placed on the client side.
• Taking into account the fact that network users are usually organized into workgroups, and that a workgroup shares a database, those fragments of the business logic and of the database access mechanisms that are common, as well as the DBMS itself, must reside on the server .

If we manage to choose the correct architectural decisions and successfully implement their tactical details, the client-server model will give the system a whole series of advantages. Berson particularly singles out that client-server architecture:

• Permits more effective use of new computer automation technologies.
• Permits data processing to be moved closer to the client, which reduces the load on the network and shortens the duration of transactions.
• Facilitates the use of graphical user interfaces, which have become available on powerful modern workstations.
• Facilitates the transition to open systems . The following points of risk must, however, be singled out:
• If a significant part of the application's logic ends up being placed on the server, the latter may become a bottleneck of the system, slowing down the users' work (as often happened when mainframes were used in a master-slave architecture).
• Distributed applications... are more complex than non-distributed ones .

We will reduce this risk by using an object-oriented approach to development.

Scenarios of Operation

Now that we have formed a picture of the system as a whole, let us continue our analysis and examine several scenarios of its operation. First let us enumerate a number of principal modes of use:

• A customer telephones a remote telemarketing organization in order to place an order.
• A customer sends an order by mail.
• A customer calls to learn the status of his order.
• A customer calls to add or remove certain items from an order.
• A stock clerk receives instructions to ship the required quantity of goods to a customer.
• The shipping service receives the goods ordered by the customer from the warehouse and prepares them for dispatch.
• Accounting prepares an invoice for the customer.
• The purchasing department prepares an order for new goods.
• The purchasing department adds or removes a supplier's name from the list.
• The purchasing department queries a supplier about the status of an order.
• The receiving department accepts a shipment from a supplier and checks its correspondence to the order.
• A stock clerk enters a new item into the list.
• Accounting notes the arrival of a new item.
• The planning department generates a report on sales figures for various types of products.
• The planning department generates a report for the tax authorities indicating the quantity of goods in the warehouses.

Each of the primary scenarios may include a number of secondary ones:

• The goods ordered by the customer are not in the warehouse.
• The customer's order is incorrectly filled out, or contains nonexistent or obsolete product identifiers.
• A customer calls to check the status of an order but does not remember exactly what was ordered, by whom, and when.
• A stock clerk has received a packing order, but some of the goods listed in it could not be found.
• The shipping service receives the goods ordered by the customer, but they do not correspond to the order.
• The customer has not paid the invoice.
• The purchasing department places a new order, but the supplier has either gone out of business or no longer supplies the type of goods ordered.
• The receiving department accepts a shipment that does not fully correspond to the order.
• A stock clerk wants to place a new item in the warehouse, but it turns out that there is no room for it.
• The tax codes change, which compels the planning department to draw up a new inventory list of the goods held in the warehouse.

For a system of such complexity, dozens of primary scenarios and an even greater number of secondary ones will probably be identified. This stage of analysis may take several weeks, until a more or less thorough level of detail can be achieved [But remember analysis paralysis: if the analysis phase does not fit within the deadlines dictated by the business, then "abandon all hope, ye who enter here" - this business is not for you]. For this reason we strongly advise applying the eighty percent rule: do not wait until a complete list of all the scenarios has taken shape (no amount of time would suffice for that), study about 80% of the most interesting ones and, if possible, try at least to make a rough check of the correctness of the overall concept. In this chapter we will dwell in detail on two principal scenarios.

Figure 10-2 presents a scenario in which a buyer places his order with a telemarketing firm. Several different objects are involved in carrying out this system function. And although control is exercised by the interaction of the customer (aCustomer) with the agent (anAgent), there are other key objects as well, namely: the customer record (aCustomerRecord), the product database (inventoryDatabase) and the packing order (aPackingOrder), which are abstractions of the inventory tracking system. This list of abstractions is formed precisely at the stage of examining the scenarios of operation.

Figure 10-2. The order scenario.

Figure 10-3. The order fulfillment scenario.

Figure 10-3 reflects the continuation of this scenario. It presents the scheme of interaction between the stock clerk and the packing order. We see that here the stock clerk is the principal figure. He interacts with other objects, for example with shipping, which was not present in the previous scenario. However, most of the objects appearing in Figure 10-3 are also present in Figure 10-2, although they play different roles in these scenarios. For example, in the scenario of interaction with the customer we create an order (anOrder) as a document in which the customer's requirements are reflected. In the warehouse scenario that same order is executed.

In composing each such scenario we must constantly ask ourselves a number of questions. Which object will bear responsibility for performing this or that action? How will an object carry out this or that operation: on its own or by using the properties of another object? Are too many operations being charged to the range of responsibilities of a given object? What will happen if an error occurs in the course of the scenario (which postconditions may be violated)? What will happen if some preconditions are violated?

Engaging in this sort of anthropomorphism for each functional property of the system, we will discover in it a whole series of interesting high-level objects. First let us enumerate the persons who interact with the system:

• Customer - customer
• Supplier - supplier
• OrderAgent - sales department employee
• Accountant - accountant
• ShippingAgent - shipping department employee
• Stockperson - stock clerk
• PurchasingAgent - purchasing department employee
• ReceivingAgent - receiving department employee
• Planner - planning department employee

It is very important for us to identify these categories of persons: each of them corresponds to its own separate role in the scenarios. If we want to track when and why certain events occurred within the system and who caused them, then it is necessary to formalize the roles of all users. For example, in considering a complaint we may have to determine who conducted the negotiations with the dissatisfied customer. In addition, we will need this classification in developing the mechanism for restricting access to various parts of the system for various groups of users. In an open system centralized control is entirely effective and unavoidable: it reduces the risk of accidental or deliberate misuse.

As a result of the analysis a number of key abstractions were identified, each of which represents a particular type of information in the system:

• CustomerRecord - customer information
• ProductRecord - product information
• SupplierRecord - supplier information
• Order - order from a customer
• PurchaseOrder - order to a supplier
• Invoice - invoice
• PackingOrder - packing order
• StockingOrder - stocking order
• ShippingLabel - shipping document

The classes CustomerRecord, ProductRecord and SupplierRecord are associated, respectively, with the abstractions Customer, Product and Supplier. We have, however, separated these two types of abstractions, since they will play somewhat different roles.

Note that there are two kinds of invoices: those that are sent by the company to customers for payment of the goods ordered, and those that the company receives from the suppliers of goods. Although they do not differ in any way in their structure, they nevertheless play entirely different roles in the system.

The classes PackingOrder and StockingOrder will require some additional explanation. In accordance with the first two scenarios, after the sales department employee (OrderAgent) accepts an order (order) from the customer (Customer), he must give instructions to the stock clerk (StockPerson) to release the goods ordered. In our system the corresponding transaction is associated with an object of the class PackingOrder (packing order). This class is responsible for gathering all the information concerning the issuing of a packing order for the given order. At the operational level this means that our system forms and then transmits the order to the portable computer of one of the stock clerks who is free at that moment. Such information must, at a minimum, include the identification number of the order and the name and quantity of each of the goods. It is not hard to guess how this scenario could be improved considerably: our system is in a position to transmit to the stock clerk the location of the goods and, possibly, even the approximate sequence for removing them from the warehouse that ensures the maximum efficiency of this operation [Of course, in the general case this is the well-known traveling salesman problem, which as is known is NP-complete. However, the problem can be substantially constrained so that acceptable solutions are obtained. In fact, the rules of transport may prescribe a certain partial ordering: load heavy freight first, then light. It is also desirable to group freight by type: trousers with shirts, hammers with nails, wheels with tires (we did warn that we are talking about a general-purpose tracking system!)]. Our system has enough information to provide assistance to a newly hired stock clerk - for example, to give him the ability to display on the screen of his portable computer a picture of the outward appearance of one item or another. Such support may also come in handy for an experienced stock clerk during a period when the assortment of goods is changing.

Figure 10-4. Key classes in taking and fulfilling an order.

Figure 10-4 contains a class diagram that reflects our understanding of the process of interaction of some of the abstractions listed above in the scenario of taking and fulfilling an order. We have supplemented this diagram with certain adornments for the attributes that play an important role in the functioning of each of the classes.

The principal motives for introducing precisely this structure of classes are connected with accounting for the traversal between instances of the classes. Having received an order, we would like, in particular, to form a marker designating the customer who placed the order; for this it is necessary to traverse from the instance of the order class (order) back to the customer (customer). Having received a packing order, we must return to the customer and to the sales department employee in order to convey information about the shipment; this means that we will need to traverse from the packing order to the order, and then from it to the customer and the sales department employee. As for the customer, it is desirable to know which goods he orders most often at one time of year or another. To carry out such a query it is necessary to return from the customer to all his previous orders.

It is worth dwelling in more detail on some further details of the diagram. Why does a 1:N (one-to-many) relationship exist between the class Order and the class PackingOrder? According to our business rules, each packing order can correspond to one and only one order. The converse, however, is not true. Suppose, for example, that certain items indicated in an order are currently not in the warehouse. Then we will have to ship them additionally under a second packing order, when the goods become available.

Let us note the constraint on the association between the objects StockPerson and PackingOrder: maintaining control over the quality of the work requires that a stock clerk service no more than one order at a time.

To conclude this stage of analysis, let us introduce two more key classes:

• Report - report
• Transaction - transaction

We have introduced the abstraction Report to designate a base class uniting all the various types of printed documents and user queries. A detailed analysis of all the scenarios may reveal many concrete types of documents, but, in view of the openness of our system, it will be better to work out a general mechanism for generating reports that allows new types of reports to be added without difficulty. Indeed, by singling out the properties common to all reports, we will be able to endow them with common behavior and structure, which will make it possible to give the corresponding elements of the system a standardized appearance and will make working with the system easier for the end user.

Our list is far from complete, but we have accumulated enough information to proceed to the development of the system architecture. Before that, however, it is necessary to consider certain principles that influence the organization of the data structures within the program.

Database Models

Date regards a database as "a repository for stored information. It is, as a rule, both integrated and shared at the same time. By 'integrated' is meant that the database may be thought of as a unification of several separate data files, redundancy of information among which has been partly or wholly eliminated... By 'shared' is meant that the information contained in the database may be used simultaneously by several users at once" . With centralized management of a database it is possible to "eliminate inconsistencies, establish standards, impose restrictions on access to information, and maintain the integrity of the database" .

Developing an efficient database is a difficult task, since many mutually contradictory requirements are placed upon it. The designer must take into account not only the functional requirements of the application, but also the speed and size of the database. Databases that are inefficient in speed turn out, as a rule, to be useless. Systems whose implementation requires filling an entire building with computers and hiring a crowd of administrators to support them are inefficient from the standpoint of cost.

There are many parallels between developing a database and creating an object-oriented application. Database design is often regarded as a process of iterative development, in the course of which decisions must be made concerning both the software logic and hardware aspects . Berkowski and Cool point out that "Objects that describe a database in the terms with which users and developers operate are called logical. Objects that reflect the physical arrangement of data in the system are called physical" [10]. In the course of design, which resembles object-oriented design, database developers constantly jump from considering logical objects to discussing the physical aspects of their implementation. In addition, the description of the elements of a database very much resembles the enumeration of the key abstractions of an object-oriented application. Database designers often use so-called entity-relationship diagrams for analysis. Class diagrams, as we have seen, may be organized in such a way that they will correspond directly to entity-relationship diagrams, while possessing still greater expressiveness.

Date asserts that in designing any database one must give an answer to the following question: "What data structures and corresponding operators must the system support?" [11]. The three different database models listed below give three different answers to this question:

• hierarchical;
• network;
• relational.

A fourth type has appeared recently, namely object-oriented databases (OODBMS). OODBMSs combine traditional database design technology with the object model. Applying such an approach has proved quite useful in such areas as computer-aided engineering (CAE) and computer-aided software engineering (CASE), where we have to manipulate significant volumes of data with diverse semantic content. For some applications object-oriented databases can yield a significant gain in speed compared with traditional relational databases. In particular, when there are a large number of relationships between tables, object-oriented databases can work considerably faster than relational ones. Moreover, an OODBMS guarantees a consistent, "seamless" integration of data and business rules. To achieve the same semantics, relational databases use a complex system of triggers, which are formed with the help of third- and fourth-generation programming languages - a model that can in no way be called clear and comprehensible.

However, for a number of reasons many companies consider that using a relational database in the context of an object-oriented architecture is less risky. Relational database technology is significantly more mature, it is implemented on a wide spectrum of different platforms, and it often offers a more complete set of facilities for security, version control and maintaining integrity. In addition, a company that has already invested a certain capital in personnel and in tools supporting the relational model simply cannot afford to change its whole working technology overnight.

The relational model is quite popular. Taking into account its wide dissemination, the broad set of software products supporting it, and also the fact that it satisfies the functional requirements of the inventory tracking system, we have chosen precisely this model. Thus, we have settled on a hybrid solution: building an object-oriented shell over a traditional relational database and taking advantage of both approaches. Let us briefly consider some of the basic principles of relational database design. Knowing them, we will better understand how to create an object-oriented shell.

The basic elements of a relational database are "tables, in which the columns represent things and their attributes, while the rows describe individual instances of the things... The model also implies the presence of operators for generating new tables on the basis of old ones: it is in precisely this way that users can manipulate data and obtain information from the database" [12].

Consider as an example the database of a warehouse of radio-electronic goods, in which resistors, capacitors and integrated circuits are stored. In accordance with the previous class diagram, each type of product possesses a unique identification number and a descriptive name. For example:

Products

We see a table with two columns, each of which represents a particular attribute. In this case the order in which the rows (columns) are arranged is not important; the number of rows is not limited, but each of them must be unique. The first column, productID, is the primary key, that is, it may be used for the unambiguous identification of a part.

Goods arrive from suppliers; the information about each of them must contain a unique supplier identifier, the name of the company, its address, and, possibly, a telephone number. Thus, the following table may be composed:

Suppliers

supplierID is the primary key in the sense that it can be used to identify a supplier unambiguously. Note that all the rows in this table are unique, yet two of them have the same supplier name.

Different suppliers offer different products at different prices, and so we can also organize a table of product costs. It contains the current price for each product/supplier combination:

Prices

There is no simple primary key in this table. For the unambiguous identification of rows we must use the combination of the keys productID and supplierID. A key formed from the values of several columns is called composite. Note that we have not included the names of parts and suppliers in this table - that would be redundant; this information can be found by the values of the fields productID and supplierID in the products and suppliers tables. The fields productID and supplierID are called foreign keys, since they represent the primary keys of other tables.

Figure 10-5 presents the class structure corresponding to these tables. Here, to designate records that make sense only in conjunction with records from other tables, we use an association with an attribute. The primary keys of the tables are enclosed in square brackets.

Figure 10-5. Association with attributes.

Further, we can check the state of the warehouse with the help of a table containing the quantity of all the products on hand:

Inventory

This table shows that the object-oriented representation of the system's data may differ from its representation in the database. In the scheme presented in Figure 10-4, quantity is an attribute of the class ProductRecord, whereas here, for the sake of performance, we have decided to place quantity in a separate table. The point is that, as a rule, the description of a product (description) is modified very rarely, while the quantity (quantity) changes constantly as goods are shipped from the warehouse and new consignments arrive at it. To optimize access to the quantity of a product it is more sensible to split it off into a separate table.

This implementation detail of the system, as follows from Figure 10-4, will not be visible to the clients of our application. The class ProductRecord creates the illusion that quantity is a part of it.

The most obvious and at the same time the most important goal of database design is to construct a scheme for arranging the data in which each fact is stored in one and only one place. In this way no duplication of information occurs, and the process of making changes to the database and maintaining its integrity (the consistency and correctness of the data) is simplified. Achieving this goal is not always easy (and it turns out that it is not always necessary either). Nevertheless, in our case this property will be highly desirable.

To achieve this goal (an important one, but not the only one [13]) a special theory of normalization was developed. Normalization is a property of a table; if a table satisfies certain conditions, then we say that it is in a normal form. There are several levels of normalization, each of which is based on the preceding one [14]:

A table in third normal form "contains the properties of the key, the whole key and nothing but the key" [15].

All the tables we have considered are in 3NF. There are still higher levels of normalization, connected mainly with multivalued facts, but in this case they are of no great significance for us.

In order to tie the object-oriented scheme and the relational model together, we sometimes have to violate the normalization of tables deliberately, introducing a certain redundancy into them. In doing so we will have to make special efforts to keep the redundant data synchronized, but in exchange we obtain the possibility of faster access to the data, which is more important to us.

SQL

When working with an object-oriented model, in which data and forms of behavior are joined together, the user may need to carry out a number of transactions with the tables. He may, for example, wish to add a new supplier to the database, remove certain goods from it, or change the quantity of a product on hand. There may also arise a need to make various selections from the database, for example to view a list of all the products from a particular supplier, or to obtain a list of goods whose quantity in the warehouse is insufficient or excessive from the standpoint of a criterion we have specified. Finally, it may be necessary to create an exhaustive report that estimates the cost of replenishing stock to a particular level using the least expensive suppliers. Transactions of this sort are present in almost every application that uses a relational database. A standard language has been developed for interacting with relational DBMSs - SQL (Structured Query Language). SQL can be used both in interactive mode and for programming.

The most important construct of the SQL language is the SELECT statement of the following form:

SELECT FROM WHERE

In order, for example, to obtain the codes of products whose stock in the warehouse is less than 100 units, one may write the following:

SELECT PRODUCTID, QUANTITY FROM INVENTORY WHERE QUANTITY < 100

More complex selections can also be created. For example, one in which the name of the product appears instead of its code:

SELECT NAME, QUANTITY
FROM INVENTORY, PRODUCTS
WHERE QUANTITY < 100 AND INVENTORY.PRODUCTID = PRODUCTS.PRODUCTID

In this statement there is a join that makes it possible, as it were, to combine several relations into one. This SELECT statement does not create a new table, but it returns a set of rows. A single selection may contain an arbitrarily large number of rows, and so we must have the means to access the information in each of them. For this purpose the concept of a cursor has been introduced into the SQL language; its meaning is similar to the iteration we spoke of in Chapter 3. One may, for example, define a cursor as follows:

DECLARE С CURSOR

FOR SELECT NAME, QUANTITY
FROM INVENTORY, PRODUCTS
WHERE QUANTITY < 100 AND INVENTORY.PRODUCTID = PRODUCTS.PRODUCTID

To open this selection, we write

OPEN C

The FETCH operator is used to read the records of the selection:

FETCH C INTO NAME, AMOUNT

And, finally, after the work is finished, we close the cursor;

CLOSE C

Instead of using a cursor one may take another path: create a virtual table containing the results of the selection. Such a virtual table is called a view. One may work with it as with a real table. Let us create, for example, a view containing the name of a product, the name of the supplier and the cost:

CREATE VIEW V (NAME, COMPANY, COST) AS
SELECT PRODUCTS.NAME, SUPPLIERS.COMPANY, PRICES.PRICE
FROM PRODUCTS, SUPPLIERS, PRICES
WHERE PRODUCTS.PRODUCTID = PRICES.PRODUCTID AND SUPPLIERS.SUPPLIERID = PRICES.SUPPLIERID

The use of views is preferable, since it makes it possible to create different views for different clients of the system. Since views may differ substantially from the low-level relations in the database, a certain degree of data independence is guaranteed. Users' access rights to information may be defined on the basis of virtual rather than real tables, thus making it possible to write secure transactions. Views differ somewhat from tables, if only in that relations in views cannot be updated directly.

In our system SQL queries will play the role of low-level abstractions. Users are unlikely to have any understanding of SQL, since this language is not part of the problem domain. We will use SQL in implementing the program. Only developers of tools for our system who are sufficiently versed in programming will be able to compose their own SQL statements. From ordinary mortals working with the system every day the query language will be hidden.

Consider the following problem: having received an order, we want to determine the name of the company that placed it. From the point of view of an SQL programmer, this is not a difficult task. However, in our case, where the main programming is done in C++, we would prefer to use the following expression:

currentOrder.customer().name()

From the point of view of the object-oriented approach, this expression calls the selector customer, which returns a reference to the customer, and then the selector name, which returns the customer's name. In fact this expression is evaluated by the following query:

SELECT NAME
FROM ORDERS, CUSTOMERS
WHERE ORDERS.CUSTOMERID = CURRENTORDER.CUSTOMERID
AND ORDERS.CUSTOMERID = CUSTOMERS.CUSTOMERID

By hiding the implementation details of this call from the client, we have concealed from him all the unpleasant peculiarities of working with SQL.

Mapping an object-oriented view of the world onto a relational one is conceptually clear, but it usually requires a rather tedious working-out of details [A large part of the advantage of object-oriented databases lies precisely in the fact that in them these tedious details are hidden from the developer. The mapping of classes onto tables is fairly easy to reduce to an algorithm, and so there is an alternative to an OODBMS: tools that automatically convert C++ class definitions into a relational schema and SQL code. Then, for example, if the application requests an attribute of a given object, the generated code creates the necessary SQL statements for a standard relational database, obtains the required data and delivers it to the client in a form consistent with the C++ interface]. As Rumbaugh remarks, "Joining an object model with a relational database is on the whole a fairly simple task, with the exception of questions connected with generalization" [16]. Rumbaugh also proposes some rules that should be taken into account when mapping classes and associations (including aggregation) onto tables:

• Each class maps onto one or several tables.
• Each many-to-many relationship maps onto a separate table.
• Each one-to-many relationship maps onto a separate table or is correlated with a foreign key [17].

He then proposes three alternative variants for mapping an inheritance hierarchy onto tables:

• The superclass and each of its subclasses map onto a table.
• The attributes of the superclass are replicated in each table (and each subclass maps onto a separate table).
• The attributes of all the subclasses are moved up to the level of the superclass (in this way we have one table for the entire inheritance hierarchy) [18].

There is nothing surprising in the fact that there are certain limitations on the use of SQL in a low-level implementation [A new standard, SQL3, was recently proposed, which contains object-oriented extensions. They substantially reduce the semantic differences between the object-oriented and the relational views of the world and eliminate many other limitations of SQL]. In particular, this language supports a limited set of data types, namely characters, fixed-length strings, integers, and real

продолжение следует...

Продолжение:

Часть 1 10 Client-Server Architecture: Inventory Tracking 10.1. Analysis
Часть 2 - 10 Client-Server Architecture: Inventory Tracking 10.1. Analysis