Showing posts with label Normalization. Show all posts
Showing posts with label Normalization. Show all posts

Correct functional dependencies for bcnf relation

Which of the FDs are correct for R to be in BCNF?, Boyce-Codd Normal Form


Question:

13. Let us consider a relation R with the schema R (A, B, C, D). If we expect R to be in BCNF, then which of the following sets of functional dependencies must hold in R?


(a) A → C, A → D, AD → C, B → A
(b) C → B, C → D, A → C, D → A
(c) C → D, CD → A, BD → A, AB → C
(d) A → D, C → A, AC → B, D → B


Answer:


(b) C → B, C → D, A → C, D → A

Discussion/Reason:


For a relation to be in BCNF, then all the FDs must have candidate keys on their left hand side.
Let us find the closure (refer here for attribute closure) for LHS of each FD of option (b).
Closure of C (C+)
FD causes the result
result = CB
C → B
result = CBD
C → D
result = CBDA
D → A
From the above table, the result of closure of C = ABCD = R. Hence, C is one of the candidate keys.
Closure of A (A+)
FD causes the result
result = AC
A → C
result = ACB
C → B
result = ACBD
C → D
From the above table, the result of closure of A = ABCD = R. Hence, A is one of the candidate keys.
Closure of D (D+)
FD causes the result
result = DA
D → A
result = DAC
A → C
result = DACB
C → B
From the above table, the result of closure of D = ABCD = R. Hence, D is one of the candidate keys.
For option (b), all the FDs left hand sides are candidate keys. So, R should hold these FDs to be in Boyce Codd Normal Form.

You try for other FDs given in other options. The LHS will not form a key.






         Previous Question                                                                                Next Question



Go to Multiple choices questions page







Find the correct functional dependency of a table

Find the correct functional dependencies that satisfy a BCNF table, Which of the following is the correct functional dependencies?


Question:

12. Let us assume a relation R (A, B, C, D, E) that is in BCNF. If BCD is the only key for R, then which of the following functional dependencies is guaranteed to hold for R?


(a) ABCE → D
(b) ABCD → E
(c) BCE → A
(d) None of the above


Answer:

(d) None of the above

Discussion/Reason:


It is given that the relation R is already in BCNF (refer here for rules of BCNF).
That means the left hand side of any functional dependencies that hold in R should be the candidate key. Hence, if the given functional dependencies to be held in R, then the left hand side of given functional dependencies should be BCD.
None of the given FDs have BCD alone in the left hand side. So option (d) is the answer.





         Previous Question                                                                                Next Question


Go to Multiple choices questions page

Find the candidate keys of a table in dbms an example

Find the candidate keys of a relation, How to find the candidate keys, Which is the key for the given table, concept of candidate key in dbms, candidate key examples


Question:
Consider a relation R with attributes ABCDEFGH and functional dependencies S as follows:
S = {A CD, ACF G, AD BEF, BCG D, CF AH, CH G, D B, H DEG}
Find all keys for R.

Solution:
We can find keys (candidate keys) for a relation by finding the closure of an/set of attributes. Checking each attribute or all subsets of the given set of attributes for a key is time consuming job. Hence, we may employ some of the following heuristics/assumptions in identifying the keys;

  • We may start checking all the left hand side attributes of any/all of the given set of functional dependencies.
  • If we find the closure of an attribute and that attribute is the candidate key then any superset cannot be the candidate key. For example, if A is a candidate key, then AB is not a candidate key but a super key.

LHS
Result
Decision
A+
= ACD from A CD
= ACDBEF from AD BEF
= ACDBEFG from ACF → G
= ACDBEFGH from CF → AH
Result includes all the attributes of relation R. That is, if we know A, then all the attributes of R could be uniquely determined. Hence, A is one candidate key.
ACF+
No need to find the closure of (ACF) because the subset A is already a candidate key.
ACF is a super key but not candidate key.
AD+
No need to find the closure of (AD) because the subset A is already a candidate key.
AD is a super key but not candidate key.
BCG+
= BCGD from BCG D
Result does not include all the attribute of relation R. Hence, (BCG) cannot be a candidate key.
CF+
= CFAH from CF AH
Further, as we know A now, then we can conclude that CF will uniquely determine all the other attributes of A.
Result includes all the attributes of relation R. Hence, (CF) is one candidate key.
D+
= DB from D → B
Result does not include all R. Hence, D cannot be a key.
H+
= HDEG from H DEG
= HDEGB from D B
Result does not include all R. Hence, H cannot be a key.
From the above table, it is clear that only A and CF are the candidate keys.



Go to - 1NF,    2NF,    3NF,    BCNF












Wikipedia

Search results