# DBMS | How to find the highest normal form of a relation

To understand this topic, you should have basic idea about

Functional Dependency & Candidate keys

**Steps to find the highest normal form of a relation:**

- Find all possible candidate keys of the relation.
- Divide all attributes into two categories: prime attributes and non-prime attributes.
- Check for 1
^{st}normal form then 2^{nd}and so on. If it fails to satisfy n^{th }normal form condition, highest normal form will be n-1.

**Example 1. ****Find the highest normal form of a relation** R(A,B,C,D,E) with FD set {A->D, B->A, BC->D, AC->BE}

**Step 1.** As we can see, (AC)^{+} ={A,C,B,E,D} but none of its subset can determine all attribute of relation, So AC will be candidate key. A can be derived from B, so we can replace A in AC by B. So BC will also be a candidate key. So there will be two candidate keys {AC, BC}.

**Step 2.** Prime attribute are those attribute which are part of candidate key {A,B,C} in this example and others will be non-prime {D,E} in this example.

**Step 3.** The relation R is in 1^{st} normal form as a relational DBMS does not allow multi-valued or composite attribute.

The relation is not in 2^{nd} Normal form because A->D is partial dependency (A which is subset of candidate key AC is determining non-prime attribute D) and 2^{nd} normal form does not allow partial dependency.

So the highest normal form will be 1^{st} Normal Form.

**Example 2.** Find the highest normal form of a relation** R(A,B,C,D,E) with FD set as {BC->D, AC->BE, B->E}**

**Step 1.** As we can see, (AC)^{+} ={A,C,B,E,D} but none of its subset can determine all attribute of relation, So AC will be candidate key. A or C can’t be derived from any other attribute of the relation, so there will be only 1 candidate key {AC}.

**Step 2.** Prime attribute are those attribute which are part of candidate key {A,C} in this example and others will be non-prime {B,D,E} in this example.

**Step 3.** The relation R is in 1^{st} normal form as a relational DBMS does not allow multi-valued or composite attribute.

The relation is in 2^{nd} normal form because BC->D is in 2^{nd} normal form (BC is not proper subset of candidate key AC) and AC->BE is in 2^{nd} normal form (AC is candidate key) and B->E is in 2^{nd} normal form (B is not a proper subset of candidate key AC).

The relation is not in 3^{rd} normal form because in BC->D (neither BC is a super key nor D is a prime attribute) and in B->E (neither B is a super key nor E is a prime attribute) but to satisfy 3^{rd} normal for, either LHS of an FD should be super key or RHS should be prime attribute.

So the highest normal form of relation will be 2^{nd} Normal form.

**Example 3. **Find the highest normal form of a relation **R(A,B,C,D,E)** with **FD set {B->A, A->C, BC->D, AC->BE}**

**Step 1.** As we can see, (B)^{+} ={B,A,C,D,E}, so B will be candidate key. B can be derived from AC using AC->B (Decomposing AC->BE to AC->B and AC->E). So AC will be super key but (C)^{+} ={C} and (A)^{+} ={A,C,B,E,D}. So A (subset of AC) will be candidate key. So there will be two candidate keys {A,B}.

**Step 2.** Prime attribute are those attribute which are part of candidate key {A,B} in this example and others will be non-prime {C,D,E} in this example.

**Step 3**. The relation R is in 1^{st} normal form as a relational DBMS does not allow multi-valued or composite attribute.

The relation is in 2^{nd} normal form because B->A is in 2^{nd} normal form (B is a super key) and A->C is in 2^{nd} normal form (A is super key) and BC->D is in 2^{nd} normal form (BC is a super key) and AC->BE is in 2^{nd} normal form (AC is a super key).

The relation is in 3^{rd} normal form because LHS of all FD’s are super keys. The relation is in BCNF as all LHS of all FD’s are super keys. So the highest normal form is BCNF.

Article contributed by Sonal Tuteja. Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.

## Recommended Posts:

- DBMS | Introduction of 4th and 5th Normal form
- DBMS | Domain Key normal form
- SQL query to find second highest salary?
- DBMS | Advantages of DBMS over File system
- Database Normalization | Normal Forms
- Need for DBMS
- Starvation in DBMS
- Disadvantages of DBMS
- DBMS | Interfaces
- Deadlock in DBMS
- Use of DBMS in System Software
- DBMS | Why recovery is needed?
- DBMS | Data Replication
- DBMS - File Organization | Set 4
- DBMS | Enhanced ER Model