RELATIONAL CALCULUS: Based on 1st order predicate calculus.
In Relational Calculus, we write declarative expressions to specify queries
- secify "what" not how (non-procedural). involves "variables'.
RELATIONAL CALCULUS (tuple version)
- specify a number of "tuple variables" each ranging over a relation
- {t | Condition(t) } = all tuples, t, satisfying Condition(t) (selection query)
- { t.A1,...,t.An | condition(t) } includes projection on attributes A1,..,An
- {t1.A1,..tn.An | condition(t1,..tn,tn+1,..tn+m} (most general form)
ti = tuple-variables
Ai = attributes of relation(s) on which ti ranges
condition = well-formed-formula (wff).
ATOMS (building blocks of all wffs):
R(ti), ti ranges over R (membership atom)
ti.A op tj.B, ti.A op c, c op tj.B "op" is: =, !=, <, >, <=, >= (evaluate to t/f)
Atoms are wffs.
If F1 and F2 are wff, the following are wff's
F1 and F2, F1 or F2, not(F)
(EXITS t)(F1) true iff there is >=1 tuple in range(t) with F1 true)
(FORALL t)(F1) true iff F1 true for all tuples in range(t)
Free and Bound (or quantified) variables:
1. Within an atom, all tuple variables are FREE.
2. Tuple variable occurrences in NOT f, are free(bound) if
they are free(bound) in f.
Tuple variable occurrences in f AND g, f OR g
are free(bound) if they are free(bound) in f or in g.
3. Occurrences of T that are free in f are bound in formulas:
EXISTS T (f), FORALL T (f)
Other tuple variable occurrences in f are free/bound
in formulas involving f as they are free/bound in f.
4. Note that formula: IF f THEN g is defined precisely
equivalent to (NOT f) OR g
truth table proof:
f g f=>g NOT-f (NOT-f)or(g)
---- ---- ---- ---- ----
T T T F T
T F F F F
F T T T T
F F T T T
A Query language is RELATIONALLY COMPLETE if it is as powerful as
relational calculus. (permits definition of any relation definable
by expressions of relational calculus).
Relational Algebra (relation manipulations using logical
combinations of the binary operators, UNION, DIFFERENCE, PRODUCT,
and the unary operators, SELECTION, PROJECTION) is relationally complete
(Relational completeness can be regarded as a basic measure of
selective or expressive power for database query languages in general).
**********************************************
QUEL AND QBE (two database languages based on relational calculus)
(Note: SQL based on relational algebra)
INGRES (Interactive Graphics and REtrieval System)
* relational data model
* Multiuser, multitasking DBMS
* uses the UNIX file system
* QUEL nonprocedural data sublang.
- build QUEL programs in "workspace"
- execute "workspace" program
* Query-By-Forms (QBF) forms-oriented insert, update, delete
* Report-By-Forms (RBF) and Report Writer, forms-oriented report gen
* VIsual FoRms EDitor (VIFRED), facility for creating custom forms
* EQUEL C-language interface precompiler converts QUEL commands to C
INGRES USER-INTERFACE, DATA DEFINITION
create database from UNIX using createdb declare relations from QUEL with create.
| login
UNIX__V________.
. - -|-createdb educ |
/usr/Ingres < - -|-destroy educ |
` - - -|-createdb educ |
|_______________|
|$ ingres educ
_INGRES____________V_____
* under the control of the Ingres
Terminal Monitor (user interface prog)
{edit}{home}{right}{del}~
{down}/xg\D~
| login
UNIX__V_______
|vi enroll_data| - - ->$HOME/enroll_data
|vi stu_data---|> $HOME/stu_data |
|______________| | |
| 25CLAY OUTBK 32,8,89
| 32THAISZKNOB 32,7,91
ingres | 38GOOD GATER 25,7,68
educ| 17BAID NY 25,6,7
| 57BROWN NY 32,6,62
| | 8,6,98
/ing | | 17,5,96
^ | - - . |
| _____V_____________________ V
|<|-copy student (num=c2,nam=c6,city=c5)
| | from "$HOME/stu_data \g
| | |
|-.
| | |
`<|- - - - - - report7|CLAY|99|<- - '
| |_______|
|_______________________
INGRES QUEL REPORT UPDATE DELETE INSERT
/usr/Ingres
^
| | $ ingres educ
| _INGRES-QUEL_______V______________
| | range of e is enroll
|>|-retrieve (student.nam,e.grade) where
| | student.num = e.snum and e.cnum = 5
| | \g - - - - - - - .
| | |_BAID_|_96_|
|<|-replace enroll (grade=100) where
| | e.cnum = 7 \g
|<|-delete enroll where e.cnum=7 or
| | e.cnum=6 \g
| | print enroll - ->ENROLL |32|8|89|
| | |17|5|96|
| | |25|5|99|
| | `-------'
| |
|>| select (mean =
| | sum(e.grade)/count(e.snum))
| | \g->_mean_
| | |__94__|
|>| retrieve (nc=countu(e.cnum)) \g
| | __nc_
| | |__2__|
`>| retrieve (numb=e.cnum, mean=avg
| (e.grade by e.cnum where
| e.grade>75))\g numb_mean
| | 8 | 89 |
| |_5_|_98_|
|
.<|-copy enroll () into
| | "/usr/staff/perrizo/enroll \g
| |_________________________________
| ._enroll_.
V |32| 8|89|
$ lpr enroll - - - - -> |17| 5|96|
|25| 5|99|
INGRES USER INTERFACE, STORAGE STRUCTURE
| | $ ingres educ
| _INGRES-QUEL_______V______________
|<|-modify enroll to heapsort on
| | grade:descending
| |
|<|-modify student to hash on nam \g
| |
|<|-index on student is numindex (num)\g
| | ________
| | print numindex \g - - ->|17 |2048|
| | |25 |1025|
`<|-modify report7 to isam |32 | 0|
| on grade \g |38 |2560|
|_________________________ |57_|1024|
QUERY BY FORMS (QBF)
* Visual, forms-driven DML-interface to
Ingres
* Performs three basic Ingres-DML funcns
-APPEND
-UPDATE(replace, delete)
-RETRIEVE
* Menu driven user-interface
* qbf [flags] dbname tablename
flags (circumvent main menu)
-a enter append function directly
-u enter update function directly
-r enter retrieve function directly
-i run qbf in INSERT mode (default)
-e run qbf in EDIT mode (fancy)
-s silent - print NONverbose messages
-p prompt for missing arguments
- no flags calls for main qbf menu
QBF SESSION
/usr/Ingres | $ ingres educ
^ _INGRES________V___________________
`-<-|-create course (num=i1, nam=c6,
| loc=c2) \g \q |
-----------------------------------
| $ qbf -a educ course
__QBF__________V___________________
| TABLE IS: course |
| |
| num:_ nam: loc:|
| |
|APPEND #1 control-F add, ret|
-----------------------------------
* alter above "default" arrangement
using VIFRED
or ctrl/N move to next field
ctrl/P move back to previous field
move to next field clearing
right of cursor
call append menu (HELP ADD END
ctrl/W redraw screen
arrows (or H J K L) nondestructive
cursor movement
ctrl/X clear current field
ctrl/D clear from cursor to end of fld
ctrl/A duplicate field from prev form
ctrl/E toggle INSERT (default)/EDIT
(fancier) modes
INITIALIZE A RELATION USING QBF
_____________QBF____________________
| TABLE IS| course |
| num: 8 nam: 3BSEX loc: ND |
| |
|APPEND #1 ctrl-F to add, esc ret) |
------------------------------------
.<- - - - -< ctrl/F |
| _______________V__________________
| | TABLE IS| course |
| | num: 7 nam: CUS loc: ND
| | |
| |APPEND #2 ctrl-F to add, esc to ret
| ----------------------------------
|<- - - - -< ctrl/F |
| _______________V___________________
| | TABLE IS| course |
| | num: 6 nam: 3UA loc: NJ
| | |
| |APPEND #3 ctrl-F to add, esc to ret
|<- - - - -< ctrl/F |
| _______________V___________________
| | TABLE IS| course |
| | num: 5 nam: 3UA loc: ND
| | |
| |APPEND #4 ctrl-F to add, esc to ret
| -----------------------------------
|<- - - - -< ctrl/F |
| esc __course_____
| END |num| nam |loc|
/usr/Ingres/data/default |8 |3BSEX|ND |
|7 |CUS |ND |
|6 |3UA |NJ |
|5__|3UA__|ND_|
UPDATING TUPLES USING THE QBF MENU
The UPDATE qbf function:
* enter UPDATE using -u flag or through main menu
* edit data already in table on row-by-row basis
* delete selected rows using forms
* faster than QUEL for changing many individual errors
* 2 states: QUERY (default) specify query (fill form) GO update matching rows one at a time
| login
| $ qbf educ course
V
QUERY BY FORMS
FUNCTION:
HELP - Help on various subjects in QBF
APPEND - The DATA ENTRY portion of QBF
UPDATE - For changing and deleting rows
RETRIEVE-Fctn of QBF for viewing rows
EXIT - Leave QBF and return to UNIX
HLP UPDT RET APPEND TABLE EXIT : u
|
_QBF_UPDATE____V_____________________
| TABLE IS: course
| num: 6 nam: loc:
| ENTER QUERY esc to return or to run
-------------------------------------
|
_______________V_____________________
| TABLE IS: course
| num: 6 nam: loc:
| HELP QUERY GO END : G
-------------------------------------
|
_______________V_____________________
| TABLE IS: course
| num: 6 nam: 3UA loc: NJ <- NY
|TYPE NEW DATA ESC return, ctrl-F next
--------------------------------------
ctrl-F |(puts update in buffer)
| NO MORE ROWS IN QUERY
_______________V______________________
| TABLE IS: course
| num: 6 nam: 3UA loc: NY
| HELP QUERY WRITE END : W
--------------------------------------
2
_______________V_____________________
| TABLE IS: course
| num: 6 nam: loc:
| ENTER QUERY esc to return or to run
-------------------------------------
RETRIEVING TUPLES USING QBF
The RETRIEVE function:
* enter RETRIEVE using -r flag or main menu
* retrieve specified rows of table
* 2 states: QUERY (default) specify query (fill form; identical to that of APPEND)
GO retrieves tuples
| login
| $ qbf educ course
V
QUERY BY FORMS
FUNCTION:
HELP-Help on various subjects in QBF
APPEND - The DATA ENTRY portion of QBF
UPDATE - For changing and deleting rows
RETRIEVE-Function of QBF to view rows
EXIT - Leave QBF and return to UNIX
HLP UPDT RET APPEND TABLE EXIT : r
_QBF_RETRIEVE__V_____________________
| TABLE IS: course
| num: nam:3UA loc:
| ENTER QUERY esc to return or to run
-------------------------------------
|
_______________V_____________________
| TABLE IS: course
| num: 6 nam:3UA loc:NY
|BROWSE DATA esc ret, ctrl-F next row
-------------------------------------
| ctrl-F
_______________V_____________________
| TABLE IS: course
| num: 5 nam:3UA loc:ND
|BROWSE DATA esc ret, ctrl-F next row
-------------------------------------
| ctrl-F
_______________V_____________________
| TABLE IS: course
| num: 4 nam:3UA loc:CO
|BROWSE DATA esc ret, ctrl-F next row
-------------------------------------
| ctrl-F
_______________V_____________________
| TABLE IS: course
| num: 4 nam:3UA loc:CO
| NO MORE ROWS FOUND esc to return
-------------------------------------
| esc
_______________V_____________________
| TABLE IS: course
| num: nam: loc:
| HELP QUERY END : E
-------------------------------------
INGRES Report Writer
* generate reports from compiled report specifications.
* REPORT retrieves, sorts, formats & writes data.
Invoke: report db-name rel-name [-mstyle] style is column, block or
wrap (default determined by width of columns in table being reported)
* relation-name can be a view
* Column-style report: (default, if columns fit)
19-SEP-1991
Report on Table: student
Num Nam City
17 BAID NY
25 CLAY OUTBK
32 THAISZ KNOB
38 GOOD GATER
57 BROWN NY
63 PELSON KNOB
BLOCK REPORTS block-style report: (default, if columns don't fit)
19-SEP-1991 02:24:28
Report on Table: whol9yds
Stunum: 17 Stunam: BAID City: NY
State: NY Corsnum: 5 Corsnam: 3UA
Loc: ND Grade: 96 Age: 68
Wt: 137 Ht: 71 Hair: grey
Eyes: blue
Stunum: 25 Stunam: CLAY City: OUTBK
State: NJ Corsnum: 6 Corsnam: 3UA
Loc: NJ Grade: 76 Age: 54
Wt: 185 Ht: 68 Hair: red
Eyes: blue
Stunum: 25 Stunam: CLAY City: OUTBK
State: NJ Corsnum: 7 Corsnam: CUS
Loc: ND Grade: 100 Age: 54
Wt: 185 Ht: 68 Hair: red
Eyes: blue
.
.
.
WRAP-STYLE REPORTS
* wrap-style reports (not default) called for by report database_name relation_name -mwrap
19-SEP-1991 02:26:18
Report on Table: whol9yds
Stunum Stunam City State Corsnum Corsnam
Loc Grade Age Wt Ht Hair Eyes
17 BAID NY NY 5 3UA
ND 96 68 137 71 grey blue
25 CLAY OUTBK NJ 6 3UA
NJ 76 54 185 68 red blue
25 CLAY OUTBK NJ 7 CUS
ND 100 54 185 68 red blue
32 THAISZ KNOB NJ 6 3UA
NJ 62 49 164 73 brown brown
. . .
REPORT-BY-FORMS (RBF)
* Forms-gen: edit default report specs
1. set up formatting commands for rpts
2. write out a UNIX file containing cmds
3. use SREPORT to compile report into db
4. use REPORT command to generate report
* What is a form? fields and trim
- fields (display of data values) and trim (display textual output)
* What can be edited? Title, column headings, data on detail line
* a Report-By-Forms session:
$ rbf educ
____________________________________
|REPORT BY FORMS |
|HELP__CATALOGS__REPORT_TABLE_EXIT:_T_|
|name: ENROLL
|
V
_______________________________________
|Editing Report: enroll |
|Hlp_Layout_Opt_Rest_Struc_Write_Ex:_S_|
V
CHANGING REPORT STRUCTURE
Hlp Layout Opts Rest Struc Write Ex: S
V
_______________________________________
| Report Structure Form |
|Column-name sort-seq sort-order brk-col
|=========== ======== ========== ======|
|snum 1 -. a y
|cnum 0 -. |
|grade 0 | |
|Help__ColumnOptions__End:_C<'_________|
V
__________________________________
| Column Options Form |-.
. . .
. . esc
. . .
|aggregate over-rpt ovr-br over-pg| .
|count | .
|sum | .
|average x | .
|minimum | .
|maximum | .
|Help__End:_E______________________|<-'
|
|
|
|
|
V
_______________________________________
| Report Structure Form --esc.
|_Help__ColumnOptions__End:_E_<- - - - '
V
______________________________________
| Editing Report: enroll |
|Hlp_Layout_Opt_Rest_Struc_Write_Ex:___|
CHANGING REPORT LAYOUT Add rpt heading
______________________________________
| Editing Report: enroll |
|Hlp_Layout_Opt_Rest_Struc_Write_Ex:_L_|
|
|
|
|
|
V
______________________________________
| ------------------------Title--------|
| _ |
| -------------------Column-Headings-- |
| snum cnum grade |
| --------------------Detail-lines-----|
| -------------------------EOF---------|
|_Help_Dele__Edit.._Move_Trim_Undo_Ed_:|
V
______________________________________
| ------------------------Title--------|
| REPORT OF GRADES |
| -------------------Column-Headings-- |
| snum cnum grade |
| --------------------Detail-lines-----|
| -------------------------EOF---------|
RBF MOVING COLUMNS
Hlp..Hding Insert Move Trim Undo End: M
|
__________________________V___________
| ------------------------Title--------|
| REPORT OF GRADES |
| -------------------Column-Headings-- |
| snum cnum _grade |
| --------------------Detail-lines-----|
| -------------------------EOF---------|
|Ctr_Col_Left_Place_Rt_Shift_Undo:_Co_:
|
____________V_________________________
| ------------------------Title--------|
| REPORT OF GRADES |
| -------------------Column-Headings---|
| snum cnum grade _ |
| --------------------Detail-lines-----|
| -------------------------EOF---------|
|Ctr_Col_Left_Place_Rt_Shift_Undo:_P__ :
|
_______________________V______________
| ------------------------Title--------|
| REPORT OF GRADES |
| -------------------Column-Headings---|
| snum cnum grade|
| --------------------Detail-lines-----|
| i_____ i_____ i____|
| -------------------------EOF---------|
|_Help_Dele__Edit.._Move_Trim_Undo_End:|
RBF SAVING REPORT SPECIFICATIONS
Hlp Del Edit . . Move Trim Undo End: En
|
__________________________________V___
| Editing Report: enroll |
|Hlp_Layt_Opts_Restrt_Struc_Wrt_Ext:_W_|
|
____________________________________V_
| Saving Report Form |
| Report Name: enroll <- to enrollavg |
|_Help__Save__End:_S___________________|
|
________V_____________________________
| Editing Report: enroll |
|Hlp_Layt_Opts_Restrt_Struc_Wrt_Ext:___|
RBF COPYING REPORT SPECS IN YOUR DIR
______________________________________
| Editing Report: enroll |
|Hlp_Layt_Opts_Restrt_Struc_Wrt_Ext:_R_|
|
_____________________V________________
| REPORT BY FORMS |
|_HELP__CATALOGUES_REPORT_TBL_EXIT:_C_|
|
_____________________V________________
| INGRES REPORTS CATALOG:
|.-------------------------------------.
| | NAME | OWNER | RBF?|LAST CHANGED|
| |=========|=======|=====|============|
| |enrollavg|perrizo| yes 1-OCT-91 2:08|
| -------------------------------------
|Hlp_ChNam_DelRpt_EdRpt_FileRpt_End:_F_
|
enter name of file| enrollavg
V
saved as UNIX file: /usr/staff/perrizo/enrollavg.rw
|Hlp_ChNam_DelRpt_EdRpt_FileRpt_End:_En
|
______________________________V______
| REPORT BY FORMS |
|_HELP__CATALOGUES_REPORT_TBL_EXIT:_E_|
V
$ cat enrollavg.rw
THE REPORT - ENROLLAVG
29-SEP-1991 00:34:58
REPORT OF GRADES
Snum Cnum Grade
17 5 96
---------------------------------------
Totals: 17
Avg: 96
----------------------------------------
25 6 76
7 100
----------------------------------------
Totals: 25
Avg: 88
----------------------------------------
32 6 62
7 100
8 89
----------------------------------------
Totals: 32
Avg: 84
----------------------------------------
38 6 98
----------------------------------------
Totals: 38
Avg: 98
----------------------------------------
INGRES USER-INTERFACE, COMMAND FILES
| login
UNIX__V________.
| vi ing_script-|
| `-.
| `-> $HOME/ing_script
| -----------------------------
| |ret (mean=avg(enroll.grade))|
| |ret (nc=countu(enroll.cnum))|
| ----------------------------
/usr/ | |
Ingres | $ ingres educ |
| _INGRES-QUEL______________________
| | i ing_script \g< - - - - - ' |
| | | |
| V V
| .__mean__. .__nc__.
| | 94 | | 2 |
| -------- ------
INGRES USER INTERFACE, USER VIEWS
| login
UNIX__V________.
/usr/Ingres | |
^ |_______________|
| |
| | $ ingres educ
| V
| ---INGRES-QUEL-------------------
| | |
| | range of s is student |
| | range of e is enroll |
| | |
|-<-|-define view readumnweep
| |(e.snum, s.city, e.grade) where
| |e.snum=s.num \g
| | retrieve
`->-|(readumnweep.all)\g ->|32|KNOB |89|
| |17|NY |96|
| |25|OUTBK|99|
|__________________________________|
INGRES DATA MODEL, DATA DESIGN
* Ingres employs relational data model
- QUEL is based on relational calculus
design information is collected in system relations and is accessed and
manipulated identically to user rels
* Redesign schema is simple process (e.g., add a column)
- use "retrieve into" to create new relation under new name
- destroy old relation
- use "retrieve into" to move new relation to old name
QUERY-BY-EXAMPLE (QBE)
IBM product. Found in modified and simplified form in Lotus 123 and
Symphony. (very restricted since lotus & symphony are 1-file dbms's)
Fill in templates (pictures of tables)
Don't have to remember field names (presented on the screen for you)
No rigid syntax
User constructs "example" related to the query by filling in screens with values and simple symbols.
- leading underscore denotes "example" (domain var tying refs together)
- other entries denote constants
- P. prefix signifies "print" or "present" that quantity as output.
- P.AO(1). prefix = print "ascending" (1 indicates major sequence)
- P.DO(2). prefix = print "descending" (2 indicates minor sequence)
- "condition box" (1 column headed "CONDITION") allows specifcation
of any complex condition involving comparisons, AND, OR, NOT)
EXAMPLES Using 3 relations:
S (S#, SNAME, STATUS, CITY) suppliers
P (P#, PNAME, COLOR, WEIGHT, CITY) parts
SP (S#, P#, QTY) shipments
Get S# and STATUS for suppliers in Paris
S S# SNAME STATUS CITY
----|---------|---------|---------|-----
P.AO(2). P.DO(1). Paris
If all columns are to be printed:
S S# SNAME STATUS CITY
----|---------|---------|-------|-------
P.AO(2). P. P.DO(1).P.Paris
or
S S# SNAME STATUS CITY
----|---------|---------|---------|-----
P. AO(2). DO(1). Paris
Get S# of suppliers in Paris & STATUS>20
S S# SNAME STATUS CITY
----|---------|---------|---------|-----
P. >20 Paris
Get S# of supplers in Paris or STATUS>20
S S# SNAME STATUS CITY
----|---------|---------|---------|-----
P. Paris
P. >20
or use condition box (easy when complex)
S S# SNAME STATUS CITY
----|---------|---------|---------|-----
P. _ST _SC
CONDITIONS
|------------------------|
_SC = Paris OR _ST > 20
Get P#'s with WEIGHT in [16,19]
P P# PNAM COLOR WT WT CITY
----|----|----|----|----|----|-----
P. >=16 <=19
Get P#'s & WT in grams.
P P# WEIGHT
----|----|------|-------------|---------
P. _PW P. 'Wt grams' P._PW*454
Get S#, P# suppliers and parts colocated
P P# CITY
----|----|----|
_PX _CX
S S# CITY
----|----|----|
_SX _CX
----|----|----|
P. _SX _PX
Get all pairs of S# such that suppliers
colocated
S S# CITY
----|----|----|
_SX _CX
_SY _CX
----|----|----|
P. _SX _SY
To eliminate duplicates:
S S# CITY
----|----|----|
_SX _CX
_SY _CX
----|----|----|
P. _SX _SY
CONDITIONS
|-----------|
_SX < _SY
Get SNAME for suppliers who supply part
P2.
S S# SNAME
----|----|-----|
_SX P.
SP S# P#
----|----|----|
_SX P2
Get P# either WT>16 or supplied by S2
or both.
P P# WT
----|----|-----|
_PX > 16
SP S# P#
----|----|----|
S2 _PY
----|----|
P. _PX
P. _PY
For each part supplied in an average quantity greater than 200, get P# and total qty. (G. for grouped by)
SP S# P# QTY
----|----|----|---------
P.G. P.SUM._QP
CONDITIONS
|-----------|
AVG._QP>200
Change color of P2 to red, increase its wieght by 5, set its city to null. (U. is the update operator)
P P# PNAM COLOR WT WT CITY
----|----|----|-----|----|--------|-----
P2 U.red _WT U._WT+5 U.NUL
Set SP.QTY = 0 for all suppliers in NY
S S# QTY
----|----|-----|
_SX NY
SP S# P#
----|----|----|
_SX U.0
Delete all suppliers in NY (D. deletes)
S S# SNAME STATUS CITY
----|---------|---------|---------|-----
D. NY
Insert part record P7 (city='NY', WT=24, COLOR=n/a. (I. inserts)
P P# PNAME COLOR WEIGHT CITY
----|----|-----|--------|---------|-----
I. P7 24 NY