REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE Urban Traffic Management and

							REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

Urban Traffic Management and Control UTMC-10

Requirements for Database Implementation
UTMC-10 Report 2 Copy 1

R. Meekums, J. Coleman, I. Cornwell, S. Archer October 1999 Cover + 47 pages

REQUIREMENTS FOR DATABASE IMPLEMENTATION

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

List of contents

Executive Summary 1 2 2.1 2.2 2.3 3 3.1 3.2 3.3 3.4 3.5 4 4.1 4.2 4.3 4.4 5 5.1 5.2 5.3 5.4 5.5 5.6 6 6.1 6.2 6.3 6.4 6.5 6.6 6.7 6.8 7 7.1 7.2 7.3 7.4 8 8.1 8.2 Introduction The Use of Information Introduction Usage characteristics of UTMC information Summary The Need for a Common Database Introduction Discussion Examples of Information Exchange Discussions With Users Discussions With Leicester City Council Requirements for a Common Database Introduction Requirements derived from data Requirements for services Constraining practical principles Technology Evaluation Introduction Architecture DBMS Client Interface Middle Tier Performance Data Model Design Introduction Data: Messages, Objects and Attributes Quality: Objects, Parameters and Class Data - Quality: Functions, Implementations, QualityStatements Access Control Auditable Items Referential Integrity UTMC-10 Common Database Internals Objectives and Definition of Trials Introduction Objectives Laboratory Trial System Definition Evaluation Live On-Street Trials Leicester Live On-Street Trial Glasgow Live On-Street Trial

3 4 5 5 5 6 7 7 7 9 12 14 16 16 16 17 19 20 20 20 21 22 24 25 26 26 26 29 31 34 34 34 35 36 36 36 37 39 40 40 41

UTMC-10 Report 2

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

Page

1

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

9 10 A A.1 B

References Glossary Appendix: CORBA SQL Query Query.idl Appendix: UTMC-10 Common Database Logical Data Model Design

44 45 46 46

UTMC-10 Report 2

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

Page 2

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

Executive Summary
UTMC systems will derive significant benefit from integration of applications, sharing data to improve the quality of service or to provide new services. Integration achieved through a common database has the advantage that each application need support only one interface, rather than a number of distinct links. Requirements for a common database are defined in this document. The common database must support reading and writing of ‘common data’. An application should only insert data into the common database if there is a separate application that can consume the data to perform some useful purpose. The common database must offer the additional services of user authentication, authorization, and must support ‘push’mode access as well as traditional ‘pull’ access. The database management system for UTMC should be relational, and access requests should be expressed in SQL. Relational technology is mature and can meet the requirements of a UTMC data service. The lack of a standard protocol for submitting SQL, together with need for enhanced levels of service, lead to the proposal of three-tier architecture based on CORBA. • • The lowest tier is the relational database. The middle tier is an application server that supports SQL access via the CORBA IIOP protocol. SQL requests will be delegated to the relational database using a suitable native data access protocol. The application server will use an ORB or ORB-like product that can support ‘push’. The top tier consists of the UTMC applications. They will communicate with the middle tier application server using the CORBA IIOP protocol. The UTMC applications may choose to integrate a full ORB, or a lightweight client-side IIOP product.

•

UTMC-10 has proposed a practical data model that can implement the features of the UTMC DataQuality model. Linking data to quality on a record-by-record basis would result in a massive redundant overhead on data volume and processing. Instead data is linked to quality through “Quality Statements”, which are fixed for a particular version of a UTMC application. Any ‘quality’ items that do vary on a record-by-record basis should be expressed in data attributes. To explore the implications of the various decisions for practical working systems, three trials have been planned. First, a laboratory trial will test a minimal UTMC system, with database server, middle tier server, and UTMC client applications. The client applications will simulate the action of real systems owned by Glasgow City Council. Building on lessons learned in the laboratory, UTMC-10 will develop and operate two on-street trials. Custom UTMC-10 applications will connect to operational systems in Leicester and in Glasgow and achieve integration of the individual systems through the common database.

UTMC-10 Report 2

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

Page 3

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

1

Introduction

This document summarises the investigations undertaken as part of the UTMC-10 project to look at the user requirements and technology requirements for a Common Database, and outlines the objectives of the laboratory demonstration and on-street trials that will be undertaken. The study has drawn upon the reports produced by UTMC-07/17. It has also drawn on the experience of Glasgow City Council, Birmingham City Council, Leicester City Council and Hampshire County Council through the ROMANSE project. The requirements of network monitoring, modelling and management are considered in detail in UTMC-04. This report addresses these aspects in relation to a Common Database. The document is structured in the following way: • • • • • • • Section 2 outlines the use of information. Section 3 discusses benefits which may be accrued from a Common Database and outlines discussions held with users to identify potential laboratory and live demonstrations. Section 4 gives details of the requirements on technology cited by UTMC-10 partners. Section 5 evaluates technology options given these requirements. Section 6 describes the principles, options and choices involved in the design of the logical data model for the Common Database. Section 7 uses the information obtained in the above sections to define the objectives of the laboratory demonstrations and the software development that will be necessary. Section 8 lists the further objectives of the post-laboratory phase of the project. Two on-street trials, involving integration with live on-street equipment in Leicester and in Glasgow, are defined. There are references and a glossary, and two appendices with supporting technical detail.

•

UTMC-10 Report 2

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

Page 4

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

2

The Use of Information

2.1

Introduction

To decide on the requirements for the implementation of a database, the use of the information has to be clearly understood. This section gives a broad overview of the information that is used and produced by UTMC applications. With the background information characteristics defined, Section 3 goes on to investigate the benefits arising from a common database, especially in UTMC integration.

2.2

Usage characteristics of UTMC information

There are a wide variety of applications available, which use and produce information at various levels of detail. UTMC applications require data to operate and will produce data, which is likely to be of use to other applications. “Static” data will form the definition of the system for each application. Additional “dynamic” data will be used as an input to the application for it to operate, and further dynamic data will be produced by the application as an output. The information produced as an output can be used in real-time: • • • • • as an input to other real-time UTMC applications; for dissemination to outside users/organisations;

and also to build an archive of historic data that can be used: for modelling/strategy development (UTMC-04); for trend analysis; for audit.

Depending on the data and what/who it will be used by, the data will be required to different levels of accuracy and quality and provided over different time periods (e.g. at the minute level or by quarter hour) depending on its use. It may be required instantaneously or may be sent once an overall picture has developed. Real-time data may be aggregated into intervals (e.g. one minute or five minute) as finer detail, for example second by second data, is not normally required by other systems. Other uses will require information some hours or days after it was created and perhaps aggregated into different time periods (for example for modelling and strategy development). There may be two types of historic data produced by applications relating to: • • normal conditions with no incidents abnormal conditions including data related to incidents

The first type of data is useful to applications for predicting what is likely to happen in the future and also for trend analysis. The second type is of use when developing strategies and modelling, for example trying to provide diversion advice after an incident has occurred at a certain location on the network. The information related to what happened when a particular event occurred can be used to develop and refine strategies. UTMC-04 is to produce a generic network model to build a picture of
UTMC-10 Report 2 REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1 Page 5

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

network conditions and will therefore use information from the common database in this network model. Operationally the common database will not directly affect the individual applications. These applications are likely to hold some key information internally and will pass information that they produce to the common database. They will pick up information from other applications via the common database and therefore if it is not available the application may operate at a lower level of reliability or may need to degrade gracefully. The level of availability of the common database is therefore likely to be a key issue in the operation of a number of applications.

2.3

Summary

In summary the common database needs to cater for a wide variety of different users. It therefore needs to be structured in a way that will allow data to be collected (and stored and referenced) to different levels of accuracy. The data will also need to be stored over different time intervals. The common database needs to allow for cross-referencing between different sources of information that relate to the same item. The data can then be combined to give greater confidence in the overall picture of conditions. Data in the common database must be useful to other applications: there is therefore no need to store information in the common database if it is only used by one application. These issues are discussed further in the following sections.

UTMC-10 Report 2

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

Page 6

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

3

The Need for a Common Database

3.1

Introduction

The key question that is addressed in this section is whether there is a need for a Common Database and what benefits can be accrued if one were to exist. In a number of towns and cities across the UK systems operate independently from each other. They provide a “service” to the end users (normally the travelling public) and are aimed either at providing accurate and timely information or operating systems which look to optimise the use of the network (for example signals operating under UTC). These systems can operate independently from each other, but additional benefits can be achieved if systems are able to obtain information either from a central database or directly from other systems.

3.2

Discussion

The benefit of sharing information is that the sum of the parts is greater than the individual parts themselves. An application can potentially give more reliable information to travellers if additional information is available to give better accuracy in predicting journey times. For example, a real-time information system for buses can gain from the information provided by a UTC system to improve the reliability of the predictions on the arrival of buses at bus stops. There is therefore added benefit if these systems exchange information even though they do not rely on the additional information being available. Further, there may be situations where the information from buses on the road network is not being sent back to the control centre and therefore the system is “blind” on the conditions on certain parts of the network. In this case there are real benefits in information from UTC being used to enable predictions to be made. Faults on one system can therefore potentially be covered by information being provided by other systems, thereby reducing or eliminating inaccuracies in the information provided to end users. The provision of reliable, timely and relevant information is key to travellers utilising the services provided whether it be better information on the road network for car journeys or better information on public transport services. Information is therefore key to providing a multi-modal sustainable transport system. Information related to the environment (for example from pollution monitors) will also be an input to a number of applications and this is likely to become increasingly important in terms of the transport system. UTMC will enable applications to be purchased from potentially different suppliers while still enabling them to send and receive information to/from each other in a common format. If applications are able to request and send information then potentially there is no need for a common database. They could each have their own database and exchange information as required. However, one application may be able to make use of information from a number of other applications and therefore if all the information is collated and held centrally it will be easier to obtain the best
UTMC-10 Report 2 REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1 Page 7

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

