Functional Dependencies and BCNF

Question

Consider the following schema: Students(Id, Name, AdvisorId, AdvisorName, FavouriteAdvisorId)

What are the functional dependencies here? Ir seems that it would be:

Id --> Name AdvisorId AdvisorName FavouriteAdvisorId

If we know the student id then we know his name, his advisor, his advisor id and his favouriteadvisorid. But the correct functional dependencies given are:

Id ---> Name FavouriteAdvisorId

AdvisorId ---> AdvisorName

Why is this?

Answer 1

Perhaps because a student with two majors might have an advisor in each department, so the ID wouldn't uniquely determine the advisor (and hence the AdvisorID and, perhaps, the AdvisorName). Remember, the definition of $A\rightarrow B$ is that if two tuples in the relation have the same value for attribute $A$, they must have the same value for attribute $B$.

Answer 2

i am trying it out:

Lets make it simple-

R(A,B,C,D,E)

    A-> B E
    C-> D

So according to the correct FD: A,C is not getting derived in RHS, so you can consider AC to be the Candidate key.So, combine them,you can derive the rest data in the relation.So, u can break the relation as :

   R1(A,B,E)
   R2(C,D)

Its BCNF as def: x->a , x should be a super key. So in both the FD , LHS is a super key/candidate key.

Now, your case

why not:

    A-> B C D E 

you can think its in BCNF, but the basic criteria of FD is canceled. as:

  A    B          C    D             E

  A1  SAM WATSON  C1   PETER PARKER  C1
  A1  SAM WATSON  C2   BRUCE WAYNE   C1

a Student can have exactly 2 different advisers.

ATD: x-> a = x is functionally dependent to a if the value of a can uniquely identify x.But in the above example its violated,A1 derives 2 different combination .

Now if break it in my way:

 A   B           E
 A1  SAM WATSON  C1


 C  D
 C1 PETER PARKER
 C2 BRUCE WAYNE

but the decomposition is not Loss-less(lossy), as there is no common attribute to unite the tables. if i try to add :

 A  C
 A1 C1
 A1 C2

so the FD definition is again violated.

Thats it! I've ried my best, i am still the beginner so if i've made any mistake please point it out, i would love to learn from it.