Dbms-Normalization Chp13

Front 1

Normalization Definition

Back 1

Used to identify relations based on keys and functional dependencies.

Front 2

Normalization Characteristics

Back 2

-minimal number of attributes necessary to support the data requirements of the enterprise;
-attributes with a close logical relationship are found in the same relation;
-minimal redundancy with each attribute represented only once with the important exception of attributes that form all or part of foreign keys.

Front 3

Normalization Benefits

Back 3

-easier for the user to access and maintain the data;
-take up minimal storage space on the computer.
*minimize data redundancy eliminating update anomolies

Front 4

Update Anomolies

Back 4

Relations with potential data redundancy problems that could lead to insertion, deletion, and modification anomalies.

Front 5

Loss-less Join and Dependency Properties

Back 5

-Lossless-join property enables us to find any instance of the original relation from corresponding instances in the smaller relations.
-Dependency preservation property enables us to enforce a constraint on the original relation by enforcing some constraint on each of the smaller relations.

Front 6

Functional Dependencies

Back 6

-Describes relationship between attributes.
-if A and B are attributes of relation R, B is functionally dependent on A (denoted A -> B), if each value of A in R is associated with exactly one value of B in R.

Front 7

Determinant of a functional dependency

Back 7

-refers to the attribute or group of attributes on the left-hand side of the arrow.

Front 8

Full Functional Dependency

Back 8

-Determinants should have the minimal number of attributes necessary to maintain the functional dependency with the attribute(s) on the right hand-side.
-Full functional dependency indicates that if A and B are attributes of a relation, B is fully functionally dependent on A, if B is functionally dependent on A, but not on any proper subset of A.

Front 9

Normalization Functional Dependencies

Back 9

-There is a one-to-one relationship between the attribute(s) on the left-hand side (determinant) and those on the right-hand side of a functional dependency.
-Holds for all time.
-The determinant has the minimal number of attributes necessary to maintain the dependency with the attribute(s) on the right hand-side.

Front 10

Transitive Dependency

Back 10

_Describes a condition where A, B, and C are attributes of a relation such that if A → B and B → C, then C is transitively dependent on A via B (provided that A is not functionally dependent on B or C).
-Potential source for update anomolies

Front 11

Process of Normalization

Back 11

-Formal technique for analyzing a relation based on its primary key and the functional dependencies between the attributes of that relation.

-Each step corresponds to a specific normal form, which has known properties.

Front 12

Purpose in identifying functional dependencies for a relation

Back 12

-To specify the set of integrity constraints that must hold on a relation.

-An important integrity constraint to consider first is the identification of candidate keys, one of which is selected to be the primary key for the relation.

Front 13

As normalization proceeds

Back 13

-Relations become progressively more restricted (stronger) in format and also less vulnerable to update anomalies.

Front 14

Unnormalized Form (UNF)

Back 14

A table that contains one or more repeating groups.

Front 15

First Normal Form (1NF)

Back 15

A relation in which the intersection of each row and column contains one and only one value.

Front 16

UNF to 1NF

Back 16

-Nominate an attribute or group of attributes to act as the key for the unnormalized table.

-Identify the repeating group(s) in the unnormalized table which repeats for the key attribute(s).
-Remove the repeating group

Front 17

Remove the repeating group; UNF to 1NF

Back 17

-Entering appropriate data into the empty columns of rows containing the repeating data (‘flattening’ the table).
Or by
-Placing the repeating data along with a copy of the original key attribute(s) into a separate relation.

Front 18

Second Normal Form (2NF)

Back 18

-Based on the concept of full functional dependency.

-Full functional dependency indicates that if A and B are attributes of a relation, B is fully dependent on A if B is functionally dependent on A but not on any proper subset of A.
-A relation that is in 1NF and every non-primary-key attribute is fully functionally dependent on the primary key.

Front 19

1NF to 2NF

Back 19

-Identify the primary key for the 1NF relation.

-Identify the functional dependencies in the relation.

-If partial dependencies exist on the primary key remove them by placing then in a new relation along with a copy of their determinant.

Front 20

Third Normal Form (3NF)

Back 20

-Based on the concept of transitive dependency.

-Transitive Dependency is a condition where
A, B and C are attributes of a relation such that if A -> B and B -> C,
then C is transitively dependent on A through B. (Provided that A is not functionally dependent on B or C).

Front 21

Third Normal Form (3NF)

Back 21

A relation that is in 1NF and 2NF and in which no non-primary-key attribute is transitively dependent on the primary key.

Front 22

2NF to 3NF

Back 22

-Identify the primary key in the 2NF relation.

-Identify functional dependencies in the relation.

-If transitive dependencies exist on the primary key remove them by placing them in a new relation along with a copy of their dominant.

Front 23

Second normal form (2NF)

Back 23

A relation that is in first normal form and every non-primary-key attribute is fully functionally dependent on any candidate key.

Front 24

Third normal form (3NF)

Back 24

A relation that is in first and second normal form and in which no non-primary-key attribute is transitively dependent on any candidate key.