DBMS (Database Management System) Normalization Study Material for IBPS IT Officer :
DBMS being a very important topic for IBPS IT Officer’s post exam, Questions are regularly asked under the “Professional Knowledge section” for IT Officers. Depending upon the questions asked in the previous exams of IBPS we have prepared a list of topics that are specifically important. We will be covering them under the Banking Study Material & Notes Section of our website. We encourage you to constantly visit our site and keep yourself updated in this topic to score good marks in the Professional Knowledge Section.
If you are new to this topic we recommend you to read the other articles for SQL, RDBMS, Data Concepts published on our website to get a fair idea of this topic.
Not only the study material and Notes but also the tips and tricks are constantly being given on our website and our social media pages at Facebook, Twitter and Google+. Subscribing and getting in touch with us will help you to stay up-to-date on all the major topics for not only IBPS IT but also for other Competition Exams.
If you have any topic or subject that you want us to cover, do inform us in the comments or through the contact us page.
Normalization :
- Database normalization is the technique of organizing the fields and tables of a relational database to reduce redundancy. Normalization usually involves dividing large tables into smaller tables and defining relationships between them. The target usually is to isolate data so that additions, deletions, and modifications of a field could be made in just one table and then propagated through the rest of the database using the defined relationships.
- Normalisation is the process of taking data from a problem and reducing it to a set of relations while ensuring data integrity and eliminating data redundancy
- Data integrity – all of the data in the database are consistent, and satisfy all integrity constraints.
- Data redundancy – if data in the database can be found in two different locations (direct redundancy) or if data can be calculated from other data items (indirect redundancy) then the data is known to contain redundancy.
- The concept of normalization is of different types, which happen to be First Normal Form (1NF), Second Normal Form (2NF), Third Normal Form (3NF), and Boyce Codd Normal Form(BCNF).
- Informally, a relational database table is often described as “normalized” if it is within the Third Normal Form. Most 3NF tables are free of insertion, updation and deletion anomalies.
- There are other normal forms, for instance 4th and 5th normal forms. They are rarely utilised in system design and are not considered further.
First Normal Form:
First Normal Form(1NF) handles the ‘shape’ of the record type. A relation is in 1NF if, and only if, it contains no repeating attributes or groups of attributes.
To remove the repeating group, either flatten the table and extend the key, or decompose the relation-leading to First Normal Form.
Flatten table and Extend Primary Key:
The Student table with the repeating group can be written as:
Student(matric_no, name, date_of_birth, ( subject, grade ) )
If the repeating group was flattened, as in the Student #2 data table, it will look something similar to:
Student(matric_no, name, date_of_birth, subject, grade )
Even though this is an improvement, we still have an issue. matric_no can no longer function as the primary key – it does not have an unique value for each row. So we need to find a new primary key – in such case it must be be a compound key since no single attribute can uniquely identify a row. The new primary key is a compound key (matrix_no subject).
We’ve now solved the repeating groups problem, however we have created other complications. Every repetition of the matric_no, name, and data_of_birth is redundant and liable to produce errors.
With the relation in its flattened form, strange anomalies appear in the system. Redundant data is the primary reason behind insertion, deletion, and updating anomalies.
Insertion anomaly:
With the primary key including subject, we cannot enter a new student until they have at least one subject to study. We are not allowed NULLs in the primary key so we must have an entry in both matric_no and subject before we can create a new record. This is called the insertion anomaly.
Update anomaly:
If the name of a student were changed for example Smith, J. was changed to Green, J. this would require not one change but many one for every subject that Smith, J. studied.
Deletion anomaly:
If all of the records for the `Databases’ subject were deleted from the table, we would inadvertently lose all of the information on the student with matric_no xxx. This would be the same for any student who was studying only one subject and the subject was deleted. Again the problem arises from the necessity to have a compound primary key.
Decomposing the relation:
The alternative approach is to split the table into two parts, one for the repeating groups and one for the non-repeating groups. The primary key for the original relation is included in both of the new relations.
Second Normal Form:
A relation is in 2NF if, and only if, it is in 1NF and every non-key attribute is fully functionally dependent on the whole key. Thus the relation is in 1NF with no repeating groups, and all non-key attributes must depend on the whole key, not just some part of it. A different way of saying this is that there must be no partial key dependencies (PKDs).
To convert a relation with partial functional dependencies to 2NF, Create a set of new relations, i.e., One relation for the attributes that are fully dependent upon the key, and One relation for each part of the key that has partially dependent attributes.
Third Normal Form:
Third Normal Form is an even stricter normal form and removes virtually all the redundant data.
A relation is in 3NF if, and only if, it is in 2NF and there are no transitive functional dependencies. Transitive functional dependencies arise whenever one non-key attribute is functionally dependent on another non-key attribute.
To convert a relation with transitive functional dependencies to 3NF, eliminate the attributes involved in the transitive dependency and put them in a new relation.
By definition transitive functional dependency can only occur in cases where there is more than one non-key field, so we can say that a relation in 2NF with Zero or one non-key field must automatically be in 3NF.
Boyce and Codd Normal Form:
Boyce and Codd Normal Form is a higher version of the Third Normal Form. This form deals with certain type of anamoly that is not handled by 3NF. A 3NF table which does not have multiple overlapping candidate keys is said to be in BCNF.
