CS 222 Database Management System

Spring 2010-11

Lecture 3

4/29/2020 Korra Sathya Babu Department of Computer Science NIT Rourkela Lecture 3 1

Commercial Query Languages

• RA and RC are formal and abstract Languages developed for the Relational model in the database technology • Many languages were commercialized taking the foundations from the abstract query languages – QBE – QUEL – SQL 2 4/29/2020 Lecture 3

Query-by-Example (QBE)

Query-by-Example (QBE)

• Developed by Moshe M. Zoolf at IBM Research during mid 1970s (in parallel to the development of SQL) • A graphical query language which is based on the notion of DRC – QBE queries can be translated to DRC directly but queries containing aggregate operators, sorting etc cannot be converted • Two dimensional syntax – system creates templates of relations that are requested by users • Queries are expressed “by example”

Query-by-Example (QBE)

• A “GUI” for expressing queries.

– Actually invented before GUIs.

– Very convenient for simple queries.

– Awkward for complex queries.

• QBE an IBM trademark.

– But has influenced many projects – Especially PC Databases: Paradox, Access, etc.

Syntax

• QBE Queries are posed by placing constants and variables (example elements) into individual columns and thereby creating an example tuple of the query result • Variables used are domain variables • Variable symbols are prefixed by underscore • Commands are followed by a dot • The field that should appear in the answer are specified by the command P. (stands for Print ( also called retrieval command )) • Equal operator is by default . Other comparison operators can also be used • Link establishment between relations is done by example elements

Syntax

• Aggregate functions and Groupings used by adding prefixes are – CNT.ALL.

– SUM.ALL.

– CNT.UNQ.ALL.

– MAX.ALL.

– MIN.ALL

– AVG.ALL

– MOD.ALL

– G. (Groupings) • Sorting commands used are – .DO (Descending Order) – .AO ( Ascending Order) • General Commands used are – P. (Print), I. (Insert), U. (Update), D. (Delete)

Sno

22 29 31 32 58 64 71 74 85 95

Sname

Dustine Brutus Lubber Andy Rusty Horatio Zorba Horatio Art Bob

Rating

7 1 8 8 10 7 10 9 3 3

Instance of Sailors Age

45.0

33.0

55.5

25.5

35.0

35.0

16.0

35.0

25.5

63.5

Example

Sid

22 22 22 22 31 31 31 64 64 74

bid

101 102 103 104 102 103 104 101 102 103

Instance of Reserves day

10/10/08 10/10/08 10/08/08 10/07/08 10/10/08 11/06/08 11/12/08 09/05/08 09/08/08 09/08/08

bid

101 102 103 104

bname

Interlake Interlake Clipper Marine

Instance of Boats color

Blue Red Green red 4/29/2020 Lecture 3 8

`Example Tables’ in QBE

attributes Relation name • QBE has its own screen editor • Users specify a query by filling in example tables , or skeletons examples.

; we will use these skeletons in our

Reserves Boats

sid bid day bid bname color

Sailors

sid sname rating age

`Creating Tables’ in QBE

Dictionary entry for Relation Reserves I.P.I.

Reserves

sid bid day Represent the dictionary entry of file columns of table Reserves

Basics

• To print names and ages of all sailors:

Sailors

sid sname rating age  P._N

P._A

Print all fields for sailors with ascending order by ( rating rating, age ): > 8, in

Sailors

P.

sid sname rating AO(1). >8 age AO(2).

  QBE puts unique new variables in blank columns. Above query in DRC (no ordering): | 

Sailors



T

 8 

And/Or Queries

• Names of sailors younger than 30 than 20:

Sailors

sid

or

sname rating age P.

< 30 older  P.

Names of sailors younger than 30 20:

Sailors

sid and sname rating age > 20 older than _Id P.

_Id P.

< 30 > 20  Names of sailors younger than 30 and rating > 4:

Sailors

sid sname rating age _Id P.

> 4 < 30

Duplicates

•

Single row with

UNQ.

P: Duplicates not eliminated by default; can force elimination by using

Sailors

UNQ.

sid sname rating age P.

< 30  Multiple rows with P: Duplicates eliminated by default! Can avoid elimination by using ALL.

Sailors

ALL.

sid sname rating age _Id P.

< 30 _Id P.

> 20

Join Queries

