Interoperability

ODBC

Standard desktop applications such as Microsoft Excel are very popular with non-technical users because they offer very powerful data manipulation features combined with an accessible and intuitive user interface. With so much functionality readily available, it makes little sense to try to duplicate it in context-specific database applications. It is much more efficient to provide a connection which allows data to be queries from the database and then processed further an application such as a spreadsheet. This approach also takes advantage of the existing skills of end users without the need for any new application-specific training.

Open Database Connectivity (ODBC) is a standard interface for programs such as desktop applications to access databases. ODBC provides a driver as a translation layer between an application and the DBMS. The application uses standard ODBC functions, and the driver passes the appropriate query to the DBMS. The existence of the ODBC standard means that all vendors of desktop software can provide support for the standard, and so offer a high degree of connectivity without having to develop proprietary interfaces.

Microsoft Excel comes equipped with ODBC drivers for major database vendors such as Oracle, and drivers are readily available for download on the Internet for any more obscure platforms. This week's practical exercise takes you through the process of setting up and using a database connection in Excel.

XML

Another current technology that provides a simple and flexible mechanism for transporting relational data is eXtensible Markup Language (XML). XML can be used to structure any type of data in text form by using markup tags in the same way that HTML tags identify the elements of a Web page.

Because XML is structured text, it can be produced and consumed very easily by any software system. It also has the added advantage that its structure can be validated so that ill-formed data can be rejected. By defining a set of XML tags specific to a particular domain, structures of arbitrary complexity can be built which correspond directly to relational structures.

For the above reasons, XML makes an excellent communication medium. However, as a storage medium it suffers from many of the same problems as standard files storage, and cannot compete with relational databases for efficiency and flexibility.