Ch. 6: Normalization

What are the steps for 1NF

1) Eliminate repeating groups -Each row/column intersection contains only 1 value 2) Identify primary key -May have to combine 3)Identify dependencies -all attributes dependent on Pkey

Steps to get to 2NF

1) Make new tables to eliminate partial dependencies (keep determinants in original table as well) 2) Reassign corresponding dependent attributes - determine attributes that are dependent on other attributes

Steps to get to 3NF

1) Make new tables to eliminate transitive dependencies - For each transitive, write a copy of its determinant as a PK for new table 2) Reassign corresponding dependent attributes - identify attributes dependent on each determinant identified in step 1 + dependency - name table to reflect its contents and functions

What is 2NF?

1) Table is in 1NF (no partial dependencies, repeating groups) 2) No partial dependencies (no attribute is dependent on only portion of PK)

What is a repeating group and how does it relate to 1NF

1NF requires that a table does not have any repeating groups, which is when row/column intersections contain multiple values

How many normal forms are there? What are they?

5 1NF 2NF 3NF BCNF 4NF (don't need to know BCNF/4NF)

How do you know when 2NF and 3NF are violated

By finding partial and transitive dependencies in the dependency diagram

partial dependency

Dependency based on only part of composite primary key

transitive dependency

Dependency of one non-key attribute with another non-key attribute ---> leads to anomalies

What are dependency diagrams used for?

Depicts all dependencies found within given table structure, identifies transitives/partial dependencies

What are some things to look for to improve your table design?

Eliminate partials/transitives Evaluate PK assignments Evaluate naming conventions Refine attribute atomicity Identify new attributes Identify new relationships

T/F: highest level of normalization is always most desirable

False

What is 3NF?

Is in 2NF Doesn't contain any transitive dependencies

Why is normalization important?

Keeps data integrity Without it, systems can be inconsistent, slow Especially in transactional/online transactional processing because anomalies can happen often Skipping normalization leaves risk of having invalid/corrupt data

Why would we want to denormalize a table?

Normalization can create more tables, more tables requires more time and processing power

What is normalization?

Process for evaluating and correcting table structures to minimize data redundancies and anomalies Ability to determine which attributes belong in specific tables

What is an un-normalized table?

isn't in 1NF, has repeating groups

When going through normalization process, consistently test to make sure it ______ properly.

processes. If not, denormalize

4NF

table is in 3NF No multiple sets of multivalued dependencies

BCNF

table is in 3NF and only has one candidate key