Introduction to Data Management CSE 344 Section 6: Relational - PowerPoint PPT Presentation

Introduction to Data Management CSE 344 Section 6: Relational Calculus and Some XML CSE 344 - Fall 2015 1

Relational Calculus Review Relational predicate P is a formula given by this grammar: P ::= atom | P ∧ P | P ∨ P | P ⇒ P | not(P) | ∀ x.P | ∃ x.P Query Q: Q(x1, … , xk) = P CSE 344 - Fall 2015 2

Likes(drinker, beer) Frequents(drinker, bar) Serves(bar, beer) Review Examples Find drinkers that frequent some bar that serves some beer they like. CSE 344 - Fall 2015 3

Likes(drinker, beer) Frequents(drinker, bar) Serves(bar, beer) Review Examples Find drinkers that frequent some bar that serves some beer they like. Q(x) = ∃ y. ∃ z. Frequents(x, y) ∧ Serves(y,z) ∧ Likes(x,z) CSE 344 - Fall 2015 4

Likes(drinker, beer) Frequents(drinker, bar) Serves(bar, beer) Review Examples Find drinkers that frequent some bar that serves some beer they like. Q(x) = ∃ y. ∃ z. Frequents(x, y) ∧ Serves(y,z) ∧ Likes(x,z) Find drinkers that frequent only bars that serves some beer they like. CSE 344 - Fall 2015 5

Likes(drinker, beer) Frequents(drinker, bar) Serves(bar, beer) Review Examples Find drinkers that frequent some bar that serves some beer they like. Q(x) = ∃ y. ∃ z. Frequents(x, y) ∧ Serves(y,z) ∧ Likes(x,z) Find drinkers that frequent only bars that serves some beer they like. Q(x) = ∀ y. Frequents(x, y) ⇒ ( ∃ z. Serves(y,z) ∧ Likes(x,z)) CSE 344 - Fall 2015 6

Likes(drinker, beer) Frequents(drinker, bar) Serves(bar, beer) Review Examples Find drinkers that frequent some bar that serves some beer they like. Q(x) = ∃ y. ∃ z. Frequents(x, y) ∧ Serves(y,z) ∧ Likes(x,z) Find drinkers that frequent only bars that serves some beer they like. Q(x) = ∀ y. Frequents(x, y) ⇒ ( ∃ z. Serves(y,z) ∧ Likes(x,z)) Find drinkers that frequent some bar that serves only beers they like. CSE 344 - Fall 2015 7

Likes(drinker, beer) Frequents(drinker, bar) Serves(bar, beer) Review Examples Find drinkers that frequent some bar that serves some beer they like. Q(x) = ∃ y. ∃ z. Frequents(x, y) ∧ Serves(y,z) ∧ Likes(x,z) Find drinkers that frequent only bars that serves some beer they like. Q(x) = ∀ y. Frequents(x, y) ⇒ ( ∃ z. Serves(y,z) ∧ Likes(x,z)) Find drinkers that frequent some bar that serves only beers they like. Q(x) = ∃ y. Frequents(x, y) ∧ ∀ z.(Serves(y,z) ⇒ Likes(x,z)) CSE 344 - Fall 2015 8

Likes(drinker, beer) Frequents(drinker, bar) Serves(bar, beer) Review Examples Find drinkers that frequent some bar that serves some beer they like. Q(x) = ∃ y. ∃ z. Frequents(x, y) ∧ Serves(y,z) ∧ Likes(x,z) Find drinkers that frequent only bars that serves some beer they like. Q(x) = ∀ y. Frequents(x, y) ⇒ ( ∃ z. Serves(y,z) ∧ Likes(x,z)) Find drinkers that frequent some bar that serves only beers they like. Q(x) = ∃ y. Frequents(x, y) ∧ ∀ z.(Serves(y,z) ⇒ Likes(x,z)) Find drinkers that frequent only bars that serves only beer they like. CSE 344 - Fall 2015 9

