Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Oracle DBA Masterclasses: Data Modeling

thatjeffsmith
September 23, 2020

Oracle DBA Masterclasses: Data Modeling

Far too often, data modeling is a process in which a distressed support person reverse engineers an undocumented database.

But putting together a proper data model shouldn't be a luxury—instead, it should be a requirement for any new database application.

Join this session where Oracle experts will demonstrate how using Oracle SQL Developer Data Modeler will lead to a more flexible, secure, and performant database.

DOWNLOAD the PDF for Working LINKS

thatjeffsmith

September 23, 2020
Tweet

More Decks by thatjeffsmith

Other Decks in Programming

Transcript

  1. Oracle DBA 2020 Data Masterclasses Day 1 – Data Modeling

    Jeff Smith Distinguished Product Manager Email: [email protected] Blog: https://www.thatjeffsmith.com @thatjeffsmith
  2. Today’s Format • ~60 minutes presentation • Question? Submit to

    the Q&A Panel • Yes, this is being recorded
  3. whoami/ select USER from DUAL • Database Tools • Contact

    me for a private session for your company
  4. Learn by Watching • 70+ Videos • Training • Tips

    & Tricks • 5 – 60 minutes YouTube Channel Link
  5. What are we doing here today? • Database Training from

    the Database Team • Part I: Design • SQL Developer Data Modeler • Modeling & Product Tips
  6. What comes “first?” • Business problem/opportunity • Design • Code

    (spec, code, debug, build…) • Test • Publish • Support DBAs ‘fall into’ a project at Delivery – aim for Design!
  7. • We’re experienced • We know better • We’ll fix

    it later, let’s just get a POC going • What’s in a name, really? Why do we frequently skip the design step? Design SHOULD INCLUDE a proper Data Model!
  8. What’s in a name? FIRST_NAME: VARCHAR2(20) LAST_NAME: VARCHAR2(25) We all

    ‘know’ how to store a person’s name, right?
  9. From notes to a logical model? ENTITIES vs TABLES Describe

    what defines an employee, or even just a PERSON ENTITY RELATIONSHIP DIAGRAM ERD
  10. I’ve seen things you people wouldn’t believe • No foreign

    keys • Foreign Keys w/o indexes • DATEs stored as strings • No security or logging Like tears in the rain. Time to die.
  11. DBAs are a wealth of knowledge • Make the database

    USABLE for developers! • Less ‘no’ and more ‘lets go!’ • After the schemas are ready, help them with their REST APIs • Participate in code/design reviews • Help setup test/dev/UAT environments • You drink SQL & you know things. Your talents are wasted if you’re only rebuilding indexes or taking backups. The Shadow “knows” IT ‘No’ leads to Shadow IT projects
  12. Offer to Review the Data Model • Look for ‘red

    flags’ • Red flag #1… THERE IS NO DATA MODEL
  13. SQL Developer vs Data Modeler • Same features in both,

    but… • SQLDev – Modeler shares Menus & Resources Advice: use standalone Modeler for big projects/tasks
  14. Your Design File(s) • Design => Design.DMD file • This

    is an XML file with pointers • Rest of the files will be in a Design Directory • Each object, attribute, line, EVERYTHING gets a file Trick: Find your work, File > Recent Designs
  15. The Basics: 101 Boot Camp • Creating a table/entity •

    Drawing a relationship • Navigating
  16. It’s NOT Too Early to Consider Security In fact, it’s

    never too early to consider security. PII: Personally Identifiable Information
  17. “Testing” the Table, did we get it right? I did

    NOT want to add the WITH GRANT OPTION, FAIL!
  18. Drawing the relationships 1. Start with parent 2. Click on

    table and let go 3. Click on other table 4. Confirm properties LINK: Animated GIF Demo
  19. More UI 101: Navigating your designs • Navigator • Search

    • SubViews Models can be BIG – make it easier to see/find stuff
  20. Search (and Replace!) Great for Reporting, or Just Answering Questions

    about your Designs LINK: Learn more about Search Feature
  21. SubViews Split things up – easier to view, understand, and

    work with Click YouTube pic for VIDEO LINK
  22. Controlling/Managing Domains • Stored in a separate XML file, separate

    from your design • Import them, or copy to the domains directory • SOURCE CONTROL THEM
  23. Play favorites…save time and scrolling • Data types…TOO MANY! •

    Filter with Preferred LINK: More on Preferred Types (and Domains)
  24. Solo or team, you need to version your work •

    Subversion or Git • Checking in Design using Data Modeler • Multiple users check out designs • Pending Changes dialog provides feedback to all users • Commit changes to repository • Manage conflicts • Review/Manage Version History Using Git with Data Modeler (Docs)
  25. Compares • Model to Another Model…or different version of a

    model • Model to a Database • Database to a Model • Delta/Alter Scripting LINK: All about Compares
  26. Sharing your work with others • Some folks will want

    pictures • Or data dictionary reports • Or will just want to write SQL
  27. Lots of features, but also lots of resources! • Forums

    • Books • Docs • Blogs • YouTube LINK: The Definitive Guide to SQLDev Data Modeler