1. 1NF, 2NF, and 3NF By
2. Basics Lets first review
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.
5. Ten superkeys Rx_rx#
(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:
Two tuples of a relation schema cannot have identical values for the collection of attribute(s) that constitute the candidate key
No proper subset of the candidate key has the uniqueness property
7. Two candidate keys Rx_rx#
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.