关系代数中的 Null

问题描述

我想查询所有从未租过的公寓的id

I want to query the id of all apartments that were never rented

我尝试过这样的事情:

(π a_id
(apartments))
-
(π a_id
σ from_date Exists ∧ end_date Exists
(rental) ⨝ rental.a_id on apartment.a_id (apartment))

但我认为我不能在关系代数或 null 或任何东西中使用 Exist.

But I think I cannot use Exist in relational algebra or null or anything.

我该怎么做?

谢谢

我在此处附上架构

推荐答案

我们如何查询 &基于表中行的含义的原因

对于最直接的关系代数，其中关系的属性设置为heading &元组设置为正文:

For the most straightforward relational algebra, where a relation has an attribute set as heading & tuple set as body:

每个查询表达式都有一个关联的(特征)谓词--fill-in-the-(命名)-blanks 语句模板，由属性参数化.使谓词成为真正的命题--语句--的元组在关系中.我们不能在没有被告知每个基本关系的谓词的情况下更新或查询业务情况.

Every query expression has an associated (characteristic) predicate--fill-in-the-(named)-blanks statement template parameterized by attributes. The tuples that make the predicate into a true proposition--statement--are in the relation. We cannot update or query about the business situation without being told each base relation's predicate.

我们得到了关系(值或变量)名称表达式的谓词.

We are given the predicates for expressions that are relation (value or variable) names.

让查询表达式 E 有谓词 e.然后:

Let query expression E have predicate e. Then:

• R ⨝ S 有谓词r and s
• R ∪ S 有谓词r or s
• R - S 有谓词 r 而不是 s
• σ p (R) 有谓词r and p
• π A (R) 有谓词 exists R 的非 A 属性 [r]

• R ⨝ S has predicate r and s
• R ∪ S has predicate r or s
• R - S has predicate r and not s
• σ p (R) has predicate r and p
• π A (R) has predicate exists non-A attributes of R [r]

给定一个关系代数查询，我们可以从叶子向上应用上述事实来获得其谓词的表达式.给定一个谓词表达式，我们可以类似地将上述事实应用到另一条路上；如有必要，我们首先重新排列为等效的表达式，其中每个 ∪ &- 有 2 个具有相同属性的参数.(它总是可以将每个 Codd 的归约算法重新排列为某个 prenex 范式.)