information available at that moment rather than having to develop individual links to each of the other applications directly. Databases will exist for a number of the individual applications. Certain data, for example the static data which underlies all applications and which will include elements used by a number of applications (for example links and VMS) could be held centrally. Applications could reference this data thereby removing unnecessary duplication of transcription, which may lead to errors and a mismatch of data. It will also avoid duplication of equipment and communication: one of the principal aims of UTMC. The benefits to be derived from a Common Database will be realised where a number of UTMC applications are in operation. One application on its own will not benefit from the database as it will not need to transmit or receive information from other applications. However this is very much the exception rather than the rule. The flexibility should be available so that if/when other applications are added then the database is readily accessible without the need for a large amount of additional work and cost. There are therefore benefits to be gained by systems that already exist utilising a common database to store and share information. It is believed that a large number of applications will be able to gain additional benefits in this way. An overview of the potential set up of the Common Database and the interaction with the users is shown in Figure 3-1. Examples and analysis of some of the potential requirements for data exchange are given in section 3.3 below. The laboratory and on-street trials to be undertaken in UTMC-10 will demonstrate the benefits of the common database.

UTMC-10 Report 2

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

Page 8

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

OFF-LINE
UTMC STATEGY DEVELOPMENT

REAL TIME

UTMC APPLICATION 1 COMMON DATABASE

UTMC MODELLING

HISTORIC INFORMATION

UTMC APPLICATION 2

CURRENT INFORMATION UTMC TREND ANALYSIS Data exchange as required UTMC APPLICATION n

OUTSIDE ORGANISATIONS

Figure 3-1 Overview of Users of the UTMC Common Database

This representation does not prohibit applications from exchanging information directly with each other where this is reasonable, but demonstrates that there is likely to be added benefit to applications by the use of a Common Database.

3.3

Examples of Information Exchange

The following table identifies some of the applications that may be operating within a UTMC system and whether they would require data being transferred to and from other applications. The sub function codes quoted in the table relate to the codes detailed in the UTMC Technical Issues document [1], Tables B1 to B9. This table highlights the complexities if these applications were to communicate with each other individually and also demonstrates that a common database would greatly simplify this situation. The table gives details of real information flows but is not intended to be exhaustive. The production of this table was undertaken as a paper exercise and is not based on any particular existing set up of systems.

UTMC-10 Report 2

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

Page 9

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

Application To From 110 140 150 180 1030 1510 1530 2000 2210 2220 2230 2500 2600
Key 110 140 150 180 1030 1510 1530 2000 2210 2220 2230 2500 2600

110

140 Υ

150 Υ Υ Υ Υ Υ Υ Υ Υ

180 Υ Υ Υ

1030 Υ Υ

1510 Υ

1530 Υ

2000 Υ

2210 Υ Υ Υ

2220

2230 Υ Υ Υ Υ Υ Υ Υ Υ Υ Υ Υ Υ

2500

2600

Υ Υ Υ Υ Υ Υ Υ

Υ Υ Υ Υ Υ Υ

Υ Υ Υ Υ Υ Υ Υ Υ Υ Υ Υ

Υ Υ Υ Υ Υ Υ Υ Υ Υ Υ Υ Υ Υ Υ Υ Υ Υ Υ

Υ Υ

Υ

Υ

Υ Υ

Υ

Υ Υ

General Traffic Signal Control Priority control for emergency vehicles Control associated with tidal flow and diversions Ramp metering Parking Management - Providing Information Monitoring of current network conditions Congestion detection Static Database Management Fault report management Equipment performance management Testing Inter-urban monitoring and control systems Police systems

Υ = potential benefit

Some of the examples of real exchanges of information (shown in the table above) and the benefits of the common database are detailed below. 110 1530 General Traffic Control to Congestion Detection Within UTC signal control running SCOOT, there are a number of detectors. Whilst one detector being congested does not necessarily indicate congestion, it is possible to create boolean algorithms for sets of detectors which can reliably detect both the onset and the presence of congestion. At present, in the Birmingham (Mattisse) area, congestion is spotted within the Peek system by using an add-on module and within Siemens systems by Ingrid. In both cases the information is then exported to Mattisse. By taking the data via the Common Database to a Strategy Management system , it would be possible to generate outputs to: a) b) Call up a different plan for signal control Set message signs on routes
REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1 Page 10

UTMC-10 Report 2

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

c)

Send information to a regional traffic and information system, for example Mattisse

150-110 Control associated with Tidal flow and Diversions – UTC Signal control At present the use of Strategies related to UTC Signal Control is restricted, though intuitively “management” wants to be able to use them. To do so effectively will require data from UTC and possibly Interurban Networks. These would be passed to a strategy module, to determine what strategy should be selected, and on to VMS/EMS control. Information would also be passed to a regional Travel information system. Input from special detectors installed on diversion routes may also be taken. This has no direct purpose except for that of monitoring the effects of the application of strategies as they are selected and thereby enabling post analysis of the strategy. 100 series The “100” series, related to traffic signal control, would potentially all exchange data with each other. For example, the priority control for emergency vehicles application (140) would exchange information with the general traffic signal control (110). The changes required to signal timings for priority vehicles will interrupt the general control of traffic and therefore the applications will need to know what the current settings are and also the flows on the different approaches to the junctions. If ramp metering sites exist then the emergency vehicles may wish to use the ramp being controlled and would therefore send information to that application to produce a green wave for the emergency vehicles. 1030 150 Parking management to tidal flow Parking Management providing information (1030) will exchange data with control associated with tidal flow and diversions (150) since if information is to be displayed on VMS associated with route guidance to car parks then it will need to know of any diversions in operation. 1510 140 Monitoring current network conditions to priority for emergency vehicles Monitoring of current network conditions (1510) will send data to a large number of other applications, for example priority control for emergency vehicles (140) so that emergency vehicles are not sent into major congestion or other incidents. Most of the data associated with application 1510 will be sent rather than received. 2000 Static Database Management This will provide the underlying data to all applications but will not receive information from the other applications detailed above. Any new information is likely to be input by operators to update or add additional infrastructure such as links or VMS.

UTMC-10 Report 2

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

Page 11

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

2210 Fault report management This will receive details of faults directly from equipment or from other applications monitoring/using equipment. Applications include 110, 150, 180 etc. The management system will send these details to the individual applications which utilise the equipment so that the applications will not try to use the faulty equipment in any strategies. Details of when the equipment becomes available again will also be provided to the applications. Currently, without a common database all faults have to be diagnosed within systems and then manually input into a fault system. This has the advantage of ensuring that the information is checked by someone but it does not allow for fast, efficient and error free transfer of data. 2500 Inter-urban monitoring and control systems This will exchange data with a number of applications related to the network conditions for prediction purposes. The above analysis has identified a large number of cases where systems could exchange data to provide additional benefits compared to operating in isolation. A Common Database would give benefits by providing a single location for data, collected and collated from the different systems, to be held and disseminated as required. Some examples of data exchange using a common database will be explored in the laboratory and onstreet trials. This will allow investigation of actual data items and attributes requiring exchange and associated data volumes and performance requirements.

3.4

Discussions With Users

This section outlines the discussions held with UTMC-10 project partners to better understand the user needs related to real, live systems. These are used as the basis for the laboratory and live trails detailed later in this report.

Discussions With Glasgow City Council Glasgow has been allocated as the main trial site for UTMC-10. Glasgow has a UTC system and a number of other systems including NADICS, CITRAC and a car park management and guidance system. A number of the systems that exist could benefit from the exchange of information through a Common Database. For the purpose of the trials it is envisaged that a certain section of the network will be used which contains the various elements required.

Common Database Candidates Discussions identified a number of candidates of data for the Common Database. These were:

UTMC-10 Report 2

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

Page 12

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

• • • • • • •

Location – required by virtually all applications. This will need to be flexible to allow for a number of different references to be made for the same point. For example local names. Flow – needed in a number of applications e.g. signal timings, car park management, travel information and public transport services. Congestion – this may be identified from a number of sources and needs to be passed to other applications for use. This is also the case for incident location. Journey time – data from equipped vehicles including buses can be fed into systems to predict journey times into the future and potentially to identify incidents. Vehicle running schedule – potentially useful in public transport management and signal control to determine active priority. Car Park Occupancy – needed for the selection of sign and signal strategies, especially when dealing with major venues. Public Transport predicted arrival time –linked to VMS and park and ride services.

Applications The discussions identified a number of applications for consideration and the matrix below shows which interactions were felt to be the most beneficial and could potentially be demonstrated: these are therefore not exhaustive.

Application To From

Traffic ANPR Control

Bus Time

FRAS

Car Park Management

Ramp Metering

“TABASCO”

Traffic Control ANPR Bus Time FRAS Car Park Management Ramp Metering “TABASCO” Common Framework
UTMC-10 Report 2 Υ Υ

(Υ)

Υ

(Υ)

Υ

Υ

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

Page 13

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

