Guava: Graphical User Interfaces as Updatable Views

1. Graphical User Interfaces are Already Updatable Views: Use GUI as a conceptual model, and program to that conceptual model, with a novel mapping to physical storage called a channel Guava: Graphical User Interfaces as Updatable Views James F. Terwilliger Portland State University

2. Table P_1588: P_1588_ID Attr Detail … … … … … … … … 1 … … 578a-b440-cdf2-8e21 Smkh_1670 • Data Dictionary:P_1588: Patients from WebRn Application, entered through form “Endoscopy Patient” • Domain(Detail/Smkh_1670) = {0, 1, 2, 3, 4, X} • 0: Non-Smoker 1: Light smoker, 1-4 cigs/day • 2: Moderate smoker, 5-8 cigs/day 3: Heavy smoker, 9-12 cigs/day • 4: Chimney, 13+ cigs/day X: Data not available

3. Queries: UI and QI • Think Query By Example • “Show me the Endoscopist and severity of completed Endoscopies where Bob was the Anesthetist and complications occurred” Graphical User Interfaces as Updatable Views

4. G-Tree: A Structure to Represent the UI Endoscopy (Entity) Personnel (Container) Outcomes (Container) Endoscopist (Attribute) Anesthetist (Attribute) Procedure Complete (Attribute) Details (Control) Severity (Attribute) Single-Launch Endoscopy Details (Entity) Primary Finding (Attribute) Other Findings (Attribute) Post-Operative Instructions (Attribute) Complications Occurred (Attribute) Anesthesia Required (Attribute) Other Surgery Required (Attribute) Graphical User Interfaces as Updatable Views

5. G-Trees  Natural Schema Endoscopy (Entity) Endoscopy( ID, Endoscopist, Anesthetist, …) Personnel (Container) Outcomes (Container) Endoscopist (Attribute) Anesthetist (Attribute) Procedure Complete (Attribute) Details (Control) Severity (Attribute) EndoscopyDetails( ID, PrimaryFinding, OtherFindings, …) Single-Launch Endoscopy Details (Entity) Primary Finding (Attribute) Other Findings (Attribute) Post-Operative Instructions (Attribute) Complications Occurred (Attribute) Anesthesia Required (Attribute) Other Surgery Required (Attribute)

6. G-Trees: Query Endoscopy (Entity) Personnel (Container) Outcomes (Container) Endoscopist (Attribute) Anesthetist (Attribute) = “Bob” Procedure Complete (Attribute) = true Details (Control) Severity (Attribute) Single-Launch Endoscopy Details (Entity) Primary Finding (Attribute) Other Findings (Attribute) Post-Operative Instructions (Attribute) Complications Occurred (Attribute) = true Anesthesia Required (Attribute) Other Surgery Required (Attribute) Graphical User Interfaces as Updatable Views

7. G-Trees: Query Endoscopy (Entity) Personnel (Container) Outcomes (Container) Endoscopist (Attribute) Anesthetist (Attribute) = “Bob” Procedure Complete (Attribute) = true Details (Control) Severity (Attribute) Single-Launch πEndoscopist, Severity (σAnesthetist=“Bob”^ProcedureComplete=true(Endoscopy) ⋈ σComplicationsOccurred=true(EndoscopyDetails)) Endoscopy Details (Entity) Primary Finding (Attribute) Other Findings (Attribute) Post-Operative Instructions (Attribute) Complications Occurred (Attribute) = true Anesthesia Required (Attribute) Other Surgery Required (Attribute) Graphical User Interfaces as Updatable Views

8. What does application middleware do? Physical Design, Business Logic Natural Schema Physical Schema Queries (QI) Insert, Update, Delete (UI) Schema Modifications (Design) Horizontal or Vertical Merging/Partitioning Referential Integrity Pivoting or unpivoting Statistics, Histograms Encoding or translation of values Business logic Information preservation Graphical User Interfaces as Updatable Views

9. Graphical Representation T1 HMerge T2 Unpivot T3 T4 Apply HMerge VPartition T5 Unpivot Graphical User Interfaces as Updatable Views

10. The Channel Horiz. Merge Vert. Part. Horiz. Merge Apply Unpivot Unpivot Natural Schema Physical Schema Q, Ins,Up,Del, DDL ∆, Loop, Error, FK Graphical User Interfaces as Updatable Views

