What is meant by entity?: The entity integrity is a constraint on primary key value. It states that any attriburte of a primary key cannot contain null value. If primary key contains null value, it is not possible to uniguely identify a record in a relation.
Define referentioal integrity: The referentaila integrtiy is a constratint on foreign key value. It states that if a foreign key exitss in a relation, the foreign key value must match the primary key value of some tuple in its parent relation. Otherwise the foreign key value msut be completely null.
Hwo is entity integrtiy attained?
The intity integrity can be attained gby specitying primary key in a relation. When a primary key constrain is specified on a relation, the DBMS automtically applies the entity integrity on the attribute that is used as primary key.
Hw is referentiaol integrity achieved?
The referential integrity can be achieved by connecting two relations by specifying relationships between them. When two relations are connected, one relation is called parent relation and ;the other is called dependent relation.
Define synonym.
A synonym is a type of problem that exists in relations. A synonym is created when two different names are used for the same information or attribute,. The name of attribute must be same if it exists in two or more entities.
Define homonym.
A homonym is a type of problem that exists in relations. A homonym is created when same name is used for two different attributes.
Define the term redundancy.
Redundancy means duplication of data in multiple files. It is a type of problem that exists in relations it is created when the same information is unnecessarily stored in two different ways or forms.
Describe mutual exclusiveness of data.
The data that does not have overlapping information is known as mutually exclusive data. The mutual excursiveness of data creates problem in some cases it creates problem for the attributes whose values can be specified as “Yes/ No” form.
Explain normalization.
The process of producing a simpler and more reliable database structure is called normalization. It is used to create a suitable set of relations for storing data. It identifies and corrects the problems and complexities of database design, it produces a new set of relations. The new design is as free of processing problems as possible.
How is functional dependency associated with the process of normalization?
Normalization is performed as a series of tests on a relation to determine whether it satisfies or violates the requirements of a given normal form. All of these normal forms are based on the functional dependencies among the attributes of a relation.
Describe functional dependency with example.
A functional dependency is a relationship between attributes such that given the value of one attribute it is possible to determine the value of the other attribute. Example of functional dependency: Name–>Phone.
Define determinant.
A determinant is an attribute whose value enables us to obtain the value (s) of other related attributes. It appears on the left side of a functional dependency. Thus, in A–>B, the determinant is A
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.