Download - James Long 1
-
8/2/2019 James Long 1
1/32
Guava: Graphical User Interfaces as
Updatable Views
James F. Terwilliger
Portland State University
Graphical User Interfaces areAlready Updatable Views:
Use GUI as a conceptual model, and program tothat conceptual model, with a novel mapping to
physical storage called a channel
-
8/2/2019 James Long 1
2/32
2
Table P_1588:
Data Dictionary:P_1588: Patients from WebRn Application, enteredthrough 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
1
DetailP_1588_ID
578a-b440-cdf2-8e21 Smkh_1670
Attr
-
8/2/2019 James Long 1
3/32
Queries: UI and QI
3
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 UpdatableViews
-
8/2/2019 James Long 1
4/32
4
G-Tree: A Structure to Represent the UI
Endoscopy
(Entity)
Personnel
(Container)Outcomes
(Container)
Endoscopist
(Attribute)Anesthetist
(Attribute)Procedure
Complete
(Attribute)Severity
(Attribute)Details
(Control)
Endoscopy
Details
(Entity)
Primary
Finding
(Attribute)
Other
Findings
(Attribute)
Anesthesia
Required
(Attribute)
Complications
Occurred
(Attribute)
Post-
Operative
Instructions
(Attribute)
Other
Surgery
Required
(Attribute)
Single-Launch
Graphical User Interfaces as UpdatableViews
-
8/2/2019 James Long 1
5/32
5
G-Trees Natural Schema
Endoscopy
(Entity)
Personnel
(Container)Outcomes
(Container)
Endoscopist
(Attribute)Anesthetist
(Attribute)
Procedure
Complete
(Attribute)Severity
(Attribute)Details
(Control)
Endoscopy
Details
(Entity)
Primary
Finding
(Attribute)
Other
Findings
(Attribute)
Anesthesia
Required
(Attribute)
Complications
Occurred
(Attribute)
Post-
Operative
Instructions
(Attribute)
Other
Surgery
Required
(Attribute)
Single-Launch
Endoscopy(
ID, Endoscopist,Anesthetist, )
EndoscopyDetails(
ID, PrimaryFinding,OtherFindings, )
-
8/2/2019 James Long 1
6/32
6
G-Trees: Query
Endoscopy
(Entity)
Personnel
(Container)Outcomes
(Container)
Endoscopist
(Attribute)Anesthetist
(Attribute)
= BobProcedure
Complete
(Attribute)
= true
Severity
(Attribute)Details
(Control)
Endoscopy
Details
(Entity)
Primary
Finding
(Attribute)
Other
Findings
(Attribute)
Anesthesia
Required
(Attribute)
Complications
Occurred
(Attribute)
= true
Post-
Operative
Instructions
(Attribute)
Other
Surgery
Required
(Attribute)
Single-Launch
Graphical User Interfaces as UpdatableViews
-
8/2/2019 James Long 1
7/32
7
G-Trees: Query
Endoscopy
(Entity)
Personnel
(Container)Outcomes
(Container)
Endoscopist
(AttributeAnesthetist
(Attribute)
= BobProcedure
Complete
(Attribute)
= true
Severity
(Attribute)Details
(Control)
Endoscopy
Details
(Entity)
Primary
Finding
(Attribute)
Other
Findings
(Attribute)
Anesthesia
Required
(Attribute)
Complications
Occurred
(Attribute)
= true
Post-
Operative
Instructions
(Attribute)
Other
Surgery
Required
(Attribute)
Single-LaunchEndoscopist, Severity
(Anesthetist=Bob^ProcedureComplete=true(Endoscopy) ComplicationsOccurred=true(EndoscopyDetails))
Graphical User Interfaces as UpdatableViews
-
8/2/2019 James Long 1
8/32
What does application middleware do?
8
Physical
Schema
NaturalSchem
a
Queries (QI)
Graphical User Interfaces as UpdatableViews
Insert, Update, Delete(UI)Schema Modifications(Design) Horizontal or Vertical
Merging/PartitioningPivoting or unpivoting
Encoding or translation ofvaluesBusiness logic
Physical Design, BusinessLogic
Information preservation
Referential Integrity
Statistics, Histograms
-
8/2/2019 James Long 1
9/32
Graphical Representation
9
T1
T2
T3
T4
T5
HMerge
VPartition
Unpivot
Apply
Unpivot
Graphical User Interfaces as UpdatableViews
HMerge
-
8/2/2019 James Long 1
10/32
The Channel
10
Physical
Schema
Horiz.Merge
Vert.Part.
Apply UnpivotHoriz.Merge
Unpivot
Graphical User Interfaces as UpdatableViews
NaturalSchem
a
Q,Ins,Up,Del,DDL ,Loop,Error, FK
-
8/2/2019 James Long 1
11/32
Transformations for Physical Design
11
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 UpdatableViews
-
8/2/2019 James Long 1
12/32
Statements that the Channel Transforms
12
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 UpdatableViews
-
8/2/2019 James Long 1
13/32
Operational Processing
13
NaturalSchem
a
Physical
Schema
Transformation O
InsertIns(T,R)
DMLODML (Ins(T,R))
Ins(T, R),Up(T,F,C,R
),Loop(t,Q,S)
Graphical User Interfaces as UpdatableViews
-
8/2/2019 James Long 1
14/32
14
Unpivot Transformation: Example
Represents the decision to transform a table into(key, attribute, value) rows
Before: After:
Patient
Patient_ID
SSN
FirstName
LastName
HomePhone
CellPhone
Patient
Patient_ID
Attribute
Value
Unpivot(Patient,Attribute, Value)
Graphical User Interfaces as UpdatableViews
-
8/2/2019 James Long 1
15/32
Definition of Unpivot
Graphical User Interfaces as UpdatableViews
15
-
8/2/2019 James Long 1
16/32
Generalized Transformations
16
MultiPivot Generalization of Pivot, where there can be multiple
attribute columns and multiple value columns
GVPartition
Generalization of VPartition, where there are arbitrarilymany output tables, and each column can belong to any ofthem
PPartition
Horizontal partition by predicate rather than provenancecolumn, and rows can end up in one to arbitrarily manyoutputs
Graphical User Interfaces as UpdatableViews
-
8/2/2019 James Long 1
17/32
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 forphysical design Transform queries, DML, DDL, FKs
Want to be information preserving, preserve key semantics
Graphical User Interfaces as UpdatableViews
-
8/2/2019 James Long 1
18/32
Correspondence Assertions
Graphical User Interfaces as UpdatableViews
18
Developer directive that establishes a relationship amongthe 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 inthis 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
-
8/2/2019 James Long 1
19/32
Channel Transformation: Definition
19
NaturalDB
Physical DB
Transformation O
Transformation ODB(D), where O is expressible inextended relational algebra
Transformation OSchema(S), describing schema
changesGraphical User Interfaces as UpdatableViews
-
8/2/2019 James Long 1
20/32
Transformation Semantics
Graphical User Interfaces as UpdatableViews
20
The core, physical design transformations have awell-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)
-
8/2/2019 James Long 1
21/32
Definition of Information Preservation
21
A transformation O is a transformation of schema anddata, expressed in extended relational algebra, thatsatisfies the following information preservationproperties: If I pose a query against the natural schema, I will get
back the same result as if the physical schema matchedthe conceptual schema
If I issue an insert, update, or delete against theconceptual schema, then issue a query for data in themodified 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 getback the instance of the table as if there were notransformations in the channel, but with altered schema
Graphical User Interfaces as UpdatableViews
-
8/2/2019 James Long 1
22/32
Definition of Information Preservation
22
A transformation O is a transformation of schema anddata, expressed in extended relational algebra, thatsatisfies the following information preservationproperties:
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) CRF 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
-
8/2/2019 James Long 1
23/32
Sample Proof (Insert through HPartition,
part 1)
Graphical User Interfaces as UpdatableViews
23
Show me what is in thecolumns C of table T_a inthe input (natural) schema
-
8/2/2019 James Long 1
24/32
Sample Proof (part 2)
Graphical User Interfaces as UpdatableViews
24
Standard relationalequivalence
Use instance-at-a-timesemantics as axiom
-
8/2/2019 James Long 1
25/32
Sample Proof (part 3)
Graphical User Interfaces as UpdatableViews
25
Result is whatever was in T_abefore, plus the inserted rows
Q
-
8/2/2019 James Long 1
26/32
Definition of Information Preservation
26
A transformation O that cannot be expressed inrelational 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) CRF 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 UpdatableViews
-
8/2/2019 James Long 1
27/32
Referential Integrity
27
Natural Schema:T1.(A,B) References T2.(A,B)
X Y A BT1:
T2:
T3:
T4:
A B C
A B P CAfter Horizontal Merge:
A M N O SAfter Pivot:
Provenance columnchanges key FK nolonger references the
whole key, nor the wholetable
Some key data
shifts to schemainformation partof FK has nothingto point to
We want to respect the keys and foreignkeys that are present in the naturalschema
Graphical User Interfaces as UpdatableViews
-
8/2/2019 James Long 1
28/32
Referential Integrity
28
Maintain referential integrity through channel operators, and offer the
developer feedback as to efficiency
Tier 1: FK(T1.C1T2.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 FKs defined on T1.C1, with OR semantics Representable by triggers (Ins, Up on T1; Up, Del on T2)
Tier 3: Check(Q1Q2) Representable by triggers, but potentially inefficient due to query complexity
Graphical User Interfaces as UpdatableViews
-
8/2/2019 James Long 1
29/32
What do we have here? (In large print)
29
An application-specific queryinterface, enabled by a new wayto look at data independence via a
channel
Graphical User Interfaces as UpdatableViews
-
8/2/2019 James Long 1
30/32
Future Work: Alternative Data Models
30
Relational model has a well-understood, standarddata 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 UpdatableViews
-
8/2/2019 James Long 1
31/32
XML Channels
31
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 UpdatableViews
-
8/2/2019 James Long 1
32/32
32
This work is supported in part by Collins Medical Trust, by DHHS NIH National Institute of DiabetesDigestive and Kidney Diseases No. 5-R33-DK061778-03 awarded to Oregon Health & ScienceUniversity (OHSU), and by NSF grant No. 0534762.
Graphical User Interfaces as Updatable
Query Interface
Channel
Thanks!