1

I am having major difficulties understanding functional dependencies just by looking at abstract examples like this:

R(A,B,C,D,E)
AB -> C, DE -> C, and B -> D

So I seek feedback on corresponding real life situations.

I want to see if these functional dependencies violate BCNF.

R(A,B,C,D,E): Employee (employee_id, firstname, lastname, title, department)

AB -> C: employee_id, firstname -> lastname
lastname can be determined by employee_id and firstname. Left hand side is a key so no BCNF violation?
DE -> C: title, department -> lastname
lastname of a specific employee cannot be determined by title and department. The left hand side contains no key attribute so BCNF violation?
B -> D: firstname -> title
title of employee cannot be determined by only firstname, because there can exist several employees with same firstname? And the left hand side has no key attribute so BCNF violation?

For these FDs for the above relation, MUST it be the same result on every example that I test with? Or does it depend on each relation and what attributes they have, and in which order?

Another example with the same FDs and attributes.

R(A,B,C,D,E): Movie (movieName, published, starName, age, address)

AB -> C: movieName, published -> starName
starName can be determined from movieName and published. No BCNF violation?
DE -> C: age, address -> starName
starName cannot be determined from age and address. BCNF violation?
B -> D: published -> age
age of the movie can be determined from published. No BCNF violation?

philipxy
  • 14,867
  • 6
  • 39
  • 83
jabbeboy
  • 289
  • 2
  • 5
  • 14
  • After each FD it's very unclear what you are trying to say about things being determined or not, what that has to do with the FD, what your reasoning is re BCNF & what you think it means for BCNF to be violated or not. – philipxy Aug 05 '22 at 10:24

1 Answers1

1

You do not understand the definitions of "functional dependency" or "BCNF". You need to memorize the definitions, including the definitions of the terms that the definitions use.

Functional dependency is not about "determining" one thing from another in some everyday sense.

In a given table a FD holds when each subrow value for the determinant attribute set always appears with the same subrow value for the determined attribute set. A table is in BCNF when every determinant of a non-trivial FD is a superkey. (Learn the definitions of those terms.)

In practice we determine what columns are functionally determined by other columns for a given table, then do the math, then use what comes out. There's not much to learn from a complicated example. Each example table you give in your question has a certain meaning and, given what situations arise, it does not have that set of FDs. Using common sense, in Employee, employee_id determines all attributes, and probably no other FDs hold other than the ones that must hold when those ones do. Using common sense, in Movie, many movies can have the same name and the only FDs are {published} -> {age} and those that follow from it.

Any time you do have a relation "R(A,B,C,D,E) where functional dependencies are AB -> C, DE -> C, and B -> D" and those that they imply (presumably R, A, B, C, D & E are placeholders for the actual names) then the candidate keys and superkeys and whether it is in BCNF and how you can decompose it will always be the same (in terms of A, B, C, D & E).

philipxy
  • 14,867
  • 6
  • 39
  • 83
  • Aha okey. So that is only valid for that relation with tha FD, but it will most likely differ if the FD would instead be: AB->C, C->E, D-B for example? Yes i wiill probably be spendninng alot of hours trying to understand these definitions and all as much as can. Thanks – jabbeboy Dec 07 '16 at 13:34
  • See http://stackoverflow.com/a/27504915/3404097. Also my extended answer. There's no substitute for memorizing definitions, theorems/rules & algorithms, but examples and practice help a lot. – philipxy Dec 07 '16 at 13:34
  • *What* is only valid for that relation & FDs? *What* would differ if the FDs AB->C, C->E, D->B held instead? If you mean the possible BCNF decompositions then yes. But you don't need to know how the FDs arose to use the decomposition algorithm. – philipxy Dec 09 '16 at 23:31