DBMS Unit 3 Part 1 Database Design & Normalization

Que3.1. What’s a functional reliance? Explain its part in database design. Describe the conclusion rules for functional dependences . Answer Functional reliance 1. A functional reliance is a constraint between two sets of attributes from the database. 2. A functional reliance is denoted by X Y, between two sets of attributes X and Y that are subsets of R specifies a constraint on the possible tuples that can form a relationr. 3. The constraint for any two tuples t1 and t2, in r which have t1( X) = t2( X); Also, must have t1( Y) = t2( Y); 4. This means that the values of the Y element of a tuple in r depends on, or are determined by the value of the X factors, or alternately, the values of the X element of a tuple uniquely( or functionally) determine the value of the Y element. part of functional reliance 1. Functional reliance allows the database developer to express data about the enterprise that the developer is modeling with the enterprise databases. 2. It allows the contrivers to express constraints, which can not be expressed with super keys. Conclusion rules for functional dependences 1. Reflexivity rule If is a set of attributes and also holds. 2. addition rule If holds and is a set of attributes also holds. 3. Transitivity rule If holds and holds, also holds. 4. Complementation rule If hold, also{ R –()} holds. 5. Multivalued addition rule hold and R and, also holds. 6. Multivalued transitivity rule If holds, also holds, also – holds. 7. Replication rule If holds and and there’s a similar that R and = and, also holds. 8. Union rule if holds and holds, also holds. 9. corruption rule If holds, also holds, and holds. 10. Pseudotransitivity rule If holds and holds, also holds. Que3.2. What’s functional reliance? Explain trivial and non-trivial functional reliance. Define canonical cover. cipher canonical cover for the following R = ( A, B, C) F = { A BC, B C, A B, AB C} Answer Functional reliance ReferQ.3.1, Page 3 – 2A, Unit- 3. Trivial functional reliance The reliance of an trait on a set of attributes is known as trivial functional reliance if the set of attributes includes that trait. A B is trivial functional reliance if B is a subset ofA. Non-trivial functional reliance If a functional reliance X Y holds true where Y isn’t a subset of X also this reliance is called nontrivial functional reliance. For illustration Let a relation R( A, B, C) The following functional dependences arenon-trivial A B( B isn’t a subset of A) A C( C isn’t a subset of A) The following dependences are trivial A, B} B( B is a subset of{ A, B}) Canonical cover A canonical cover of a set of functional dependences F is a simplified set of functional dependences that has the same check as the original setF. Numerical There are two functional dependences with the same set of attributes A BC A B Database Design & Normalization 3 – 4 A( CS/ IT- Sem- 5) These two can be combined to get A BC Now, the revised set F becomes F = { A BC B C AB C There’s an extraneous trait in AB C because indeed after removing AB C from the set F, we get the same closures. This is because B C is formerly a part ofF. Now, the revised set F becomes F = { A BC B C C is an extraneous trait in A BC, also A B is logically inferred by A B and B C( by transitivity) F = { A B B C After this step, F doesn’t change presently. Hence, the needed canonical cover is, F = { A B, B C} Que3.3. Explain full functional reliance and partial functional reliance. Answer Full functional reliance 1. Given a relation scheme R and functional reliance X Y, Y is completely functionally dependent on X, if there’s no Z, where Z is a proper subset of Y similar that ZY. 2. The reliance X Y is left reduced, there being no extraneous attributes in theL.H.S of the reliance. For illustration In the relational schema R( ABCDEH) with the FDs. F = { A BC, CD E, E C, CD AH, ABH BD, DH BC}. Database Management System 3 – 5 A( CS/ IT- Sem- 5) The reliance A BC is left reduced and BD is completely functionally dependent onA. However the functional dependences ABH BC isn’t left reduced because the trait B being extraneous in this reliance. Partial functional reliance 1. Given a relation schema R with the functional dependences F defined on the attributes of R and K as a seeker keys if X is a proper subset of K and if X A also A is said to be incompletely dependent onK. i. InFig.3.3.1,( Name Course) is a seeker key, So Name and Course are high attributes, Grade is completely functionally dependent on the seeker keys and Phoneno., Course- deptt. and roll no. are incompletely functional dependent on the seeker key. ii. Given R( A, B, C, D) and F = { AB C, B D}. also key of this relation is AB and D is incompletely dependent on the key. Que3.4. Define partial functional reliance. Consider the following two way of functional dependences F = { A C, AC D, E announcement, E H} and G = { A CD, E AH}. Check whether or not they are original. Answer Partial functional reliance ReferQ.3.3, Page 3 – 4A, Unit- 3. Numerical From F, E announcement E A( By corruption Rule) E D Also given that E H So, E AH( By Union Rule) which is a FD of setG. Again A C and AC D Imply A D( By Pseudotransitivity Rule) A CD( by Union Rule) which is FD of setG. Hence, F and G are original. Que3.5. Write the algorithm to find minimum cover F for set of functional dependences E. Answer Algorithm 1. Set F = E. 2. Replace each functional reliance X{ A1, A2,., An} in F by the n functional dependences X A1, X A2,., X An. 3. For each functional reliance X A in F for each trait B that’s an element of X still, if{{ F –{ X A}}{( X –{ B}) A}} is original toF. also replace X A with( X –{ B}) A inF. 4. For each remaining functional reliance X A in F still, if{ F –{ X A}} is original toF. also remove X A fromF.

Leave a Comment