Computerized Spreadsheets

1. Logical SpreadsheetsMichael GeneserethLogic GroupStanford Universityin collaboration withMike Kassoff and Eric Kao

2. Computerized Spreadsheets Huge Success individual users companies conglomerates Good Features Automatic computation of values Ease of specification using simple math formulas

3. Limitations of Traditional Spreadsheets • Functional formulas • Unidirectional Update • B1 B2 B3 B3 = B1 + B2 5 2 3

4. Examples of Non-Functional Constraints • Scheduling • Start times must be before end times • Room 104 may not be scheduled after 5:00 pm • Only senior managers can reserve the third floor conference room • Travel Reservations • The number of lap infants in a group on a flight must not exceed the number of adults. • Academic Programs • Students must take at least 2 math courses

5. Logical Spreadsheets Extension Relational constraints on the values of cells Good Features Automatic computation of values Ease of set-up (using logical formulas) Challenges Automatic update Temporary inconsistencies User feedback

6. Spreadsheet/Websheet Applications Data management Design/Configuration Smart Forms Interactive Answers

7. Definitions

8. Logical Spreadsheets A logical spreadsheet consists of a finite set of cells, a set of possible values for those cells, and a set of constraints on cell values. Cells: p, q, r, s Possible Values: a, b, c, d Constraints: If p is assigned a, then q must be assigned b or c. If r is not a, then s must have the same value as q.

9. Value Assignments A value assignment is a function from cells to values. A value assignment is complete if this function is total. Otherwise, it is partial. In what follows, we formalize value assignments as sets of ground, atomic sentences in which the relation represents the cell and the argument represents the value. {p(a), q(b), r(c)}

10. Completeness • A value assignment  for a logical spreadsheet is complete if and only if it assigns exactly one value to each cell in the spreadsheet, i.e. for every cell  there is exactly one constant  such that (). • Cells = {p, q, r} • Values = {a, b, c, d} •  = {p(x)  q(x), q(x)  r(x)} • = {p(a), q(a), r(a)}

11. Constraint Language A constraint is a boolean formula involving cell names, object names, variables, and equality. Cell p must be a or both q and r must both be b. p(a)  (q(b)  r(b)) If p is assigned a, then q must be assigned b or c. p(a)  q(b)  q(c) If p and q are the same, then r must be different. p(x)  q(x)  r(x)

12. Consistency • A value assignment  is consistent with a self-consistent set of constraints  if and only if they do not logically entail a contradiction, i.e.    | . • Cells = {p, q, r} • Values = {a, b, c, d} •  = {p(x)  q(x), q(x)  r(x)} • = {p(a ), q(a), r(a)} NB: In managing logical spreadsheets, we must sometimes deal with inconsistent value assignments.

13. Goals and Limitations User’s Goal complete, consistent value assignment satisfying unwritten constraints as well Limitation one cell at a time changing

14. General Principles Obedience - specified update made together with all other updates logically required by the update Propagation - consequences shown Fairness - no arbitrary choices made by the system Conservation - no unnecessary loss of information

15. Obedience Update Outcome: p q r a b p q r a

16. Conservation Update Outcome: p q r a b c p q r a c

17. p q r a Categorical Entailment Update p(x)  r(x) Outcome: p q r a a

18. Categorical Entailment Update p(x)  r(x) Outcome: p q r erase a a p q r

19. Categorical Entailment Update p(x)  r(x) Outcome: p q r a a erase p q r

20. Categorical Entailment Update p(x)  r(x) Outcome: p q r a a b p q r b b

21. p q r a a a p q r a a a Ambiguity Update p(x) q(x) r(x) Possible Outcomes: p q r p q r a a a a p q r a

22. p q r a,b,c a,b,c a,b,c Explosion! Logical Entailment:  |  if and only if every model of  is a model of . If a set of sentences is inconsistent, then it logically entails everything. s a,b,c

23. Existential -Entailment A set of sentences existentially entails a sentence relative to (written  | ) if and only if there is an -consistent subset of  that, when added to , logically entails . ’  and ’ |  and ’ |  Existential -entailment is not explosive. It generates only “justified” conclusions.

24. Example Constraints: p(x) q(x) r(x) p(a) q(a)  s(c) q(a)  r(a)  t(c) Data: Conclusions: p(a) s(c) q(a) t(c) r(a)

25. Spreadsheet State Spreadsheet: C,U, C - set of names for cells U - set of names for values  - set of constraints State:  - base assignment  - deletion set - a set of cells  - computed assignment - what the user sees

26. Initial State Spreadsheet: C,U, C - set of names for cells U - set of names for values  - set of constraints Initial state:  = {}  = {}  = {() |  |=() and C and U}

27. Addition Rule Addition:  Base Assignment ’ =  - { | {} |=}  {} Deletion Set ’ =  - { | {} |=() for some U}

28. Deletion Rule Deletion:  Base Assignment ’ =  - { | {} |= } Deletion Set ’ =   {} where  = ()}

29. Existential Entailment • A set  of ground atoms existentially entails  with respect to  (written  |= ) if and only if there is a subset  of  that meets the following conditions. •  is consistent with .    |  (2)  and  logically entail . •   |= 

30. Update Rule Entailment Set: * = { | ’ |= } Diminished Entailment Set ** = *- {()  * |   } Unique Diminished Entailment Set *** = **- {(1)  ** | (2)  ** and 12} Computed Assignment: ’ = ***  

31. Example

32. Example

33. Example

34. Example

35. Example

36. Example

37. Example

38. Example

39. Example

40. Example

41. Conclusion

42. clear(a)clear(b) clear(c) table(a)table(b) table(c) clear(a)clear(b) table(b) table(c) on(b,c) clear(a)table(c) on(b,c) on(a,b) X Dynamic Constraints Static Constraints Dynamic Constraints clear(a)table(c) on(a,b) on(b,c) clear(a)table(c) on(a,b) on(b,a) X

43. Existential Entailment Computation • The naïve approach to computing existential conclusions from  is to take each subset of  and derive logical consequences. While this works, it is exponential in the size of the dataset). • Solution: Transform constraints and use deduction.   ’ • ’ = { | ’ ’ |= } = { | ’ |= } Advantage: Computational effort focussed on those cases where there are conclusions.

44. Differential Computation The naïve approach to computing updates is to recompute values for each cell on each step. Possible to restrict effort to just those cells whose values are affected by updates. Differentiatial computation based on differential logic [Orman][Genesereth, Kassoff].

45. Logical Spreadsheets as CSPs A logical spreadsheet can be viewed as a constraint satisfaction problem (1) with support for inconsistency in the data (2) with support for interactive solution and/or interactive solution modification.

46. Logical Spreadsheets as Databases A logical spreadsheet can be usefully viewed as a deductive database (database with constraints) (1)consisting of single-valued, unary relations (2) with the possibility of incompleteness and inconsistency in the data (3) one row at a time changed

47. Additional Spreadsheet Issues User Interface Current Value Assignment (Value Display) Inconsistency and Incompleteness (Colors) Conflict Sets (Mouse-over) Relatives (Mouse-over) Choices (Disabling and Menus) Automation Deconfliction Satisfaction

48. Administrator Interface

49. Spreadsheet/Websheet Applications Data management Design/Configuration Smart Forms Interactive Answers

50. Collaborative Spreadsheets