Chpt 14 - Normalization, Anomalies, and Functional Dependencies

Name (3) characteristics of Full Functionary Dependencies

(1) Are intrinsic to the relation (hold for all time) (2) There exists a one-to-one relationship between the attribute(s) on the left-hand side (determinant) and those on the right-hand side (3) The determinant has the minimal number of attributes necessary to maintain the dependency with the attribute(s) on the right hand-side

Benefits of using a database that has a suitable set of relations is that the database will be:

(1) Easier for the user to access and maintain the data (2) Take up minimal storage space on the computer

Name (3) types of anomalies

(1) Insertion (2) Deletion (3) Update (or Modification)

Name (3) characteristics of a suitable set of relations

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

Define Transitive Dependency

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.

Define Deletion Anomaly

A deletion anomaly occurs when needed data is lost while deleting other data.

What does a functional dependency describe?

A functional dependency describes the relationship between attributes

Define Partial Functional Dependency

A non-primary key attribute is functionally dependent on part of a primary key

Define Functional Dependency

For example, 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 (1:1)

Define Full Functional Dependency

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

What is the purpose of identifying the functional dependencies in a relation?

Main purpose of identifying a set of functional dependencies for a relation is to specify the set of integrity constraints that must hold on a relation.

Define normalization

Normalization is a formal technique for analyzing a relation based on its primary key and the functional dependencies between the attributes of that relation in order to produce a set of suitable relations that support the data requirements of an enterprise.

Characteristic for a candidate key

The attributes of a determinant either individually or working together must be able to functionally determine all the other attributes in the relation.

Define Determinant

The determinant of a functional dependency refers to the attribute or group of attributes on the left-hand side of the arrow

What part of a relation do you analyze when normalizing?

You analyzing a relation based on its primary key and the functional dependencies between the attributes of that relation.

Define First Normal Form (1NF)

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

Define Second Normal Form (2NF)

A relation that is in 1NF and every non-primary-key attribute is fully functionally dependent on the primary key

Define Third Normal Form (3NF)

A relation that is in 2NF and in which no non-primary-key attribute is transitively dependent on the primary key (no non-key attribute functionally determines another non-key attribute)

Define Boyce-Coff Normal Form (BCNF)

A relation that is in 3NF and in which every determinant is a candidate key

Define Unnormalized Form (UNF)

A table that contains one or more repeating groups

Define Insertion Anomaly

An Insert Anomaly occurs when certain attributes cannot be inserted into the database without the presence of other attributes.

What is the main integrity constraint to consider after identifying functional dependencies?

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.

Define Update Anomaly

An update anomaly is a data inconsistency that results from data redundancy and a partial update - data is inconsistent among rows of the relation

What is decomposition?

Decomposing is the breaking of an original relation open to anomalies into smaller relations that are not prone to anomalies.