A. Define the term Functional Dependency as it applies to attributes in a single
ID: 3885234 • Letter: A
Question
A. Define the term Functional Dependency as it applies to attributes in a single table.
B. What makes a functional dependency trivial?
C. Assume a relation R(A,B,C,D) with primary key AB is in 1st normal form, but not 2 nd normal form. Give at least three potential FDs that determine only part of a row.
D. What if R above is in 2nd normal form, but not 3rd normal form. Give an example FD that prevents R from being in 3rd normal form.
E. What if R has a key AB. What FD must be true for R to be in BCNF?
F. Assume R has the FDs: A-> ABCD and C->D, the last of which causes a violation of 3rd normal form. Decompose R into two relations R1 and R2 that are in 3NF. What attributes of R are in R1 and what attributes of R are in R2?
G. SQL permits arithmetic like Select A*D from R; Given that information and R1 and R2 from part F, write a select statement using JOIN that returns A*D
Explanation / Answer
a) Functional dependency : When one attribute is functionally depended on another attribute then those two attributes are involved in functional dependency.
for example, if two rows has same value for attribute A then those two rows has same values for attribute B too then we can say that B is functionally dependent on A.
i.e A functionally determines B. It is denoted as A->B
b) Trivial function dependency : Functional dependency x->y is called trivial if y is subset of x.
c) Given relation R is not in 2nd normal form, i.e there is at least one partial dependency.
i.eat least one non prime attribute functionally dependent on prime attribute.
Here A->C, B->D and ABC->D these functional dependencies determine only part of row because two of them are partial dependencies.
d) R is in 2NF. Hence there is no partial dependencies.
It is not in 3NF hence there are transitive dependencies.
FDs that can prevent R from being in 3NF : A->C, C->D
here AB is CK and given dependencies shows transitive dependency.
e) Here if there are only two FDs: A->C and B->D
then R is in BCNF and AB is also a key.
f) here C->D causes violation of 3NF by adding transitive dependency.
Decomposition :
R1(ABC) with FD : A->ABC
and R2(CD) with FD : C->D.
R1 has 3 attributes : A, B, C
R2 has 3 attributes : C, D
g) SQL statement :
select R1.A, R2.D
from R1 inner join R2
on R1.C = R2.C
this query will return all pairs of (A,D) from R.
if * is multiplication operation then we can perform it like this :
select R1.A*R2.D as multiplication
from R1 inner join R2
on R1.C = R2.C
you can give further clarification about last question if both context are wrong.
if you have any doubts then you can ask in comment section.