sql和关系代数中的日期为NULL? [英] DATE in sql and relation Algebra is NULL?

问题描述

我有一个问题，是否可以在 NULL 表中设置日期(在我的情况下，发货日期不存在)?例如我有以下表格:

I have a questions is it possible to set a date in tables NULL(in my case that a shipdate does not exist)? E.g I have this following tables:

Customer{cid,name}
Product{prodno,name}
Order{orderid, shipdate, cid}
Ordered{orderid, prodno, quantity}

现在我想获得所有从未订购过的产品.所以我在 RA(关系代数)中创建了这个:

And now I wanto to get all Products which were never ordered. So I create this in RA(Relational Algebra):

  πP.name,P.prodno(σO.cid = C.cid AND Order.shipdate is NULL(Order)))⋈Customer)

我确定它在 SQL 查询中查找:

I am sure that it looks in SQL query:

  SELECT P.Name, P.Prodno 
  FROM CUSTOMERS C, Order O E, Ordered Ordd Product P
  WHERE O.CID = C.CID
  AND O.shipdate is Null

所以我认为如果日期在 SQL 中是可能的，那么我可以获得所有未订购的产品.也许可以用 Ordered 表来做到这一点，但是如果我检查 Order.orderid 是否不等于 Ordered.orderid 并且 Poduct.prodno 不等于 Ordered.prodno 不知道如何在 RA 中创建它.但是只获得产品是不是很复杂，所以我认为我的 RA 和 SQ 可能是对的，还是?

so I think if date is could possible in SQL than I can get all products that are not ordered. maybe it could be possible to do it with the table Ordered but how maybe if I check if the Order.orderid is not equal to Ordered.orderid and Porduct.prodno is not equal to Ordered.prodno not sure how so create it in the RA. But is to complex for only getting the Products, so I think my RA and SQ could be right or?

2.更新

 SELECT prodno, name
 FROM Products P, Order O Ordered Ordd
 WHERE prodno NOT IN (
     SELECT prodno FROM Ordered 

但是如何创建 RA?

推荐答案

显然 Product(prodno,name) 的意思是product [prodno] 被命名为 [name]"，而 Ordered(orderid, prodno,quantity) 的意思是order [orderid]] 用于产品 [prodno] 的数量 [quantity]".但是你需要把这些意思说清楚.

Apparently Product(prodno,name) means "product [prodno] is named [name]" and Ordered(orderid, prodno, quantity) means "order [orderid] was for quantity [quantity] of product [prodno]". But you need to make such meanings clear.

检查 Order.orderid 是否不等于 Ordered.orderid 并且Product.prodno 不等于 Ordered.prodno

check if the Order.orderid is not equal to Ordered.orderid and Porduct.prodno is not equal to Ordered.prodno

这还不够清楚，无法对问题进行推理.条件并不总是对应于选择.努力找到您想要的清晰、完整的自然语言描述.已订购产品是出现在已订购产品中的产品，未订购产品是仅出现在产品中的产品.显然你想要 prodno 和未订购产品的名称.

That is just not clear enough to reason about the problem. Conditions don't always correspond to selections. Work to find a clear, full natural language description of what you want. Ordered products are those that appear in Ordered and unordered products are those that only appear in Product. Apparently you want prodno and name of unordered products.

我们使用代数减法从另一个关系中删除一个关系的元组.但是减法参数需要相同的属性.产品的 prodnos 是 π prodno Product，有序产品的 prodnos 是 π prodno Ordered.所以无序的 prodno 是 π prodno Product - π prodno Ordered.但是您想要带有这些 prodno 的产品的 prodno 和名称:

We use algebraic substraction to remove a relation's tuples from another's. But subtraction arguments need the same attributes. The prodnos of products are π prodno Product and the prodnos of ordered products are π prodno Ordered. So unordered prodnos are π prodno Product - π prodno Ordered. But you want the prodno and name of the products with those prodnos:

(π prodno Product - π prodno Ordered) ⋈ Product

这不涉及客户.

这不涉及订单或其发货日期.(不要将发货的产品与订购的产品混淆.)

This doesn't involve orders or their shipment dates. (Don't confuse shipped products with ordered products.)

但是，您的问题主题涉及日期和 NULL.所以我可能没有真正理解你的问题.如果订单发货日期可以为空，那么这是一个不同的问题.(您必须决定什么关系对应于带有 NULL 的 SQL 表，并且您必须在代数运算符和 SQL 运算符之间的转换中使用 NULL.)

However, your question topic involves dates and NULL. So I may not really have understood your question. If Order shipdate is nullable then that is a different issue. (You have to decide what relation corresponds to an SQL table with NULLs and you have to involve NULL in translating between algebra operators and SQL operators.)

这篇关于sql和关系代数中的日期为NULL?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！