1 of 15

�Functional Dependency�

  • The functional dependency is a relationship that exists between two attributes.

​

  • It typically exists between the primary key and non-key attribute within a table.

​

  • X   →   Y  

​

  • The left side of FD is known as a determinant, the right side of the production is known as a dependent.

​

2 of 15

  • Assume we have an employee table with attributes: Emp_Id, Emp_Name, Emp_Address.

​

  • Here Emp_Id attribute can uniquely identify the Emp_Name attribute of employee table because if we know the Emp_Id, we can tell that employee name associated with it.

​

  • Functional dependency can be written as:

​

  • Emp_Id → Emp_Name   

​

  • We can say that Emp_Name is functionally dependent on Emp_Id.

​

3 of 15

��Types of Functional dependency��

4 of 15

1. Trivial functional dependency

  • A → B has trivial functional dependency if B is a subset of A.

​

  • The following dependencies are also trivial like: A → A, B → B

Example:

  1. Consider a table with two columns Employee_Id and Employee_Name.  
  2. {Employee_id, Employee_Name}   →    Employee_Id is a trivial functional dependency as   
  3. Employee_Id is a subset of {Employee_Id, Employee_Name}.  
  4. Also, Employee_Id → Employee_Id and Employee_Name   →    Employee_Name are trivial dependencies too.  

​

5 of 15

2. Non-trivial functional dependency

​

  • A → B has a non-trivial functional dependency if B is not a subset of A.

​

  • When A intersection B is NULL, then A → B is called as complete non-trivial.

​

Example:

​

  1. ID   →    Name,  

​

  • Name   →    DOB  

​

6 of 15

Inference Rule (IR):

​

  • The Armstrong's axioms are the basic inference rule.
  • Armstrong's axioms are used to conclude functional dependencies on a relational database.
  • The inference rule is a type of assertion.
  • It can apply to a set of FD(functional dependency) to derive other FD.
  • Using the inference rule, we can derive additional functional dependency from the initial set.

​

  • The Functional dependency has 6 types of inference rule:

​

7 of 15

1. Reflexive Rule (IR1)

​

  • In the reflexive rule, if Y is a subset of X, then X determines Y.

​

  1. If X ⊇ Y then X  →    Y  

​

  • Example:

​

  1. X = {a, b, c, d, e}  

​

  • Y = {a, b, c}  

​

8 of 15

2. Augmentation Rule (IR2):

​

  • The augmentation is also called as a partial dependency.

​

  • In augmentation, if X determines Y, then XZ determines YZ for any Z.

​

  1. If X    →  Y then XZ   →   YZ   

​

  • Example:

​

  1. For R(ABCD),  if A   →   B then AC  →   BC  

​

9 of 15

3. Transitive Rule (IR3):

​

  • In the transitive rule, if X determines Y and Y determine Z, then X must also determine Z.

​

  1. If X   →   Y and Y  →  Z then X  →   Z  

​

10 of 15

4. Union Rule (IR4):

​

  • Union rule says, if X determines Y and X determines Z, then X must also determine Y and Z.
  • If X    →  Y and X   →  Z then X  →    YZ     

​

  • Proof:
  • 1. X → Y (given)�2. X → Z (given)�3. X → XY (using IR2 on 1 by augmentation with X. Where XX = X)�4. XY → YZ (using IR2 on 2 by augmentation with Y)�5. X → YZ (using IR3 on 3 and 4)

​

11 of 15

5. Decomposition Rule (IR5):

​

  • Decomposition rule is also known as project rule.
  • It is the reverse of union rule.
  • This Rule says, if X determines Y and Z, then X determines Y and X determines Z separately.
  • If X   →   YZ then X   →   Y and X  →    Z   

​

  • Proof:
  • 1. X → YZ (given)�2. YZ → Y (using IR1 Rule)�3. X → Y (using IR3 on 1 and 2)

​

12 of 15

  • 6. Pseudo transitive Rule (IR6):

​

  • In Pseudo transitive Rule, if X determines Y and YZ determines W, then XZ determines W.

​

  1. If X   →   Y and YZ   →   W then XZ   →   W   

​

  • Proof:
  • 1. X → Y (given)�2. WY → Z (given)�3. WX → WY (using IR2 on 1 by augmenting with W)�4. WX → Z (using IR3 on 3 and 2)

​

13 of 15

Advantages of Functional Dependency:

​

  • Functional Dependency avoids data redundancy.

​

  • Therefore same data do not repeat at multiple locations in that database

​

  • It helps you to maintain the quality of data in the database

​

  • It helps you to defined meanings and constraints of databases

​

  • It helps you to identify bad designs

​

  • It helps you to find the facts regarding the database design

​

14 of 15

�Alternative Approaches to Database Design�

  1. Bottom Up Approach: This approach builds relations on the basis of the relationships existing among individual attributes.
  2. This is not so commonly used as collecting a large number of attributes initially can be a very complex task.
  3. This approach is also known as Design by Synthesis.

​

15 of 15

Alternative Approaches to Database Design

2.Top Down Approach: This approach is known as Design by Analysis as it begins with certain relations and then after some analysis .

  • Various rules and methods are applied until all the desirable properties are met.

​