11. Transformations for Physical Design • Vertical Partition • Vertically partition into two tables with a 1:1 relationship • Vertical Merge • Vertically merge two tables with a 1:1 relationship • Horizontal Partition • Horizontally partition based on the value in a column • Horizontal Merge • Horizontally merge, and introduce a provenance column • Pivot/Unpivot • Similar to data warehouse operations, moving data to schema (pivot) or schema to data (unpivot) • Apply • Apply an invertible function to data in each tuple Graphical User Interfaces as Updatable Views

12. Statements that the Channel Transforms • Queries in extended relational algebra • Inserts, updates, and deletes (DML) • Add/rename/drop table, column, domain element (DDL) • Element DDL corresponds to changing elements in a drop-down list • Transformations may also introduce element operations • Add/drop foreign key constraints • Loop statements • For each tuple t in query Q, do statements S • Error checks • If query Q returns a non-empty result, raise an error • Transformations are closed with respect to these statements Graphical User Interfaces as Updatable Views

13. Operational Processing Transformation O Natural Schema Physical Schema DML ODML (Ins(T,R)) Insert Ins(T,R) Ins(T, R’), Up(T’’,F,C,R), Loop(t,Q,S) Graphical User Interfaces as Updatable Views

14. UnpivotTransformation: Example Before: After: Patient Patient Patient_ID SSN FirstName LastName HomePhone CellPhone Patient_ID Attribute Value Unpivot(Patient, Attribute, Value) Represents the decision to transform a table into (key, attribute, value) rows Graphical User Interfaces as Updatable Views

15. Definition of Unpivot Graphical User Interfaces as Updatable Views

16. Generalized Transformations • MultiPivot • Generalization of Pivot, where there can be multiple attribute columns and multiple value columns • GVPartition • Generalization of VPartition, where there are arbitrarily many output tables, and each column can belong to any of them • PPartition • Horizontal partition by predicate rather than provenance column, and rows can end up in one to arbitrarily many outputs Graphical User Interfaces as Updatable Views

17. Application-Specific Transformations • Adorn • Add columns, and specify data to be held in the new columns, such as environment data • Audit • Add start/end timestamps, and ensure that data is deprecated rather than deleted (temporal database features) • Others • Use lookup table (slightly different than function application) • Establish surrogate key • These actions are similar to the transformations for physical design • Transform queries, DML, DDL, FK’s • Want to be information preserving, preserve key semantics Graphical User Interfaces as Updatable Views

18. Correspondence Assertions • Developer directive that establishes a relationship among the extents of schema elements • “The set of staff members is a subset of the set of people.” • “The value of column X is the same as the value of column Y in this other table.” • Column Equate • Marks a column as holding redundant data, and removes it from the physical database • Table Equate • Marks a table as holding redundant data, and removes it from the physical database • Plays with definition of information preservation Graphical User Interfaces as Updatable Views

19. Channel Transformation: Definition Transformation O Natural DB Physical DB Transformation ODB(D), where O is expressible in extended relational algebra Transformation OSchema(S), describing schema changes Graphical User Interfaces as Updatable Views

20. Transformation Semantics • The core, physical design transformations have a well-understood definition in terms of instance-at-a-time transformation • “Oblivious Put” • HPartition(Ta, C): • instout(t) = πCols(Ta)-C σC=t instin(Ta) (for each value t found in the domain of C) Graphical User Interfaces as Updatable Views

21. Definition of Information Preservation • A transformation O is a transformation of schema and data, expressed in extended relational algebra, that satisfies the following information preservation properties: • If I pose a query against the natural schema, I will get back the same result as if the physical schema matched the conceptual schema • If I issue an insert, update, or delete against the conceptual schema, then issue a query for data in the modified table, I will get back exactly what I would get if there were no transformations in the channel • If I issue a DDL statement against the conceptual schema, then issue a query for data in the modified table, I will get back the instance of the table as if there were no transformations in the channel, but with altered schema Graphical User Interfaces as Updatable Views

