DATA NORMALIZATION

duplication of data

• the same data is listed in multiple lines of the database

insert anomaly

• a record about an entity cannot be inserted into the table without first inserting information about another entity

delete anomaly

• a record cannot be deleted without deleting a record about a related entity

update anomaly

• cannot update information without changing information in many places

before normalization

• begin with a list of all the fields that must appear in the database. think of this as 1 BIG TABLE

• do not include computed fields

• one place to begin getting this information is from a printed document used by the system

• additional attributes besides those for the entities described on the document can be added to the database

first normal form

• separate repeating groups into new tables

• repeating groups are fields that may be repeated several times for one document or entity

• create a new table containing the repeating data

• the primary key of the new table (repeating group) is always a composite key; usually, document number and a field uniquely describing the repeating line like an item number

second normal form

• remove partial dependencies

• create separate table with the functionally dependent data and the part of the key on which it depends.

• tables created at this step will usually contain descriptions of resources

• contains functional and partial dependencies

functional dependency

• the value of one attribute in a table is determined entirely by the value of another

partial dependency

• a type of functional dependency where an attribute is functionally dependent on only part of the primary key (PK must be a composite key)

third normal form

• remove transitive dependency

• create a separate table containing the attribute and the fields that are functionally dependent on it.

• tables created at this step will usually contain descriptions of either resources or agents

• leep a copy of the key attribute in the original file

transitive dependency

• a type of functional dependency where attribute is functionally dependent on an attribute other than the primary key

• thus, its value is only indirectly determined by the primary key

1nf, 2nd, 3nf, bcnf, 4nf, 5nf

• types of normalization in SQL

1nf

• this type ensures that each column contains only atomic values

• eliminates repeating group

2nf

• this type eliminates partial dependencies

3nf

• also called thread normal form

• this eliminates transitive dependencies

bcnf

• also called boyce-codd normal form

• this is a strict version of 3NF that addresses additional anomalies (functional dependency)

4nf

• this deals with multi-values dependencies

5nf

• this type addresses join dependencies