Download Functional Database Management Systems (DBMS) and more Study Guides, Projects, Research Database Management Systems (DBMS) in PDF only on Docsity!
1 Basics:
Functional Dependence :- Let R be a relation and x and y be two arbitrary subset of the set of attributes of R , then we say that y is functionally dependent on x, represented as: X Y or X functionally determines Y, if for each value of X in R there is precisely one value of Y associated with it. Operationally, when two tuples of R agree on their X value, they will also agree on their Y value. Notes:- o Here X is called the determinant and Y is called the dependent
o if X Y, then cardinality from X to Y will be many to one.
o if cardinality between X & Y is 1 : 1, then we can say
X Y
Y X
Eg:- Consider a relation SCP ( s#, p#, city, quantity) where, the supplier with supplier number s who lives in “city”, supplies the specified “quantity” of the part with p#. Instance:- S# City P# Qty S1 Gwalior P1 100 S1 Gwalior P2 100 S2 Indore P1 200 S2 Indore P2 200 S3 Indore P2 300 S4 Gwalior P2 400 S4 Gwalior P4 400 S4 Gwalior P5 400 Consider following functional dependence s# city It means that, is s# is same in two tuples, then the city will also be the same. Following are some more f.d. (s # , p # ) qty. (s # , p # ) city (s # , p # ) {city, Qty} (s # , p # ) s # (s # , p # ) p # Notes :- (i) Functional dependence is a property of semantic or meaning associated with the attributes, which depends upon the logic of the application. (ii) In fact F.D. specifications are integrity constraints specifications. Because these limit the tuples in a relationship. In other words, a relation cannot have tuples which does not satisfy the functional dependence specified. (iii) F.D’s may be of two types :- a) Time dependent b) Time Independent Time dependent F.D.’s are property of an instance of database; whereas time independent F.D.’s are the property of schema of database.
Chapter 5
FUNCTIONAL DEPENDENCE
Hence obviously time independent F.D.’s are of our major interest. (iv) Functional Dependence & Super Key:- The notion a concept of functional dependence is the generalization of super key concept. Let X & Y are two sets of attributes such that:
X R & Y R
The functional dependence X Y holds on R if for two tuples t 1 & t 2 in R satisfies the
following constraint If t 1 [X] = t 2 [X] then t 1 [Y] = t 2 [Y] If K in the super key of relation R then we say that
K R
Must hold true because for two tuples t 1 & t 2 If t 1 [K] = t 2 [K] then t 1 [R] = t 2 [R] It means that t 1 & t 2 are duplicate tuples
Ex : Let a relation be R(A, B, C, D) A given instance of the relation is: A B C D a1 b1 c2 d a2 b4 c3 d a3 b2 c1 d Let the given functional dependence are: A B B D Can the following tuples be inserted in the relation: (a1, b2, c1, d3) (a2, b4, c4, d7) (a3, b2, c2, d3)
Ex :- Given the relation R (A,B,C) Instance:- (^) A B C a 1 b 1 c 1 a 1 b 1 c 2 a 2 b 1 c 1 a 2 b 1 c 3 Determine all types of functional dependence in relation Solution:- o A B o C B o {C, A} B o {C, A} R Hence {C, A} is super key Note:- The functional dependence derived with this method is time dependent functional dependence. To find out time independent F.D. many instances of the relation has to be considered.
2 Trivial & Non- Trivial Functional Dependence
A dependence is trivial if its dependent (R.H.S.) is a sub-set of determinent (L.H.S.). It means that for two sets of attributes A & B of the relation R, if If B A
R = (A, B, C, G, H, I)
F = { A B
A C
CG H
CG I
B H }
Find some members of F + A H by transitivity from A B and B H AG I by augmenting A C with G, to get AG CG and then transitivity with CG I CG HI by augmenting CG I to infer CG CG I, and augmenting of CG H to infer CGI HI, and then transitivity
5 Procedure for Computing F+
F + = F
repeat for each functional dependency f in F + apply reflexivity and augmentation rules on f add the resulting functional dependencies to F + for each pair of functional dependencies f 1and f 2 in F + if f 1 and f 2 can be combined using transitivity then add the resulting functional dependency to F + until F + does not change any further
Ex1 : Let R = (A, B, C, D) and F = { A B, A C, BC D }. Find whether the dependence A D is in F+. Solution: Since AB and AC hence, ABC union or additivity Since, ABC and BC D hence A D transitivity So we can say that the dependence A D is in F+.
7. Closure of an Attribute or Attribute Set:
Let there be a relation R with some attributes. The X is an attribute or a set of attribute of R. The closure of X under a set of functional dependencies F, written as X+, is the set of attributes {A1, A2, A3… Am} such that the FD XAi for Ai X+^ follows from F by the inference axioms for functional dependencies. Algorithm to compute X+ Let there be a relation R with some attributes. Let X is an attribute or a set of attributes. F is the set of functional dependence given on R. Then to find the closure of X, the following algorithm is applied.
X+^ = X repeat oldX+^ := X+; for each functional dependence Y Z in F do if X+^ Y then X+^ := X+^ Z; until ( X+^ = oldX+)
Ex 1: Let X = BCD and F = { ABC, CDE, EC, DAEH, ABHBD, DHBC}. Compute X+ of X under F. Sol: Initialize X+ to X so X+ := BCD. Since the lhs of FD CDE is a subset of X+, so X+ := BCDE. Since thle lhs of FD DAEH is a subset of X+, so X+ := ABCDEH. X+ can not be augmented further, so the algorithm ends. Hence, X+ of X under F is ABCDEH.
Ex 2: There is a relation R(A, B, C, G, H, I). Let X = AG and F = { AB, AC, CGHI, BH}. Compute X+ of X under F. Sol: Initialize X+ to X so X+ := AG. Since the lhs of FD AB is a subset of X+, so X+ := ABG. Since the lhs of FD AC is a subset of X+, so X+ := ABCG. Since the lhs of FD CGH is a subset of X+, so X+ := ABCGH. Since the lhs of FD CGI is a subset of X+, so X+ := ABCGHI.
X+ cannot be augmented further, so the algorithm ends. Hence X+ of X under F is ABCGHI.
Ex3: Given R(ABCDE) and the set of FDs on R given by F = { AB CD,ABC E,C A}. What is X+ if X = ABC? What is the candidate key of R? In what normal form is R?
8. Uses of Attribute Closure
There are several uses of the attribute closure algorithm: (i) Testing for superkey: To test if is a super-key, we compute +, and check if + contains all attributes of R , then is the super key. (ii) Testing functional dependencies To check if a functional dependency holds (or, in other words, is in F +), just check if +. That is, we compute + by using attribute closure, and then check if it contains . Is a simple test, and very useful (iii) Computing closure of F For each R, we find the closure +, and for each S +, we output a functional dependency S.
9. Testing whether or not an FD is in a closure
To find whether F implies X Y or not, we simply compute X+ under the FDs set F. If Y X+, then we can say that, the dependence X Y is in F otherwise not. Following is the algorithm; it uses the algorithm to find the closure of a set of attributes.
Ex 1:
Compute X+ using the previous algorithm. If Y X+, then X Y F+ := true else X Y F+ := false
C is extraneous in AB CD since A B C can be inferred even after deleting C
Testing if an Attribute is Extraneous Consider a set F of functional dependencies and the functional dependency in F. (i) To test if attribute A is extraneous in compute ({} – A)+^ using the dependencies in F check that ({} – A)+^ contains A; if it does, A is extraneous
(ii) To test if attribute A is extraneous in compute +^ using only the dependencies in F’ = ( F – { }) { ( – A )}, check that +^ contains A; if it does , A is extraneous
12. Non-redundant Cover, minimal cover or canonical cover of FD:
There is a relation R with a given set F of functional dependencies. G is called the canonical cover of F if: o all FD of F are implied by G o there is no redundant FD in G o there is no extraneous attribute on the right hand side of all the FD of G o there is no extraneous attribute on the left hand side of all the FD of G
The working method is: o Break all RHS (dependents) to atomic attributes. o Remove all redundant/extraneous FD from the set: o Take an FD XY from the set F. Consider F’ = { F – ( XY) } o Find X+ based on F’. o If Y X+, then remove XY from the set o Remove all extraneous attributes from the determinants of all FD’s.
Ex 1: If F = { A BC, CD E, E C, D AEH, ABH BD, DH BC} Find the non-redundant cover of F. Sol: Consider the FD, CDE; we find CD+ from set { F – (CD E) }. It comes out to be ABCDEH. Since, the RHS E is subset of ABCDEH; hence the FD can be removed.
Input: A set of FDs F Output: A non-redundant cover F
G := F; /initialize G to F / for each FD XY in G do if XY { F – ( XY) }+ /{ F – (X Y) } implies XY/ then F := { F – ( XY) }; [end for]
G := F; /*G is non-redundant cover of F */ remove all extraneous attribute from the determinant; remove all extraneous attribute from the dependant;
end;
Now G = { A BC, E C, D AEH, ABH BD, DH BC} Now, consider the FD, DHBC; we find DH+ from the FD set {G – (DHBC) }. It comes out to be ABCDEH. Since, the RHS BC is subset of ABCDEH; hence this FD can also be removed. No other FD can be removed, so the non-redundant cover of F is: G = { A BC, E C, D AEH, ABH BD}
Ex2: Find the canonical cover of F = { A BC, CD E, E C, D AEH, ABH BD, DH BC} Sol: o First, we remove all redundant FD from F. Consider the FD, CDE; we find CD+ from set { F – (CD E) }. It comes out to be ABCDEH. Since, the RHS E is subset of ABCDEH; hence the FD can be removed. Now G = { A BC, E C, D AEH, ABH BD, DH BC} Now, consider the FD, DHBC; we find DH+ from the FD set {G – (DHBC) }. It comes out to be ABCDEH. Since, the RHS BC is subset of ABCDEH; hence this FD can also be removed. No other FD can be removed, so the non-redundant cover of F is: G = { A BC, E C, D AEH, ABH BD} o The FD A BC can be decomposed into: A B A C o The FD ABH BD can be decomposed into ABHB ABHD Since, A B, so both can be reduced to AHB AHD o But, AH B is redundant since, A B is already there. So the canonical cover is Fc = { A B, A C, E C, D A, D E, D H, AH D}
Notes:
_1. Functional dependencies (FDs) are used to specify formal measures of the "goodness" of relational designs.
- FDs are_ constraints that are derived from the meaning and interrelationships of the data attributes
13 Non-loss Decomposition:
The process of normalization causes decomposition of the relation. The decomposition must be non-loss type. It means that every information in the original relations must be preserved in the decomposed relations. The information is in three forms: o In attributes o In tuples o In functional dependence All the three must be preserved during decomposition.
14. Miscellaneous Points: 1: Informally, each tuple in a relation should represent one entity or relationship instance. (Applies to individual relations and their attributes).
2: Design a schema that does not suffer from the insertion, deletion and update anomalies. If there are any present, then note them so that applications can be made to take them into account.
3: Relations should be designed such that their tuples will have as few NULL values as possible Attributes that are NULL frequently could be placed in separate relations (with the primary key) Reasons for nulls:
- attribute not applicable or invalid
- attribute value unknown (may exist)
- value known to exist, but unavailable 4. Bad designs for a relational database may result in erroneous results for certain JOIN operations. The "lossless join" property is used to guarantee meaningful results for join operations. The relations should be designed to satisfy the lossless join condition. No spurious tuples should be generated by doing a natural-join of any relations. 5. There are two important properties of decompositions: (a) non-additive or losslessness of the corresponding join (b) preservation of the functional dependencies. Note that property (a) is extremely important and cannot be sacrificed. Property (b) is less stringent and may be sacrificed.
Exercises:
1. Suppose we have the following requirements for a university database that is used to keep track of students transcripts: (a) The university keeps track of each student's name (SNAME), student number (SNUM), social security number (SSSN), current address (SCADDR) and phone (SCPHONE), permanent address (SPADDR) and phone (SPPHONE), birthdate (BDATE), sex (SEX), class (CLASS) (freshman, sophomore, ..., graduate), major department (MAJORDEPTCODE), minor department (MINORDEPTCODE) (if any), and degree program (PROG) (B.A., B.S., ..., Ph.D.). Both ssn and student number have unique values for each student. (b) Each department is described by a name (DEPTNAME), department code (DEPTCODE), office number (DEPTOFFICE), office phone (DEPTPHONE), and college (DEPTCOLLEGE). Both name and code have unique values for each department. (c) Each course has a course name (CNAME), description (CDESC), code number (CNUM), number of semester hours (CREDIT), level (LEVEL), and offering department (CDEPT). The value of code number is unique for each course. (d) Each section has an instructor (INSTUCTORNAME), semester (SEMESTER), year (YEAR), course (SECCOURSE), and section number (SECNUM). Section numbers distinguish different sections of the same course that are taught during the same semester/year; its values are 1, 2, 3, ...; up to the number of sections taught during each semester. (e) A transcript refers to a student (SSSN), refers to a particular section, and grade (GRADE).
Design an relational database schema for this database application. First show all the functional dependencies that should hold among the attributes. Then, design relation schemas for the database that are each in 3NF or BCNF. Specify the key attributes of each relation. Note any unspecified requirements, and make appropriate assumptions to make the specification complete. 2. Consider the following two sets of functional dependencies F= {A ->C, AC ->D, E->AD, E ->H} and G = {A ->CD, E ->AH}. Check whether or not they are equivalent.
There is a relation R(A,B,C,D,E). The given FD’s are F = {ABC, CDE, BD, EA}. o Find B+. o List various candidate keys of R. o Compute the closure of the following set of FD. o Compute the canonical cover of F.
4. Consider the universal relation R = {A, B, C, D, E, F, G, H, I, J} and the set of functional dependencies F = {ABC, A DE, BF, FGH, DI J}. What are the keys for R?
5. Repeat exercise 3 for the following different set of functional dependencies G = ABC, BD EF, AD GH, AI, H J.
6. Given relation R(A,B,C,D,E) with dependencies AC C CD DE B is AB a candidate key? is ABD a candidate key? (Find all candidate keys)
Steps for finding candidate keys:
- for any set Y to be candidate key, Y+ must contain all the attributes; but the closure of no proper subset of Y can contain all attributes.
- find all attributes of the given relation.
- see all dependants, find missing attribute in dependants; let the group is X. Only X or any superset of X may be candidate key.
- find the closure of X or closure of superset of X.
- if a set Y is a candidate key, no proper superset can be candidate key (it will be a super key)
