Systems Integration Secrets Using Logical Databases

1. Systems Integration Secrets Using Logical Databases 14895 East 14th Street, Suite 300  San Leandro, CA 94578 phone 800.888.0470 / 510.352.3000  fax 510.352.7301  www.verican.com

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 application’s and reporting objectives (*note this not the only definition given for logical database) vs. 14895 East 14th Street, Suite 300  San Leandro, CA 94578 phone 800.888.0470 / 510.352.3000  fax 510.352.7301  www.verican.com

3. Logical Database: Other Definitions • Not the logical database referred to in Edger F. (Ted) Codd’s Process of Normalization/Database Design • Not part of Oracle’s OFA (Optimal Flexible Architecture) – the standard placement and naming of database-related files to optimize performance in a multi-disk environment 14895 East 14th Street, Suite 300  San Leandro, CA 94578 phone 800.888.0470 / 510.352.3000  fax 510.352.7301  www.verican.com

4. Logical Reasons for Creating Logical Databases Their world doesn’t revolve around your design • Simplified supporting reports • Disparate systems integration – you can “trick” an application into using your data structures • Continuous application development 14895 East 14th Street, Suite 300  San Leandro, CA 94578 phone 800.888.0470 / 510.352.3000  fax 510.352.7301  www.verican.com

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 14895 East 14th Street, Suite 300  San Leandro, CA 94578 phone 800.888.0470 / 510.352.3000  fax 510.352.7301  www.verican.com

6. app1 app2 app3 report interface Creating Logical Databases Presentation Logical Physical 14895 East 14th Street, Suite 300  San Leandro, CA 94578 phone 800.888.0470 / 510.352.3000  fax 510.352.7301  www.verican.com

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 14895 East 14th Street, Suite 300  San Leandro, CA 94578 phone 800.888.0470 / 510.352.3000  fax 510.352.7301  www.verican.com

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’; 14895 East 14th Street, Suite 300  San Leandro, CA 94578 phone 800.888.0470 / 510.352.3000  fax 510.352.7301  www.verican.com

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 14895 East 14th Street, Suite 300  San Leandro, CA 94578 phone 800.888.0470 / 510.352.3000  fax 510.352.7301  www.verican.com

10. Tool: triggers (on views) Triggers: normally data would be inserted, updated or deleted from the view’s underlying table, but this event driven process follows the defined in PL/SQL 14895 East 14th Street, Suite 300  San Leandro, CA 94578 phone 800.888.0470 / 510.352.3000  fax 510.352.7301  www.verican.com

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; / 14895 East 14th Street, Suite 300  San Leandro, CA 94578 phone 800.888.0470 / 510.352.3000  fax 510.352.7301  www.verican.com

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’; 14895 East 14th Street, Suite 300  San Leandro, CA 94578 phone 800.888.0470 / 510.352.3000  fax 510.352.7301  www.verican.com

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 user’s 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 14895 East 14th Street, Suite 300  San Leandro, CA 94578 phone 800.888.0470 / 510.352.3000  fax 510.352.7301  www.verican.com

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 14895 East 14th Street, Suite 300  San Leandro, CA 94578 phone 800.888.0470 / 510.352.3000  fax 510.352.7301  www.verican.com

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 14895 East 14th Street, Suite 300  San Leandro, CA 94578 phone 800.888.0470 / 510.352.3000  fax 510.352.7301  www.verican.com

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) 14895 East 14th Street, Suite 300  San Leandro, CA 94578 phone 800.888.0470 / 510.352.3000  fax 510.352.7301  www.verican.com

17. Eric Buskirk Contact Information: 14895 East 14th Street, Suite 300 San Leandro, CA 94578 www.verican.com 14895 East 14th Street, Suite 300  San Leandro, CA 94578 phone 800.888.0470 / 510.352.3000  fax 510.352.7301  www.verican.com