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:

 (SSN)+ = SSN, FName, LName (PNumber)+ = PNumber, PName, PLoc (SSN,PNumber)+ = SSN, PNumber, FName, LName, PName, PLoc, Hours

• Fact 1 :

 (SSN,PNumber) is a super key

Because:

 (SSN,PNumber)+ = R = (SSN, PNumber, FName, LName, PName, PLoc, Hours)

• Fact 2 :

 (SSN,PNumber) is minimal

Because:

 {(SSN,PNumber) - (SSN)}+ = (PNumber) ≠ R {(SSN,PNumber) - (PNumber)}+ = (SSN) ≠ R

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 .