slide1 n.
Skip this Video
Loading SlideShow in 5 Seconds..
1NF, 2NF, and 3NF PowerPoint Presentation
Download Presentation
1NF, 2NF, and 3NF

play fullscreen
1 / 15
Download Presentation

1NF, 2NF, and 3NF - PowerPoint PPT Presentation

Download Presentation

1NF, 2NF, and 3NF

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

    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.

    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.

    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.