Take a relvar DOG with the attributes DOG_ID and DOG_NAME. Take another relvar DOG_TYPE containing attributes DOG_BREED and DOG_COLOR. Is it possible for these two relvars to have a referential relationship even though they have no attributes in common? I don't think so, but am unsure.

What's a "relvar"? I'm guesing its either the aliens that appear in South Park episodes, or perhaps a form of male enhancement medication?

The answer is yes, if "referential relationship" just means a multi-relvar constraint.

If "referential relationship" actually means "foreign key" then I'd say the answer is "perhaps". The term "foreign key" rarely seems to be precisely defined in textbooks. Most examples tend to assume the referencing and referenced attributes have the same name but there's no obvious reason why that MUST be so. Presumably they must at least be of the same type or have a common supertype. And then there is the somewhat arbitrary restriction that a foreign key must only reference a candidate key...

Thanks, very helpful. The thing that really threw me was that I have now learnt from you (and via other reading) is that attributes in foreign keys do not have to have the same name that the attributes in the associated primary key, they only need be of the same type. Almost every example I have seen shows the attribute names being the same so I assumed that this was necessary. However it seems you can reference keys regardless of the attribute names involved. Most books I have read failed to make this clear early on.

Take this example;
Relvar PLAYER contains attributes ID and TEAM_ID.
Relvar PLAYER_HISTORY contains attributes PLAYER_ID and SURNAME.

Now I previously thought that there was no way that these two relvars could have a referencing relatoinship as they had no common attributes. BUT it now appears that they can. ID and PLAYER_ID could have such a relationship if they are of the same type (which they are). It seems you can select keys and reference them however you want as long as the types are the same. The attribute names are immaterial. Do I have this correct?

Correct. The attribute names are unimportant in principle. In practice it may depend on the syntax of the language used to define a foreign key. A syntax that absolutely required names to be the same would not be very useful I think, but most examples and many database designers tend to use consistent names for attributes wherever they occur.

The standard Tutorial D doesn't have any special syntax for a referential constraint. It just has general-purpose relvar constraints.

SQL on the other hand has a syntax for a so-called "FOREIGN KEY" constraint that is not the same as what is usually understood to be a foreign key in the relational model: the SQL one can reference a super key whereas RM conventionally uses the term "foreign key" to mean a constraint that references a candidate key. This is a potential cause of confusion.

I should learn this stuff. Sometimes, I just don't know how I manage to do my job.

Thanks again, I am stumbling through this now. Here is another aspect that you may so kind as to clear up, forgive me if my question is nonsensical.

If we were to add another relvar to my database described above so we now have;
Relvar PLAYER contains attributes ID and TEAM_ID.
Relvar PLAYER_HISTORY contains attributes PLAYER_ID and SURNAME.
Relvar SCORING contains attributes PLAYER_ID and GOALS.

{ID}/{PLAYER_ID} is the glue (technical term) that holds this database together. How would the referencing of these 3 relvars be implented? Here are some ideas;

* SCORING{PLAYER_ID} references PLAYER_HISTORY{PLAYER_ID} which in turn references PLAYER{ID} - ie a chain.
* SCORING{PLAYER_ID} references PLAYER{ID} and PLAYER_HISTORY{PLAYER_ID} also references PLAYER{ID} - ie a Y shape.
* SCORING{PLAYER_ID} references PLAYER_HISTORY{PLAYER_ID} which in turn references PLAYER{ID} which in turn refernces SCORING{PLAYER_ID} - ie a ring.

Which of these relationship schemes would be used?

It depends on what these relvars mean. What is a PLAYER_HISTORY and how is it different from a PLAYER given that all it contains is an ID and a surname? Is it to cope with players who change their surnames over time? What are the keys of the PLAYER and PLAYER_HISTORY relvars?

Looking at it another way, who scored the SCORING: the PLAYER or the PLAYER_HISTORY?

OK I have been doing some more thinking and found some Webpages that clearly explain the basics about referencing. So here goes an attempt at structuring at my 3 relvar database above using keys that reference each other;

I would make PLAYER{ID} a primary key
I would make SCORING{PLAYER_ID} a foreign key that references PLAYER{ID}
I would also make PLAYER_HISTORY{PLAYER_ID} a foreign key that references PLAYER{ID}

Does this make sense?

If I am correct I can't believe my textbook made such a simple concept so incredibly complex. If I am wrong it is back to the drawing board.

however it good practice to make them sort of similar, but the overriding concern must be that the column name must be immediately obvious. As a general rule I wouldn't repeat the table namein the column defintion

eg
table: contacts
id
title
forename
...etc

rather than

table: contacts
contacts_id
contacts_title
contacts_forename
...etc

some people recommend the plural of the entity as the table name (eg contacts, containing details of multiple contact). the primary key (especially of autogenerated columns is ID for numeric), Code for alphanumeric

when used as a foreign key in the 'child' table it can become contact_id, as it immediately identifies that this column is the ID from table contacts.

but what is fundamenatl is whatever naming convention you use you are consistent. theres little point in referring to tel_no in one table and telephone_number in another, like wise be consistent with you underscores and camel casing.. some people like telno, some like tel_no, some like TelNo... its often personal (or site preference)....


HTH

yes, and don't you agree that it is actually rather simple

of course, all my experience is with tables and not "relvars," using SQL (ptui!!!) and not a relational database

i are deprived

Where did you learn databases: School of Hard Knocks, or University of Life?

;-)

on the job training -- started using SQL on DB2 in 1987

there were no courses in relational concepts back then

To sound like a broken record, I can't believe Date made such a simple concept so difficult to understand. I found one or two Websites that conveyed the basic concept of how referential relationships work in about 2 paragraphs that I understood in about 2 minutes. Now I have a basic understanding I can re-read Date and try to gain an appreciation of the finer details of the theory.

In theory, theory and practice are identical. In practice, theory and practice are incompatible.

-PatP