Test: Functional Dependencies & Normalization- 2 - Computer Science Engineering (CSE) MCQ

A trivial functional dependency is a database dependency that occurs when describing a functional dependency of an attribute or of a collection of attributes that includes the original attribute.

So, Option c is correct answer.

1. y is dominant entity.

2. x is subordinate entity..
Since, y is dominant entity. So does not depend on any other and x is subordinate. So x is existence dependent on y and deletion of x does not effect y, So, x is detected, so y is incorrect.

Relations produced from E-R model generally in 1-NF form.

The relation EMPLOYEE has one to one relation b/w project and employee i.e. each employee has being assigned a project and each project is associated with only one employee, hence project- number is functionally dependent on Emp-no.

R (a, b, d, c) and S (e,f)
1. Insertion into R does not create any violation,
2. Insertion into S create violation.
3. Deletion in S does not create any violation.
4. Deletion in R can create violation.

In the given FDs,

1) Convert elements on LHS as singleton

           Thus, A -> BC can be written as A-> B and A->C

2) Remove composite attributes from LHS

           Here, AB -> C can be written as A -> C, because we have an FD {A -> B}.

3) Remove redundant attributes

        We have FD = {A -> B, A -> C , B -> C}

       this is a transitive depenency A -> B -> C. After remove redundancy, we get A->B, B->C.

Option A is the required answer

BCNF relation may or may not preserve dependency also any relation cannot be necessarily decomposed into BCNF relation,

Clearly we can see that B is the common attribute and B is not candidate key of any of the decomposed relations. Hence, dependency is not preserved.

R = {A, B, C, D, E, F}
F = {A → B , C → DE, AC → F)
R₁ = ( A B )
R₂ =.{C,D,E}
R₃ = {A, C, F}

Convert b to a in 3^rd row convert b to a in 3^rd row. Since 3^rd row contains all ‘a’s hence the relation has lossless join property.

Generally Normalization is done on the schema itself. From the relational instance given,we may strike out FD s that do not hold. e.g.B does not functionally determine C(This is true). But we cannot say that A functionally determines B for the entire relation itself. This is because that, A->B holds for this instance, but in future there might be some tuples added to the instance that may violate A->B. So overall on the relation we cannot conclude that A->B, from the relational instance which is just a subset of an entire relation. 

R = (A, B, C, D, E)
F = (A →BC, C D → E, D,B → D, E→A)
A⁺ = ABCDE so A is the candidate key.
A can be replaced by E
So, E also a candidate key.
Scan be replaced by CD.
Hence, (A, E, C, D) are the candidate keys.

A relation is in 3NF if for X →A
(i) X is a super key or candidate key
(ii) A is a prime attribute.
Hence, for A to be non-key attribute (non-prime attribute)
X must be satisfying (i)

1) Pin code →  city, state

2) Street, city, state →  pin code

The candidate keys of relation is empcode as it uniquely identifies the relation.

Hence, it is in 2NF and therefore, also in 1NF.