Anomalies in Database

 Hello, and welcome to the blog. Today we are going to talk about "Anomalies in Database".

From this blog we are going to talk more about creating a database that can be used as a project and understanding more about structuring database tables in more efficient way.

For that we have to have first understand what things we can do wrong while creating tables. That's why we have started with "Anomalies". Anomalies generally means flaws in the existing system.

I think I am done with the overview, now let's see what we are going to cover up today:

1. What is anomalies?
2. Types of Anomalies
1. Insert
2. Update
3. Delete

let's begin with

1) What is anamolies?

1. Anomalies are nothing but problems that can occur in poorly planned database.
2. Generally in poorly designed database people try to put many columns in a single table.
3. In our database do we have poorly designed table?. Yes our "Employee" table is poorly designed table and have several anomalies. While discussing "Types of Anomalies" we will discuss about how to identify anomalies and how I realized that the table is poorly designed.
4. I am creating one more table to explain it better.

   CREATE TABLE [dbo].[EmployeeAnomaly] (
   	[Id] [int] NOT NULL
   	,[FirstName] [varchar](100) NOT NULL
   	,[LastName] [varchar](100) NOT NULL
   	,[Department] [varchar](50) NULL
   	,[Salary] [decimal](18, 2) NULL
   	,[Designation] [varchar](50) NULL
   	)
   

5. I have also added some data.

   Id	First Name	Last Name	Department	Salary	Designation
   1	John	Doe	Sale	50000	Sales Manager
   2	Layla	Crook	Sale	25000	Sales Person
   3	Lecia	Testa	Marketing	45000	Analyst
   4	Sara	Sink	Marketing	40000	Brand Manager
   5	Raye	Husband	IT	20000	Trainee
   6	Daisy	Reach	IT	50000	Team Lead
   7	Lan	Eimer	IT	25000	Junior Developer
   22	John	Doe	Sale	12000	Trainee
   9	Donte	Litre	HR	50000	Manager
   10	Ka	Rattler	HR	25000	Trainee

2) Types of Anomalies :

2.1 Insert anomaly:

1. Insert anomaly is when you have to add unnecessary data or unrelated information to add a data row in a table or we can also say it as certain attributes cannot be inserted into database table without presence of other attributes
2. We will clarify it more by understanding a example.
3. As we have a table named EmployeeAnomaly we have added certain data in it.
4. Suppose now I want to introduce a new Department in my company called "Account and Finance".
5. Now as per design of our EmployeeAnomaly table we cannot have null values as we have enforced "NOT NULL". So if you want to add a department then you have to add a complete a record you must have Employee to hold values of FirstName, LastName, Id etc.
6. Now, let's consider one more scenario, your company is doing Mass Recruitment. Now everyone who is recruited has to do 6 months of training. After that trainig the company will decide a candidate will go in which department whether he will code or test or do analytics is not fix.
7. We cannot add all the candidate hired in Mass Recruitment in our Employee table as we cannot insert null value in Department column of EmployeeAnomaly table.
8. In short it is inability to add data due to absence of other data.

2.2 Update anomaly:

1. The main reason for this anomaly is "Data Redundancy".
2. Data Redundancy is duplication of data.
3. Update anomaly occurs when you have to update same data at multiple places.
4. Let's see a example: Let's assume we have done a spelling mistake in writing "Sales" department name.
5. Now whenever I write a query I have to ensure that I have updated every row where the spelling mistake was there.

2.3 Delete Anomaly:

1. Delete anomaly occurs when you delete a record that may contain attributes that should not get deleted.
2. For example: Suppose I want to delete Employee with FirstName as "Lacia".
3. It is the only record with designation as "Analyst" and also only employee from "Marketing" department.
4. Removing "Lacia" from database also means removing "Marketing" department and "Analyst" designation.

Note: In practical life when you create database you only try and remove "Insert" and "Update" anomaly. Yes you read it right because in real life nobody is deleting data instead everyone has column in their table that marks a data row as deleted.

Okay, that's it for now we will learn how to remove this anomalies. See you in some other blog till then "Keep coding, keep learning because trust me you can make it big".

"Have a great day"