A. Define the term Functional Dependency as it applies to attributes in a single

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.

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.