What Kind of Relationship is Between These Tables?

问题:

I have two tables that have foreign keys to each other's primary key. This DB is in French. I will translate the two tables that I want to you to understand.

• Atelier Cuisine ==> Kitchen
• Cuisinier == > Cooking chef

So in this picture we see that in the Kitchen table we have a PK referenced by the FK from the Cooking chef table; in the Cooking chef table we have a PK referenced by the FK from the Kitchen table. So I am confused. I don't understand this kind of relationship between these tables.

And I hope to check my query that I did to create these two tables if its correct

CREATE TABLE [ATELIER CUISINE] ( 

NumCuisine INT NOT NULL PRIMARY KEY,
TelCuisine VARCHAR(50) NOT NULL
)

CREATE TABLE CUISINIER (

NumCuisinier  INT NOT NULL PRIMARY KEY,
NomCuis  VARCHAR(50) NOT NULL,
DateEmb DATE NOT NULL,
NumCuisine INT NOT NULL CONSTRAINT FK_CUISINIER_NumCuisine FOREIGN KEY REFERENCES [ATELIER CUISINE](NumCuisine)

See the Image here:
Relationship model of the restaurant database

See the Image here:
Example records for some tables

回答1:

What you are seeing here is most probably the most important child pattern.

I would assume that we need to start from the standard parent-child relationship of "one kitchen employs one-or-more chefs". So we would have a foreign key in the cuisinier / chef table that contains the ID of the atelier_cuisine / kitchen where the cuisinierin question works.

But for some reason, the kitchen must be able to point at the most important cook/chef working there. Only that numCuisinier or chefID / cookID is a poor naming of that foreign key column, as it misleads, just as it mislead us in your example. If you gave it a name like numCuisinierChef or "chef/cook in chief ID", the column name would be self-explanatory.

But, the way the columns were named here, it could also be just the other way around: a chef/cook works in one or more kitchens, but one kitchen is his or her most important employer.

Without a documentation or comments in the data model, you're fried, really ...

Hope this helps rather than confuses - as the data model and the naming does ...

Maybe one clue. MarceloBarbosa actually inspired me to that: If , in atelier_cuisine , numCuisinier is null-able, and , in cuisinier, numCuisine is NOT NULL, then atelier_cuisine is the parent, and cuisinier is the child, and vice-versa.

Marco the Sane

回答2:

IMHO, this "key exchange" is plainly wrong; from the sample data we may conjecture (but not for sure!) that the relationship between [ATELIER CUISINE] and [CUISINER] is one-to-many (CUISINIER table), in which case the [ATELIER CUSINE] should NOT have the NumCuisinier column, or otherwise NumCuisine is not a Primary Key (which, according to the data model, is)! So the data model is probably wrong.

My best bet for your query would be

SELECT B.*, A.TelCuisine
FROM [ATELIER CUISINE] A INNER JOIN CUISINIER B ON A.NumCuisine = B.NumCuisine

Unless of course NumCuisinier in [ATELIER CUISINIER] table is NOT a FK, as @marcothesane suggests and it has a completely different meaning, in which case it should be contained in the result set (but with a different alias):

SELECT B.*, A.TelCuisine, A.NumCuisinier as [HeadChef]
FROM [ATELIER CUISINE] A INNER JOIN CUISINIER B ON A.NumCuisine = B.NumCuisine

回答3:

So you want to know how to query this.

As many said, it depends on what is needed.

And - philipxy - I can't regard a foreign key as a fact; you can't calculate the sum or the average or the standard deviation of foreign key values. And, for a Kimball-ian as me, facts are facts because you can do these things with them.

And a foreign key is the depiction at data definition language level of the relationships between the entities that are depicted by the tables. My point of view, at least. But, actually, who cares as long as it works - and as long as we don't create confusion.

Having said that:

If you need all kitchens' telephone numbers and their head cook's name:

SELECT
  telCuisine
, nomCuisinier
FROM atelier_cuisine
JOIN cuisinier USING(numCuisinier)
;

If you need all cooks (no matter if chief cook or anybody else) that you can reach with one phone number in one kitchen, you go:

SELECT
  nomCuisinier
FROM atelier_cuisine
JOIN cuisinier USING(numCuisine)
WHERE telCuisine = '[phone]';

If you want to know all phone numbers of the kitchens where Gaston works, you go:

    SELECT
      telCuisine
    FROM cuisinier
    JOIN atelier_cuisine USING(numCuisine)
    WHERE numCuisinier='Gaston'
;

I'm aware there is the possibility to use the ON clause for the joins, but I'm lazy and prefer the USING() clause ...

Other than that, I would not know what your question might be, really ...

回答4:

Tables (base and query results) represent application relationships.

From (an earlier version of) this answer:

For every base table, the DBA gives a predicate--a natural language fill-in-the-(named-)blanks statement template parameterized by column names.

-- chef with id NUMCUISINIER has name NOMCUIS and ...
CUISINIER(NumCuisinier, NomCuis, ...)

A base table holds the rows that, using its columns' values to fill in the (named) blanks, make a true statement aka proposition.

CUISINIER
NumCuisinier | NomCuis | ...
----------------------------
1            | DURAND  | ...  -- chef with id 1 has name 'DURAND' and ...
...

-- AND for every absent row (NUMCUISINIER, NOMCUIS, ...),
    NOT (chef with id NUMCUISINIER has name NOMCUIS and ...)

Each SQL expression/clause transforms an old table value to a new value holding the rows that make a true statement from some new predicate that can be expressed in terms of the original's predicate.

The predicate of R JOIN S is the predicate of R ANDed with the predicate of S. The predicate of R ON/WHEREcondition is the predicate of R ANDed with condition.

/* rows where
     chef with id c.NUMCUISINIER has name c.NOMCUIS and ...
AND kitchen with id a.NUMCUISINE ...
AND c.NUMCUISINE = a.NUMCUISINE
*/
CUISINIER c join ATELIER_CUISINE a on c.NumCuisine = a.NumCuisine

So we query by writing an SQL expression whose associated predicate characterizes the application relationship (aka association) whose rows we want.

FKs (foreign keys) are not relationships.

FK constraints are called relationships by some people, but they are not. They are facts.

A FK of a table is a set of columns. "FK" is also used to mean an associated constraint that we have when we have a FK. Which like every constraint is a true statement in every database state. (Equivalently, every application situation.) A FK constraint says that values for certain columns in a certain table are also values for certain columns in a certain other table where they form a CK (candidate key). (Equivalently, it says that if some values/entities satisfy a certain application relationship then some of them plus some other values/entities satisfy a certain other application relationship where the values/entities form a CK.) A PK (primary keys) is just some CK that you decided to call PK.)