The following is a brief explanation of the applications above: • • • • • • • • Traffic control – UTC system using traffic signals to control traffic ANPR – Automatic Number Plate Recognition system using image processing techniques to produce journey time information between two points Bus Time – public transport information system. FRAS – Fault Reporting and Analysis System for equipment located across the network Car Park Management and Guidance system – system to count vehicles entering/leaving car parks and to direct traffic to spaces Ramp Metering – the use of traffic signals to meter the flow of traffic from a slip road onto a main carriageway TABASCO – a diversion strategy which compares the journey time on two routes and diverts traffic by the use of VMS Common Framework – a reference model to underlie all applications.

These real/live systems give a good spread of applications, which could be used to demonstrate the common database.

3.5

Discussions With Leicester City Council

Applications As Glasgow does not have a SCOOT UTC system Leicester is being used as the demonstration site for trials related to SCOOT. Leicester City Council operates a SCOOT UTC system, which also includes ASTRID and INGRID. A number of other systems are also in operation in Leicester including an environmental monitoring system, which collects information from sensors on the network. A bus AVL system is currently being developed and a car park VMS system integrated with UTC using fixed text message signs exists. Leicester has also been one of the key locations for the development of a system for road tolling. The detector data collected by SCOOT is required by the SCOOT system in real time to update the signal control at the junctions. The information is then either “lost” or sent to ASTRID (if available) for storage or to be used as historical data. This information could be stored at the raw/detailed level or it could be stored at the 5 minute or 15 minute level but the detail will therefore be lost. However, data at the very detailed level is unlikely to be required for the majority of other systems. Other systems are in general interested in the profile of (for example) the flow on the links rather than the minute by minute counts. SCOOT can also send information out in the form of SCOOT messages which are of a standard format and contain information at a variety of levels (e.g. link or region) and on a variety of different types of data (e.g. flows, speeds, number of stops etc).
REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

UTMC-10 Report 2

Page 14

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

Other information related to bus priority at traffic signals may also be time critical and may need to be held within SCOOT to enable the benefits of priority to be realised. This depends on how the priority system operates: in Southampton the ROMANSE project AVL system (STOPWATCH) sends details of the location of buses to the UTC system to change the signal timings to allow a late bus to get a green signal. In other cases the buses are detected near to the stop line via a detector and the information is sent directly to the signal controller at the junction to give priority. The latter method needs the data to be transmitted more quickly than the former and is time critical. Currently in Leicester, environmental information is sent from the SCOOT system in the form of SCOOT messages to the Airviro pollution monitoring system. This information is sent directly between the two systems, however, if other applications were to require this information (for modelling for example) there would be benefits in passing the information through the common database. As in Glasgow, a number of different applications exist which could benefit from the existence and use of a Common Database and the trials are detailed later in this report.

UTMC-10 Report 2

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

Page 15

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

4

Requirements for a Common Database

4.1

Introduction

Section 3 detailed the discussion held with users in terms of the systems that exist and potential demonstrations that could be undertaken. The UTMC-10 Project Report 1 “Review of Database Technology” presented technology options of relevance to a UTMC Common Database. It also acted as a stimulus for discussion and feedback from UTMC-10 partners. This section summarises the technological requirements cited by UTMC partners. In Section 3 the requirements were discussed mostly from an application domain-specific viewpoint. In addition, technology requirements are evident in feedback from the UTMC-10 partners, the users of UTMC. Overall, the technology requirements are: • • • requirements derived from the nature of the data as defined in UTMC-07/17; requirements for services delivered by the technology; constraining practical principles cited by UTMC users.

These areas are looked at more closely below.

4.2

Requirements derived from data

Project UTMC-07/17 has defined a model for UTMC data and its quality. UTMC data primitives are “data attributes” which are grouped into “data objects”. The data attributes are not further refined: there are never data objects with attributes that are themselves data objects. Orthogonal structures describe the quality associated with the data objects and attributes. The UTMC-07/17 model has been used as a starting point for UTMC-10. (A full treatment appears in Section 6). Data characteristics will vary widely. Individual traffic monitoring messages will be very large in number and small in size, with real-time access requirements, while plans will be large objects, small in number, heavily linked to other data, and requiring less frequent access. The Common Database must satisfy a full range of varying requirements on entity size, population size, complexity, and access patterns. Data included in a common database must be useful to other UTMC applications. This means that some of the most rapidly varying data in urban systems may be too sensitive to be of practical use. Requirements for time resolution should be driven by the consuming applications: if there is no application that can derive benefit from having data updates with a particular time resolution, then the data need not be sent to the common database with that time resolution. The same argument applies to real-time frequency of access; however, applications may find it convenient to supply data as soon as it becomes available, even although consumers (who do need the full resolution) may not require realtime access. The characteristics of r apidly varying data, whose resolution or access frequency is genuinely required to meet some valuable purpose in another UTMC application, should be a driver for performance requirements of a common database implementation. Representative applications are used to illustrate very broad requirements of scale for each property:
UTMC-10 Report 2 REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1 Page 16

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

•

Population A representative of applications with a large number of messages is UTC SCOOT. To provide a good overall monitoring of network performance, 15%-20% of the network detectors need to be monitored. Providing those links selected are the critical ones, increasing the level of monitoring improves any overall network model only slightly. Taking Birmingham as an example, the SCOOT system would supply to the common database ~200 each of two kinds of message every 45 seconds. (Note that raw SCOOT input data is not likely to be useful to a common database – it’s specific to the SCOOT kernel). Access pattern Sub-second access is common. Individual vehicle recognition systems have an average period of a few seconds, with sub-second bursts. Entity size Individual vehicle and the above SCOOT messages are typically small, in the order of 20-50 bytes. At the other end of the scale, the application in UTMC with highest demands for transmission and storage (if desired) is CCTV. There is no perceived requirement for continuous real-time storage of CCTV, but there is a requirement to handle images or short sections. A single digital image is an identified UTMC data attribute. A data object containing a single image could be in the order of one megabyte. For video sequences, considerable data compression is possible: slow scan, transmitting only the areas that have changed, and reduced pixels can yield a data rate of <32Kbit/sec. Complexity There will be UTMC data objects that are simple, in the sense that they are sufficiently self-describing and need no further linkage to other objects, but dynamic data will more typically require linkage to static data – for example the dynamic data will identify some equipment rather than duplicating all parameters about that equipment, and consumers of the dynamic data will also need the static data. It is not possible to gauge the level of linkage from the UTMC data dictionary, but it seems likely that more complex queries will arise, and so the common database must be able to support sets of data with several levels of linkage.

• •

•

4.3 Requirements for services In discussions between UTMC-10 partners, the Report 1 Section 4 “Database Services” has been used as a focus for establishing requirements divorced from technology. This section presents service requirements for a common database, derived from partner feedback. The authors expect to refine this list through the remainder of the UTMC-10 project. Essential Services • • • • • The common database must support reading and writing of data with characteristics identified in section 4.2 above. The common database must offer access control to prevent unauthorised access. The common database must support the restriction of access to individual areas of data. The common database must support asynchronous notification (“push”). The common database should support flexible filtering on push conditions. Not only should users be able to request notification of a particular data entity, but also they should be able to restrict the
REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1 Page 17

UTMC-10 Report 2

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

“push” to only those updates which matched certain conditions of interest. For example, a consumer of data could ask for notification of environmental monitoring data, but only when a pollution level passed a certain threshold. This reduces communication and saves the consumer application from applying the filter. • The common database should be able to act as the point of provision of value-added services. Examples of beneficial value-added services include prediction of congestion and interpretation of raw data for incident reconstruction.

Further User Needs • • • It is desirable that access control be achieved through (as a minimum) password authentication of known users. The common database should not allow unprotected direct internet access. There is interest in using map based user interfaces. This has relevance to the common database because a design option would be to have server-based supply of geographic data, and spatial query facilities. Access control on a row/column level (or non-relational equivalent) may be required. The same set of standard interfaces to the common database should be used wherever possible. The interfaces and the supporting technology must be designed to handle the demands of UTMC functions that supply or consume large data volumes in real time. Whilst it is hoped that the performance of the standard interfaces will support most needs, specialised interfaces for high performance access can not be ruled out at this stage of the UTMC-10 project. No extra “clearing house” will be required. Any requirements for data consistency should be implemented in the Common Database – either by the DBMS or by middle tier logic. No need for dynamic binding was foreseen. The transportation domain doesn’t have a sufficiently well-defined framework for developers to be able to predict uses (OMG may be defining such a framework but no output is available from them at this time). The common database should support the storage of images and video, but does not require realtime storage. Although continuous real-time storage of complete video is not required, some interest in live feed through a common database interface was expressed. This could be from a live source, or from some external storage known to the common database server. This will inevitably require a specialized interface. Some interest in data warehousing was expressed, for example to analyse trends in SCOOT data. Warehousing is considered to be outside the scope of UTMC-10, since it can be treated independently: different products are involved, and the Common Database can be used as a source to populate a data warehouse at a later stage if required.

• •

• •

• •

•

UTMC-10 Report 2

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

Page 18

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

4.4

Constraining practical principles

Scalability UTMC Common Database technology must be scalable, not prohibitively expensive. It is desirable that the recommended technical solution can be applied to multiple hardware platforms. A small authority may require a minimal common database using a standard PC (with standard storage sizes) and low software licence costs. Reliability High reliability is clearly desirable, but detailed requirements will be driven by the way in which UTMC applications are written: if they are written such that they depend on the common database and cannot function without it, then the availability requirements of the common database become the most stringent availability requirements of the connecting applications. If it is possible to engineer the UTMC applications such that they can still operate (with degraded functionality) even while the common database is unavailable, then availability of 95-99% would be sufficient. Note that in either case downtime may be acceptable if scheduled. Maintenance The common database should incur low operational maintenance costs. Authorities may have limited skill resources to devote to database maintenance. Overall, no specific technology or architecture was preferred or discounted by the local authority partners.

