The LanguagesThe Languages

Query By Example (QBE) – Base on domain relational calculus.

Quel – Base on tuple relational calculus.

Datalog – modeled after the Prolog Language.

I.I. QBE: Introduction QBE: IntroductionFor Data ManipulationFor Data Manipulation

1. It has two dimensional syntax, so it requires two dimensions for its expressions.

2. QBE queries are expressed “by example.”

QBE tablesQBE tables

Users specify a query by filling in tables.

Reserves sid bid day

Books bid btitle type

Student sid sname class age

BasicsBasicsPrint names and ages of all students:Student Sid Sname Class age

P._N P._A

• Print all fields of students who are at least sophomores in ascending order by (class, age):Student Sid Sname Class age

P. AO(1). > sophomore

AO(2).

ContinueContinue Names of student younger than 20 or older than 25:

Duplicates not eliminated by default:

Student Sid Sname Class ageP.

P.

>25

<20

Student Sid Sname Class age

UNQ P. <25

Print unique student names older than 25.

Join QueriesJoin Queries

Joins are accomplished by repeating variables

Student Sid Sname Class Age

_Id P.S

Reserves Sid Bid day

_Id `11/30/01’

sid is the common attribute that join the two tables.

• Print students who borrowed a book on 11/30/01.

Join QueriesJoin Queries Types of books reserved by students who have

reserved a book for 11/30/01 and are older than 25.

Student Sid Sname Class age

_Id _S >25

Reserves Sid Bid day

_Id _B `11/30/01’

Books Bid Bname type

_B P.

AggregatesAggregatesQBE supports: AVG, COUNT, MIN, MAX, SUM

G. are the group by fields – All tuples have the same values.

Unnamed columns – Print result of an expression.

Student Sid Sname Class age

_Id G. G.P.AO _A P.AVG._A

Conditions BoxConditions BoxUsed to express conditions involving 2 or more

columns.Conditions can be expressed involving a group.

Print student names that are between the ages of 20 and 25.

Student Sid Sname Class age

P. _A

CONDITIONS

20 < _A AND _A <25

Inserting & Deleting TuplesInserting & Deleting TuplesTuple insertion:

• Tuple deletion: Delete all reservations for students with age<23

Student Sid Sname Class age

I. 1369 Lisa Senior 23

Student Sid Sname Class age

_Id < 23

Reserves Sid Bid day

D. _Id

II.II. Quel – Basic Structure Quel – Basic Structure

Range of t is r

- Declares t to be a tuple variable restricted to take on values of tuples in relation r.

•Retrieve (t.attribute)

- The retrieve clause is similar in function

to the select clause of SQL.

Continue…Continue…

Where P

- The where clause contains the selection predicate.

Quel Query StructureQuel Query Structure

Each t is a tuple variable.Each r is a relation.Each A is an attribute.The notation t.A denotes the value of tuple

variable t on attribute A.

Range of t is rRetrieve (t.A)Where P

ExampleExample

Find the names of all customers having a loan at the bank.

Range of t is borrowerRetrieve (t.CustomerName)

Example: Tuple VariablesExample: Tuple Variables

Find all customers who live in the same city as Smith.

Range of s is customerRange of t is customerRetrieve ( s.CustName )Where t.CustName = “Smith and s.CustCity = t.CustCity

Certain queries need 2 variables over the same relation.

Example:

Aggregate FunctionAggregate Function

Aggregate functions in Quel compute functions on groups of tuples.

An aggregate expression appear anywhere a constant may appear.

For ExampleFor ExampleIn a where clause.Find the average balance for all San Jose accounts.

Range of t is accountRetrieve avg (t.balance Where t.Branch = “San Jose”)

Modification of DatabaseModification of DatabaseDeletion:Deletion:

The form of a Quel deletion is:range of t is rdelete twhere p

•t can be implicitly defined.•Predicate P can be any valid Quel predicate.

If the where clause is omitted, all tuples in the relation are deleted.

Example:Example:

Delete all of Lee’s account record:

range of t is depositordelete twhere t.CustName = “Lee”

InsertionInsertion

Insertions are expressed in Quel using the append to.

Insert the account 123456 at the San Josebranch with a balance of $5000.00:

append to account (branch = “San Jose” account = “123456” balance = “5000”)

UpdatesUpdates

Updates are expressed in Quel using the replace command

Increase all account balances by 5 percent:

range of t is accountreplace t (balance = 1.05 * t.balance)

III.III. Datalog – Basic Structure Datalog – Basic Structure

Logic based language that allows recursive queries.

A Datalog program consists of a set of rules that defines views.

Example:Example:

Define a view relation vt containing account numbers and balances for accounts at the San Jose branch with a balance of over $100.

vt (A B) :- account ( “San Jose”, A B) B > 100

for all A,Bif (“San Jose”, A,B) E account A and B > 100then (A,B) E vt

Datalog RulesDatalog Rules

A positive literal has the form:

p(t1,t2,…,tn)A negative literal has the form:

not p(t1,t2,…,tn)

p is the name of the relation with n attributes.Each t is a constant or variable.

Continue…Continue…

Rules are built out of literals and have the form:

p(t1,t2,…,tn) :- L1,L2…LnEach L is a literalHead – the literal p(t1,t2,…,tn)Body – the rest of the literals.

Semantics of a Semantics of a RuleRuleAn instantiation rule is the result of

replacing each variable in the rule by some constant.

Rule defining v1:

v1 (A,B):- account(“SanJose”, A,B), B>100An instantiation Rule:

v1 (123456, 300) :- account (“San Jose”, “123456”,300) 300 > 100

Semantics of Recursion in DatalogSemantics of Recursion in Datalog The view relations of a recursive program containing a

set of rules K are defied to contain exactly the set of facts /. facts / are derived from rules K.

Facts / is compute by a recursive procedure called Datalog-Fixpoint:

At the end of the procedure, infer ( K, / )= / Datalog-Fixpoint will computes all the facts / until

the rules in the program has all negative literal or no more true record according to the rules K.

Procedure Datalog- Fixpoint / = set of facts in the database repeat

Old./ = // = / U infer (K,/)

until / = Old./