0

I am trying to figure out the canonical cover for F of the table below and decompose it to BCNF. - Attribute of book

Book ID Title ISBN Publish Date Publisher ID Publisher Name Book score Author ID Author Name

Each book got single score and one author of course.

These are the given functional dependencies:

Book ID - > Title, ISBN, Publish Date, Publisher ID, Publisher Name<br /> 
Book ID - > Author ID, Author Name, Book Score<br /> 
Publisher ID- > Publisher Name<br /> 
Author ID - > Author Name<br />

To find the canonical cove for F, I know that the right hand size has to be single RHS, and no extraneous on LFS.

So I have this:

BookID- > Title <br /> 
BookID- > ISDN<br /> 
BookID- > Publish Date<br /> 
BookID- > Publish ID<br /> 
BookID- > Publisher Name<br /> 
BookID- > Author ID<br /> 
BookID- > Author Name<br /> 
BookID- > Book Score<br /> 
Publisher ID- > Publisher Name<br /> 
Author ID - > Author Name<br />

Then, I am stuck and have no idea how to move on...
To decompose it into BCNF, every determinant is a candidate key, and I believe BookID, Publisher ID and Author ID are ALL candidates for a key.

And I have no idea how to continue afterwards...

philipxy
  • 14,867
  • 6
  • 39
  • 83
Peter
  • 19
  • 2
  • Please read the edit help for basics re formatting including for blocks of code by indenting 4 spaces. Look at the formatted version of your post before you stop editing. – philipxy Oct 22 '18 at 20:21
  • Hi. Please give the name & edition of your published academic textbook. Show your work following it. Quote definitions, theorems & algorithms you are using. The first place you are stuck explain about why by referencing them & how you are trying to use them. Don't expect us to know the particular ones you are using or reproduce them once we do. (Anyway this is an easily found faq.) PS "These are the given functional dependencies" is not helpful. Given for what purpose? What about them? You mean that they form a cover for all the FDs that hold here. – philipxy Oct 22 '18 at 20:30

1 Answers1

0

Canonical Cover

In the canonical cover you should eliminate the dependency BookID → PublisherName, since it can be derived from BookID → PublisherID and PublisherID → PublisherName.

Candidate keys

Then, the only candidate key is BookID: PublisherID and AuthorId are not candidate keys, since they do not determine all the attributes of the relation, just one of them.

Decomposition in BCNF

Finally it is easy in this case to find the BCNF, since it is sufficient to decompose the relation in three relations with candidate keys the three determinants of the different functional dependencies, and the other attributes the determined attributes. In more complex cases, however, it is necessary to use some algorithm, for instance the so-called “analysis algorithm”, that you can find in any good book on databases.

Renzo
  • 26,848
  • 5
  • 49
  • 61
  • Hello Renzo, Thanks a lot for your answer. May I ask some more questions please? For Canonical Cover - Can I understand this as: BookID → PublisherName (A->B) BookID → PublisherID (A->C) PublisherID → PublisherName (C->B) Hence, A ->B is extraneous? But are we supposed to only remove extraneous on LFS and extraneous on the RHS only? Is this step really needed? For BCNF, so once I find the only Candidate key is BookID, can I simply use it to decompose it to different table? I have read some video on youtube but seems they have skipped some steps that I missed... Thanks again! – Peter Oct 23 '18 at 00:48
  • For a canonical cover three steps are needed: reduce all the FDswith a single attribute on the right; remove extraneous attributes on the LHS, remove superflous dependencies (i.e. dependencies implied by others). For BCNF, no, the decomposition works only in the simplest cases, like this one, but in general the process is more complex. See for instance this [reply](https://stackoverflow.com/a/42803509/2382734). – Renzo Oct 23 '18 at 08:07