14895 East 14th Street, Suite 300 ? San Leandro, CA 94578

1
Systems Integration Secrets Using Logical
Databases
2

• Physical Database
• Underlying storage structure of persistent data
  (tables)

• Logical Database
• Group of objects that presents the underlying
  tables in a manner that is consistent with the
  supported applications and reporting objectives
  (note this not the only definition given for
  logical database)

vs.
3
Logical Database Other Definitions

• Not the logical database referred to in Edger F.
  (Ted) Codds Process of Normalization/Database
  Design
• Not part of Oracles OFA (Optimal Flexible
  Architecture) the standard placement and naming
  of database-related files to optimize performance
  in a multi-disk environment

4
Logical Reasons for Creating Logical Databases
Their world doesnt revolve around your design

• Simplified supporting reports
• Disparate systems integration you can trick
  an application into using your data structures
• Continuous application development

5
Additional reasons

• Beneficial for all uses
• even when directly defining an underlying table
  (i.e. create view v_employee as select from
  employee)
• easier to manage security and more easily
  accommodates future changes
• Increase efficiency
• Customize the path by which data is accessed,
  making users more efficient and ensuring optimal
  performance
• Simplifying the complex
• Confusing data structures can be presented in a
  manner that is intuitive to users

6
Creating Logical Databases

• Presentation Logical Physical

7
Tool view

• View provides a customizable presentation of an
  underlying table or tables and acts similar to a
  table but has no persistent data

8
Tool view, continued

• CREATE or REPLACE VIEW EMPLOYEE AS
• SELECT emp_id as e_id, f_name l_name as
  full_name, address_no street as
  address1, city, st as state, zip_cd as postal_cd,
  USA as country
• FROM emp
• WHERE status active

9
Tool materialized view (snapshot view)

• Materialized view similar to a view except that
  it stores persistent data that utilizes the DBMS
  synchronization to the underlying tables.
• The Query Rewrite feature of the Oracle optimizer
  improves performance by utilizing materialized
  views

10
Tool triggers (on views)

• Triggers normally data would be inserted,
  updated or deleted from the views underlying
  table, but this event driven process follows the
  defined in PL/SQL

11
Example of view with instead of trigger

• CREATE OR REPLACE TRIGGER employee_insert
• INSTEAD OF INSERT
• ON employee
• FOR EACH ROW
• BEGIN
• INSERT INTO emp
• (emp_id, f_name, l_name)
• VALUES
• (NEW.emp_id,NEW.f_name,NEW.l_name)
• END employee_insert
• /

12
CREATE VIEW EMPLOYEE AS SELECT emp_id as e_id,
f_name l_name as full_name,
address_no street as address1, city,
st as state, zip_cd as postal_cd, USA as
country FROM emp WHERE status active
13
Tools stored procedures, synonyms, database link

• Stored Procedures provides a process to write to
  underlying tables, especially helpful for complex
  operations
• Synonyms provides a means of renaming to public
  or a specific users scope underlying objects
  (tables, types, views, materialized views,
  sequences, procedures, functions, packages)
• Database link allows creation of connection to a
  table or view in a remote database that makes the
  ojbect act as if it is local

14
Development Process

• Identify a long term vision
• Where is this database going?
• Design changes in physical model
• Decision point
• Modify physical DB and logical accommodates
• OR modify logical DB to represent future physical
  DB
• Dependency if applications, reports, stored
  procedures, etc. write directly to tables, you
  must choose the latter option

15
Development Process, continued

• Group users of data
• applications, reports, interfaces, etc.
• Model groups
• typically based on function which represents the
  underlying business objective, i.e. account
  payable
• Create logical DB for one or more groups
• Repeat for each iteration

16
Project Planning

• Develop a project plan with each iteration at a
  fix interval
• Typically one to three months
• Lock down schema changes, at which time schema
  changes are assigned to the following release
  (this keeps groups from waiting for a significant
  amount of time)

17

• Eric Buskirk
• Contact Information
• 14895 East 14th Street, Suite 300
• San Leandro, CA 94578
• www.verican.com