1.

Given the relation R(A, B, C, D, E) with functional dependencies:

A→B
BC → D
A→C
D→E
a) Find the closure of A
b) List all candidate keys
c) Identify prime attributes and non-prime attributes more such types of question

Answer:

Given:

Relation: R(A, B, C, D, E)
Functional Dependencies (FDs):

1. A→B
2. BC → D
3. A→C
4. D→E

(a) Find the closure of A (A⁺)

To find the closure of A, start with A⁺ = {A}, and apply FDs until no new attributes can be added.

A → B → Add B ⇒ A⁺ = {A, B}
A → C → Add C ⇒ A⁺ = {A, B, C}
1.

Now, we have B and C → BC → D ⇒ Add D ⇒ A⁺ = {A, B, C, D}

2.

D → E ⇒ Add E ⇒ A⁺ = {A, B, C, D, E}
3.
4.

✅ So, A⁺ = {A, B, C, D, E}

(b) List all candidate keys

A candidate key is a minimal set of attributes whose closure contains all attributes in the relation.

We already found:

 A⁺ = {A, B, C, D, E} = all attributes ⇒ So, A is a candidate key

Now we check for other possible candidate keys by testing closures of other attribute combinations:

Try combinations:

 BC⁺
BC → D
D→E
But we don’t have A or B directly, so not a superkey

A → B, A → C ⇒ B, C
 AC⁺

BC → D
D→E
 So: A, C ⇒ {A, C, B, D, E} ⇒ All attributes
✅ A is already a candidate key, and C is dependent on A, so AC is not minimal (not a new
candidate key)
 AB⁺
A → C, A → B
BC → D

AB gives B, C, D, E ⇒ Again, A alone is enough ⇒ not minimal

D→E

✅ So, only candidate key is: {A}

(c) Identify prime attributes and non-prime attributes

 Prime attributes: Attributes that are part of a candidate key

→ Candidate key is {A}
→ So A is a prime attribute
 Non-prime attributes: Attributes not part of any candidate key
→ B, C, D, E

 Prime attribute: A
 Non-prime attributes: B, C, D, E

2. Given R(P, Q, R, S) and FDs:

PQ → R
R→S
Q→P
a) Find candidate keys
b) Decompose R into 2NF
c) Is the relation in BCNF?
d) Prime and non-prime attributes

Answer:

Given:

Relation: R(P, Q, R, S)
Functional Dependencies (FDs):

1. PQ → R
2. R → S
3. Q → P

(a) Find candidate keys

We need to find minimal attribute sets whose closure includes all attributes: {P, Q, R, S}.

Step 1: Check closure of Q

Q → P ⇒ add P ⇒ Q⁺ = {Q, P}
 Q⁺ = {Q}

PQ → R (we have P and Q now) ⇒ add R ⇒ Q⁺ = {P, Q, R}



R → S ⇒ add S ⇒ Q⁺ = {P, Q, R, S}


Q⁺ = {P, Q, R, S} ⇒ Q is a candidate key.

Step 2: Check closure of other combinations

 P⁺ = {P} (no FD starting with P alone)

 R⁺ = {R, S} (from R → S)
 PQ⁺ = {P, Q, R, S} (as above)

QR⁺ = {Q, P, R, S} (since Q → P) ⇒ QR is a superkey but not minimal (Q alone is key)

 PR⁺ = {P, R, S} (no FD from P alone)

 PS⁺, QS⁺, etc. won't have full closure.
Candidate key = {Q}[Answer]

(b) Decompose R into 2NF

 2NF means: No partial dependency on a proper subset of candidate key.

 Here candidate key is Q (single attribute), so no partial dependencies are possible.
 Hence, R is already in 2NF.

(c) Is the relation in BCNF?

 BCNF requires for every FD X → Y, X should be a superkey.

Check the FDs:

 PQ → R
o PQ⁺ = {P, Q, R, S}, PQ contains candidate key Q, so PQ is a superkey?
o Since Q is a candidate key alone, PQ is a superkey (superkey can have extra
attributes). So, FD okay.
 R→S

o R⁺ = {R, S} only, not all attributes ⇒ R is NOT a superkey.

