Question

Write the complete proof.

Consider the relational schemas given below and the respective sets of functional dependencies valid in the schemas For each one of the relational schemas, determine the highest normal form, which is valid for a schema. Justify your answer If a schema is not in BCNF, then decompose it into a minimum number of schemas so that each one of them is in BCNF. Justify your answers. Justification must include the derivations of minimal keys from the functional dependencies and testing the validity of all normal forms (2NF, 3NF, BCNF) against the relational schemas, minimal keys, and functional dependencies. B-A (2) R- (A, B, C, D) (3) R - (A, B, C, D) A-C B-D (4) R (A, B, C, D) A, BC A, BD A relational schemaR (A, B, C, D) Functional dependencies: A B, A- C,B-D Keys? If A B and A C then through union rule A BC If A- B and B- D then through transitivity rule A D If A BC and A D then through union rule A BCD f A-BCD is valid in R and it covers entire relational schema then its left hand side is a minimal key (A) Normal form? Not 3NF because a non prime attribute c is transitively dependent on primary key A 2NF because no nonprime attribute depends on a part of primary key Decomposition into BCNF? Let R (A1, ..., An) be a relational schema (a header of relational table) and let X, Y, Z be the nonempty subsets of (A1,An (i) If Y X then X → Y (reflexivity axiom) (ii) If X- Y then X, Z Y, Z (augmentation axiom) (i) If X → Y and Y → Z then X → Z (transitivity axiom) ioms (),(i), and (ii)

Answer 1

Steps in determining the highest normal form:

step 1: finding all the possible candidate keys of the relation

step 2: dividing all the attributes into prime and non-prime attributes.

step 3:checking for the 1st normal form and then 2nd normal form. If it fails in nth normal form, it is n-1 is the highest normal form.

(1)

RIA. B : C)

$A\rightarrow B$

$B\rightarrow A$

step1:

(AC)+A, B, C

           

(BC)" = A. B. C

so they are candidate keys.

step2: all are prime attributes

step3: The relation is in 1NF as given relational DBMS does not allow multiple values.

But, it is not in 2NF since A derives B being it is a candidate key.

So the highest normal is 1NF.

(2)

R(A, B, C, D

$A\rightarrow B$

$B\rightarrow C$

step1:

ABD)-A, B,C, D

so ABD is the candidate key.

step2: {A,B,D}   ------prime attributes

           {C}--------------non-prime

step3: It is in 1NF.

but not in 2NF since A derives B being it is a part of candidate key.

So the highest normal is 1NF.

(3)

R(A, B, C, D

$A\rightarrow B$

$A\rightarrow C$

$B\rightarrow C$

step1:

(AD)+ (A. B. C. D}

so AD is the candidate key.

step2: AD -----prime attribues

            BC--non-prime attributes

step3: It is in 1NF.

But not in 2NF. AD is a candidate key. Part of AD, A derives C.

So the highest normal is 1NF.

(4)

R (A, B, C, D)

$A,B\rightarrow C$

$A,B\rightarrow D$

$C\rightarrow D$

step1: $\left ( AB \right )^{+}=\left \{ A,B,C,D \right \}$

           so it is candidate key.

step2 :    AB------------prime attributes

               CD----------------non-prime attributes.

step3: It is in 1NF.

It is in 2NF.

But not in 3NF because there is transitive depandency.

If the relation R needs to be in BCNF it needs to satisfy the following

If   $X\rightarrow Y$ is a relation

then X is a super key or Y is a prime attribute.

If there exists partial dependancy it is not in 2NF.

$ZX\rightarrow S$    and ZX is a candidate key then if

$X\rightarrow Y$

it is not in 2NF.

We have to separate the tables

$X\rightarrow Y$

$Y\rightarrow Z$

If there is a relation like this it is in transitive depandacy.

It is not in 3NF.

Then we have to separate them.

A →B BA Acy Bc are candidate keys cpartiol D ependlancy Be ICK) pasbal pepaendlam RCABC) A B BA BC

BC ,3-Candidate key hansihve P (ABCD AB AB BC ABP A->B

R(A,B,c,D) depe deney C. → D Transitive CD AB CD ⑤ RCA,B,C,D) A-> B aTansihve deandant p//ABc