# Presentation: 066118

## LOGICAL TO PHYSICAL MODEL CONSIDERATIONS

- Carl Livingood

## 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)

## INFORMATION MODEL TYPES

**Entity Relationship Diagram**

**Key-based Model**

**LOGICAL**

**PHYSICAL**

## 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*

## 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

## 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

## 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.***

## 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*

## 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*

## 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

## 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

- ent-a-key
- ent-a-owned-attr

**ENTITY-A**

- ent-b-key
- ent-b-owned-attr
- ent-a-key (FK)

**ENTITY-B**

## “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*

## 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

## 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

## 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”

## 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

## 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*

## INFORMATION MODEL TYPES

**Entity Relationship Diagram**

**Key-based Model**

**LOGICAL**

**PHYSICAL**

## 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

## 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

## 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

## 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