Appendix B: The Sample Database

This appendix contains the initial version of the demographics database used throughout this book in examples. Any changes made throughout this book are not necessarily reflected in this appendix unless absolutely necessary. In other words, this is the clean version of the demographics database.

All diagrams are drawn in Erwin, a database modeling design tool, using the Information Engineering (IE) methodology. The database used to write this book was initially an Oracle10 g Database and an SQL Server Database. Figures B-1 and B-2 show the logical and physical relational ERD model, respectively.

Figure B-1: The logical Entity Relationship Diagram (ERD) for the demographics schema

Figure B-2: The physical Entity Relationship Diagram (ERD) for the demographics schema

Scripts for creating schemas in Oracle and SQL Server databases, as used in this book, can be found at www.oracledbaexpert.com/oracle/beginningxmldatabases/index.html.

The database is too large to include actual table insertions in this book. Scripts for the Oracle and SQL Server database table insert commands can be found at www.oracledbaexpert.com/oracle/beginningxmldatabases/index.html.

Depending on what version of each database engine you are using, you may have to alter script syntax to make the script function properly.

Figure B-3 shows an object equivalent model for the relational model ERD. The object model is used as a basis to create an initial XML document object for the entire demographics database.

Figure B-3: The XML object model for the demographics schema

There are various ways to represent the object model for the demographics schema. However, the model shown in Figure B-3 should be familiar when compared with the relational model shown in Figure B-1. In general, the object model for data removes types and many-to-many relationships from the relational model. Types are removed by creating multiple collection contained classes (the language and occupation specialized classes are collections within the population class). The many-to-many relationship entities of POPULATIONBYLANGUAGE and POPULATIONBYOCCUPATION are catered for by the inheritance from the population class into the four specialized population classes for the regions , countries , states, and cities.

The XML document for the demographics database is much too large to include in this book in printed form. Both scripts to create the XML document (using Oracle Database PL/SQL) and the XML document are available at www.oracledbaexpert.com/oracle/beginningxmldatabases/index.html.

The XML version of the demographics database (demographics.xml) does not include languages and occupations because the XML documents are simply too large.

A script was used to generate the demographics database as a single XML document from an Oracle database. This script is written using Oracle Database PL/SQL, and will function only in Oracle Database. However, PL/SQL is a cinch for any programmer to understand, and this script should be easily recoded into any programming language for XML document generation from either SQL Server or DB2. This script can also be found at www.oracledbaexpert.com/oracle/beginningxmldatabases/index.html.