Query by Example
Encyclopedia
Query by Example is a database
query language
for relational database
s. It was devised by Moshé M. Zloof at IBM Research
during the mid 1970s, in parallel to the development of SQL
. It is the first graphical query language, using visual tables where the user would enter commands, example elements and conditions. Many graphical front-ends for databases use the ideas from QBE today. Originally limited only for the purpose of retrieving data, QBE was later extended to allow other operations, such as inserts, deletes and updates, as well as creation of temporary tables.
The motivation behind QBE is that a parser can convert the user's actions into statements expressed in a database manipulation language, such as SQL
. Behind the scenes, it is this statement that is actually executed. A suitably comprehensive front-end can minimize the burden on the user to remember the finer details of SQL, and it is easier and more productive for end-users (and even programmers) to select tables and columns by selecting them rather than typing in their names,
In the context of information retrieval
, QBE has a somewhat different meaning. The user can submit a document, or several documents, and ask for "similar" documents to be retrieved from a document database. Similarlity search is based comparing document vectors (see Vector Space Model
).
QBE is a seminal work in End-user development, frequently cited in research papers as an early example of this topic.
Currently, QBE is supported in several relational database front ends, notably Microsoft Access, which implements "Visual Query by Example", as well as Microsoft SQL Server Enterprise Manager. It is also implement in several object-oriented databases
(e.g. in db4o
).
is given here to illustrate how QBE works.
). The software can automatically generate the queries for the user (usually behind the scenes). Here are some examples:
Example Form B:
.....Name: Bob
..Address:
.....City:
....State: TX
..Zipcode:
Resulting SQL
:
SELECT * FROM Contacts WHERE Name='Bob' AND State='TX'
Note how blank items do not generate SQL
terms. Since "Address" is blank, there is no clause generated for it.
Example Form C:
.....Name:
..Address:
.....City: Sampleton
....State:
..Zipcode: 12345
Resulting SQL
:
SELECT * FROM Contacts WHERE City='Sampleton' AND Zipcode=12345
More advanced versions of QBE have other comparison operator options, often via a pull-down menu, such as "Contains", "Starts With", "Greater-Than", and so forth.
Database
A database is an organized collection of data for one or more purposes, usually in digital form. The data are typically organized to model relevant aspects of reality , in a way that supports processes requiring this information...
query language
Query language
Query languages are computer languages used to make queries into databases and information systems.Broadly, query languages can be classified according to whether they are database query languages or information retrieval query languages...
for relational database
Relational database
A relational database is a database that conforms to relational model theory. The software used in a relational database is called a relational database management system . Colloquial use of the term "relational database" may refer to the RDBMS software, or the relational database itself...
s. It was devised by Moshé M. Zloof at IBM Research
IBM Research
IBM Research, a division of IBM, is a research and advanced development organization and currently consists of eight locations throughout the world and hundreds of projects....
during the mid 1970s, in parallel to the development of SQL
SQL
SQL is a programming language designed for managing data in relational database management systems ....
. It is the first graphical query language, using visual tables where the user would enter commands, example elements and conditions. Many graphical front-ends for databases use the ideas from QBE today. Originally limited only for the purpose of retrieving data, QBE was later extended to allow other operations, such as inserts, deletes and updates, as well as creation of temporary tables.
The motivation behind QBE is that a parser can convert the user's actions into statements expressed in a database manipulation language, such as SQL
SQL
SQL is a programming language designed for managing data in relational database management systems ....
. Behind the scenes, it is this statement that is actually executed. A suitably comprehensive front-end can minimize the burden on the user to remember the finer details of SQL, and it is easier and more productive for end-users (and even programmers) to select tables and columns by selecting them rather than typing in their names,
In the context of information retrieval
Information retrieval
Information retrieval is the area of study concerned with searching for documents, for information within documents, and for metadata about documents, as well as that of searching structured storage, relational databases, and the World Wide Web...
, QBE has a somewhat different meaning. The user can submit a document, or several documents, and ask for "similar" documents to be retrieved from a document database. Similarlity search is based comparing document vectors (see Vector Space Model
Vector space model
Vector space model is an algebraic model for representing text documents as vectors of identifiers, such as, for example, index terms. It is used in information filtering, information retrieval, indexing and relevancy rankings...
).
QBE is a seminal work in End-user development, frequently cited in research papers as an early example of this topic.
Currently, QBE is supported in several relational database front ends, notably Microsoft Access, which implements "Visual Query by Example", as well as Microsoft SQL Server Enterprise Manager. It is also implement in several object-oriented databases
Object database
An object database is a database management system in which information is represented in the form of objects as used in object-oriented programming...
(e.g. in db4o
Db4o
db4o is an embeddable open source object database for Java and .NET developers. It is developed, commercially licensed and supported by Versant....
).
Example
A simple example using the Suppliers and Parts databaseSuppliers and Parts database
The Suppliers and Parts database is an example of a relational database very much referred to in literature. It is a very simple and straightforward database containing only three tables: Supplier, Part and Orders....
is given here to illustrate how QBE works.
As a general technique
The term also refers to a general technique influenced by Zloof's work whereby only items with search values are used to "filter" the results. It provides a way for a software user to perform queries without having to know a query language (such as SQLSQL
SQL is a programming language designed for managing data in relational database management systems ....
). The software can automatically generate the queries for the user (usually behind the scenes). Here are some examples:
Example Form B:
.....Name: Bob
..Address:
.....City:
....State: TX
..Zipcode:
Resulting SQL
SQL
SQL is a programming language designed for managing data in relational database management systems ....
:
SELECT * FROM Contacts WHERE Name='Bob' AND State='TX'
Note how blank items do not generate SQL
SQL
SQL is a programming language designed for managing data in relational database management systems ....
terms. Since "Address" is blank, there is no clause generated for it.
Example Form C:
.....Name:
..Address:
.....City: Sampleton
....State:
..Zipcode: 12345
Resulting SQL
SQL
SQL is a programming language designed for managing data in relational database management systems ....
:
SELECT * FROM Contacts WHERE City='Sampleton' AND Zipcode=12345
More advanced versions of QBE have other comparison operator options, often via a pull-down menu, such as "Contains", "Starts With", "Greater-Than", and so forth.
Sources
- Oracle Definitions: http://searchoracle.techtarget.com/sDefinition/0,,sid41_gci214554,00.html
- http://www.cs.sfu.ca/CC/354/zaiane/material/notes/Chapter5/node2.html
- http://www.cs.wisc.edu/~dbbook/openAccess/thirdEdition/qbe.pdf