Definition for “logical difference”

The question you raise has to do with the definition of first normal form (1NF). Whether the answer directly involves functional dependencies depends in part on the definitions you accept. Wikipedia has a fairly simple article about 1NF.

title                                author    year  category   
--
An Introduction to Database Systems  CJ Date   2003  databases, modeling, storage, retrieval

If you look at the column "category" one way, it contains a single value. Depending on your dbms and your design, that value might be the string "databases, modeling, storage, retrieval", or it might be the array "{databases, modeling, storage, retrieval}".

If you look at the column "category" another way, it contains four values. Those values are the four strings "databases", "modeling", "storage", and "retrieval".

In database design, the solution is to use two tables. But I don't think you can decompose the "bad" table by projection (which CJ Date identifies as the decomposition operator), because projection doesn't split the content of a column into multiple rows. (Projection doesn't give you four rows from the single value "databases, modeling, storage, retrieval", which is what you need to do. "Join", the recomposition operator, doesn't yield a single value like "databases, modeling, storage, retrieval", either.)

The inability to decompose by projection suggests that the solution to this problem doesn't have to do with functional dependencies. The resulting table would have three attributes, {title, author, category}, the only key would also be {title, author, category}, and that table would be in 5NF.

There's a little aphorism that goes:

• The key (1NF)
• The whole key (2NF)
• And nothing but the key (3NF)

. . . So help me Codd

In your example we can assume 1NF to begin with as the relational structure doesn't imply any repeating groups within the row (i.e. no D₁, D₂, D₃ etc.).

R = {a, b, c, d, e, f, g} F = {AB --> C, A --> DE, B --> F, F --> GH, D --> IJ }

2NF deals with 'the whole key' - if you have a composite key and some members of the relation are dependent on a part of that key then they should be split off into their own relations. In your case:

• R1 = AB --> C is in 2NF as 'AB' is 'the whole key'

• R2 = A --> DEIJ is 2NF but not 3NF as A --> DE, but A --> IJ is not 3NF as the real dependency of I and J is D --> IJ. D is not a key of this relation (A --> DEIJ), so A --> IJ violates the 'nothing but the key' princple of 3NF.

• R3 = B --> FGH is in 2NF but not 3NF as B --> F is a correct relation and F is dependent on the entirety of the key 'B'. However, B --> GH is not in 3NF as the correct relation is F --> GH. B --> GH violates the 'nothing but the key' principle, as G ahd H are correctly dependent on the non-key attribute F.

Your decomposition to 2NF is correct. Decomposition to 3NF requires taking the non-key attributes that have their own dependencies into separate relations.

The relation in 3NF would look like:

• R1 = AB --> C

• R2 = A --> DE (I and J are dependent on the non-key attribute D)

• R3 = B --> F (G and H are dependent on the non-key attribute F)

• R4 = D --> IJ

• R5 = F --> GH