o Is R a superkey?

o Violates BCNF.

o Q is a candidate key ⇒ FD okay.

 Q→P

Conclusion:
Relation R is NOT in BCNF due to FD R → S.

(d) Prime and non-prime attributes

Candidate key is {Q} ⇒ Prime attribute = Q

 Prime attributes: Attributes that are part of any candidate key.

 Non-prime attributes = {P, R, S}

Summary:
Part Answer

(a) Candidate Key Q

(b) 2NF Decomposition Already in 2NF (no partial dependencies)

(c) BCNF No, due to R → S violating BCNF

 Part Answer

(d) Prime Attributes Q

Non-prime Attributes P, R, S

3. Given R(W, X, Y, Z) with FDs:

W→X
X→Y
Y→Z

a) Find the closure of W

b) Find all candidate keys
c) Prime and non-prime attributes

Answer:

Given:

Relation: R(W, X, Y, Z)

Functional Dependencies (FDs):

W→X
X→Y
Y→Z

(a) Find the closure of W (W⁺)

Start with W⁺ = {W}.

From W → X ⇒ add X ⇒ W⁺ = {W, X}

From X → Y ⇒ add Y ⇒ W⁺ = {W, X, Y}

From Y → Z ⇒ add Z ⇒ W⁺ = {W, X, Y, Z}

Answer: W⁺ = {W, X, Y, Z}

(b) Find all candidate keys

Candidate key = minimal set of attributes whose closure is all attributes {W, X, Y, Z}.

From (a), W⁺ = {W, X, Y, Z}, so W alone is a candidate key.

Check if any other attribute or combination is a candidate key:

X⁺ = {X} → X → Y ⇒ {X, Y}

Y → Z ⇒ {X, Y, Z}

Missing W, so X is not a key.

Y⁺ = {Y} → Y → Z ⇒ {Y, Z}

Missing W and X, so no.

Z⁺ = {Z} only Z, no FD starting from Z.

WX, WY, etc. are supersets of W, not minimal.

Answer: Candidate key = {W}

(c) Prime and non-prime attributes

Prime attributes: Attributes that are part of any candidate key

→ Candidate key = {W}

→ Prime attribute: W

Non-prime attributes: All other attributes

→ Non-prime: X, Y, Z

Summary:

Part Answer

(a) Closure of W {W, X, Y, Z}

(b) Candidate Keys {W}

(c) Prime Attributes W

Non-prime Attributes X, Y, Z

4. Given the relation R(A, B, C, D, E) with functional dependencies:

A → BC
C→D
D→E
a) All keys
b) All superkeys
c) Find the closure of C
d) Which normal form does the relation satisfy?
Answer:

Given:
Relation R with attributes {A, B, C, D, E}
Functional Dependencies (FDs):

1. A → BC
2. C → D
3. D → E

1. List all keys

A key is a minimal set of attributes whose closure contains all attributes.

Let's assume the relation schema is R(A, B, C, D, E).

Step 1: Find closure of A:

A → BC ⇒ add B, C ⇒ A⁺ = {A, B, C}
 A⁺ starts with {A}

C → D ⇒ add D ⇒ A⁺ = {A, B, C, D}


D → E ⇒ add E ⇒ A⁺ = {A, B, C, D, E}



Since A⁺ contains all attributes, A is a candidate key.

Step 2: Check if any other key exists.

C → D ⇒ {C, D}
 C⁺ = {C}

D → E ⇒ {C, D, E}


Missing A and B ⇒ Not a key.



B⁺ = {B} (No FD starting from B) ⇒ Not a key.



 Check combination C with other attributes, e.g., AC or BC. Since A alone is key, supersets
are not minimal.

Candidate keys: {A}

2. List all superkeys

 Superkey: Any superset of candidate key containing candidate key.

 Candidate key is {A}, so any attribute set containing A is a superkey:

{A}, {A, B}, {A, C}, {A, D}, {A, E}, {A, B, C}, ... etc.

3. Find the closure of C (C⁺)

Start with C⁺ = {C}.

C → D ⇒ add D ⇒ C⁺ = {C, D}
D → E ⇒ add E ⇒ C⁺ = {C, D, E}