22. Definition of Information Preservation • A transformation O is a transformation of schema and data, expressed in extended relational algebra, that satisfies the following information preservation properties: • OQuery(QT) (ODB (D)) = instin(T) • OQuery(QT) (ODML(Ins(T,R)) (ODB(D))) = instin(T) ∪ R • OQuery(QT) (ODML(Up(T,F,C,R)) (ODB(D))) = σNOT F instin(T) ∪ αC→RσF instin(T) • OQuery(QT) (ODML(Del(T,F)) (ODB(D))) = σNOT F instin(T) • OQuery(QT) (ODDL(∆(T)) (ODB(D))) = ∆(T)(instin(T)) Graphical User Interfaces as Updatable Views

23. Sample Proof (Insert through HPartition, part 1) Show me what is in the columns “C” of table T_a in the input (natural) schema Graphical User Interfaces as Updatable Views

24. Sample Proof (part 2) Use instance-at-a-time semantics as axiom Standard relational equivalence Graphical User Interfaces as Updatable Views

25. Sample Proof (part 3) Result is whatever was in T_a before, plus the inserted rows Q Graphical User Interfaces as Updatable Views

26. Definition of Information Preservation • A transformation O that cannot be expressed in relational algebra cannot use the same formulae: • OQuery(QT) (ODB (D)) = instin(T) • OQuery(QT) (ODML(Ins(T,R)) (ODB(D))) = instin(T) ∪ R • OQuery(QT) (ODML(Up(T,F,C,R)) (ODB(D))) = σNOT F instin(T) ∪ αC→RσF instin(T) • OQuery(QT) (ODML(Del(T,F)) (ODB(D))) = σNOT F instin(T) • OQuery(QT) (ODDL(∆(T)) (ODB(D))) = ∆(T)(instin(T)) Graphical User Interfaces as Updatable Views

27. Referential Integrity Natural Schema: T1.(A,B) References T2.(A,B) We want to respect the keys and foreign keys that are present in the natural schema Provenance column changes key – FK no longer references the whole key, nor the whole table T2: AB C After Horizontal Merge: T3: T1: X Y A B ABP C Some key data shifts to schema information – part of FK has nothing to point to After Pivot: T4: A M N O S Graphical User Interfaces as Updatable Views

28. Referential Integrity • Maintain referential integrity through channel operators, and offer the developer feedback as to efficiency • Tier 1: FK(T1.C1T2.C2) • C2 is entire PK for T2 • Only one FK allowed for T1.C1 • Representable by standard foreign keys • Tier 2: FK(F | T1.C1 G | T2.C2) • F and G are conditions on the key elements of tables T1 and T2, respectively • C2 can be only a part of PK for T2 • Can be multiple FK’s defined on T1.C1, with OR semantics • Representable by triggers (Ins, Up on T1; Up, Del on T2) • Tier 3: Check(Q1⊆Q2) • Representable by triggers, but potentially inefficient due to query complexity Graphical User Interfaces as Updatable Views

29. What do we have here? (In large print) • An application-specific query interface, enabled by a new way to look at data independence via a channel Graphical User Interfaces as Updatable Views

30. Future Work: Alternative Data Models • Relational model has a well-understood, standard data and schema update language (input statements) • Relational model has well-understood physical design paradigms (channel transformations) • XML supports create and drop, but not alter, in part due to complex expressive power • Choice, order, grouping, mixed types • Other models to consider, such as RDF, OO • Hybrid models • Transformation to translate from one model to another Graphical User Interfaces as Updatable Views

31. XML Channels • Transformations • Promote attribute to element • Invert hierarchy • Queries • XPath, XQuery • Alter queries using in-place replacement of references to nodes in each axis • DML • Node insert, node delete, replace value • DDL • No DDL for XML schema defined • Could introduce some basic atomic changes • Change cardinality • Impose order • Could also consider “DDL” to be a new total schema, plus XQuery • Simultaneous XSLT on schema and data Graphical User Interfaces as Updatable Views

32. Thanks! Query Interface Channel This work is supported in part by Collins Medical Trust, by DHHS NIH National Institute of Diabetes Digestive and Kidney Diseases No. 5-R33-DK061778-03 awarded to Oregon Health & Science University (OHSU), and by NSF grant No. 0534762. Graphical User Interfaces as Updatable Views