Databases Test 2

Concept:

A superkey is a set of attributes within a table whose values can be used to uniquely identify a tuple. A candidate key is a minimal superkey.

A superkey is a superset of candidate key or primary key.

Explanation:

Primary key is AE. (given)

All superkeys must contain this primary key AE. From the given keys, key, which doesn’t contain

the AE.

Here, option 3: DACB

“DACB” doesn’t contain the primary key AE. So, it is not a superkey.

Concept:

The closure of F, denoted as F+, is the set of all regular FD, that can be derived from.

For trivial functional dependency,

Let A and be two sets consists of attributes of a relation

A → B

A \(\supseteq\) B 

Explanation:

Option 1: 

{X, Z} → {Z, W}

{X, Z}  \(\nsupseteq\) {Z, W}

Not a trivial functional dependency

Option 2: 

{X, V} → {V}

{X, V} \(\supseteq\) {V}

It is a trivial functional dependency

Option 3: 

{X, W, V} → {Y}

{X, W, V} \(\nsupseteq\)  {Y}

Not a trivial functional dependency

Option 4: 

{Y, W } → {Y, X}

{Y, W } \(\nsupseteq\) {Y, X}

Not a trivial functional dependency

NOTE:

\(\supseteq\) → superset

\(\nsupseteq\) → not superset

Concept:

A foreign key in a table refers to the primary key of another table. It contains subset of entries of primary key of other table. It specifies the referential integrity constraint.

Explanation:

Given:

Two relations:

R₁(a, b, c) and  R₂(x, y, z)

a is the foreign key of R₁ that refers to the primary key of R₂

(a) Insert into R₁

It will cause violation. As a is the foreign key referring to the primary key of R₂. If we insert values in R₁, then that value must also be present in the primary key of R₂ and which is not necessary as while inserting in R₁ we are not aware of R₂.

(b) Insert into R₂

It will not cause violation. Because inserting element in R₂ will not effect R₁. Values present in R₂’s primary key may or may not present in foreign key of R₁.

(c) Delete from R₁

It has no effect on the relation. It will not cause violation. Values that are deleted from R₁ need not be deleted from R₂. As R₁ contains the foreign key.

(d) Delete from R₂

It will cause violation. If we delete tuples from R₂ relation, then if that tuple is present in R_1­ which is referring to the primary key of R₂ must also be deleted.

Functional Dependencies are: A -> BC and C-> D

First, find the candidate key for the relation R(A, B, C and D).

A⁺ = {A, B, C, D},

‘A’ closure is deriving all the attributes of the relation. So, it is candidate key

A → BC                        

This functional dependency is in BCNF form as left side is the key

C → D

In this functional dependency, left side is not the key so not in BCNF. Also, right side is not prime attribute. So, not in 3NF.

But it is in 2NF as there is no partial dependency in this.

So, when one functional dependency comes in weaker form then overall relation will be in that form. Here, given relation is in 2NF.

Since R of T₂ is referring to P of T₁

∏_R (t₂) ⊆ ∏_P (t₁)

∏_R (t₂) – ∏_P(t₁) = Φ (always holds)

Hence, I is true

∏_P(t₁) ≠ ∏_R(t₂)

It is possible if ∏_R(t₂) doesn’t contain the same number of elements (less) as  ∏_P(t₁)

but it not always true

∏_P (t₂) – ∏_R(t₁) = Φ

It is possible if ∏_R(t₂) contains the same number of elements as ∏_P(t₁)

but it not always true

0nly I always true

II and III may or may not be true

Concept:

Candidate key: it is the set of attributes that uniquely identifies a relation. It is also known as superkey with no repeated attributes.

Explanation:

Relation R = R = (P, Q, R, S, T, U)

F = {P ➝ Q, PQ ➝ S, T ➝ R, S ➝ P}

Find the closure of keys given. If all the attributes of relation are present in the closure, then it will be the candidate key of that relation.

(PTU)+ = {P, T, U, Q, R, S}

Therefore, PTU is a key

(QTU)+ = {Q, T, U, R, S, P}

Therefore, QTU is a key

(STU)+ = {S, T, U, R, P, Q}

Therefore, STU is a key

Hence there are 3 candidate keys.

Concept:

Lossless join: It guarantees that the spurious tuple generation problem does not occur with respect to the relation schemas created after decomposition

Dependency preservation: It ensures that each functional dependency is represented in some individual relation resulting after decomposition.

Explanation:

3NF:

• Third normal form is based on the concept of transitive dependency. A functional dependency X->Y in a relation schema R is a transitive dependency if there exists a set of attributed Z in R that is neither a candidate key nor a subset of any key of R.
• A relation is in 3NF if it satisfies 2NF and no prime attribute of R is transitively dependent on the primary key.
• If X - > A is a functional dependency, then A should be prime attribute or X should be a candidate key.
• Loss less join and dependency preservation is always possible in 3NF. 3NF decomposition is always lossless join and dependency preserving.

BCNF:

• It stands for Boyce codd normal form.
• A relation R is in BCNF if whenever a non-trivial functional dependency X -> A holds in R, then X is a superkey of R. Any relation with two attributes is always in BCNF. Because when a relation contains only two attributes than one attributes determines another and left side of the functional dependency will always be a candidate key in that case.
• BCNF is not always dependency preserving.

To check for lossless join decomposition using FD set, following conditions must hold:

In the given schema r(A, B, C, D, E), the candidate key is A because A⁺ = {A, B, C, D, E}

1. First condition holds true as Att(R1) U Att(R2) = (ABC) U (ADE) = (ABCDE) = Att(R).
2. Second condition holds true as Att(R1) ∩ Att(R2) = (ABC) ∩ (AD) ≠ Φ
3. Third condition holds true as Att(R1) ∩ Att(R2) = A is a key of R1(ABC) because A->BC is given.

Therefore, the relation is lossless.

For dependency preserving: If we decompose a relation R into relations R1 and R2, all dependencies of R either must be a part of R1 or R2 or must be derivable from combination of FD’s of R1 and R2.

Therefore, the above decomposition does not preserve dependency.

Statement I: False

In 3NF, R has a nontrivial functional dependency Y → X, where Y is not a superkey and X is a prime attribute

Statement II: False

In 2NF, R has a nontrivial functional dependency Y → X, where Y is not a superkey and X is a non-prime attribute and Y is a proper subset of some key.

Statement III: True

In BCNF, R has a nontrivial functional dependency Y → X, where Y is a superkey.

Therefore it is also in 2NF