UTMC-10 Report 2

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

Page 19

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

5

Technology Evaluation

5.1

Introduction

Using the requirements cited by UTMC partners as an input, this section analyses corresponding technical solutions. The technology reviewed in UTMC-10 Report 1 is evaluated in the UTMC context, as set by the preceding sections. It is not the purpose of this section to define final recommendations for design or implementation of the UTMC Common Database – that will be the final output of the project. It does however, make tentative recommendations, and looks to assist in focusing the forthcoming laboratory and live trials.

5.2

Architecture

UTMC-10 Report 1 identified limitations in the SQL Client/Server architecture which are extremely relevant to UTMC. In particular the lack of a standard communications protocol is a significant drawback in the UTMC context. Every system supplier that developed UTMC applications would be forced to use the proprietary communications protocol required by the common database server in that urban area. If two common database servers in different urban areas used different vendors for their implementation, then UTMC applications developed for one area could not be re-used in the other area. Connectivity products hide these protocol differences but do not cover every combination of client and server. With these solutions, the problem of lack of standardised procedural constructs remains. Any value-added services written via SQL will not be portable. Furthermore, the UTMC requirement of asynchronous notification is not generally achievable through connectivity products. Different database vendors have custom (if any) mechanisms for implementing asynchronous notification, and these mechanisms cannot be easily hidden in client programs. The requirements for services lead us to proposing a three-tier architecture, as outlined in UTMC-10 Report 1. In particular the requirement for asynchronous notification, and the requirement for valueadded services, are better handled by middle tier software than by a DBMS alone. The three tiers are: UTMC applications, middle tier software and a DBMS. The Figure from UTMC-10 Report 1 is presented as a reminder.

UTMC Function

UTMC Function

UTMC Application Server (“middle tier”)

UTMC Common Database Management System (DBMS)

Figure 5-1 Three-Tier Architecture REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

UTMC-10 Report 2

Page 20

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

While the three-tier scheme has the extra initial cost of a middle-ware product, this should be easily outweighed by any cost savings achieved by increased re-use through inter-operability and standardisation. The DBMS technology for UTMC is evaluated in section 5.3. The interfaces which UTMC applications could use to connect to the Common Database are discussed in Section 5.4. With client interfaces and DBMS defined, requirements for the middle tier will be clear, so the evaluation of middle tier technology is presented in Section 5.5.

5.3

DBMS

The chosen DBMS technology must be able to meet the requirements for the ranges of access outlined in Section 4.2 above. The authors believe that all of the DBMS technologies can potentially support these needs, and that the choice must be based on other factors. UTMC-10 Report 1 discussed features and limitations of various data management technology; the importance of these can now be assessed given UTMC requirements. Relational databases’ limited support for data types remains something of a problem. In particular, UTMC will require storage of images and possibly video, but would have to rely on vendors’ proprietary extensions for representation of these types. A mitigation is that each major vendor does offer a mechanism that is functionally equivalent, but some porting effort would be necessary to re-use a UTMC application with a different SQL dialect. Other perceived limitations are less serious in UTMC. Proprietary extensions to SQL are mostly to provide procedural constructs, but in a three-tier architecture any significant procedural work should be performed in the middle tier. A major drawback of relational databases, and a corresponding strength of object databases, is in the area of support for persistence of objects. In UTMC there is no need to support the requirements for persistence of an ad-hoc object-oriented application, since all UTMC data access will be in terms of pre-defined UTMC data, following the data dictionary. The problem of incompatibility of relational normalisation and object-oriented self-containment vanishes, since even the UTMC “data objects” have not been designed with object-oriented principles. An ODBMS can directly represent any “logical inclusion” in an application domain, for example “a road has 2 carriageways”. In an RDBMS, the method of linking the road to the carriageway is exposed: applications need to know that the road and carriageway entities are linked by a common attribute: the foreign key in carriageway which identifies the road. In UTMC, “data objects” contain “data attributes”, but there are no cases where the data attributes are themselves objects. This means that the UTMC data can be readily represented in a relational model. The object-to-relational mapping that plagues hybrid systems is not required. Thus much of the benefit of an ODBMS is negated. This argument could be taken further. If UTMC (client) applications developed in the future are object-oriented, aside from their data access, then these applications will require internal mapping to comply with the UTMC data dictionary representation at their interfaces. If future UTMC application development is likely to be mostly object-oriented, then the mapping effort may be considered wasteful: why not instead replace the existing data dictionary with an agreed object model, and use an
UTMC-10 Report 2 REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1 Page 21

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

object-oriented database, with very direct access? UTMC-10 does not believe that the benefit would justify the extra effort. Object persistence standards are not sufficiently mature. The RDBMS vendors’ “head start” is a significant factor. The RDBMS products are mature, robust and powerful, and have massive industry support that will ensure their continued existence and development for many years. Hybrid object/relational DBMS have advantages in a local environment, but their extended features are not standardised, and to rely on particular features would mean locking the UTMC community into the use of one vendor. The limitations of file storage are held to be too serious to allow consideration of further utilisation in new development. Any initial cost savings will quickly be negated by extra development effort to extract the level of service required. However, legacy files can still be used as data sources. When a file-based system is to be integrated as a data source in UTMC, the integrator can compare the effort of: 1) a complete conversion to database access with 2) a middle-tier solution, where data is supplied to UTMC applications through the standardised interfaces, hiding the fact that the middle tier actually accesses the legacy files. Feedback has confirmed that an important objective for the common database is to provide a common geo-spatial reference system. UTMC objects could then be related by virtue of position alone if necessary. In order to support efficient spatial queries based on UTMC location attributes the Common Database will need access to GIS type spatial operations (eg intersect, outside, within, beyond). This could be provided by adopting a relational database with spatial query extensions, or possibly by using the Common Database applications layer to intercept spatial queries and redirect them to a separate GIS database. For the reasons discussed, UTMC-10 proposes that the UTMC common database be implemented using a relational DBMS. This also allows UTMC-10 to endorse the UTMC Technical Specification in its choice of SQL to express requests for access to the common database. Consequent principles and options for common database design are detailed in Section 6.

5.4

Client Interface

The Common Database specifications must include details of the client interface to be used by UTMC applications. The content of any request for service and the mechanism (protocol) by which this request is issued are discussed independently.

Protocol The main options are CORBA IIOP, COM, and native protocols supported by the DBMS vendors. Native protocols can be hidden by standard APIs such as ODBC and JDBC, but ODBC is giving way to COM-based OLE DB approaches and JDBC is for Java only. As discussed in UTMC-10 Report 1, CORBA IIOP is the open standard which suits the UTMC design principles. (However, understanding the practical implications of CORBA on architecture and scalable integration for UTMC will be a focus of the forthcoming trials). IIOP is an efficient protocol which should not constrain the range of transactions performed through the common database.
REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

UTMC-10 Report 2

Page 22

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

A further benefit of a CORBA approach is that UTMC technology should be compatible with emerging Travel Information Highway (TIH) architecture, which will be a useful source of inter-urban information. The communications paths allowed in Common Database access should also be clarified here. The architecture suggested that all UTMC applications should communicate through the middle tier, and not connect directly to the DBMS. Since the DBMS will also support connections through a native protocol, one might consider that option (in addition to middle-tier access) where the level of service provided by the DBMS was sufficient. However, that access could be native and proprietary, and therefore undesirable, since the purpose of UTMC-10 is to define a standard facilitating interoperability of UTMC products. All access, where performance allows, should be through the middle tier, using the specified protocol. If any specialized applications arise whose performance requirements cannot be met by the standard interfaces, specialized interfaces can be created. A specialized interface could use the direct-to-DBMS path at risk of reduced portability.

Content Since the DBMS will be relational, the query language should be SQL, the standard for relational database access. This matches the requirement in the original UTMC specifications. SQL is still an evolving standard. At present SQL-92 is most recent standardised version, but an updated version is progressing through standardisation. When this version is ratified, UTMC must look at vendor support and decide whether to adopt the new standard; meanwhile SQL-92 should be used. The alternative of hiding the relational database through a custom data access layer has little benefit, and involves either vendor tie-in for an off-the-shelf tool, or a huge development effort. SQL will be used to express all snapshot queries, and updates to data (including insert, delete). Since the underlying protocol will be IIOP, the SQL must be embedded in a method invocation. The CORBA Query Service, which supports SQL as a query language, is a standardised way of achieving this, but implementations have been slow to emerge. A custom IDL interface definition may be sufficient for UTMC needs. The client interface should also support subscriptions, and requests for value-added services. The exact form of these interfaces is yet to be defined. The CORBA Notification Service is not yet fully defined and standardised, so cannot be recommended. An important distinction will be whether or not the client interface is required to support a call back mechanism for notification. SQL is suitable for applications that supply or consume discrete items and is less suitable for continuous feed of live information. UTMC-10 partners have not identified a requirement for continuous live feed, but if the requirement arises in the future, a new custom CORBA interface would be defined and implemented on the middle tier server application. The SQL interface would however be equally suitable for operations on discrete static data (such as equipment locations), and discrete time-varying dynamic data. Through common database timestamps, the SQL interface can also be used to provide a global time reference. Provision of complete GIS-type graphical applications (on screen mapping and other functionality) is not a function of the Common Database and UTMC users will typically have their own systems and standards for that. However it would be possible for users to download static spatial data from the
UTMC-10 Report 2 REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1 Page 23

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

