DATE in sql and relation Algebra is NULL?

Question

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}

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)

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

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

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

But how to create the RA?

It should work with a left join; `SELECT Name FROM CUSTOMERS C LEFT JOIN Order O E ON O.CID = C.CID WHERE O.shipdate is Null` — Joachim Isaksson, May 21 '14 at 18:55
@JoachimIsaksson I have updated my question and now I think thats rigth but I stuck now to create a RA — ThePom, May 21 '14 at 19:35
Note that [explicit joins](http://stackoverflow.com/q/5654278/) are generally preferred over implicit joins. — outis, May 26 '14 at 05:01

Serpiton · Answer 1 · 2014-05-21T19:24:28.987

0

There is no need of the table CUSTOMER, just get the prodno not in Ordered, unless you want to know which product each customers have never ordered.

In SQL

SELECT Prodno
FROM   Products 
WHERE  Prodno NOT IN (SELECT Prodno
                      FROM   Ordered)

or the equivalent

SELECT Prodno
FROM   Products 
EXCEPT 
SELECT Prodno
FROM   Ordered

edited May 21 '14 at 19:24

answered May 21 '14 at 19:07

Serpiton

3,676
3
24
35

ok cool I also update my question so is also correct yes or ? – ThePom May 21 '14 at 19:12
how now we ask it twice in the same table or not? – ThePom May 21 '14 at 19:23
I think we have to add the SELECT Product if we consider your first ex. – ThePom May 21 '14 at 19:24
so the RA should like πP.prodno(Product)-(πOrdd.prodno(Ordered) but I am not sure – ThePom May 21 '14 at 19:34

score 0 · Answer 2 · answered May 21 '14 at 19:10

0

Yes, though the table schema has to declare the column as nullable. If you're creating a new table, you'll need to do something like

create table Order
(
  order_id  int      not null primary key ,
  ship_date datetime     null ,
  ...
)

If you are modifying an existing table, you'll need a magic incantation like

alter table Order alter column shop_date datetime null

The exact syntax may vary, depending on your particular flavour of SQL.

answered May 21 '14 at 19:10

Nicholas Carey

71,308
16
93
135

I have updated my question o I think now I should get the prodno and P.name but how the RA.. – ThePom May 21 '14 at 19:20

score 0 · Answer 3 · answered May 21 '14 at 23:43

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.

check if the Order.orderid is not equal to Ordered.orderid and Porduct.prodno is not equal to Ordered.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.

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

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

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

IMO the title is tied with the assumption of the first query in the question, after the other answers the OP edited with the query you translated in RA — Serpiton, May 22 '14 at 00:36

DATE in sql and relation Algebra is NULL?

3 Answers3

Linked