1 / 24

Automatic Database Schema Navigation

Automatic Database Schema Navigation. … or Better Living Through Graphs and Trees. David Kaplan. Background. Microsoft Access 1.0, 1.1, 2.0 (1992-94) Strong sales, first-time users Wizards for novices Adequate: basic Form, Report wizards Poor: advanced Form, Report wizards

rachelr
Download Presentation

Automatic Database Schema Navigation

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. Automatic Database Schema Navigation … or Better Living Through Graphs and Trees David Kaplan

  2. Background • Microsoft Access 1.0, 1.1, 2.0 (1992-94) • Strong sales, first-time users • Wizards for novices • Adequate: basic Form, Report wizards • Poor: advanced Form, Report wizards • None: Query (non-)wizards • Leverage/Constraints • Rich state machine • Add-in environment, add-in team • Visual Basic (VBA)

  3. Goals • Solve it in Access ’95 • Automate most common user tasks • Single-record form • Master-detail form • Tabular, Banded reports • SPJ query, possibly with simple aggs • Simplify our code base

  4. Solutions • Simplified user experience • Wizard unification • Common UI components • Field Picker, Style Picker • Shared layout engine

  5. Solutions • Simplified user experience • Wizard unification • Common UI components • Field Picker, Style Picker • Shared layout engine • Shared data-organization engines • IntelliJoin (I-Join) • IntelliView (I-View)

  6. I-Join • Motivation • Information crosses multiple tables • Users don’t know from JOIN clauses • Response • Automatic join engine (graph-traversal) • User picks fields from any tables • … we do the rest

  7. I-Join • Problem: Find minimal subschema (join graph) to cover user-chosen fields • Classical graph theory problem: “Steiner Problem on Vertices” • NP-Hard, so … call in Pro from Dover (Karp)

  8. I-Join • Solution: heuristic scoring-pruning method “On Finding Steiner Vertices”, V.J. Rayward-Smith and A. Claire. Networks, Vol 16 (1986) 283-294, John Wiley & Sons, Inc. • Works well on lightly-connected graphs (e.g., database schemas)

  9. I-Join Example • Query on Customers, Products, Categories

  10. I-Join Example • Fields in Customers, Products, Categories • “Empty” join tables

  11. I-Join Example • Fields in Customers, Products, Categories • “Empty” join tables

  12. I-View • Motivation • Information requires hierarchy • Users don’t know from subforms, group levels • Response • Automatic hierarchy engine (tree-from-graph) • User picks a hoist point • … we do the rest

  13. I-View • Motivation • Information requires hierarchy • Users don’t know from subforms, group levels • Response • Automatic hierarchy engine (tree-from-graph) • User picks a hoist point1 • … we do the rest 1For ‘tis the sport to have the enginer hoist with his own petard. - Hamlet, Act III, Scene iv

  14. I-View Graph Analysis • Require acyclic input graph • Tractable graph permits auto-hierarchy • “Star almost-DAG”

  15. I-View in Action • Hoist point: Customers

  16. I-View in Action • Hoist point becomes Level 1 (no u-u nodes) 1

  17. I-View in Action • Next downstream node becomes Level 2 1 2

  18. I-View in Action • Next downstream node and its un-assigned upstream nodes become Level 3 1 2 3

  19. I-View in Action • Hoist point: Products

  20. I-View in Action • Hoist point and its un-assigned upstream node become Level 1 1

  21. I-View Algorithm Inputs: graph (must be tractable) hp, the hoist point I-View • i  0 • b  hp • Loop: • i i + 1 • N(i)  Uu(b) • If (df(b) is null) terminate • b  df(b)

  22. I-View Extras • User-defined levels • Agg queries

  23. Patents

  24. Possible Future Directions • XML • Move data as graph (id/idref), infer hierarchy at destination • Run I-Join, I-View against XML schema • Move I-Join, I-View onto server • Join-less SQL • H-SQL • Graphs with multiple most-many nodes • “Parse” into tractable, semi-disjoint sub-graphs • Display as inter-locking forms or reports

More Related