In the Real WorldWhen and Why Learn Relational Theory?

In the Real World When and Why Learn Relational Theory?

A corollary of the Law of Preservation of Matter is: "Everything Has to be Somewhere." Most books about Microsoft Access deal with relational database design theory in the first few chapters, and this latest of the eight editions of Special Edition Using Microsoft Office Access is no exception. Prior editions categorized this subject as an "Advanced Access Technique" that appeared toward the end of the book.

Understanding relational database design requires familiarity not only with the objects that make up the database, but with the use of these objects. After you're comfortable with table and query basics, have a feeling for form and report design, and gained an introduction to Web-based database applications, you're probably better prepared to delve into the arcana of relational algebra, such as normalization rules. That's why the previous editions didn't include this chapter in the first part of the book.

After you've gained experience working with the sample relational databases in Access and this book, their design appears intuitive and entirely logical. Experienced database designers envision even the most complex business processes as collections of related tables. So, it's somewhat surprising to many that Dr. Codd's relational database theory originated in 1970, well after the development of complex network and hierarchical architectures. If you're embarking on the road to relational database development, however, you're not likely to find database design topics at all intuitive.

An argument in favor of moving the relational database design topic to the beginning of the book is that many readers decide to use Access to accomplish a specific task that involves creating a special-purpose database. In this case, you advance through the book's chapters using the sample databases as examples rather than learning models. If you're using Access as a learning tool, starting with database design and implementation also makes the structure and relationships of the tables in the sample databases of the book more meaningful.

Increasing sales of desktop and client/server RDBMSs has spawned a multitude of books ranging from introductory tutorials to graduate-level texts on relational database design theory and practice. Michael J. Hernandez' Database Design for Mere Mortals, Second Edition (Addison-Wesley, ISBN 0-201-75284-0), subtitled "A Hands-On Guide to Relational Database Design," is an excellent resource for folks who want more than this chapter offers in the way of database design guidance. Mike and John L. Viescas, a well-known Access writer and developer, are co-authors of SQL Queries for Mere Mortals (Addison-Wesley, ISBN 0-201-143336-2), which delivers thorough coverage of SQL SELECT queries for Access and SQL Server users.

You need a basic knowledge of relational database design techniques to understand the structure of XML documents and schemas generated from Access tables and queries. XML is today's lingua franca for interchanging data between systems, not just productivity applications. Almost all software publishers offer XML document import and export, and most support XML schema definition (XSD) language.

Access 2003's upgraded XML export feature generates XML documents and corresponding XML schemas from multiple, related tables. (Access 2002 was limited to exporting flat XML documents from a single table or query). For example, you can export all or selected (contiguous) records of the Orders table and specify that the XML document and its schema includes all related Order Details records (see Figure 4.14). You also can export lookup tables that supply data for subdatasheets, but doing this can greatly increase the size of your XML document.

Figure 4.14. Access 2003's new Export XML dialog lets you generate a hierarchical XML document that includes elements for records in base (Orders) and related (Order Details) tables.

For more information on Access 2003's new XML export features, see "Exporting Tables and Queries to XML and HTML," p. 954.

The XML elements for the Order Details records are called child nodes of the Order parent node (see Figure 4.15). The noNamespaceSchemaLocation="OrdersOrderDetails.xsd" attribute specifies the schema file associated with the document and is assumed to be in the same folder. The xmlns:od="urn:schemas-microsoft-com:officedata" XML namespace declaration indicates that the document originated from an Office 2002 or 2003 member.

Figure 4.15. This exported XML document for the first Order record in the Northwind Orders database includes child nodes for its three Order Details records, only one of which is visible here. Spaces aren't allowed in XML element names, so Access and SQL Server substitute _X0020_ for spaces in table and field names.

The exported XML schema contains a complete description of the relationship between the tables and their fields (see Figure 4.16). Access 2003's XML schema generation feature depends on information from the Relationships window. You can use the schema to import the XML document into other XML-enabled Office 2003 members automatically, because the schema also includes the xmlns:od="urn:schemas-microsoft-com:officedata" namespace declaration. For example, if you import the Order10248.xml document into an empty Access 2003 database, the document/schema combination generates new Orders and Order Details tables, creates indexes, and establishes the relationship between the tables. Importing the sample document into Excel 2003 automatically generates an Excel list that displays data from the three Order Details records with duplicated Orders data in each row.

Figure 4.16. The XML schema for Figure 4.15's document, only a small part of which is shown here, contains all the information needed by Access 2003 to generate new Orders and Order Details tables in another database and populate the tables with the contents of the associated XML document.

Note

Copies of Order10248.xml, OrdersOrderDetails.xsd, an XML worksheet with an imported list (ExportXML.xls), and a Word 2003 document created from Order10248.xml are in the \Seua2003\Chaptr04 folder of the accompanying CD-ROM.

For the details of importing XML documents into Access and other Office 2003 members, see "Importing XML Data to Tables," p. 983.

If you're serious about getting the most out of Access 2003, consider purchasing a copy of Mike's book or browse the bookstore shelves for titles on relational database design. Your investment will pay handsome dividends when you're able to create the optimum design to start, instead of attempting to restructure a badly designed database after it's grown to 20 or 30 tables containing thousands or millions of rows.