Common Database for use with local desktop GIS, or corporate GIS systems, subject to satisfactory resolution of any licensing issues.

Implications for UTMC Application Developers A CORBA/SQL solution places few constraints on the environment for implementation. Development can use any language for which there is a CORBA binding, for example C++, C, Java, Ada. There are ORBs and client-side IIOP products for numerous platforms, providing full support of the Technical Specification’s requirement for X-Window or MS Windows user interfaces. The use of SQL allows straightforward re-use of legacy clients, with modest wrappers.

5.5

Middle Tier

With DBMS and the client interface defined, it is straightforward to specify the minimum requirements of the middle tier. • It must understand IIOP. Most likely the middle tier uses a CORBA ORB, but other products, for example Sybase Jaguar CTS, have built-in IIOP protocol handlers. • It must be able to access the RDBMS. The code implementing the services offered must integrate with a database access mechanism. Since the middle tier and Common Database should be viewed as single entity from the viewpoint of systems supply, we suggest that the middle tier uses the native protocol of the chosen DBMS. This gives maximum efficiency and is still hidden from the UTMC application developer. • • It must support user authentication for each session. It must have a mechanism for determining when a data change should be notified to interested consumers. This must be custom code, as no suitable product exists. The DBMS may perform the initial detection of change, for example through triggers. The middle tier should therefore be custom UTMC code attached to an ORB or ORB-like middleware product such as Sybase Jaguar CTS. The ORB market is fully scaled: there are free ORBs, and there are commercial ORBs with improved performance. We expect ORBs to be cheaper and more widespread by the time UTMC applications are being developed. A UTMC application could choose a suitable ORB to meet its requirements; for example the Common Database for a large authority may use a commercial ORB, while a small car park management application could use a free ORB. Another free option is the client-side “orb-let” from Sybase, designed for use with Jaguar CTS. Note however that this product does not support calls from server to client, so will be unsuitable if the common database notification mechanism requires a callback.

UTMC-10 Report 2

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

Page 24

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

There are versions of these products for both Unix and Microsoft Windows NT, and because of IIOP, the components are interchangeable. UTMC-10 will not specify a particular ORB or ORB-like product as mandatory for a common database implementation, but will rely on CORBA standards such as IIOP.

5.6

Performance

Performance scalability of the server application / relational database pair is important, but to obtain a favourable performance/price trade-off, direct access to the common database should be restricted to a predictable set of providers. Providing direct public internet access creates an unpredictable level of access, with an unpredictable processing and communications load. Instead WWW support can be provided through a separate machine with local storage, which would read from the common database. If that separate machine failed to cope with the load placed by public access, the operational common database would be unharmed. This also improves potential security options for the common database; for example the web server can be placed on the other side of a firewall. As identified in UTMC-10 Report 1, RAID technology gives improvements in reliability, at a cost. Since RAID is transparent to the applications and even to the operating system, each UTMC common database implementation can evaluate the RAID reliability/cost trade-off independently, without any impact on the rest of the technical solution. UTMC-10 considers that some level of RAID is desirable for all common database implementations. Database replication is another technology that can improve overall system availability at cost, and can also provide performance benefits through dynamic load balancing. However the options for design, deployment and operation are complex, so the cost is high. Replication will not be mandated by UTMC-10. If load balancing or provision of a “warm stand-by” become important, then replication design can be investigated without reversing the general aspects of the UTMC-10 technology evaluation.

UTMC-10 Report 2

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

Page 25

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

6

Data Model Design

6.1

Introduction

This section describes the principles, options and choices involved in the design of the logical data model for the Common Database. It deals first with features designed to support the UTMC Technical Specification and the Data-Quality architecture proposed by UTMC 07/17. Finally features introduced by this project (UTMC 10) are described, mainly relating to 'standard' database issues like access control, audit trails and referential integrity. Much of the detail is relegated to a separate report produced from the project CASE tool, which is attached as Appendix B, UTMC-10 Common Database Logical Data Model Design. The logical design is used to generate a first-cut physical design for the required RDBMS (Sybase Adaptive Server 11.9 has been chosen for the UTMC-10 trials), but that is beyond the scope of this document. For UTMC 10 the challenge has been to develop a practical design for a Common Database, which could remove the requirement for multiple point-to-point data passing between UTMC Functions. The Common Database would provide a single source or sink for UTMC Data Objects to all UTMC Functions. Each function would only require one information exchange interface, to the Common Database. The adoption of standard SQL as the language for data exchange is a natural consequence of the Common Database concept, and provides the immediate benefit of supporting queries. The message based technique proposed in the first UTMC Technical Specification allowed a Function to send data to another Function, but not to request certain information be sent in return.

6.2

Data: Messages, Objects and Attributes

The original concept was that UTMC Functions would exchange data by sending messages to each other using a carefully defined message structure. Each message would be a long character string comprising a variable number of attributes identified by short codes introducing the associated value string. Certain attributes were identified as mandatory (message header) but most were optional. Many message types were identified and many attributes were legitimate in the context of multiple different message types. UTMC 07/17 introduced the concept of Data Objects to take the place of messages but the approach was very similar to the original proposal, as shown by their figure :UTMC Function (Faaa)

UTMC Data Attribute (ccc)

UTMC Data Object (bbb)

Figure 6-1 UTMC 07/17 Proposed UTMC data structure incorporating data objects

UTMC-10 Report 2

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

Page 26

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

Although the word 'object' becomes more prominent, the UTMC 07/17 proposals do not amount to a restructuring of the UTMC data model in an object oriented way. UTMC Data Objects are made up of mandatory and optional Data Attributes, which are themselves fairly simple and certainly are not Data Objects in their own right. The Common Database should be designed to support the Data Objects proposed by UTMC 07/17, but how should UTMC data be structured in the Common Database ? The many-to-many relationships in Figure 6-1 must be removed in a practical implementation, possibly replacing them with 'link' type entities as in UTMC 07/17's Microsoft Access demonstrator. Also there is an issue of how to deal with the huge number of optional Data Attributes per Data Object, all of which are legitimate but most of which will be missing for any given instance of that object. There is a trade off between 'elegant simplicity' (Figure 6-2) but inconvenient to use, and 'natural' and convenient but very sparsely populated (Figure 6-3)
DataObject ObjectCode ObjectName

DataAttribute AttributeCode AttributeName

ObjectInstance ObjectId

AttributeValue ValueString

.
Figure 6-2 Elegant Simplicity

UTMC-10 Report 2

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

Page 27

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

ExhaustPollution InputTime StartTime Location SourceId + ~20 other generic attributes ConcNO2 ConcNonMethaneHydrocarbons ConcNO ConcOzone BackgroundAirPollution ConcPollutantsWaterRunOff SalinityWaterRunOff AirQualityMonitoringLocation ConcMethane ConcCO ConcParticulateParticles Conc ConcNumericalValue SevereExhaustPollution ConcSO2 ConcTotalHydroCarbons

Parking InputTime StartTime Location SourceId + ~20 other generic attributes NumVacant ParkingDuration Mobility AllCarParksFull SpaceForDisabled ParkingNotAllowed FewAvailable NoInfoAvailable ParkingClass Filled ParkingHeightRestriction ParkingId CarParkFull ParkingSpaceDescription ParkingWidthRestriction + more optional attributes

RampMeterControl InputTime ActionPlans Accident StartTime InputTime Location StartTime CameraInformation InputTime SourceIdInputTime Enforcement Location StartTime + ~20Location other generic attributes Delays SourceIdInputTime StartTime ... + ~20 Location SourceIdInputTime other generic attributes StartTime TrafficEquipment lots more optional generic attributes ... + ~20Location SourceIdInputTime other attributes StartTime ChargesAndFines RampMeterFixedMode lots more optionalgeneric attributes ... + ~20 Location other attributes Flow SourceIdInputTime StartTime RampMeterModeSelectgeneric attributes RampMeterFixedMode lots more optional attributes ... + ~20Location SourceIdInputTime other StartTime RampMeterModeSelectgeneric attributes (~ 100 ?) RampMeterFixedMode lots more optional Many More Tables ... + ~20Location SourceId attributes other StartTime RampMeterModeSelectgeneric attributes RampMeterFixedMode lots more optional attributes ... + ~20Location other SourceId InputTime RampMeterModeSelectgeneric attributes RampMeterFixedMode lots more optional attributes ... + ~20 other SourceId StartTime RampMeterModeSelectgeneric attributes RampMeterFixedMode lots more optional attributes ... + ~20 other Location RampMeterModeSelect attributes RampMeterFixedMode lots more optional ... SourceId RampMeterModeSelect attributes RampMeterFixedMode attributes lots more optional + ~20 other generic RampMeterModeSelect RampMeterFixedMode ... RampMeterModeSelect lots more optional attributes

Figure 6-3 Natural application domain structure

