A TUTORIAL ON INGRES
--------------------
by
Jacques Levin, Ph.D
and
Mientje Levin, Ph.D
COMPUTER BASED LEARNING / COMPUTER CENTER
-----------------------------------------
N O V A U N I V E R S I T Y
------------------------------
This tutorial on Ingres, the relational data base management system
currently running under UNIX, is designed as a set of 69 lessons, numbered
from 1 to 69. It is based on Robert Epstein's "A Tutorial on Ingres".
The tutorial on Ingres uses the "demo" database implemented in Ingres,
and provides a complete illustration of the query language used by Ingres
(called QUEL).
To access the demo database, just type, from the UNIX prompt:
ingres demo
It is followed by a tutorial on how to create your own data base using
Ingres. This tutorial is based on Robert Epstein's "Creating and maintaining
a data base using Ingres". It consists of 37 lessons numbered from 100 to
137.
LESSON# 1
----------
A TUTORIAL ON INGRES
This tutorial describes how to use the INGRES data base management system.
You should be able to follow the examples given here and observe the same
results.
The data manipulation language supported by the INGRES system is called
QUEL (QUEry Language). Complete information on QUEL and INGRES appears in
the INGRES reference manual. This tutorial does not attempt to cover every
detail of INGRES.
Begin by logging onto UNIX, the time sharing system under which INGRES runs.
If at all possible, use a terminal that has both upper and lower case letters;
otherwise life is going to be miserable for you. If you are on an upper case
only terminal, type "\\" everywhere "\" appears in the tutorial.
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, a text on a TUTORIAL ON INGRES has appeared on the screen.
2. type the command indicated by the tutorial (% ingres demo).
The percent sign is not part of the command. It is only there
to tell you to enter a Unix command.
3. after typing the command (% ingres demo), which gets you into the ingres
demo data base, type "\q" to exit the database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 2
----------
--------------------
! % ingres demo !
--------------------
This requests "UNIX" to invoke INGRES using the data base called "demo".
After a few seconds, the following will appear :
INGRES version 6.1/0 login
Tue Aug 30 14: 52: 23 1977
COPYRIGHT
The Regents of the University of California
1977
This program material is the property of the
Regents of the University of California and
may not be reproduced or disclosed without
the prior written permission of the owner.
------
! go !
! * !
------
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type again the command :
% ingres demo
3. as indicated in the tutorial, the system should display :
go
*
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 3
----------
The first two lines include the INGRES version number (in this case version
6. 1) and the current date. Following that is the "dayfile", which includes
messages related to the INGRES system. The "go" indicates that INGRES is ready
for your interactions.
The INGRES monitor prints an asterisk ("*") at the beginning of each line to
remind you that INGRES is waiting for input.
Type the command :
----------------
! * print parts !
! * \g !
!Executing . . . !
----------------
The line "print parts" requests a printout of some data stored in the data
base. The "\g" means "go". The message "Executing . . ." indicates that INGRES
is processing your query. The following then appears :
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* print parts
* \g
3. as indicated in the tutorial, the system should display :
a parts relation
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 4
----------
parts relation
! pnum ! pname ! color ! weight ! qoh !
!-------!----------------------!-----------!-----------!--------!
! 1 ! central processor ! pink ! 10 ! 1 !
! 2 ! memory ! gray ! 20 ! 32 !
! 3 ! disk drive ! black ! 685 ! 2 !
! 4 ! tape drive ! black ! 450 ! 4 !
! 5 ! tapes ! gray ! 1 ! 250 !
! 6 ! line printer ! yellow ! 578 ! 3 !
! 7 ! l-p paper ! white ! 15 ! 95 !
! 8 ! terminals ! blue ! 19 ! 15 !
! 13 ! paper tape reader ! black ! 107 ! 0 !
! 14 ! paper tape punch ! black ! 147 ! 0 !
! 9 ! terminal paper ! white ! 2 ! 350 !
! 10 ! byte-soap ! clear ! 0 ! 143 !
! 11 ! card reader ! gray ! 327 ! 0 !
! 12 ! card punch ! gray ! 427 ! 0 !
!_______!______________________!___________!___________!________!
-----------
! continue !
! * !
-----------
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* print parts
* \g
3. as indicated in the tutorial, the system should display :
a parts relation
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 5
----------
What is printed on your terminal is the "parts relation". Intuitively, a rela-
tion is nothing more than a table with rows and columns.
In this case the relation name is "parts". There are five columns (we call them
domains) named pnum (part number), pname (part name), color, weight, qoh
(quantity on hand). Each row of the relation (called a tuple) represents one
entry, which in this case represents one part in a computer installation.
A relation can have up to 49 domains and a virtually unlimited number of
tuples.
Notice that after the query is executed, INGRES prints "continue", while when
we first entered INGRES it printed "go". As you enter a query INGRES saves
what you type in a "workspace". If you ever mistype a query, typing "\r" will
"reset" (ie. erase) your workspace. (Later on we will learn ways to edit
mistakes so we don't have to retype the entire query.)
At any time you can see what is in the workspace by typing "\p".
Try typing "\p" :
---------------
! * \p !
! print parts !
! * !
---------------
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* print parts
* \g
* \p
3. as indicated in the tutorial, the system should display :
print parts
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 6
----------
The current contents of the workspace is printed. Now try typing "\r" :
---------------
! * \r !
! go !
! * !
---------------
The workspace is now empty. Whenever INGRES types "continue" the workspace is
non-empty; whenever INGRES types "go" the workspace is empty.
After a query is executed, INGRES typically types "continue". If you then type
a new query, INGRES automatically erases the previous query, so you don't
have to type "\r" after every query. This will be further explained as we
proceed.
Using the "retrieve" command we can write specific queries about relations.
As an example, let's have INGRES print only the "pname" domain of the parts
relation. Type the command :
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* print parts
* \g
* \p
* \r
3. as indicated in the tutorial, the system should display :
go
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 7
----------
-------------------------
! * range of p is parts !
! * retrieve (p.pname) !
! * \g !
! Executing . . . !
-------------------------
! pname !
!------------------------!
! central processor !
! memory !
! disk drive !
! tape drive !
! tapes !
! line printer !
! l-p paper !
! terminals !
! paper tape reader !
! paper tape punch !
! terminal paper !
! byte-soap !
! card reader !
! card punch !
!________________________!
------------
! continue !
! * !
------------
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* range of p is parts
* retrieve (p.pname)
* \g
3. as indicated in the tutorial, the system should display :
a domain of the parts relation
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 8
----------
The output is just the pname domain from the parts relation. What we did
required two steps. First we declared what is called a "tuple variable" and
assigned it to range over the parts relation.
range of p is parts
What this means in English is that the letter "p" represents the parts rela-
tion. It may be thought of as a marker which moves down the "parts" relation
to keep our place. INGRES remembers the association so that once p is decla-
red to range over parts, we don't have to repeat the range declaration. This
is useful when we are working with more than one relation, as will be seen
later on.
Next we used the retrieve command. Its form is
retrieve (list here what you want retrieved)
"p" by itself refers to the parts relation. "p.pname" refers to the pname
domain of the parts relation, so saying :
retrieve (p.pname)
means retrieve the pname domain of the parts relation.
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* range of p is parts
* retrieve (p.pname)
* \g
3. as indicated in the tutorial, the system should display :
a domain of the parts relation
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 9
----------
Try the query to retrieve pname and color :
------------------------------
! * retrieve p.pname, p.color !
! * \g !
! Executing . . . !
------------------------------
2500 : syntax error on line 1
last symbol read was : .
----------------
! continue !
! * !
----------------
Unfortunately we've made an error. INGRES tells us that it found a syntax error
on the first line of the query. "Syntax error" means that we have typed some-
thing which INGRES cannot recognize. The error occurred on line 1. INGRES
makes a sometimes helpful and sometimes feeble attempt at diagnosing the pro-
blem. Whenever possible, INGRES tells us the last thing it read before it got
confused.
In this case, the error is that the list of things to be retrieved (called the
target list) must be enclosed in parenthesis. The correct query is :
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* retrieve p.pname, p.color
* \g
3. as indicated in the tutorial, the system should display :
an error message
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 10
----------
--------------------------------
! * retrieve (p.pname, p.color) !
! * \g !
! Executing . . . !
--------------------------------
! pname ! color !
!-----------------------!-----------!
! central processor ! pink !
! memory ! gray !
! disk drive ! black !
! tape drive ! black !
! tapes ! gray !
! line printer ! yellow !
! l-p paper ! white !
! terminals ! blue !
! paper tape reader ! black !
! paper tape punch ! black !
! terminal paper ! white !
! byte-soap ! clear !
! card reader ! gray !
! card punch ! gray !
!_______________________!___________!
------------
! continue !
! * !
------------
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* range of p is parts
* retrieve (p.pname, p.color)
* \g
3. as indicated in the tutorial, the system should display :
a domain of the parts relation
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 11
----------
You can restrict which tuples are printed by adding a "qualification" to the
query. For example to get the name and color of only those parts which are
gray, type :
--------------------------------
! * retrieve (p.pname, p.color) !
! * where p.color = "gray" !
! \g !
! Executing . . . !
--------------------------------
! pname ! color !
!-------------------!-----------!
! memory ! gray !
! tapes ! gray !
! card reader ! gray !
! card punch ! gray !
!___________________!___________!
--------------
! continue !
! * !
--------------
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* range of p is parts
* retrieve (p.pname, p.color)
* where p.color = "gray"
* \g
3. as indicated in the tutorial, the system should display :
a domain of the parts relation
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 12
----------
Notice that INGRES prints only those parts where p.color is gray. Notice also
that gray must be in quotes ("gray"). This is necessary. The only way INGRES
will recognize character strings (e.g. words) is to enclose them in quotes.
What if we only wanted part names for gray or pink parts? We only need to
append to the previous query the phrase :
or p.color = "pink"
Remember, however, that if the next line typed begins a new query, INGRES
will automatically reset the workspace. The workspace will be saved only if
the next line begins with a command such as "\p" or "\g". (There are others
which we will come to later.) If such a command is typed, the previous query
is saved and anything further will be apended to that query.
Thus, by typing :
-------------------------------
! * \p !
! retrieve (p.pname, p.color) !
! where p.color = "gray" !
! * !
-------------------------------
you can see the previous query.
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* range of p is parts
* retrieve (p.pname, p.color)
* where p.color = "gray"
* \p
3. as indicated in the tutorial, the system should display :
the content of the workspace
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 13
----------
Now type :
------------------------
! * or p.color = "pink" !
! * !
------------------------
INGRES appends that last line to the end of thee query. You can verify this
yourself by printing the workspace :
-------------------------------
! * \p !
! retrieve (p.pname, p.color) !
! where p.color = "gray" !
! or p.color = "pink" !
! * !
-------------------------------
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* range of p is parts
* retrieve (p.pname, p.color)
* where p.color = "gray"
* or p.color = "pink"
* \p
3. as indicated in the tutorial, the system should display :
the content of the workspace
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 14
----------
Now run the query :
--------------------
! * \g !
! Executing !
--------------------
! pname ! color !
!-----------------------!-----------!
! central processor ! pink !
! memory ! gray !
! tapes ! gray !
! card reader ! gray !
! card punch ! gray !
!_______________________!___________!
-------------
! continue !
! * !
-------------
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* range of p is parts
* retrieve (p.pname, p.color)
* where p.color = "gray"
* or p.color = "pink"
* \g
3. as indicated in the tutorial, the system should display :
a domain of the parts relation
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 15
----------
The rules about when the workspace is reset may be very confusing at first.
In general, INGRES will do exactly what you want without having to think
about it.
We have seen qualifications which used "or" and "=". In general one can use :
and
or
not
= (equal)
!= (not equal)
> (greater than)
>= (greater than or equal)
< (less than)
< (less than or equal)
Evaluation occurs in the order the qualification was typed (i.e. left to right)
Parenthesis can be used to group things in any arbitrary order.
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. there are no commands to type in this lesson. So just go to the next
lesson.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 16
----------
INGRES can do computations on the data stored in a relation. For example, the
parts relation has quantity on hand and weight for each item. We might like
to know the total weight for each group of parts (i.e. weight multiplied by
qoh).
To get the name, part number and total weight for each part type the query :
--------------------------------------------------
! * retrieve (p.pname, p.pnum, p.qoh * p.weight) !
! * \g !
! Executing . . . !
--------------------------------------------------
2500 : syntax error on line 1
last symbol read was : *
---------------
! continue !
! * !
---------------
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* range of p is parts
* retrieve (p.pname, p.pnum, p.qoh, * p.weight)
* \g
3. as indicated in the tutorial, the system should display :
an error message
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 17
----------
Another error. The problem is that when a computation is done, INGRES does
not know how to title the domain on the printout. For a simple domain, INGRES
uses the domain name as a title. For anything else, you must create a new
domain title by specifying :
tot = p.qoh * p. weight
More generally the form is :
title = expression
For example :
name = p.pname
computation = p.weight / 2000 * (p.qoh + 2)
Let's fix the error by retyping the query. As long as the first line after a
query does not begin with a "\p" or "\g" then INGRES will automatically reset
the workspace, erasing the previous query for us.
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. there are no commands to type in this lesson. So just go to the next
lesson.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 18
----------
----------------------------------------------------------
! * retrieve (p.pname, p.pnum, tot = p.qoh * p.weight !
! * \g !
! Executing !
----------------------------------------------------------
! pname ! pnum ! tot !
!-----------------------!----------!---------!
! central processor ! 1 ! 10 !
! memory ! 2 ! 640 !
! disk drive ! 3 ! 1370 !
! tape drive ! 4 ! 1800 !
! tapes ! 5 ! 250 !
! line printer ! 6 ! 1734 !
! l-p paper ! 7 ! 1425 !
! terminals ! 8 ! 285 !
! paper tape reader ! 13 ! 0 !
! paper tape punch ! 14 ! 0 !
! terminal paper ! 9 ! 700 !
! byte-soap ! 10 ! 0 !
! card reader ! 11 ! 0 !
! card punch ! 12 ! 0 !
!_______________________!__________!_________!
------------
! continue !
! * !
------------
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* range of p is parts
* retrieve (p.pname, p.pnum, tot = p.qoh * p.weight)
* \g
3. as indicated in the tutorial, the system should display :
a domain of the parts relation
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 19
----------
In addition to multiplication, INGRES supports :
+ addition
- substraction (and unary negation)
/ division
* multiplication
** exponentiation (e.g. 3**10)
abs absolute value (e.g. abs (p.qoh - 50))
mod modulo division
and many others. Please refer to the INGRES reference manual for a brief but
complete description of what is supported.
If all we wanted were part numbers 2 or 10, then we could add the qualifica-
tion :
where p.pnum = 2 or p.pnum = 10
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. there are no commands to type in this lesson. So just go to the next
lesson.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 20
----------
CAUTION : If we just started typing "where p.pnum . . . ." INGRES would under-
stand this as the beginning of a new query and would reset the workspace. To
avoid this you could type "\p" and force INGRES to print the workspace, or
you can type "\a" (append). The append command guarantees that whatever else
is typed will be appended to what is already in the workspace. This command
is only needed immediately after a query is executed. Any other time data will
be appended automatically. Try the following :
-------------------------------------
! * \a !
! * where p.pnum = 2 or p.pnum = 10 !
! * \g !
! Executing . . . !
-------------------------------------
! pname ! pnum ! tot !
!----------------!----------!---------!
! memory ! 2 ! 640 !
! byte-soap ! 10 ! 0 !
!________________!__________!_________!
---------------
! continue !
! * !
---------------
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* range of p is parts
* retrieve (p.pname, p.pnum, tot = p.qoh * p.weight)
* \g
* \a
* where p.pnum =2 or p.pnum = 10
* \g
3. as indicated in the tutorial, the system should display :
a domain of the parts relation
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 21
----------
To include all part numbers greater than 2 and less than or equal to 10 :
---------------------------------------------------------
! * retrieve (p.pname, p.pnum, tot = p.qoh * p.weight !
! * where p.pnum > 2 and p.pnum <10 !
! * \g !
! Executing . . . !
---------------------------------------------------------
! pname ! pnum ! tot !
!--------------------!----------!---------!
! disk drive ! 3 ! 1370 !
! tape drive ! 4 ! 1800 !
! tapes ! 5 ! 250 !
! line printer ! 6 ! 1734 !
! l-p paper ! 7 ! 1425 !
! terminals ! 8 ! 285 !
! terminal paper ! 9 ! 700 !
! byte-soap ! 10 ! 0 !
!____________________!__________!_________!
-------------
! continue !
! * !
-------------
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* range of p is parts
* retrieve (p.pname, p.pnum, tot = p.qoh * p.weight)
* where p.pnum > 2 and p.pnum <10
* \g
3. as indicated in the tutorial, the system should display :
a domain of the parts relation
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 22
----------
Now, suppose we want to change the previous query to give results for part
numbers between 5 and 10 instead of 2 and 10. You are probably annoyed at
having to retype the entire query in order to change one character. Consequent-
ly, INGRES lets you use the UNIX text editor to make corrections and / or
additions to your workspace. At any time you can type "\e" and the INGRES
monitor will write your workspace to a file and call the UNIX "ed" program.
For example :
---------
! * \e !
! >>ed !
! 83 !
---------
The ">>ed" message tells you that you are now using the editor. The number 83
is the number of characters in your workspace.
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* range of p is parts
* retrieve (p.pname, p.pnum, tot = p.qoh * p.weight)
* where p.pnum > 2 and p.pnum <10
* \g
* \e
now we are in the editor :
the next command saves your workspace into a file "zz" :
: 1,$ w ~/zz
the next command gets you out of the editor :
: q
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 23
----------
We can now edit the query by changing the 2 to 5. Included in the UNIX documen-
tation is a tutorial on using the text editor. Rather than duplicating that
tutorial, we will just use a few of the editor commands to illustrate how to
do editing :
---------------------------------------------------------------
! 1p !
! retrieve (p.pname, p.pnum, tot = p.qoh * p.weight !
! 2p !
! where p.pnum > 2 and p.pnum <10 !
! s / 2 / 5 / p !
! where p.pnum > 5 and p.pnum <10 !
! w !
! 83 !
! q !
! < monitor !
! * !
---------------------------------------------------------------
Very briefly, this is what happens. "1p" and "2p" printed lines 1 and 2.
"s / 2 / 5 / p" substitutes a 5 for a 2 on the current line (line 2), and then
prints that line. "w" writes the query back to the INGRES workspace.
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* \e
now we are in the editor :
the next command retrieves our workspace from file "zz" :
: $ r ~/zz
the next commands let us edit and change our workspace as explained
in the tutorial :
: 1p
: 2p
: 3p
: s/2/5/p
the next command saves the modified query in file "zz" :
: 1,$ w! ~/zz
: w
: q
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 24
----------
Inside the editor you can use any "ed" command except "e" (since e changes the
file name). When you quit the editor (q command), the INGRES monitor will print
"<monitor" to remind you that you are back in INGRES. Notice that you MUST
precede the "q" command with a "w" command to pass the corrected workspace
back to INGRES.
To verify that the query is correct and to run it, type :
--------------------------------------------------------------
! * \p\g !
! retrieve (p.pname, p.pnum, tot = p.qoh * p.weight) !
! where p.pnum > 5 and p.pnum <10 !
! Executing . . . !
--------------------------------------------------------------
! pname ! pnum ! tot !
!-------------------!----------!---------!
! line printer ! 6 ! 1734 !
! l-p paper ! 7 ! 1425 !
! terminals ! 8 ! 285 !
! terminal paper ! 9 ! 700 !
! byte-soap ! 10 ! 0 !
!___________________!__________!_________!
-------------
! continue !
! * !
-------------
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* \e
now we are in the editor :
the next command retrieves our workspace from file "zz" :
: $ r zz
: w
: q
the next command runs the modified query :
* \p\g
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 25
----------
Having exhausted the interesting queries concerning the parts relation, let's
now look at a new relation called "supply". Type :
--------------------
! * print supply !
! * \g !
! Executing !
--------------------
supply relation
! snum ! pnum ! jnum ! shipdate ! quan !
!----------!----------!----------!--------------!----------!
! 475 ! 1 ! 1001 ! 73 - 12 - 31 ! 1 !
! 475 ! 2 ! 1002 ! 74 - 05 - 31 ! 32 !
! 475 ! 3 ! 1001 ! 73 - 12 - 31 ! 2 !
! 475 ! 4 ! 1002 ! 74 - 05 - 31 ! 1 !
! 122 ! 7 ! 1003 ! 75 - 02 - 01 ! 144 !
! 122 ! 7 ! 1004 ! 75 - 02 - 01 ! 48 !
! 122 ! 9 ! 1004 ! 75 - 02 - 01 ! 144 !
! 440 ! 6 ! 1001 ! 74 - 10 - 10 ! 2 !
! 241 ! 4 ! 1001 ! 73 - 12 - 31 ! 1 !
! 62 ! 3 ! 1002 ! 74 - 06 - 18 ! 3 !
! 475 ! 2 ! 1001 ! 73 - 12 - 31 ! 32 !
! 475 ! 1 ! 1002 ! 74 - 07 - 01 ! 1 !
! 5 ! 4 ! 1003 ! 74 - 11 - 15 ! 3 !
! 5 ! 4 ! 1004 ! 75 - 01 - 22 ! 6 !
! 20 ! 5 ! 1001 ! 75 - 01 - 10 ! 20 !
! 20 ! 5 ! 1002 ! 75 - 01 - 10 ! 75 !
! 241 ! 1 ! 1005 ! 75 - 06 - 01 ! 1 !
! 241 ! 2 ! 1005 ! 75 - 06 - 01 ! 32 !
! 241 ! 3 ! 1005 ! 75 - 06 - 01 ! 1 !
! 67 ! 4 ! 1005 ! 75 - 07 - 01 ! 1 !
! 999 ! 10 ! 1006 ! 76 - 01 - 01 ! 144 !
! 241 ! 8 ! 1005 ! 75 - 07 - 01 ! 1 !
! 241 ! 9 ! 1005 ! 75 - 07 - 01 ! 144 !
!__________!__________!__________!______________!__________!
-------------
! continue !
! * !
-------------
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* print supply
* \g
3. as indicated in the tutorial, the system should display :
the supply relation
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 26
----------
The supply relation contains snum (the supplier number), pnum (the part number
which is supplied by that supplier), jnum (the job number), shipdate (the date
it was shipped), and quan (the quantity shipped).
To find out what parts are supplied by supplier number 122 type :
------------------------------------------------------
! * retrieve (s.pnum) where s.snum = 122 !
! * \g !
! Executing . . . !
------------------------------------------------------
2109 : line 1, Variable 's' not declared in RANGE statement
--------------
! continue !
! * !
--------------
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* retrieve (s.pnum) where s.snum = 122
* \g
3. as indicated in the tutorial, the system should display :
an error message
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 27
----------
We have referenced the tuple variable "s" (i.e. s.pnum) without telling INGRES
what "s" represents. We are missing a range declaration. Retype the query as
follows :
-------------------------------------------------------
! * range of s is supply !
! * retrieve (s.pnum) where s.snum = 122 !
! * \g !
! Executing !
-------------------------------------------------------
! pnum !
!---------!
! 7 !
! 7 !
! 9 !
!_________!
-------------
! continue !
! * !
-------------
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* range of s is supply
* retrieve (s.pnum) where s.snum = 122
* \g
3. as indicated in the tutorial, the system should display :
a domain of the supply relation
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 28
----------
Supplier number 122 supplies part numbers 7, 7 and 9. Note that 7 is listed
twice. When retrieving tuples onto a terminal it is more efficient for INGRES
NOT to check for duplicate tuples. INGRES can be forced to remove duplicate
tuples. We will come to that later.
We now know that supplier 122 supplies part numbers 7 and 9. If you haven't
run this query a few hundred times you probably don't know what part names
correspond to part numbers 7 and 9. We could find out simply by running the
query :
------------------------------------------------------
! * retrieve (p.pname) where p.pnum = 7 or !
! * p.pnum = 9 !
! * \g !
! Executing . . . !
------------------------------------------------------
! pname !
!------------------------!
! l-p paper !
! terminal paper !
!________________________!
-------------
! continue !
! * !
-------------
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* range of p is parts
* retrieve (p.pname) where p.pnum = 7 or
* p.pnum = 9
* \g
3. as indicated in the tutorial, the system should display :
a domain of the supply relation
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 29
----------
After two queries we know by part name what part names are supplied by supplier
number 122. We could do the same thing in one query by asking :
---------------------------------------------------------
! * retrieve (p.pname) where p.pnum = s.pnum !
! * and s.snum = 122 !
! * \g !
! Executing !
---------------------------------------------------------
! pname !
!-----------------------------------!
! l-p paper !
! l-p paper !
! terminal paper !
!___________________________________!
-------------
! continue !
! * !
-------------
Again note that "l-p paper" is duplicated. Look closely at this query. Note
that the domain pnum exists in both the parts and supply relations. By saying
p.pnum = s.pnum, we are logically joining the two relations.
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* range of p is parts
* range of s is supply
* retrieve (p.pname) where p.pnum = s.pnum
* and s.snum = 122
* \g
3. as indicated in the tutorial, the system should display :
a domain of the parts relation
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 30
----------
Suppose we wished to find all suppliers who supply the central processor. We
know that we will want to retrieve s.snum. We want omly those s.snum's where
the corresponding s.pnum is equal to the part number for the central processor.
If we find the p.pname which is equal to "central processor" then that will
tell us the correct p.pnum. Finally we want s.pnum = p.pnum. The query is :
--------------------------------------------------------
! * retrieve (s.snum) where !
! * s.pnum = p.pnum and p.pname = "central processor" !
! * \g !
! Executing . . . !
--------------------------------------------------------
! snum !
!----------!
! 475 !
! 475 !
! 241 !
!__________!
-------------
! continue !
! * !
-------------
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* range of p is parts
* range of s is supply
* retrieve (s.snum) where
* s.pnum = p.pnum and p.pname = "central processor"
* \g
3. as indicated in the tutorial, the system should display :
a domain of the supply relation
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 31
----------
Let's abandon the parts and supply relations and try another. First, we can
see what other relations are in the database by typing :
----------------
! * help \g !
! * Executing !
----------------
relation name relation owner
relation ingres
attribute ingres
indexes ingres
integrity ingres
constraint ingres
item ingres
sale ingres
employee ingres
dept ingres
supplier ingres
store ingres
parts ingres
supply ingres
-------------
! continue !
! * !
-------------
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* help \g
3. as indicated in the tutorial, the system should display :
a list of all the relations in the data base
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 32
----------
Let's look at the "employee" relation. Since we know nothing about the relation
we can also use the "help" command to learn about it. Type :
-------------------------
! * help employee !
! * \g !
! Executing !
-------------------------
Relation : employee
Owner : ingres
Tuple width : 30
Saved until : Fri Mar 25 11: 01: 30 1977
Number of tuples : 24
Storage structure : paged heap
Relation type : user relation
attribute name type length keyno.
number i 2
name c 20
salary i 2
manager i 2
birthdate i 2
startdate i 2
-------------
! continue !
! * !
-------------
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* help employee
* \g
3. as indicated in the tutorial, the system should display :
a description of the employee relation
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 33
----------
The help command lists overall information about the employee relation together
with each attribute, its type and its length.
INGRES supports three data types : integer numbers, floating point numbers, and
character strings. Character domains can be from 1 to 255 characters in length.
Integer domains can be 1, 2, or 4 bytes in length. This means that integers
can obtain a maximum value of 127; 32,767; and 2,147,483,647 respectively.
Floating point numbers can be either 4 or 8 bytes. Both hold a maximum value
of about 10**38; with 7 or 17 digit accuracy respectively.
To look at all domains we could use the print command or we could use the
retrieve command and list each domain in the target list. INGRES provides a
shorthand way of doing just that. Try the following :
-------------------------------------
! * range of e is employee !
! * retrieve (e.all) !
! * \g !
! Executing . . . !
-------------------------------------
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* range of e is employee
* retrieve (e.all)
* \g
3. as indicated in the tutorial, the system should display :
the employee relation
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 34
----------
! number ! name ! salary ! manage ! birthd ! startd !
!-----------!-----------------------!----------!--------!----------!---------!
! 157 ! Jones, Tim ! 12000 ! 199 ! 1940 ! 1960 !
! 1110 ! Smith, Paul ! 6000 ! 33 ! 1952 ! 1973 !
! 35 ! Evans, Michael ! 5000 ! 32 ! 1952 ! 1974 !
! 129 ! Thomas, Tom ! 10000 ! 199 ! 1941 ! 1962 !
! 13 ! Edwards, Peter ! 9000 ! 199 ! 1928 ! 1958 !
! 215 ! Collins, Joanne ! 7000 ! 10 ! 1950 ! 1971 !
! 55 ! James, Mary ! 12000 ! 199 ! 1920 ! 1969 !
! 26 ! Thompson, Bob ! 13000 ! 199 ! 1930 ! 1970 !
! 98 ! Williams, Judy ! 9000 ! 199 ! 1935 ! 1969 !
! 32 ! Smythe, Carol ! 9050 ! 199 ! 1929 ! 1967 !
! 33 ! Hayes, Evelyn ! 10100 ! 199 ! 1931 ! 1963 !
! 199 ! Bullock, J.D. ! 27000 ! 0 ! 1920 ! 1920 !
! 4901 ! Bailey, Chas M. ! 8377 ! 32 ! 1956 ! 1975 !
! 843 ! Schmidt, Herman ! 11204 ! 26 ! 1936 ! 1956 !
! 2398 ! Wallace, Maggie J. ! 7880 ! 26 ! 1940 ! 1959 !
! 1639 ! Choy, Wanda ! 11160 ! 55 ! 1947 ! 1970 !
! 5119 ! Ferro, Tony ! 13621 ! 55 ! 1939 ! 1963 !
! 37 ! Raveen, Lemont ! 11985 ! 26 ! 1950 ! 1975 !
! 5219 ! Williams, Bruce ! 13374 ! 33 ! 1944 ! 1959 !
! 1523 ! Zugnoni, Athur A. ! 19868 ! 129 ! 1928 ! 1949 !
! 430 ! Brunet, Paul C. ! 17674 ! 129 ! 1938 ! 1959 !
! 994 ! Iwano, Masahiro ! 15641 ! 129 ! 1944 ! 1970 !
! 1330 ! Onstadt, Richard ! 8779 ! 13 ! 1952 ! 1971 !
! 10 ! Ross, Stanley ! 15908 ! 199 ! 1927 ! 1945 !
! 11 ! Ross, Stuart ! 12067 ! 0 ! 1931 ! 1932 !
!___________!_______________________!__________!________!__________!_________!
-------------
! continue !
! * !
-------------
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* range of e is employee
* retrieve (e.all)
* \g
3. as indicated in the tutorial, the system should display :
the employee relation
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 35
----------
"All" is a keyword which is expanded by INGRES to become all domains. The
domains are not guaranteed to be in any particular order. The previous query
is equivalent to :
range of e is employee
retrieve (e.number, e.name, e.salary, e.manager,
e.birthdate, e.startdate)
Let's retrieve the salary of Stan Ross. At this point we will need to be able
to type both upper and lower case letters. If you are on an upper case only
terminal, type a single "\" before a letter you wish to capitalize. Thus on
an upper case only terminal type "\ROSS, \STAN". If you are on an upper and
lower case terminal, use the shift key to capitalize a letter.
Run the query :
-----------------------------------------------
! * retrieve (e.name, e.salary) !
! * where e.name = "Ross, Stan" !
! * \g !
! Executing . . . !
-----------------------------------------------
! name ! salary !
!---------------------------------------------!
!_____________________________________________!
-------------
! continue !
! * !
-------------
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* range of e is employee
* retrieve (e.name, e.salary)
* where e.name = "Ross, Stan"
* \g
3. as indicated in the tutorial, the system should display :
empty
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 36
----------
The result is empty. There is no e.name which satisfies the qualification.
That's strange because we know there is a Stan Ross. However, INGRES does not
know, for example, that "Stanley" and "Stan" are semantically the same.
To get the correct answer in this situation you may use the special "pattern
matching" characters provided by INGRES.
One such character is "*". It matches any string of zero or more characters.
Try the query :
--------------------------------------------
! * retrieve (e.name, e.salary) !
! * where e.name = "Ross, S*" !
! * \g !
! Executing . . . !
--------------------------------------------
! name ! salary !
!-----------------------!---------!
! Ross, Stanley ! 15908 !
! Ross, Stuart ! 12067 !
!_______________________!_________!
-------------
! continue !
! * !
-------------
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* range of e is employee
* retrieve (e.name, e.salary)
* where e.name = "Ross, S*"
* \g
3. as indicated in the tutorial, the system should display :
salary of selected employees
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 37
----------
In the first case "*" matched the string "tanley" and in the second case it
matched "tuart".
Here is another example. Find the salaries of all people whose first name is
"Paul" :
-----------------------------------------
! * retrieve (e.name, e.salary) !
! * where e.name = "*, Paul*" !
! *\g !
! Executing . . . !
-----------------------------------------
! name ! salary !
!-----------------------!----------!
! Smith, Paul ! 6000 !
! Brunet, Paul C. ! 17674 !
!_______________________!__________!
-------------
! continue !
! * !
-------------
Notice that if we had asked for e.name = "*,Paul" we would not have gotten the
second tuple. Also, INGRES ignores blanks in any character comparison whether
using pattern matching characters or not. This means that the following would
all give the same results :
e.name = "Ross,Stanley"
e.name = "Ross, Stanley "
e.name = "R o s s , Stanley"
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* range of e is employee
* retrieve (e.name, e.salary)
* where e.name = "*, Paul*"
* \g
3. as indicated in the tutorial, the system should display :
salary of selected employees
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 38
----------
Particular characters or ranges of characters can be put in square brackets
( [] ). For example, find all people whose names start with "B" through "F" :
---------------------------------
! * retrieve (e.name, e.salary) !
! * where e.name = "[B-F]*" !
! * \g !
! Executing . . . !
---------------------------------
! name ! salary !
!-----------------------!----------!
! Evans, Michael ! 5000 !
! Edwards, Peter ! 9000 !
! Collins, Joanne ! 7000 !
! Bullock, J.D. ! 27000 !
! Bailey, Chas M. ! 8377 !
! Choy, Wanda ! 11160 !
! Ferro, Tony ! 13621 !
! Brunet, Paul C. ! 17674 !
!_______________________!__________!
-------------
! continue !
! * !
-------------
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* range of e is employee
* retrieve (e.name, e.salary)
* where e.name = "[B-F]*"
* \g
3. as indicated in the tutorial, the system should display :
salary of selected employees
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 39
----------
Notice that this last query could be done another way :
-------------------------------------------
! * retrieve (e.name, e.salary) !
! * where e.name > "B" and e.name < "G" !
! * \g !
! Executing . . . !
---------------------------------------------
! name ! salary !
!--------------------!-----------!
! Evans, Michael ! 5000 !
! Edwards, Peter ! 9000 !
! Collins, Joanne ! 7000 !
! Bullock, J.D. ! 27000 !
! Bailey, Chas M. ! 8377 !
! Choy, Wanda ! 11160 !
! Ferro, Tony ! 13621 !
! Brunet, Paul C. ! 17674 !
!____________________!___________!
-------------
! continue !
! * !
-------------
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* range of e is employee
* retrieve (e.name, e.salary)
* where e.name > "B" and e.name < "G"
* \g
3. as indicated in the tutorial, the system should display :
salary of selected employees
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 40
----------
The two results are identical; however, the second way is generally more
efficient for INGRES to process.
There are three types of pattern matching costructs. All three can be used
in any combination for character comparison. They are :
* matches any length character string
? matches any one (non-blank) character
[ ] can match any character listed in the brackets. If two characters
are seperated by a dash ( - ), then it matches any character
falling between the two characters.
The special meaning of a pattern matching character can be turned off by
preceeding it with a "\". This means that "\*" refers to the character "*".
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. there are no commands to type in this lesson. So just go to the next
lesson.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 41
----------
We turn now to the aggregation facilities supported by INGRES. This allows a
user to perform computations on whole domains of a relation. For example, one
aggregate is average (avg). To compute the average salary for all employees,
we enter :
---------------------------------------------
! * retrieve (avgsal = avg (e.salary)) !
! * \g !
! Executing . . . !
---------------------------------------------
! avgsal !
!----------------!
! 11867.520 !
!________________!
-------------
! continue !
! * !
-------------
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* range of e is employee
* retrieve (avgsal = avg (e.salary))
* \g
3. as indicated in the tutorial, the system should display :
avarage salary of all employees
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 42
----------
The particular title "avgsal" is arbitrary, but necessary; INGRES needs some
sort of title for any expression in the target list (other than a simple
domain).
We can also find the minimum and maximum salaries :
--------------------------------------------------------------
! * retrieve (minsal = min(e.salary), maxsal = max(e.salary)) !
! * \g !
! Executing . . . !
--------------------------------------------------------------
! minsal ! maxsal !
!---------!---------!
! 5000 ! 27000 !
!_________!_________!
-------------
! continue !
! * !
-------------
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* range of e is employee
* retrieve (minsal = min(e.salary), maxsal = max(e.salary))
* \g
3. as indicated in the tutorial, the system should display :
minimum and maximum salaries
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 43
----------
If we wanted to know the names of the employees who make the minimum and maxi-
mum salaries, that query would be :
---------------------------------------------------------------
! * retrieve (e.name, e.salary) !
! * where e.salary = min(e.salary) or e.salary = max(e.salary) !
! * \g !
! Executing . . . !
---------------------------------------------------------------
! name ! salary !
!----------------------------!----------!
! Evans, Michael ! 5000 !
! Bullock, J.D. ! 27000 !
!____________________________!__________!
-------------
! continue !
! * !
-------------
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* range of e is employee
* retrieve (e.name, e.salary)
* where e.salary = min(e.salary) or e.salary = max(e.salary)
* \g
3. as indicated in the tutorial, the system should display :
names of employees who make the minimum and maximum salaries
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 44
----------
INGRES supports the following aggregates :
count
min
max
avg
sum
any
We now indicate the query to list each employee along with the average salary
for all employees :
----------------------------------------------
! * retrieve (e.name, peersal = avg(e.salary)) !
! * \g !
! Executing . . . !
----------------------------------------------
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* range of e is employee
* retrieve (e.name, peersal = avg(e.salary))
* \g
3. as indicated in the tutorial, the system should display :
a list of each employee along with the average salary for all
employees
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 45
----------
! name ! peersal !
!-----------------------!--------------!
! Jones, Tim ! 11867.520 !
! Smith, Paul ! 11867.520 !
! Evans, Michael ! 11867.520 !
! Thomas, Tom ! 11867.520 !
! Edwards, Peter ! 11867.520 !
! Collins, Joanne ! 11867.520 !
! James, Mary ! 11867.520 !
! Thompson, Bob ! 11867.520 !
! Williams, Judy ! 11867.520 !
! Smythe, Carol ! 11867.520 !
! Hayes, Evelyn ! 11867.520 !
! Bullock J.D. ! 11867.520 !
! Bailey, Chas M. ! 11867.520 !
! Schmidt, Herman ! 11867.520 !
! Wallace, Maggie J. ! 11867.520 !
! Choy, Wanda ! 11867.520 !
! Ferro, Tony ! 11867.520 !
! Raveen, Lemont ! 11867.520 !
! Williams, Bruce ! 11867.520 !
! Zugnoni, Arthur A. ! 11867.520 !
! Brunet, Paul c. ! 11867.520 !
! Iwano, Masahiro ! 11867.520 !
! Onstad, Richard ! 11867.520 !
! Ross, Stanley ! 11867.520 !
! Ross, Stuart ! 11867.520 !
!_______________________!______________!
-------------
! continue !
! * !
-------------
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* range of e is employee
* retrieve (e.name, peersal = avg(e.salary))
* \g
3. as indicated in the tutorial, the system should display :
a list of each employee along with the average salary for all
employees
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 46
----------
An aggregate always evaluates to a single value. To process the last query,
INGRES replicated the average salary next to each e.name.
Aggregates can have their own qualification. For example, we can retrieve a
list of each employee along with the average salary of those employees over 50.
--------------------------------------------------
! * retrieve (e.name, peersal = !
! * avg (e.salary where 1977-e.birthdate > 50)) !
! * \g !
! Executing . . . !
--------------------------------------------------
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* range of e is employee
* retrieve (e.name, peersal =
* avg (e.salary where 1977-e.birthdate > 50))
* \g
3. as indicated in the tutorial, the system should display :
a list of each employee along with the avarage salary of those
employees over 50
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 47
----------
! name ! peersal !
!---------------------!--------------!
! Jones, Tim ! 19500.000 !
! Smith, Paul ! 19500.000 !
! Evans, Michael ! 19500.000 !
! Thomas, Tom ! 19500.000 !
! Edwards, Peter ! 19500.000 !
! Collins, Joanne ! 19500.000 !
! James, Mary ! 19500.000 !
! Thompson, Bob ! 19500.000 !
! Williams, Judy ! 19500.000 !
! Smythe, Carol ! 19500.000 !
! Hayes, Evelyn ! 19500.000 !
! Bullock, J.D. ! 19500.000 !
! Bailey, Chas M. ! 19500.000 !
! Schmidt, Herman ! 19500.000 !
! Wallace, Maggie J. ! 19500.000 !
! Choy, Wanda ! 19500.000 !
! Ferro, Tony ! 19500.000 !
! Raveen, Lemont ! 19500.000 !
! Williams, Bruce ! 19500.000 !
! Zugnoni, Arthur A. ! 19500.000 !
! Brunet, Paul C. ! 19500.000 !
! Iwano, Masahiro ! 19500.000 !
! Onstad, Richard ! 19500.000 !
! Ross, Stanley ! 19500.000 !
! Ross, Stuart ! 19500.000 !
!_____________________!______________!
-------------
! continue !
! * !
-------------
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* range of e is employee
* retrieve (e.name, peersal =
* avg (e.salary where 1977-e.birthdate > 50))
* \g
3. as indicated in the tutorial, the system should display :
a list of each employee along with the avarage salary of those
employees over 50
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 48
----------
Contrast the previous query with the next one. We will retrieve the names of
those employees over 50 and retrieve the average salary for all employees.
------------------------------------------------------
! * retrieve (e.name, peersal = avg(e.salary)) !
! * where 1977-e.birthdate > 50 !
! * \g !
! Executing . . . !
------------------------------------------------------
! name ! peersal !
!------------------------!--------------!
! James, Mary ! 11867.520 !
! Bullock, J.D. ! 11867.520 !
!________________________!______________!
-------------
! continue !
! * !
-------------
There is a very important distinction between these last two queries. An
aggregate is completely self-contained. It is not affected by the qualification
of the query as a whole.
In the first case, average is computed only for those employees over fifty,
and all employees are retrieved. In the second case, however, average is
computed for all employees but only those employees over 50 are retrieved.
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* range of e is employee
* retrieve (e.name, peersal = avg(e.salary))
* where 1977-e.birthdate > 50
* \g
3. as indicated in the tutorial, the system should display :
the names of those employees over 50 and the average salary for
all employees
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 49
----------
If we wanted a list of all employees over fifty together with the average
salary of employees over fifty, we would combine the previous two queries
into one. That query would be :
------------------------------------------------------!
! * retrieve (e.name, peersal = !
! * avg(e.salary where 1977 - e.birthdate > 50)) !
! * where 1977 - e.birthdate > 50 !
! * \g !
! Executing . . . !
------------------------------------------------------
! name ! peersal !
!----------------------!--------------!
! James, Mary ! 19500.000 !
! Bullock, J.D. ! 19500.000 !
!______________________!______________!
-------------
! continue !
! * !
-------------
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* range of e is employee
* retrieve (e.name, peersal =
* avg(e.salary where 1977 - e.birthdate > 50))
* where 1977 - e.birthdate > 50
* \g
3. as indicated in the tutorial, the system should display :
a list of all employees over fifty together with the avarage
salary of employees over fifty
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 50
----------
It is sometimes useful to have duplicate values removed before an aggregation
is computed. For example if you wanted to know how many managers there are,
the following query will not give the right answer :
------------------------------------------------------
! * retrieve (bosses = count (e.manager)) !
! * \g !
! * Executing . . . !
------------------------------------------------------
! bosses !
!-----------------!
! 25 !
!_________________!
--------------
! continue !
! * !
--------------
Notice that that gives the count of how many tuples there are in employee.
What we want to know is how many unique e.manager's there are.
INGRES provides three special forms of aggregation.
countu count unique values
avgu average unique values
sumu sum unique values
It's interesting to note that minu, maxu, and anyu are not needed. Their values
would be the same whether duplicates were removed or not.
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* range of e is employee
* retrieve (bosses = count (e.manager))
* \g
3. as indicated in the tutorial, the system should display :
wrong answer
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 51
----------
The correct query to find the number of managers is :
--------------------------------------------------------
! * retrieve (bosses = countu (e.manager)) !
! * \g !
! Executing . . . !
--------------------------------------------------------
! bosses !
!---------------!
! 9 !
!_______________!
-------------
! continue !
! * !
-------------
Another aggregate facility supported by INGRES is called aggregate functions.
Aggregate functions group data into categories and perform separate aggrega-
tions on each category.
For example, what if you wanted to retrieve each employee, and the average
salary paid to employees with the same manager? That query would be :
---------------------------------------------------------------
! * retrieve (e.name, manageravg = avg(e.salary by e.manager)) !
! * \g !
! Executing . . . !
---------------------------------------------------------------
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* range of e is employee
* retrieve (bosses = countu (e.manager))
* \g
3. as indicated in the tutorial, the system should display :
the number of managers
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 52
----------
! name ! manageravg !
!--------------------------!----------------!
! Jones, Tim ! 11117.555 !
! Thomas, Tom ! 11117.555 !
! Edwards, Peter ! 11117.555 !
! James, Mary ! 11117.555 !
! Thompson, Bob ! 11117.555 !
! Williams, Judy ! 11117.555 !
! Smythe, Carol ! 11117.555 !
! Hayes, Evelyn ! 11117.555 !
! Ross, Stanley ! 11117.555 !
! Smith, Paul ! 9687.000 !
! Williams, Bruce ! 9687.000 !
! Evans, Michael ! 6688.500 !
! Bailey, Chas M. ! 6688.500 !
! Collins, Joanne ! 7000.000 !
! Bullock, J.D. ! 19533.500 !
! Ross, Stuart ! 19533.500 !
! Schmidt, Herman ! 10356.333 !
! Wallace, Maggie J. ! 10356.333 !
! Raveen, Lamont ! 10356.333 !
! Choy, Wanda ! 12390.500 !
! Ferro, Tony ! 12390.500 !
! Zugnoni, Arthur A. ! 17727.666 !
! Brunet, Paul C. ! 17727.666 !
! Iwano, Masahiro ! 17727.666 !
! Onstad, Richard ! 8779.000 !
!__________________________!________________!
-------------
! continue !
! * !
-------------
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* range of e is employee
* retrieve (e.name, manageravg = avg(e.salary by e.manager))
* \g
3. as indicated in the tutorial, the system should display :
each employee and the average salary paid to employees with the
same manager
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 53
----------
The first nine people all have the same manager and their average salary is
11117.555. The next two people have the same manager and their salary is
9687.000 etc.
Once again, if we wanted to see the same list just for those employees over
50 :
--------------------------------------------------------------
! * retrieve (e.name, manageravg = avg(e.salary by e.manager !
! * where 1977-e.birthdate > 50 !
! * \g !
! Executing . . . !
--------------------------------------------------------------
! name ! manageravg !
!-------------------------!-----------------!
! James, Mary ! 11117.555 !
! Bullock J.D. ! 19533.500 !
!_________________________!_________________!
-------------
! continue !
! * !
-------------
Aggregate functions (unlike simple aggregates) are not completely local to
themselves. The domains upon which the data is grouped (called the by-list)
are logically connected to the domains in the rest of the query.
In these last examples, the "e.manager" in the by-list refers to the same
tuple as "e.name" in the target list.
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* range of e is employee
* retrieve (e.name, manageravg = avg(e.salary by e.manager))
* where 1977-e.birthdate > 50
* \g
3. as indicated in the tutorial, the system should display :
the same list just for those employees over 50
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 54
----------
If we wanted to compute the average salaries by manager for only managers 33
and 199, then the query would be :
------------------------------------------------------
! * retrieve (e.name, manageravg = !
! * avg(e.salary by e.manager) !
! * where e.manager = 199 or e.manager = 33 !
! * \g !
! Executing . . . !
------------------------------------------------------
! name ! manageravg !
!-------------------------!-----------------!
! Jones, Tim ! 11117.555 !
! Thomas, Tom ! 11117.555 !
! Edwards, Peter ! 11117.555 !
! James, Mary ! 11117.555 !
! Thompson, Bob ! 11117.555 !
! Williams, Judy ! 11117.555 !
! Smythe, Carol ! 11117.555 !
! Hayes, Evelyn ! 11117.555 !
! Ross, Stanley ! 11117.555 !
! Smith, Paul ! 9687.000 !
! Williams, Bruce ! 9687.000 !
!_________________________!_________________!
-------------
! continue !
! * !
-------------
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* range of e is employee
* retrieve (e.name, manageravg =
* avg(e.salary by e.manager))
* where e.manager = 199 or e.manager = 33
* \g
3. as indicated in the tutorial, the system should display :
the avarage salaries by manager for only managers 33 and 199
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 55
----------
Suppose we wanted to find out how many people work for each manager, and in
addition wanted only to include those employees who have worked at least
seven years.
--------------------------------------------------------------------
! * retrieve (e.manager, people = count(e.name by e.manager where !
! * e.startdate < 1970)) !
! * \g !
! Executing !
--------------------------------------------------------------------
! manage ! people !
!-----------!----------!
! 199 ! 8 !
! 33 ! 2 !
! 32 ! 0 !
! 10 ! 0 !
! 0 ! 2 !
! 26 ! 2 !
! 55 ! 1 !
! 129 ! 2 !
! 13 ! 0 !
!___________!__________!
-------------
! continue !
! * !
-------------
Notice that managers 32, 10, and 13 have no employees who started before 1970.
Now suppose we want to know the average salary for those employees. Simply
change "count" to "avg" and rerun the query.
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* range of e is employee
* retrieve (e.manager, people = count(e.name by e.manager where
* e.startdate < 1970))
* \g
3. as indicated in the tutorial, the system should display :
how many people worked for more than seven years for each manager
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 56
----------
--------------------------------------------------------------------
! * retrieve (e.manager, people = avg(e.salary by e.manager where !
! * e.startdate < 1970)) !
! * \g !
! Executing . . . !
--------------------------------------------------------------------
! manage ! people !
!----------!------------!
! 199 ! 10882.250 !
! 33 ! 22687.000 !
! 32 ! 0.000 !
! 10 ! 0.000 !
! 0 ! 19533.500 !
! 26 ! 9542.000 !
! 55 ! 13621.000 !
! 129 ! 18771.000 !
! 13 ! 0.000 !
!__________!____________!
-------------
! continue !
! * !
-------------
Notice what INGRES does for managers 32, 10 and 13. The average salary for
those manager employees is actually undefined since there are no employees
who started before 1970. INGRES always makes undefined values zero in
aggregates.
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* range of e is employee
* retrieve (e.manager, people = avg(e.salary by e.manager where
* e.startdate < 1970))
* \g
3. as indicated in the tutorial, the system should display :
the avarage salary for the employees found in the last lesson
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 57
----------
If you want to remove the zero values from the output, a qualification can be
added to the query. The following query will find the average salaries only
for those which are greater than zero.
-------------------------------------------------------------------
! * retrieve (e.manager, people = avg(e.salary by e.manager where !
! * e.startdate < 1970)) !
! * where avg(e.salary by e.manager where e.startdate < 1970) > 0 !
! * \g !
! Executing . . . !
-------------------------------------------------------------------
! manage ! people !
!----------!-------------!
! 199 ! 10882.250 !
! 33 ! 22687.000 !
! 0 ! 19533.500 !
! 26 ! 9542.000 !
! 55 ! 13621.000 !
! 129 ! 18771.000 !
!__________!_____________!
-------------
! continue !
! * !
-------------
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* range of e is employee
* retrieve (e.manager, people = avg(e.salary by e.manager where
* e.startdate < 1970))
* where avg(e.salary by e.manager where e.startdate < 1970) > 0
* \g
3. as indicated in the tutorial, the system should display :
the avarage salaries only for those which are greater than zero
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 58
----------
Up until now we have been retrieving results directly onto the terminal. You
can also save results by retrieving them into a new relation. This is done by
saying :
------------------------------
! retrieve into newrel (...) !
! where . . . !
------------------------------
The rules are exactly the same as for retrieves onto the terminal. INGRES will
create the new relation with the correct domains, and then put the results of
the query in the new relation.
For example, create a new relation called "overpaid" which has only those
employees who make more than $8000 :
------------------------------------------------
! * retrieve into overpaid (e.all) !
! * where e.salary > 8000 !
! * print overpaid !
! * \g !
! Executing . . . !
------------------------------------------------
overpaid relation
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* range of e is employee
* retrieve into overpaid (e.all)
* where e.salary > 8000
* print overpaid
* \g
3. as indicated in the tutorial, the system should display :
a new relation, called "overpaid"
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 59
----------
! number ! name ! salary ! manage ! birthd ! startd !
!----------!-----------------------!----------!----------!----------!---------!
! 10 ! Ross, Stanley ! 15908 ! 199 ! 1927 ! 1945 !
! 11 ! Ross, Stuart ! 12067 ! 0 ! 1931 ! 1932 !
! 13 ! Edwards, Peter ! 9000 ! 199 ! 1928 ! 1958 !
! 26 ! Thompson Bob ! 13000 ! 199 ! 1930 ! 1970 !
! 32 ! Smythe Carol ! 9050 ! 199 ! 1929 ! 1967 !
! 33 ! Hayes, Evelyn ! 10100 ! 199 ! 1931 ! 1963 !
! 37 ! Raveen, Lamont ! 11985 ! 26 ! 1950 ! 1974 !
! 55 ! James, Mary ! 12000 ! 199 ! 1920 ! 1969 !
! 98 ! Williams, Judy ! 9000 ! 199 ! 1935 ! 1969 !
! 129 ! Thomas Tom ! 10000 ! 199 ! 1941 ! 1962 !
! 157 ! Jones Tim ! 12000 ! 199 ! 1940 ! 1960 !
! 199 ! Bullock, J.D. ! 27000 ! 0 ! 1920 ! 1920 !
! 430 ! Brunet, Paul C. ! 17674 ! 129 ! 1938 ! 1959 !
! 843 ! Schmidt, Herman ! 11204 ! 26 ! 1936 ! 1956 !
! 994 ! Iwano, Masahiro ! 15641 ! 129 ! 1944 ! 1970 !
! 1330 ! Onstad, Richard ! 8779 ! 13 ! 1952 ! 1971 !
! 1523 ! Zugnoni, Arthur A. ! 19868 ! 129 ! 1928 ! 1949 !
! 1639 ! Choy, Wanda ! 11160 ! 55 ! 1947 ! 1970 !
! 4901 ! Bailey, Chas M. ! 8377 ! 32 ! 1957 ! 1975 !
! 5119 ! Ferro, Tony ! 13621 ! 55 ! 1939 ! 1963 !
! 5219 ! Williams, Bruce ! 13374 ! 33 ! 1944 ! 1959 !
!__________!_______________________!__________!__________!__________!_________!
-------------
! continue !
! * !
-------------
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. there are no commands to type in this lesson. So just go to the next
lesson.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 60
----------
On a "retrieve into" nothing is printed. We had to include a "print" command
to see the results. Also, the relation name on a "retrieve into" must not
already exist. For example, if we tried the same query again :
---------------------------------------------------------------
! * \g !
! Executing . . . !
! 5102 : CREATE : duplicate relation name overpaid !
! continue !
! * !
---------------------------------------------------------------
There are two special features about a "retrieve into". First, the result
relation is automatically sorted and any duplicate tuples are removed.
Second, the relation becomes part of the data base and is owned by you. If
you don't want it to be saved you should remember to destroy it. The mechanism
for destroying a relation will be mentioned a bit later.
So far we have only retrieved data but never changed it. INGRES supports three
update commands, append, replace, and delete.
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* range of e is employee
* retrieve into overpaid (e.all)
* where e.salary > 8000
* print overpaid
* \g
3. as indicated in the tutorial, the system should display :
an error message : you cannot duplicate the name of an existing
relation (overpaid)
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 61
----------
For example, to add "Tom Terrific" to the list of overpaid employees and start
him off at $ 10000 :
----------------------------------------------------------------
! * append to overpaid (name = "terrific, Tom", salary = 10000) !
! * \g !
! Executing . . . !
----------------------------------------------------------------
-------------
! continue !
! * !
-------------
Notice that we specified values for only two of the six domains in "overpaid".
That is fine. INGRES will automatically set numeric domains to zero and
character domains to blank, if they are not specified.
Notice also that INGRES did not print anything after the query. This is true
for all update commands.
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* append to overpaid (name = "Terrific, Tom", salary = 10000)
* \g
3. as indicated in the tutorial, the system should display :
one tuple has been appended to the overpaid relation
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 62
----------
Let's give everyone in overpaid a 10% raise. To do this we want to replace
o.salary by 1.1 times its value. Type the query :
---------------------------------------------------
! * range of o is overpaid !
! * replace o(salary = o.salary * 1.1) !
! * \g !
! Executing . . . !
---------------------------------------------------
-------------
! continue !
! * !
-------------
While the append command requires that you give a relation name (e.g. append
to overpaid), the replace and delete commands require a tuple variable.
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* range of o is overpaid
* replace o(salary = o.salary * 1.1)
* \g
3. as indicated in the tutorial, the system should display :
the overpaid relation has been updated (22 tuples)
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 63
----------
Note that the command is :
replace o ( . . . )
where . . .
and not :
replace overpaid ( . . . )
where . . .
print the results of these last two updates :
----------------------------------
! * print overpaid !
! * \g !
! Executing . . . !
----------------------------------
overpaid relation
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* range of o is overpaid
* print overpaid
* \g
3. as indicated in the tutorial, the system should display :
the overpaid relation has been updated (22 tuples)
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 64
----------
! number ! name ! salary ! manage ! birthd ! startd !
!-----------!-----------------------!---------!----------!----------!--------!
! 10 ! Ross, Stanley ! 17498 ! 199 ! 1927 ! 1945 !
! 11 ! Ross, Stuart ! 13273 ! 0 ! 1931 ! 1932 !
! 13 ! Edwards, Peter ! 9899 ! 199 ! 1928 ! 1958 !
! 26 ! Thompson, Bob ! 14299 ! 199 ! 1930 ! 1970 !
! 32 ! Smythe, Carol ! 9954 ! 199 ! 1929 ! 1967 !
! 33 ! Hayes, Evelyn ! 11109 ! 199 ! 1931 ! 1963 !
! 37 ! Raveen, Lemont ! 13183 ! 26 ! 1950 ! 1974 !
! 55 ! James, Mary ! 13199 ! 199 ! 1920 ! 1969 !
! 98 ! Williams, Judy ! 9899 ! 199 ! 1935 ! 1969 !
! 129 ! Thomas, Tom ! 10999 ! 199 ! 1941 ! 1962 !
! 157 ! Jones, Tim ! 13199 ! 199 ! 1940 ! 1960 !
! 199 ! Bullock, J.D. ! 29699 ! 0 ! 1920 ! 1920 !
! 430 ! Brunet, Paul C. ! 19441 ! 129 ! 1938 ! 1959 !
! 843 ! Schmidt, Herman ! 12324 ! 26 ! 1936 ! 1956 !
! 994 ! Iwani, Masahiro ! 17205 ! 129 ! 1944 ! 1970 !
! 1330 ! Onstad, Richard ! 9656 ! 13 ! 1952 ! 1971 !
! 1523 ! Zugnoni, Arthur A. ! 21854 ! 129 ! 1928 ! 1949 !
! 1639 ! Choy, Wanda ! 12275 ! 55 ! 1947 ! 1970 !
! 4901 ! Bailey, Chas M. ! 9214 ! 32 ! 1956 ! 1975 !
! 5119 ! Ferro, Tony ! 14983 ! 55 ! 1939 ! 1963 !
! 5219 ! Williams, Bruce ! 14711 ! 33 ! 1944 ! 1959 !
! 0 ! Terrific, Tom ! 11000 ! 0 ! 0 ! 0 !
!___________!_______________________!_________!__________!__________!________!
-------------
! continue !
! * !
-------------
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. there are no commands to type in this lesson. So just go to the next
lesson.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 65
----------
Let's fire whoever has the smallest salary :
---------------------------------------------------------
! * delete o where o.salary = min(o.salary) \g !
! Executing . . . !
---------------------------------------------------------
-------------
! continue !
! * !
-------------
Notice that the delete command requires a tuple variable (eg. delete o) and not
a relation name.
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* range of o is overpaid
* delete o where o.salary = min(o.salary)
* \g
3. as indicated in the tutorial, the system should display :
one tuple has been deleted
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 66
----------
What if we wanted to know who makes more than Tom Terrific? The query to do
this is very subtle. First we use a new tuple variable called "t" which ranges
over overpaid, and will be used to refer to Tom. t.name must equal "Terrific,
Tom". Next, we use a tuple variable called "o" which will scan the whole
ralation. If we ever find an o.salary > t.salary then o.name must make more
than Tom.
The complete query is :
---------------------------------------------------------------
! * range of t is overpaid !
! * retrieve (o.name, osal = o.salary, tomsal = t.salary) !
! * where o.salary > t.salary !
! * and t.name = "Terrific, Tom" !
! * \g !
! Executing . . . !
---------------------------------------------------------------
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* range of o is overpaid
* range of t is overpaid
* retrieve (o.name, osal = o.salary, tomsal = t.salary)
* where o.salary > t.salary
* and t.name = "Terrific, Tom"
* \g
3. as indicated in the tutorial, the system should display :
the list of people who earn more than Tom Terrific
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 67
----------
! name ! osal ! tomsal !
!-------------------------!-----------!-------------!
! Ross, Stanley ! 19247 ! 11000 !
! Ross, Stuart ! 14600 ! 11000 !
! Thompson, Bob ! 15728 ! 11000 !
! Hayes, Evelyn ! 12219 ! 11000 !
! Raveen, Lemont ! 14501 ! 11000 !
! James, Mary ! 14518 ! 11000 !
! Thomas, Tom ! 12098 ! 11000 !
! Jones, Tim ! 14518 ! 11000 !
! Bullock, J.D. ! 32668 ! 11000 !
! Brunet, Paul C. ! 21385 ! 11000 !
! Schmidt, Herman ! 13556 ! 11000 !
! Iwano, Masahiro ! 18925 ! 11000 !
! Zugnoni, Arthur A. ! 24039 ! 11000 !
! Choy, Wanda ! 13502 ! 11000 !
! Ferro, Tony ! 16481 ! 11000 !
! Williams, Bruce ! 16182 ! 11000 !
!_________________________!___________!_____________!
------------
! continue !
! * !
------------
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. there are no commands to type in this lesson. So just go to the next
lesson.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 68
----------
If we wanted to give Tom Terrific $50 more than anyone else, the query would
be :
-----------------------------------------------------
! * replace o(salary = max(o.salary) + 50) !
! * where o.name = "Terrific, Tom" !
! * \g !
! Executing . . . !
-----------------------------------------------------
-------------
! continue !
! * !
-------------
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* range of o is overpaid
* replace o(salary = max(o.salary) + 50)
* where o.name = "Terrific, Tom"
* \g
3. as indicated in the tutorial, the system should display :
one tuple has been modified
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 69
----------
Finally, to destroy a relation owned by yourself, type the command :
----------------------------------
! * destroy overpaid !
! * \g !
! Executing . . . !
----------------------------------
-------------
! continue !
! * !
-------------
We are now ready to leave INGRES. This is done either by typing an end-of-file
(control/d) or more typically use the "\q" command :
-----------------------------------------
! * \q !
! INGRES vers 6.1/0 logout !
! Tue Aug 30 14:55:20 1977 !
! goodbye bob - - come again !
-----------------------------------------
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a TUTORIAL ON INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* destroy overpaid
* \g
3. as indicated in the tutorial, the system should display :
the relation has been removed from the database
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 100
----------
CREATING AND MAINTAINING A DATABASE USING INGRES
by
ROBERT EPSTEIN
Memorandum No. ERL - M77 - 71
December 16, 1977
Electronics Research Laboratory
College of Engineering
University of California, Berkeley
94720
CREATING AND MAINTAINING A DATABASE USING INGRES
1. INTRODUCTION
In this paper we describe how to create, structure and maintain relations in
INGRES. It is assumed that the reader is familiar with INGRES and understands
QUEL, the INGRES query language. It is strongly suggested that the document
" A TUTORIAL ON INGRES" (ERL M77/25) be read first.
This paper is divided into six sections :
1. Introduction
2. Creating a Relation
3. Using Copy
4. Storage Structures
5. Secondary Indices
6. Recovery and Data Update
To create a new data base you must be a valid INGRES user and have "create
data base" permission. These permissions are granted by the "ingres" super-
user. If you pass those two requirements you can create a data base using the
command to the Unix shell :
--------------------------
! % creatdb mydata !
--------------------------
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, a text on a DATABASE USING INGRES has appeared on the screen.
2. type the command indicated by the tutorial (% creatdb mydata).
The percent sign is not part of the command. It is only there
to tell you to enter a Unix command.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 101
----------
where "mydata" is the name of the data base. You become the "data base admini-
strator" (DBA) for mydata. As the DBA you have certain special powers.
1. Any relation created by you can be accessed by anyone else using "mydata".
If any other user creates a relation it is strictly private and cannot be
accessed by the DBA or any other user.
2. You can use the "-u" flag in ingres and printr. This enables you to use
ingres on "mydata" with someone else's id. Refer to the INGRES reference
manual under sections ingres (unix) and users (files) for details.
3. You can run sysmod, restore and purge on "mydata".
4. The data base by default is created to allow for multiple concurrent
users. If only one user will ever use the data base at a time, the data
base manager can turn off the concurrency control. Refer to creatdb (unix)
in the INGRES reference manual. Once a data base has been created you
should immediately run :
----------------------
! % sysmod mydata !
----------------------
This program will convert the system relations to their "best" structure for
use in INGRES. Sysmod will be explained further in section 4.
As a DBA or as a user you can create and structure new relations in any data
base to which you have access. The remainder of this paper describes how this
is done.
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a DATABASE USING INGRES has appeared on the screen.
2. type the command :
% sysmod mydata
3. as indicated in the tutorial, the system should display :
sysmod done
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 102
----------
2. CREATING NEW RELATIONS IN INGRES
There are two ways to create new relations in INGRES.
create
retrieve into
"Retrieve into" is used to form a new relation from one or more existing
relations. "Create" is used to create a new relation with no tuples in it.
example 1 :
range of p is parts
range of s is supply
retrieve into newsupply (
number = s.snum,
p.pname,
s.shipdate)
where s.pnum = p.pnum
example 2 :
create newsupply (
number = i2,
pname = c20,
shipdate = c8)
In example 1 INGRES creates a new relation called "newsupply", computing what
the format of each domain should be. The query is then run and new supply is
modified to "cheapsort". (This will be covered in more detail in section 4.)
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a DATABASE USING INGRES has appeared on the screen.
2. type the commands :
% ingres mydata
* create newsupply (
* number = i2,
* pname = c20,
* shipdate = c8)
* \g
3. as indicated in the tutorial, the system should display :
a new relation
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 103
----------
In example 2 "newsupply" is created and the name and format for each domain is
given. The format types which are allowed are :
i1 1 byte integer
i2 2 " "
i4 4 " "
f4 4 byte floating point number
f8 8 " " " "
c1, c2, ... , c255 1, 2, ... , 255 byte character
In example 2, the width of an individual tuple is 30 bytes (2 + 20 + 8), and
the relation has three domains. Beware that INGRES has limits. A relation
cannot have more that 49 domains and the tuple width cannot exceed 498 bytes.
UNIX allocates space on a disk in units of 512 byte pages. INGRES gets a per-
formance advantage by doing I/O in one block units. Therefore relations are
divided into 512 byte pages. INGRES never splits a tuple between two pages.
Thus some space can be wasted. There is an overhead of 12 bytes per page plus
2 bytes for every tuple on the page. The formulas are :
number tuples per page = 500 / (tuple width + 2)
wasted space = 500 - number of tuples per page
* (tuple width + 2)
For our example there are
22 = 500 / (20 + 2)
16 = 500 - 22 * (20 + 2)
22 tuples per page and 16 bytes wasted per page. These computations are valid
only for uncompressed relations. We will return to this subject in section 4
when we discuss compression.
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a DATABASE USING INGRES has appeared on the screen.
2. there are no commands to type in this lesson. So just go to the next
lesson.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 104
----------
If you forget a domain name or a format, use the "help" command.
For example if you gave the INGRES command :
help newsupply
the following would be printed :
Relation : newsupply
Owner : bob
Tuple width : 30
Saved until : Thu Nov 10 16:17:06 1977
Number of tuples : 0
Storage structure : paged heap
Relation type : user relation
attribute name type length keyno
number i 2
pname c 20
shipdate c 8
Notice that every relation has an expiration date. This is set to be one week
from the time it was created. The "save" command can be used to save the
relation longer. See "save (quel)" and "purge (unix)" in the INGRES reference
manual.
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a DATABASE USING INGRES has appeared on the screen.
2. type the commands :
% ingres mydata
* help newsupply
* \g
3. as indicated in the tutorial, the system should display :
a description of the relation
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 105
----------
3. COPYING DATA TO AND FROM INGRES.
Once a relation is created, there are two mechanisms for inserting new data :
append command
copy command
Append is used to insert tuples one at a time, or for filling one relation
from other relations.
Copy is used for copying data from a UNIX file into a relation. It is used for
copying data from another program, or for copying data from another system.
It is also the most convenient way to copy any data larger than a few tuples.
Let's begin by creating a simple relation and loading data into it.
Example :
create donation (name = c10, amount = f4, ext = i2)
Now suppose we have two people to enter. The simplest procedure is probably
to run the two queries in INGRES using the append command.
append to donation (name = "frank", amount = 5, ext = 204)
append to donation ( name = "harry", ext = 209, amount = 4.50)
Note that the order in which the domains are given does not matter. INGRES
matches by recognizing attribute names and does not care in what order attri-
butes are listed. Here is what the relation "donation" looks like now :
donation relation
! name ! amount ! ext !
!---------------!-------------!----------!
! frank ! 5.000 ! 204 !
! harry ! 4.500 ! 209 !
!_______________!_____________!__________!
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a DATABASE USING INGRES has appeared on the screen.
2. type the commands :
% ingres mydata
* create donation (name = c10, amount = f4, ext = i2)
* append to donation (name = "frank", amount = 5, ext = 204)
* append to donation (name = "harry", ext = 209, amount = 4.50)
* print donation
* \g
3. as indicated in the tutorial, the system should display :
the donation relation
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 106
----------
We now have two people entered into the donation relation. Suppose we had fifty
more to enter. Using the append command is far too tedious since so much typing
is involved for each tuple. The copy command will better suit our purposes.
Copy can take data from a regular Unix file in a variety of formats and append
it to a relation. To use the copy command first create a Unix file (typically
using "ed") containing the data.
For example, let's put five new names in a file using the editor.
--------------------------------
! % ed !
! a !
! bill, 3.50, 302 !
! sam, 10.00, 410 !
! susan, , 100 !
! sally, .5, 305 !
! george, 4.00, 302 !
! . !
! w newdom !
! 68 !
! q !
! % !
--------------------------------
The format of the above file is a name followed by a comma, followed by the
amount, then a comma, then the extension, and finally a newline. Null entries,
for example the amount for susan, are perfectly legal and default to zero for
numerical domains and blanks for character domains.
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a DATABASE USING INGRES has appeared on the screen.
2. type the commands :
% ed
a
bill, 3.50, 302
sam, 10.00, 410
susan, , 100
sally, .5, 305
george, 4.00, 302
.
w newdom
q
% cat newdom
3. as indicated in the tutorial, the system should display :
a file containing 5 names
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 107
----------
To use copy we enter INGRES and give the copy command.
copy donation (name = c0, amount = c0, ext = c0)
from "/mnt/bob/newdom"
Here is how the copy command works ;
copy relname (list of what to copy) from "full pathname"
In the case above we wrote :
copy donation ( . . . ) from "/mnt/bob/newdom"
Although amount and ext are stored in the relation as f4 (floating point) and
i2 (integer), in the Unix file they were entered as characters. In specifying
the format of the domain, copy accepts :
domain = format
where domain is the domain name and the format in the UNIX file is one of
i1, i2, i4 (true binary integer of size 1, 2, or 4)
f4, f8 (true binary float point of size 4 or 8)
c1, c2, c3,...c255 (a fixed length character string)
c0 (a variable length character string delimited by a
comma, tab or new line)
In the example we use
name = c0, amount = c0, extensionl = c0
This means that each of the domains was stored in the Unix file as variable
length character strings. Copy takes the first comma, tab, or new line charac-
ter as the end of the string. This by far is the most common use of copy when
the data is being entered into a relation for the first time.
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a DATABASE USING INGRES has appeared on the screen.
2. type the commands :
% ingres mydata
* copy donation (name = c0, amount = c0, ext = c0)
* from "/usr/nova/jclevin/learn_db/dms/newdom"
the student should replace the instructor's path by his or her own path
* print donation
* \g
3. as indicated in the tutorial, the system should display :
an updated relation
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 108
----------
Copy can also be used to copy data from a relation into a Unix file. For
example :
copy donation (name = c10, amount = c10, ext = c5)
into "/mnt/bob/data"
This will cause the folowing to happen :
1. If the file /mnt/bob/data already exists it will be destroyed.
2. The file is created in mode 600 (read/write by you only)
3. Name will be copied as a 10 character field, immediately followed by
amount, immediately followed by ext. Amount will be converted to a
character field 10 characters wide. Ext. will be converted to a character
field 5 characters wide.
The file "/mnt/bob/data" would be a stream of characters looking like this :
frank 5.000 20 4harry 4.500 209bill
3.500 302sam 10.000 410susan
0.000 100sally 0.500 305george
4.000 302
The output was broken into four lines to make it fit on this page. In actuality
the file is a single line. Another example :
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a DATABASE USING INGRES has appeared on the screen.
2. type the commands :
% ingres mydata
* copy donation (name = c10, amount = c10, ext = c5)
* into "/usr/nova/jclevin/learn_db/dms/data"
the student should replace the instructor's path by his or her own path
* \g
* \q
% cat data
3. as indicated in the tutorial, the system should display :
a file containing 7 names
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 109
----------
copy (name = c0, colon = d1, ext = c0, comma = d1
amt = c0, nl = d1) into "/mnt/bob/data"
In this example "c0" is interpreted to mean "use the appropriate character
format". For character domains it is the width of the domain. Numeric domains
are converetd to characters according to the INGRES defaults. (see ingres
(unix)).
The statements :
colon = d1
comma = d1
nl = d1
are used to insert one colon, comma, and newline into the file. The format "d1"
is interpreted to mean one dummy character. When copying into Unix file, a
selected set of characters can be inserted into the file using this "dummy
domain" specification. Here is what the file "/mnt/bob/data" would look like :
frank : 204, 5.000
harry : 209, 4.500
bill : 302, 3.500
sam : 410, 10.000
susan : 100, 0.000
sally : 305, 0.500
george : 302, 4.000
If you wanted a file with the true binary representation of the numbers you
would use :
copy (name = c10, amount = f4, ext = i2)
This would create a file with the exact copy of each tuple, one after the other.
This is frequently desireable for temporary backup purposes and it guarantees
that floating point domains will be exact.
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a DATABASE USING INGRES has appeared on the screen.
2. type the commands :
% ingres mydata
* copy donation (name = c0, colon = d1, ext = c0, comma = d1,
* amount = c0, nl = d1) into "/usr/nova/jclevin/learn_db/dms/data"
the student should replace the instructor's path by his or her own path
* \g
* \q
% cat data
3. as indicated in the tutorial, the system should display :
a file containing 7 names
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 110
----------
TYPICAL ERRORS.
There are 17 errors that can occur in a copy. We will go through the most
common ones.
Suppose you have a file with
bill, 3.5, 302
sam, 10, 410,
susan, 3, 100
and run the copy command
copy donation (name = c0, amount = c0, ext = c0)
from "/mnt/bob/data"
You would get the error message
5809 : COPY : bad input string for domain amount. Input was "susan". There were
2 tuples successfully copied from /mnt/bob/data into donation.
What happened is that line 2 had an extra comma. The first two tuples were
copied correctly. For the next tuple, name = "" (blank), amount = "susan", and
ext = "3". Since "susan" is not a proper floating point number, an error was
generated and processing was stopped after two tuples.
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a DATABASE USING INGRES has appeared on the screen.
2. type the commands :
you should reedit the "data file" as indicated in the tutorial
% ed
a
bill, 3.5, 302
sam, 10, 410,
susan, 3, 100
.
w data
q
% ingres mydata
* copy donation (name = c0, amount = c0, ext = c0)
* from "/usr/nova/jclevin/learn_db/dms/newdom"
the student should replace the instructor's path by his or her own path
* print donation
* \g
3. as indicated in the tutorial, the system should display :
an error message
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 111
----------
If you tried to copy the file with a file such as
nancy, 5.0, 35000
you would get the error message
5809 : COPY : bad input string for domain ext. Input was "35000". There were
0 tuples successfully copied from /mnt/bob/data into donation.
Here, since ext is an i2 (integer) domain, it cannot exceed the value 32767.
There are numerous other error messages, most of which are self-explanatory.
In addition there are three, non-fatal warnings which may appear on a copy
"from".
If you are copying from a file into a relation which is ISAM or hash, a count
of the number of duplicate tuples will appear, (if there were any). This will
never appear on a "heap" because no duplicate checking is performed.
INGRES does not allow characters (such as "bell" etc.) to be stored. If copy
reads any control characters, it converts them to blanks and reports the number
of domains that had control characters in them.
If you are copying using the c0 option, copy will report if any character
strings were longer than their domains and had to be truncated.
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a DATABASE USING INGRES has appeared on the screen.
2. type the commands :
you should reedit the "data file" as indicated in the tutorial
% ed
a
nancy, 5.0, 35000
.
w data
q
% ingres mydata
* copy donation (name = c10, amount = f4, ext = i2)
* from "/usr/nova/jclevin/learn_db/dms/newdom"
the student should replace the instructor's path by his or her own path
* print donation
* \g
3. as indicated in the tutorial, the system should display :
an error message (it should !!!!)
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 112
----------
SPECIAL FEATURES
There are a few special functions that make copy a little easier to use
1. Bulk copy
If you ask for :
copy relname () from "file"
or
copy relname () into "file"
copy expands the statement to mean :
copy each domain in its proper order according to its proper format.
So, if you said
copy donation () into :/mnt/bob/donation"
It would be the same as asking for :
copy donation (name = c10, amount = f4, ext = i2)
into "/mnt/bob/donation"
This provides a convenient way to copy whole relations to and from INGRES.
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a DATABASE USING INGRES has appeared on the screen.
2. type the commands :
% ingres mydata
* copy donation ()
* into "/usr/nova/jclevin/learn_db/dms/donation"
the student should replace the instructor's path by his or her own path
* \g
* \q
% cat donation
3. as indicated in the tutorial, the system should display :
a file containing the whole relation
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 113
----------
2. DUMMY DOMAINS
If you are copying data from another computer or program, frequently there will
be a portion of the data that you will want to ignore. This can be done by
using the dummy domain specifications d0, d1, d2 ... d511. For example :
copy rel (dom1 = c5, dummy = d2, dom2 = i4,
dumb = d0) from "/mnt/me/data"
The first five characters are put in dom1, the next two characters are ignored.
The next four bytes are an i4 (integer) and go in dom2, and the remaining
delimited string is ignored. The name given to a dummy specifier is ignored.
As mentioned previously, dummy domains can be used on a copy "into" a Unix file
for inserting special characters. The list of recognizable names includes :
nl newline
tab tab character
sp space
nul a zero byte
null a zero byte
comma ,
dash -
colon :
lparen (
rparen )
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a DATABASE USING INGRES has appeared on the screen.
2. type the commands :
you should reedit the "data file" as indicated in the tutorial
% ed
a
nancyxx1000yyyy
.
w data
q
% ingres mydata
* create rel (dom1 = c5, dom2 = i4)
* copy rel (dom1 = c5, dummy = d2, dom2 = i4,
* dumb = d0) from "/usr/nova/jclevin/learn_db/dms/data"
the student should replace the instructor's path by his or her own path
* print rel
* \g
3. as indicated in the tutorial, the system should display :
a new relation
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 114
----------
3. TRUNCATION
It is not uncommon to have a mistake occur and need to start over. The simplest
way to do that is to "truncate" the relation. This is done by the command :
modify relname to truncated
This has the effect of removing all tuples in relname, releasing all disk
space, and making relname a heap again. It is the logical equivalent of a des-
troy followed by a create (but with a lot less typing).
Since formatting mistakes are possible with copy, it is not generally a good
idea to copy data into a relation that alredy has valid data in it. The best
procedure is to create a temporary relation with the same domains as the exis-
ting relation. Copy data into the temporary relation and then append it to the
real relation. For example :
create tempdom (name = c10, amount = f4, ext = i2)
copy tempdom (name = c0, amount = c0, ext = c0)
from "/mnt/bob/data"
range of td = tempdom
append to donation (td.all)
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a DATABASE USING INGRES has appeared on the screen.
2. type the commands :
you should reedit the "data file" as indicated in the tutorial
% ed
a
nancy, 5.0, 200
.
w data
q
% ingres mydata
* create tempdom (name = c10, amount = f4, ext = i2)
* copy tempdom (name = c0, amount = c0, ext = c0)
* from "/usr/nova/jclevin/learn_db/dms/data"
* range of td = tempdom
* append to donation (td.all)
the student should replace the instructor's path by his or her own path
* print donation
* \g
3. as indicated in the tutorial, the system should display :
an updated relation
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 115
----------
4. SPECIFYING DELIMITORS.
Sometimes it is desirable to specify what the delimiting character should be
on a copy "from" a file. This can be done by specifying :
domain = c0delim
where "delim" is a valid delimitor taken from the list of recognizable names.
This list was summarized on the previous page under "dummy domains".
For example :
copy donation (name = conl) from "/mnt/me/data"
will copy names from the file to the relation. Only a new line will delimit
the names so any commas or tabs will be passed along as part of the name.
When copying "into" a Unix file, the "delim" is actually written into the file,
so on a copy "into" the specification :
copy donation (name = c0nl) into "/mnt/me/file"
will cause "name" to be written followed by a new line character.
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a DATABASE USING INGRES has appeared on the screen.
2. type the commands :
% ingres mydata
* copy donation (name = c0nl)
* into "/usr/nova/jclevin/learn_db/dms/file"
the student should replace the instructor's path by his or her own path
* \g
* \q
% cat file
3. as indicated in the tutorial, the system should display :
a file containing the donation names
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 116
----------
4. CHOOSING THE BEST STORAGE STRUCTURES
We now turn to the issue of efficiency. Once you have created a relation and
inserted your data using either copy or append, INGRES can process any query
on the relation. There are several things you can do to improve the speed at
which INGRES can process a query.
INGRES can store a relation in three different internal structures. These are
called "heap", "isam", and "hash". First we will briefly describe each struc-
ture and then later expand our discussion.
HEAP
When a relation is first created, it is created as a "heap". There are two
important properties about a heap : duplicate tuples are not removed, and
nothing is known about the location of the tuples. If you ran the query :
range of d is donation
retrieve (d.amount) where d.name = "bill"
INGRES would have to read every tuple in the relation looking for those with
name "bill". If the relation is small this isn't a serious matter. But if the
relation is very large, this can take minutes (or even hours!).
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a DATABASE USING INGRES has appeared on the screen.
2. there are no commands to type in this lesson. So just go to the next
lesson.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 117
----------
HASH
A relation whose structure is "hash" can give fast access to searches on cer-
tain domains. (Those domains are usually referred to as "keyed domains".)
In addition, a "hashed" relation contains no duplicate tuples. For example,
suppose the donation relation is stored hashed on domain "name". Then the
query :
retrieve (d.amount) where d.name = "bill"
will run quickly since INGRES knows approximately where on disk the tuple is
stored. If the relation contains only a few tuples you won't notice the diffe-
rence between a "heap" and a "hash" stucture. But as the relation becomes
larger, the difference in speed becomes more noticeable.
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a DATABASE USING INGRES has appeared on the screen.
2. there are no commands to type in this lesson. So just go to the next
lesson.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 118
----------
ISAM
An isam structure is one where the relation is sorted on one or more domains,
(also called keyed domains). Duplicates are also removed on "isam relations".
When new tuples are appended, they are placed "approximately" in their sorted
position in the relation. (The "approximately" will be explained a bit later.)
Suppose donation is isam on name. To process the query
retrieve (d.amount) where d.name = "bill"
INGRES will determine where in the sorted order the name "bill" would be and
read only those portions of the relation.
Since the relation is approximately sorted, an isam structure is also efficient
for processing the query :
retrieve (d.amount) where d.name >= "b" and d.name < "g"
This query would retrieve all names beginning with "b" through "f". The entire
relation would not have to be searched since it is isam on name.
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a DATABASE USING INGRES has appeared on the screen.
2. there are no commands to type in this lesson. So just go to the next
lesson.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 119
----------
SPECIFYING THE STORAGE STRUCTURE
Any user created relation can be converted to any storage structure using the
"modify" command. For example :
modify donation to hash on name
or
modify donation to isam on name
or even
modify donation to heap
PRIMARY AND OVERFLOW PAGES
At this point it is necessary to introduce the concepts of primary and overflow
pages on hash and isam structures. Both hash and isam are techniques for
assigning specific tuples to specific pages on a relation based on the tuple's
keyed domains. Thus each page will contain only a certain specified subset of
the relation.
When a new tuple is appended to a hash or isam relation, INGRES first determi-
nes what page it belongs to, and then looks for room on that page. If there is
space then the tuple is placed on that page. If not, then an "overflow" page
is created and the tuple is placed there.
The overflow page is linked to the original page. The original page is called
the "primary" page. If the overflow page became full, then INGRES would connect
an overflow page to it. We would then have one primary page linked to an
overflow page, linked to another overflow page. Overflow pages are dynamically
added as needed.
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a DATABASE USING INGRES has appeared on the screen.
2. type the commands :
% ingres mydata
* modify donation to hash on name
* \g
3. as indicated in the tutorial, the system should display :
a hashed relation
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 120
----------
SPECIFYING FREE SPACE
The modify command also lets you specify how much room to leave for the rela-
tion to grow. As was mentioned in "create", relations are divided into pages.
A fillfactor can be used to specify how full to make each primary page. This
decision should be based only on whether more tuples will be appended to the
relation. For example :
modify donation to isam on name where fillfactor = 100
This tells modify to make each page 100% full if at all possible.
modify donation to isam on name where fillfactor = 25
This will leave each page 25% full or, in other words, 75% empty. We would do this if we had roughly
1/4 of the data already loaded and it was fairly well distributed about the
scisbet.
Keep in mind that if you don't specify the fillfactor, INGRES will typically
default to a reasonable choice. Also when a page becomes full, INGRES automa-
tically creates an "overflow" page so it is never the case that a relation
will be unable to expand.
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a DATABASE USING INGRES has appeared on the screen.
2. type the commands :
% ingres mydata
* modify donation to isam on name where fillfactor = 25
* \g
3. as indicated in the tutorial, the system should display :
an isam relation with fillfactor = 25
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 121
----------
When modifying a relation to hash, an additional parameter "minpages" can be
specified. Modify will guarantee that at least "minpage" primary pages will
be allocated for the relation.
Modify computes how many primary pages will be needed to store the existing
tuples at the specified fillfactor assuming that no overflow pages will be
necessary originally. If that number is less than minpages, then minpages is
used instead.
For example :
modify donation to hash on name where fillfactor = 50,
minpages = 1
modify donation to hash on name where minpages = 150
In the first case we guarantee that no more pages than are necessary will be
used for 50% occupancy. The second case is typically used for modifying an
empty or near empty relation. If the approximate maximum size of the relation
is known in advance, minpages can be used to guarantee that the relation will
have its expected maximum size.
There is one other option available for hash called "maxpages". Its syntax is
the same as minpages. It can be used to specify the maximum number of primary
pages to use.
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a DATABASE USING INGRES has appeared on the screen.
2. type the commands :
% ingres mydata
* modify donation to hash on name where minpages = 150
* \g
3. as indicated in the tutorial, the system should display :
a hashed relation with minpages = 150
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 122
----------
COMPRESSION
The three storage structures (heap, hash, isam) can optionally have
"compression" applied to them. To do this, refer to the storage structures as
cheap, chash and cisam. Compression reduces the amount of space needed to store
each tuple internally. The current compression technique is to suppress trai-
ling blanks in character domains. Using compression will never require more
space and typically it can save disk space and improve performance. Here is
an example :
modify donation to cisam on name where fillfactor = 100
This will make donation a compressed isam structure and fill every page as full
as possible. With compression, each tuple can have a different compressed
length. Thus the number of tuples that can fit on one page will depend on how
successfully they can be compressed.
Compressed relations can be more expensive to update. In particular if a repla-
ce is done on one or more domains and the compressed tuple is no longer the
same length, then INGRES must look for a new place to put the tuple.
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a DATABASE USING INGRES has appeared on the screen.
2. type the commands :
% ingres mydata
* modify donation to cisam on name where fillfactor = 100
* \g
3. as indicated in the tutorial, the system should display :
a compressed isam relation
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 123
----------
TWO VARIATIONS ON A THEME
As mentioned, duplicates are not removed from a relation stored as a heap.
Frequently it is desirable to remove duplicates and sort a heap relation.
One way of doing this is to modify the relation to isam specifying the order
in which to sort the relation. An alternative to this is to use either
"heapsort" or "cheapsort". For example
modify donation to heapsort on name, ext
This will sort the relation by name then ext. The tuples are further sorted
on the remaining domains, in the order they were listed in the original create
statement. So in this case the relation will be sorted on name then ext and
then amount. Duplicate tuples are always removed. The relation will be left
as a heap. Heapsort and cheapsort are intended for sorting a temporary relation
before printing and destroying it. It is more efficient than modifying to isam
because with isam INGRES creates a "directory" containing key information
about each page. The relation will NOT be kept sorted when further updates
occur.
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a DATABASE USING INGRES has appeared on the screen.
2. type the commands :
% ingres mydata
* modify donation to heapsort on name, ext
* \g
3. as indicated in the tutorial, the system should display :
a sorted heap relation
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 124
----------
Examples :
Here are a collection of examples and comments as to the efficiency of each
query. The queries are based on the relations :
parts (pnum, pname, color, weight, qoh)
supply (snum, pnum, jnum, shipdate, quan)
range of p is parts
range of s is supply
modify parts to hash on pnum
modify supply to hash on snum, jnum
retrieve (p.all) where p.pnum = 10
INGRES will recognize that parts is hashed on pnum and go directly to the page
where parts with number 10 would be stored.
retrieve (p.all) where p.pname = "tape drive"
INGRES will read the entire relation looking for matching pnames.
retrieve (p.all) where p.pnum < 10 and p.pnum > 5
INGRES will read the entire relation because no exact value for pnum was given.
retrieve (s.shipdate) where s.snum = 471 and s.jnum = 1008
INGRES will recognize that supply is hashed on the combination of snum and jnum
and will go directly to the correct page.
retrieve (s.shipdate) where s.snum = 471
INGRES will read the entire relation. Supply is hashed on the combination of
snum and jnum. Unless INGRES is given a unique value for both, it cannot take
advantage of the storage structure.
retrieve (p.pname. s.shipdate) where
p.pnum = s.pnum and s.snum = 471 and s.jnum = 1008
INGRES will take advantage of both storage structures. It will first find all
s.pnum and s.shipdate where s.snum = 471 and s.jnum = 1008. After that it will
look for all p.pname where p.pnum is equal to the correct value.
This example illustrates the idea that it is frequently a good idea to hash
a relation on the domains where it is "joined" with another relation.
For example, in this case it is very common to ask for p.pnum = s.pnum.
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a DATABASE USING INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* range of p is parts
* range of s is supply
* modify parts to hash on pnum
* modify supply to hash on snum, jnum
* retrieve (p.all) where p.pnum = 10
* \g
3. as indicated in the tutorial, the system should display :
since you do not own relation parts, you will have to adapt this
example to your own database.
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 125
----------
To summarize :
To take advantage of a hash structure, INGRES needs an exact value for each
key domain. An exact value is anything such as :
s.snum = 471
s.pnum = p.pnum
An exact value is not
s.snum >= 471
(s.snum = 10 or s.snum = 20)
Now let's consider some cases using isam
modify supply to isam on snum, shipdate
retrieve (s.all) where s.snum = 471
and s.shipdate > "75-12-31"
and s.shipdate < "77-01-01"
Since supply is sorted first on snum and then on shipdate, INGRES can take full
advantage of the isam structure to locate the portions of supply which satisfy
the query.
retrieve (s.all) where s.snum = 471
Unlike hash, an isam structure can still be used if only the first key is
provided.
retrieve (s.all) where s.snum > 400 and s.snum < 500
Again INGRES will take advantage of thee structure.
retrieve (s.all) where s.shipdate >= "75-12-31" and
s.shipdate < "77-01-01"
Here INGRES will read the entire relation. This is because the first key (snum)
is not provided in the query.
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a DATABASE USING INGRES has appeared on the screen.
2. type the commands :
% ingres demo
* range of p is parts
* range of s is supply
* modify supply to isam on snum, shipdate
* retrieve (s.all) where s.snum = 471
* and s.shipdate > "75-12-31"
* and s.shipdate < "77-01-01"
* \g
3. as indicated in the tutorial, the system should display :
since you do not own relation parts, you will have to adapt this
example to your own database.
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 126
----------
To summarize :
Isam can provide improved access on either exact values or ranges of values.
It is useful as long as at least the first key is provided.
To locate where the tuples are in an isam relation, INGRES searches the isam
directory for that relation. When a relation is modified to isam, the tuples
are first sorted and duplications are removed. Next, the relation is filled
(according to the fillfactor) staring at page 0, 1, 2... for as many pages
as are needed.
Now the directory is built. The key domains from the first tuple on each page
are collected and organized into a directory (stored in the relation on disk).
The directory is never changed until the next time a modify is done.
Whenever a tuple is added to the relation, the directory is searched to find
which page the new tuple belongs on.
Within that page, the individual tuples are NOT kept sorted. This is what is
meant by "approximately" sorted.
HEAP v. HASH v. ISAM
Let's now compare the relative advantages and disadvantages of each option.
A relation is always created as a heap. A heap is the most efficient structure
to use to initially fill a relation using copy or append.
Space from deleted tuples of a heap is only reused on the last page. No dupli-
cate checking is done on a heap relation.
Hash is advantageous for locating tuples referenced in a qualification by an
exact value. The primary page for tuples with a specific value can be easily
computed.
Isam is useful for both exact values and ranges of values. Since the isam
directory must be searched to locate tuples, it is never as efficient as hash.
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a DATABASE USING INGRES has appeared on the screen.
2. there are no commands to type in this lesson. So just go to the next
lesson.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 127
----------
OVERFLOW PAGES
When a tuple is to be inserted and there is no more room on the primary page
of a relation, then an overflow page is created. As more tuples are inserted,
additional overflow pages are added as needed. Overflow pages, while necessary,
decrease the system performance for retrieves and updates.
For example, let's suppose that supply is hashed on snum and has 10 primary
pages. Suppose the value snum = 3 falls on page 7. To find all snum = 3
requires INGRES to search primary page 7 and all overflow pages of page 7 (if
any). As more overflow pages are added the time needed to search for snum = 3
will increase. Since duplicates are removed on isam and hash, this search must
be performed on appends and replaces also.
When a hash or isam relation has too many overflow pages it should be remodi-
fied to hash or isam again. This will clear up the relation and eliminate as
many overflow pages as possible.
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a DATABASE USING INGRES has appeared on the screen.
2. there are no commands to type in this lesson. So just go to the next
lesson.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 128
----------
UNIQUE KEYS
When choosing key domains for a relation it is desirable to have each set of
key domains as unique as possible. For example, employee id numbers typically
have no duplicate values, while something like color is likely to have only
a few distinct values, and something like sex, to the best of our knowledge,
has only two values.
If a relation is hashed on domain sex then you can expect to have all males
on one primary page and all its overflow pages and a corresponding situation
with females. With a hash relation there is no solution to this problem.
A trade-off must be made between the most desirable key domains to use in a
qualification versus the uniqueness of the key values.
Since isam structure can be used if at least the first key is provided, extra
key domains can sometimes be added to increase uniqueness. For example, suppose
the supply relation has only 10 unique supplier numbers but thousands of
tuples. Choosing an isam structure with the keys snum and jnum will probably
give many more unique keys. However, the directory size will be larger and
consequently it will take longer to search. When providing additional keys
just for the sake of increasing uniqueness, try to use the smallest possible
domains.
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a DATABASE USING INGRES has appeared on the screen.
2. there are no commands to type in this lesson. So just go to the next
lesson.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 129
----------
SYSTEM RELATIONS
INGRES uses three relations ("relation", "attribute", and "indexes") to main-
tain and organize a data base. The "relation" relation has one tuple for each
relation in the data base. The "attribute" relation has one tuple for each
attribute in each relation. The "indexes" relation has one tuple for each
secondary index.
INGRES accesses these relations in a very well defined manner. A program called
"sysmod" should be used to modify these relations to hash on the appropriate
domains. To use sysmod the data base administrator types
% sysmod data-base-name
Sysmod should be run initially after the data base is created and subsequently
as relations are created and the data base grows. It is insufficient to run
sysmod only once and forget about it. Rerunning sysmod will cause the system
relations to be remodified. This will typically remove most overflow pages
and improve system response time for everything.
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a DATABASE USING INGRES has appeared on the screen.
2. type the commands :
% sysmod mydata
3. as indicated in the tutorial, the system should display :
an improved database
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 130
----------
5. SECONDARY INDICES
Using an isam or hash structure provides a fast way to find tuples in a rela-
tion given values for the key domains. Sometimes this is not enough. For ex-
ample, suppose we have the donation relation
donation (name, amount, ext)
hashed on name. This will provide fast access to queries where the qualifica-
tion has an exact value for name. What if we also will be doing queries giving
exact values for ext?
Donation can be hashed either on name or ext, so we would have to choose which
is more common and hash donation on that domain. The other domain (say ext)
can have a secondary index. A secondary index is a relation which contains
each "ext" together with the exact location of where the tuple is in the
relation donation.
The command to create a secondary index is :
index on donation is donext (ext)
The general format is :
index on relation name is secondary index name (domains)
Here we are asking INGRES to create a secondary index on the relation donation.
The domain being indexed is "ext". Indices are formed in three steps :
1. "Donext" is created as a heap.
2. For each tuple in donation, a tuple is inserted in "donext" with the value
for ext and the exact location of the corresponding tuple in donation.
3. By default "donext" is modified to isam.
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a DATABASE USING INGRES has appeared on the screen.
2. type the commands :
% ingres mydata
* index on donation is donext (ext)
* \g
3. as indicated in the tutorial, the system should display :
an indexed relation
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 131
----------
Now if you run the query
range of d is donation
retrieve (d.amount) where d.ext = 207
INGRES will automatically look first in "donext" to find ext = 207. When it
finds one it then goes directly to the tuple in the donation relation. Since
"donext" is isam on ext, search for ext = 207 can typically be done rapidly.
If you run the query
retrieve (d.amount) where d.name = "frank"
then INGRES will continue to use the hash structure of the relation "donation"
to locate the qualifying tuples.
Since secondary indices are themselves relations, they also can be either hash,
isam, chash or cisam. It never makes sense to a secondary index a heap.
The decision as to what structure to make them on involves the same issues as
were discussebefore :
Will the domains beferenced by exact value?
Wiley be referenced by ranges ovalue?
etc.
In this case text" domain will be referenceb exact values, and since
the relation is nearly full we will do :
modify donext to hash on ext where fillfactor = 100
and minpages = 1
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a DATABASE USING INGRES has appeared on the screen.
2. type the commands :
% ingres mydata
* range of d is donation
* retrieve (d.amount) where d.ext = 204
* \g
3. as indicated in the tutorial, the system should display :
a domain of the donation relation
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 132
----------
Secondary indices provide a way for INGRES to access tuples based on do never makes sense ains
that are not key domains. A relation can have any number of secondary indices
and in addition each secondary index can be an index up to six domains of the
primary relation.
Whenever a tuple is replaced, deleted or appended to a primary relation, all
secondary indices must also be updated. Thus secondary indices are "not free".
They increase the cost of updating the primary relation, but can decrease the
cost of finding tuples in the primary relation.
Whether a secondary index will improve performance or not strongly depends on
the uniqueness of the values of the domains being indexed. The primary concern
is whether searching through the secondary key is more efficient than simply
reading the entire primary relation. In general it is if the number of tuples
which satisfy the qualification is less than the number of total pages (both
primary and overflow) in the primary relation.
For example if we frequently want to find all people who donated less than
five dollars, consider creating
index on donation is donamount (amount)
By default donamount will be isam on amount. If INGRES processes the query :
retrieve (d.name) where d.amount < 5.0
it will locate d.amount < 5.0 in the secondary index and for each tuple it
finds will fetch the corresponding tuple in donation. The tuples in donamount
are sorted by amount but the tuples in donation are not. Thus in general each
tuple fetch from donation via donamount will be on a different page.
Retrieval using the secondary index can then cause more page reads than simply
reading all of donation sequentially. So in this example it would be a bad idea
to create a secondary index.
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a DATABASE USING INGRES has appeared on the screen.
2. type the commands :
% ingres mydata
* range of d is donation
* index on donation is donamount (amount)
* retrieve (d.name) where d.amount < 5.0
* \g
3. as indicated in the tutorial, the system should display :
a domain of the donation relation
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 133
----------
6. RECOVERY AND DATA UPDATE
INGRES has been carefully designed to protect the integrity of a data base
against certain classes of system failures. To do this INGRES processes changes
to a relation using what we call "deferred update" or "batch file update".
In addition there are two INGRES programs "restore" and "purge" that can be
used tondheck out a data base after a system failure. We will first discuss
how deferred updates are created and processed, and second we will discuss the
use of purge and restore.
DEFERRED UPDATE (Batch update)
An append, replace or delete command is run in four steps :
1. An empty batch file is created.
2. The command is run to completion and each change to the result relation
is written into the batch file.
3. The batch file is read and the relation and its secondary indices (if any)
are actually updated.
4. The batch file is destroyed and INGRES returns back to the user.
Deferred update defers all actual updating until the very end of the query.
There are three advantages to doing this.
1. Provides recovery from system failures.
If the system "crashes" during an update, the INGRES recovery program will
decide to either run the update to completion or else "back out" the update,
leaving the relation as it looked before the update was started.
2. Prevents infinite queries.
If "donation" were a heap and the query
range of d is donation
append to donation (d.all)
were run without deferred update, it would terminate only when it ran out of
space on disk! This is because INGRES would start reading the relation from
the beginning and appending each tuple at the end. It would soon start reading
the tuples it had just previously appended and continue indefinitely to "chase
its tail".
While this query is certainly not typical, it illustrates the point. There are cert
certain classes of queries where problems occur if WHEN an update actually
occurs is not precisely defined. With deferred update we can guarantee
consistent and logical results.
3. Speeds up processing of secondary indices
Secondary indices can be updated faster if they are done one at a time instead
of all at once. It also insures protection against the secondary index becoming
inconsistent with its primary relation.
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a DATABASE USING INGRES has appeared on the screen.
2. there are no commands to type in this lesson. So just go to the next
lesson.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 134
----------
TURNING DEFERRED DATA OFF
If you are not persuaded by any of these arguments, INGRES allows you to turn
deferred data off! Indeed there are certain cases when it is appropriate
(although certainly not essentiel) to perform updates directly, that is, the
relation is updated while the query is being processed.
To use direct update, you must be given permission by the INGRES super user.
Then when invoking INGRES specify the "-b" flag which turns off batch update.
% ingres mydate -b
INGRES will use directly update on any relation without secondary indices. It
will still silently use deferred update if a relation has any secondary indi-
ces. By using the "-b" flag you are sacrificing points one and two above. In
most cases you SHOULD NOT use the -b flag.
If you are using INGRES to interactively enter or change one tuple at a time,
it is slightly more efficient to have deferred update turned off.
If the system crashes during an update the person entering the data will be
aware of the situation and can check whether the tuple was updated or not.
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a DATABASE USING INGRES has appeared on the screen.
2. type the commands :
% ingres mydata -b
3. as indicated in the tutorial, the system should display :
deferred data turned of
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 135
----------
RESTORE
INGRES is designed to recover from the common types of system crashes which
leave the Unix file system intact. It can recover from updates, creates,
destroys and index commands.
INGRES is designed to "fail safe". If any inconsistancies are discovered or any
failures are returned from Unix, INGRES will generate a system error message
(SYSERR) and exit.
Whenever Unix crashes while INGRES is running or whenever an INGRES syserr
occurs, it is generally a good idea to have the data base administrator run
the command.
% restore data base name
The restore program performs the following functions :
1. Looks for batch update files. If any are found, it examines each one to see
if it is complete. If the system crash occurs while the batch file was being
read and the data base being updated, then restore will complete the update.
Otherwise the batchfile was not completed and it is simply destroyed; the
effect is as though the query had never been run.
2. Checks for uncompleted modify commands. This step is crucial. It guarantees
that you will either have the relation as it existed before the modify, or
restore will complete the modify command. Modify works by creating a new copy
of the relation in the new structure. Then when it is ready to replace the old
relation, it stores the new information in a "modify batch file". This enables
restore to determine the state of uncompleted modifies.
3. Checks consistency of system relations. This check is used to complete
"destrory" commands, back out "create" commands, and back out or complete
"index" commands that were interrupted by a system crash.
4. Purges temporary relations and files. Restore executes the "purge" program
to remove temporary relations and temporary files created by the system.
Purge will be discussed in more detail a bit later.
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a DATABASE USING INGRES has appeared on the screen.
2. type the commands :
% restore my data
3. as indicated in the tutorial, the system should display :
a restored database (after a failure)
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 136
----------
Restore cannot tell the user which queries have run and which have not. It can
only identify those queries which were in the process of being run when the
crash occurred. When batching queries together, it is a good idea to save the
output in a file. By having a monitor print out each query or set of queries,
the user can later identify which queries were run.
Restore has several options to increase its usability. They are specified by
"flags". The options include :
-a ask before doing anything
-f passed to purge. used to remove temporary files.
-p passed to purge. used to destroy expired relations.
no data base restores all data bases for which you are the dba.
Of these options the "-a" is the most important. It can happen that a Unix
crash can cause a page of the system catalogues to be incorrect. This might
cause restore to destroy a relation. In fact, you might want to "patch" the
system relations to correct the problem. No restore program can account for
all possibilities. It is therefore no replacement (fortunately) for a human.
If "-a" is specified, restore will state what it wants to do and then ask for
permission. It reads standard input and accepts "y" to mean go ahead and any-
thing else to mean no. For example, to have restore ask you before doing
anything
restore -a mydatabase
To have it take "no" for all its questions.
restore -a mydatabase </dev/null
Using the -a flag, restore might ask for permission to perform some cleanup;
for example, if it finds an attribute for which there is no corresponding
relation, or if it finds a secondary index for which there is no primary
relation, etc.
To date, we have never had a system crash which INGRES could not recover from.
This does not mean that it will never happen, but rather that it shouldn't be
too great a concern for you. It should be mentioned that restore is not a
substitution for doing periodic backing up, nor does it ever perform such a
function.
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a DATABASE USING INGRES has appeared on the screen.
2. type the commands :
% restore -a mydata
3. as indicated in the tutorial, the system should display :
a restored database with options being asked
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================
LESSON# 137
----------
PURGE
Purge can be used to report expired relations, destroy temporary systems rela-
tions, remove extraneous fields, and destroy expired relations. To use purge
must be the DBA for the data base.
% purge mydatabase
Purge has several options which are specified by flags which are worth noting :
-f (default is off) remove all extraneous files. Each file is reported and
then removed. If "-f" is not specified then the file is only reported.
-p (default is off) destroy all expired relations. Each expired relation is
reported and if "-p" was specified the relation is destroyed.
Purge always destroys relations and files which are known to be INGRES system
temporaries. When processsing multi-variable queries and queries with aggre-
gate functions, INGRES will usually create temporary relations with interme-
diate results. These relations always begin with the characters "_SYS".
Other INGRES commands create temporary files which also begin with "_SYS".
Under normal processing they are always destroyed. If a system crash occurs,
they might be left. Purge will always clean up the temporary system files.
It cleans up the user's relations only when specifically asked to.
----- TYPE CONTROL-D TO CONTINUE -----
Execute the following steps :
1. first, the next part of a DATABASE USING INGRES has appeared on the screen.
2. type the commands :
% purge mydata
3. as indicated in the tutorial, the system should display :
a purged database
4. type "\q" to exit the ingres database.
to get to the next lesson :
----- type "les" followed by the lesson number -----
(Don't forget a space between "les" and the lesson number)
=========================================