1

Suppose we have a table with 3 columns A,B and C

A      B      C
---------------
1      2      3
2      4      5
4      6      7
n      5      n

Here 'n' means null.

Can we say that A -> B and A -> C? I know the definition of functional dependencies but I'm just confused in the case of null values.

Michael Mior
  • 28,107
  • 9
  • 89
  • 113
Zephyr
  • 1,521
  • 3
  • 22
  • 42
  • 1
    Functional Dependencies are not defined in presence of null values. All the classical scientific literature on the subject of FD, as well as all the books on databases that threat FDs, are clear about the fact that the relational theory has been defined ignoring null values. Perhaps you can find something interesting for your question in a relatively recent paper: Badia, A., and D. Lemire. “Functional Dependencies with Null Markers.” The Computer Journal, May 15, 2014. https://doi.org/10.1093/comjnl/bxu039. – Renzo Oct 26 '17 at 15:53
  • Possible duplicate of [What to do with null values when modeling and normalizing?](https://stackoverflow.com/questions/40733477/what-to-do-with-null-values-when-modeling-and-normalizing) – philipxy Oct 27 '17 at 04:54
  • See my answers to the duplicate questions. If you really are dealing with FDs then "null" has to be just another value to = & join, *and you must ask your instructors how to apply them to SQL designs*. (At least, until I write another answer reorgainizing my answers at the links.) – philipxy Oct 27 '17 at 19:55

1 Answers1

1

If null is considered a value, then the answer is yes. A -> B, C holds in the given data. However, to be a value imposes certain requirements. All operators applicable to the domain (e.g. integers) like equality, addition, less than, and so on, must be well-defined in the presence of nulls.

If null is not a value, then the answer is more complicated. Functional dependencies, strictly speaking, apply to relations. If a table represents a relation, then we can refer to functional dependencies in the table. However, a symbol that represents the absence of a value is metadata, not data. It allows multiple union-incompatible relations to be represented by a single table. In this case, we can't apply the concept of functional dependency to the table since it's not clear which relation we're talking about.

Further confusing things, SQL DBMSs don't handle nulls consistently. In some cases, they're handled like values, in others like the absence of values. If you want to understand and describe a table logically, the best option is to decompose it into a set of null-free relations, and then to analyze each of those parts independently.

In the case of your example table, we run into a problem if null isn't a value. The last row has no unique identifier (it can't be B:4 since another row has B:4 as well) and we can't determine anything from a lack of information. The example can't be decomposed into a set of relations without discarding that row.

If we change the last row to have B:5 instead, then we decompose it into two relations: R1 = {(A:1, B:2, C:3), (A:2, B:4, C:5), (A:4, B:6, C:7)} and R2 = {(B:2), (B:4), (B:6), (B:5)}. We can say A -> B, C holds in R1 but not in R2.

reaanb
  • 9,806
  • 2
  • 23
  • 37
  • Sir actually I came across this problem when I was trying to merge two tables. I had relation R1(AC) with A->C and R2(B) and both were related with R1 side totally participating and R2 partially participating. In original R1(AC), A -> C was holding true. But after combining these 2 relations I was confused whether A->C still holds or not. – Zephyr Oct 26 '17 at 16:17
  • Also, if last row of B is 5, then B column can act as a primary key of merged table (i.e B -> AC ) right ? – Zephyr Oct 26 '17 at 16:45
  • Regardless of how you obtained your table, functional dependencies don't apply when non-value nulls are involved. – reaanb Oct 26 '17 at 16:46
  • Actually the question was minimum number of tables required for the relation to be in 1NF and 3NF. So for 1NF, I merged both the tables as both tables didn't have nulls originally and made B as PK.For 3NF, there was a problem because of null values obtained after merging because we take FD's into picture when checking for 3NF – Zephyr Oct 26 '17 at 16:48
  • A table containing non-value nulls isn't in any normal form. – reaanb Oct 26 '17 at 16:49
  • There were no nulls initially. After merging I got nulls because R2(B) was partially participating in the relation. – Zephyr Oct 26 '17 at 16:50
  • What was in R2(B) before the "merge"? If it was `{2, 4, 6}` then the "merge" was a non-relational operation. If it was `{2, 4, 6, 4}` then you didn't have a relation to start with since it had duplicate rows. – reaanb Oct 26 '17 at 16:54
  • B had {2,4,6,5} and the relation was , <1,2> <2,4> and <4,6>. All members of A participated (total) in relation but 5 in B didn't participate in the relation (partial participation) – Zephyr Oct 26 '17 at 17:02
  • If B:5 isn't in the relation, then where did you get nulls from? – reaanb Oct 26 '17 at 17:17
  • That's because I merged both the tables. So for {B:5}, we don't have any A value as it's not participating in the Relation. – Zephyr Oct 26 '17 at 17:19
  • Your merge isn't a relational operation. The result isn't a relation and functional dependencies are no longer relevant. – reaanb Oct 26 '17 at 17:20
  • Partial participation means a domain can contain values which don't appear in any rows in a relation. It doesn't mean there are nulls in the relation for the non-participating values. – reaanb Oct 26 '17 at 17:32
  • so the resulting table is not even in 1NF ? All the values are atomic right ? – Zephyr Oct 26 '17 at 17:34
  • 2
    If nulls aren't values then they aren't any kind of value, let alone atomic values. BTW atomic is misleading, a value can be as simple or complex as we want, as long as it's not a variable. – reaanb Oct 26 '17 at 17:38
  • @philipxy So after merging, it is still in 1NF but not in 3NF right ? – Zephyr Oct 27 '17 at 06:40
  • @Zephyr & reaanb "Null isn't a value" is nonsense. Null is just a value treated specially by SQL operators. At best that phrase pitifully informally misleadingly abbreviates "null isn't a value *like the ones that SQL operators treat the way non-SQL operators do*". *Saying* it isn't a value doesn't *make* it not a value. Null is a value used (in a certain way) with SQL operators in a design where (in a certain way) there is no appropriate non-null value--although a different (in a certain way) design with non-null values and SQL operators would have been fine – philipxy Oct 27 '17 at 20:08
  • @Zephyr "Merging" is not a technical term; maybe you mean join. Learn & use the right terms. If you are using SQL then "the definition of FD" doesn't apply; you can't interpret =, FD, superkey, CK, join, NF etc normally because `null` is treated specially by `=` & `join`; you can't interpret `primary key` as PK because it (only) means *`unique`* `not null`. Hence reaanb distinguishes "working relationally" vs working in SQL. You need to ask your instructors *what the hell they mean* when they say to "decompose" a *non-relation*, because they can't mean decompose--that applies to *relations*. – philipxy Oct 27 '17 at 20:19
  • That "decompose it into two relations" is not what is ever meant by decomposition. You haven't treated null as a value & haven't explained what it is you are doing instead. Also it is misleading & unclear to say "a symbol that represents the absence of a value is metadata, not data". Say what you mean, about what the given text (possibly) represents, clearly. PS Since your answer was posted, a question edit put a 5 where there was a 4 was in the last row. – philipxy Sep 09 '20 at 17:27