Functional Dependencies II

# Functional Dependencies II - CANDIDATE KEYS A candidate key...

This preview shows pages 1–5. Sign up to view the full content.

CANDIDATEKEYS A candidate key of a relation schema R is a subset X of the attributes of R with the following twoproperties: 1. Every attribute is functionally dependent on X, i.e., X + =all attributes of R (also denoted as X + =R). 2. No proper subset of X has the property (1), i.e., X is minimal with respect to the property (1). A sub-key of R: asubset of a candidate key; a super-key :aset of attributes containing a candidate key. We also use the abbreviation CK to denote "candidate key". Let R(ABCDE) be a relation schema and consider the fol- lowing functional dependencies F = {AB E, AD B, B C, C D}. Since ( AC ) + =ABCDE, A + =A ,and C + =CD, we knowthat ACisacandidate key ,both A and C are sub-keys, and ABC is a super-key .T he only other candidate key sare AB and AD. Note that since nothing determines A, A is in every can- didate key.

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
-2- Computing All Candidate Keys of a Relation R . Givenarelation schema R( A 1 , A 2 ,..., A n )and a set of functional dependencies F which hold true on R, howcan we compute all candidate keysofR? Since each candidate key must be a minimal subset Z of { A 1 A n }such that Z + =R ,wehav e the following straightforward (and brute-force) way algorithm: (1) Constructalist L consisting of all non-empty subsets of { A 1 , ..., A n }(there are 2 n - 1o fthem). These subsets are arranged in L in ascending order of the size of the subset: We get L = Z 1 , Z 2 Z 2 n - 1 ,such that | Z i | | Z i + 1 |. Here | Z i |denotes the number of elements in the set Z i . (2) Initialize the set K = {} (K will contain all CK’sofR). While L is not empty ,remove the first element Z i from L, and compute Z i + . If Z i + ,then (a) Add Z i to K (b) Remove any element Z j from L if Z i Z j (because Z j is too big, it can not be a CK). (3) OutputKasthe final result.
-3- ANALYSIS The method in previous page is correct but not effi- cient: the list L is too big (exponential in the number of attributes in R). IDEA: •F ocus on "necessary" attributes that will definitely appear in ANY candidate key of •I gnore "useless" attribute that will NEVER be part of a CK. Necessary attributes: An attribute A is said to be a necessary attribute if (a) A occurs only in the L.H.S. (left hand side) of the fd’s in F; or (b) A is an attribute in relation R, but A does not occur in either L.H.S. or R.H.S. of anyfdinF . In other words, necessary attributes NEVER occur in the R.H.S. of anyfdinF . Useless attributes: An attribute A is a useless attribute if A occurs ONLYinthe R.H.S. of fd’sinF . Middle-ground attributes: An attribute A in relation R is a middle-ground attribute if A is nei- ther necessary nor useless. Example. Consider the relation R(ABCDEG) with set of fd’sF={AB C, C D, AD E} Necessary Useless Middle-ground A, B, G EC ,D

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
-4- An important observation about necessary attribute is: a nec- essary attribute will appear in every CK of R, and thus ALL neces- sary attributes must appear in every CK of R.
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

### Page1 / 20

Functional Dependencies II - CANDIDATE KEYS A candidate key...

This preview shows document pages 1 - 5. Sign up to view the full document.

View Full Document
Ask a homework question - tutors are online