Date: Tue, 14 Jan 1997 20:08:02 GMT
Server: NCSA/1.5
Content-type: text/html
Last-modified: Wed, 15 Feb 1995 00:41:02 GMT
Content-length: 11668
Paradox QBE Tutorial
1. Introduction
Query By Example (QBE) is a query language, which is used by Borland's
Paradox database management system, and explained in Section 4.2 in
Korth and Silberschatz's "Database System Concepts" (the course textbook).
The instructions included in this tutorial are based on Paradox 4.5 for
MS-DOS and Paradox 1.0 for Windows. There are some differences between
the MS-DOS and the Windows versions, but they will be clearly pointed out.
2. Start Up
Paradox runs on the IBM PC-Compatibles in the open lab (2205 TMCB). There
are two versions of Paradox available in the labs.
- MS-DOS:
- Paradox can be found under the C:\PARADOX directory. After entering the
Paradox directory, you can run Paradox by typing paradox at the prompt. (Hint:
the DOS version displays important function keys and actions they perform at
the bottom row of the screen. These function keys can be of great help when
you're not sure what to do next.) Working with the MS-DOS version is faster
and is probably best for simple tasks.
- Windows:
- Paradox can be found under the C:\PDOXWIN directory. There is a
Paradox group window with icons to click on. Click on the icon entitled Pad
The user interface, menu design, and online help are available. Also, QBE
queries can be stored on disk for later retrieval. However, it runs at typical
"windoze" speed, slower than the DOS version.
3. Creating Tables
In this section, we will show how to create relations (tables) and add tuples
to relations in Paradox.
The following SQL command creates the table (relation scheme) students:
CREATE TABLE students (
name Char (10) PRIMARY KEY,
SID NUMBER PRIMARY KEY,
address CHAR (20));
A similar concept is followed in Paradox's method for creating tables:
- Identify the attributes and their data types, and the key attributes
(if any).
- Give the table relation scheme a name.
3.a Creating Relation Schemes in Paradox
Windows
- 1. Select File/New/Table from the menu bar, i.e., File
followed by New and then by Table.
- 2. The table type is Paradox for Windows (the default)
- 3. Enter the following:
- The field name,
- The type (A = Alphanumeric,
S = Short Number (integer),
and D = Date with the format mm/dd/yy),
- The size (for alphanumeric types only).
- Specify whether or not it is a key attribute.
All key attributes must be at
the top of the field list. In the student example, if SID and name are the
combined primary key, they should be listed as the first two fields. To specify
that the field is a key or part of a key, press any key on the keyboard while
in this column, or click the mouse button on this column while the cursor is
positioned in the column.
- 4. Click on the Save As...
button and enter the name of the file. All tables
are automatically given the ".db" file extension if an extension is not
specified. It is recommended to use the default extension.
MS-DOS
- 1. Select from the menu bar Create
- 2. Enter the name of the table to be created.
- 3. Similar to step 3 in the Windows version, enter the field name
and the field type. Data types are the same in DOS as in Windows.
However, the data type, size, and key attribute area all entered in the "Field
Type" column. Hence, if an attribute is (part of) a primary key of an
alphanumeric type with a size of 10 characters, the "Field Type" is
"A10*".
- 4. Press F2 (DoIt!) to save the table.
- Note: Paradox requires the key fields to come first.
3.b Adding Tuples to a Table
Windows
- 1. Select File/Open/Table from the menu bar.
- 2. Pick the table you want to insert data into.
- 3. Select Table/Edit/Data from the menu bar or press
F9.
- 4. Enter the data in the appropriate columns.
- 5. When finished entering data, select Table/End/Edit or press
F9. The table with the new values is automatically saved
when an edit session is ended.
MS-DOS
- If the table to be edited is not present on the desktop:
- 1. Select Modify/Edit form the menu bar.
- 2. Enter the name of the table to edit.
- 3. Insert the data in the appropriate columns.
- 4. Press F2 (DoIt!) to save the table.
- If the table is already visible on the desktop, then there is no need to re-
open the table. Just click on the table and press F9 to allow editing of
the table and hit F2 to save modifications made to the table.
4. Building Queries
Building queries in QBE on Paradox is similar to the ones given in the textbook
with some exceptions which will be discussed in this section.
4.a Bringing Up Skeleton Tables
Before a QBE query can be built, the skeleton tables for each relation involved
in the query must be selected:
Windows
- 1. Select File/New/Query from the menu bar
- 2. A window will appear with the names of available tables. Select the
table involved in the query.
- 3. If there are other tables involved in the query, select
Query/Add Table...
from the menu bar to select more tables involved in the query.
- 4. Repeat step #3 for each table you wish to query on.
MS-DOS
- 1. Select Ask from the menu bar and enter the names of the
tables involved in the query.
- 2. Repeat this for all tables involved in the query.
4.b Performing Projections
Windows and MS-DOS
- 1. Select the table containing the attributes on which you want to project.
If the table is already on the desktop, click anywhere on the table;
otherwise, open it.
- 2. Using the arrow keys on the keyboard or the mouse, select an attribute
field on which you want to project.
- 3. Hit the F6 key or click on the little white box in the field
and a check will appear in the field.
To remove the check, hit the F6 key in that field again.
- 4. Repeat steps 1-3 for any other fields on which you want to project.
4.c Performing Selections
Windows and MS-DOS
- 1. Select the table containing the attributes on which you want to select.
If the table is already on the desktop, click anywhere on the table;
otherwise, open it.
- 2. Select the attribute fields using the arrow keys or the mouse.
- 3. Enter the comparison operator followed by the comparing value (either
a constant or another variable).
- Valid relational comparison operators include >, <, >=, <=, NOT,
and the optional =.
4.d Performing Joins
Windows and MS-DOS
- 1. Select a table to be joined.
If the table is already on the desktop, click anywhere on the table;
otherwise, open it.
- 2. Select an attribute field to be joined.
- 3. Enter a variable into the field. Variables must be preceded by an
underscore, "_". In Windows, the variable will appear in red and
the underscore is not shown.
- 4. Repeat steps 1 - 3 for the other tables whose attributes you want to
join.
4.e Performing Set Queries
- QBE uses the SET command with NO or EVERY in the attribute field to
perform such queries as Find the employee who makes more money than
all others, or Find the customer who has ordered none of the
books. For this type of query we will need to first create a set of tuples for
comparison and compare the set to another table; we then perform
other selections and projections after grouping as explained in
the following example.
- Suppose we wish to find all employees who work for all of departments.
- First we
need to create a set of all the departments by Dno in the Department location
table. To do this, we follow the steps below:
- Select the Dep_loc table.
- Type SET into the field under the table name.
- Select the field Dno to be grouped as a set of tuples.
- Enter the variable _dno into the field.
Now we need to compare this set of Dno's with the departments in the
employee table to find all of the employees who work in all (or none of the)
departments. To do this, we continue as follows:
- Select the Employee table.
- Select the Field Dno in the Employee table to compare with the set.
- Enter EVERY into the field followed by the variable _dno.
We now have the following query specification (where P. represents the
check mark). The first table establishes the set of Dno's, and the second
table prints the names of employees associated with EVERY Dno in the set.
=== Dep_loc ===== Dep_Name ===== Dno ===
SET | | _dno |
========================================
=== Employee ===== Name ===== Emp_No ===== Dno ======
| P. | | EVERY _dno |
=====================================================
-
The set comparison operators are ONLY, NO, EVERY, and EXACTLY.
- ONLY: The values in the group contain only members
of the defined set. For example: Which customers have
ordered only one book in the Travel Series?
NO: No values in the group are in the defined set. For example:
Which patients on Ward 12 haven't exhibited any symptom
seen in the patients on Ward 6?
EVERY: The values in the group include all members of the defined set.
For example: Which Students have taken all the courses required for a
major in chemistry?
EXACTLY: The values in the group exactly match the
members of the defined set (combines ONLY and EVERY). For example:
Which customers have ordered all the books in the Travel
Series and no others?
4.f Performing Multiple Sub-queries on the Same Table.
- Each Table skeleton has multiple horizontal lines where you can perform
multiple sub-queries on that table. After entering the first query in a table,
use
the arrow to move to the next line to perform a second sub-query. This can be
repeated for more queries on that same table. Suppose, for example, that we
want to query all Employees who are not supervised by a compute scientist.
This would involve two queries on the Employee relation: One to select all
computer scientists that are supervisors and another to compare each
employee with that set of computer scientists that are supervisors. The
following query demonstrates how the Employee table can be used twice in a
query.
=== Emp === Emp_No === Sup_No === Degree ==============
| _eno | | NOT Computer Science |
| P. | _eno | |
=======================================================
4.g Executing and Saving Queries
Windows
- 1. If the query is not on the desktop,
i.e., needs to be read in from a file, click on File/Open/Query
in the menu bar and select the QBE file to be opened.
- 2. To Execute the query on the desktop, select Query/Run
from the menu bar.
MS-DOS
- 1. Queries cannot be loaded and saved and, hence, queries must be built on
the desktop.
- 2. Press F2 (DoIt!) to execute the chosen query.