CS 222 Database Management System Spring 2010-11 Lecture 3 b

1. CS 222 Database Management SystemSpring 2010-11 Lecture 3 b Korra Sathya Babu Department of Computer Science NIT Rourkela Lecture 3

2. Query Language (QUEL) • QBE is graphical based • QUEL is a relational database access language similar to SQL • It was created as a part of the Ingres effort at University of California, Berkeley, based on Codd's earlier suggested but not implemented Data Sub-Language ALPHA • QUEL was used for a short time in most products based on the freely-available Ingres source code, most notably Informix • QUEL continues to be available as a part of the Ingres DBMS • QUEL is purely based on TRC • It can be embedded with ‘C’ only Lecture 3

3. Example Instance of Boats Instance of Reserves Instance of Sailors Lecture 3

4. Query Language (QUEL)

5. Query Language (QUEL) Has three clauses range of retrieve where All queries are represented with these clauses

6. Syntax range of of t is R retrieve t where Predicate Similar to ∃(t(R)) Similar to Select Clause in SQL Similar to where in SQL Relational Operators used in the Predicate are <,>,≤,≥,≠,=, ∧,∨,

7. Equivalence The above TRC query can be written in QUEL as range of t1 is R1 . . range of tk is Rk retrieve (ti1.A1,…,tir.Ar) where Ψ (condition)

8. Statements in QUEL Retrieve into : Used to create a newfile and insert tuples range of t1 is R1 . . range of of tk is Rk retrieve into S (A1=W1,…,An=Wn) where Ψ (t1 ,…,tn) • The above query creates a newfile and insert tuples into the file • Retrieve is similar to SELECT operation in Relation Algebra

9. Statements in QUEL Delete: Used to calculate the set difference range of tis R delete t where Ψ (t) Delete from R all tuples where Ψ (t)

10. Statements in QUEL append to: to append tuples to existing relation range of t1 is R1 . . range of of tk is Rk append to S (A1=W1,…,An=Wn) where Ψ (t1 ,…,tn) • append to is similar to Set Union operation of Relational Algebra

11. Completeness of QUEL • Lets evaluate with the Five basic operations Suppose R(A1,...,An) and S(B1,...,Bm) are two relations and T is a new relation name Union (T=R U S) (assuming m=n) range of ris R append to T (C1=r.A1,…, Cn=r.An) range of sis S append to T (C1=s.A1,…, Cn=s.Bn)

12. Completeness of QUEL Suppose R(A1,...,An) and S(B1,...,Bm) are two relations and T is a new relation name Set Difference(T=R - S) (assuming m=n) range of ris R append to T (C1=r.A1,…, Cn=r.An) range of sis S range of tis T delete t where s.B1=t. C1 and … and s.Bn=t.Cn)

13. Completeness of QUEL Suppose R(A1,...,An) and S(B1,...,Bm) are two relations and T is a new relation name Cartesian Product (T=R X S) range of ris R range of sis S append to T (C1=r.A1,…, Cn=r.An, Cn+1=s.B1 ,… , Cn+m=s.Bm)

14. Completeness of QUEL Suppose R(A1,...,An) and S(B1,...,Bm) are two relations and T is a new relation name Selection (σF(R)) range of ris R append to T (C1=r.A1,…, Cn=r.An) where F

15. Completeness of QUEL Suppose R(A1,...,An) and S(B1,...,Bm) are two relations and T is a new relation name Projection (Пi1,... ik(R) ) range of ris R append to T (C1=r.A i1,…, Ck=r.A ik)

16. Example Find the name and rating of Sailors whose rating is greater than 7 range of t is Sailors retrieve into SN (S=s.Name,R=s.Rating) where rating > 7

17. Summary • QUEL is purely based on TRC. • It is quite expressive (relationally complete). • Majority of the DBMS incorporated earlier with QUEL are replaced by SQL. • Still lives in the open source community.