What is second normal form and explain it with simple example?

Answer 1

Example: The following relation is in First Normal Form, but not Second Normal Form:

Order #CustomerContact PersonTotal1Acme WidgetsJohn Doe$134.232ABC CorporationFred Flintstone$521.243Acme WidgetsJohn Doe$1042.424Acme WidgetsJohn Doe$928.53

In the table above, the order number serves as the primary key. Notice that the customer and total amount are dependent upon the order number -- this data is specific to each order. However, the contact person is dependent upon the customer. An alternative way to accomplish this would be to create two tables:

CustomerContact PersonAcme WidgetsJohn DoeABC CorporationFred Flintstone

Order #CustomerTotal1Acme Widgets$134.232ABC Corporation$521.243Acme Widgets$1042.424Acme Widgets$928.53

The creation of two separate tables eliminates the dependency problem experienced in the previous case. In the first table, contact person is dependent upon the primary key -- customer name. The second table only includes the information unique to each order. Someone interested in the contact person for each order could obtain this information by performing a JOIN operation.

Answer 2

Second normal form (2NF) is a database normalization technique that eliminates partial dependencies in a table by ensuring that each non-key attribute is fully functionally dependent on the primary key. In simple terms, 2NF means that each column in a table must depend on the table's primary key and not on any other column. For example, if we have a table with columns Student ID, Course ID, and Course Name, where Course Name is functionally dependent on Course ID but not on Student ID, we would need to separate these into two tables to achieve 2NF.