The Gateway to Computer Science Excellence

First time here? Checkout the FAQ!

x

+24 votes

Consider the schema $R=(S,T, U, V)$ and the dependencies $S \rightarrow T, T \rightarrow U, U \rightarrow V$ and $V \rightarrow S$. Let $R = (R1\text{ and } R2)$ be a decomposition such that $R1 \cap R2 \neq \phi$. The decomposition is

- not in $2NF$
- in $2NF$ but not $3NF$
- in $3NF$ but not in $2NF$
- in both $2NF$ and $3NF$

+42 votes

Best answer

$R_1 \cap R_2 \neq \phi.$ This makes the decomposition lossless join, as all the attributes are keys, $R_1 \cap R_2$ will be a key of the decomposed relations (lossless condition says the common attribute must be a key in at least one of the decomposed relation). Now, even the original relation $R$ is in $3NF$ (even $\text{BCNF}$)as all the attributes are prime attributes (in fact each attribute is a candidate key). Hence, any decomposition will also be in $3NF$ (even $\text{BCNF}$). Option $D$.

PS: Decomposition in $3NF$ means decomposed relations are in $3NF$. But when we consider any decomposed relation, we must also include any FD which are being implied by the original relational schema. For example, in a decomposed relation $STU,$ there will be a FD $U\to S$ as well.

PS: Decomposition in $3NF$ means decomposed relations are in $3NF$. But when we consider any decomposed relation, we must also include any FD which are being implied by the original relational schema. For example, in a decomposed relation $STU,$ there will be a FD $U\to S$ as well.

@Arjun : I agree since all the attributes are Candidate Keys. Now whatever the decomposition is, the decomposed relations will be in BCNF. But still there is a confusion which you stated above that Normal Form is on Relation rather decomposition. Till now whatever books i read they all suggested Normal Form on Relation rather than decomposition.

Decomposition- means for decomposed relations. The question was not correct earlier and my comments were made then.

@Arjun Sir, Is this question correctly copied ?

check question number 2.7 here http://gateforum.com/wp-content/uploads/2013/01/CS-1999.pdf

which says R1 ∩ R2 **=** ∅

No. They copied it wrong. https://drive.google.com/file/d/0B8_aYGBndW4HRVVPVmpEeGJlbjA/view?usp=sharing

If some 1 would clarify a lil doubt i would be grateful. The attributes are prime attributes in the parent relation but suppose the decomposition is done as R1 :S,T,U R2 :U,V . then in this case the relation in R1 U will not be prime attribute right ? because there is no V. So from transitive clousure S woud be the candidate key.Then wouldnt this relation be under transitive dependency as S->T and T -> U.U is non prime and T is not key ? Can some 1 explain !!!

In STU, S, T and U are keys and in UV, U and V are keys. There aren't any composite attributes- so no chance of partial dependency.

Here how can be S,T,U will be keys in decomposed relation STU ?? and U, V are the keys in relation UV ???

+16 votes

Answer : **In both 2NF and 3NF **

Dependencies are

S --> T, T --> U, U -->V V --> S

S+ = STUV U+ = UVST

T+ = UVST V+ = VSTU

There is no Partial Dependencies here So it is in 2 NF

RHS of every Dependencies is a Key as well as all are Prime Attributes So it is in 3 NF.

+5 votes

+4 votes

(d) both in 2nf and 3nf

since r1 and r2 does not have any common attributes , and if we decomse it to relation having 2 attributes, then they are by default in 3nf , Suppose if we decompose r1 with 1 attribute and r2 with 3 attributes, since all of them are dependent all of them could be a prime attribute in this case also they are in 3nf, since they are in 3nf, 2nf by default satisfy

since r1 and r2 does not have any common attributes , and if we decomse it to relation having 2 attributes, then they are by default in 3nf , Suppose if we decompose r1 with 1 attribute and r2 with 3 attributes, since all of them are dependent all of them could be a prime attribute in this case also they are in 3nf, since they are in 3nf, 2nf by default satisfy

since all attributes are prime attributes (in fact candidate keys) can't we straight away say that R1 and R2 are in 3NF?

@Arjun Sir I read that for a lossless decomposition of a relation R into R1 and R2, R1(inter)R2->R1 or R1(inter)R2->R2 But here R1(inter)R2 is phi, so how can it be lossless ? and if it is not lossless then how can it be a valid decomposition ? and if its not a valid decomposition how can it be 2nf or 3nf ?

Obviously it is lossy decomposition. But I don't know to answer the remaining questions. Because I assume a normal form is applied to a relation and not to a decomposition. I searched a bit but couldn't find a solid reference saying either way. Have you seen anywhere saying that a lossy decomposition produces relations which won't have a particular normal form?

Sir this is what found in elmasri

"Normal forms, when considered in isolation from other factors, do not guarantee a good database design. It is generally not sufficient to check separately that each relation schema in the database is, say, in BCNF or 3NF. Rather, the process of normalization through decomposition must also confirm the existence of additional properties that the relational schemas, taken together, should possess. These would include two properties:

The nonadditive join or lossless join property, which guarantees that the spurious tuple generation problem discussed in Section 15.1.4 does not occur with respect to the relation schemas created after decomposition.

The dependency preservation property, which ensures that each functional dependency is represented in some individual relation resulting after decomposition.

**The nonadditive join property is extremely critical and must be achieved at any cost, whereas the dependency preservation property, although desirable, is sometimes sacrificed**."

+2 votes

since no partial dependency and non key to non key

every determinant is deriving all atributes so in bcnf

by default 1nf

so it is all 1nf,2nf,3nf, and bcnf

every determinant is deriving all atributes so in bcnf

by default 1nf

so it is all 1nf,2nf,3nf, and bcnf

+1 vote

If all the attributes are candidate keys, then any subset of attributes will be a super key

Whatever FD you can form, your LHS will have a subset of attributes

From above stataments, we can say that any FD that can be formed will have LHS as a super-key

This is good enough to say that the relation is in BCNF ...

This is also applicable even after the decomposition of relation irrespective of what common attributes we have (even if there is no common attributes, though lossy decompositions are not recommended)

Whatever FD you can form, your LHS will have a subset of attributes

From above stataments, we can say that any FD that can be formed will have LHS as a super-key

This is good enough to say that the relation is in BCNF ...

This is also applicable even after the decomposition of relation irrespective of what common attributes we have (even if there is no common attributes, though lossy decompositions are not recommended)

- All categories
- General Aptitude 1.2k
- Engineering Mathematics 4.7k
- Digital Logic 1.9k
- Programming & DS 3.5k
- Algorithms 3k
- Theory of Computation 3.7k
- Compiler Design 1.5k
- Databases 2.8k
- CO & Architecture 2.5k
- Computer Networks 2.9k
- Non GATE 837
- Others 1.2k
- Admissions 278
- Exam Queries 396
- Tier 1 Placement Questions 17
- Job Queries 50
- Projects 7

33,687 questions

40,231 answers

114,269 comments

38,800 users