LOGICAL TO PHYSICAL MODEL CONSIDERATIONS

1. LOGICAL TO PHYSICAL MODEL CONSIDERATIONS Carl Livingood

2. LOGICAL / PHYSICAL MODEL BOUNDARIES • Logical model considers only the business information (data entities) and its inherent relationships • Logical model is not constrained by the "physical” realities • Targeted database management system • Network • Resources ($, people)

3. Fully Attributed Model Fully Attributed Model Fully Attributed Model Transform Model Transform Model Transform Model DBMS Model INFORMATION MODEL TYPES Entity Relationship Diagram Key-based Model LOGICAL PHYSICAL

4. MODEL TRANSFORMATION • Assumes a targeted database management system • Oracle, Sybase, MS/SQLServer, DB2, Informix, etc. • Reviews anticipated access patterns • Determines acceptable level of denormalization for performance enhancement …BRIDGES THE PHYSICAL DESIGN BACK TO THE LOGICAL DESIGN

5. TRANSFORM MODEL TASKS • Denormalization • Recording of physical system names consistent with RDBMS constraints • e.g., table and column names limited to 30 characters • Specify datatypes for attribute data • i.e., character vs. integer vs. date • Create indices for rapid access • Define strategy for rule enforcement

6. RULE ENFORCEMENT • Enforcement options • DBMS function • Application code • Both • Enforcement “timing” • real-time, either/both application or database • violation record with after-the-fact repairs

7. ENFORCEMENT FUNCTIONS • Referential integrity (i.e., “data integrity”) • “No nulls” • Delete restrictions • Valid values • Defaults Note: all of these functions are inherent in today’s DBMS offerings…but oftentimes enforcement is only at the application level.

8. REFERENTIAL INTEGRITY • Ensures that all assertions for a set of relationships are true • parent referenced in foreign key actually exists in the parent table • parent update and/or delete either restricted (if children exist) or cascaded • Compliance with Referential Integrity reached only when no unmatched foreign key values are found within the database

9. REFERENTIAL INTEGRITY IMPLEMENTATION • Referential Integrity enforces • Parentage requirements • Foreign key consistency • May be enforced, within the database, using • Declarative integrity (“foreign key declarations”) • Triggers • trigger logic enables additional function

10. TRIGGERS • “Triggers” fire when insert, update, or delete actions are taken against a table • Triggers may enforce referential integrity • Triggers may enable function beyond declarative integrity • e.g., Update Cascade functions • Triggers may contain database application code

11. REFERENTIAL INTEGRITY EXAMPLE • Every entry to “ENTITY-B” must carry a reference to “ent-a-key” • Referenced “ent-a-key” must exist in ENTITY-A ENTITY-A ENTITY-B ent-a-key ent-a-owned-attr ent-b-key ent-b-owned-attr ent-a-key (FK)

12. “NO NULLS” • Used upon “insert” and “replace” • DBMS insists upon entry of data in specified data fields • Required for any mandatory relationships • Is often implemented as well at the application level but should always be explicit in the database where appropriate

13. DELETE RESTRICTIONS • Protects child entities from deletion as appropriate • “CASCADE” • Child entities (and their history) are deleted when a parent is deleted • “RESTRICT” • Parents cannot be deleted if there are child entities present

14. VALID VALUES • “Valid values” allow for specification of entries allowed for a given attribute • Field value given by user/application is validated upon insert, update • Depending on usage and volatility, may be better implemented as a “TYPE” entity (i.e., lookup table) with relationships to other entities

15. DEFAULTS • “Defaults” allow for specification of an attribute field value upon insert when no value is provided in insert statement • Particularly useful in data entry applications where value is predominantly “X”

16. DATA MODEL ENFORCEMENT REPRESENTATIONS • All data modeling methodologies allow for the specification of • referential integrity constraints (i.e., foreign keys based on entity relationships) • Nulls allowed / no nulls • Delete restrictions • Most tools allow for Valid Values and Defaults to be specified within the model

17. TRADITIONAL SYSTEM DEVELOPMENT • DBMS’ did not always have the function they have today for referential integrity and other enforcement functions • Enforcement was typically done at the application level • Today’s development should always enforce at the database as well as at the application level • facilitates data integration and accessibility

18. Fully Attributed Model Fully Attributed Model Fully Attributed Model Transform Model Transform Model Transform Model DBMS Model INFORMATION MODEL TYPES Entity Relationship Diagram Key-based Model LOGICAL PHYSICAL

19. DBMS MODEL • Described by the data definition language that creates the database (e.g., SQL) • Initially addresses one (or more) transform models for different major areas of the business • The DBMS model will grow as areas of focus are expanded

20. DBMS MODEL LAYOUT • Each entity in the Transform Model becomes a table • Each attribute becomes a column in a table • Primary / foreign keys declared for each table • Referential integrity constraints declared for each relationship

21. TOOLS, TOOLS, TOOLS! • Automated tools exist that will assist in the modeling and database design task • ERD through the Transform Model • Tools can create SQL scripts, consistent with the Transform Model, to be used for database generation

22. SUMMARY • A successful data model is a function of • understanding your business • time • data modeling skills • First stop: users • Why? Because the business information and the way it is used is what structures the data model