FOR FREE YEAR SOLVED

2NF:

Before seeing this chapter follow previous chapters: 

Concept of Normalization, 1NF, Find Super and Candidate Key from Attribute Closer.

(R is in 2NF) ⇔ [(R should be in 1NF) ∩ (not contain any Partial Dependency)]

Partial Dependency (PD):

Let X is Candidate Key (CK) and Y is a subset of candidate key ‘X’. ‘A’ is a non-prime attribute (NPA).

Partial Dependency (PD): If ‘Y’ is a subset of candidate key ‘X’ and A is a non-prime attribute then Y → A is partial dependency.

Functional Dependency (FD): If ‘Y’ is not a subset of candidate key ‘X’ and A is prime attribute then Y → A is functional dependency.

Example: 

Then A → CD is a Partial Dependency (PD) because A → AB and ‘CD’ is Non-Prime Attribute (NPA), same B → D is Partial dependency because B ⊂ AB and D is NPA.

What about B → AC [where A – Prime Attribute and C – Non-Prime Attribute] then we decompose it B → A  (FD), B → C  (PD).

So, B → AC  not 2NF because partial dependency exists B → C.

Definition: A relation schema R is in 2NF if every non-prime attribute A in R is not partially dependent on any key of R.

# Let R (ABCD) and CK = {B, A, E} in this case no Partial Dependency is possible because all Candidate Key (CK) is single not compound. So, if it is single then subset is not possible (Y ⊂ CK). So, Partial Dependency is not possible.

#  So if all CK of a relation is atomic (single) or all attribute of a relation prime attribute then this relation is in 2NF i.e. no Partial Dependency is possible.

Another Example:

Let AB → C is in 2NF because AB is not a proper subset of CK (AB itself CK)

C → D is in 2NF because of C ⊄ CK.

B → E is not in 2NF because B ⊂ AB and E are NPA.

So, here B → E creates redundancy.

Primary key of R, here only candidate key is AB. So, because we know B → E creates redundancy, then we know the point from where we decompose the table.

We generally break the table in such a way, that we create a table with those attributes which create the problem (by PD) and creates another table by the rest of the attributes. One attribute should be common in both tables to keep lossless decomposition and dependency preserving.

How redundancy removes which only created by B → E. May be ${\mathrm{R}}_1$ contain some redundancy but here we only remove redundant which is from B → E.

Now there is no Partial Dependency here because Candidate Key of ${\mathbf{R}}_{\mathbf{1}}$ is AB and ${\mathbf{R}}_{\mathbf{2}}$ is B.

Now rest of the redundancy we solve at 3NF.

2NF reduces some of the redundancy.

Redundancy Level