The first model requires exactly 4 tables and wastes no space. An instance of any Data Object is represented by a row in ObjectInstance with a unique identifier and related to DataObject to identify what type of object it is. For each Data Attribute actually present in the given instance there is a row in AttributeValue which relates to DataAttribute to identify what type of attribute it is. The downside is its generic nature, and that accessing a single logical record requires a join of several tables. Also because all values end up in a single column, the datatype for that column must be the least common denominator, a formatted character string. Per attribute check constraints would also be impossible to arrange in the normal way, as would mandatory columns and indices. This model would be difficult for browsers to deal with and ad-hoc queries would need careful construction, also performance would suffer due to the number of joins, datatype conversions and lack of bespoke indices. The second model looks much more natural, there is a single table for each UTMC Data Object, and each has a column for every possible attribute. Given a column per attribute then setting correct datatypes, check constraints and indices is not a problem, once the required behaviour is defined. At present there are about 50 identified Data Objects and the greatest number of attributes for a single object (Service Information) is 163. The number of tables or columns is not a problem in itself, large scale database servers can support many databases, each with hundreds of tables. Permitted number of columns per table is the closest constraint, our Sybase system restricts each table to 250 columns. The downside with the second model is that it seems likely that very many columns will be very rarely used. The second model has been adopted by UTMC-10, there will be one table per Data Object. At present the intention is to implement as tables only those Data Objects required for the UTMC-10 trials, and to create columns for all the possible attributes of each Data Object, although our trials will only use a few of them. This approach represents a compromise on the issue of whether to attempt a comprehensive implementation of the UTMC Data Dictionary at the outset, or to adopt an incremental approach and only implement tables and columns as required. In practice, if no UTMC Function can be found which supplies values for an attribute then perhaps the attribute should be removed. Or
UTMC-10 Report 2 REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1 Page 28

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

maybe only those attributes which are used should be implemented as columns, retaining their definitions in the Data Dictionary for future reference. This would provide a dramatic simplification, and new attributes could be implemented incrementally by simply ALTERing the tables (adding columns). UTMC-10 should gain experience during the trials which will lead to a firm recommendation for the best approach. We have adopted a naming convention illustrated in Figure 6-3, tables and columns will be identified by (fairly) short names, not 3 (or 4) letter abbreviations. Our database is set up to be case insensitive so 'SELECT * FROM ExhaustPollution' is equivalent to 'select * from exhaustpollution'. However table and column names are created in mixed case, using upper case letters to signify word breaks (space is not permitted) for readability.

6.3

Quality: Objects, Parameters and Class

UTMC 07/17 considered the important topic of data quality in relation to UTMC data and proposed a quality architecture somewhat analogous to its Data Objects proposal. It was proposed that every value assigned to any Data Attribute should have an associated Quality Class, which effectively provides information on the accuracy of the supplied value. Furthermore the quality of any given value could be classified according to several different Quality Parameters, each providing an accuracy yardstick for a different aspect of the attribute in question. Thus an instance of an IndividualVehicle object provided by a number-plate recognition system might have a Location attribute assessed as QualityClass B (within 10m) on the measurement_location_precision (LP) scale and QualityClass D (< 1min ) on the measurement_lifetime (LI) scale. UTMC 07/17 identified 21 individual QualityParameters although it was recognised that only a few would be appropriate for each Data Attribute. For convenience the parameters were grouped into 5 distinct families, called Quality Objects. In practice it seems that the Quality Objects are not very useful at all, as Quality Class of each Data Attribute must be assigned on a per Quality Parameter basis.

UTMC-10 Report 2

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

Page 29

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

Figure 6-1 shows the Data-Quality entities designed for the Common Database, based on UTMC 07/17's proposals. It is quite similar to the structure of their Microsoft Access demonstrator, with some minor improvements. Most significant is the addition of a QualityClassScheme entity between QualityParameter and QualityClass, this is felt necessary to support the concept of alternative classification schemes for a single parameter. UTMC 07/17 recognised that this could be required in some cases, for example one Function might need to classify measurement_location_precision in absolute terms (A = ±0.5m, B = ± 5.0m ...) while another might need to classify the same attribute in relative terms (A = ± 1%, B = ± 5...). Also the structure of QualityClass has been inverted so that each class is a row rather than a column. This means it is simple to use as many (or few) classifications as are deemed to be necessary. The model is not restricted to A,B,C,D + U/S as proposed by UTMC/017. Finally, a CodedValues column has been added to DataAttribute so that the entity can behave exactly like an on-line data dictionary for reference purposes. The entire text of the definition and coded values columns in the published documents will be loaded into the da_def and CodedValues columns respectively. Note that the entities which appear empty in this logical model are equivalent to the 'link' tables in the UTMC 07/17 demonstrator. They have 'dependent' relationships, meaning their key fields will be the combined keys of their parents.

DataObject dobj_code dobj_name dobj_desc

is associated with

DataQualityObject has

one of

QualityObject qobj_code qobj_name qobj_desc

DataObjectAttribute described by

QualityParameter qp_code qp_name qp_desc

one of classified according to DataAttribute da_code da_name da_dattyp da_unit da_def CodedValues QualityClassScheme QualitySchemeCode QualitySchemeName

with a single quality classification letter

QualityClass QualityClassCode QualityValueRange

Figure 6-1 UTMC 10 Data-Quality Entities

UTMC-10 Report 2

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

Page 30

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

6.4

Data - Quality: Functions, Implementations, QualityStatements

UTMC 07/17's work was principally involved with metadata and did not make any clear proposal about how actual instances of UTMC data objects should be related to quality objects quantifying the accuracy of the various attributes. How should that be done ? UTMC-10 considered the notion of requiring UTMC functions supplying the Common Database to provide each data object complete with associated quality objects. That would be a massive overhead, each attribute of every data object would need to be accompanied by several quality classifications relating to relevant quality parameters. Furthermore there would be huge redundancy, in most cases quality does not vary on a record by record basis. For example it is likely that every Location attribute provided by a single system would be assigned the same QualityClass on the measurement_location_precision scale. The approach which has been adopted is based on the concept of a QualityStatement which must be associated with every DataObject supplied. That QualityStatement assigns QualityClass codes to each DataAttribute supplied, for relevant QualityParameters. However there is a one-to-many relationship between QualityStatement and instances of data objects (eg Parking), the supplier of Parking attributes only needs to provide one QualityStatement record before supplying thousands of rows of Parking data which reference it.

UTMC-10 Report 2

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

Page

31

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

Function fn_num fn_name is implemented by Implementation SystemId SystemName SystemVersion SystemSupplier relating to a single data object used by this function

DataObject dobj_code dobj_name dobj_desc is associated with

DataQualityObject

has

one of Supplies/consumes data objects in accordance with QualityObject qobj_code qobj_name qobj_desc

QualityStatement consumer supplier QualityStatementId SupplierOrConsumer with supplied/required quality class per data attribute/quality parameter pair

described by CompatibleStatement ActionPlans RampMeterControl InputTime Accident StartTime InputTime InputTime Enforcement Location StartTime StartTime SourceId Location Parking InputTime + ~20Location SourceIdInputTime other generic attributes StartTime SourceId generic NumVacantStartTime attributes + ~20Location other + ~20 Location other ParkingDuration generic attributes NumVacant SourceId NumVacant Mobility + ~20 other generic attributes ParkingDuration SourceId ParkingDuration AllCarParksFull other generic attributes Mobility + ~20 NumVacant Mobility SpaceForDisabled AllCarParksFull ParkingDuration NumVacant AllCarParksFull ParkingNotAllowed SpaceForDisabled Mobility ParkingDuration SpaceForDisabled FewAvailable ParkingNotAllowed AllCarParksFull Mobility ParkingNotAllowed NoInfoAvailable FewAvailable SpaceForDisabled AllCarParksFull FewAvailable ParkingClass NoInfoAvailable ParkingNotAllowed SpaceForDisabled Filled NoInfoAvailable ParkingClass FewAvailable ParkingNotAllowed ParkingClass ParkingHeightRestriction Filled NoInfoAvailable FewAvailable Filled ParkingId NoInfoAvailable ParkingHeightRestriction ParkingClass ParkingHeightRestriction CarParkFullParkingClass ParkingId Filled ParkingId ParkingSpaceDescription CarParkFull ParkingHeightRestriction Filled CarParkFull ParkingWidthRestriction ParkingSpaceDescription ParkingId ParkingHeightRestriction ParkingSpaceDescription + more optional attributes ParkingWidthRestriction CarParkFull ParkingId ParkingWidthRestriction + more optional attributes ParkingSpaceDescription CarParkFull + more optional attributes ParkingWidthRestriction ParkingSpaceDescription + more optional attributes ParkingWidthRestriction + more optional attributes DataObjectAttribute

QualityPhrase states quality class for this data attribute

QualityParameter qp_code qp_name qp_desc

classified according to one of DataAttribute da_code da_name da_dattyp da_unit da_def CodedValues

QualityClassScheme QualitySchemeCode QualitySchemeName

with a single quality classification letter

with this quality classification

QualityClass QualityClassCode QualityValueRange

Figure 6-1 UTMC-10 Data-Quality Integration

UTMC-10 Report 2

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

Page 32

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

