Normalization Flashcards
a process that involves the elimination of inappropriate functional dependencies in a relation
Normalization
it takes a relational schema through a series of tests to certify whether or not it belongs to a certain normal form
Normalization
explain functional dependency of normalization
denoted by X → Y, is a relationship between attributes.
The attribute Y is said to be functionally dependent on attribute X if the value of X determines the value of Y. (or if we know the value of X we will know the value of Y).
Attribute X is called a determinant of attribute Y
who is Dr. Edgar F. Codd
the originator of the relational model, proposed the three normal forms, which he called first, second and third
It is a table/relation that contains one or more repeating groups.
UNNORMALIZED FORM (UNF)
The state prior to 1NF is called the Unnormalized Form (UNF).
UNNORMALIZED FORM (UNF)
A relation in which the intersection of each row and column contains one and only one value
First Normal Form (1NF)
It is considered to be part of the formal
definition of a relation (disallowing multivalued attributes)
First Normal Form (1NF)
First Normal Form (1NF)
● A relation in which the intersection of each row and column contains one and only one value
● It is considered to be part of the formal
definition of a relation (disallowing multivalued attributes)
● To transform the UNF to 1NF, we need to identify and remove repeating groups within the table
a relation is in 2NF if it is in 1NF and every non-key attribute is fully functionally dependent on the primary key
SECOND NORMAL FORM (2NF)
a relation that is in 1NF will be in 2NF if any one of the following conditions apply:
○ the primary key consists of only one attribute
○ no non-key attribute exists in the relation (all attributes are keys)
○ every non-key attribute is functionally dependent on the full set of primary key attributes
SECOND NORMAL FORM (2NF)
a relation is in 2NF if it is in 1NF and every non-key attribute is fully functionally dependent on the primary key
a relation is in 3NF if it is in 2NF and no transitive dependencies exist
THIRD NORMAL FORM
what is transitive dependency
is a functional dependency between two (or more) non-key attributes in a relation
functional dependency of the THIRD NORMAL FORM
X → Y in a relation R is a transitive dependency if there is a set of attributes Z
that is not a subset of any key of R, and both functional dependencies X → Z and Z → Y hold.
a rule to transform a relation in 3NF
decompose the relation by separating the attributes that caused the transitive dependency to exist.
why decompose the relation by separating the attributes that caused the transitive dependency to exist.
in:
SALES (CustomerNo, Name, Salesperson)
SPERSON (Salesperson, Region)
in the relation SALES, we removed the attributes Salesperson and Region, and grouped them together to compose a
new relation. The attribute Salesperson was retained in the original relation and acts as a foreign key.
