Question

0 5bour. C1 C2 C5 5.1 Identify and discuss the partial and transitive dependencies in the above dependency diagram. (2 points) 5.2 Create a database whose tables are in 2NF, showing the dependency diagram for each table. (2 points) 5.3 Create a database whose tables are in 3NF, showing the dependency diagranm for each table. (2 points) dnuary, 201 5) Consider the following dependency diagram and answer the questions that follow: C1 C2 C3 C4 C5 5.1 Identify and discuss the partial and transitive dependencies in the above dependency diagram. (2 points)

Answer 1

5.1

C3 С5 C1 C2 C4

Partial Dependency : A functional dependency X $\rightarrow$ Y is a partial dependency if some attribute A $\epsilon$ X can be removed and the dependency still holds.

In the given Dependency Diagram :

Combination of C4 and C5 is the candidate key and

C1 is partially dependent on C4.

Transitive dependency : A functional dependency X $\rightarrow$ Y in a relational schema , R, is a transitive dependency if there is a set of attributes Z that is not a subset of any key of R and both X$\rightarrow$ Z and Z $\rightarrow$ Y holds.

In the given Dependency Diagram :

Combination of C4 and C5 is the candidate key.,

C2 is dependent on the key (C4,C5) but C3 is dependent on C3

Hende C3 is transitively dependent on (C4 , C5)

5.2

A relational Schema, R is in 2NF if every non-prime attribute , A in R, is fully functionally dependent on the primary key of R. It removes partial dependency.

C1 is partially dependent on (C4, C5)

To convert the Schema to 2NF, we remove the partial dependency. We create another table with C1 and C4 where C4 is the primary key and make C4 a foreign key in this table.

Dependency diagram :

C2 C4 C5 C3 C4 C1

5.3

A Relational schema , R is in 3NF if it is in 2NF and no non-prime attribute of R is transitively dependent on the primary key of R.

A Relational schema , R, is in 3NF if whenever a functional dependency X$\rightarrow$A holds in R, either

(a) X is the super key of R or,

(b) A is a prime attribute of R

In the given Dependency Diagram, C3 is transitively dependent on (C4,C5). To remove the transitive dependency , we remove C2 and C3 from this table and create another table with C2 and C3 where C2 is the  primary key of the table and it forms foreign key in the main table.

Dependency Diagram:

C5 C4 C2 C2. C3 C4 C1