-1

In the question given in the link https://www.hackerrank.com/challenges/database-normalization-6/problem the answer is 2.

But I cannot understand why all the relations are not in BCNF (Since for all non-trivial functional dependencies of the form X->Y, X is a superkey of the corresponding relation). Someone please explain where I am wrong.

The question copied from hackerrank site:

Let us take the example of a simple movie library. Each movie has a description, director, and serial number. Customers have a name, address, and membership number. Assume only one copy of each movie exists in the library. We are given the following relations and determinants. The keys for each relation are CAPITALIZED.

Relations (The key is CAPITALIZED):
customer(name,addr,MEMBERNO)
movie(DESCRIPTION,director,serialno)
borrow(memberno,DATE,SERIALNO)

Determinants:
description->director,serialno
serialno->description
serialno->director
name,addr -> memberno
memberno -> name,addr
serialno,date -> memberno

The above relation is in xNF form where x may take the following values {1,2,3,3.5} corresponding to {1NF, 2NF, 3NF and BCNF} respectively.
What is the maximum possible value of x such that the above relation satisfies the xNF form?

closed as off-topic by Md Haidar Ali Khan, Michael Green, mustaccio, Colin 't Hart, Marco Sep 24 '18 at 13:02

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Michael Green, Colin 't Hart, Marco
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    why not post your question in the discussions forum associated with that question? – markp Sep 22 '18 at 17:33
  • 3
    Please add all the necessary parts in the question so anyone who wants to answer doesn't have to read external links – ypercubeᵀᴹ Sep 22 '18 at 17:56
  • 2
    You are correct. All the relations are in BCNF. But please, next time post the question as text and not as link. – Renzo Sep 23 '18 at 4:55
  • 1
    @Renzo question updated. You can answer know (and I agree, the relations seem to be all in BCNF) – ypercubeᵀᴹ Sep 23 '18 at 19:20
2

Problems in the exercise

Inconsistency

In the exercise, it is said:

We are given the following relations ...

Relations (The key is CAPITALIZED):
customer(name,addr,MEMBERNO)
movie(DESCRIPTION,director,serialno)
borrow(memberno,DATE,SERIALNO)

Then, a few lines later:

The above relation is ... ... What is the maximum possible value of x such that the above relation satisfies the xNF form?

The three relations first presented become now a single relation, and I am not sure that this is a simple typo (see below).

Terminology

In the exercise, the term “determinant” is wrongly used instead of “functional dependency”. Actually the “determinant” is the proper name of the left hand side of a functional dependency, while the “determinate” is tha name of the rigth hand side. For instance, description->director,serialno is a functional dependency in which the determinant is {description}, and {director, serialno} is the determinate. The reason for this terminology is clear: the left hand side uniquely “determines” the rigth hand side.

Keys and functional dependencies

The information about the primary key of the three relations is redundant (non essential) with respect to the following functional dependencies. This is due to the fact that from the functional dependencies one can derive all the candidate keys of a relation (in general a relation can have more than one candidate key).

Reformulation of the exercise

So, to give an answer to your question, I think that we should try to reformulate the exercise as something like:

Given a relation:

R(name, addr, memberno, description, director, serialno, date)

with functional dependencies:

description->director,serialno
serialno->description
serialno->director
name,addr -> memberno
memberno -> name,addr
serialno,date -> memberno

and the decomposition in three relations:

customer(name,addr,memberno)
movie(description,director,serialno)
borrow(memberno,date,serialno)

what is the maximum possible value of x such that the relations customer, movie and borrow satisfy the xNF form?

The answer

From the given functional dependencies, we can obtain all the candidate keys of the original relation. There are two candidate keys:

date description

and

date serialno

In the schema customer, the following functional dependencies hold:

memberno -> name, addr
addr, name -> memberno

Since in this relation both {memberno} and {addr, name} are candidate keys, no dependency violates the BCNF, and the relation is in BCNF.

In the schema movie, the following functional dependencies hold:

serialno -> director, description
description -> serialno, director

and since {serialno} and {description} are both candidate keys, also this relation is in BCNF.

Finally, in the schema borrow the functional dependencies:

date, serialno -> memberno

holds, and since both the attributes are the only candidate key, the relation is in BCNF.

So all the three relations are in BCNF, and your initial answer, given my "re-interpretation” of the question, is correct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.