Question

Q1: Define normalization in DBMS. Explain with examples two possible anomalies in a relational database tables in which those tables need to be normalized

Answer 1

Normalisation can be regarded as the most basic as well as the most essential part of a database designing, a database designer must have a good understanding of semantics of data, in order to build an efficient design which can be achieved by using the concept of normalisation.

There are several purposes of normalisations which are given below:-

• Minimize Data Redundancy
• Removing various type of anomalies such as insert delete and update anamoly during database activities
• The process of reorganizing the data can be eliminated when a relation is modified or enhanced
• Normalisation reduces complex view to a small and stable subgroups of fields / relations.

Normalisation is achieved in various forms which are described as:

1. FIRST NORMAL FORM (1NF) : first normal form is achieved in a relation when there is no multivalued dependencies and relation is already in unnormalized form and it has no repeating group.
2. SECOND NORMAL FORM (2NF) : relation is said to be in a second normal form when it has already achieved first normal form and there is no partial dependency in a relation
3. THIRD NORMAL FORM (3NF) : a relation is in third normal form when it is already in 2nd normal form and it has no transitive dependency
4. BOYCE CODD NORMAL FORM (BCNF) : Boyce codd normal form is achieved, when relation is already in a third normal form and every determinant is a candidate key.
5. FOURTH NORMAL FORM ( 4NF) : fourth normal form is achieved when BCNF is achieved and relation has no multivalued dependency.
6. FIRST NORMAL FORM (5NF) : relation is already in 3NF and no join dependency is there in a relation.

Various types of ANOMALIES which are there in a relation when normalisation is not achieved are:

• UPDATE ANOMALY
• INSERTION ANOMALY
• DELETION ANOMALY

For example:

let there be a relation of "invoice of a book company" having some relations as

1. Customer (cust_no, cust_name,cust_add)
2. Customer_book (cust_no, ISBN, Title,Author_name,Author_country,Qty,Unit_price)

Insertion anomaly:

In relation 2, data of new book can only be added when a customer buys a book, no DATA of new book can be added when a book is published thus data is added only when customer buys a book.

Deletion anomaly:

In relation 2, if a book is purchased by only one customer then the book data will be lost when we delete that record after fully satisfying that customer order

THANK YOU. PLEASE UPV