CS457 Syllabus & Progress

Finding the closure set X⁺: a concrete example

A concrete example of finding some closure sets

Let us first use meaningful attribute names to illustrate the algorithm

Relation: (stores information of employees AND projects)

EmpProj(SSN, FName, LName, PNumber, PName, PLocation, Hours)

Functional dependencies:

℉ = { SSN → FName, LName PNumber → PName, PLocation SSN,PNumber → Hours }

Computing SSN⁺:

Initialize: SSN⁺ := SSN;
For every FP in ℉ do: 1. SSN → FName, LName ---> SSN⁺ := SSN, FName, LName DONE = FALSE; 2. PNumber → PName, PLocation ---> no change 3. SSN,PNumber → Hours ---> no change Result at end of loop: SSN⁺ = SSN, FName, LName
There were changes, so loop again... For every FP in ℉ do: 1. SSN → FName, LName ---> no change 2. PNumber → PName, PLocation ---> no change 3. SSN,PNumber → Hours ---> no change Result at end of loop: SSN⁺ = SSN, FName, LName
There were NO changes, so DONE SSN⁺ = SSN, FName, LName

Computing PNumber⁺:

Initialized: PNumber⁺ := PNumber;
For every FP in ℉ do: 1. SSN → FName, LName ---> no change 2. PNumber → PName, PLoc ---> PNumber⁺ := PNumber, PName, PLoc DONE = FALSE; 3. SSN,PNumber → Hours ---> no change Result at end of loop: PNumber⁺ = PNumber, PName, PLoc
There were changes, so loop again... For every FP in ℉ do: 1. SSN → FName, LName ---> no change 2. PNumber → PName, PLoc ---> no change 3. SSN,PNumber → Hours ---> no change Result at end of loop: PNumber⁺ = PNumber, PName, PLoc
There were NO changes, so DONE PNumber⁺ = PNumber, PName, PLoc

Computing (SSN,PNumber)⁺:

Initialize: (SSN,PNumber)⁺ := SSN,PNumber;
For every FP in ℉ do: 1. SSN → FName, LName ---> (SSN,PNumber)⁺ := SSN, PNumber, FName, LName DONE = FALSE; 2. PNumber → PName, PLoc ---> (SSN,PNumber)⁺ := SSN, PNumber, FName, LName, PName, PLoc DONE = FALSE; 3. SSN,PNumber → Hours ---> (SSN,PNumber)⁺ := SSN, PNumber, FName, LName, PName, PLoc, Hours DONE = FALSE; Result at end of loop: (SSN,PNumber)⁺ = SSN, PNumber, FName, LName, PName, PLoc, Hours
There were changes, so loop again... For every FP in ℉ do: 1. SSN → FName, LName ---> no change 2. PNumber → PName, PLoc ---> no change 3. SSN,PNumber → Hours ---> no change Result at end of loop: (SSN,PNumber)⁺ = SSN, PNumber, FName, LName, PName, PLoc, Hours
There were NO changes, so DONE (SSN,PNumber)⁺ = SSN, PNumber, FName, LName, PName, PLoc, Hours

Observation in the computation of the closures
- Summary of the results:
- Fact 1 :
  Because:
- Fact 2 :
  Because:
  So every attribute in (SSN,PNumber) is necessary to form the super key
  Therefore, (SSN,PNumber) is minimal
  In other words: (SSN,PNumber) is a key .

Finding the closure set X+: a concrete example

Finding the closure set X⁺: a concrete example