Please explain 1 What is normalization and denormalization a

(Please explain)

1) What is normalization and denormalization and why can it be desirable?

2) Explain the problems that unnormalized tables have for insert, update, and delete actions.

Solution

1) Database consist of tables and if these tables are very large then there can be redudancy and inconsistency. To optimize the performance of the databse we use the concept of normalization and denormalizaton.

Normalization-It is a process of organising data into related tables.It is done to remove data redundancy and inconsistency. In this large related tables are broken down into smaller interrelated tables using primary and foreign keys. It involves procedures such as 1NF,2NF,3NF,BCNF and tehn the data is normalized.

Denormalization-It is the process of adding redundant data to speed up complex queries which involves multiple Joins.Data is included in one table from another in order to eliminate the second table which reduces the number of JOINS in a query and thus achieves performance.

Normalization and denormaliation is desirable to speed up the database performance by reducing incosistency and data redundancy.

2) In unnormalized tables the data is repeated,inconsistent and there are insert, update and delete anomalies.

These anomalies and problems for insert, update and delete can be understood by below example-

consider a relation student_course with following attributes-

1. stud_id (primary key)

2. stud_name

3. stud_address

4. course_id

5. course_name

6. course_teacher_id

Problems in insert-Suppose a new course has been introduced but initially no student is alloted to that course then this course cannot be inserted in this table as stud_id will have null values which is not allowed in Primary Key.

Problems in delete-Suppose there is only one student for a course and that student leaves the college so row of that student has to be deleted from this table but with it the course details will also be deleted. So in such cases deletion of information of one entity deletes another entitydetails also.

Problems in Update-Suppose the teacher of a course has been changed,this requires that the course_teacher_id in all the rows correponding to that course must be changed. If we fail to update all the rows then two different teachers id will show in course_teacher_id for the same coursewhich is inconsistency of data in the database.

Such problems are faced in case of unnormalized tables.