这些表格之间有什么样的关系？ [英] What Kind of Relationship is Between These Tables?

问题描述

我有两个表中有对外的主键的外键。这个DB是法文的。我将翻译我想要了解的两张表。

• 工作室菜式==​​>厨房
  
• Cuisinier ==>烹饪厨师

所以在这幅图中我们看到在 code>表中，我们有一个由烹饪厨师表中的FK引用的PK;在烹饪厨师表中，我们有一个由 Kitchen 表中的FK引用的PK。所以我很困惑。我不明白这些表之间的这种关系。



我希望检查我的查询，我做了这两个表，如果它正确的

$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $
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外国关键参考[ATELIER CUISINE]（NumCuisine）


在这里查看图片：

餐馆数据库的关系模型

在这里查看图片：

一些表格的记录示例

解决方案

$ b $ p 表（基本和查询结果）表示应用程序关系。

（早期版本）这个答案：

对于每个基表，DBA给出一个谓词 - 由列名参数化的自然语言fill-in-（named-）blanks语句模板。 b $ b

   - 带ID的厨师NUMCUISINIER有名字NOMCUIS和... 
 CUISINIER（NumCuisinier，NomCuis，...）
  

$保存行，使用列的值填充（命名）空白，做一个真正的声明，也就是命题。 b $ b

CUISINIER
NumCuisinier | NomCuis | ...
----------------------------
1 | DURAND | ... - ID为1的厨师有名字DURAND和...
...

- 对于每一个缺席的行（NUMCUISINIER，NOMCUIS，...）
NOT（厨师ID为NUMCUISINIER，名称为NOMCUIS ...）


每个SQL表达式/子句都将旧的表值转换为新的值，该值保存了一些新的谓词，这些新的谓词可以用原始的谓词来表示。

R JOIN S 是谓词 R AND ，谓词 S 。谓词 R ON / WHERE condition 是 R AND 使用 条件 。 >






/ * rows其中
带ID的厨师c.NUMCUISINIER有名字c.NOMCUIS和...
和厨房用id a.NUMCUISINE ...
和c.NUMCUISINE = a.NUMCUISINE
* /
CUISINIER c加入ATELIER_CUISINE a因此，我们通过编写一个SQL表达式来进行查询，这个SQL表达式的关联谓词描述了这个表达式的含义。应用程序关系（又名关联），我们想要的行。

FK不是关系。

FK约束被一些人叫做关系，但它们不是。它们是事实。

表格的FK是一组列。 FK也用来表示我们拥有FK时的相关约束条件。每个约束都是每个数据库状态中的真实语句。 （相当于每个应用程序的情况。）FK约束表示某个表中某些列的值也是某个其他表中某些列的值。 （等价地说，如果某些值/实体满足特定的应用程序关系，那么其中一些值/实体满足某种其他应用程序关系。）

A FK约束具有某种相关的应用关系，但当关系用于FK（约束）时，这不是什么意思。 （FK也用于表示FK列的子值，或者表示一列FK列的值。）

您需要知道每个表的含义。

必须由设计者提供谓词以及模式。你需要找出这些表的含义。然后用它们表达你的查询谓词。然后转换为SQL。

有时，我们可以通过常识和命名或多或少地对谓词进行猜测。 FKs和其他约束可以帮助猜测。

常见的名称，PK（加下划线）和FK（＃？）建议您的表格含义如下：

 pref =lang-none prettyprint-override>   - 带ID的厨师NUMCUISINIER有名字NOMCUIS和开始日期DATEEMB （NumCuisinier，NomCuis，NateEmb，NumCuisine）
  - 有ID的厨房NUMCUISINE有电话号码TELCUISINE和有NUMCUISINIER的厨师作为主厨
 ATELIER_CUISINE（NumCuisine ，TelCuisine，NumCuisinier）
  

FK不需要查询

在上面的SQL查询中，将谓词转换为真命题的行始终是行返回的行。无论每个 NumCuisiner 或 NumCuisine 值（即它们是否是PK）或一个值必须出现在另一个表中（即它们是否是FK）。或者有什么其他的约束。

我们需要知道要查询的谓词。当我们知道他们时，我们不需要知道任何约束。我们不需要知道FK。

FK，PK，备用键和UNIQUE列集（超级键）与查询无关，只是如果你知道某个东西是一个超级键，那么你可以写查询从一行结果中提取一个值。但是你可以表达同样的结果，不用提取。

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

解决方案

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

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, 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.

这篇关于这些表格之间有什么样的关系？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！