Phase I is the pre-design phase of the database project. In this report, we present the:

• END-USERS
• END-USER REQUIREMENTS
• SCOPE OF THE PROJECT
• SOFTWARE SELECTION CRITERIA
• SOFTWARE CHOICE (JUSTIFICATION)
• CURRENT PROBLEMS (with ABC Utility information systems)
• RECOMMENDATIONS

In the final report on Phase I the following topics will be added:

• DEVELOPMENT PLAN
• ESTIMATED COST
• STATIC PROTOTYPE

The end-users of the proposed database will be:

• OPERATION PERSONNEL (maintenance, planning, etc.)
• DESIGNER-DRAFTSMEN
• ENGINEERS

Potential users:

• ADMINISTRATION
• SAFETY PERSONNEL
• TRAINING PERSONNEL

Subject to availability and security controls the end-users will be able to retrieve, update and insert the following types of data:

• ALPHANUMERIC
• DRAWING
• DOCUMENT
• PICTURE
• VIDEO
• SOUND

 The database will keep track of selected:

• EQUIPMENT
• FACILITIES
• DRAWINGS

Each item tracked may have attributes of the above types. The user will be able to query the database based on any attribute(s), including associations. Ad hoc queries based on alphanumeric data will reduce to the familiar method of inputting field values on the graphical user interface (GUI) screen, or by clicking on a screen button. For more complex queries a superset of the standard SQL language that handles multimedia as well as user defined datatypes will be used. CI will design and implement a customized graphical user interface (GUI) for each type of user (operational, designer-draftsmen, etc.).

The prototype database will keep track of equipment, facilities, and drawing data, supplied to CI by ABC Utility staff. The database will be developed on an HP-9000 server running UX and PC clients running NT. The graphical user interface (GUI) will be PowerBuilder. The final interface screens will meet end-user approvals. The developed prototype will be tested and evaluated by the users before deployment to other servers and clients. The time frame of product delivery is contingent on the ability of ABC Utility staff to supply CI with the requested data. CI’s time estimate is two months from the date of requested data delivery. The deployment rate will be controlled by ABC Utility’s management.

1.The data model must support user-defined types (not just character, float, integer, and data types).

2. The data model must support hiearchial (complex nested or compound data).

3. Stored procedures must be encapsulated with the data. For example, a procedure to display a drawing or zoom in on a part must be encapsulated in the object that represents the drawing.

4. Support reuse of defined types through composition and inheritance. Composition means a user-defined type is made up of other user-defined types. Inheritance means that new user-defined types (a user-defined type includes data and procedures that operate on the data) can be created by extending an existing user-defined type.

5. Support client/server architecture that is able to scale the database system to hundreds of concurrent users with thousands of gigabytes of data.

6. Support multimedia of data such as pictures, video, drawings, documents, and sound.

7. Support a superset of the ANSI SQL/2 query language which can handle user-defined

and multimedia types.

8. Support a query optimiser that will automatically determine the best way to process a

given query.

9. Support authorization, i.e. allow the user to either grant or revoke privileges to other

users.

10. Support concurrency control, transaction management, and recovery (soft and hard crashes).

11. Permits schema changes that not only add and drop new tables and classes, but changes such as adding new columns to tables or new attributes to classes.

12. Support automatic performance tuning by adjusting the system parameters.

13. Support integration with existing relational databases, such as Oracle and DB2, and existing graphical user interface tools, such as PowerBuilder and Visual Basic.

Relational database management systems fail to meet the first three criteria:

  1. A relational model does not support user-defined types nor inheritance. For example, a drawing may be a data type with its own attributes and methods. ABC Utility drawings are in files, attributes of those drawings are kept separately on an ORACLE database, methods are stored on DMCS, and displays of drawings are done by CAD OVERLAY.

2. A relational database management system does not support hiearchial or nested data data types. In a relational database, hiearchial data, such as bill of materials, are represented by several tables. In a relational database the retrieval of complex data would require performing several join operations, generally a very expensive procedure. At ABC Utility, most of the engineering data is either hiearchial or complex.

3. Stored procedures in a relational database system are not encapsulated with data; i.e.,

they are not associated with any table or row. Further, since a relational database

system does not have inheritance mechanism, stored procedures cannot automatically

