The CD-ROM product that is included in the back of The Data Model Resource Book, Revised Edition, Volume 1 (which needs to be licensed separately) provides all the SQL scripts needed to implement the data models described in the book, electronic versions of the diagrams contained in the book, and numerous reports with information about the models in the book. The CD-ROM in the back of the Volume 1 book also contains demos to illustrate what you can expect on the full-blown CD-ROM and includes directories \v1 demo and \v2 demo that include a sample of SQL code for a single data model from Volume 1 and for a single data model from Volume 2. The full-blown Data Model Resource CD-ROM Volume 1 product contains the SQL, diagrams, and reports for all the models in the Volume 1 book. The contents of this directory may be unlocked by contacting John Wiley and Sons via phone (800) 825-8763 or online (http://silverston.wiley.com).

Both the demo and the full-blown CD-ROM include SQL scripts to implement the models, reports describing the models, and electronic versions of the data models. SQL Scripts are included that can be run in Oracle, SQL Server, and generic ODBC scripts that are included for use with other relational database management systems (RDBMSs). These SQL scripts may be used to either build a database or reverse-engineer the models into a CASE tool for further analysis and modifications.

The reports show a great deal of information and cross-referencing on the subject data areas, entities, attributes, tables, and columns. Electronic versions of the data model diagrams are included in Visio format and JPEG format. The JPEG files allow you to view all the models in an electronic manner. The Visio files allow you to modify the data model diagrams for your own purposes, if you have Visio 2000 software.

Within each of the root directories are five directories: three directories for each of database platforms, one for reports (\reports), and one for the diagrams (\data model diagrams). The database platform directories are \oracle, \sqlserver and \odbc and contain subdirectories with SQL scripts for Oracle, Microsoft SQL Server, and generic OBDC scripts. The subdirectories within each database platform directory correspond to the logical data models, data warehouse data models and star schema design models described in this book. The \logicaldm subdirectory contains the scripts to implement the logical data models that are part of the corporate data model described in Chapters 2 through 9. The \datawarehouse subdirectory contains scripts to build the sample enterprise data warehouse described in Chapter 11. The \starschema subdirectory has the scripts to build the star schemas described in Chapters 12 through 14.

Within each of the subdirectories can be found files that contain the actual SQL code needed to build the described models. The files with a .tab extension can be used to build all the tables and referential integrity constraints for the model. The files with an .ind extension contain the SQL code to build all the indexes. The files with a .drp extension contain the SQL code to drop the tables that have been built. The SQL Server and ODBC directories contain additional .tab scripts that allows table to be built without referential integrity constraints (these have a "nori" before the filename extension, for example "sqlservermfgnori.tab").

Using the scripts provided on the CD-ROM is quite simple. They can be used immediately if no changes are going to be made or they could be copied to a working directory on a hard drive or file server so they can be edited before execution. In either case, the scripts are ASCII files that can be loaded and executed from the standard SQL interface for the database selected (e.g., SQL*Plus for Oracle, SQL Server Query Analyzer for MS SQL Server). Be sure to execute the files (to build tables) before the .ind file (which builds indexes).

Most modeling tools have a reverse-engineering feature, which allows the extraction of object definitions from the database into the CASE tool. So once the models have been built in the target database, tools such as Oracle Designer/2000, ERwin, or StarDesignor can be used to reverse-engineer the definitions for further analysis or reengineering. Many popular CASE tools even have the ability to reverse engineer directly from the SQL scripts.Notes Specific to Platforms

The following notes pertain to the various platforms such as Oracle, SQL Server, and ODBC upon which the SQL scripts are run

The scripts have been designed for an Oracle 7.3 platform and fully tested to make sure the scripts run on a Oracle 7.3.4 platform. These scripts should be upward compatible with Oracle 8 and higher levels of Oracle, depending on Oracle’s upward compatibility standards. Since Oracle7 databases only support 30 characters for entity names, entities that are greater than 30 characters have been truncated to 30 characters by shortening the name from the end of the string until the entity name is 30 characters.

The index scripts (.ind suffix) generate indexes for foreign keys, since the primary keys already have indexes from the primary key alter table statements in the .tab scripts.

The SQL Server scripts were designed and fully tested on a SQL Server 7.0 database.

The entity and table CASE was changed in the SQL Server scripts to CASE_HEADER because CASE is a keyword in SQL SERVER. Also in PRICE COMPONENT, percent is a reserved SQL Server work and therefore this was changed to price_percent.

SQL Server does not generate unique indexes when creating referential integrity constraints and therefore the index scripts (.ind files) contain unique index creation statements on primary keys and non-unique index creation statements on foreign keys.

The drop table script will not drop all the tables in SQL Server since the referential integrity constraints prohibit dropping the table after the referential integrity constraints are in place. The DBA needs to remove the referential integrity constraints in order to use the drop table scripts (.drp). Additional scripts are added for SQL Server that end with "nori.tab" (no referential integrity table scripts) that allow creation of tables without the referential integrity constraints.

ODBC scripts were created that represent general, common formats for SQL scripts that may be used for any database that supports ODBC standards and include very common datatypes such as NUMERIC or VARCHAR. The scripts were generated for version 2.0 ODBC drivers and should be upward compatible Version 3.0 ODBC compliant database management systems.

The Volume 1 demo files shows a sample of SQL scripts, reports, images for one diagram from Volume 1, namely Figure 2.6a Common Party Relationships. They are stored in the \v1 demo directory. These directories have subdirectories for the supported databases, for some sample reports, and for sample diagrams. The database directories such as \oracle, \sql server and \odbc do not have subdirectories for logicaldm, datawarehouse, and starschema (as does the fully licensed version), because only a sample of a logical data model diagram is provided.

The Volume 2 demo files shows a sample of SQL scripts, reports, images for one diagram from Volume 2, namely Figure 4.7 Health Care Delivery.

If there is more than one relationship from an entity that will inherit the same foreign key from both relationships, the foreign key column names are changed to distinguish the relationships. For example PAY_CHECK has relationships to two roles of INTERNAL ORGANIZATION and EMPLOYEE. Both of these entities have keys of party_id and role_type_id. The column names are therefore employee_party_id, employee_role_type_id, int_org_party_id, and int_org_role_type_id.

The ORDER, SIZE and UNION tables were changed to an ORDER_HEADER, SIZE_FEATURE, and UNION_ORG tables in the SQL scripts in order to not use the reserved work ORDER, SIZE, or UNION.

The data constructs in The Data Model Resource CD-ROM can be enhanced with additional industry specific constructs, using the Volume 2 industry downloads. These contain an electronic version of the industry data models that are in The Data Model Resource Book, Revised Edition, Volume 2. Each industry model contains electronic versions of the diagrams, reports, and SQL scripts to implement the eight industry models from Volume 2 on various database platforms (same as Volume 1). These industry models can be used to further expand the data model constructs in the Volume 1 CD-ROM by including industry applicable constructs for manufacturing, telecommunications, health care, insurance, financial services, professional services, travel, and e-commerce. Please refer to the Volume 2 book or the Volume 2 readme file for additional information.