Create an example table that is in its first normal form(DBMS)

DBMSDatabaseBig Data Analytics

If a table has data redundancy and is not properly normalized then it will be difficult to handle and update. If a relation contains composite or multivalued attributes it violates the first normal form.

A table is in 1Nf if the following conditions exist −

  • There are only single valued attributes.
  • Attribute domain does not change.
  • There is a unique name for every attribute or column.
  • The order in which data is stored does not matter.

Example

Consider the table given below −

IdNameCourse
1AC1,C2
2EC3
3BC2,C3

The above table is in un normalized form, the course attribute is having multivalued attributes which violates 1NF.

Now we represent the above table by creating a new row for each course as −

IdNameCourse
1AC1
1AC2
2EC3
3BC2
3BC4

The above table contains redundant data due to course, as for each course number we have to repeat all the information of students. So course attributes should be separated from the above table.

We divide or decompose the above table R into two tables which is the concept of normalization −

R1(key, multivalued attribute), R2(R-multivalued attribute)

=>R1(Id, course), R2(Id , name).

Steps to decompose the 1NF table

Place all items that appear in the repeating group in a new table. Find a primary key for each new table produced. Duplicate in a new table the primary key of the table from which the repeating group was extracted or vice versa.

R1

IdCourse
1C1
1C2
2C3
3C2
3C4

R2

IdName
1A
2E
3B

R1 and R2 are in 1NF.

Key of R1 = ID

Key of R2 = (rollno, course)

The above table is now in 1NF because no multivalued attributes are present. But it is not in 2NF because ID-> name is a partial dependency {since name depend on apart of key}

Anomalies

The table also suffers with anomalies as follows −

  • Insertion anomaly − We cannot insert a new course such as 'C6' to the table unless we have a student who has to take the subject.

  • Updation anomaly − If we change the course from C2 to C5 we have to make changes in more than one place otherwise the table will be inconsistent.

raja
Updated on 06-Jul-2021 12:39:23

Advertisements