Normalization Quiz

A relation is in third normal form (3NF) if it is in second normal form (2NF) and there are no functional (___________) dependencies between two (or more) nonprimary key attributes To convert a relation into 3NF, decompose the relation into new relations using __________

- transitive - determinates

- get rid of any columns that hold the same data (no repeating groups) - split up data that can be no-many-to-many relations - each row must be unique. All fields dependent on primary key

1st normal form

No repeating groups (no arrays) -> phone number example Each row must be unique -> enforcing referential integrity takes care of this

1st normal form

No partial key dependencies -> full functional dependency If you are in 1NF and do not have a composite key you are in _______

2nd normal form

get rid of data not dependent on every part of primary key. Full function dependency

2nd normal form

if there is no composite key and the relation is in 1st normal form then the relation is in

2nd normal form automatically

relational database model: Five properties

Entries in cells are simple (single value in each cell). Entries in columns are from the same set of values. Each row is unique. The sequence of columns can be interchanged without changing the meaning or use of the relation. The rows may be interchanged or stored in any sequence.

Each nonprimary key attribute is identified by the whole key (called full functional dependency)

second normal form

A relation is normalized to the 3rd normal form if each field is dependent on:

The key (each row must be unique and no repeating groups); (1st) The whole key (full functional dependency); and (2nd) Nothing but the key (no transitive dependencies). (3rd)

At every row and column intersection there exists a single value, never a list of values.

first normal form

A particular relationship between two attributes. For a given relation, attribute D is functionally dependent on attribute A if, for every valid value of A, that value of A uniquely determines the value of D

functional dependency

What is normalization based on?

functional dependency (FD)

A named, two-dimensional table of data. Each relation consists of a set of named columns and an arbitrary number of unnamed rows.

relation

Data represented as a set of related tables or relations

relational database model

Nonprimary key attributes do not depend on each other (called transitive dependencies)

third normal form

To Fix a List of Values Problem

1. Put the list of values fields in a new table with a unique identifier (primary key). Example: PhoneNo & PK PhoneID 2. Put the primary key from the first table in the new table. Example: (PhoneID, PhoneType,PhoneNo,Customer_ID) 3. The first table now looks like: (Customer_ID,FName,LName)

- keep splitting it up no many to many relations - no non-key attribute should be dependent on another non-key attribute. No transitive dependencies

3rd normal form

Nonprimary key attributes do not depend on each other -> transitive dependency

3rd normal form

So, how do you know if the relationship is FD?`

Instances or sample data in a relation do not prove the existence of a functional dependency Knowledge of data domain is the most reliable method for identifying functional dependency

To convert a relation into 2NF, decompose the relation into new relations using the attribute(s), called _________________, that determine other attributes The _______________ becomes the primary key of the new relation

determinates

Definition: The process of converting complex data structures into simple, stable data structures What does it do? Eliminates redundancy (like Table Analyzer)

normalization