What is difference between first NF and second NF?

Answer 1

One of the main challenges in designing a database is to design-out duplication of data. You can paraphrase this approach as "one piece of information, once only". This is important because it makes it easier to ensure the data remains consistent across multiple tables. This design approach is known as 'normalizing' and the rules written by Edgar Codd (the grandfather of relational databases) include different levels of 'normalising', which are named First Normal Form (1NF), Second Normal Form (2NF) ... all the way up to Sixth Normal Form (6NF).

First normal form is the process of 'flattening' a table to make it easier to access and update and to remove any structural limits. Take the following table example from an inventory system;

ProductCode

Description

QuantityInLocationA

ValueInLocationA

QuantityInLocationB

ValueInLocationB

QuantityInLocationC

ValueInLocationC

This could hold values like;

ABC001, Katsouris Sausage, 12, $1.90, 10, $1.60, 22, $3.50

NOR999, Adler Typewriter, 1, $0.10, 0, $0.00, 0, $0.00

The problems with this structure should be fairly obvious; in records where there is only really one value (like the second one) there is redundant data. If you need to hold quantity and value information for 4, 5 or 999 locations, the table is going to get wider and wider with more and more redundancy. Accessing a single set of values in multiple records is overly complex.

The table would be normalised for 1NF by 'flattening' it, which is going to need a new key column to hold our Location values;

ProductCode

Description

LocationCode

Quantity

Value

ABC001, Katsouris Sausage, 'A', 12, $1.90

ABC001, Katsouris Sausage, 'B', 10, $1.60

ABC001, Katsouris Sausage, 'C', 22, $3.50

NOR999, Adler Typewriter, 'A', 1, $0.10

You will notice now that now we have multiple records instead of one, we have introduced the need to store one piece of information (the description) multiple times. This is considered a BAD thing because it will make it more difficult to change the product description reliably, because we would have to update multiple records instead of just one. This is where second normal form (2NF) rides to the rescue, making us split this kind of column into a separate table.

Our product table becomes;

ProductCode

LocationCode

Quantity

Value

With values;

ABC001, 'A', 12, $1.90

ABC001, 'B', 10, $1.60

ABC001, 'C', 22, $3.50

NOR999, 'A', 1, $0.10

And then we have a new table to hold the description;

ProductCode

Description

With values;

ABC001, Katsouris Sausage

NOR999, Adler Typewriter

If you can master 1NF and 2NF, you're 90% the way to being a database designer!

----------------Answer1st normal form: This dictates that all table be flat and have no repeating groups. In other words this table is flat and has only 2 dimensions-length(number of records or rows) and width(number of fields or columns) and most importantly, cannot contain fields with more than one value.

2nd normal form: This dictates that data in all non-key columns be fully dependent on the primary key only. In other words, the primary key must exist before the record can be considered of the second normal form.