C⁺ = {C, D, E}

4. Which normal form does the relation satisfy?

Step 1: Identify candidate key: {A}

Step 2: Check FDs and their determinants:

A → BC: Determinant A is candidate key ⇒ No violation of BCNF

C → D: Determinant C is not a candidate key ⇒ Violates BCNF


D → E: Determinant D is not candidate key ⇒ Violates BCNF




Is it in 3NF?

3NF allows FDs where the determinant is not a key if the dependent attribute is prime.

 Prime attributes = attributes in candidate keys = {A}

 Check FDs violating BCNF:
 C → D: C is not key, D is not prime → violates 3NF
 D → E: D is not key, E is not prime → violates 3NF

Is it in 2NF?

2NF prohibits partial dependency on part of a composite key. Since the candidate key is single
attribute A, no partial dependency possible. So relation is at least in 2NF.
Relation is in 2NF only (not in 3NF or BCNF).[Answer]

Summary:
Question Answer

All keys {A}

All superkeys All sets containing A

Closure of C {C, D, E}

Normal form satisfied 2NF

5. Given:
R(U, V, W, X, Y)
FDs:

U→V
V→W
W→X
X→Y

Tasks:

a) Find closure of U
b) List all candidate keys
c) Identify prime and non-prime attributes

Answer:
U → V ⇒ {U, V}
a) U⁺ = {U}

V → W ⇒ {U, V, W}
W → X ⇒ {U, V, W, X}
X → Y ⇒ {U, V, W, X, Y}
Closure: {U, V, W, X, Y}

b) Candidate key = {U}

c) Prime attribute = {U}

Non-prime attributes = {V, W, X, Y}

6. Given:
R(M, N, O, P)
FDs:

 MN → O
 O→P
 N→M

Tasks:

a) Find closure of N
b) Find all candidate keys
c) Identify prime and non-prime attributes

Answer:

N → M ⇒ {N, M}
a) N⁺ = {N}

MN → O (M and N present) ⇒ {N, M, O}

O → P ⇒ {N, M, O, P}
Closure: {M, N, O, P}

b) Candidate key = {N}

c) Prime attribute = {N}

Non-prime attributes = {M, O, P}

7. Given the relation R(A, B, C, D) with functional dependencies:

A→B
B→C
A→D
a. Identify the candidate key(s).
b. What is the highest normal form of R?
c. Decompose R into BCNF if necessary.

Answer:

Given:

Relation: R(A, B, C, D)
Functional Dependencies (FDs):
 1. A → B
2. B → C
3. A → D

(a) Identify the candidate key(s)

We want to find the minimal set of attributes whose closure contains all attributes {A, B, C, D}.

Step 1: Find closure of A:

A → B ⇒ add B ⇒ A⁺ = {A, B}
 A⁺ = {A}

B → C ⇒ add C ⇒ A⁺ = {A, B, C}


A → D ⇒ add D ⇒ A⁺ = {A, B, C, D}



Since A⁺ contains all attributes, A is a candidate key.

Step 2: Check if any other candidate keys exist:

B → C ⇒ {B, C}
 B⁺ = {B}

Missing A and D ⇒ Not a key.



 C⁺ = {C} only C. No FD starts with C.
 D⁺ = {D} only D. No FD starts with D.
 Check combinations like B, C or B, D, etc. Since A alone is key, supersets including A are
not minimal keys.

Candidate key(s): {A}

(b) What is the highest normal form of R?

Check normal forms in order:

 1NF: By default, relations are in 1NF if atomic values (assumed).

Candidate key is single attribute A ⇒ No partial dependency possible ⇒ Relation is in 2NF.

 2NF: No partial dependency on part of candidate key.

 3NF: For every FD X → Y, either

o X is a superkey, or
o Y is a prime attribute (part of candidate key).

Let's check the FDs:

A → B: A is candidate key ⇒ OK
B → C: B is NOT a superkey, and C is NOT prime ⇒ Violation of 3NF


A → D: A is candidate key ⇒ OK


 So relation is not in 3NF due to B → C.
 BCNF: Requires all determinants to be superkeys, so same violation here.

