Outline

• Logic for Relations
• In-Class Exercise

Introduction

• On Monday, we began talking about algebraic and logical query languages.
• We showed how to extend the relational algebra operations from earlier in the semester to work on bags (multisets or sets with repeats) rather than sets.
• We said the relational algebra is often used as an intermediate language between SQL and the final computation of the results of a query.
• It was important to extend this algebra to bags, as bags are often more natural in terms of computing results of a query.
• We then extended the relation algebra, with new and modified operators: `delta` (distinct), `gamma` (grouping and aggregation), `tau` (sorting), extended projection (`pi` with renaming and arithmetic and string expressions), and various outer joins.
• Today, we are going to look at a second query language, this time based on logic, which motivated some of the design choices in SQL.
• We will then talk about writing queries in SQL.

Datalog

• The logical query language we will consider is called datalog ("database logic").
• A datalog program will consist of a series of if-then rules which can be used to represent knowledge about some domain (i.e., be a database for that domain).
• These rules will express the idea that from certain combinations of tuples in certain relations we may infer some other tuple must be in some other relation, or must be in the answer to a query.
• To get started with datalog, we introduce its building blocks...

Predicates and Atoms

• Relations in Datalog are represented by predicates which have a fixed number of input slots.
• An atom is a predicate which has been supplied with arguments for each of these input slots. For example, `P(x_1, ..., x_n)`.
• A predicate can take as arguments either constants like the number 7 or a string like "hi there" or a fixed value of some type, variables like `x`, `y`, `z` ..., or expressions built out of these.
• One can think of a predicate `P(x_1, ..., x_n)` as a true false function which returns true if the tuple of atoms `(x_1, ..., x_n)` belongs to the relation that `P` represents, and false otherwise.
• For example, if `R` is the relation:

  A|B
  ---
  1|2
  3|4
  

  then `R(1,2)` and `R(3,4)` are true atoms and `R(1,1)` is false. `R(1,z)` will return true if `z=2`, and otherwise, return false.

Arithmetic Atoms

• Arithmetic atoms are a special kind of atom involving a comparison between two arithmetic expressions. For example, `x < y` or `x+1 ≥ y+4 times z`.
• The non-arithmetic atom examples of the previous slide are called relational atoms.

Datalog Rules and Queries

• Operations similar to those of the relational algebra are described in Datalog by rules, which consist of
  1. A relational atom called the head, followed by
  2. The symbol `<-`, which can be read as "if", followed by
  3. A body consisting of one or more atoms called subgoals which are connected by AND and which may be optionally preceded by the operator NOT.
• For example,

  LongMovie(t,y) <- Movies(t,y,l,g,s,p) AND l ≥ 100 
  

  is an example Datalog rule
• The head of this rule represents a relation consisting of tuples (t,y) such that there exists a Movies(t,y,l,g,s,p) where `l ≥ 100`. I.e., a movie title and year of a movie that is more than 100 minutes long.
• Since we don't use the variables g,s,p in computing LongMovie, we could have made them anonymous by replacing them with underscores, making the rule look like:

  LongMovie(t,y) <- Movies(t,y,l,_,_,_) AND l ≥ 100 
  

• Sometimes we will follow Prolog's convention and use commas in place of AND:

  LongMovie(t,y) <- Movies(t,y,l,_,_,_) , l ≥ 100 
  

• This rule corresponds to the relational algebra query:
  `mbox(LongMovie) := pi_{mbox(title, year)}(sigma_{mbox(length) >= 100}(mbox(Movies)))`
• A query in Datalog is a collection of one or more rules.
• If there is only one relation that appears in the heads of these rules, then the value of this relation is the answer to the query. I.e., in the above, LongMovie is the answer to the query.
• When there is more than one rule head, we will assume the relation of the last rule head is the answer to the query, and all the other rule heads are used as assisting in the calculation of this answer.

Meaning of Datalog Rules

