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

1

Introduction to Data Management CSE 344

Section 6: Relational Calculus and Some XML

CSE 344 - Fall 2015

Relational Calculus Review

P ::= atom | P ∧ P | P ∨ P | P⇒P | not(P) | ∀x.P | ∃x.P

Relational predicate P is a formula given by this grammar:

Q(x1, …, xk) = P

Query Q:

CSE 344 - Fall 2015 2

Review Examples

Find drinkers that frequent some bar that serves some beer they like.

Likes(drinker, beer) Frequents(drinker, bar) Serves(bar, beer)

CSE 344 - Fall 2015 3

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)

Likes(drinker, beer) Frequents(drinker, bar) Serves(bar, beer)

CSE 344 - Fall 2015 4

Review Examples

Find drinkers that frequent some bar that serves some beer they like. Find drinkers that frequent only bars that serves some beer they like.

Q(x) = ∃y. ∃z. Frequents(x, y)∧Serves(y,z)∧Likes(x,z)

Likes(drinker, beer) Frequents(drinker, bar) Serves(bar, beer)

CSE 344 - Fall 2015 5

Review Examples

Find drinkers that frequent some bar that serves some beer they like. Find drinkers that frequent only bars that serves some beer they like.

Q(x) = ∃y. ∃z. Frequents(x, y)∧Serves(y,z)∧Likes(x,z) Q(x) = ∀y. Frequents(x, y)⇒ (∃z. Serves(y,z)∧Likes(x,z))

Likes(drinker, beer) Frequents(drinker, bar) Serves(bar, beer)

CSE 344 - Fall 2015 6

Review Examples

Find drinkers that frequent some bar that serves some beer they like. Find drinkers that frequent only bars that serves some beer they like. Find drinkers that frequent some bar that serves only beers they like.

Q(x) = ∃y. ∃z. Frequents(x, y)∧Serves(y,z)∧Likes(x,z) Q(x) = ∀y. Frequents(x, y)⇒ (∃z. Serves(y,z)∧Likes(x,z))

Likes(drinker, beer) Frequents(drinker, bar) Serves(bar, beer)

CSE 344 - Fall 2015 7

Review Examples

Find drinkers that frequent some bar that serves some beer they like. Find drinkers that frequent only bars that serves some beer they like. Find drinkers that frequent some bar that serves only beers they like.

Q(x) = ∃y. ∃z. Frequents(x, y)∧Serves(y,z)∧Likes(x,z) Q(x) = ∀y. Frequents(x, y)⇒ (∃z. Serves(y,z)∧Likes(x,z)) Q(x) = ∃y. Frequents(x, y)∧∀z.(Serves(y,z) ⇒ Likes(x,z))

Likes(drinker, beer) Frequents(drinker, bar) Serves(bar, beer)

CSE 344 - Fall 2015 8

Review Examples

Find drinkers that frequent some bar that serves some beer they like. Find drinkers that frequent only bars that serves some beer they like. Find drinkers that frequent only bars that serves only beer they like. Find drinkers that frequent some bar that serves only beers they like.

Q(x) = ∃y. ∃z. Frequents(x, y)∧Serves(y,z)∧Likes(x,z) Q(x) = ∀y. Frequents(x, y)⇒ (∃z. Serves(y,z)∧Likes(x,z)) Q(x) = ∃y. Frequents(x, y)∧∀z.(Serves(y,z) ⇒ Likes(x,z))

Likes(drinker, beer) Frequents(drinker, bar) Serves(bar, beer)

CSE 344 - Fall 2015 9

Review Examples

Find drinkers that frequent some bar that serves some beer they like. Find drinkers that frequent only bars that serves some beer they like. Find drinkers that frequent only bars that serves only beer they like. Find drinkers that frequent some bar that serves only beers they like.

Q(x) = ∃y. ∃z. Frequents(x, y)∧Serves(y,z)∧Likes(x,z) Q(x) = ∀y. Frequents(x, y)⇒ (∃z. Serves(y,z)∧Likes(x,z)) Q(x) = ∃y. Frequents(x, y)∧∀z.(Serves(y,z) ⇒ Likes(x,z))

Likes(drinker, beer) Frequents(drinker, bar) Serves(bar, beer)

10 CSE 344 - Fall 2015

Q(x) = ∀y. Frequents(x, y)⇒ ∀z.(Serves(y,z) ⇒ Likes(x,z))

Exercises

CSE 344 - Fall 2015 11

Uses(recipe, ingredient_name) Ingredient(name, category)

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

Exercises

CSE 344 - Fall 2015 12

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

• ne category of ingredients. Return the recipe and the category.

Uses(recipe, ingredient_name) Ingredient(name, category)

Q(r,c) = ∃i. ∃n. Uses(r,i) ∧ Ingredient(n,c) ∧ ∀i2.(Uses(r,i2) ⇒ Ingredient(i2,c))

Exercises

CSE 344 - Fall 2015 13

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.

Uses(recipe, ingredient_name) Ingredient(name, category)

Q(i) = ∃c. Ingredient(i,c) ∧ not(∃r. Uses(r,i))

Exercises

CSE 344 - Fall 2015 14

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.

Uses(recipe, ingredient_name) Ingredient(name, category)

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)))

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