0
$\begingroup$

I thought I understood BCFN until I bumped into this example from my course slides.

  • A relation in a library database contains what books are currently borrowed by what users.
  • The relation contains the unique ID of the user, their name and the title of the book.
  • Different users might share the same name.
  • A book can only be borrowed by one user.
  • A user can have borrowed more than one book.

An example of this relation could be:

USER_ID USER_NAME BOOK_TITLE
U012 Charles Hamlet
U034 Alex Blue Moon
U491 Diane Capital
U034 Alex The Crow
U491 Diane Economics 2
U012 Charles Lost Girl
U034 Alex Chess basics
U012 Charles Greek History
U491 Diane Red Mars

According to the slide, this relation is in Boyce-Codd's Normal Form,as:

all functional dependencies of the form $\alpha \rightarrow \beta $ are either trivial or satisfy that $\alpha$ is a super-key of the relation

While it is my understanding that those are the requirements for BCNF, I don't think this relation satisfies them. In particular, the functional dependency USER_ID $\rightarrow$ USER_NAME is not trivial (as $\beta$ is not a subset of $\alpha$) and $\alpha$ is not a super-key (since by itself we cannot distinguish between entries representing different books borrowed by the same user).

Am I missing something?

Improve this question
$\endgroup$

1 Answer 1

Reset to default
1
$\begingroup$

Assuming that each book has a different title, you are correct in thinking that the relation is not in BCNF.

In fact, the (only) candidate key of the relation is Book_title, so the functional dependency User_id -> User_name violates the definition of BCNF. Assuming that a cover of the dependencies are:

User_id -> User_name    (each user_id identifies a user with a certain name)
Book_title -> User_id   (each book can be borrowed only by a certain user)

a decomposition of the original relation in BCNF is:

R1(User_id, User_name)  with candidate key User_id
R2(Book_title, User_id) with candidate key Book_title
Improve this answer
$\endgroup$
4
  • $\begingroup$ Thanks for the answer. Since you start with "Assuming that each book has a different title", I want to ask: isn't that irrelevant to whether the relation is in BCNF? In that case, the candidate key is (User_id, Book_title), and there is still a non-trivial functional dependency User_id -> User_name, with User_id not being a super-key. $\endgroup$
    – user2891462
    Commented May 17, 2021 at 10:15
  • 1
    $\begingroup$ If a book title is not unique, then two different books with the same title could be borrowed to the same user. In that case there is no candidate key (since (User_id, Book_title) is not unique anymore), and in fact the relations is not even a relation, since it could have duplicate rows. So, in that case one should introduce some way of identifying books (for instance the ISBN, or something like that). $\endgroup$
    – Renzo
    Commented May 17, 2021 at 10:26
  • 1
    $\begingroup$ In other words, in that case the problem is that the representation of data with a “relation” (in the correct sense of the data model) is not possible unless we add other attributes (and it is not useful to apply the results of the normalization theory). $\endgroup$
    – Renzo
    Commented May 17, 2021 at 10:31
  • $\begingroup$ Ah, of course! Thanks for taking the time :) $\endgroup$
    – user2891462
    Commented May 17, 2021 at 14:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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