Question

Consider the following relation

A

B

C

D

E

F

with functional dependencies
A --> B
C --> E
E --> F
and decomposition into

A

B

C

D

F

C

E

Which of the following statements is true?

		A-Decompositions were necessary to convert the relation into 3NF but the proposed one does not satisfy the lossless join property.
		B-The decomposition was unnecessary since the relation was already in 3NF.
		C-Decompositions were necessary to convert the relation into 3NF but the proposed one is not dependency preserving.
		D-Decompositions were necessary to convert the relation into 3NF and the proposed one is appropriate.

Answer 1

Before getting into options lets first check the , current Schema / Relation

Its given that, schsma has attributes (A,B,C, D, E, F)

and dependencies are as follows:

A -> B

C- > E

E -> F

NOTE : Find candidate key first (Primary key) , its the most important and neccessary step , before going ahead.

Now to find candidate key i will tell you the best way, which will help you with all normalisation questions,

Candidate key is the key from which , you can get all the other attributes, and also this key should be minimal, which means this key shouldn't be the subset of any other key.

So, all such keys are known as SUPER KEYS,

Minimal SUPER KEYS are known as CANDIDATE KEY

and, you can make any candidate key as PRIMARY KEY.

So lets find out the keys now.

You can see, all the dependencies are unable to find out attribute D, so it means key must contain D

so candidate key would be , ACD as

A derives A, B

C derives C, E, F

D derives D (an attribute can derive itself TRIVIAL PROPERTY)

so, now lets check about the , normalisation part.

2NF says, if X -> Y , then X should not be a part of CANDIDATE KEY  

here, A -> B, A is the part of CANDIDATE KEY , ACD

so we need to decompose the table, the best way to do so is, move all the dependencies to different table which violate the rule,

so R1 (A, B, C D, F)

R2 ( C, E)

now we have 2 schemas in 2NF, lets check if they are in 3NF ,

R1 has dependency A-> B ,

whereas R2 has C-> E

so they both are in 3NF, but wait,  

don't you think, 1 dependency is missing, YES, you got the answer,

OPTION C , TRUE rest all are FALSE.

C-Decompositions were necessary to convert the relation into 3NF but the proposed one is not dependency preserving.

HOPE i helped you.