Question

Imagine you are a database developer in a local university and you've been asked by the CIO to provide a summary of normalizing database tables that the IT staff will use in the upcoming training session.

Describe the steps you would use in order to convert database tables to the First Normal Form, the Second Normal Form, and the Third Normal Form. Provide an example that is relevant to a college environment that illustrates reasons for converting database tables to the First, Second, and Third Normal Forms.

Answer 1

Answer:-

There are following steps to convert database table to  First Normal Form, the Second Normal Form, and the Third Normal Form.

1NF (First Normal Form) Rules

• Each table cell should contain a single value.
• Each record needs to be unique.

The Primary key is usually a single column, but sometimes more than one column can be combined to create a single primary key. For example consider a table which is not in First normal form.

example:

2NF (Second Normal Form) Rules

As per the Second Normal Form there must not be any partial dependency of any column on primary key. It means that for a table that has concatenated primary key, each column in the table that is not part of the primary key must depend upon the entire concatenated key for its existence. If any column depends only on one part of the concatenated key, then the table fails Second normal form.

In the above Table in First Normal Form, while the candidate key is {Student, Subject}, Age of Student only depends on Student column, which is incorrect as per Second Normal Form. To achieve second normal form, it would be helpful to split out the subjects into an independent table, and match them up using the student names as foreign keys.

Example:-

3NF(Third Normal form) Rules

In this every non-prime attribute of table must be dependent on primary key, or we can say that, there should not be the case that a non-prime attribute is determined by another non-prime attribute. So this transitive functional dependency should be removed from the table and also the table must be in Second Normal form.

Example:-

In this table Student_id is Primary key, but street, city and state depends upon Zip. The dependency between zip and other fields is called transitive dependency. Hence to apply 3NF, we need to move the street, city and state to new table, with Zip as primary key.