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:
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_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?