GATE CSE 2008 | Question: 69

Question

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 $\text{true}$?

• A. Both Book and Collection are in $\text{BCNF}$

• B. Both Book and Collection are in $\text{3NF}$ only

• C. Book is in $\text{2NF}$ and Collection in $\text{3NF}$

• D. Both Book and Collection are in $\text{2NF}$ only

Comments

If titleauthor is a candidate key then in third one it is partial dependency bcz title is the proper subset of ck and price is the none key attribute. Then how how it is in 2NF.plz help

---

Even I have this doubt!

---

2NF violation

$ C_k \space \underrightarrow { Partial } \space $ $ Non \space Prime \space attribute$.
This type of Functional Dependency shouldn’t be present.
We have 2 candidate keys $\{ Catalog \_no\} $ and $\{Author, Title\}$

The 3^rd functional dependency is clearly not a proper subset of above 2 candidate keys.
Hence the relation is in 2NF.

 

Answer 1

Answer: C

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

The given dependencies are : 

• $\{\text{Title}, \text{Author}\}\to  \text{Catalog_no}$
• $\text{Catalog_no} \to \{\text{Title},\text{Author}, \text{Publisher}, \text{Year}\}$
• $\{\text{Publisher}, \text{Title}, \text{Year}\} \to \{\text{Price}\}$

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

• $\{\text{Title}, \text{Author}\} \to \text{Catalog_no}$

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

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

• $\{\text{Title}, \text{Author}\}^+ \to \{\text{Title}, \text{Author}, \text{Catalog_no}, \text{Publisher}, \text{Year}, \text{Price}\}$
• $\{\text{Catalog_no}\}^+ \to \{\text{Title}, \text{Author}, \text{Publisher}, \text{Year}, \text{Price}, \text{Catalog_no}\}$

So candidate keys are : $\text{Catalog_no}, \{\text{Title}, \text{Author}\}$ 

But in the given set of dependencies we have $\{\text{Publisher}, \text{Title}, \text{Year}\} \to \text{Price},$ which has a Transitive Dependency. So, Book is not in 3NF but is in 2NF.

Comments

Is it a partial FD?

---

ohh sorry , my bad.. It is transitive dependency as Part of prime + non-prime --> non prime ,

So , it is in 2NF not in 3NF.

I am editing my answer . Thank you sir for pointing out my mistake.

---

How can we say that {Publisher,Title,Year} $\rightarrow$ Price has transitive dependency ? According to defination "There shouldn't be the case that a non-prime attribute is determined by another non-prime attribute." But, we also have a prime attribute 'Title' on LHS, then how can we say that a non-prime attribute is determining a non-prime attribute ?

---

Where is that definition given?

---

Wikipedia  site for the term Transitive Dependency says so in the example mentioned there.

---

In schema Collection ( Title , Author , Catalog_no ) , 

shouldn't the FD: Catalog_no --> {Title , Author} also hold?

[ Inferred from the FD: Catalog_no --> {Title , Author , Publisher , Year } ]

Although, schema Collection will still be in BCNF.

---

In a FD, if RHS is not a prime attribute and LHS is not a super key, the FD violates 3NF for sure.

@Pratyush That does hold and is given in a compact form

Catalog_no --> {Title , Author , Publisher , Year } which could be split to

• Catalog_no --> Title
• Catalog_no --> Author
• Catalog_no --> Publisher
• Catalog_no --> Year

---

@partyush madhuker that's exactly i was going to mentioned then saw you had already mentioned it.

---

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

---

Can anyone clear my doubt.

According to me, the functional dependencies in Collection schema will look like-

Title Author -> Catalog_no

Catalog_no -> Title Author (after splitting from the FDs given for library database)

So, then BCNF will be violated as the candidate key given is {Title Author}.

Hence it will only satisfy 3NF.

---

How was that split of FDs done?

---

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?

---

Only collection is in BCNF too

---

Book is in 2NF because:-

It is {Publisher Title Year } $\rightarrow$ Price

But not Title $\rightarrow$ Price

