L8 Normal Forms

Question 1

What is Data redundancy?

Answer 1

Data redundancy is a condition created within a database or data storage technology in which the same piece of data is held in two separate places.

This can mean two different fields within a single database, or two different spots in multiple software environments or platforms.

Question 2

What is lossy decomposition?

Answer 2

The decompositio of relation R into R1 and R2 is lossy when the join of R1 and R2 does not yield the same relation as in R.

Question 3

What is good database design based on?

Answer 3

on using certain normal
forms for relation schemas

Question 4

What is Transitive dependency?

Answer 4

Transitive dependency occurs when

a non-key attribute is determining another non-key attribute

Question 5

What is the First Normal Form (1NF)?

Answer 5

For a table to be in the First Normal Form, it should follow the following rules:

• It should only have single(atomic) valued attributes/columns.
• Values stored in a column should be of the same domain
• All the columns in a table should have unique names.

Question 6

Name two interconnected problems caused by bad database design.

Answer 6

• Redundancy problems
• Update anomalies

Question 7

What is a Trivial Functional Dependency?

Answer 7

If a functional dependency (FD) X → Y holds, where Y is a subset of X, then it is called a trivial FD. Trivial FDs always hold.

Question 8

What are the three types of update anomalies?

Answer 8

1. Insertion Anomalies
2. Deletion Anomalies
3. Modification Anomalies

Question 9

What is Dependency Preservation?

Answer 9

A decomposition is dependency preserving if the FDs
which hold on the original relation schema can be tested
on the decomposed schemas, without using joins.

“Putting it simply dependency is preserved when we find both sides of the FD in the attributes of one sub-schema”

We cannot always find a BCNF decomposition that is
dependency preserving.
To test that no FDs are violated, we may need to join
relations (expensive).
We can always find a 3NF dependency-preserving
decomposition.

Question 10

What is an insertion anomaly?

Answer 10

An insertion anomaly is the inability to add data to the database due to absence of other data.

For example, assume Student_Group is defined so that null values are not allowed. If a new employee is hired but not immediately assigned to a Student_Group then this employee could not be entered into the database. This results in database inconsistencies due to omission.

Question 11

What is a Lossless join?

Answer 11

the ability to ensure that any instance of the original relation can be identified from corresponding instances in the smaller relations

Question 12

What is the Boyce–Codd normal form (BCNF)?

Answer 12

A relational schema R is in Boyce–Codd normal form if and only if for every one of its dependencies X → Y, at least one of the following conditions hold:

1) X → Y is a trivial functional dependency (Y ⊆ X)
2) X is a superkey for schema R

It is a slightly stronger version of the third normal form (3NF)

BCNF acts differently from 3NF only when there are multiple overlapping candidate keys.

Question 13

What is the Third normal form (3NF)?

Answer 13

• The relation R (table) is in second normal form (2NF)
• Every non-prime attribute of R is non-transitively dependent on every key of R.

Question 14

What is Dependency Preservation?

Answer 14

A decomposition is dependency preserving if the FDs
which hold on the original relation schema can be tested
on the decomposed schemas, without using joins.

We cannot always find a BCNF decomposition that is
dependency preserving.

We can always find a 3NF dependency-preserving
decomposition.

Question 15

Describe the BCNF Decomposition Algorithm.

Answer 15

Split R into two relation schemas:
1. R1 , containing all the attributes in the
violating FD
2. R2 containing all attributes in R
except those on the RHS of the violating FD

Question 16

What is a modification anomaly?

Answer 16

a data inconsistency that results from data redundancy and a partial update.

For example, each employee in a company has a department associated with them as well as the student group they participate in. If A. Bruchs’ department is an error it must be updated at least 2 times or there will be inconsistent data in the database. If the user performing the update does not realize the data is stored redundantly the update will not be done properly.

Question 17

What is Partial dependency?

Answer 17

Partial dependency means that a nonprime attribute is functionally dependent on part of a candidate key.

Question 18

What is Normalization?

Answer 18

Normalization is the process of splitting relations into well structured relations that allow users to insert, delete, and update tuples without introducing database inconsistencies.

Without normalization many problems can occur when trying to load an integrated conceptual model into the DBMS.

Question 19

Describe the 3NF Decomposition Algorithm.

Answer 19

Given a relation schema R and a set of FDs F, the following steps produce a 3NF decomposition of R that
satisfies the lossless join condition and is dependency preserving:
1. Eliminate redundant FDs (we haven’t covered this).
2. Create a relation Ri = XY for each FD X → Y in F
3. If the key K of R does not occur in any relation Ri, create one more relation Ri=K
4. Delete any of the relations Ri that are contained in another.

Question 20

What are update unomalies?

Answer 20

problems which are the results from the un-normalized databases

Question 21

What is a deletion anomaly?

Answer 21

the unintended loss of data due to deletion of other data

For example, if the student group Beta Alpha Psi disbanded and was deleted from the table above, J. Longfellow and the Accounting department would cease to exist. This results in database inconsistencies and is an example of how combining information that does not really belong together into one table can cause problems.

Question 22

What is the Second Normal Form? (2NF)

Answer 22

a relation is in 2NF if it is in 1NF and every non-prime attribute of the relation is dependent on the whole of every candidate key

A functional dependency on part of any candidate key is a violation of 2NF. In addition to the primary key, the relation may contain other candidate keys; it is necessary to establish that no non-prime attributes have part-key dependencies on any of these candidate keys.

Question 23

Which Normal Form can guarantee dependency preservation?

Answer 23

3NF

Question 24

Compare Boyce-Codd Normal Form and Third Normal Form in terms of their relative advantages and disadvantages.

Answer 24

• a table in BCNF is already in 3NF, but BCNF is stronger
• we cannot always find a BCNF decomposition that is dependency preserving
  
  • to test that no FDs are violated, we may need to join relations (expensive)
• we can always find a 3NF decomposition that is dependency preserving

Question 25

Why does 3NF disallow relation schemas which have transitive FD’s?

Answer 25

• trasitive: non prime attribute determines another non prime attribute
• this can lead to inconsistencies / update anomalies?
  
  • lets say A -> B and B -> C
  • if B is not a prime attribute, we can allow null values for B
  • but if we have null values for B, then we can’t find some Cs