dbms normalization - 2nf/bcnf query

Question

Consider the following relational schemes: R(A, B, C, D, E, F) and S(A, B, C) with in the following functional dependencies: I. AB --> C II. C --> ABDE III. ADE --> F Assume {A,B} is the key for both schemes. Which of the following statements is true?

R is in 2NF and S is in 3NF Both R and S are in 3NF only Both R and S are in 2NF only Both R and S are in BCNF   For relation R: Is it not 1NF. Since for it to be in 2NF it should not be having partial PA → NPA, which it is havin in ADE → F. Given that AB form the PA. (So R is in 1NF) For relation S: only AB→ C is the relation for the S table, which satisfies BCNF properties. (So S is in BCNF) Please let me know if it is correct or not.

Answer 1

Option 1 Is correct 

R is in 2NF and S is in 3NF

Because:

for the Relation R 

I. AB --> C

II. C --> ABDE

III. ADE --> F

out of these 3 FD's 2nd one is giving (Non-Key->Non-key) so it is not in 3NF , but there are no partial dependencies, you might be confused in 3rd FD(but it not  partial FD).

Regarding Relation S,

It is in BCNF means it is also in 3nf right.

So option 1 is more appropriate than 3rd because it is saying ONLY.

Comments

I have 3 independent questions...

1. given that AB is the key for both the schemas, can I say that AB are the prime attributes?

2. ADE-> F

A(prime attribute) DE(non-prime) -> F (non prime)

As we know, this to be in 2nf should not have (partial prime attr -> non prime attr), but accprding to the above line it is having, then how is it in 2nf?


3. ADE->F can I say that ADE is partial prime attr? (considering AB is the prime attribute othe non prime).

---

Yes, I have the same doubt...

Answer 2

Its simple:

here AB is candidate key.

for R it is not in BCNF AND 3NF for sure.

to check for 2NF, we are not able to find any partial dependency, hence according to the definition of 2NF, if there is no partial dependency then R is in 2NF.

Also for ADE->F, there is no partial dependency(means not any part of candidate key is pointing to non primary attribute). ADE will be treated as one single variable. EX- A->F is partial as A is part  of candidate key AB but not ADE->F as ADE  whole is not part of candidate key AB

Also S is in BCNF

to check AB->C , here AB is super key hence BCNF