Volume 2 Electronic Download Contents & Information

Contents of the Industry Electronic Products

The electronic Volume 2 downloadable products, as well as the volume 2 demo, include SQL scripts to implement the models, reports describing the models, and electronic versions of the data model diagrams. SQL scripts are included that can be run in Oracle and SQL Server. Generic ODBC scripts are also 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, corresponding figures for 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, using either a browser or any program that can open JPEGs. The Visio files allow you to modify the data model diagrams for your own purposes, if you have purchased and installed Visio 2000 software.

Within each of the root directories are five more directories, 3 directories for each of database platforms, one for reports (\reports), and one for the diagrams (\data model diagrams). This structure is the same on the demo as well as the installation of the full products.

Each of the three database-named directories contains SQL scripts for each supported database. SQL scripts for Oracle are found in the \oracle directory; Microsoft SQL Server in the \sql server directory; and generic OBDC scripts are in the \odbc directory.

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").

Each electronic industry product contains the electronic SQL scripts, diagrams, and reports for the corresponding chapter and appendix within this book. For instance, the manufacturing electronic product contains the electronic diagrams for the models in Chapter 2, and reports and SQL scripts to implement the manufacturing models that are described in Appendix A. Similarly, the telecommunications electronic product contains the electronic diagrams, reports, and SQL scripts to build the models described in Chapter 3 and Appendix B. Thus each chapter and corresponding appendix of this book has a corresponding industry electronic product, sold separately.

These industry electronic products provide a very easy mechanism for physically instantiating the models in this book, customization of the SQL scripts, customization of the diagrams (you will need software that allows modifications to Visio 2000 or jpeg formats), loading the models into your CASE tool, or viewing reports about the models. All SQL scripts have been thoroughly tested for Oracle 7.3 platforms and Microsoft SQL Server 7.0, and generic ODBC scripts are provided that can be easily modified for other database platforms. Database vendors generally support upward compatibility of SQL scripts.

Using the Scripts

Using the scripts provided on the CD-ROM is quite simple. They can be used immediately if no changes need to be made or they can 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 Microsoft SQL Server, and so on). 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 objects 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 database objects for further analysis or reengineering. Many popular CASE tools even have the ability to reverse engineer directly from the SQL scripts.

How to Use the Industry Electronic Products in Conjunction with the Data Model Resource CD-ROM Volume 1

Each of the industry model scripts may be used in conjunction with the Volume 1 CD-ROM scripts. The industry model scripts contain all the SQL to implement the industry models of this book, which contain relationships to the applicable Volume 1 tables. Each of the industry electronic products corresponds to the entities and attributes of a particular chapter and appendix in this book. These industry models can be used together with the generic Volume 1 data model scripts, enabling a flexible means of selecting the desired models and SQL scripts for your organization.

The CD-ROM in the back of this book provides a sample of what is in the Volume 1 CD-ROM in the directory \v1 demo. The sample consists of the SQL scripts, reports and a diagram for the V1:2.6a model, "Common Party Relationships". The full product includes the SQL scripts, reports and electronic diagrams for the whole book. (For more complete information, please refer to the "How to use the CD-ROM" section of The Data Model Resource Book, Revised Edition Volume 1.)

The electronic products were designed to provide modular, mix-and-match capabilities, allowing the modeler to add the desired industry components to the generic Volume 1 data model constructs. These industry database scripts provide relationships to Volume 1 table names, using many of the same table names (when appropriate), primary keys, and the same foreign key constraints as the Volume 1 models, thus making it easier for the modeler to merge the generic Volume 1 models and industry models. The modeler may decide to implement the generic Volume 1 models and enhance them with one or more industry model(s), which provide additional and enhanced constructs. Alternatively, the modeler can start with an industry model then enhance the industry with additional constructs from the Volume 1 CD-ROM.

The decisions on which models to use and how to integrate them is subjective in nature and will vary based upon the needs of your organization. For example, the modeler implementing the health care electronic product may decide to use the HEALTH CARE OFFERING constructs, the PRODUCT constructs from the Volume 1 CD-ROM or a combination of both. The modeler may also decide to use the SQL constructs for PATIENT and then enhance them with the additional "party" constructs from the Volume 1 CD-ROM. Additionally, the modeler may decide to enhance their models with components from other industries such as including the PARTY NEED constructs from the financial industry product and/or constructs from the "e-commerce" electronic product.

Many CASE tools provide the ability to merge models by reverse engineering them in the tools under separate applications or subject data areas, and then consolidate them into a single model. Alternatively, the modeler may decide to manually review the models and decide which entities to include, which relationships are appropriate for their enterprise, and the most appropriate ways to link the data models and database constructs.

Platform Specific Notes and Additional Comments

Oracle Notes

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 only for foreign keys, since the primary keys already have indexes from the primary key alter table statements in the .tab scripts.

All entity and attribute names that are greater than 30 characters have been truncated to 30 characters, because the maximum limit of Oracle 7.3 is 30 characters. For instance, the dimension INDIVIDUAL_HEALTH_CARE_PRACTITIONERS was changed to INDIVIDUAL_HEALTH_CARE_PRACTS in order to make it unique from INDIVIDUAL_HEALTH_CARE_PRACTIT. In some cases the names were shortened with an abbreviation, for example, ACCOUNT_TRANSACTION_RELATIONSHIP_TYPE was changed to ACCOUNT_TRANS_REL_TYPE.

SQL Server Notes

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

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

SQL Server and ODBC directories have an additional file used for generating tables without referential integrity statements. This is provided because SQL Server does not allow dropping tables with that have referential integrity constraints. Therefore, these scripts allow the generation of tables without these constraints, allowing generation and dropping of tables more easily. These files have a .tab extension and a "nori" (no referential integrity) just before the .tab extension. For example, sqlservermfgnori.tab is the script that generates SQL Server create table scripts without referential integrity for the SQL Server environment.

Due to reserved word changes, the following words in the SQL Server and ODBC scripts were changed

In the manufacturing product, the entity name CONSTRAINT was changed to CONSTRAINT_SPECIFICATION. In the telecommunications product, the PRICE_COMPONENT percent attribute was changed to price_percent. In the financial services product, the table PLAN was changed to FINANCIAL_PLAN. In the travel product, TRAVEL PRICE COMPONENT percent was changed to price_percent.

ODBC Notes

The ODBC scripts are provided to generate SQL scripts that may be used and/or tailored for use most relational database systems. They use very standard SQL syntax and common datatypes such as DATE, NUMERIC, and VARCHAR.

Demo Files

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 of the CD-ROM in the back of Volume 2. These directories have subdirectories for the supported databases, for some sample reports, and for sample diagrams.

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.

General Notes

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 in the manufacturing models, there are two relationships from PART to PART BOM, allowing a many-to-many recursion. The parent entity PART has a primary key of part_id and in order to distinguish the foreign key columns in PART BOM, there are two column names in the PART BOM, part_id_for and part_id_in.

In applicable industry models, the ORDER and SIZE tables were changed to an ORDER_HEADER and SIZE_FEATURE in the SQL scripts in order to not use the reserved word ORDER or SIZE.

In the health care industry, the entity GROUP was translated to HEALTH_CARE_GROUP because group is a reserved word in most SQL environments. All columns named date were renamed with a prefix in the physical models because "date" is a reserved word in many SQL variations.

Return to the Electronic Download Product Descriptions

Suggestions

Other Results