Given a database consisting of one first normal form relation FIRST with attribu

Question

Given a database consisting of one first normal form relation FIRST with attributes (A, B, C, D, E, F, G, H, I J and the functional dependencies as well as the dependency diagram as follows: FIRST ? A | B | C | D | E | F | G | H Functional Dependencies fAB C,A-DE, B-F, E-D,F- GH, D-IJ Dependency Diagram HA? What is the key for FIRST? (2 marks) Normalize FIRST into second normal form by giving the relation names, their attributes with primary keys underscored and foreign keys pointing to the corresponding attributes properly and its dependency diagrams as showing above (4 marks) Normalize the result in the second question into the third normal form by giving the relation names, their attributes with primary keys underscored and foreign keys pointing to the corresponding attributes properly and its dependency diagrams as showing above (4 marks). 1. 2. 3. the sswing above (4

Explanation / Answer

ANSWER:

THE BELOW DATA ILLUSTRATES WITH RESPECT TO THE GIVEN DATA;

a)

here,The key for these functional dependencies is AB. That is the only key existing. Here is the closure of key AB deriving all the columns of the database.

{AB}+ -> ABC now A gives DE ->ABCDE -> B gives F -> ABCDEF -> F give GH-> ABCDEFGH-> D gives IJ -> ABCDEFGHIJ.

b)

THEN,FOR   the dependencies to be in the second normal form:

rule 1: it should be in the 1st normal form

rule 2: all the non-prime attributes should be fully functionally dependent on any key.

so our key is AB and non-prime attributes are {D,E,F,G,H,I,J} now D and E are dependent on A which is a proper subset of key AB this production violates the rule 2 and from the dependency B->F we can see that the B alone can derive F. This dependency also violates rule 2 other relations are fine. now we will divide the dependencies into two relations R1:{AB->C,E->D,F->GH,D->IJ} , R2:{A->DE} , and R3{B->F}

c)

thus For the dependencies to be in the Third normal form:

rule 1: It should be in the 2nd normal form

rule 2: and no non-prime attribute is transitively dependent on candidate key or there should not be a case that a non-prime attribute is determined by another non-prime attribute

we will first see relation 2 :

A will be the primary key and D and C are the non-prime attributes and there is no production like D->C so the relation is in third normal form same applies to the relation 3 it is also in 3NF. The primary key is B and non-prime attribute will be F now in relation 1, we can directly see the dependencies

R1:{AB->C,E->D,F->GH,D->IJ} E->D and F->GH and D->IJ we will further divide them into relations

R1:{AB->C},R2:{A->DE} , R3{B->F}, R4{E->D}, R5{F->GH}, R6{D->IJ}

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.