DBMS Interview Questions
This page consists of DBMS interview questions and answers.
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.
Insert Anomaly occurs when we can't insert data for certain attributes without the presence of other attribute.
Consider the following
|s1||Yusuf||Shakeel||CSE||Prof. Xavier||2018-01-01 10:00:01||2018-01-01 10:00:01|
|s2||Jane||Doe||CSE||Prof. Xavier||2018-01-01 10:01:10||2018-01-01 10:01:10|
|s3||John||Doe||CSE||Prof. Xavier||2018-01-01 10:02:00||2018-01-01 10:02:00|
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).
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.
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.
Following are the normalisation rules.
1NF or First Normal Form is the simplest form of normalisation. In this we try to achieve the following.
A table is in 2NF if we have the following points.
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.
A table is in 3NF if it satisfies the following points.
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.
A table is in BCNF if it satisfies the following points.
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.
A table is in 4NF if it satisfies the following points.
Multi valued dependency exists if the following points are satisfied.