be reused. At ABC Utility, stored procedures are part of application programs such as

PPMIS, DMCIS, and WARS.

 Object-oriented database management systems can be classified as either object-oriented or object-relational. Both types allow the user to create new data types made up of attributes and methods. Object-Relational Database Systems support both object-oriented and relational models. In fact, object-relational is an extension of a relational database which supports the object-oriented data model. At CI, we have examined the following commercial object-oriented database management systems:

• OBJECTIVITY/DB from Objectivity, inc.
• OBJECTSTORE from Object Design, inc.
• VERSANT from Versant Object Technology, inc.
• ONTOS from Ontos, inc.

and the following known commercial object-relational database management systems:

• UNISQL from Unisql, inc.
• ILLUSTRA from Illustra, inc. (Acquired by Informix Corp.)
• OPENDB from Hewlett-Packard

All of the object-oriented database management systems that we have examined did not adequately meet all of the software criteria. Objectivity/DB was examined thoroughly, which included training and software evaluation. We also examined ObjectStore, which included training and software evaluation. We have examined Versant literature, attended Versant seminars, and interviewed their technical staff. With ONTOS, we simply examined their technical literature. There are other object-oriented database management systems, such as Itasca from Itasca Systems Inc., Object Database from Object Database Inc., O2 from O2 technologies (France), and GemStone from Servio Corporation. It is clear that all of these purely object-oriented database management systems do not adequately satisfy criteria six through thirteen.

1. Specifically, criterion 6 was not adequately satisfied. None of them solved the problem of storing, retrieving, and updating very large multimedia data, nor have they solved the problem of incremental retrieval of multimedia data. For example, the page buffer in general cannot hold the entire object. Incremental update should not result in copying the entire multimedia object. Also, object-oriented database management systems have not solved concurrency control such that more than user can simultaneously access the same large multimedia object. And finally, recovery logging should not lead to copying an entire object.

2. Criterion 7 is not adequately satisfied. In object-oriented databases, querying facilities are not fully supported. SQL wrappers have been written for some object-oriented databases, however they do not support all the ANSI SQL2 features. For example, nested queries, set queries (union, intersection difference), aggregation functions (SUM, COUNT, AVERAGE, etc.), group by views, and even multiple joints are not supported. This means that object-oriented databases are not compatible with relational databases, i.e. they are not a superset of a relational database.

3. Criterion 8 is not satisfied. Object-relational databases do not support automatic query optimisers.

4. Criterion 9 is not adequately supported. Object-relational databases do not have grant and revoke features to read or change the data in the database, or change the definition of tables in the database.

5. Criterion 10 is not adequately supported. Support for concurrency control is not automatic. The user must explicitly set and release locks when processing query and update statements.

6. Criterion 11 is not adequately supported. Purely object-oriented database management systems allow new tables (classes) to be added, but do not allow additional changes to the database schema, such as adding an attribute or a method to a class, or dropping a class or table. The capability of adding new attributes to a table or class is essential to the project.

7. Criterion 12 is not adequately satisfied. In object-relational databases, system parameters are set manually when the object is created and there is no automatic tuning.

8. Criterion 13 is not adequately satisfied. Object-relational databases cannot be fully

integrated with object-relational database management systems. Object-oriented databases can interface with relational databases through a gateway but the user must be aware of the existence of two different types of databases. Further, only simple requests are possible, i.e. not all types of queries are allowed.

For the above reasons, we have settled on the object-relational model. Object-relational database management systems satisfy the thirteen stated criteria far better than the purely object-oriented database systems.

It would have been convenient to adopt HP’s Open DB for the project since ABC Utility has already made a considerable investment in HP hardware as well as some software. However, we have eliminated HP’s Open DB and its multi-database extension, Pegasus, as a choice for ABC Utility. Open DB uses a OO-layer approach. With this approach, the user interfaces with the database using an object-oriented database language (in the case of Open DB, it is Object SQL), and the OO-layer performs all the translations from the object-oriented aspects of the Object SQL to their relational equivalents. The translation overhead is significant and thus compromises performance. For example, the OO-layer maps objects to rows of tables, generates object ID’s and passes them, to the relational database as another attribute of a row buy using the interface that the relational database makes available. Furthermore, Open DB has its own proprietary language which may create interface portability problems. Also, learning a new proprietary language is a burden and an expense.