Given a relational algebra query, we can apply the above facts from the leaves up to get an expression for its predicate. Given a predicate expression, we can similarly apply the above facts to go the other way; if necessary, we first rearrange to an equivalent expression where every ∪ & - has 2 arguments with the same attributes. (It always works to rearrange per Codd's reduction algorithm to a certain prenex normal form.)

重新关系代数查询.

将其应用于您的查询

Apartment 中的公寓 id 大概是用于公寓 &租赁中的公寓 ID 用于出租公寓.然后未出租的公寓是Apartment 中的公寓，而不是Rental 中的公寓.它们的 ID 是那些基本关系的投影之间的关系差异中的 ID.

Presumably apartment ids in Apartment are for apartments & apartment ids in Rental are for rented apartments. Then the unrented apartments are the ones in Apartment but not in Rental. Their ids are the ones in a relational difference between projections of those base relations.

猜测您的 ERD 的图例/键，租赁引用公寓中有一个 FK(外键).这证实了Rental 中的公寓也在Apartment 中.所以Apartment ⨝ Rental 与Rental 具有相同的公寓.这确认您不需要加入；您可以只使用租来的公寓.

Guessing at the legend/key for your ERD, there is a FK (foreign key) in Rental referencing Apartment. That confirms that an apartment in Rental is also in Apartment. So Apartment ⨝ Rental has the same apartments as Rental. That confirms that you don't need to join; you can just use Rental for rented apartments.

你提到了 NULL &存在.也许你在谈论 SQL NULL &EXISTS 和/或您正在尝试查找 SQL 查询的关系代数版本和/或您正在 SQL 中进行推理.和/或也许你在谈论逻辑存在 &列或元组中是否存在值.

You mention NULL & EXISTS. Maybe you are talking about SQL NULL & EXISTS and/or you are trying to find a relational algebra version of an SQL query and/or you are reasoning in SQL. And/or maybe you are talking about logic EXISTS & whether values exist in columns or tuples.

从关于租房的常识开始根据您的说法，Rental 可能是 入住者 O 从日期 F 到日期 T 租用公寓 A 的行.但是你提到NULL.从常识 &猜测 T 可以为 NULL，Rental 似乎是 从日期 F 到日期 T OR 居住者 O 从日期 F 租用公寓 A 的行 &T 为空.

From common sense about renting & from you not saying otherwise, Rental might be rows where occupant O rented apartment A from date F to date T. But you mention NULL. From common sense & guessing T can be NULL, Rental seems to be rows where occupant O rented apartment A from date F to date T OR occupant O rented apartment A from date F ongoing & T is null.

NULL 是一个被 SQL 运算符特别处理的值 &句法.我们不知道你的代数和语言处理 NULL.在数学中 EXISTS X [p] &FOR SOME X [p] 说存在一个值，我们可以命名满足条件 p 的 X.SQL EXISTS (R) 表示表 R 中是否存在行.即EXISTS t [t IN R].当 R 为 (X,...) 行其中 r 时，即是否EXISTS X,... [r].

NULL is a value that is treated specially by SQL operators & syntax. We don't know how your algebra & language treat NULL. In mathematics EXISTS X [p] & FOR SOME X [p] say that a value exists that we can name X that satisfies condition p. SQL EXISTS (R) says whether rows exist in table R. That is whether EXISTS t [t IN R]. When R is (X,...) rows where r, that is whether EXISTS X,... [r].

当 R 是其中 r 的行时，π x (R) 根据定义是行，其中 EXISTS R 的非 x 属性 [r].所以 π A (Rental) 是 EXISTS O,F,T [居住者 O 从日期 F 至今租用的公寓 A T OR 居住者 O 从日期 F 租用的公寓 A 正在进行中的行 &T 为空].

When R is rows where r, π x (R) is by definition rows where EXISTS non-x attributes of R [r]. So π A (Rental) is rows where EXISTS O,F,T [occupant O rented apartment A from date F to date T OR occupant O rented apartment A from date F ongoing & T is null].

当 R 是其中 r 的行时，σ p (R) 根据定义是行，其中 r &.居住者 O 从日期 F 开始租用公寓 A 的行T 为空 是 (居住者 O 从日期 F 到日期 T 租用公寓 A 或居住者 O 从日期 F 租用公寓 A 进行中 & T 为空)&T 为空.那就是 σ T 为空(出租).

When R is rows where r, σ p (R) is by definition rows where r & p. Rows where occupant O rented apartment A from date F ongoing & T is null is rows where (occupant O rented apartment A from date F to date T OR occupant O rented apartment A from date F ongoing & T is null) & T is null. That's σ T is null (Rental).

当 R 是 r & 的行时S 是行，其中 s，R - S 根据定义是行，其中 r &不是 .假设 Apartment 是 apartment A 有 S 平方英尺的行.......您需要 EXISTS S,... [公寓 A 有 S 平方英尺...] & 的行NOT EXISTS O,F,T [居住者 O 从日期 F 至今租用的公寓 A T 或居住者 O 从日期 F 租用的公寓 A 正在进行中&T 为空].那是 π A(公寓) - π A(出租).这就是本节开头的关系差异.

When R is rows where r & S is rows where s, R - S is by definition rows where r & NOT s. Suppose Apartment is rows where apartment A has S square feet .... You want rows where EXISTS S,... [apartment A has S square feet ...] & NOT EXISTS O,F,T [occupant O rented apartment A from date F to date T OR occupant O rented apartment A from date F ongoing & T is null]. That's π A (Apartment) - π A (Rental). That's the relation difference at the start of this section.

这篇关于关系代数中的 Null的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！