What is the relationship between these tables?

Question

I have two tables that have foreign keys to each other. This database is in French. I will translate two tables that I want to understand for you.

• Kitchen Atelier ==> Kitchen
• Cuisinier ==> Culinary Chef

So, in this figure, we see that in the Kitchen table we have PK, which is referenced by FK from the Cooking chef table; in the Cooking chef table, we have PK referenced by FK from the Kitchen table. Therefore, I am confused. I do not understand this relationship between these tables.

And I hope to check my query, which I made 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 image here:
Restaurant Database Interaction Model

See image here:
Sample entries for some tables

Answer 1

What you see here is most likely the most important child template.

I would suggest that we need to start with the standard parent-child relationship "one kitchen uses one or more chef s". Thus, we will have a foreign key in the cuisinier / chef table, which contains the identifier atelier_cuisine / kitchen where cuisinier works.

But for some reason, the kitchen should be able to point to the most important cook / chef working there. Only this numCuisinier or chefID / cookID is the bad name of this foreign key column, because it is misleading, just as it is misleading to us in your example. If you gave it a name like numCuisinierChef or "chef / cook in Chief ID", the column name would be taken for granted.

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

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

Hope this helps, not embarrasses - like data and naming models ...

Maybe one key. Marcelo Barbosa really inspired me to do this: If atelier_cuisine , numCuisinier not valid, and in cuisinier , numCuisine not NULL, then atelier_cuisine is parent and cuisinier is a descendant and vice versa.

Marco Saun

Answer 2

Tables (database and query results) represent application relationships.

From (earlier version) this answer :

For each base table, the database administrator provides a predicate, a natural language fill wording (named-) template, parameterized by column names.

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

The base table contains rows that, using the values ​​of their columns, fill in (named) spaces, make a true statement as a sentence.

 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 statement / statement converts the value of the old table to a new value containing strings that make a true statement from some new predicate that can be expressed in terms of the original predicate.

The predicate R JOIN S is the predicate R AND ed with the predicate S The predicate R ON/WHERE condition is the predicate R AND ed 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 

Thus, we request, by writing an SQL expression, a predicate associated with it characterizing the relationship of the application (aka association) whose rows we want.

FKs (foreign keys) are not relationships.

FK restrictions are called some people's relationships, but they are not. These are the facts.

A FK table is a collection of columns. “FK” is also used to denote the associated restriction that we have when we have FK. Which, like any restriction, is a true statement in every state of the database. (Equivalent, each application situation.) The FK constraint says that values ​​for certain columns in a specific table are also values ​​for certain columns in some other table, where they form CK (candidate key). (Equivalently, he says that if some values ​​/ entities satisfy certain application relationships, then some of them plus some other values ​​/ entities satisfy some other application relationship, where the values ​​/ entities form CK.) PK (primary keys) are just some CK, which you decide to call PK.)

The FK constraint has a certain relation to related applications, but this does not mean that the relation is used for the FK (restriction) relation. ("FK" is also used to indicate the substring value for FK columns or the value in a row for a single-column FK column.)

You need to know what each table means.

Predicates must be supplied by the designer along with the 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 predicates more or less successfully through common sense and naming. FCs and other restrictions can help guessing.

Common sense, names, PKs (underlined?) And FKs ("#"?) Offer you table values:

 -- 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) 

FK not needed for request

In the SQL query above, the rows that predicates enter in the true statement always return rows. It does not matter how many rows exist for the NumCuisiner or NumCuisine (i.e., Are they PK) or should the value appear in another table (i.e., are they FK). Or any other restrictions.

We need to know the predicates for the query. When we know them, we do not need to know any restrictions. We do not need to know FK.

FKs, CKs, PK, alternative keys and UNIQUE columns (superclasses) are not relevant to the request, except that if you know that something is a superkey because of one, then you can write queries related to the extraction of the value from a single row result. But you could express the same result without any extra exits.

Answer 3

IMHO, this "key exchange" is clearly incorrect; from sample data, we can assume (but not necessarily!) that the relationship between [ATELIER CUISINE] and [CUISINER] is a one-to-many relationship (CUISINIER table), in which case [ATELIER CUSINE] should NOT have a NumCuisinier column, or otherwise, NumCabin is not a Primary Key (which, according to the data model, is)! Thus, the data model is probably incorrect.

My best bet for your request would be

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

Unless, of course, the NumCuisinier in the table [ATELIER CUISINIER] is NOT 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 

Answer 4

So you want to know how to request this.

As many say, it depends on what you need.

And - philipxy - I cannot consider a foreign key as a fact; you cannot calculate the sum or the mean or standard deviation of foreign key values. And for Kimballyan, like me, facts are facts, because you can do all this with them.

And the foreign key is a language-level image of the definition of these relations between entities, which are represented by tables. My point, at least. But, in fact, who cares as long as it works - and until we create confusion.

Having said that:

If you need the phone numbers of all kitchens and the name of their chief cook:

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

If you need all the cooks (regardless of the chief chef or someone else) with whom you can contact 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 the phone numbers in the kitchen where Gaston works, you go:

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

I know that it is possible to use the ON clause for connections, but I am lazy and prefer the USING () clause ...

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