How to find candidate key before Normalization?

Finding candidate keys is the foundation before we normalize a relation (2NF, 3NF, BCNF)

Q. What is a Candidate Key?

Ans: A candidate key is a minimal set of attributes that can uniquely identify every tuple (row) in a relation. "Minimal" means: if you remove any attribute from it, it will no longer uniquely identify.

Q. What are the steps to find candidate key?

Steps to Find Candidate Keys

1. List all attributes in the relation.

2. List given Functional Dependencies (FDs).

3. Find closure of attributes (denoted as X⁺ = all attributes X can determine).

4. If X⁺ = all attributes in relation → X is a superkey.

5. Check minimality: if no proper subset of X is a superkey → X is a candidate key.

Let's try to understand this with simple example

Example:

Given: 

Relation with attributes and its functional dependencies are given here in case study.

Relation R (A, B, C, D, E)

Functional Dependencies (FDs):

A → B

B → C

CD → E

E → A

Now apply above steps to find out Candidate Key from the above relation.

Step 1: Check closures

A⁺ = {A, B, C} (from A→B, then B→C) → not all attributes.

B⁺ = {B, C} → not all.

C⁺ = {C} → not all.

D⁺ = {D} → not all.

E⁺ = {E, A, B, C} (E→A, then A→B, B→C) → missing D → not all.

Step 2: Try combinations

AE⁺ = {A, B, C, E} → still missing D.

AD⁺ = {A, B, C, D} → missing E.

ED⁺ = {E, A, B, C, D} → this gives all attributes → superkey.

Step 3: Check minimality

From ED, 

Remove E: D⁺ = {D} → not all.

Remove D: E⁺ = {E, A, B, C} → not all.

→ So, ED is minimal → Candidate Key.

Step 4: Verify if there are others

Try CD⁺ = {C, D, E, A, B} → also gives all → Candidate Key.

So, candidate keys are:

{ED}, {CD}

Q. Why Candidate Key helps in Normalization?

Ans:

Candidate keys help us identify prime attributes (those that are part of any key).

In higher normalization (2NF, 3NF, BCNF), we check FDs based on candidate keys and prime attributes