If the following Dependency

Title $\rightarrow$ Price

Holds Then Book will surely NOT be in 2NF, But it is present here.

---

@arjun Sir can you please clear my doubt ?

as mentioned in your comment 

Catalog_no --> {Title , Author , Publisher , Year } which could be split to

• Catalog_no --> Title
• Catalog_no --> Author
• Catalog_no --> Publisher
• Catalog_no --> Year

and according to wiki def for BCNF 

If a relational schema is in BCNF then all redundancy based on functional dependency has been removed, although other types of redundancy may still exist. A relational schema R is in Boyce–Codd normal form if and only if for every one of its dependencies X → Y, at least one of the following conditions hold:^[2]

• X → Y is a trivial functional dependency (Y ⊆ X)
• X is a superkey for schema R

clearly Catalog_no is not SK for 'collection' relation hence it is not in BCNF but in 3NF 

and that's what answer C says but answer by  worst_engineer amd taught by my coaching instructor says it is in BCNF

---

Did you get the answer to this, I got the same doubt.

---

@mehul vaidya @Krishna Sai Vootla

You are right that relation Collection is in BCNF because (Title, Author) is a primary key, and it is mentioned in the above answer also.

But because relation Book is in 2NF we have only one choice in the options which suits our results and that is 

 

Book is in 2NF and Collection in 3NF

---

If it would be like Title -> Price then it was partial dependence.

part of key + non-prime attribute -> non-key is considered as transitive dependecy.right???

---

@MRINMOY_HALDER

Yes. Partial-dependency exists only when a PROPER-SUBSET of a key determines some non-prime attribute.

and,

"part of key + non-prime attribute -> non-key "

Here, neither the L.H.S is a Super-key nor the R.H.S is a prime-attribute. So definitely not in 3NF.

---

Even I have this doubt!

Also I have a doubt it part of the key+ nonkey -->part of the key+non key

Is it a transitive relationship?

---

@MRINMOY_HALDER @Kaurbaljit

You sure about this?

---

Yes it is.

Because neither lhs is superkey nor rhs is prime attribute

---

Ignoring the options and looking for best possible information, can we say:

Collection is in BCNF.

         Book is in 2NF.

---

@Arjun How is Publisher Title Year→ Price not a Partial Dependency since Price ( a non-key attribute) is dependent on Title ( a part of the key) ?

---

Partial Dependency only happens when the LHS is the proper subset of the key. {Publisher, Title, Year} is not a proper subset of {Title, Author}.

---

{publisher tittle author} --→ price

sir in this case title is proper subset of candidate key and price is non prime attribute thus it is partially dependent and hence it is not in 2nf form, then how we are considering this in 2nf form?

---

price is dependent on {publisher, title, author} collectively. Not on {title} alone.

And {publisher, title, author} is not a proper subset of any candidate key. 

That why no partial dependency.

---

Sir, Why to calculate candidate key if it is already given in question? Do we need to take Catalog_no as Candidate Key for Book? I am kind of confused here

---

@kirtijyotisenapati bcz u have to check all the candidate keys right with the help of only given key u can't conclude so u've to find first all candidate keys 

Answer 2

(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

Comments

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

---

@Arjun sir Cant we think like this that since key has only one attribute. so no part of key, no partial dependency . So book is in 2NF. We can easily see transitive dependency , so it is not 3nf.

Answer 3

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.

Comments

@ 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

Answer 4

one point worth noting is that even though the key is mentioned but that simply refers to the primay key which is one of the key among candidate key ,so you have to find all  the candidate key first to determine the normal forms of the schema.

for collection schema:
fd's are:

catalog_no->title author

title author->catalog_no

also catalog_no and (title author) are candidate key for this schema.

so this is in bcnf.

for book schema:

fd's are:

Title Author → Catalog_no

Catalog_no → Title Author Publisher Year

Publisher Title Year→ Price 

the candidate key's are catalog_no and (titlle,author) 

clearly 3rd is a transitive dependency hence 2nf and not 3nf or above.