A FK constraint has a certain associated application relationship, but that's not what is meant when "relationship" is used for "FK (constraint)". ("FK" is also used to mean a subrow value for the columns of a FK, or a value in a row for the column of a one-column FK.)

You need to know what each table means.

Predicates must be supplied by the designer along with a schema. You need to find out what the tables mean. Then express your query predicate in terms of them. Then convert to SQL.

Sometimes we guess at the predicates more or less successfully via common sense and naming. FKs and other constraints can help with guessing.

Common sense, names, PKs (underlined?) and FKs ("#"?) suggest table meanings for you like:

-- chef with id NUMCUISINIER has name NOMCUIS and start date DATEEMB and works in kitchen with id NUMCUISINE
CUISINIER(NumCuisinier, NomCuis, NateEmb, NumCuisine)
-- kitchen with id NUMCUISINE has phone number TELCUISINE and chef with id NUMCUISINIER as head chef
ATELIER_CUISINE(NumCuisine, TelCuisine, NumCuisinier)

FKs are not needed to query

In the SQL query above the rows that make the predicate into a true proposition are always the rows returned. It doesn't matter how many rows there are per NumCuisiner or NumCuisine value (ie whether they are PKs) or whether a value must appear in another table (ie whether they are FKs). Or what any other constraint is.

We need to know the predicates to query. When we know them we don't need to know any constraints. We don't need to know FKs.

FKs, CKs, PKs, alternate keys and UNIQUE column sets (superkeys) are irrelevant to querying except that if you know something is a superkey because of one then you can write queries involving extracting a value from a one-row result. But you could have expressed the same result without extractions.