CSPs and Relational DBs: Foundations and Connections

1. CSPs and Relational DBs Foundations of Constraint Processing CSCE421/821, Fall 2004: www.cse.unl.edu/~choueiry/F04-421-821/ Berthe Y. Choueiry (Shu-we-ri) Avery Hall, Room 123B choueiry@cse.unl.edu Tel: +1(402)472-5444 CSPs and Relational DBs

2. Background Strong historical & conceptual connections exist between: • Constraint Databases & Constraint Logic Programming • Query processing in Relational DBs &Solving CSPs Indeed: • Constraint databases (deductive BD, Datalog) and constraint logic programming (CLP) share the representation language (restricted forms of FOL) • Relational databases and Constraint Satisfaction share computation mechanisms CSPs and Relational DBs

3. Relations Binary relation: Given two sets Da and Db, a set of any 2-tuples < x, y> with  Da and y  Db defines a relationRab Ra,b = {(x, y)}  Da x Db Function:(special binary relation) For any element x in Da there is at most one tuple < x, ? >  Rab Da is called the domain of the the function Db is called the range of the function k-ary relation: Given k sets D1, D2, …, Dk, any set of k-tuples < x1, x2, ..., xk > with x1 D1, x2 D2, …, xk Dk defines a k-ary relation: R1, 2, ..., k = {(x1, x2, ..., xk)}  D1 x D2 x … x Dk CSPs and Relational DBs

4. C1 A V B C3 C2 E G F V D V Representation of relations Binary arrays:  2-dim binary array (i.e., bit matrix):  more generally, k-dimensional binary arrays Tables: CSPs and Relational DBs

5. Comparison of terminology CSPs and Relational DBs

6. Relational Algebra: operations on relations Database: • Intersection • Union • Difference • Selection • Projection • Join (Cartesian product), etc. CSP: • The above and composition (= combination of join and projection) CSPs and Relational DBs

7. Operators in Relational Algebra • Selection, projection: • unary operators, defined on one relation • Intersection, union, difference: • binary operators • relations must have same scope • Join: • binary operator • relations have different scopes CSPs and Relational DBs

8. x - y > 10 x + y > 10 Intersection • Input: two relations of the same scope • Output: a new more restrictive relation with the same scope, made of tuples that are in all the input relations (simultaneously) • Bit-matrix operation: logicalAND RR' R’’ • RR’ ? RR'’ ? Okay Not defined CSPs and Relational DBs

9. Union • Input: two relations of the same scope • Output:a new less restrictive relation with the same scope made of tuples that are in any of the input relations • Bit-matrix operation: logical OR RR' R’’ • RR'? RR''? Not defined Okay CSPs and Relational DBs

10. Difference • Input: two relations R and R' of the same scope • Output: a new more restrictive relation than R made of tuples that are in R but not in R' • Bit-matrix operation: Boolean difference RR' R’’ R - R'? R - R''? Not defined Okay CSPs and Relational DBs

11. Selection • Input: A relation R and some test/predicate on attributes of R • Output: A relation R', same scope as R but containing only a subset of the tuples in R (those that satisfy the predicate) • Relation operation: row selection R Select such that x1> x2, x1> x2(R)? CSPs and Relational DBs

12. Projection • Input: A relation R and a subset s of the scope (attributes) • Output: A relation R' of scope s with the tuples rewritten such that positions not in s are removed • Relation operation: column elimination R Project R on x1, x2, x1, x2(R)? CSPs and Relational DBs

13. Join (natural join) Input: Two relations R and R' Output: A relation R'', whose scope is union of scopes of R and R' and tuples satisfy both R and R'.  R and R' have no attribute common: Cartesian product  R and R' have an attribute in common, compute all possibilities Operation: Compute all solutions to a CSP. RR" Join R and R'', RR''? CSPs and Relational DBs

14. Composition of relationsMontanari'74 Input: two binary relations Rab and Rbc with 1 variable in common. Output: a new induced relation Rac (to be combined by intersection to a pre-existing relation between them, if any). Bit-matrix operation: matrix multiplication Note: - generalization as constraint synthesis [Freuder, 1978] - Direct (explicit) vs. induced (implicit) relations CSPs and Relational DBs

15. Questions • Given • two variables V1 and V2 and • two constraints C1and C2 between them How do the two expressions C1C2 and C1 C2 relate? • Given • three variables V1, V2, V3 and • the binary constraints CV1, V2 and CV2, V3 write the induced CV1, V3, in relational algebra • Given • three variables V1, V2, V3 and • the binary constraints CV1, V2, CV1, V3, and CV2, V3, write the new induced C’V1, V3 in relational algebra CSPs and Relational DBs

16. Comparison of Terminology CSPs and Relational DBs

17. DB vs. CSP DB: • Relations • Few relations in a query • Usually, high arity relations • Domains • Large domains, many, many tuples • Mostly finite (CDB introduce continuous domains, restricted to linear constraints) • Seeking (almost) all tuples • Cost model: I/O disk access, memory size CSPs: • Relations • Many, many relations • Mostly low-arity relations (binary) • Domains • Small-size domains • Finite (frequent), but also continuous (with arbitrary relations). • Seeking (in general) one solution • Cost model: computational cost CSPs and Relational DBs