The Gateway to Computer Science Excellence
First time here? Checkout the FAQ!
x
+26 votes
3.3k views

Consider the following relational schemes for a library database:

Book (Title, Author, Catalog_no, Publisher, Year, Price)
Collection(Title, Author, Catalog_no)

with the following functional dependencies:

  1. $\text{Title Author }\rightarrow\text{ Catalog_no}$

  2. $\text{Catalog_no }\rightarrow\text{ Title Author Publisher Year}$

  3. $\text{Publisher Title Year}\rightarrow\text{ Price}$

Assume $\left\{\text{ Author, Title }\right\}$ is the key for both schemes. Which of the following statements is true?

  1. Both Book and Collection are in BCNF

  2. Both Book and Collection are in 3NF only

  3. Book is in 2NF and Collection in 3NF

  4. Both Book and Collection are in 2NF only

asked in Databases by Veteran (68.8k points)
retagged by | 3.3k views

@ worst_engineer  {Title , Author}  is the key for both schemes .what did u take candidate keys are : Catalog_no , {Title , Author}

@bikram sir see this pls
Ans is A, Both are in BCNF.

3 Answers

+24 votes
Best answer

Answer: C

It is given that {Author , Title} is the key for both schemas.

The given dependencies are : 

  • {Title, Author} --> {Catalog_no}
  • Catalog_no --> {Title , Author , Publisher , Year }
  • {Publisher , Title , Year} --> {Price}

First , let's take schema Collection ( Title , Author , Catalog_no ) :

{Title , Author} --> Catalog_no

{Title, Author} is a candidate key and hence super key also and by definition of BCNF this is in BCNF.

Now , let's see Book (Title , Author , Catalog_no , Publisher , Year , Price ) :

 {Title , Author}+ --> {Title , Author , Catalog_no , Publisher , Year , Price}

{Catalog_no}+--> {Title , Author , Publisher , Year , Price , Catalog_no}

So candidate keys are : Catalog_no , {Title , Author} 

But in the given dependencies , {Publisher , Title , Year} --> Price , which has Transitive Dependency. So , Book  is in 2NF.

 

 

answered by Boss (5.8k points)
edited by
Like you have already mentioned ,

 

Catalog_no → Title Author Publisher Year

could be split into -

Catalog_no --> Title

Catalog_no --> Author

Catalog_no --> Publisher

Catalog_no --> Year

So, Collection schema will contain -

{Title,Author} -> Catalog_no   

Catalog_no ->  Title

Catalog_no  -> Author

Since the given key is {Title,Author} for both schemas. So only 3nf satisfies.
But catalog_no also becomes super key for relation collection...so it is still in bcnf.
Hello Sir,

How Book can be in 2nf , 'title' is a prime attribute and 'price' is an non prime attribute . so there is partial dependency  in

{Publisher , Title , Year} --> Price
Hello Sir, why it is in 2nf , please explain

Thank you
@Shekhar There is no partial dependency.
But in statement III ,FD III

a prime attribute Title is defining Price (non attribute) isn't this a partial dependency ?

A functional dependency $\alpha \rightarrow \beta$ is called a partial dependency if there is a proper subset $\gamma$ of $\alpha $ such that $\gamma \rightarrow \beta $. We say that $\beta$ is partially dependent on $\alpha $.

- from book by Korth

 

 

Is this definition correct?
Yes. Of course. It's from a standard book.

Assume { Author, Title } is the key for both schemes

then candidate key is given. why are we adding Catalog_no as candidate key? 

is it necessary to find CKs even after it is specified in question?

+8 votes
(c)

in collection all the non prime attributes depend directly on candidate key - so BCNF and hence 3NF (actually collection has only prime attributes so it should, by default be at least in 3NF)

in book the non prime attr (price), depends indirectly on the candidate key (catalog_no) thus forming transitive dependency and hence not in 3NF. There is no partial dependency so - 2nf
answered by Loyal (3.4k points)
why collection not in BCNF? Catalog_no is a key for Book rt?
Yeah, Collection should be BCNF, And I think there answers are twisted, just to trick us
Collection is in BCNF because Title, Author is a super key.
book is not in 2NF also...because in last FD... price is not fully functionally dependent on (title,author)....pls explain..
Price is fully dependent on (title, author). Neither title nor author alone can determine price. So, no partial FD and hence 2NF condition satisfied.
How come price is fully functionally dependent on (title, author) as it is dependent on (publisher, title, year)?
Note: Prime attribute Non prime attribute -> Non prime is in 2NF
ok therefore we are considering

{Publisher , Title , Year} --> {Price} in 2NF

since Title is prime Attribute and {publisher, year} is non prime attribute and {price} is also a non prime attribute right?

and also plz tell what is the NF of following dependency:-

{Prime, Non Prime }-> {Prime} ?
In you question Prime Non Prime -> prime

RHS becomes Prime attributes which is suffiecint for this FD to be in 3NF.
How come this
(prime,non-prime)-->(non-prime)
be in 2NF?
Since defn. says "every non-prime attribute A in relation R must be fully functionally dependent on the primary key of R"

but above says that only having some prime attributes and not the primary key could result in 2NF
I think

(prime, non prime) -> non prime

will be consider as

(non prime) -> non prime.

hence it is considered as 2nf.

ryt?

To be in 2NF "every non-prime attribute should fully functionally dependent on the primary key."

So how

non-prime --> non-prime    is in 2NF ??

Primary key -> any thing(prime, non prime, key) will be in BCNF.
sir collection has some other functional dependencies which u can derive if u take closure of title or author they give only title->title and author ->author

hence the rhs is not a super key but lhs is is a prime attribute hence the collection is only in 3NF but not in BCNF
+3 votes
Ans. C

In Relation Book there is a transitive dependency and hence not in 3NF. There is no partial dependency so in 2NF

In Relation Collection all the non prime attributes depend directly on candidate key. In fact, collection has only prime attributes. So BCNF and hence 3NF.
answered by Active (1k points)

Raushank2

catalog_no->Title Author Publisher year 

According to 3nf if X->Y

then 1) X is a super key or 2) y is a prime attribute 

in case of title ,it is prime attribute so by this it must be in 3 nf ?

https://gateoverflow.in/1260/gate2007-62-ugcnet-june2014-ii-47 see this



Quick search syntax
tags tag:apple
author user:martin
title title:apple
content content:apple
exclude -tag:apple
force match +apple
views views:100
score score:10
answers answers:2
is accepted isaccepted:true
is closed isclosed:true

32,505 questions
39,217 answers
109,115 comments
36,599 users