Physical Data Model Concepts

Top  Previous 

The business content of a data model is created in the logical data model.  That includes the entity types, relationships, attributes, domains and their definitions.  The physical model (as implemented by ARTS) takes this information in and translates the long business names into shortened physical names, maps the domains to physical SQL data types and constraints and recasts entities as tables and attributes a columns.

By design, the fully attributed model created in the ARTS Logical Model has all many to many relationships resolved using join entities.  Likewise all primary and foreign keys are fully identified and defined.  Accordingly, the basic entity relationship models viewed in a logical model are translated on a one-to-one basis into SQL tables in the physical data model.  This one-to-one translation means that entities are mapped to tables, attributes are mapped to columns, domains are mapped to data types and relationships are mapped to foreign key constraints in the physical model.

The ARTS physical data model supplements the logical model-based tables with views and extract-transform-load (ETL) tables used to populate the ARTS Data Warehouse Model V3.0 and its physical model.  This is the reason why ARTS transitioned from a limited "near physical" model in versions 6.1 and earlier to a full physical model with DDL in version 7.0/7.1.  The ERWIN data model tool will not allow a logical, dimensional data model (ARTS DWM 3.0) to be mapped to a logical operational data model.  It requires that the mapping use a physical database.  Accordingly, ARTS had to generate a physical operational data model to explicitly map data warehouse dimensional entities and attributes to the ARTS operational data model.  The diagram shows a simplified view of the transformation from logical to physical to DDL.

Figure 99 - Logical to Physical to DDL



As a byproduct of this requirement ARTS extended its work product set for ARTS ODM V7.0/7.1 to include a fully generated sample database.  The procedure for this is explained in detail in the ARTS Data Warehouse Extract, Transform & Load Physical Subject Areas topic.