• To evaluate a rule we imagine trying to satisfying each subgoal in the body with a tuple of constant values (i.e., each variable has been assigned a fixed value).
• We then look at the values that the variables in the head of the rule have been assigned to in the body and view that as the tuple satisfying the head.
• Consider

  Father("bob", "tony") <-. //notice this rule has an empty body. 
    // We view such a rule as a fact. Sometimes we just write: 
    // Father("bob", "tony").
  Father("tony", "john") <-.
  Ancestor(x,y) <- Father(x, y).
  Ancestor(x,z) <- Father(x, y), Father(y,z).
  

• You might wonder why we don't have the rule:

  Ancestor(x,z) <- Father(x, y), Ancestor(y,z).
  

  This would be a valid Prolog rule, but we don't usually allow a predicate to appear on both sides of a rule in Datalog.
• To compute Ancestor(x,z) we can go through our rules from top to bottom, and see Ancestor(x,y) <- Father(x, y) and so try to satisfy it by trying to satisfy Father(x, z) where we have unified z=y.
• We can then cycle down through our list of rules trying to satisfy Father(x, y) and satisfying using the rule Father("bob", "tony") <-. This gives x="bob" and z=y="tony", so the tuple ("bob", "tony") is in Ancestor.
• We can also satisfy Ancestor(x,z), using x="tony" and z=y="John" via the second Father fact above.
• Finally, we can satisfy Ancestor(x,z) via the second Ancestor rule using x='bob', z="john" and y="tony".
• In order to determine a concrete, finite set of tuples in our answer, we require each variable appear safely:
  • Every variable that appears anywhere in the rule must appear in some, nonnegated, relational subgoal of the body.
• So

  P(x,y) <- Q(x,z), NOT R(w,x,y), x < y
  

  is not safe because of how `y` occurs and how `w` occurs.

In-Class Exercise

1. Give an example of a rule involving negation and an arithmetic atom which is safe.
2. Express ordered pairs where `1 < x < y < 5` as a collection of Datalog facts.
3. Express the query that selects pairs where `y > 3`.

Post your solutions to the Oct 30 In-Class Exercise Thread.

Extensional and Intensional Predicates

• We often make a distinction between kinds of predicates.
• Extensional predicates are predicates whose relation is stored in a database (can be thought of a collection of fact rules).
• Intensional predicates are predicates whose relations are computed by applying one or more Datalog rules which are not facts.
• The collection of all intensional predicates in a database we call the intensional database (IDB).
• The collection of all extensional predicates in a database we call the extensional database (EDB).

Datalog Applied to Bags

• So far we have been imagining our Datalog rules as applying to sets.
• Just as with the relational algebra, we can imagine them applying to bags.
• For example, consider the rule:
  `H(x,z) <- R(x,y), S(y,z)` where relation `R(A,B)` is given by:

  A|B
  ---
  1|2
  1|2
  

  and `S(B,C)` is given by

  B|C
  ---
  2|3
  4|5
  4|5
  

  then we can compute `H` as

  1|3
  1|3
  

  by treating the two rows of `R` as distinct when trying to satisfy the clause.

Relational Algebra and Datalog

• Each of the relation algebra operations can be implemented using Datalog.
• The book shows how to do this for each operation, we only show a few.
• The set operation `T := R(vec{a}) cup S(vec(a))` could be implemented via two rules: `T(vec{a}) <- R(vec{a})`. `T(vec{a}) <- S(vec{a})`.
• The set operation `T := R(vec{a}) cap S(vec(a))` could be implemented via the rule: `T(vec{a}) <- R(vec{a}), S(vec{a})`.
• The set operation `T := R(vec{a}) - S(vec(a))` could be implemented via the rule: `T(vec{a}) <- R(vec{a}), NOT \ S(vec{a})`.
• Projection can be implement via leaving out variable `pi_{vec{a}}(R(vec{a}, vec{b}))` then could be expressed as:
  `T(vec{a}) <- R(vec{a}, vec{b})`
• Selection can be implemented using arithmetic atoms, join and products via conjunctions. For example, `sigma_{x>10}(R(x))` could be implemented as:
  `T(x) <- R(x), x> 10`
  `R(a) times S(b)` could be implemented as:
  `T(a,b) <- R(a), S(b)`.