Likes(drinker, beer) Frequents(drinker, bar) Serves(bar, beer) Review Examples Find drinkers that frequent some bar that serves some beer they like. Q(x) = ∃ y. ∃ z. Frequents(x, y) ∧ Serves(y,z) ∧ Likes(x,z) Find drinkers that frequent only bars that serves some beer they like. Q(x) = ∀ y. Frequents(x, y) ⇒ ( ∃ z. Serves(y,z) ∧ Likes(x,z)) Find drinkers that frequent some bar that serves only beers they like. Q(x) = ∃ y. Frequents(x, y) ∧ ∀ z.(Serves(y,z) ⇒ Likes(x,z)) Find drinkers that frequent only bars that serves only beer they like. Q(x) = ∀ y. Frequents(x, y) ⇒ ∀ z.(Serves(y,z) ⇒ Likes(x,z)) CSE 344 - Fall 2015 10

Uses(recipe, ingredient_name) Ingredient(name, category) Exercises We have a database of recipes and ingredients. An ingredient has a name and a category (meat, dairy, veggie, etc.). The relation uses captures which recipe uses which ingredient. Write a relational calculus query that returns all recipes that use ingredients from at least two different categories. Q(r) = ∃ i1. ∃ i2. ∃ c1. ∃ c2. Uses(r, i1) ∧ Uses(r,i2) ∧ Ingredient(i1,c1) ∧ Ingredient(i2,c2) ∧ c1 != c2 CSE 344 - Fall 2015 11

Uses(recipe, ingredient_name) Ingredient(name, category) Exercises We have a database of recipes and ingredients. An ingredient has a name and a category (meat, dairy, veggie, etc.). The relation uses captures which recipe uses which ingredient. Write a relational calculus query that returns all recipes that use only one category of ingredients. Return the recipe and the category. Q(r,c) = ∃ i. ∃ n. Uses(r,i) ∧ Ingredient(n,c) ∧ ∀ i2.(Uses(r,i2) ⇒ Ingredient(i2,c)) CSE 344 - Fall 2015 12

Uses(recipe, ingredient_name) Ingredient(name, category) Exercises We have a database of recipes and ingredients. An ingredient has a name and a category (meat, dairy, veggie, etc.). The relation uses captures which recipe uses which ingredient. Write a relational calculus query that returns all ingredients that are never used in a recipe. Q(i) = ∃ c. Ingredient(i,c) ∧ not( ∃ r. Uses(r,i)) CSE 344 - Fall 2015 13

Uses(recipe, ingredient_name) Ingredient(name, category) Exercises We have a database of recipes and ingredients. An ingredient has a name and a category (meat, dairy, veggie, etc.). The relation uses captures which recipe uses which ingredient. Write a relational calculus query that returns pairs of recipes that use the same categories of ingredients but never dairy. Q(r1,r2) = ∃ i1. ∃ i2. ∃ c. (c != ‘Dairy’) ∧ Uses(r1,i1) ∧ Uses(r2,i2) ∧ ∀ i3. (Uses(r1,i3) ⇒ ∃ i4. (Uses(r2,i4) ∧ Ingredient(i3,c) ∧ Ingredient(i4,c)) ∧ ∀ i5. (Uses(r2,i5) ⇒ ∃ i6. (Uses(r1,i6) ∧ Ingredient(i5,c) ∧ Ingredient(i6,c))) CSE 344 - Fall 2015 14

XML CSE 344 - Fall 2015 15

Saxon Setup • Download Saxon http://sourceforge.net/projects/saxon/files/ • Download Mondial dataset and DTD • Make sure Java VM is installed

Practice with Saxon • Open text editor and save a query: <result> { Xpath expression} </result> • Run the program $ java -cp saxon9he.jar net.sf.saxon.Query ex.xq > a1.xml • To pretty print $ xmllint --format a1.xml