Figure Ref-1. A semantic schema for a university application.
Figure Ref-2. A relational schema for the university
application.
Many languages, similar to one of the two languages studied here, with some syntactic variations, are in use in different database models and different database management systems. The purpose of the presentation here is to delineate the common denominators of the classes of languages, while avoiding the technical details specific to particular systems.
Gender: the pronoun he includes she and the pronoun she includes he.
2.2. Fourth-Generation Programming
2.2.1. Principles
Programming languages which exhibit well-structured flow of control,
elaborate typing, and a high degree of machine independence are called
third-generation programming languages. An example of such a language
is Pascal. In this section, we shall use the example of Pascal to
introduce the principles of fourth-generation database manipulation
extensions of third-generation programming languages.
The essence of the fourth-generation data manipulation languages is
the structured access to the database. This is contrasted with
earlier data manipulation languages, which provided no automatic loops
to process bulks of information in the database but only single
commands to access one item at a time. As a result, the programmer
was left with the responsibility of "navigating" between different
data items in the database.
The principal instruction of the language extension to be introduced
is the for loop, whose body is executed for every object which is
present in the instantaneous database (when the program is run) and
satisfies conditions given in the for statement.
Example 2-2.
(* Print the name of every instructor, that is, of every
object in the category INSTRUCTOR. *)
for instructor in INSTRUCTOR do
(* Print the name of the current instructor, that
is, of the object referred to by the variable
instructor. Separate the current name by a blank ` '
from the name printed in the previous iteration of
this loop.*)
write (` ', instructor. LAST-NAME)
Example 2-3.
(* Print the name of every Computer Science instructor, that
is, of every object in the category INSTRUCTOR who WORKS-IN
the department whose name is `Computer Science'.*)
for department in DEPARTMENT
satisfying (department NAME `Computer Science')
do
for instructor in INSTRUCTOR
satisfying (instructor WORKS-IN
department)
do
(* Print the name of the current
instructor, that is, of the
object referred to by the
variable instructor *)
write (` ', instructor. LAST-
NAME)
2.2.2. Specification of Extended Pascal
The following is a fourth-generation extension of Pascal for
structured access to databases.
2.2.2.1. Data types and parameters
1. Global parameters - among the global parameters of a program such
as INPUT and OUTPUT, there are the names of the database and of
the userview. The database will be accessed through the userview
during the execution of the program. The userview will also be
accessed during the compilation of the program in order to check
for the correct usage of the names of the categories and
relations and to correctly interpret the program's commands.
Example 2-4.
Let UNIVERSITY-MASTER-VIEW be the userview identical to the
whole schema. The following may be an Extended Pascal heading
for a program using the whole schema of the University
database. We assume that the name of the database is
UNIVERSITY-DB. This name will be used by the DBMS to locate
all the files comprising the database.
program My-program (Input, Output, UNIVERSITY-DB,
UNIVERSITY-MASTER-VIEW);
2. Data type ABSTRACT - a new basic data type, in addition to
INTEGER, BOOL, REAL, CHAR, enumerated types , and STRING. (The
type STRING is not defined in the standard Pascal but is used,
sometimes with a different name, in most practical versions of
Pascal.)
The variables of type ABSTRACT will contain abstract objects.
(Practically, these variables will contain logical references to
abstract objects. The referencing, however, is transparent to
the user.) The variables of this type are called abstract
variables.
The abstract variables cannot be printed. They cannot receive a
value through a read instruction. There are no constants of type
ABSTRACT.
Assignment to the abstract variables can be done from other
abstract variables or from the database or by the instruction
create as discussed later.
In expressions, the only meaningful operation on arguments of
type ABSTRACT is the test for their equality. The equality test,
"=", produces TRUE if the two arguments are one and the same
object in the database.
Example 2-5.
var jackson: ABSTRACT (* The abstract variable jackson may be
used to retrieve from the database a reference to Professor
Jackson, that is, to the abstract object of the category
INSTRUCTOR related by the relation LAST-NAME to the string
`Jackson'. *)
2.2.2.2. Extended expressions
There are new operators which can be used in Pascal expressions:
1. (expression-of-type-ABSTRACT is a category-from-the-
userview)
This Boolean expression gives TRUE when the left-side
subexpression is evaluated into an object which is a member of
the category on the right side.
The membership test is done according to the information in the
instantaneous database at the run time of the program.
Example 2-6.
o (jackson is a STUDENT)
If the variable jackson is uninitialized, then a run-time
error results. If this variable contains an abstract object,
then the result of the expression is TRUE if that object is a
student. If this object is simultaneously a student and an
instructor, the result is still TRUE.
2. (expression relation-from-the-userview expression)
This Boolean expression gives TRUE when the two subexpressions
yield objects participating in the relation in the instantaneous
database. The types of the subexpressions must be consistent
with the relation. For example, if the relation is between
abstract objects and real numbers, then the type of the left
subexpression must be ABSTRACT and the type of the right
subexpression must be REAL.)
Example 2-7.
o (jackson FIRST-NAME `Roberta')
o (jackson BIRTH-YEAR 1960)
Instead of one of the subexpressions, the keyword null may
appear. Then the Boolean expression would give TRUE if the object
yielded by the remaining subexpression is related by the relation
to no object in the instantaneous database.
Example 2-8.
o (jackson WORKS-IN null)
This expression yields TRUE when the person referred to by the
variable jackson does not work in any department.
Example 2-9.
o (jackson BIRTH-YEAR null)
This expression yields TRUE if, for the person referred to by
the variable jackson, no birth-year was recorded in the
database.
3. (expression. functional-relation-from-the-userview)
Reminder: a functional relation is an m:1 relation. It relates
every object of its domain to at most one object of its range.
The expression x.R produces the object related by the relation R
to x; that is, the result is the object y from the instantaneous
database such that (x R y) is TRUE.
If no such object y exists, then a null object results, which can
cause a subsequent execution-time error.
Example 2-10.
o (jackson. BIRTH-YEAR)
Example 2-11.
Here is a program fragment to print the age of the person
referred to by the variable jackson. We assume that the
current year is available in the variable current-year.
write (` Professor ', jackson. FIRST-NAME, ` ', jackson.
LAST-NAME, ` is approximately ', (current-year -
jackson. BIRTH-YEAR), ` years old.')
2.2.2.3. Atomic database manipulation statements
1. create new abstract-variable in abstract-category-from-the-
userview
o A new abstract object is created in the database
o This object is placed into the specified category (the
database is updated to reflect this fact)
o [A reference to] this object is assigned to the specified
variable
Example 2-12.
var department: ABSTRACT; ...
create new department in DEPARTMENT
This instruction has two effects:
o A new abstract object is created in the category
DEPARTMENT in the database.
o A reference to this object is assigned to the variable
department in the program's memory.
2. categorize: expression-of-type-ABSTRACT is a category
The expression is evaluated to produce an existing instantaneous
database object, and this object is inserted into the specified
category (in addition to other categories the object may be a
member of).
Example 2-13.
Let the variable jackson refer to an existing instructor
Professor Jackson. The following instruction will place
Professor Jackson also into the category STUDENT (in addition
to the category INSTRUCTOR).
categorize: jackson is a STUDENT
This instruction has only one effect: a change in the
instantaneous database. It produces no change in the program's
working space; that is, it does not change the contents of any
variable.
3. decategorize: expression-of-type-ABSTRACT is no longer a category
The object is removed from the category.
The object is also automatically removed from the subcategories
of the category. (Otherwise the database would become
inconsistent.)
The object is also automatically removed from the relations whose
domains or ranges are categories of which the object is no longer
a member. (This automatic removal saves programming effort. This
removal is also necessary to maintain the consistency of the
database.)
If after the decategorization the object would not belong to any
category in the database, then the object is removed from the
database.
Example 2-14.
decategorize: jackson is no longer a STUDENT
The person referred to by the variable jackson will no longer
be a student. She will no longer participate in any relation
whose domain or range is the category STUDENT. For example,
she will be disconnected from her major and minor departments.
Example 2-15.
The following instruction removes the object referred to by
the variable jackson from the database:
decategorize: jackson is no longer a PERSON
4. relate: expression relation expression
A new fact is added to the database: a relationship between the
objects yielded by the two expressions.
Example 2-16.
Assuming that the variable jackson refers to an instructor
whose birth-year was not known until now, the following
instruction will set the birth-year:
relate: jackson BIRTH-YEAR 1961
Example 2-17.
Assuming that the variable jackson refers to an instructor who
is also a student having a major department, the following
instruction will make Jackson work in her major department.
If she was also working in some other department, she will
continue working there too.
relate: jackson WORKS-IN jackson. MAJOR
5. unrelate: expression relation expression
This has the reverse effect of the instruction relate.
Example 2-18.
Assuming that the variable jackson refers to an instructor
whose birth-year has been incorrectly recorded, the following
instructions will change the birth-year to 1961:
unrelate: jackson BIRTH-YEAR jackson. BIRTH-YEAR
(* The expression "jackson. BIRTH-YEAR" gives the
previously recorded birth-year.*);
relate: jackson BIRTH-YEAR 1961
6. expression.relation := expression
The assignment statement
x.R := y
means:
o For every z, unrelate x R z
o Then relate x R y.
Example 2-19.
Assuming that the variable jackson refers to an instructor
whose birth-year has not been recorded yet, or has been
incorrectly recorded, the following instruction will make the
birth-year 1961:
jackson. BIRTH-YEAR := 1961
Example 2-20.
Assume that the variable math refers to the Mathematics
department, and the variable miller refers to an instructor.
What is the effect of the following instruction?
miller. WORKS-IN := math
Miller will only be working in the Mathematics department.
o If Miller was not working in any department, he will be
working in Mathematics.
o If Miller was working in the Management Science and
Physics departments, he is hereby fired from Management
Science and Physics, and hired in Mathematics.
2.2.2.4. The for statement
The for statement is the core of fourth-generation programming. This
statement creates a structured loop. Syntax:
for variablein category
satisfying boolean-expression
do statement
Interpretation:
The statement after do, which may be a compound statement, will
be performed once for every object which belongs to the category
and satisfies the boolean-expression.
Example 2-21.
Print the last names of all the students born in 1964.
for s in STUDENT
satisfying s BIRTH-YEAR 1964
do writeln(s.LAST-NAME)
Example 2-22.
Print the last names of all the students.
for s in STUDENT
satisfying true
do writeln(s.LAST-NAME)
The for statement is functionally equivalent to the following
algorithm.
Let VEC of length L be the vector of all the category's
objects in the instantaneous database. The vector is
arranged in an arbitrary order, transparent to the user.
Then the equivalent algorithm for the for statement is:
for i := 1 to L do
begin
variable := VEC [i];
if boolean-expression
then statement
end
Abbreviation:
In the for statement, the ``in category'' part may be omitted.
In this case, by default the category is assumed to be a special
category OBJECT, which is regarded as the union of all the
abstract categories in the database. Thus, the body of the loop
will be executed for every abstract object (in the instantaneous
database) satisfying the condition of the loop. Practically, the
condition may explicitly or implicitly restrict the loop to one
category.
Example 2-23.
Print the last names of all the persons born in 1964.
for s satisfying s BIRTH-YEAR 1964 do
writeln (s.LAST-NAME)
Example 2-24.
A larger program fragment:
Who are the persons that taught persons that taught persons
that taught persons that taught Mary?
for mary in STUDENT satisfying (mary FIRST-NAME `Mary') do
for enrl1 in COURSE-ENROLLMENT satisfying (enrl1 THE-
STUDENT mary) do
for enrl2 in COURSE-ENROLLMENT satisfying (enrl2
THE-STUDENT enrl1.THE-OFFERING.THE-INSTRUCTOR)
do
for enrl3 in COURSE-ENROLLMENT satisfying
(enrl3 THE-STUDENT enrl2.THE-
OFFERING.THE-INSTRUCTOR) do
for enrl4 in COURSE-ENROLLMENT satisfying
(enrl4 THE-STUDENT enrl3.THE-
OFFERING.THE-INSTRUCTOR) do
writeln (enrl4.THE-OFFERING.THE-
INSTRUCTOR.LAST-NAME)
2.2.2.5. Transactions
1. The transaction statement
transaction compound-statement
The effects of "transaction S" are:
a. While S is being executed, the program containing the
transaction statement and all the other concurrent programs
see the database in its instantaneous state just before S.
b. All the updates are logically performed instantly when S is
completed, provided the new instantaneous database would not
violate the integrity constraints and no error-condition is
raised.
Note: Among the advantages of this statement is the following:
At an intermediate state, the instantaneous information
could be incomplete, which could bring failure of an
integrity constraint and incorrect comprehension of the
database by concurrent programs.
Example 2-25.
Assume that the relation LAST-NAME is total. We wish to
create a new person and give her the last name `Chen'.
The following is a wrong program fragment to perform the task:
create new chen in PERSON;
chen. LAST-NAME := `Chen'
The above program would violate the integrity of the database
when the instruction create was performed: there would be a
person with no last name, contrary to the totality of the
relation LAST-NAME. The program would probably be aborted
before it reached the assignment statement. To prevent the
integrity validation between the two statements, we enclose
them in a transaction statement:
transaction begin
create new chen in PERSON;
chen. LAST-NAME := `Chen'
end
Also, we would not have to worry that concurrent queries see
the database half-updated: a person without a last name.
Example 2-26.
The database needs to be changed to pretend that the person
referred to by the variable jackson is not, nor has ever been,
a student. Thus, the object has to be decategorized from
STUDENT, and all the relevant enrollments have to be erased.
transaction
begin
for e in COURSE-ENROLLMENT satisfying (e THE-STUDENT
jackson) do
decategorize: e is no longer a COURSE-ENROLLMENT;
decategorize: jackson is no longer a STUDENT
end
A database update statement which is not embedded in a
transaction statement is regarded as one transaction.
2. Error exit
When the system fails to perform a transaction because of an
error, such as a violation of an integrity constraint, it
notifies the program by invoking
procedure Transaction-error-handler (error-description: String)
The body of this procedure can be specified in the program by the
user. This allows the programmer to decide what to do in case of
error. If the procedure is not defined by the user in the
program, then, by default, the system will insert the following
specification of the body of this procedure:
procedure Transaction-error-handler (error-description:
String);
begin
writeln (`The program was terminated by the default
transaction error handler when a transaction
failed with the following error condition: ',
error-description);
stop
end
Example 2-27.
The user can specify the following handler, which prints a
message and then allows the program to continue.
procedure Transaction-error-handler (error-description:
String);
begin
writeln (`A transaction failed with the following
error condition: ', error-description);
end
2.2.3. A programming example
Example 2-28.
The university has decided to expel all the students whose
average grade is below 60 (out of 100). To prevent this
wrongdoing to computer science students, the department
offered a fictitious course, Computer-Pass, by Prof. Good, in
which all computer science students are to receive a
sufficient grade so as to not to be expelled, if possible.
The following program fabricates Prof. Good and the
Computer-Pass course, enrolls students in this course, grades
them accordingly, and prints the names of those computer
science students whom this measure cannot help.
program Pass (Input, Output, UNIVERSITY-DB, UNIVERSITY-MASTER-VIEW);
var Computer-Pass-Course, Prof-Good, Good-Offer, comp-science, this-
quarter, cs-student, her-enrollment, fictitious-enrollment:
ABSTRACT;
desired-grade, number-of-grades, total-of-grades, current-year:
INTEGER;
begin
(* Get the current year from the standard input file.*)
read (current-year);
(* Fabricate the course.*)
create new Computer-Pass-Course in COURSE;
Computer-Pass-Course. NAME := `Computer Pass';
(* Fabricate the instructor.*)
create new Prof-Good in INSTRUCTOR;
Prof-Good. LAST-NAME := `Good';
(* Fabricate the offering.*)
create new Good-Offer in COURSE-OFFERING;
Good-Offer. THE-COURSE := Computer-Pass-Course;
Good-Offer. THE-INSTRUCTOR := Prof-Good;
(* Find the relevant quarter and connect it to the offering
Good-Offer.*)
for this-quarter in QUARTER
satisfying (this-quarter. YEAR = current-year and
this-quarter. SEASON = `Winter')
do Good-Offer. THE-QUARTER := this-quarter;
(*The following loop will be performed only once. Inside the body of
the loop, the variable comp-science will refer to the Computer
Science Department.*)
for comp-science in DEPARTMENT
satisfying (comp-science NAME `COMPUTER SCIENCE') do
begin
(*Make believe that Prof. Good works in Computer Science.*)
relate: Prof-Good WORKS-IN comp-science;
for cs-student in STUDENT
satisfying (cs-student MAJOR comp-science) do
begin (*the current computer science student*)
(* Calculate this student's current statistics: number-of-
grades and total-of-grades *)
number-of-grades := 0;
total-of-grades := 0;
for her-enrollment in COURSE-ENROLLMENT
satisfying (her-enrollment THE-STUDENT cs-
student) do
if not (her-enrollment FINAL-GRADE null) then
begin
number-of-grades := number-of-grades + 1;
total-of-grades := total-of-grades + her-
enrollment.FINAL-GRADE
end;
(* calculate the minimal desired grade in computer-pass
course, by solving the equation (total+x)/(number+1)=60
*)
desired-grade := 60 * (number-of-grades + 1) - total-
of-grades;
if desired-grade > 100 then
(* the student cannot be helped. Print a message *)
writeln (` The student ', cs-student. LAST-NAME,
` cannot be helped. Sorry!')
else if desired-grade < 60 then
(* No need to help. *)
else (* 100 > desired-grade > 60 *)
transaction begin
create new fictitious-enrollment in COURSE-
ENROLLMENT;
fictitious-enrollment. THE-OFFERING := Good-Offer;
fictitious-enrollment. THE-STUDENT := cs-student;
fictitious-enrollment. FINAL-GRADE := desired-
grade
end (*transaction*)
end (*current student*)
end (*Computer Science Department*)
end.
2.3. Logic as a Nonprocedural Language
2.3.1. Principles
Nonprocedural language - a language in which the user specifies what
is to be done without specifying how it is to be done.
Example 2-29.
In a nonprocedural language, the user might say:
``Let no student be enrolled twice in the same
offering of a course''
The user would probably use a more precise and formal
statement, which still would be nonprocedural:
``If enrl1 is an enrollment, and enrl2 is an
enrollment, and
enrl1. THE-STUDENT = enrl2. THE-STUDENT, and
enrl1. THE-OFFERING = enrl2. THE-OFFERING,
then enrl1 = enrl2.''
This statement contains no indication as to how the constraint
is to be enforced: this is left to the system. The system
might enforce the constraint as follows:
Whenever a transaction like
transaction begin ...
create new enrl in COURSE-ENROLLMENT;
enrl. THE-STUDENT := s;
enrl. THE-OFFERING := of;
...
end
is being completed, perform automatically the following:
(* If there is another enrollment with the same student
and offering, then give an error message and stop.
*)
for other-enrl in COURSE-ENROLLMENT
satisfying ( other-enrl. THE-STUDENT = s and
other-enrl. THE-OFFERING = of)
do begin
writeln (` In violation of an integrity
constraint, the program has attempted to
generate a duplicate enrollment'); stop
end
Example 2-30.
In a nonprocedural language, the user might request the
following information:
``What instructors give the grade 100 (sometimes)?''
The user would probably use a more precise and formal
statement, which still would be nonprocedural:
``Get instructor.LAST-NAME where
exists enrollment such that
enrollment. THE-OFFERING. THE-INSTRUCTOR =
instructor and
enrollment. FINAL-GRADE = 100''
The statement would contain no indication as to how this query
is to be performed: this is left to the system. The system
might use the following algorithm:
for instructor in INSTRUCTOR
do begin
ok := false
for enrl in COURSE-ENROLLMENT
satisfying (enrl. THE-OFFERING. THE-
INSTRUCTOR
= instructor and
enrl FINAL-GRADE 100)
do ok := true
if ok then write (` ', instructor. LAST-
NAME)
end
Many nonprocedural database languages are based on Predicate Calculus,
borrowed from Mathematical Logic. The language of Predicate Calculus
will be defined in the following subsection.
Uses: nonprocedural specification of queries, integrity constraints,
inference of userviews, and update transactions.
Predicate Calculus is based on Boolean expressions involving
variables.
Example 2-31.
o instructor. LAST-NAME = `Einstein'
o d NAME `Geology'
o student. BIRTH-YEAR > 1970 or student. BIRTH-YEAR = 1960
Such Boolean expressions can be used to specify queries similarly to
the specification of sets in mathematics:
o A set of objects is found which make a Boolean expression to
be true.
o These objects or their functions are displayed.
Example 2-32.
A query to find the names of the persons born in 1967:
get person. LAST-NAME
where (person. BIRTH-YEAR = 1967)
A query can display several columns of output, in a table form.
Example 2-33.
A query to find the first and last names of the persons born
in 1967:
get person. FIRST-NAME, person. LAST-NAME
where (person. BIRTH-YEAR = 1967)
Several different objects, referred to by different variables, may be
used in one row of the output tables.
Example 2-34.
For every student, list the instructors of the student's major
department.
get student. FIRST-NAME, student. LAST-NAME, instructor.
FIRST-NAME, instructor. LAST-NAME
where
(instructor
WORKS-IN
student. MAJOR)
Very powerful constructs of Predicate Calculus are the quantifiers for
every and exists. They are used to form nontrivial Boolean
expressions.
Example 2-35.
What instructors work in every department? (Each relevant
instructor shares her time between all the departments.)
get instructor. LAST-NAME where
(for every d in DEPARTMENT:
instructor WORKS-IN d)
Example 2-36.
What instructors taught every student?
get instructor. LAST-NAME where
(for every s in STUDENT:
exists enrl in COURSE-ENROLLMENT:
((enrl THE-STUDENT s) and
(enrl. THE-OFFERING. THE-INSTRUCTOR =
instructor)))
2.3.2. First-order predicate calculus expressions
The First-order Predicate Calculus is well-known to those who have
studied some Mathematical Logic. This calculus can be applied to
databases, if we regard the instantaneous database as a finite
structure with binary relations, unary relations (categories), and
functions (functional relations). This text, however, does not
require a prior knowledge of Predicate Calculus.
Expression - a combination of constants, variables, operators, and
parentheses. The syntax and semantics are given below.
An expression may depend on some variables. When the variables
are interpreted as some fixed objects, the expression can be
evaluated with respect to a given instantaneous database and will
yield an abstract or concrete object. The following are syntactic
forms of expressions:
1. constant
a. number
b. character-string (in quotes)
c. Boolean value (TRUE and FALSE)
Example 2-37.
7, 16.5, `Mary', `87/05/31', TRUE
2. variable
A variable is a sequence of letters, digits, and hyphens. The
first character must be a letter.
3. ( expression )
Parentheses in expressions may be omitted when no ambiguity
results.
4. (expression basic-binary-operator expression)
The basic binary operators are: +, -, *, /, >, <, >, <, =, =,
and, or.
Each operator may be used only when the expressions yield values
of types appropriate for the operator. The only basic binary
operators defined for abstract objects are = and =, which produce
TRUE or FALSE as results.
Example 2-38.
o 5 + 6 * 7
o x = y
o (`Abc' > `Bcc') or (1 + 2 > 2)
5. (if expression then expression)
Both component expressions must yield Boolean values. When the
right expression yields TRUE, the result is TRUE regardless of
the left expression. When the left expression yields TRUE and
the right expression yields FALSE, the result is FALSE. When the
left expression yields FALSE, the result is TRUE regardless of
the right expression.
Example 2-39.
The following expressions are TRUE:
o if 1=1 then 2=2
o if 1=3 then 2=2
o if 1=3 then 2=4
The following is FALSE:
o if 1=1 then 2=4
Note:
if e then e
1 2
is equivalent to
(not e ) or e
1 2
6. (expression relation expression)
The relation is a relation from the userview. The result is TRUE
if the two objects are related by the relation in the
instantaneous database.
Example 2-40.
(x BIRTH-YEAR 1960)
The value of this Boolean expression depends on the variable
x.
7. (basic-unary-operator expression)
The basic unary operators are: -, not.
Example 2-41.
(not (1>1)) = TRUE
8. (expression is a category)
This Boolean expression yields TRUE when the object is in the
category in the instantaneous database.
Example 2-42.
x is a STUDENT
9. (expression . functional-relation)
x.R is the object related by the relation R to x; it is the
object y from the instantaneous database such that (x R y) gives
true. Such an expression is called dot-application.
Example 2-43.
o x. BIRTH-YEAR
o e. THE-OFFERING. THE-INSTRUCTOR. LAST-NAME
The dot-application is well-defined only for total functional
relations. The case of nontotal functional relations will be
discussed later.
10. (exists variable in category : expression)
The `:' may be pronounced `so that the following is true:'.
The contained expression must be Boolean.
The result is also Boolean.
It is TRUE when there exists at least one object in the category
which satisfies the Boolean expression.
The expression usually depends on the variable but may also
depend on additional variables. The resulting expression no
longer depends on the variable.
Interpretation:
Let a , a ,...,a be all the objects in the category in the
1 2 n --------
instantaneous database.
Let e , e ,..., e be obtained from the expression by
1 2 n ----------
substituting each of a , a ,..., a for all the occurrences
1 2 n
of the variable in the expression.
Then
exists variable in category : expression
is equivalent to
e or e or ... or e
1 2 n
Example 2-44.
(exists x in INSTRUCTOR :
x. BIRTH-YEAR = 1960)
This expression is TRUE if there is at least one instructor
who was born in 1960. The whole expression does not depend on
the variable x, although its subexpression "x. BIRTH-YEAR =
1960" does depend on this variable.
Example 2-45.
(exists x in INSTRUCTOR :
x. BIRTH-YEAR = y)
This is TRUE if there is at least one instructor who was born
in the year y. The whole expression depends only on the
variable y.
The keyword exists is often called the existential quantifier.
It may be abbreviated by the symbol oppE.
11. (for every variable in category : expression)
The `:' is pronounced `the following is true:'.
The expression must be Boolean. The result is also Boolean. It
is TRUE when all the objects of the category satisfy the Boolean
expression. The expression usually depends on the variable and
may also depend on additional variables. The resulting
expression no longer depends on the variable.
Interpretation:
Let a , a ,..., a be all the objects in the category in the
1 2 n --------
instantaneous database.
Let e , e ,..., e be obtained from the expression by
1 2 n ----------
substituting each of a , a ,..., a for all the occurrences
1 2 n
of the variable in the expression.
Then
for every variable in category : expression
is equivalent to
e and e and ... and e
1 2 n
Example 2-46.
(for every x in INSTRUCTOR :
x. BIRTH-YEAR = 1960)
This expression is TRUE if all the instructors were born in
1960.
Notice that the result is TRUE even if there are no
instructors at all in the instantaneous database. (Isn't it
true that every presently living dinosaur knows Predicate
Calculus or that every American monarch is a republican?)
The whole expression does not depend on the variable x,
although its subexpression "x. BIRTH-YEAR = 1960" does depend
on this variable.
Example 2-47.
(for every x in INSTRUCTOR :
x. BIRTH-YEAR = y)
This is TRUE if all the instructors were born in the year y.
The whole expression depends only on the variable y.
The keyword for every is often called the universal quantifier.
It may be abbreviated by the symbol .
Note:
for every variable in category : expression
is equivalent to
not (exists variable in category : not expression)
Usage of variables:
The variable after a quantifier in a subexpression should not be
used outside that subexpression. Although many versions of
Predicate Calculus do not have this requirement, it does not
decrease the power of the calculus but improves readability,
prevents some typical errors in query specification, and
simplifies the semantics.
Example 2-48.
WRONG:
(exists x in PERSON: x is a STUDENT) and (x BIRTH-YEAR
1970)
Here, x appears in the quantifier of the left subexpression
but also appears in the right subexpression. Logically, these
are two distinct variables, and they should not be called by
the same name x.
To use the expressions correctly, we shall need to know what
variables are quantified in an expression and on what variables
an expression depends.
Quantified variable - variable v is quantified in expression e if v
has an appearance in e immediately after a quantifier.
Example 2-49.
The variable v is quantified in:
((z > 0) or (exists v in STUDENT: v is an INSTRUCTOR))
Expression e depends on variable v if v appears in e and is not
quantified.
Example 2-50.
The following expression depends on z and x but not on y.
((z > 0) or (exists y in STUDENT: x = y.BIRTH-YEAR))
Notation: When an expression e depending on variables x , x ,..., x
-------- - 1 2 k
is referred to (not in the actual syntax of the language), it may
be denoted as
e(x , x ,..., x )
1 2 k
Example 2-51.
The expression of the previous example may be referred to as
e(z,x).
(In many texts, a variable on which an expression depends is
called a free variable in that expression. An expression which
depends on no variables is called a closed expression.)
Condition on variables x , x ,..., x - a Boolean expression which
1 2 k
depends on x , x ,..., x .
1 2 k
Example 2-52.
(x+y>3) is a condition on x and y.
Assertion - a Boolean expression which does not depend on any
variable; that is, every variable is restricted by a quantifier.
Interpretation: For a given instantaneous database, the
assertion produces TRUE or FALSE.
Example 2-53.
Assertion that every student took at least one course in 1990:
for every st in STUDENT:
exists enrl in COURSE-ENROLLMENT:
((enrl THE-STUDENT st ) and
(enrl. THE-OFFERING. THE-QUARTER. THE-YEAR=1990))
Dot-application of nontotal functional relations
If f is not total, then e.f may be ambiguous. The concerned user
might wish to avoid such expressions. However, a smart DBMS may
be able to follow the user's intuition in using such expressions.
This is discussed further in Section
2.3.3. Queries
Specification of a query to retrieve a table, that is, a set of rows
of values:
get expression,..., expression
where
(condition-on-the-variables-on-which-the-expressions-depend)
Interpretation of the query get e ,...,e where (U(x ,...,x ))
-------------- 1 n 1 k
o The variables x ,...,x are assigned all the possible tuples of
1 k
objects which objects are in the current instantaneous database
and which make the assertion U(x ,...,x ) evaluate to TRUE.
1 n
o The expressions e ,...,e are evaluated for the above selected
1 n
tuples and the corresponding results are output. (The output is
not printable if any of the expressions produces an abstract
object.)
Example 2-54.
Who took Prof. Smith's courses?
get student.LAST-NAME where
exists enrl in COURSE-ENROLLMENT:
(enrl. THE-STUDENT=student and
enrl. THE-OFFERING. THE-INSTRUCTOR. LAST-NAME=`Smith')
Abbreviation:
The following abbreviation is used in some literature for the
query syntax. It is akin to the mathematical notation for sets.
{e ,...,e |U(x ,...,x )}
1 n 1 k
Abbreviation:
Queries which output only one value may be specified without the
``where condition'' part, as:
get expression
(provided the expression depends on no variables).
Example 2-55.
The following is a yes-or-no query which displays TRUE if
every student took at least one course.
get
(for every s in STUDENT:
exists enrl in COURSE-ENROLLMENT:
s=enrl.THE-STUDENT)
Headings of output columns:
The columns in a table which is an output of get can be labeled:
get heading :e ,...,heading :e where condition
1 1 n n ---------
Example 2-56.
Print a table with two columns, which associates students with
their teachers. Only last names are printed.
get Teacher: instructor. LAST-NAME, Student-taught:
student. LAST-NAME where
exists enrl in COURSE-ENROLLMENT:
enrl.THE-STUDENT = student and
enrl. THE-OFFER. THE-INSTRUCTOR = instructor
When no heading for e is specified, then, by default, the
i
following heading is assumed:
o If e ends in ".relation", then the heading is the
i --------
relation.
o Otherwise the heading is the number i.
Example 2-57.
The query
get x.LAST-NAME, x.BIRTH-YEAR
where x is a STUDENT
produces a table with two columns, whose headings are:
LAST-NAME, BIRTH-YEAR
2.3.4. Integrity constraints in Logic
Specification of static integrity constraints - by assertions.
Example 2-58.
No student may be enrolled twice in the very same offering of
a course.
for every enrl in COURSE-ENROLLMENT:
for every enrl2 in COURSE-ENROLLMENT:
if enrl.THE-STUDENT=enrl2.THE-STUDENT and
enrl.THE-OFFERING=enrl2.THE-OFFERING
then enrl=enrl2
Specification of dynamic integrity constraints:
A dynamic integrity constraint is syntaxed as a static integrity
constraint, but categories and relations may be suffixed with
-old to denote the concepts of the previous state of the
database.
Example 2-59.
The Student Council has secured that from 1991 once a grade
has been reported (and thus, probably has been made known to
the student) it may not be retroactively decreased.
for every enrl in COURSE-ENROLLMENT:
if enrl.FINAL-GRADE-old>enrl.FINAL-GRADE then
enrl.THE-OFFERING.THE-QUARTER.THE-YEAR<1991
Dynamic integrity constraints are rarely dictated by the logic of
a user's world, since their existence may cause irreversibility
of some users' errors.
Example 2-60.
In the previous example, a data-entry clerk who erroneously
enters the grade of 80 instead of the correct grade of 70 may
not be able to correct the typo.
2.3.5. *Extensions of Logic: aggregates, userviews, transactions,
query forms
This is an advanced section.
2.3.5.1. *Aggregate operations: sum, count, average
Defined here is a second-order extension to enable set operations,
such as summation, counting, etc. This is done by extending the
syntax of expression with the summation quantifier:
R expression
1
variables
where
condition
Example 2-61.
The sum of the birth-years of all students =
R s.BIRTH-YEAR
s
where
s is a STUDENT
Example 2-62.
The number of pairs (instructor, department) where the
instructor works in the department.
R 1
instructor,department
where
instructor WORKS-IN department
(In the above formula, 1 is a constant function. Thus, for
5
example, R 1 equals 5. Adding up the constant 1 is thus the
i=1
same as counting the object pairs satisfying the condition
under the sum.)
The variables under R are quantified by the summation symbol. In
addition to these variables, the condition and/or the expression
--------- --------- 1
may depend on other variables.
Example 2-63.
The sum of the grades of student s. The sum depends on the
variable s, meaning s remains free in the sum.
R enrl.FINAL-GRADE
enrl
where
enrl is a COURSE-ENROLLMENT and
not (enrl FINAL-GRADE null) and
enrl THE-STUDENT s
Interpretation:
Let e be an expression, and let U(x ,...,x y ,...,y ) be a
1 n, 1 k
condition. Then the following is also an expression (it depends
on the variables y ,...,y ):
1 k
R( e(x ,...,x ,y ,...,y ))
1 n 1 k
x ,...,x
1 n
where
U(x ,...,x ,y ,...,y )
1 n 1 k
When all the parameter-variables y ,...,y are interpreted as
1 k
some fixed objects, the sum yields a number. This number is the
result of summation of the values of e computed for every tuple
of objects x ,...,x satisfying U(x ,...,x ,y ,...,y ).
1 n 1 n 1 k
The R acts like a quantifier for x ,...,x . Therefore, although
1 n
the subexpression e does depend on x ,...,x , the whole R
1 n
expression does not. The variables y ,...,y remain
1 n
unquantified.
Alternative (linear) notation (we would not use the two-dimensional
notation of R in a real computer language):
sum e
for x ,...,x
1 n
where U(x ,...,x ,y ,...,y )
1 n 1 k
Abbreviation:
When x ,...,x are exactly the variables on which the expression
1 n
e depends (that is, all x and none of y appear free in the
i i
expression e), the for clause may be omitted:
sum e where U(x ,...,x ,y ,...,y )
- 1 n 1 k
Example 2-64.
For every information systems student, print his last name and
the sum of his grades.
get
student. LAST-NAME,
(sum enrollment. FINAL-GRADE
where enrollment THE-STUDENT student)
where
student is a STUDENT and
(student. MAJOR DEPARTMENT-NAME `Information
Systems')
Example 2-65.
Print the sum of all the grades given by Prof. Smith. This
query outputs only one value (the sum). There is no ``where
condition'' for the ``get'' of the query.
get (sum enrollment. FINAL-GRADE
where
`Smith'=enrollment. THE-OFFERING.
THE-INSTRUCTOR.LAST-NAME)
Abbreviation for count:
count x ,...,x where U(x ,...,x ,y ,...,y )
1 n 1 n 1 k
stands for:
sum 1 for x ,...,x where U(x ,...,x ,y ,...,y ).
1 n 1 n 1 k
Example 2-66.
How many students are there in the university?
get (count std where std is a STUDENT)
Abbreviation for average:
average e ...
stands for:
(sum e ...) / (count e ...)
Example 2-67.
What students have their average grade below 60?
get std. LAST-NAME
where std is a STUDENT and
60 > (average enrl. FINAL-GRADE
where enrl THE-STUDENT std)
Note: this query could not be formulated as follows, since
only the distinct grades would be then taken into account:
get std. LAST-NAME
where 60 >
average fgrade
where
exists enrl in ENROLLMENT:
(enrl FINAL-GRADE fgrade and
enrl THE-STUDENT std)
If a student has three enrollments with grades 100, 50, and
100, then the average calculated in the first version would be
250/3=83 (correct), and in the second version 150/2=75
(incorrect).
2.3.5.2. *Shorthand notation for n-ary relationships
Example 2-68.
Often we need to specify a condition like:
The instructor i offered course c in quarter q.
In calculus this can be stated as:
exists offer in COURSE-OFFERING:
offer THE-INSTRUCTOR i and
offer THE-COURSE c and
offer THE-QUARTER q
The above statement can be written in a shorthand notation as:
COURSE-OFFERING
(THE-INSTRUCTOR: i,
THE-COURSE: c,
THE-QUARTER: q)
Abbreviation:
category (relation : expression ,..., relation : expression )
-------- 1 1 k k
stands for
exists x in category:
(x relation expression and ... and x relation expression )
1 1 k k
Example 2-69.
Print the names of the courses taught by Prof. McFarland.
get c. NAME
where
exists i in INSTRUCTOR:
i LAST-NAME `McFarland' and
COURSE-OFFERING (THE-INSTRUCTOR: i, THE-COURSE: c)
2.3.5.3. *Inference rules of userviews
Reminder:
A userview consists of an alternative schema and inference rules.
The inference rules specify the categories and relations of the
alternative schema in terms of the categories and relations of
the original schema.
Specification of an inferred relation
userview relation: expression new-relation expression
1 --- -------- 2
where condition
Example 2-70.
The following is a specification of an inferred relation
TAUGHT (between instructors and students) in terms of the
relations existing in the schema.
userview relation: instructor TAUGHT student
where
exists enrl in COURSE-ENROLLMENT:
enrl.THE-STUDENT = student and
enrl. THE-OFFER. THE-INSTRUCTOR = instructor
Example 2-71.
The following is a specification of an inferred relation
[] average-grade - attribute of STUDENT, range: 0..100
(m:1)
userview relation: student AVERAGE-GRADE
(average e.FINAL-GRADE
where
e is a COURSE-ENROLLMENT and
e THE-STUDENT student)
where student is a STUDENT
Specification of an inferred abstract category
Usually, but not always, an inferred abstract category is a new
subcategory of an existing category. Its purpose is usually to
restrict the userview user to a subset of objects which are
relevant to that user's task. An inferred category can be
specified as a set of objects which is derived from the
categories and relations of the database. The specification is:
userview subcategory: expression is a new-subcategory
where condition
Example 2-72.
userview subcategory: s is a COMPUTER-SCIENCE-MAJOR
where
s is a STUDENT and
s. MAJOR NAME `Computer Science'
Specification of representation of relationships by a category
The definition is preceded by an example.
Example 2-73.
We wish to have (in the userview) a category of events of work
of instructors in departments:
[] WORK - category
[] the-department - relation from WORK to DEPARTMENT
(m:1)
[] the-instructor - relation from WORK to INSTRUCTOR
(m:1)
The user of the user-view will perceive the category WORK as
containing objects (events) which are distinct from any other
objects in the database. These new objects will be perceived
only through the userview, without actually existing in the
database. Thus, they are virtual objects.
This is done by specifying a category of virtual objects with
relationships to existing objects. The syntax is:
userview category:
new-category (relation : expression ,..., relation : expression )
--- -------- 1 1 k k
where condition
Let x ,...,x be the variables on which the condition and the
1 n ---------
expressions depend. (They must depend on the same variables.) For
every tuple of values of variables satisfying the condition, one
new virtual object is created. That object is related by the new
relations relation ,...,relation to the values of the
1 k
expressions expression ,...,expression .
1 k
Example 2-74.
The following is a specification of an inferred category WORK
and two inferred relations, THE-DEPARTMENT and THE-INSTRUCTOR,
whose domain is WORK. The specification is in terms of the the
relations existing in the schema.
userview category:
WORK (THE-DEPARTMENT: department, THE-INSTRUCTOR:
instructor)
where instructor WORKS-IN department
2.3.5.4. *Transactions
This section extends Predicate Calculus to allow specification of
transactions - creation of sets of objects, categorization and
decategorization of objects, relating and unrelating objects, and so
on.
The operations in Calculus are usually not atomic but work on sets of
objects. One single operation can create a set of new objects, place
them in categories, and relate them to different existing objects by
several relations.
Creation of new abstract objects and relating them to existing or
concrete objects:
insert into category
(relation : expression ,..., relation : expression )
1 1 k k
where condition
o If no where clause is specified, then only one new abstract
object is created. This object is put into the category and
related by the relations to the values of the expressions.
Example 2-75.
Create a new department named `Computer Engineering'
insert into DEPARTMENT (NAME: `Computer Engineering')
o Some of the names of the relations may be identical. This allows
one object to be related to several objects by one relation (m:m
or 1:m).
Example 2-76.
Create a new department named `Computer Engineering' and `CE'.
insert into DEPARTMENT (NAME: `Computer Engineering', NAME:
`CE')
o If a where clause is specified with a condition on variables
x ,...,x , then for every tuple of values of the variables
1 n
satisfying the condition, one new object is created and related
accordingly.
Example 2-77.
Enroll the computer science student Jack Johnson into the
Databases course given by Prof. Smith in Fall 1990.
insert into COURSE-ENROLLMENT (THE-STUDENT: s, THE-OFFERING:
offer) where
s.MAJOR NAME `Computer Science' and
s.LAST-NAME=`Johnson' and
s.FIRST-NAME=`Jack' and
offer.THE-QUARTER.YEAR=1990 and
offer.THE-QUARTER.SEASON=`Fall' and
offer.THE-COURSE.NAME=`Databases' and
offer.THE-INSTRUCTOR.LAST-NAME=`Smith'
o The variables on which the condition depends must be those on
which the expressions depend.
Example 2-78.
Enroll all computer science students into the Databases course
given by Prof. Smith in Fall 1990.
insert into COURSE-ENROLLMENT (THE-STUDENT: s, THE-OFFERING:
offer)
where
s.MAJOR NAME `Computer Science' and
offer.THE-QUARTER.YEAR=1990 and
offer.THE-QUARTER.SEASON=`Fall' and
offer.THE-COURSE.NAME=`Databases' and
offer.THE-INSTRUCTOR.LAST-NAME=`Smith'
o When the insert statement calls for an insertion of a new object
while there is already an object having the same relationships as
those of the new object, the new object is not inserted.
Example 2-79.
If the department named `Management' already exists, then the
following command produces no effect:
insert into DEPARTMENT (NAME: `Management')
Connection between existing abstract objects, between existing
abstract objects and concrete objects, between existing abstract
objects and categories:
connect fact ,...,fact [where condition ]
1 k ---------
Each fact is either
i
expression category
i i
or
expression relation expression'
i i i
Interpretation:
o If no where clause is specified, the values of the
expressions are related by the relations and/or categorized
by the categories.
o If a where clause is specified with a condition U on
,...,x , variables exery tuple of values of the
1 n
variables satisfying U the values of the expressions are
related and categorized as above.
o The variables on which the condition U depends must be those
on which the expressions depend.
Example 2-80.
Let `CS' be an alternative name for the department named
`Computer Science'.
connect dept NAME `CS'
where
dept is a DEPARTMENT and
dept NAME `Computer Science'
Example 2-81.
Give the grade 100 to the computer science student Jack
Johnson enrolled in the Databases course given by Prof. Smith
in Fall 1990.
connect enrl FINAL-GRADE 100
where
enrl.THE-STUDENT.MAJOR NAME `Computer Science' and
enrl.THE-STUDENT.LAST-NAME=`Johnson' and
enrl.THE-STUDENT.FIRST-NAME=`Jack' and
enrl.THE-OFFERING.THE-QUARTER.YEAR=1990 and
enrl.THE-OFFERING.THE-QUARTER.SEASON=`Fall' and
enrl.THE-OFFERING.THE-COURSE.NAME=`Databases' and
enrl.THE-OFFERING.THE-INSTRUCTOR.LAST-NAME=`Smith'
Example 2-82.
Give the grade 100 to all computer science students enrolled
in the Databases course given by Prof. Smith in Fall 1990.
connect enrl FINAL-GRADE 100
where
enrl.THE-STUDENT.MAJOR NAME `Computer Science' and
enrl.THE-OFFERING.THE-QUARTER.YEAR=1990 and
enrl.THE-OFFERING.THE-QUARTER.SEASON=`Fall' and
enrl.THE-OFFERING.THE-COURSE.NAME=`Databases' and
enrl.THE-OFFERING.THE-INSTRUCTOR.LAST-NAME=`Smith'
Removal of connections and removal of objects:
disconnect fact ,...,fact [where condition]
1 k ---------
Interpretation:
o If no where clause is specified, the values of the
expressions are unrelated and/or decategorized. Objects that
are removed from all their categories are removed from the
database.
o If a where clause is specified with a condition U on
variables x ,...,x , then for every tuple of values of the
1 n
variables satisfying U the values of the expressions are
unrelated and decategorized as above.
o The variables on which the condition depends must be those
on which the expressions of the facts depend.
Example 2-83.
Let `CS' no longer be an alternative name of a department.
disconnect dept NAME `CS'
where
dept is a DEPARTMENT and dept NAME `CS'
Example 2-84.
The computer science student Jack Johnson has dropped the
Databases course given by Prof. Smith in Fall 1990.
disconnect enrl ENROLLMENT
where
enrl.THE-STUDENT.MAJOR NAME `Computer Science' and
enrl.THE-STUDENT.LAST-NAME=`Johnson' and
enrl.THE-STUDENT.FIRST-NAME=`Jack' and
enrl.THE-OFFERING.THE-QUARTER.YEAR=1990 and
enrl.THE-OFFERING.THE-QUARTER.SEASON=`Fall' and
enrl.THE-OFFERING.THE-COURSE.NAME=`Databases' and
enrl.THE-OFFERING.THE-INSTRUCTOR.LAST-NAME=`Smith'
Example 2-85.
Void all the grades in the Databases course given by Prof.
Smith in Fall 1990.
disconnect enrl FINAL-GRADE enrl.FINAL-GRADE
where
enrl.THE-OFFERING.THE-QUARTER.YEAR=1990 and
enrl.THE-OFFERING.THE-QUARTER.SEASON=`Fall' and
enrl.THE-OFFERING.THE-COURSE.NAME=`Databases' and
enrl.THE-OFFERING.THE-INSTRUCTOR.LAST-NAME=`Smith'
Correction of facts:
update fact ,..., fact [where condition]
1 k ---------
This is a combination of disconnect and connect. Before a
connection aRb is made, the relationships aRx are removed for
every x. Before a connection aC' is made, the facts aC are
removed for every C.
Example 2-86.
Let `CS' be the new name instead of `Computer Science'.
update dept NAME `CS'
where
dept is a DEPARTMENT and
dept NAME `Computer Science'
Example 2-87.
Give the grade 100 to the computer science student Jack
Johnson enrolled in the Databases course given by Prof. Smith
in Fall 1990. If a grade has been previously given, replace
it by the new grade.
update enrl FINAL-GRADE 100
where
enrl.THE-STUDENT.MAJOR NAME `Computer Science' and
enrl.THE-STUDENT.LAST-NAME=`Johnson' and
enrl.THE-STUDENT.FIRST-NAME=`Jack' and
enrl.THE-OFFERING.THE-QUARTER.YEAR=1990 and
enrl.THE-OFFERING.THE-QUARTER.SEASON=`Fall' and
enrl.THE-OFFERING.THE-COURSE.NAME=`Databases' and
enrl.THE-OFFERING.THE-INSTRUCTOR.LAST-NAME=`Smith'
Example 2-88.
Increase by 10 percent the grades of all computer science
students enrolled in the Databases course given by Prof.
Smith in Fall 1990.
update enrl FINAL-GRADE 1.1xenrl.FINAL-GRADE
where
enrl.THE-STUDENT.MAJOR NAME `Computer Science' and
enrl.THE-OFFERING.THE-QUARTER.YEAR=1990 and
enrl.THE-OFFERING.THE-QUARTER.SEASON=`Fall' and
enrl.THE-OFFERING.THE-COURSE.NAME=`Databases' and
enrl.THE-OFFERING.THE-INSTRUCTOR.LAST-NAME=`Smith'
2.3.5.5. *Parametric query forms
Often the users ask similar queries which differ only in the values of
some parameters.
Example 2-89.
1. What are the grades of the student whose name is
`Jackson'?
2. What are the grades of the student whose name is `Smith'?
It is desirable that such queries are predefined in parametric form,
and the users would supply only the values of the parameters.
Example 2-90.
What are the grades of the student whose name is x, where x is
supplied by the end user when the query runs?
Such a predefinition is called a query in parametric form or query
form. It saves time on specification of similar queries and allows
the less-sophisticated end users to use queries which can be specified
only by more sophisticated users, such as programmers and analysts.
In calculus, query forms are specified by the following syntax:
depending on parameters
get expressions
where condition
The condition and the expressions may depend on the parameters.
Example 2-91.
What are the grades of the student whose name is x, where x is
supplied by the end user when the query runs?
depending on x
get e.THE-OFFERING.THE-COURSE.NAME, e.FINAL-GRADE
where
e is an ENROLLMENT and
e.THE-STUDENT.LAST-NAME = x
2.3.6. *Nontotal functional relations: interpretation of the dot-
application
If f is not total, then e.f may be ambiguous. However, a smart DBMS
may be able to follow the user's intuition in using such expressions.
In order to provide a meaningful result, the dot-application e.f of a
nontotal functional relation f to an expression e is interpreted by
the smart DBMS by analyzing the whole condition or assertion
containing the dot-application.
Example 2-92.
Consider the following assertion which contains a dot-
application of the nontotal relation BIRTH-YEAR.
for every y in STUDENT:
y.BIRTH-YEAR > 1980
This assertion will be interpreted by a smart DBMS as
for every y in STUDENT:
exists x in Integer:
y BIRTH-YEAR x and x > 1980
This interpretation of the dot-application of nontotal functional
relations can be defined formally as follows.
An expression e.f, where e is an expression and f is a database
functional relation, is regarded as a syntactic abbreviation. Let
x ,...,x be the variables on which the expression e depends. For the
1 k
above example, the only such variable is y.
Let U be the largest subexpression (within the whole assertion or
condition) containing e.f and still depending on all the variables
x ,...,x ; that is, none of these variables is quantified in the
1 k
subformula U. (U may depend also on additional variables.) For the
above example,
U = (y.BIRTH-YEAR > 1980)
Let C be the range of f.
x
Let V=U| . (That is, V is obtained from U by substitution of a
e.f
new variable x for all the occurrences of (e.f) in V.) For the above
example,
V = (x > 1980)
Then U stands for:
(exists x in C: ((e f x) and V))
Figure Ref-2. A relational schema for the university
application.