Relational Algebra by Way of Adjunctions Jeremy Gibbons (joint work - PowerPoint PPT Presentation

Relational Algebra by Way of Adjunctions Jeremy Gibbons (joint work with Fritz Henglein, Ralf Hinze, Nicolas Wu) S-REPLS#10, Sep 2018

Relational Algebra by Way of Adjunctions 2 1. Overview • relational databases in terms of certain monads (sets, bags, lists) • monads support comprehensions , providing a query notation : [ ( customer . name , invoice . amount ) | customer ← customers , invoice ← invoices , customer . cid == invoice . customer , invoice . due � today ] which are the essence of SQL queries: SELECT name , amount FROM customers , invoices WHERE cid = customer AND due � today • monads have nice mathematical foundations via adjunctions • monad structure explains aggregation , selection , projection • less obvious how to explain join

� � � � � � Relational Algebra by Way of Adjunctions 3 2. Galois connections Relating monotonic functions between two ordered sets: f ( A , � ) ( B , ⊑ ) means f b � a ⇐ ⇒ b ⊑ g a ⊥ g For example, inj × k ( R , ≤ R ) ⊥ ( Z , ≤ Z ) ( Z , � ) ⊥ ( Z , � ) floor ÷ k “Change of coordinates” can sometimes simplify reasoning. Eg rhs gives n × k � m ⇐ ⇒ n � m ÷ k , and multiplication is easier to reason about than rounding division.

� � � � Relational Algebra by Way of Adjunctions 4 3. Adjunctions Adjunctions are the categorical generalisation of Galois connections. Given categories C , D , and functors L : D → C and R : C → D , adjunction L means ∗ ⌊ - ⌋ : C ( L X , Y ) ≃ D ( X , R Y ) : ⌈ - ⌉ C D ⊥ R The functional programmer’s favourite example is given by currying : - × P with curry : Set ( X × P , Y ) ≃ Set ( X , Y P ) : uncurry Set ⊥ Set ( - ) P hence definitions and properties of apply = uncurry id Y P : Y P × P → Y .

� � Relational Algebra by Way of Adjunctions 5 4. Free commutative monoids Free/forgetful adjunction: Free CMon ⊥ Set with ⌊ - ⌋ : CMon ( Free A , ( M , ⊗ , ǫ )) ≃ Set ( A , U ( M , ⊗ , ǫ )) : ⌈ - ⌉ U Unit and counit: single A = ⌊ id Free A ⌋ : A → U ( Free A ) � M � = ⌈ id M ⌉ : Free ( U M ) → M -- for M = ( M , ⊗ , ǫ ) whence, for h : Free A → M and f : A → U M = M , h = � M � · Free f ⇐ ⇒ U h · single A = f ie 1-to-1 correspondence between (i) homomorphisms from the free commutative monoid (bags) and (ii) their behaviour on singletons.

Relational Algebra by Way of Adjunctions 6 5. Aggregation Aggregations are bag homomorphisms: aggregation monoid action on singletons count ( N , 0 , + ) � a � ֏ 1 sum ( R , 0 , + ) � a � ֏ a max ( Z ∪ {−∞} , −∞ , max ) � a � ֏ a all ( B , True , ∧ ) � a � ֏ a Projection π i = Bag i is a homomorphism—just functorial action. Selection σ p is also a homomorphism, to bags, with action guard : ( A → B ) → Bag A → Bag A guard p a = if p a then � a � else ∅ Projection and selection laws follow from homomorphism laws.

� � Relational Algebra by Way of Adjunctions 7 6. Monads Finite bags form a monad ( Bag , union , single ) with Bag = U · Free union : Bag ( Bag A ) → Bag A single : A → Bag A which justifies the use of comprehension notation � f a b | a ← x , b ← g a � and its equational properties. In fact, any adjunction L ⊣ R yields a monad ( T , µ, η) on D , where T = R · L L µ A = R ⌈ id A ⌉ L : T ( T A ) → T A C ⊥ D η A = ⌊ id A ⌋ : A → T A R

Relational Algebra by Way of Adjunctions 8 7. Maps Database indexes are essentially maps Map K V = V K . Maps ( - ) K from K form a monad (the Reader monad in Haskell), so arise from an adjunction. The laws of exponents follow from this adjunction (and from those for products and coproducts): Map 0 V ≃ 1 Map 1 V ≃ V Map ( K 1 + K 2 ) V ≃ Map K 1 V × Map K 2 V Map ( K 1 × K 2 ) V ≃ Map K 1 ( Map K 2 V ) Map K 1 ≃ 1 Map K ( V 1 × V 2 ) ≃ Map K V 1 × Map K V 2 : merge

� � Relational Algebra by Way of Adjunctions 9 8. Indexing Relations are in 1-to-1 correspondence with set-valued functions: J Rel Set where J embeds, and E R : A → Set B for R : A ∼ B . ⊥ E Moreover, the correspondence remains valid for bags: index : Bag ( K × V ) ≃ Map K ( Bag V ) Together, index and merge give efficient relational joins: x f ⋈ g y = flatten ( Map K cp ( merge ( groupBy f x , groupBy g y ))) groupBy : Eq K ⇒ ( V → K ) → Bag V → Map K ( Bag V ) flatten : Map K ( Bag V ) → Bag V expressible also via comprehensive comprehensions .

Relational Algebra by Way of Adjunctions 10 9. Finiteness A catch: • being finite is important, for aggregations • begin a monad is important, for comprehensions • finite bags form a monad (as above) • maps form a monad • finite maps do not form a monad: the unit η a = (λ k → a ) : A → Map K A generally yields an infinite map. How to reconcile finiteness of maps with being a monad?

Relational Algebra by Way of Adjunctions 11 10. Graded monads Grading (indexing, parametrizing) a monad by a monoid: an indexed family of endofunctors that collectively behave like a monad. For monoid M = ( M , ⊗ , ǫ ) , the M -graded monad ( T , µ, η) is a family T m of endofunctors indexed by m : M , with µ X : T m ( T n X ) → T m ⊗ n X η X : X → T ǫ X satisfying the usual laws. These too arise from adjunctions (even though T itself is not an endofunctor!). For example, think of finite vectors, indexed by length. We use the monoid ( K ∗ , + + , � � ) of finite sequences of finite key types K .

Relational Algebra by Way of Adjunctions 12 11. Query transformations These can now all be shown by equational reasoning: -- when i · j = i π i · π j = π i σ p · π i = π i · σ -- when p · i = p p � M � · Bag f · π i = � M � · Bag ( f · i ) � M � · Bag f · σ = � M � · Bag (λ a → if p a then f a else ǫ ) p x f ⋈ g y = Bag swap ( y g ⋈ f x ) ( x f ⋈ g y ) ( g · snd ) ⋈ h z = Bag assoc ( x f ⋈ ( g · fst ) ( y g ⋈ h z )) ⇒ f ′ ( i a ) = g ′ ( j b ) = π i x f ′ ⋈ g ′ π j y π i × j ( x f ⋈ g y ) -- when f a = g b ⇐ σ p ( x f ⋈ g y ) = σ q x f ⋈ g σ r y -- when p ( a , b ) = q a ∧ r b for monoid M = ( M , ⊗ , ǫ ) .

Relational Algebra by Way of Adjunctions 13 12. Summary • monad comprehensions for database queries • structure arising from adjunctions • equivalences from universal properties • fitting in relational joins , via indexing and graded monads • calculating query transformations Paper to appear at ICFP 2018. Thanks to EPSRC Unifying Theories of Generic Programming for funding.