Re: constraints in algebra instead of calculus

Brian Selzer wrote:
"paul c" <toledobythesea@xxxxxxxx> wrote in message news:JQ94i.200044$DE1.108062@xxxxxxxxxxxx

Brian Selzer wrote:

"paul c" <toledobythesea@xxxxxxxx> wrote in message news:dX84i.201343$6m4.74192@xxxxxxxxxxxx


Marshall wrote:


Okay, a while back we were talking about writing constraints
in a language with aspects of the relational calculus, specifically
the existential and universal quantifiers. The point was made
that that's unnecessary; the calculus is no more expressive
than the algebra.

So it ought to be possible to write any constraint from the
calculus in the algebra.

Well, I'm having a hard time figuring out how to do it. Can
anyone help?

How does one write a functional dependency in the algebra?
A foreign key?


Marshall


I thought that one was easy, if FK is the set of 'foreign key' attributes and A is the 'referencing table' and using an op like D&D <AND>, then it's something like A{FK} <AND> B{FK} = A{FK}. (I like this one because it's particularly easy to implement.)



Very slick. One point, though. The constraint

A{FK} <AND> B{FK} = A{FK}

Is not sufficient. Also required is the constraint

COUNT(B{FK}) = COUNT(B)





I've never seen anybody justify why a foreign key must be a candidate key in the referenced relation/relvar/table except for 'so-and-so says so'. Whereas I've seen examples where the non-key reference is essential.


There is a paper by Mark Levene and Millist W. Vincent, "Justification for Inclusion Dependency Normal Form." In it they argue that a database schema should be free of circular inclusion dependencies and should only contain key-based inclusion dependencies (which are as far as I can tell from their definition, foreign key constraints)
...

What is their argument?

An inclusion dependency that is not also a foreign key constraint denotes a many-to-many relationship, ...

Not necessarily. My attitude is very much from an engine perspective where the feature definitions ought to be as elemental as possible. This shouldn't stop somebody who is using a product that has a hard time with circular dependencies from using a so-called catalog who doesn't want them for stylistic reasons from preventing their definition.

In my view, the candidate key aspect is a second constraint, combining the two in one "foreign key" directive buys nothing since anytime the reference is not to a key, another directive would be needed anyway. When Dave P says the reason is historical, I'm sure he's right, but the biggest lesson of history, if you ask me, is that it as much as not shows why we shouldn't always follow history. Somewhere, Hugh Darwen argued for the deprecation of the term "foreign key" - I think I'm with him. Of course, I would hope an ideal dbms would allow simple macro-ization for users who want to combine features with their own choice of constraint keyword.

Here is a quote from my possibly inaccurate echo of Darwen's example that I sent in a another thread:

(quote)
Student { StudentId, Name } KEY { StudentId }
Course { CourseId, Title } KEY { CourseId }
Staff { StaffId, Name } KEY { StaffId }

Teaches { StaffId, CourseId } KEY { StaffId }

(note that a teacher may teach only one course, the argument depends on
this)

Enrolment { StudentId, CourseId } KEY { StudentId, CourseId }

(note also that a student may take many courses and that a student may
enrol in a course before a teacher is assigned to it)

TutorFor { StudentId, StaffId } KEY { StudentId, StaffId }

It's possible to enter a row in TutorFor where StaffID stands for a
teacher who doesn't teach any course the student is enrolled in. If I
understand the argument it is that it would be simpler/easier for an
implementation if the system allowed a foreign key F1
(StudentId,StaffId) for TutorFor referencing the join of Enrolment and
TutorFor, eg., the view V={StudentId,StaffId,CourseId} where V is
constrained by its own foreign key F2 (StaffId,CourseId) referencing
Teaches. If I've got this right, the schema is in BCNF, but neither F1
or F2 references a key.
(end quote)

Sorry for the long post about something so basic, but no apology for talking about basics!

p
.

Relevant Pages

  • Re: New to SQL server
    ... [CONSTRAINT constraint_name] ... | [FOREIGN KEY] ... Is the name of the database in which the table is created. ... REFERENCES permission on the type is ...
    (microsoft.public.access.adp.sqlserver)
  • Re: A philosophical newbie issue: catch redundant errors via relationships or programmically?
    ... foreign key reference" that allows me to do the following: ... AccountCD.CD_symbol referencing 'Certificate of Deposit' and ... It is a matter of focus and scale. ...
    (comp.databases.theory)
  • Problem merging two queries
    ... CONSTRAINT PRIMARY KEY CLUSTERED ... REFERENCES ( ... CONSTRAINT FOREIGN KEY ...
    (microsoft.public.sqlserver.programming)
  • Re: Selecting data from one table and putting into another
    ... instead of using the tiertypecode from the tiertype table) this will give me ... ALTER TABLE.WITH NOCHECK ADD ... CONSTRAINT PRIMARY KEY CLUSTERED ... CONSTRAINT FOREIGN KEY ...
    (microsoft.public.sqlserver.programming)
  • Re: Selecting data from one table and putting into another
    ... here is the ddl for a good> portion of this, so if the name ever changed there would be a big problem> since the name is a foreign key reference to a parent table... ... > ALTER TABLE.WITH NOCHECK ADD> ... > CONSTRAINT PRIMARY KEY CLUSTERED> ... >) ON DELETE CASCADE ON UPDATE CASCADE> ...
    (microsoft.public.sqlserver.programming)
Loading