Re: teaching relational basics to people, questions

From: Mr. Scott <>
Date: Sat, 12 Dec 2009 17:37:49 -0500
Message-ID: <>

"paul c" <> wrote in message news:DASUm.55772$Db2.7870_at_edtnps83...
> Mr. Scott wrote:
> ...
>> In a typical table,
>> each row states that a particular COURSE is taught by a particular
>> in a particular ROOM to a particular STUDENT.
>> Now, while it can be argued that there can't be a course without a
>> teacher,
>> or that there can't be a course without a student, or that there can't be
>> a
>> student without a teacher, the room exists independent of whether there
>> is a
>> course, or a teacher or a student. It therefore follows that locating
>> the
>> fact that 'there is a room <ROOM>' only in table CTRS is a problem
>> because
>> then there could only be a room whenever there is at least one course and
>> at
>> least one teacher and at least one student. When one inserts a row into
>> an
>> empty CTRS, one effectively asserts
>> ...
>> that 'there is a room <ROOM>,' ...
> This presumes that the predicate for a table R { ROOM } is the same as the
> predicate of CTRS { ROOM } but from the dbms perspective it's patent they
> can't be, since R and CTRS don't even have the same heading. If CTRS is
> not base, maybe you can say such but if it is base, one can only assert
> that there exists some teacher, student and course such that room <ROOM>
> is involved.

There is no table R. The only place in the database to record assertions that there is a particular room is in CTRS, but one cannot assert that there is a particular room without also asserting that there is a particular teacher, that there is a particular student, and that said teacher is teaching a particular course to said student in that room.

>> If you disagree that all of these assertions are a consequence of
>> inserting a single row, then how is it that there can be an answer to the
>> query, "is course <COURSE> held in room <ROOM>?" Unless there is the
>> possibility that 'course <COURSE> is held in room <ROOM>,' there can be
>> no answer (at least not a yes or a no) to the query.
>> ...
> If CTRS is base, that question is strictly not answerable.

Yes, it is answerable. The fact that a particular teacher is teaching a particular student a particular course in a particular room implies the fact that that particular course is held in that particular room.

> I suspect that the man in the street might think such a query is possible
> (not to mention many db designers) but to be precise I think it's
> important to distinguish what is from what could be, otherwise we lapse
> into mysticism. A query that is answerable from CTRS is "are there some
> teachers and students such that course <COURSE> and room <ROOM> are
> involved?".

I think you're assuming that it is possible for there to be courses in rooms even if there aren't any students or teachers, but that hasn't been established, and in fact since there is no place in the database for such assertions, there cannot be courses in rooms without students or teachers. The queries that are answerable are therefore not limited to just those that involve all four attributes.

If there were a place in the database for such assertions, such as a table

CR {COURSE,ROOM} Then what is in the database would consist of the logical sum of all courses in rooms without students or teachers and all courses in rooms with students and teachers. The query "Is course <COURSE> held in room <ROOM>?" would involve both CR and CTRS, unless there is an inclusion dependency from CTRS[COURSE,ROOM] to CR[COURSE,ROOM], in which case the query could be answered without involving CTRS.

> Just because SQL and the like might not be expressive enough to prevent
> the query doesn't mean the most basic condition, ie., the definition of
> CTRS, can be ignored. A language that might reflect this might have two
> sets of operands for projection instead of one.
> (I also realize that the usual explanations of projection operators don't
> make this clear. Not that table/relvar names carry any significance other
> than as a device to segregate rows/tuples according to predicate but it
> might be more suggestive of the actual situation if the name of CTRS were
> changed to 'EXPELLED'.)

Table names are significant. There can be more than one table with the same set of columns. The closest logical analog to a table name is a predicate symbol. Predicate symbols are significant.

P(X) may be true for a given X even if Q(X) is false for the same X. Received on Sat Dec 12 2009 - 23:37:49 CET

Original text of this message