Solution

The correct answer is: Option 2 Key Points

• Option 1: The statement "A relation with only two attributes is always in BCNF" is incorrect. While relations with only two attributes are often in BCNF, this is only true if there are no partial dependencies or anomalies in the functional dependencies. Therefore, this cannot be concluded universally.
• Option 2: The statement "If all attributes of a relation are prime attributes then the relation is in BCNF" is correct. A relation is in BCNF if, for every non-trivial functional dependency (X → Y), X is a superkey. If all attributes are prime attributes (i.e., part of at least one candidate key), it ensures that the relation satisfies the BCNF condition.
• Option 3: The statement "Every relation has at least one non-prime attribute" is incorrect. There are relations where all attributes are prime attributes (e.g., a relation where all attributes are part of candidate keys).
• Option 4: The statement "BCNF decomposition preserves functional dependencies" is incorrect. BCNF decomposition does not always preserve functional dependencies. While it eliminates redundancy, some functional dependencies may be lost in the decomposition process.

Additional Information

• Prime Attribute: A prime attribute is an attribute that is part of at least one candidate key of the relation.
• BCNF: Boyce-Codd Normal Form (BCNF) is stricter than 3NF. It ensures that there are no partial dependencies or transitive dependencies, which reduces redundancy.
• Functional Dependency: A functional dependency exists when one attribute uniquely determines another attribute (e.g., A → B).

Hence, the correct answer is: Option 2