1 / 17

Systems Integration Secrets Using Logical Databases

Systems Integration Secrets Using Logical Databases. Physical Database Underlying storage structure of persistent data (tables). Logical Database

arich
Download Presentation

Systems Integration Secrets Using Logical Databases

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

More Related