Database Normalization

Database Normalization

This lesson explains the concept of correcting table structures in order to minimize database anomalies through a process called normalization.

The normalization process consists of multiple steps, called normal forms, each ensuring the elimination of the various types of anomalies.

Database Management System Design

a database is often a collection of information or data organized and stored in tables with efforts made to ensure its accuracy and ease of retrieval. During the design process on a database management system, or DBMS, it's of utmost importance that the arrangement of tables and their relation to each other is correct. How data is added, edited, and deleted is also taken into consideration. The table structures, consisting of columns and rows, are important as well.

What Is Normalization?

Normalization is the process of removing existing or possible incidences of anomalies, data redundancies, and data inaccuracy in a database.

https://study.com/academy/lesson/what-is-normal-form-in-dbms-types-examples.html#/lesson

------------------------------------Vocabulary----------------------------------

•Incidences: The occurrence, rate, or frequency

•Anomaly: Something that deviates from what is standard, normal, or expected.

•Redundancy: the state of being not or no longer needed or useful.

•Inaccuracy: an aspect of something that is not accurate

Multiple Choice

Normalisation is:

• Removing all necessary data from a database

• Putting fields from different tables into one big database

• Organising a database to remove repeated entries and increase the accuracy of the data

Multiple Choice

An advantage of normalisation is:

• The storage space needed for a normalised database is likely to be smaller

• It is possible to see previous details that have been changed, such as a customer’s address, as we can look back at an older entry

• The process of normalising a database is very easy and doesn’t require any skill

Multiple Choice

A disadvantage of normalisation is:

• The data loses its integrity as some of it is removed in the normalisation process

• he process of searching the database may be slower due to a higher demand on the central processing unit (CPU)

• Removing redundant data means that links cannot be created between tables

Multiple Choice

When designing a database you should

• make sure entities are in normal form before table structures are created

• only normalize the database when performance problems occur

• create table structures then normalize the database

• consider more important issues such as performance before normalizing

Types of Anomalies

• Update Anomaly: Let say we have 10 columns in a table out of which 2 are called employee Name and employee address. Now if one employee changes it’s location then we would have to update the table. But the problem is, if the table is not normalized one employee can have multiple entries and while updating all of those entries one of them might get missed.

• Insertion Anomaly: Let’s say we have a table that has 4 columns. Student ID, Student Name, Student Address and Student Grades. Now when a new student enroll in school, even though first three attributes can be filled but 4th attribute will have NULL value because he doesn't have any marks yet.

• Deletion Anomaly: This anomaly indicates unnecessary deletion of important information from the table. Let’s say we have student’s information and courses they have taken as follows (student ID, Student Name, Course, address). If any student leaves the school then the entry related to that student will be deleted. However, that deletion will also delete the course information even though course depends upon the school and not the student.

Multiple Choice

Consider the given scenario and check the true conditions

• If we try to insert a record in STUDENT_COURSE with STUD_NO =7, it will not allow.

• f we try to delete a record from STUDENT with STUD_NO =1, it will be allow