Highest normal form: 2NF

(c) Decompose R into BCNF if necessary

The violating FD is:
B → C where B is not a superkey.

We need to decompose relation R into two relations:

1. R1(B, C) with FD: B → C (now B is key in R1)

2. R2(A, B, D) with FDs: A → B, A → D

Check R2:

 Candidate key for R2 is A (since A → B and A → D)

Both relations are in BCNF:

 R1: B → C and B is candidate key (BCNF)

 R2: A → B, A → D and A is candidate key (BCNF)

Final answers:

(a) Candidate Key(s): {A}

(b) Highest Normal Form: 2NF

(c) BCNF Decomposition: R1(B, C) and R2(A, B, D)

8. Given the relation R(A, B, C, D, E) with functional dependencies:

A→B
BC → D
A→C
D→E
a) Find the closure of A
b) List all candidate keys
c) Identify prime attributes and non-prime attributes

Answer:

Given:

Relation R(A, B, C, D, E)
Functional Dependencies (FDs):

1. A→B
2. BC → D
3. A→C
4. D→E

(a) Find the closure of A (A⁺)

Start with A⁺ = {A}

A → B ⇒ add B ⇒ A⁺ = {A, B}
A → C ⇒ add C ⇒ A⁺ = {A, B, C}


BC → D: We have B and C in A⁺, so add D ⇒ A⁺ = {A, B, C, D}



D → E ⇒ add E ⇒ A⁺ = {A, B, C, D, E}



Answer:
A⁺ = {A, B, C, D, E}

(b) List all candidate keys

Candidate key: minimal attribute set whose closure contains all attributes {A, B, C, D, E}

From (a), A⁺ includes all attributes ⇒ A is a candidate key.

Check if other keys exist:

 B⁺ = {B} (no FD starts from B alone)

 C⁺ = {C} (no FD starts from C alone)

o BC → D ⇒ add D ⇒ {B, C, D}
 BC⁺ = {B, C}

o D → E ⇒ add E ⇒ {B, C, D, E}

D⁺ = {D} → D → E ⇒ {D, E}
o Missing A, so BC is not key.

 Other combinations like AC, AB, etc., but since A alone is key, no need to check supersets.

Answer:
Candidate key = {A}

(c) Identify prime and non-prime attributes

Candidate key is {A} ⇒ Prime attribute: A

 Prime attributes = attributes that appear in any candidate key

 Non-prime attributes: B, C, D, E

Summary:
Part Answer

(a) Closure of A {A, B, C, D, E}

(b) Candidate Keys {A}

(c) Prime Attributes A

Non-prime Attributes B, C, D, E

9. Given the relation R(A, B, C, D, E) with functional dependencies: A → B

B→C
CD → E
 A→D
Find the closure of A, Identify all candidate keys, Identify prime attributes and non-prime
attributes and What is the highest normal form satisfied?
Answer:

Given:

Relation R(A, B, C, D, E)
Functional Dependencies (FDs):

1. A→B
2. B→C
3. CD → E
4. A→D

1. Find the closure of A (A⁺)

Start with A⁺ = {A}

A → B ⇒ add B ⇒ A⁺ = {A, B}
B → C ⇒ add C ⇒ A⁺ = {A, B, C}


A → D ⇒ add D ⇒ A⁺ = {A, B, C, D}


CD → E: We have C and D in A⁺ ⇒ add E ⇒ A⁺ = {A, B, C, D, E}




Answer:
A⁺ = {A, B, C, D, E}

2. Identify all candidate keys

A candidate key is a minimal set of attributes whose closure contains all attributes.

Since A⁺ contains all attributes, A is a candidate key.

Check if other keys exist:

o B → C ⇒ {B, C}
 B⁺ = {B}

o CD → E requires both C and D, but D missing ⇒ B⁺ incomplete.

 C⁺ = {C} only.
 D⁺ = {D} only.

o CD → E ⇒ add E ⇒ {C, D, E}
 CD⁺ = {C, D}

Missing A, B.
 AC, AB, etc., but since A alone is a candidate key, no smaller or equal sets to check.