The other two commercial object-relational database management systems, Illustra and UniSQL, use the single engine approach. This approach melds the object and relational into a single layer. The necessary changes in both the data manager layer (i.e. talk to the database via SQL statements), and the storage manager layer (i.e., talk to the database via low-level procedure calls) are made to accommodate a superset of ANSI SQL/2. These single engine systems deliver higher performance compatible to object-oriented database for those operations that can be performed using object-oriented databases. Both Illustra and UniSQL are available for the HP-UX and NT. Illustra supports some features that UniSQL does not, such as storage and analysis of two-dimensional and three-dimensional special objects, and queries based on image content. However, we would not be able to take advantage of these capabilities since the images must be loaded by Illustra’s loader and most of the images at ABC Utility have been loaded by an HP loader. Our main objection to Illustra is that it does not support video data on HP-UX.

UniSQL supports all the multimedia types on HP-UX and NT. An another attractive feature of UniSQL is that it is a multi-database system. This system allows application development using a single global view and a singe database language over multiple heterogeneous relational and object databases. This eliminates the need for application developers to use several different database interface languages and deal with schematic differences among multiple heterogeneous databases. This may be used in the future to integrate DB2, ORACLE, and IMS databases at ABC Utility. At this time, we recommend UniSQL for the project. It satisfies all the thirteen criteria and has the potential to unify the existing ABC Utility databases. Illustra is the second choice only because it does support video data on HP-UX.

There are several serious problems with ABC Utility’s information systems:

1. Information on the same object is scattered over several autonomous databases.

This results in inconsistent data, low productivity, chronic delays, and high costs. At ABC Utility, equipment information and related operations are tracked on a hiearchial database management system, IBM IMS. The IMS interface, PPMIS, has no query capability. To remedy this, IMS data is copied to a DB2 database every twelve hours. The copy is sometimes inconsistent with the original, since different attribute names are occasionally used on the DB2 database. Equipment drawings are stored in individual files, and attributes for these drawings are stored on an ORACLE database. To locate a drawing, a search is performed on a Document Management Control System (DMCS), a front end to the ORACLE database. If the search is successful, the drawing is loaded into AUTOCAD, and viewed through program CAD OVERLAY. To access information, a user may have to perform searches on PMIS, DB2, and DMCS. Every user we interviewed expressed their frustration with this method. The hardware and personnel costs to maintain all of these databases are much higher than necessary.

2. Complex data is stored on systems designed to handle simple text data.

At ABC Utility, engineering data is stored on either relational or hiearchial databases, which are not adequately capable of handling such data. The difficulties of maintaining complex data on a relational database are that the data has to be taken apart and distributed over several tables. And, when we query complex data, these tables have to be joined in order to get all the decomposed data. The join operation is expensive, which causes the poor performance of relational databases when modeling complex data. The problems of using relational databases to represent complex data were discussed in the software evaluation section.

3. The interfaces to the databases, with the exception of DMCS, are difficult to learn and use.

For example, PPMIS does not support a query language and is very difficult to use. The only way to retrieve an item is to have the item ID. To interact with PPMIS, users must consult with a PPMIS expert, e.g., Dennis Pennington.The WARS interface is also difficult to use, and users must consult with a WARS expert, e.g. Jerry Piazza. The reliance on interface experts to retrieve and update data puts ABC Utility in a very vulnerable position.

At CI, we recognize that management cannot afford to risk the loss of investment in existing software, staff and expertise, nor disrupt current operations by changing to object technology. Neither can it ignore strategies that offer improvements in productivity, service, and operating costs. The problem is that most of the data at ABC Utility is complex (engineering data), which hiearchial (IMS) and relational (ORACLE, DB2) database management systems do not support well. At the time these databases were installed, there were no other options available. Today, technology exists that can handle complex engineering data and still preserve legacy data and applications. This new database technology is object-relational. It is now possible to migrate from existing pre-relational and relational databases to object-relational databases gradually at a rate that is comfortable to users, developers, and management. The important thing about object relational database management systems is that management, not technology, is in control of the transition process. This capability to make a significant transition incrementally, and with little impact on existing applications, staff, and operations, cannot be over-built. Management can decide whether to rush or stroll over to objects, or perhaps take a few steps in that direction.