Figure 6-1 shows how instances of Data Objects will be related to Quality Objects in the Common Database. Each QualityStatement comprises a number of QualityPhrases, one per attribute per quality classification. It is recognised that every access to the Common Database should be in support of an identifiable UTMC function. UTMC-10 takes the view that nothing gets into or out of the Common Database except on that basis. However UTMC functions will be implemented in various ways, and possibly several applications will be involved, each implementing the function only partially. These systems may supply and /or require data with different quality 'signatures', each of which should be expressed as a QualityStatement. The Implementation entity in Figure 6-1 supports that concept, and provides SystemName, SystemVersion, SystemSupplier attributes for the details. Thus if two different suppliers both offer systems which support the Traffic Signal Control (F100) function, they would each provide a QualityStatement describing the quality of each attribute their system supplies. It is also possible that a single vendor might offer different systems which implement similar functionality, but with a superior or inferior quality signature. In that case there would simply be a separate QualityStatement record for each system, differentiated by its SystemName and SystemVersion fields and relating to the associated set of quality data. In the case of data suppliers their QualityStatements can be regarded as contracts, an assurance that the attributes specified will meet or exceed the stated quality classifications. For consumers of data their QualitySatements represent a minimum specification for data they need from the Common Database. An obvious service which the Common Database could provide is to match compatible consumers and suppliers of data, and this is supported by the CompatibleStatement entity. Then consumers could simply ensure their quality needs are met by adding a subquery:- 'where QualityStatementId in (select SupplierId from CompatibleStatement where ConsumerId = MyStatementId)'. The Implementation entity could also support an effective mechanism for setting minimum quality standards for UTMC compliant systems. It could be argued that the quality model renders minimum standards unnecessary as all data suppliers must quantify the quality of their data explicitly and all consumers can specify their own quality requirements. However it is proposed that UTMC should exploit the Common Database structure by adding special 'QualityTemplate' records to the Implementation table. These would be 'pseudo' implementations with a SystemSupplier (say) of 'UTMC' and a suitable SystemName (eg 'Traffic Signal Controller for dense urban environment' ), possibly qualified by a SystemVersion ('Level 2'). The related QualityStatements, one for each DataObject involved, would define a minimum quality standard acceptable to UTMC in terms of the per attribute quality classifications. All attributes referred to in the statements would be deemed to be necessary, suppliers could not avoid meeting the specified quality class by omitting the attribute from supplied data objects. These QualityTemplate records would never be referenced directly by actual instances of data objects, but would be used to validate new Implementations for would-be suppliers of UTMC compliant systems. Then an authority could specify their requirements '...UTMC compliant system meeting the 'Traffic Signal Controller for dense urban environment', 'Level 2' QualityTemplate...'. Multiple suppliers could bid, and maybe offer compliance to 'Level 1' at half price.

UTMC-10 Report 2

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

Page 33

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

6.5

Access Control

Our discussions with potential users of the Common Database highlighted a requirement for controlling access of clients to the data. The Common Database will be set up with suitable login names, database users and groups to allow flexible and effective access control to UTMC tables. Normally groups are set up which are granted specific permissions on individual tables, thus members of 'TrafficControlSuppliers' could be granted write permission on TrafficEquipment and TrafficSignalPlans, while members of 'ParkingSystemSuppliers' might be granted read-only access. It is likely that all clients may be granted permission to create (and own) database views to suit their own requirements, and that system owned views will be used to restrict or hide certain tables and columns if necessary. Once again, experience during the trials should help us provide firm recommendations on the right level of access control and the best mechanisms for providing it.

6.6

Auditable Items

The UTMC-10 demonstration Common Database for the lab and on-street trials will include several effective features which support auditing.:• • • Each row inserted will automatically include date/time, server login name, and database user name Each row will have an update counter column which will automatically increment on modification The database audit system will be set up to record client login/logout as well as errors etc.

At the application level, it would be possible to monitor each individual access as it passes through the Application Server 'Middle Tier'.

6.7

Referential Integrity

This refers to mechanisms intended to ensure that rows of one table referenced by another (via a 'foreign key') actually exist. Thus every row in QualityParameter should have a 'parent' in QualityObject referenced by the qobj_code column. Referential integrity ensures that QualityObjects may not be deleted while QualityParameters exist which refer to them (ie no 'orphans'). The UTMC-10 CASE tool is set up to create a database with 'declarative referential integrity' which will implement every relationship shown in the diagrams as a 'foreign key constraint' to enforce the intended references. Declarative integrity does not use database triggers. However the issue of referential integrity between instances of Data Objects has hardly been addressed until now. UTMC 07/17 approached data quality in terms of accuracy and confidence limits and apparently did not consider referential integrity. Given the number of tables and columns, there are probably a large number of valid cross-references and guaranteeing referential integrity would be very complex, certainly beyond the scope of declarative integrity constraints. However an insert trigger could be designed for each table which could test that certain conditions are met before permitting the insert. It

UTMC-10 Report 2

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

Page 34

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

is not proposed to develop these in the trial systems unless actual problems arise which could be solved in this way.

6.8

UTMC-10 Common Database Internals

The UTMC-10 data structures discussed so far have all been 'business domain' entities, as is normally the case in logical designs. However structures must also be designed to support the Common Database system itself and these are referred to in the CASE tool as 'UTMC-10 Internals'. These will include tables for configuration parameters, exception logging, client contact details etc. Also in this category are tables to support the active notification (Push) by the system of data to clients which have expressed an interest by subscription. UTMC-10 will produce a recommended Common Database Specification document which will include all the required 'UTMC-10 Internals'.

UTMC-10 Report 2

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

Page 35

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

7

Objectives and Definition of Laboratory Trial

7.1

Introduction

This section identifies the objectives of the UTMC-10 laboratory trial, and defines the software system that will be developed.

7.2

Objectives

The overall purpose of the laboratory trial is to achieve, in a minimal time framework, a model UTMC system that incorporates and demonstrates the technology proposed in Section 5. Objectives of this work are: • To assess the capabilities of RDBMS: its ability to represent UTMC data, and offer the required level of service. Any pitfalls encountered will be noted, with special attention devoted to those that would be solved by use of an object DBMS. To identify any database features employed that have vendor-specific interfaces – in case of any effect on the ability of a particular database vendor to support a system of equivalent functionality. Conversely, standard database features that are found to give significant benefit will be noted for recommendation. To assess the value of 3-tier architecture. The middle tier allows standardised communications and provides enhanced service, but has development cost. We will examine the service benefits arising from the middle tier, weighed against the cost. This assessment will continue in the forthcoming onsite trials. To assess the cost of development constraints on client application development in adhering to the interfaces of the common database demonstrator. For example, CORBA IIOP will be the standard protocol to be used by UTMC applications for access to the common database. We will examine the ease of implementation for a UTMC application in a very short time scale. To assess the reliability of the proposed technology. To assess the technical suitability of specific client interfaces for the following services: • • • • one-off requests to read data from the common database, requests to write data into the common database, (subscriptions to data and asynchronous notifications arising from subscriptions will be assessed in live trials.)

•

•

•

• •

To examine middle tier design and implementation, not with the objective of producing a specification of these, but as an example of how the desired service can be provided in the proposed architecture.

UTMC-10 Report 2

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

Page 36

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

•

To attempt to adhere to the UTMC Data Dictionary. This should generate some useful feedback on the practical application of the dictionary, and may highlight specific problems. Useful application data may have no corresponding data dictionary item, or representation may be awkward. To examine the practical implementation issues arising from support of the UTMC Data Quality model. To assess the overall performance. (This aspect is limited in the laboratory trial, but the live trial will provide a more demanding application: there will be special interest in the handling of SCOOT data. Initially, we will use the standard common database interface to insert the SCOOT data. The standard interface will incur the overhead of middle tier processing in Java, and we may find that the overhead is too high for real-time SCOOT. In this case we will broadly quantify the “breaking point” and investigate both specialised direct interfaces and improvement of performance in the standard interface). To identify any performance (or other) issues arising from high volume concurrent access to the common database. (In the live trial we can investigate this further by performing queries during active SCOOT data upload). To include in the laboratory demonstrator sufficient fragments of a network model to support the data extracted from applications owned by Glasgow City Council: the BusTime system, and automated number plate recognition (ANPR). We will be able to start the evaluation of central management of this network data and the benefit in reducing maintenance costs for individual legacy applications. To investigate inter-application integration. As highlighted in Section 3, UTMC users can also foresee considerable benefit in sharing data dynamic between different UTMC functions. As a first concrete realisation of this idea, the laboratory demonstration will include a mode-comparison of journey time between bus and car, made possible by the common database. The live on-site trials will continue this policy with other concrete examples.

• •

•

•

•

The programme of assessment will continue into the forthcoming live trials. Evaluation will be extended on issues such as cost against service benefit, and diversity of underlying data representation. Other questions left unanswered in the laboratory phase will be addressed, for example on institutional issues.

7.3

Laboratory Trial System Definition

The laboratory trial will explore a mini UTMC system, with common database implementation, and client applications integrating two real urban traffic functions.

UTMC-10 Report 2

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

Page 37

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

ANPR Filter 1 ANPR Filter 2

BusTime filter

Mode Comparison

Server Application: Sybase Jaguar CTS SQLStatement interface

RDBMS: Sybase Adaptive Server Enterprise
Figure 7-1 Laboratory Trial UTMC System

Server Software The laboratory trial will require development of a database, managed by a RDBMS. This database will support the UTMC data and quality model, following the design presented in Section 6, but will contain only those entities required by other parts of the laboratory demonstration software, and not the full data dictionary. A middle tier server application will support requests from clients, and implement the services via connections to the RDBMS. It will support the following services: • • one-off requests to read data from the Common Database, requests to write data into the Common Database,

The server software developed in the lab trial will also be used as the basis of the on-site trials (with any necessary modifications arising from the evaluation of the laboratory trial). Subscriptions to data and asynchronous notifications (push) arising from subscriptions will be implemented for the live trials. The interface used to submit SQL over CORBA is presented in Appendix A. The component implementing this interface will be written in Java and use JDBC to connect to the RDBMS, with the Sybase JConnect driver.

UTMC-10 Report 2

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

Page 38

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