Answer:
Candidate key = {A}

3. Identify prime and non-prime attributes

 Prime attributes: those that appear in any candidate key.

Candidate key = {A}
So, prime attributes = {A}
  Non-prime attributes = {B, C, D, E}

4. Highest Normal Form satisfied by R

 Candidate key: {A}

Check FDs for normal forms:

A → B: A is key ⇒ satisfies BCNF

B → C: B not a key, C not prime ⇒ violates BCNF and 3NF


CD → E: CD not a key, E not prime ⇒ violates BCNF and 3NF



A → D: A is key ⇒ satisfies BCNF




Therefore:

 Not BCNF due to B → C and CD → E

2NF: Candidate key is single attribute A ⇒ no partial dependency possible ⇒ relation is in

 Not 3NF due to same reasons

2NF

Final Answer:

Part Answer
Closure of A {A, B, C, D, E}
Candidate Keys {A}
Prime Attributes {A}
Non-prime Attributes {B, C, D, E}
Highest Normal Form 2NF

10. Relation:
R(A, B, C, D, E)

Functional Dependencies (FDs):

A ->B
B -> C
A -> D
D -> E

(a) Define functional dependency. Explain its importance in normalization.

(b) Find the candidate key(s) for relation R using the given FDs.
(c) Decompose the relation R into Third Normal Form (3NF). Show all steps clearly.

Answer:

(a) Definition of Functional Dependency (2 marks)

A functional dependency (FD), denoted as ( X ->Y ), is a constraint between two sets of attributes in a
relation. It means that if two tuples have the same value for attribute set X, they must also have the
same value for Y.
Importance in Normalization:

 FDs help identify redundancy in a relation.

 Used to determine keys and to decompose relations into normal forms (1NF, 2NF, 3NF,
BCNF).
 Ensure data integrity and remove anomalies (insert, update, delete).

(b) Find Candidate Key(s) (2 marks)

We will compute the attribute closure of A:

1. Start with A+ = {A}

2. A ->B → Add B
A+ = {A, B}
3. B ->C → Add C
A+ = {A, B, C}
4. A ->D → Add D
A+ = {A, B, C, D}
5. D ->E → Add E
A+ = {A, B, C, D, E}

So, A+ = all attributes → A is a candidate key.

There are no other minimal keys because B, D, etc., alone cannot determine all attributes.

Candidate Key: {A}

(c) Decompose into 3NF (4 marks)

Step 1: Check each FD for 3NF condition:

A relation is in 3NF if for each FD ( X ->Y ), one of the following is true:

 X → Y is trivial (Y ⊆ X)
 X is a super key
 Y is a prime attribute (part of a candidate key)

Candidate key: A

Let’s evaluate:

1. A ->B → A is candidate key ✅

2. B ->C → B is not a super key ❌
3. A ->D → A is candidate key ✅
4. D ->E → D is not a super key ❌

So, FDs 2 and 4 violate 3NF.

Step 2: Create relations for each FD:

1. A ->B : → Relation R1(A, B)

2. B ->C : → Relation R2(B, C)
3. A ->D : → Relation R3(A, D)
 4. D ->E : → Relation R4(D, E)

Now check all attributes:

All attributes: A, B, C, D, E
Covered across all relations

Step 3: Ensure candidate key is preserved.

Candidate key A is present in R1 and R3. But none of the relations contains all attributes of R.

So, we add a relation with the full candidate key:

R5(A, B, C, D, E) is not needed if all attributes and dependencies are preserved.

But in 3NF synthesis, we ensure the key is preserved by adding a relation that includes the full key:

Final 3NF Relations:

1. R1(A, B)
2. R2(B, C)
3. R3(A, D)
4. R4(D, E)
5. R5(A) (to preserve the candidate key if not covered completely above)

However, if the key A appears as a left side in multiple relations and we can infer the full key, R5 may
be optional.

11. Lossless and Dependency Preserving?

Lossless Join:

 As A is a candidate key and appears in multiple relations (R1, R3), and we preserve
connections, the join is lossless.

Dependency Preservation:

 All functional dependencies are included across the decomposed relations, so dependencies
are preserved.