Database management system a Give the rules and remedies for

Database management system:

a. Give the rule(s) and remedies for resolving a relation to 1NF.

b. Give the rule(s) and remedies for resolving a relation to 3NF.

Solution

First Normal Form (1NF)

According to the first normal form,

Student Table :

Student                Age         Subject

Adam      15             Biology, Maths

Alex         14              Maths

Stuart      17              Maths

In First Normal Form, any row must not have a column in which more than one value is saved, like separated with commas.

In that scenario we must separate such data into multiple rows.

Student Table following 1NF will be :

Student                   Age     Subject

Adam        15       Biology

Adam        15       Maths

Alex           14       Maths

Stuart        17       Maths

Using first normal form their is introduction of large amount of redundancy but also each row as per its existence is unique.

Third Normal Form (3NF)

Student_Detail Table :

Student_id         Student_name DOB       Street   city         State     Zip

In this table Student_id is Primary key, but street, city and state depends upon Zip attribute. The dependency between zip attribute and other fields is known as transitive dependency.

Hence to apply 3NF, we need to move the street, city and state to new table, with Zip as primary key.

New Student_Detail Table :

    Student_id     Student_name DOB       Zip

Address Table :

     Zip     Street   city         state

The advantage of removing transtive dependency is,

•             Amount of data duplication is reduced.

•             Data integrity achieved.