1NF, 2NF, and 3NF

1
1NF, 2NF, and 3NF

• By
• Vishal Punjabi

2
Basics

• Lets first review
• Superkey
• Candidate Key
• Primary Key

3
Superkey

• A set of one or more attributes, which taken
  collectively, uniquely identifies a tuple of a
  relation is referred to as a the superkey of the
  relation schema.

4
Example
5
Ten superkeys

• Rx_rx
• (Rx_rx, Rx_pat)
• (Rx_rx, Rx_medcode)
• (Rx_rx, Rx_dosage)
• (Rx_pat, Rx_medcode)
• (Rx_rx, Rx_pat, Rx_medcode)
• (Rx_rx, Rx_pat, Rx_dosage)
• (Rx_rx, Rx_medcode, Rx_dosage)
• (Rx_pat, Rx_medcode, Rx_dosage)
• (Rx_rx, Rx_pat, Rx_medcode, Rx_dosage)

6
Candidate key

• Defined as a superkey with no proper subsets that
  are superkeys
• A candidate key has two properties
• Uniqueness
• Two tuples of a relation schema cannot have
  identical values for the collection of
  attribute(s) that constitute the candidate key
• Irreducibility
• No proper subset of the candidate key has the
  uniqueness property

7
Two candidate keys

• Rx_rx
• (Rx_pat, Rx_medcode)

8
Primary key (vs. candidate key)

• A primary key is a candidate key (an irreducible
  unique identifier) with one additional property
• Entity integrity constraint
• Specifies that the primary key of a relation
  schema cannot have a missing value (i.e., a
  null value), essentially assuring identification
  of every tuple in a relation

9
Why Nomarlize? What are Normal Forms?

• To remove data redudancy
• A stepwise progression toward the goal of a fully
  normalized relation schema that is guaranteed to
  be free of data redundancies that cause
  modification anomalies from a functional
  dependency perspective

10
First Normal Form (1NF)

• Definition
• - A scheme R is in 1NF only when the attributes
  comprising the schema are atomic and
  single-valued. Unless a schema is in 1NF it is
  not a relation schema. That is, a relation
  schema is, by definition, in 1NF.

11
(No Transcript)
12
Second Normal Form (2NF)

• Definition
• - A relation schema R is in 2NF if every
  non-prime attribute in R is fully functionally
  dependent on the primary key of R- i.e., a
  non-prime attribute is not functionally dependent
  on a proper subset of the primary key of R.

13
(No Transcript)
14
Third Normal Form (3NF)

• Definition
• - A relation schema R is in 3NF if no non-prime
  attribute is functionally dependent on another
  non-prime in R.

15
(No Transcript)