What are Anomalies?

When data is stored in an unnormalized (poorly structured) table, it can lead to redundancy
and inconsistency. These problems are called anomalies:

1. Insertion Anomaly

o Difficulty in inserting new data due to missing values.

o Example: If a new student hasn’t enrolled in any course yet, we cannot insert
the student into the table because course details are required.

2. Update Anomaly

o If a value occurs multiple times, updating one instance but not others leads
to inconsistency.

o Example: If a course fee is stored in multiple rows and the fee changes,
updating only one row causes inconsistency.

3. Deletion Anomaly

o Deleting one piece of information unintentionally removes other important

o Example: If the last student enrolled in a course is deleted, the course

Normalization
Normalization is the process of organizing data into tables to reduce redundancy and
avoid anomalies.

What is Database Normalization?

Database normalization is a database design principle for organizing data in an organized

It helps you avoid redundancy and maintain the integrity of the database. It also helps you
eliminate undesirable characteristics associated with insertion, deletion, and updating.
What is the Purpose of Normalization?

The main purpose of database normalization is to avoid complexities, eliminate duplicates,

Database administrators are able to achieve these relationships by using primary keys,
foreign keys, and composite keys.

To get it done, a primary key in one table, for example, employee_wages is related to the
value from another table, for instance, employee_data.

N.B.: A primary key is a column that uniquely identifies the rows of data in that table. It’s a
unique identifier such as an employee ID, student ID, voter’s identification number (VIN),
and so on.

A foreign key is a field that relates to the primary key in another table.

A composite key is just like a primary key, but instead of having a column, it has multiple
columns.

What is 1NF 2NF and 3NF?

1NF, 2NF, and 3NF are the first three types of database normalization. They stand for first
normal form, second normal form, and third normal form, respectively.

There are also 4NF (fourth normal form) and 5NF (fifth normal form). There’s even 6NF
(sixth normal form), but the commonest normal form you’ll see out there is 3NF (third
normal form).

All the types of database normalization are cumulative – meaning each one builds on top of
those beneath it. So all the concepts in 1NF also carry over to 2NF, and so on.

The First Normal Form – 1NF

For a table to be in the first normal form, it must meet the following criteria:

• a single cell must not hold more than one value (atomicity)

• there must be a primary key for identification

• no duplicated rows or columns

• each column must have only one value for each row in the table
The Second Normal Form – 2NF

The 1NF only eliminates repeating groups, not redundancy. That’s why there is 2NF.

A table is said to be in 2NF if it meets the following criteria:

• it’s already in 1NF

• has no partial dependency. That is, all non-key attributes are fully dependent on a
primary key.

The Third Normal Form – 3NF

When a table is in 2NF, it eliminates repeating groups and redundancy, but it does not
eliminate transitive partial dependency.

This means a non-prime attribute (an attribute that is not part of the candidate’s key) is
dependent on another non-prime attribute. This is what the third normal form (3NF)
eliminates.

So, for a table to be in 3NF, it must:

• be in 2NF

• have no transitive partial dependency.

Let consider the given ERD.

Teach:(Student- Teacher)

UNF:(sid, sname, ph_num, tid, city, country, email) // [Write all the attributes from ERD]

1NF: i) ph_num is a multivalued attribute

ii) email is a multivalued attribute

2NF: i) sid, sname, ph_num

ii) tid, city, country, email

iii)sid, ph_num -> composite primary key

iv) tid, email -> composite primary key

3NF: i) sid, sname, tid ->FK

ii) tid, city, country, coid ->FK

iii)sid, ph_num ->cpk

iv) tid, email ->cpk

v) coid, country, city

Enroll: (Student-Department)

UNF: (sid, sname, ph_num, did, dname)

1NF: ph_num is a multivalued attribute

2NF: i) sid, sname, ph_num

ii) did, dname

iii) sid, ph_num ->cpk

3NF: i) sid, sname

ii) did, dname

iii)sid, ph_num ->cpk

OR

i) sid, sname, sdid

ii) did, dname, sdid

iii) sid, ph_num ->cpk

iv) sdid, sid, did

Work: (Teacher-Department)

UNF:( tid, city, country, email, did, dname)

1NF: i) email is a multivalued attribute

2NF: i) tid, city, country, email

ii) did, dname

iii) tid, email -> cpk

3NF: i)tid, city, country, coid

ii) did, dname, tid

iii) tid, email -> cpk

iv)coid, city, country

List of tables per relation:

Teach:

i) sid, sname, tid

ii) tid, coid

iii)sid, ph_num ->cpk

iv) tid, email ->cpk

v) coid, country, city

i) sid, sname

ii) did, dname

iii)sid, ph_num ->cpk

iv) sid, did ->cpk

Work:

i)tid, coid

ii) did, dname, tid

iii) tid, email -> cpk

iv)coid, city, country

List of Final Table:

i) sid, sname, tid

ii) tid, codi

iii)sid, ph_num ->cpk

iv)tid, email ->cpk

v) coid, country, city

vi) did, dname, tid

vii) sid, did ->cpk

For more information: [Link]