• Names of sailors who’ve reserved a boat for 11/06/2008 and are older than 25 (note that dates and strings with blanks/special chars are quoted):

Sailors

sid _Id sname P._S

rating age > 25

Reserves

sid _Id bid day ‘8/24/96’  Joins accomplished by repeating variables.

Join Queries (Contd.)

• Colors of boats reserved by sailors who’ve reserved a boat for 8/24/96 and are older than 25 :

Sailors

sid sname rating age _Id _S > 25

Reserves Boats

sid _Id bid _B day ‘8/24/96’ bid bname _B ‘Interlake’ color P.

Join Queries (Contd.)

• Names and ages of sailors who’ve reserved some boat that is also reserved by the sailor with

sid

= 22:

Sailors

sid sname rating age _Id P.

P.

Reserves

sid 22 _Id bid _B _B day

Unnamed Columns

• Useful if we want to print the result of an expression, or print fields from 2 or more relations.

– QBE allows P. to appear in at most one table!

Sailors

sid sname rating age _Id P.

_R _A P._D

P.(_R/_A)

Reserves

sid _Id bid day _D

“Negative Tables”

• Can place a negation marker in the relation column:

Sailors

sid sname rating age _Id P._S

sid _Id bid _B day  Variables appearing in a negated table must also appear in a positive table!

Aggregates

• QBE supports

AVG, COUNT, MIN, MAX, SUM

– None of these eliminate duplicates, except COUNT – Also have AVG.UNQ. etc. to force duplicate elimination

Sailors

sid sname rating _Id G.

age G.P.AO _A P.AVG._A

 The columns with G. are the fields.

group-by fields; all tuples in a group have the same values in these — The (optional) use of .AO orders the answers.

—

Every column with P. must include G. or an aggregate operator to get the aggregates.

Conditions Box

• Used to express conditions involving 2 or more columns, e.g., _R/_A > 0.2. • Can express a condition that involves a group, similar to the HAVING clause in SQL:

Sailors

sid sname rating age G.P. _A CONDITIONS AVG._A > 30  Express conditions involving AND and OR:

Sailors

sid sname rating age P.

_A CONDITIONS 20 < _A AND _A < 30

Find sailors who’ve reserved all boats

• A division query; need aggregates (or update operations) to do this in QBE .

Sailors

sid P.G._Id

sname rating age

Reserves

sid bid _Id _B1 day CONDITIONS COUNT._B1= COUNT._B2

Boats

bid bname _B2 color

Inserting Tuples

• Tuple insertion:

Sailors

I.

sid sname rating age 74 Janice 7 14

Delete and Update

• Delete all reservations for sailors with rating < 4

Sailors

sid _Id sname rating age < 4

Reserves

D.

sid _Id bid day  Increment the age of the sailor with sid = 74

Sailors

sid sname rating age 74 U._A+1

Restrictions on Update Commands

• Cannot mix I., D. and U. in a single example table, or combine them with P. or G.

• Cannot insert, update or modify tuples using values from fields of other tuples in the same table. Example of an update that violates this rule:

Sailors

sid sname rating age john joe _A U._A+1 Should we update every Which Joe’s age?

John’s age should we use?

Find sailors who’ve reserved all boats (Again!)

• We want to find sailors _Id such that there is no boat _B that is not reserved by _Id:

Sailors

sid sname rating age _Id P._S

bid bname color _B sid _Id bid _B day  Illegal query! Variable _B does not appear in a positive row. In what order should the two negative rows be considered? (Meaning changes!)

A Solution Using Views

• Find sailors who’ve not reserved some boat _B:

Sailors

sid sname rating age _Id P._S

BadSids

I.

sid _Id

Boats

bid bname color _B sid _Id  Next, find sailors not in this `bad’ set:

Sailors

sid sname rating age _Id P._S

bid _B day sid _Id

Advantages of QBE

• User-friendly • Order of the row is immaterial • Highly non procedural query language • Several ways to represent a query • QBE is interpreter based so debugging is easy

Disadvantages of QBE

• QBE doesn’t support complete views • QBE doesn’t have a preprocessor for optimization • Implementation of QBE is not complete but theoretically its complete

Summary

• QBE is an elegant, user-friendly query language based on DRC.

• Simple queries are especially easy to write in QBE, and there is a minimum of syntax to learn.

• Has influenced the graphical query facilities offered in many products, including Borland’s Paradox and Microsoft’s Access.