Question

tableA is a table in a relational database with a composite prime key consisting of fields F1 and F2. You have determined that tableA is 1NF. There are four other fields in tableA. Two of them, F10 and F12, are functionally determined by F1. The other two, F50 and F55, are functionally determined by F2. Because all fields are functionally determined by at least a portion of the key, is tableA 2NF?   If you believe the table is not yet 2NF, what would be required to make it 2NF? Explain your answer

Answer 1

TableA(F1,F2,F10,F12,F50,F55) has the primary key (F1,F2) and the functional dependencies are given as :-

F.D. = { F1→F10, F1 → F12, F2→F50, F2→F51}

For the given fuctional dependencies we have to check for the 2 conditions for the table to be in the 2NF :-

1.) Table should be in the 1NF that is it should be in the first normal form.(given that table is is 1NF due to Composite Primary Key F1,F2)

2.) The functional dependencies should not contain any partial dependency.

But the table contains Partial Dependency that is { F1→F10, F1 → F12, F2→F50, F2→F51} these are the partial dependency which states that proper subset of candidate key determines non-prime attribute and F1 and F2 are proper subsets of the candidate key and F10, F12, F50, F55 are non prime attributes.

Now, to convert the table into 2NF we have to decompose the table into two tables such that the tables should be :-

1.) Dependency Preserving.

2.) Lossless Join Decomposition.

as 2NF has these two properties stated above

The Table A is decomposed as:-

Table A1 (F1, F10, F12) has the Functional Dependency as {F1→F10, F1 → F12}

Table A2 (F2, F50, F55) has the Functional Dependency as {F2→F50, F2→F51}

Table A3 (F1,F2)

All the above tables do not have partial dependencies so they are in 2NF. All the tables have a common attribute so the Decomposition is lossless and we have all the functional dependencies so it is dependency preserving as well.

So, we have converted the Table A (F1,F2,F10,F12,F50,F55) into 2NF by Decomposing it into Table A1 (F1, F10, F12), Table A2 (F2, F50, F55), Table A3 (F1,F2)