Database Design(Normal Forms)

BCNF is a stronger version 3NF. So every relation in BCNF will also be in 3NF.

    X → Y is a trivial functional dependency (Y ⊆ X)
    X is a superkey for schema R 

Book (Title, Author, Catalog_no, Publisher, Year, Price)
Collection (Title, Author, Catalog_no) 

I. Title, Author --> Catalog_no
II. Catalog_no --> Title, Author, Publisher, Year
III. Publisher, Title, Year --> Price  

Assume {Author, Title} is the key for both schemes 

• The table "Collection" is in BCNF as there is only one functional dependency “Title Author –> Catalog_no” and {Author, Title} is key for collection.
• Book is not in BCNF because Catalog_no is not a key and there is a functional dependency “Catalog_no –> Title Author Publisher Year”.
• Book is not in 3NF because non-prime attributes (Publisher Year) are transitively dependent on key [Title, Author].
• Book is in 2NF because every non-prime attribute of the table is either dependent on the whole of a candidate key [Title, Author], or on another non prime attribute. In table book, candidate keys are {Title, Author} and {Catalog_no}. In table Book, non-prime attributes (attributes that do not occur in any candidate key) are Publisher, Year and Prince

A) {EF}+ = {EFGIJ} ≠ R(The given relation)

B) {EFH}+ = {EFGHIJKLMN} = R (Correct since each member of the 
                                    given relation is determined)

C) {EFHKL}+ = {EFGHIJKLMN} = R (Not correct although each member 
                                of the given relation can be determined 
                                but it is not minimal, since by the definition
                                of Candidate key it should be minimal Super Key)

 D) {E}+ = {E} ≠ R

Since, {EFGHIJKLMN}+ =  {EFGHIJKLMN}

{EFGHIJKLM}+ =  {EFGHIJKLMN} ( Since L -> {N}, hence can replace N by L)

In a similar way K -> {M} hence replace M by K

{EFGHIJKL}+ =  {EFGHIJKLMN} 

Again {EFGHIJ}+ =  {EFGHIJKLMN} (Since  {E, H} -> {K, L}, hence replace KL by EH)

{EFGH}+ =  {EFGHIJKLMN} (Since {F} -> {I, J} )

{EFH}+ =  {EFGHIJKLMN} (Since {E, F} -> {G} )

 
S1: Every table with two single-valued 
      attributes is in 1NF, 2NF, 3NF and BCNF.

1. If {A->B} exists then BCNF since {A}+ = AB = R
2. If {B->A} exists then BCNF since {B}+ = AB = R
3. If {A->B,B->A} exists then BCNF since A and B both are Super Key now.
4. If {No non trivial Functional Dependency} then default BCNF.

S2: AB->C, D->E, E->C is a minimal cover for 
      the set of functional dependencies 
      AB->C, D->E, AB->E, E->C.