Data Integrity and Normalization: When is a relation in first normal form?
A relation R is in first normal form if and only if all underlying domains contain atomic values only. It means that the relation does not contain any repeating group.
What is removed when a relation is converted to the first normal form?
All repeating groups are removed and the primary keys are identified when a relation is converted to the first normal form.
Describe repeating group.
A repeating group is a set of one or more data items that may occur a variable number of times in a tuple. Each cell in a relation should contain only one value.
What are the database anomalies?
Database anomalies are the problems in relations that occur due to redundancy in the relations. These anomalies affect the process of inserting, deleting and modifying data in the relations. Important data may be lost if a relation with database anomalies is updated.
List different types of anomalies?
- Insertion anomaly
- Deletion anomaly
- Modification anomaly
Define insertion anomaly.
The insertion anomaly occurs when a new record is inserted in the relation. In this anomaly, the user cannot insert a fact about an entity until he has an additional fact about another entity.
Describe partial dependency.
A type of dependency in which one or more non-key attributes are functionally dependent on a part of primary key is called partial dependency.
Differentiate between partial dependency and transitive dependency.
A partial dependency exists when an attribute is dependent on only a part of primary key. It is associated with 1NF. Transitive dependency is a condition in which an attribute is dependent on another attribute that is not part of primary key. It usually requires the decomposition of the table containing the transitive dependency.
When is a relation is second normal form?
A relation is in second normal form (2NF) if it is in 1NF and every non-key attribute is fully functionally dependent on the primary key. All non-key attributes must depend on all parts of the primary key.
List the conditionals for second normal form.
- The primary key consists of only one attribute
- No non-key attributes exist in the relation
- Every non-key attribute is functionally dependent on the full set of primary key attribute.
What is removed when a relation is converted from 1NF to 2NF?
All the partially dependent attributes are removed and placed in another relation when a relation is converted from 1NF to 2NF.
Describe transitive dependency.
The transitive dependency is a type of functional dependency between two or more non-key attributes. It exists if a non-key attribute depends on any other non-key attribute.
Define 3NF.
A relation is in 3NF if it is in 2NF and no transitive dependency exists. The transitive dependency is an important factor in normalization. A relation is not in 3NF if the value of a non-key attribute can be obtained by knowing the value of another non-key attribute.
What is removed when a relation is converted from 2NF to 3NF.
Any transitive dependencies, nonkey attributes dependent on other nonkey attributes, are removed when a relation is converted from 2NF to 3 NF.
Compare normal form and normalization.
Normal form is a state of a particular relation regarding functional dependencies while the normalization is a process of producing a simpler and more reliable database structure.
Name three typical problems in merging relations.
Three typical problems in merging relations are synonyms, homonyms and transitive dependency.