1 / 15

1NF, 2NF, and 3NF

Faraday
Download Presentation

1NF, 2NF, and 3NF

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

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.

More Related