DBMS Interview Questions - Set 6

This page consists of DBMS interview questions and answers.

Q1: What is normalisation?

Normalisation is a technique of removing data redundancy and anomalies like insert, update and delete.

We perform normalisation of database to remove duplicate data (redundancy) and store the data is a logical order for easy maintenance and accessibility.

In simple terms, if we have single table holding all the data then by normalisation technique we will split the table into multiple tables to reduce data redundancy and store the data in a logical order.

Q2: Explain Insert Anomaly

Insert Anomaly occurs when we can't insert data for certain attributes without the presence of other attribute.

Consider the following student table.

In the above table we can't insert a new student record without knowing the department and department_head.

Similarly, if multiple students enroll for CSE department then we will have multiple rows repeating the department and department_head value (data redundancy).

Q3: Explain Update Anomaly

Update Anomaly occurs when one or more rows of duplicate data is updated, but not all. This gives rise to inconsistent data.

If we consider the above student table we can see that for CSE department Prof. Xavier is the head.

Now, if Prof. Jack becomes the new head of the department then we have to update all the rows having Prof. Xavier as CSE department head. And if any one record is not updated then we will have inconsistent data.

Q4: Explain Delete Anomaly

Delete Anomaly exists when certain attributes are lost because some other attribute gets deleted.

If we consider the above student table we can see that we are storing the department and department_head information along with the student data.

So, if at the end of the academic year if we delete all the students from the table then all the department and department_head data will be lost.

Q5: List the different normalisation rules

Following are the normalisation rules.

• 1NF - First Normal Form
• 2NF - Second Normal Form
• 3NF - Third Normal Form
• BCNF - Boyce-Codd Normal Form
• 4NF - Fourth Normal Form

Q6: Explain 1NF

1NF or First Normal Form is the simplest form of normalisation. In this we try to achieve the following.

• Remove duplicate columns from the given table.
• Split the given table into multiple tables to store related data.
• Identify each row of the tables with a unique column.
• Each column store single value.

Q7: Explain 2NF

A table is in 2NF if we have the following points.

• The table is in 1NF.
• Every non-prime attribute fully depends on the primary key.
• The table contains no partial dependency.

Non-prime attribute: Column (attribute) of a table that is not a part of any candidate key.

Partial Dependency: Any attribute of a table that partially depends on the primary key gives us a Partial Dependency.

Q8: Explain 3NF

A table is in 3NF if it satisfies the following points.

• The table is in 2NF.
• There is no transitive dependency.

If we have Functional Dependency A → B and B → C then, A → C is called Transitive dependency.

So, in transitive dependency we have a non-prime attribute depending on another non-prime attribute rather than on the primary key.

Q9: Explain BCNF

A table is in BCNF if it satisfies the following points.

• The table is in 3NF
• For every functional dependency X → Y, X must be a super key.

So, for a table to be in BCNF if we have a functional dependency X → Y then, X can't be a non-prime attribute if Y is a prime attribute.

Q10: Explain 4NF

A table is in 4NF if it satisfies the following points.

• The table is in BCNF.
• The table has no multi valued dependency.

Multi valued dependency exists if the following points are satisfied.

• The table has at least 3 columns.
• For a functional dependency X → Y if for a single value of X there exists multiple value of Y.
• For a relation R(X, Y, Z) if there is a multi valued dependency between X and Y then, Y and Z must be independent of each other.