Can one attribute in the functional dependency be null?

Question

I have a set of functional dependencies F, R = {cid, cname, bid, name, rentdate, returndate, cost} in a bookstore, there is just one table of it.

customerid, bookid, bookname, rent and return date of this book by this person.

Obvious, it's not BCNF

but how to identify the F of non-trival functional dependencies for this?

in my opinion:

cid -> cname

bid -> bname

bid, rentdate -> returndate, cid

is that ok? in the last functional dependencies, i think each order, one book be rented in a specific time, will have the unique return date and belongs to just one person

but I am also confused about this Functional dependencies, because in this table, the rentdate and returndate can also set to be null!!!

in this way, does the

bid, rentdate -> returndate, cid

correct?

Answer 1

Codd would simply have excluded tuples with nulls from the application of functional dependencies. As far as I know, there is no self-consistent way to cope with functional dependencies in pure SQL, using Codd's 3-value logic.

I would therefore expect most people to tell you to avoid nulls. Obviously, that is not always received as a practically useful recommendation.

There has been academic work on the topic however if you are interested. We have a paper that covers specific this issue:

Antonia Badia and Daniel Lemire, Functional dependencies with null markers, Computer Journal The Computer Journal (2015) 58 (5): 1160-1168. http://arxiv.org/abs/1404.4963