Chapter 14/15: Normalization/Advanced Normalization

0nf to 4nf

Normalization and its purpose

a technique for producing a set of relations with desirable properties, given the data requirements of an enterprise

A characteristic of a suitable set of relations includes …. (hint: 3)

• Minimal number of attributes

• Attributes with close logical relationship

• Minimal redundancy of attributes

Approaches to normalization: (hint: 2)

1. Use as a bottom-up standalone database design technique

2. Check validation to check the structure of relation

What are anomalies? What are the three?

inconsistencies or errors that can arise when working with relational databases

Insertion, Deletion, Update/modification

What is insertion anomaly?

specific attributes cannot be inserted into the database without the presence of other attributes

EX: Entering details about staff would also mean entering the branch details. What if the staff isn’t assigned yet? This info would be null.

Entering details about the branch would also have to have staff associated with it.

Deletion Anomaly means that deletion of specific data …

results in the loss of additional, unintended data

EX: staffNo SA9 quits or is moved, B005 and its data would be gone and not exist anymore

Update/modification anomaly:

modifying any single piece of data calls for updating other copies

EX: If we wanted to update the address for B003 in the StaffBranch relation, we would have to update all the appropriate tuples. Otherwise the data will become inconsistent

What are the two properties that you need to worry about when decomposing larger relations into smaller ones? There are two properties

Lossless-join and Dependency

What is lossless-join?

when you split a big table into smaller ones, you can always put them back together (join them) without losing any information from the original table.

What is dependency preservation?

Maintaining the ability to enforce rules from the original table by checking smaller tables independently without needing to join them.

Functional dependency describes the relationship between __________.

attributes

In the expression A ---> B, B is functionally dependent on A or A is a __________ for B.

determinant

If B is functionally dependent on A, but not on any proper subset of A, then B is considered to have a __________ dependency.

full functional

A table that contains one or more repeating groups is referred to as __________ .

Unnormalized Form (0NF)

To achieve First Normal Form (1NF), each row and column must contain only one __________ value.

atomic

Second normal form is 1NF and…

Every non PK is fully functionally dependent on the pk, meaning partial dependencies are removed

How are partial dependencies removed?

placing them in a new relation along with a copy of their determinant

Third normal form is 2nf and…

non pk attributes that are transitive dependent on the pk are removed

How to normalize to 3nf?

Place attributes in a new relation along with a copy of the determinant

Boyce-Codd Normal form (BCNF) states that every determinant must be a …

Candidate key

Violations of BCNF are rare, but can happen. What are the two circumstances?

1. The relation contains two or more sets of composite candidate keys or

2. The candidate keys overlap, that is have at least one attribute in common

Multi-valued dependency states that for a single value of A …

More than one B exists. Also columns B and C are independent of each other

What is 4NF?

For every nontrivial multi-valued dependency A->>B, A is a candidate key of the relation