Question

Given a 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: Functional Dependencies {AB → C, A → DE, B → F, E→D, F → GH, D → IJ} Dependency Diagram 1. What is the key in FIRST? (2 marks) 2. 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 shown above. (4 marks) 3. Normalize the result in 2 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.

Answer 1

The key in the relation FIRST is {A, B}. To normalize FIRST into second normal form (2NF), create relations SECOND, THIRD, and FOURTH. To further normalize into third normal form (3NF), create relations FIFTH, SIXTH, SEVENTH, and EIGHTH.

Step-by-step explanation:

To find the key in the relation FIRST, we need to determine the attribute(s) that uniquely identify each tuple. In this case, the key is the set of attributes that can determine all other attributes in the relation. Looking at the given functional dependencies, we find that the key is {A, B} since both A and B are not functionally dependent on any other attribute. So, the key in FIRST is {A, B}.

To normalize FIRST into second normal form (2NF), we need to eliminate partial dependencies. Step 1: Create a new relation called SECOND with attributes {A, B, C}. Here, {A, B} is the primary key for SECOND. Step 2: Create another new relation called THIRD with attributes {A, D, E} and a foreign key pointing to the primary key of SECOND. And finally, create a new relation called FOURTH with attributes {B, F, G, H} and a foreign key pointing to the primary key of SECOND.

To further normalize the result into third normal form (3NF), we need to eliminate transitive dependencies. Step 1: Create a new relation called FIFTH with attributes {A, D} and a foreign key pointing to the primary key of SECOND. Step 2: Create another new relation called SIXTH with attributes {A, E} and a foreign key pointing to the primary key of SECOND. Step 3: Create a new relation called SEVENTH with attributes {F, G, H} and a foreign key pointing to the primary key of FOURTH. And finally, create a new relation called EIGHTH with attributes {D, I, J} and a foreign key pointing to the primary key of FIFTH.