Que3.6. Define minimum cover. Suppose a relation R( A, B, C) has FD set F = { A B, B C, A C, AB B, AB C, AC B}. Convert this FD set into minimum cover. Answer minimum cover A minimum cover of a set of FDs F is a minimum set of functional dependences Fmin that’s original toF. Numerical Given R( A, B, C) Non-redundant cover for F Step 1 Only one trait on right hand side F = A B B C A C AB B AB C AC B Database Management System 3 – 7 A( CS/ IT- Sem- 5) Step 2 No extraneous trait on left hand side. Since AB B, AB C, AC B are extraneous trait. Hence, remove all these we get A B B C A C Step 3 By rule of transitivity, we can remove. Hence, we get the minimum cover A C A B B C Que3.7. Define normal forms. List the delineations of first, alternate and third normal forms. Explain BCNF with a suitable illustration. OR Explain 1NF, 2NF, 3NF and BCNF with suitable illustration. Answer 1. Normal forms are simply stages of database design, with each stage applying further strict rules to the types of information which can be stored in a table. 2. Normal form is a system to homogenize the relations in database. 3. Normal forms are grounded on the functional dependences among the attributes of a relation. Different normal forms are 1. First Normal Form( 1NF) a. A relations R is in 1NF if all disciplines are simple i.e., all rudiments are infinitesimal. For illustration The relation LIVED- IN given in Table3.7.1 isn’t in 1NF because the sphere values of the trait ADDRESS are not infinitesimal. 2. Alternate Normal Form( 2NF) a. A relation R is in 2NF if and only if it’s in 1NF and everynon-key trait is completely dependent on the primary key. b. A relation R is in 2NF if everynon-prime trait of R is completely functionally dependent on each relation key. For illustration The relation flight( flight#,Type_of_aircraft, date, source, destination) with functional dependences given isn’t in 2NF. flight# flight# date source destination Then flight# date is crucial butType_of_aircraft depends only on flight#. To convert relation flight( flight#,Type_of_aircraft, data, source, destination) into 2NF break the relation into two relation flight1( flight#,Type_of_aircraft) flight2( flight#, date, source, destination) 3. Third Normal Form( 3NF) a. A relation R is in 3NF if and only if, for all time, each tuple of R consists of a primary crucial value that identifies some reality in the database. b. A relation schema R is in 3NF with respect to a set F of functional dependences , if for all functional dependences in F of the form where R and R, at least one of the following holds is a trivial functional reliance. ii. is a super key forR. iii. Each trait A in – is contained in seeker key forR. For illustration Let us consider a relation R( B, E, F, G, H) with primary crucial BFGH and functional reliance are B F, F GH. The relation R has transitive property as B F, F GH also B GH. So R isn’t in 3NF. To convert relation R in 3NF break the relation R into two relation as R1( B, E, F), R2( F, G, H). 4. Boyce- Codd Normal Form( BCNF) a. A relation R is in BCNF if and only if every determinant is a seeker key. b. A relation schema R is in BCNF with respect to a set F of functional dependences if for all functional dependences in F of the form where R and R, at least one of the following holds is a trivial functional reliance( i.e.,) ii. is a super key for schemaR. c. A database design is in BCNF if each member of the set of relation schemas that constitute the design is in BCNF. For illustration Let consider a relation R( A, B, C, D, E) with AC as primary key and functional dependences in the relation R is given as A B, C DE. To convert relation R into BCNF break the relation in three relation R1( A, B), R2( C, D, E), R3( A, C). Que3.8. Consider the universal relational schema R( A, B, C, D, E, F, G, H, I, J) and a set of following functional dependences . F = { AB C, A DE, B F, F GH, D IJ} determine the keys for R? putrefy R into 2nd normal form. Answer AB) = ABC AB C = ABCDE A DE = ABCDEF B F = ABCDEFGH F GH = ABCDEFGHIJ D IJ So, AB is crucial ofR. In the given relation, R has a compound primary key( A, B). Thenon-prime trait are( C, D, E, F, G, H, I, J). In this case, FDs are AB C, A DE, B F which is only part of the primary key. thus, this table doesn’t satisfy 2NF. To bring this table to 2NF, we break the table into three relation as R1( A, B, C), R2( A, D, E, I, J) and R3( B, F, G, H). Que3.10. Prove that BCNF is stricter than 3NF. OR Prove that BCNF is stronger than 3NF. Answer 1. A relation, R, is in 3NF iff for every reliance X A satisfied by R at least one of the ensuing conditions X A is trivial( i.e., A is subset of X) X is a superkey for R, or c. A is a crucial trait forR. BCNF doesn’t permit the third of these options. 2. BCNF identifies some of the anomalies that aren’t addressed by 3NF. 3. A relation in BCNF is also in 3NF butvice-versa isn’t true. Hence, BCNF is more strict/ stronger than 3NF. Que3.11. Write the difference between 3NF and BCNF. Find the normal form of relation R( A, B, C, D, E) having FD set F = { A B, BC E, ED A}. AKTU 2018- 19, Marks 07 Answer Difference relateQ.3.9, Page 3 – 10A, Unit- 3. Numerical Given R( A, B, C, D, E) and F = A B BC E ED A ACD) = ACDB A B = ABCDE BC E = ABCDE ED A So, ACD is a key ofR. Relation R is in 1NF as all disciplines are simple i.e., all rudiments are infinitesimal. Que3.12. Explain addition dependences . Answer 1. An addition relianceR. Xset of attributes X of relation schema R, and Y of relation schema S specifies the constraint that, at any specific time when r is a relation state of R and s a relation state of S, we must have X( r( R)) Y( s( S)) 2. The set of attributes on which the addition reliance is specified X of R and Y of S must have the same number of attributes. Also disciplines for each brace of corresponding attributes should be compatible. 3. Addition dependences are defined in order to formalize two types of interrelational constraints a. The foreign key( or referential integrity) constraint can not be specified as a functional or multivalued reliance because it relates attributes across relations. b. The constraint between two relations that represent a class/ class relationship also has no formal description in terms of the functional, multivalued, and join dependences . 4. For illustration, if X = { A1, A2,…, An} and Y = { B1, B2,…, Bn}, one possible correspondence is to have dom( Ai) compatible with dom( Bi) for 1 in. In this case, we say that Ai corresponds to Bi.
