edited by
501 views
1 votes
1 votes

Given R(A,B,C,D,E) with the set of FDs,
F{AB→CD,  ABC → E, C → A}
the normal form of R is

  1. 2NF
  2. BCNF
  3. 4NF
  4. 3NF
edited by

2 Answers

Best answer
3 votes
3 votes

$R(A,B,C,D,E)$ and Fd's are $F\{AB\rightarrow CD,ABC\rightarrow E,C\rightarrow A\}$

Candidate keys are: $AB,CB$

Prime attributes or key attributes $=A,B,C$ and non-prime attributes or non-keys attributes $=D,E$

Second Normal Form$(2NF)$:- A relation is in $2NF$ if it has No Partial Dependency. i.e.no non-prime attribute (attributes which are not part of any candidate key) is dependent on any proper subset of any candidate key of the table.

Partial Dependency – If the proper subset of candidate key determines non-prime attribute, it is called partial dependency.

The given Fd's are in $2NF$.

Third Normal Form$(3NF)$:- 

  • A relation is in third normal form, if there is no transitive dependency for non-prime attributes as well as it is in second normal form.
  • A relation is in $3NF$ if at least one of the following condition holds in every non-trivial function dependency $X\rightarrow Y$
  1. $X$ is a super key.
  2. $Y$ is a prime attribute (each element of $Y$ is part of some candidate key).

 Transitive dependency – If $A\rightarrow B$ and $B\rightarrow C$ are two FD's then $A\rightarrow C$ is called transitive dependency.

The given Fd's are in $3NF$.

Boyce-Codd Normal Form $(BCNF)$:-

A relation $R$ is in BCNF if $R$ is in Third Normal Form and for every FD, LHS is super key. A relation is in BCNF iff in every non-trivial functional dependency $X \rightarrow Y, X$ is a super key.

The given Fd's are not in $BCNF$ because Fd's $C\rightarrow A, C$ is not super key.

 If the relation is not in $BCNF$, then no need to check $4NF$.

Important Points:

  1. If a relation is in BCNF, then 3NF is also satisfied.
  2.  If all attributes of relation are prime attribute, then the relation is always in 3NF.
  3. Every Binary Relation ( a Relation with only 2 attributes ) is always in BCNF.
  4. If a Relation has only singleton candidate keys( i.e. every candidate key consists of only 1 attribute), then the Relation is always in 2NF( because no Partial functional dependency possible).

References:

So,the correct answer is $(D)$. 

selected by
0 votes
0 votes
F(AB->CD,ABC->E,C->A)

here AB->C and C->A

so(extraneous attribute) we can write ABC->E as AB->E or CB->E  so

F(AB->CD,AB->E,C->A) or F(AB->CD,CB->E,C->A)

now ck are AB and CB

so  both fds clearly satisfying 3nf property .Hence option D is correct.
Answer:

Related questions

1 votes
1 votes
1 answer
1
Bikram asked Nov 26, 2016
319 views
Consider the join of relation R with a relation S. If R has $m$ tuples and S has $n$ tuples, then the maximum and minimum sizes of the join, respectively, are __________....
0 votes
0 votes
1 answer
2
Bikram asked Nov 26, 2016
229 views
A functional dependency of the form x → y is trivial ify ⊆ xy ⊂ xx ⊆ yx ⊂ y
0 votes
0 votes
1 answer
3
Bikram asked Nov 26, 2016
295 views
What does the following Tuple Relational Calculus query produce?The expression σθ1 (E1 ⋈θ2 E2) is the same as: E1 ⋈θ1^ θ2 E2 (σθ1 E1) ∧ (σθ2 E2 ) E1 ⋈ θ...