Data Supply The laboratory trial requires two source simulation applications to feed data, from the Glasgow City Council-owned ANPR and Bus Time systems, into the Common Database. The sources of the data will be files, acquired and supplied by Glasgow City Council. These applications will use time information in the files to simulate real-time operation. They will test the schema of the Common Database, and the proposed interface mechanism. The ANPR data comes from two separate sensors at either end of a traffic corridor. There will therefore be two instances of the ANPR filter application running, one configured to represent each sensor. The Bus Time filter application will be a single instance to simulate a central Bus Time UTMC application. The applications will use the CORBA SQLStatement interface to perform SQL insert statements. They will use the ORB included in Java 2 to manage the CORBA invocations. The applications will be written in Java.

Data Consumer The laboratory demonstration requires a simple client application to consume the Common Data. This application will be minimal: it will periodically read individual vehicle data, then calculate and textually report journey times by bus and by car. It will however test the main interfaces offered by the Common Database: the CORBA SQLStatement interface will be used to perform SQL select statements. Again Java 2 will be used.

7.4

Evaluation

UTMC-10 will produce a written evaluation of the laboratory trial, addressing each of the objectives stated in Section 7.2. As identified, in some cases the evaluation must be preliminary, with a mature evaluation emerging only after the live trials. The laboratory evaluation will also have an impact on the development of the live trials.

UTMC-10 Report 2

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

Page 39

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

8

Live On-Street Trials

8.1

Leicester Live On-Street Trial

Objectives Building on the laboratory trial, the Leicester on-street trial adds the following new specific objectives: • • To investigate the benefit of a common database approach to the integration of two systems, compared to the existing proprietary connection between these two systems. To investigate ‘push’ interfaces. One of the main justifications for the proposed common database architecture is the provision of ‘push’ over an industry standard protocol. The following details will be of interest: • • • • • • • The IDL design Underlying implementation issues Performance

To investigate ‘push’ with flexible filter conditions. To map another set of real application data to the UTMC Data Dictionary form, and the proposed schema, and to have real applications working with this mapping. To measure the performance of the ‘push’ interface. To investigate the performance demands that SCOOT places on the common database.

Definition The Leicester on-street trial will be a mini-UTMC system, providing an alternative implementation of an existing proprietary link between the following systems: • • SCOOT, the UTC system for Leicester; and, Airviro, a pollution and network modelling system.

At present the SCOOT system sends messages containing pollution and traffic flow information to the Airviro system, via a dedicated RS-232 serial communications line. The mini-UTMC system will employ a common database approach to this integration - SCOOT messages will instead be sent to the common database, with the Airviro system becoming one of any number of other systems subscribing to this data. Since no software development can be undertaken to extend either the SCOOT or Airviro systems, their existing serial line interfaces will be used by two UTMC compliant adapter applications. This solution is shown in Figure 8-1. The bold type indicates existing systems; all other applications are custom development by UTMC-10.
REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

UTMC-10 Report 2

Page 40

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

LCC UTC System (SCOOT) RS232

Airviro System RS232

SCOOT Adapter

Pollution Alarm

Airviro Adapter

insert

subscribe

notify

subscribe

notify

Common Database

Figure 8-1 Leicester On-Street UTMC Trial

A SCOOT Adapter application will be developed that reads SCOOT messages from the existing serial line interface and converts them into the appropriate UTMC data dictionary representation for insertion into the Common Database. An Airviro Adapter will subscribe to this data so that it receives notifications of the inserts made by the SCOOT Adapter. The Airviro Adapter will convert each notification from the UTMC representation back to the serial line format expected by the Airviro System. The SCOOT messages processed will include at least the pollutant levels message (‘A02’) which contains measured levels of three gases plus temperatures, at named detector sites. SCOOT real-time control messages are not relevant to Airviro. To demonstrate the benefit achieved by the Common Database approach, a Pollution Alarm application will also subscribe to the data inserted by the SCOOT Adapter and generate an alarm message when a particular exhaust pollutant exceeds a given threshold. This will also demonstrate the filtering capability of the subscription facility since the Pollution Alarm will only subscribe to data exceeding the threshold value.

8.2

Glasgow Live On-Street Trial

Objectives The Glasgow on-street trial will provide further useful feedback, adding to the laboratory and Leicester trials, to inform the final deliverables of UTMC-10. In addition to a general re-enforcement of previous
REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

UTMC-10 Report 2

Page 41

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

findings through concrete implementation experience, the Glasgow trial has the following specific new objectives: • • To investigate the real benefit of a completely new integration (through the common database) of two previously unconnected systems. To achieve a common database representation of core data entities from UTC, the most important application in UTMC. As in the other trials, the process of mapping real application data to the UTMC Data Dictionary form, and the proposed schema, will be instructive. To show how common database integration can be used to drive application action. A ‘push’ will trigger business logic in a UTMC application; indeed the filter conditions on the subscription can also be viewed as encapsulating business logic. The Glasgow trial will investigate the option of UTC action arising directly from common database notification.

•

Definition The Glasgow on-street trial will be a mini-UTMC system, integrating two existing applications owned by Glasgow City Council (GCC): • • CITRAC, the UTC system for Glasgow Car Park Guidance, which displays car park availability information on VMS.

GCC Car Park Guidance System GCC UTC System(CITRAC)

CarPark Filter Strategy Selection UTC Controller

Common Database

Figure 8-2 Glasgow On-Street UTMC Trial
REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

UTMC-10 Report 2

Page 42

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

At present there is no integration between the two systems. However, car park exit figures would be a useful input to UTC as a supplement to flow detector data. In the trial, real exit rates from the Car Park system will be supplied to the common database and used to influence the plan selection for UTC. The overall system configuration is shown in Figure 8-2. The bold type indicates existing systems; all other applications are custom development by UTMC-10. A filter will transform the Car Park Guidance system into a UTMC client. This filter will have a custom link to extract data from Car Park Guidance, and will then supply the data to the common database through the SQLStatement interface. A “strategy selection” application will create a subscription to receive car park exit figures as they become available. On receipt of a high exit rate, the application will apply logic and may determine that a change of traffic signal plan is appropriate to counter congestion on the car park exit route. If so, a plan selection will be written back to the common database. When the exit rate returns below the threshold, the plan will be withdrawn, and the UTC will revert to its timetabled plans. A third client application will simulate a UTMC-compliant UTC system. This application will subscribe to plan selections, and therefore be notified when the strategy selection decides to change the plan. The UTMC client will then connect to the real UTC system and supply the instruction to change plan, which will be actioned on-street.

UTMC-10 Report 2

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

Page 43

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

9

References

1

Urban Traffic Management and Control (UTMC) Systems Technical Issues Draft for Consultation 1996

UTMC-10 Report 2

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

Page 44

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

10

Glossary

ANPR API CASE CCTV COM CORBA DBMS GIS IDL IIOP JDBC ODBC ODBMS OLE OMG ORB RAID RDBMS SCOOT SQL UTC UTMC VMS WWW

Automated Number Plate Recognition Application Programming Interface Computer Aided Software Engineering Closed circuit television Component Object Model Common Object Request Broker Architecture Database Management System Graphical Information System Interface Definition Language Internet Inter-ORB Protocol Java Database Connectivity Open Database Connectivity Object Database Management System Object Linking and Embedding Object Management Group Object Request Broker Redundant Array of Inexpensive Discs Relational Database Management System Split Cycle Offset Optimisation Technique Structured Query Language Urban Traffic Control Urban Traffic Management and Control Variable Message Sign World Wide web

UTMC-10 Report 2

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

Page 45

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

A

Appendix: CORBA SQL Query

A.1

Query.idl

/*** This file defines the interface to the query mechanism for UTMC-10 CORBA clients. */ #include <TabularResults.idl> module Query { /** The <code>CorbaSQLException</code> structure mimics the java.sql.SQLException class contents. It will not actually be raised as an exception, but be included as a member of the <code>CorbaSQLExceptions</code> exception */ struct CorbaSQLException { string detailMessage; string SQLState; long errorCode; }; /*** The <code>CorbaSQLWarning</code> structure mimics the java.sql.SQLWarning class contents. It will not actually be raised as an exception, but be included as a member of the <code>CorbaSQLWarnings</code> output parameter of the method calls defined below. */ struct CorbaSQLWarning { string detailMessage; string SQLState; long errorCode; }; /*** The <code>CorbaSQLExceptions</code> exception comprises a sequence of <code>CorbaSQLException</code> structures. The exception will be raised if one or more <code>java.sql.SQLException</code>'s are thrown within the method calls. */ exception CorbaSQLExceptions { sequence < CorbaSQLException > exceptions; }; /*** The <code>ClientIdentificationException</code> exception will be raised if problems are encountered while trying to identify the incoming client connection. */ exception ClientIdentificationException REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

UTMC-10 Report 2

Page

46

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

{ }; typedef sequence < CorbaSQLWarning > CorbaSQLWarnings; typedef sequence < long > UpdateCounts; interface SQLStatement { /** Executes the statement provided as the input param <code>statement</code>.<p> A 'result' is either a ResultSet for a query statement or an update count for non-query statements. Normally a single result will be obtained, however if the statement is a complex SQL script, then it may return multiple 'results'<br> The <code>resultSets</code> param contains the 'results' that returned ResultSets(i.e. rows of data).<br> The <code>updateCounts</code> param contains the update count for each 'result' that returned an update count (usually an UPDATE statement or DDL statement).<br> The <code>warnings</code> param contains any SQLWarnings that were generated while executing the statement. */ void execute ( in string statement, out TabularResults::ResultSets resultSets, out UpdateCounts updateCounts, out CorbaSQLWarnings warnings ) raises (CorbaSQLExceptions, ClientIdentificationException); }; };

UTMC-10 Report 2

REQUIREMENTS FOR DATABASE IMPLEMENTATION ISSUE 1

Page 47