L8 Normal Forms Flashcards
What is Data redundancy?
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.
What is lossy decomposition?
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.
What is good database design based on?
on using certain normal
forms for relation schemas
What is Transitive dependency?
Transitive dependency occurs when
a non-key attribute is determining another non-key attribute
What is the First Normal Form (1NF)?
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.
Name two interconnected problems caused by bad database design.
- Redundancy problems
- Update anomalies
What is a Trivial Functional Dependency?
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.
What are the three types of update anomalies?
- Insertion Anomalies
- Deletion Anomalies
- Modification Anomalies
What is Dependency Preservation?
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.
What is an insertion anomaly?
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.
What is a Lossless join?
the ability to ensure that any instance of the original relation can be identified from corresponding instances in the smaller relations
What is the Boyce–Codd normal form (BCNF)?
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.
What is the Third normal form (3NF)?
- 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.
What is Dependency Preservation?
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.
Describe the BCNF Decomposition Algorithm.
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
What is a modification anomaly?
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.
What is Partial dependency?
Partial dependency means that a nonprime attribute is functionally dependent on part of a candidate key.
What is Normalization?
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.
Describe the 3NF Decomposition Algorithm.
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.
What are update unomalies?
problems which are the results from the un-normalized databases
What is a deletion anomaly?
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.
What is the Second Normal Form? (2NF)
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.
Which Normal Form can guarantee dependency preservation?
3NF
Compare Boyce-Codd Normal Form and Third Normal Form in terms of their relative advantages and disadvantages.
- 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
Why does 3NF disallow relation schemas which have transitive FD’s?
- 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
