Define functional dependencies. Explain 2nd normal form with example.

This answer is restricted. Please login to view the answer of this question.

Login Now

A functional dependency is a constraint that specifies the relationship between two sets of attributes where one set can accurately determine the value of other sets. It is denoted as X → Y, where X is a set of attributes that is capable of determining the value of Y. The attribute set on the left side of the arrow, is called Determinant, while on the right side, is called the Dependent.

Example

The following is an example that would make it easier to understand functional dependency −

We have a <Department> table with two attributes − DeptId and DeptName.

DeptId = Department ID
DeptName = Department Name

The DeptId is our primary key. Here, DeptId uniquely identifies the DeptName attribute. This is because if you want to know the department name, then at first you need to have the DeptId.

DeptId DeptName
001 Finance
002 Marketing
003 HR

Therefore, the above functional dependency between DeptId and DeptName can be determined as DeptId is functionally dependent on DeptName −

DeptId -> DeptName

 

Second Normal Form (2NF)

  • In the 2NF, relational must be in 1NF.
  • In the second normal form, all non-key attributes are fully functional dependent on the primary key

Example: Let’s assume, a school can store the data of teachers and the subjects they teach. In a school, a teacher can teach more than one subject.

TEACHER table

TEACHER_ID SUBJECT TEACHER_AGE
25 Chemistry 30
25 Biology 30
47 English 35
83 Math 38
83 Computer 38

In the given table, non-prime attribute TEACHER_AGE is dependent on TEACHER_ID which is a proper subset of a candidate key. That’s why it violates the rule for 2NF.

To convert the given table into 2NF, we decompose it into two tables:

TEACHER_DETAIL table:

TEACHER_ID TEACHER_AGE
25 30
47 35
83 38

TEACHER_SUBJECT table:

TEACHER_ID SUBJECT
25 Chemistry
25 Biology
47 English
83 Math
83 Computer
If you found any type of error on the answer then please mention on the comment or report an answer or submit your new answer.
Leave your Answer:

Click here to submit your answer.

Discussion
0 Comments
  Loading . . .