Download Functional Dependency and Key Findings in Relational Databases and more Study notes Database Management Systems (DBMS) in PDF only on Docsity!
Functional Dependency
Functional dependency (FD) is a set of constraints
between two attributes in a relation. Functional
dependency says that if two tuples have same
values for attributes A1, A2,..., An, then those two
tuples must have to have same values for
attributes B1, B2, ..., Bn.
Functional dependency is represented by an arrow
sign (→) that is, X→Y, where X functionally
determines Y. The left-hand side attributes
determine the values of attributes on the right-hand
side.
Trivial − If a functional dependency (FD) X → Y holds, where Y is a subset of X, then it is called a trivial FD. Trivial FDs always hold. Non-trivial − If an FD X → Y holds, where Y is not a subset of X, then it is called a non-trivial FD. Completely non-trivial − If an FD X → Y holds, where x intersect Y = Φ, it is said to be a completely non-trivial FD.
The closure of a set F of functional dependencies is the set of all functional dependencies logically implied by F. We denote the closure of F by F+
CLOSURE OF FUNCTIONAL DEPENDENCY
COMPUTE ATTRIBUTE CLOSURE OF EACH OF THE ATTRIBUTES
Consider a relation R with the schema R(A, B, C, D, E, F) with a set of functional dependencies F as follows; {AB → C, BC → AD, D → E, CF → B} Finding (AB)+ First, let us find (AB)+ , the closure of attribute set AB (We do not need to test all the attributes individually. Instead we can try with those attributes that are on the left hand side of any FD.) · result = AB As AD determines C, C can be included with the result. Hence, result = AB U C = ABC. According to second FD BC → AD, the attributes B and C together can identify both A and D. Hence, result = ABC U AD = ABCD · If you know D, you will know E according to third FD D → E. so, result = ABCD U E = ABCDE. · F cannot be identified by any of these FDs. And our result can include ABCDE attributes only. Hence, the solution is AB is not the key for R. The reason is, the closure of AB, i.e., (AB)+ does not include all the attributes of R in the result.
Finding (ABF)+ Then what would be the key for R? We can try all the left hand side attributes (because they are the determiners), or some of their combination. From the above example, we would get an idea to include F as one of the key attribute. So, let us try to find (ABF)+ , the closure of attribute set ABF. result = ABF · from the above example, we could say (AB)+ = ABCDE. we know C and F, then according to CF → B, we would deduce the result as ABCDEF, which includes all the attributes from R. Hence, the solution is ABF is one of the key for R. because, (ABF)+ includes all the attributes of R.
KEYS
A superkey (or key superset) of a relation schema is a
set of attributes S so that no two tuples of the
relationship can have the same values on S.
A key is thus a minimal superkey: it is a superkey with
no extraneous attributes that can be removed.
Relations can have multiple keys, in which case each is
called a candidate key.
For arbitrary performance-related reasons we
designated one of these the primary key ; other
candidate keys are known as secondary keys.
A prime attribute is an attribute (ie column name) that
belongs to some candidate key. A nonprime attribute
is not part of any key.
Using Attribute Closure method, Super key is {SSN,Dnumber} , Primary Key is {SSN} as it is the minimal super key Primary attributes are SSN & Dnumber , Non-Prime attributes are Ename,Bdate,Address,Dnumber
Step 3: Considering B If the set L does not produce a key for R in Step 2, then begin adding attributes, one by one, from the set denoted by B to attributes of L and compute their closure. Attributes should be added to L in turn to ensure that all candidate keys of R are found. (If L is empty, then begin by computing the closure of attributes in the set B) For every attribute A, if A is in set R, then A will not be part of any candidate key of R
- Example -
- Example -
- Example -
R=(branch-name, branch-city, assets,customer-name, loan-number, amount) The functional dependencies for this relation schema are: R=(branch-name,customer-name,banker-name,office-number) The functional dependencies for this relation schema are:
FIND KEYS OF THE RELATIONAL SCHEMAS
