Question

Calculate the closures {ssn}+ and {dnumber}+

Exercisel Consider the relation schema EMP DEPT(ename,ssn,bdate,address,dnumber,dname,dmgrssn) and the following set G of functional dependencies on EMP DEPT: ssn ename, bdate, address, dnumber dnumber dname, dmgrssn Calculate the closures (ssnj. and (dnumber)

Answer 1

The closure of a Functional Dependency is defined as the set of all attributes that can be derived from that particular dependency, using the FD rules defined.

In the above example, we have 2 FD rules:
1. ssn -> ename, bdate, address, dnumber
2. dnumber -> dname, dmgrssn

For calculating closure for a particular attribute, we initially add that attribute to the set. Then we add all the attributes derived from that original attribute, from the FD rules defined previously. After this operation, we add the attributes derivable from the attributes present in the closure set. This process is repeated, till no more attributes can be added.

-----------------------------------------------------------------------------------------------------------------------------------------------

For calculating closure of ssn,

Step 1 : {ssn}⁺ = {ssn}
Step 2 : {ssn}⁺ = {ssn, ename, bdate, address, dnumber} (from ssn -> ename, bdate, address, dnumber)
Step 3 : {ssn}⁺ = {ssn, ename, bdate, address, dnumber, dname, dmgrssn} (from dnumber -> dname, dmgrssn)

For calculating closure of ssn,

Step 1 : {dnumber}⁺ = {dnumber}
Step 2 : {dnumber}⁺ = {dnumber, dname, dmgrssn} (from dnumber -> dname, dmgrssn)

-----------------------------------------------------------------------------------------------------------------------------------------------

Therefore, the closure will be given as:

{ssn}⁺ = {ssn, ename, bdate, address, dnumber, dname, dmgrssn}

{dnumber}⁺ = {dnumber, dname, dmgrssn}