Normal Forms
how to normalize to 1NF
create a separate record for each occurrence
Normalizing to 1NF
done to tables that are not relations
when do multivalued dependencies occur
when separate columns of the same relation contain unrelated values
when are most tables in BCNF
when they are in 3NF
a transitive dependency occurs when
a nonkey column determines another nonkey column
denormalization deals with performance issues by
bringing tables together as one table to enable faster retrieval
A table is in 1NF if
each row is unique and no column in any row contains multiple values
a table is in 2NF if
it is in 1NF and does not contain partial functional dependencies
a table is in 3NF if
it is in 2NF and does not contain transitive functional dependencies
a table is in 4NF when
it is in BCNF and does not contain multivalued dependencies
denormalization deals with normalization issues by
keeping a normalized master version of the database where updates can be made
partial functional dependency
occurs when a component of a primary key or any other candidate key on its own functionally determines the nonkey columns of a relation
full key functional dependency
occurs when a key functionally determines a column of a relation while no component of the key partially determines the same column
what is faster, retrieving 8 columns or 4 tables with 2 columns each
retrieving 8 columns, linking tables is time consuming
denormalization
reversing the effects of normalization by joining normalized relations into a relation that is not normalized
when there is a composite candidate key
the key is eliminated no matter what, if you consider it a candidate key it will be eliminated in 2NF, if it is not considered a candidate key it will be eliminated in 3NF
the PK of the additional column in 3NF is
the nonkey column that determines the other nonkey column
Boyce Codd Normal Form
the table contains no functional dependencies other than full key functional dependencies
a table is automatically in 2NF if
there is a single PK
Which form is not done in a relational database
1NF
every relation table is
1NF
