Here, we are checking whether 3NF is satisfied or not. How I think of it is :
PK – primary key , FK – foreign key
R12 can’t be merge with E1 or E2 because… then we will be forming a composite key as say, only a11 won’t be able to identify each of the attributes values of the newly formed relational table which consist of attributes (a11, a21, a12, a22), as a11 can uniquely identify only a12, so a21 also have to be PK, so they combine to be a composite key.
Now, why didn’t we merge them?
Because as said earlier then a12 can be uniquely identified by a11 i.e. which is:
a11 -> a12, but a11 and a21 both combined were PK, so it gave rise to Partial Dependency. So, 2NF will be violated, hence 3NF would have been violated too. Yes, there might be the reason for being multivalued i.e. a row of an attribute may contain multiple values, hence violating 1NF .. but the one I mentioned earlier can be one of the reasons too, right?
Further, checking for why we combine R13 with E3?
It was done because here even after R13 was combined with E3, E3 still can uniquely identify each of its tuples using a31 which is PK of E3. And E1 has its own PK and as it’s 2-attributed hence, E1 satisfies all NF forms. Let’s come back to E3 again. Here, a31 will uniquely identify a32 and a11 and as we have only 1 PK in E3, so partial dependency can’t occur. We can also think that even an a11 of E3 can act as PK, but it can’t because a11 of E3 can contain redundant tuples as well as NULL, as it’s an FK.. so it may or may not be referencing E1 for particular tuples. Also, a32 may contain redundant values so it too can’t be PK. Hence, here in E3 only a31 will be uniquely identifying each of the tuples and on the LHS of an FD, only a31 should be present.
If everything makes sense or not, please let me know. And if I am getting something wrong then I am open to discussion. @adad20 @raja11sep