The Languages Query By Example (QBE) – Base on domain relational calculus. Quel – Base on tuple relational calculus. Datalog – modeled after the Prolog Language. I. QBE: Introduction For Data Manipulation 1. It has two dimensional syntax, so it requires two dimensions for its expressions. 2. QBE queries are expressed “by example.” QBE tables Users specify a query by filling in tables. Reserves Books Student sid sid bid day bid btitle type sname class age Basics Print 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 P. Sid Sname Class AO(1). > sophomore age AO(2). Continue Names of student younger than 20 or older than 25: Student Sid Sname Class age P. P. >25 <20 Duplicates not eliminated by default: Student UNQ Sid Sname Class P. age <25 Print unique student names older than 25. Join Queries Joins are accomplished by repeating variables Student Sid Sname Class _Id P.S Reserves Sid Bid _Id Age day `11/30/01’ sid is the common attribute that join the two tables. • Print students who borrowed a book on 11/30/01. Join 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 Reserves _S Sid Bid day _Id _B Books >25 `11/30/01’ Bid Bname type _B P. Aggregates QBE supports: AVG, COUNT, MIN, MAX, SUM Student Sid Sname Class age _Id G. G.P.AO _A P.AVG._A G. are the group by fields – All tuples have the same values. Unnamed columns – Print result of an expression. Conditions Box Used to express conditions involving 2 or more columns. Conditions can be expressed involving a group. Student Sid Sname Class age CONDITIONS P. _A 20 < _A AND _A <25 Print student names that are between the ages of 20 and 25. Inserting & Deleting Tuples Tuple insertion: Student Sid Sname Class I. 1369 Lisa age Senior 23 • Tuple deletion: Delete all reservations for students with age<23 Student Sid _Id Reserves Sid D. _Id Sname Class Bid age < 23 day II. 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… Where P - The where clause contains the selection predicate. Quel Query Structure Range of t is r Retrieve (t.A) Where P 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. Example Find the names of all customers having a loan at the bank. Range of t is borrower Retrieve (t.CustomerName) Example: Tuple Variables Certain queries need 2 variables over the same relation. Example: Find all customers who live in the same city as Smith. Range of s is customer Range of t is customer Retrieve ( s.CustName ) Where t.CustName = “Smith and s.CustCity = t.CustCity Aggregate Function Aggregate functions in Quel compute functions on groups of tuples. An aggregate expression appear anywhere a constant may appear. For Example In a where clause. Find the average balance for all San Jose accounts. Range of t is account Retrieve avg (t.balance Where t.Branch = “San Jose”) Modification of Database Deletion: The form of a Quel deletion is: range of t is r delete t where 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: Delete all of Lee’s account record: range of t is depositor delete t where t.CustName = “Lee” Insertion Insertions are expressed in Quel using the append to. Insert the account 123456 at the San Jose branch with a balance of $5000.00: append to account (branch = “San Jose” account = “123456” balance = “5000”) Updates Updates are expressed in Quel using the replace command Increase all account balances by 5 percent: range of t is account replace t (balance = 1.05 * t.balance) III. Datalog – Basic Structure Logic based language that allows recursive queries. A Datalog program consists of a set of rules that defines views. 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,B if (“San Jose”, A,B) E account A and B > 100 then (A,B) E vt Datalog 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… Rules are built out of literals and have the form: p(t1,t2,…,tn) :- L1,L2…Ln Each L is a literal Head – the literal p(t1,t2,…,tn) Body – the rest of the literals. Semantics of a Rule An 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>100 An instantiation Rule: v1 (123456, 300) :- account (“San Jose”, “123456”,300) 300 > 100 Semantics 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: Procedure Datalog- Fixpoint / = set of facts in the database repeat Old./ = / / = / U infer (K,/) until / = Old./ 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.