Oracle SQL Developer Data Modeler - Version Control Your Designs

A brief overview of SQL Developer Data Modeler and step-by-step guide to adding your design to Subversion, generating DDL Alter Scripts, and more.

1. Collaborative Design with Oracle SQL Developer Data Modeler Jeff Smith, Product Manager [email protected] @ thatjeffsmith

2. Legal The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.

3. Agenda Brief functional overview Using Subversion to Version Your Designs Team collaboration Resources Hint: Click on the blue text for more info

4. Oracle SQL Developer Data Modeler Overview No cost diagramming and data modeling tool – standalone or within SQL Developer Single tool for design and review Modeling across platforms within one integrated system Business rule and process flow documentation and verification

5. SQL Developer Data Modeling Strategy and Analysis Relational Database Design Data Type Star Schema Physical Multidimensiona l ERD DFD Logical Reporting Import Models Domains

6. Logical Modeling Model entities, attributes, relations and entity type hierarchies Transform logical to many relational and multidimensional models Configurable forward and reverse engineering Provide different modeling notations and displays of same model Use subviews to work on subset of model

7. Relational Modeling Model tables, columns and foreign keys Map one logical data model to one or more relational models Configurable forward and reverse engineering Use subviews to work on subset of model

8. Physical Modeling Storage, Security, Stored Procedures Compression, Partitioning, Encryption, Redaction One or more physical models per relational model for different deployments Data warehouse OLTP 11g vs 12c

9. Working with Diagrams Adjust Content Adjust Anchor Points Subviews Multiple Displays

10. Working with Objects Properties Dialog

11. Forward and Reverse Engineering Engineering options: General, compare/copy, and synch Include design glossary and naming standards

12. Collaborative Design Overview

13. Collaborative Design Use Team > Versions to connect to Subversion repository Add Design using Data Modeler 1 or more users check out designs Pending Changes dialog provides feedback to all users Commit changes to repository Manage conflicts Review/Manage Version History

14. Let’s Talk Subversion and SQL Developer 1 model → many files Each entity/table gets it’s own file You deal with model entities, not files File tagging allows us to Compare/Merge/Commit models at the object level Use existing Subversion Repository or deploy w/ SQL Developer

15. Adding an Existing Model to Subversion New Remote Directory Check out directory Create local directory/copy Open existing model Save to local folder YES!

16. Now Watch SVN Add the Model files in the BG

17. Save Model to See Outgoing Changes

18. Compare Model to Last Repository Revision See differences side-by-side Highlighted in red Merge if you agree

19. Bonus Feature: Track Diagram Changes Too

20. Compare Model to Data Dictionary Generate DDL Synch Scripts Diffs & Alter Scripts Compare 2 versions of the same model

21. Thanks! Download SQL Developer Data Modeler Product Forums YouTube Channel

22. Graphic Section Divider