*Boyce-Codd Normal Form*

The objective of decomposition is to replace a relation by several that do not exhibit anomalies. There is, it turns out, a simple condition under which the anomalies discussed above can be guaranteed not to exist. This condition is called Boyce-Codd normal form, or BCNF.

● A relation R is in BCNF if and only if: whenever there is a nontrivial FD A_{1}A_{2} . . . A_{n} → B for R, it is the case that {A_{1}, A_{2}, . . . , A_{n}} is a superkey for R

That is, the left side of every nontrivial FD must be a superkey. Remember that a superkey need not be minimal. Thus, a corresponding statement of the BCNF condition is that the left side of every nontrivial FD must include a key.

When we find a BCNF-violating FD, we occasionally wind up with a simpler complete decomposition into BCNF relations if we enlarge the right side of the FD to include the right sides of all the other FDs that have the same left side, whether or not they are BCNF violations. The following is an alternative definition of BCNF in which we look for a set of FDs with common left side, at least one of which is nontrivial and violates the BCNF condition.

● Relation R is in BCNF if an only if: whenever there is a nontrivial FD A_{1}A_{2} . . . A_{n} → B_{1}B_{2} . . . B_{m} for R, it is the case that {A_{1},A_{2}, . . . , A_{n}} is a superkey for R.

This requirement is equivalent to the original BCNF condition. Recall that the FD A_{1}A_{2} . . . A_{n} → B_{1}B_{2} . . . B_{m} is shorthand for the set of FDs A_{1}A_{2} . . . A_{n} → B_{i} for i = 1,2, . . . , m. Since there must be at least one Bi that is not among the As (or else A_{1}A_{2} . . . A_{n} → B_{1}B_{2} . . . B_{m} would be trivial), A_{1}A_{2} . . . A_{n} → B_{i} is a BCNF violation according to our original definition.**Example (a) :** Relation Movies, as in "Design of Relational Database Schemas / Anomalies" Figure 1, is not in BCNF. To see why, we first need to determine what sets of attributes are keys. We argued in "Keys of Relations" Example, why {title, year, starName} is a key. In this way, any set of attributes containing these three is a superkey. The same arguments we followed in "Keys of Relations" Example, can be used to explain why no set of attributes that does not include all three of title, year and starName could be a superkey. Therefore, we state that {title, year, starName} is the only key for Movies.

However, consider the FD

**title year **** length filmType studioName**

which holds in Movies according to our discussion in "Keys of Relations" Example.

Unluckily, the left side of the above FD is not a superkey. Particularly, we know that title and year do not functionally determine the sixth attribute, starName. Therefore, the existence of this FD violates the BCNF condition and tell us Movies is not in BCNF. Furthermore, according to the original definition of BCNF, where a single attribute on the right side was required, we can offer any of the three FDs, such as title year → length, as a BCNF violation.**Example (b) :** On the other hand, Movies1 of "Decomposing Relations" Figure 2, is in BCNF. Since** title year **** length filmType studioName**

holds in this relation, and we have argued that neither title nor year by itself functionally determines any of the other attributes, the only key for Movies1 is {title, year}. Moreover, the only nontrivial FDs must have at least title and year on the left side, and therefore their left sides must be superkeys. Thus, Movies1 is in BCNF.**Example (c) :** We claim that any two-attributes relation is in BCNF. We need to study the possible nontrivial FDs with a single attribute on the right. There are not too many cases to consider, so let us consider them in turn. In what follows, suppose that the attributes are A and B.

1. There are no nontrivial FDs. Then surely the BCNF condition must hold, because only a nontrivial FD can violate this condition. By the way, note that {A,B} is the only key in this case.

2. A → B holds, but B → A does not hold. In this case, A is the only key, and each nontrivial FD contains A on the left (in fact the left can only be A). Thus there is no violation of the BCNF condition.

3. B → A holds, but A → B does not hold. This case is symmetric to case (2).

4. Both A → B and B → A hold. Then both A and B are keys. Surely any FD has at least one of these on the left, so there can be no BCNF violation.

It is worth noticing from case (4) above that there may be more than one key for a relation. Moreover, the BCNF condition only requires that some key be contained in the left side of any nontrivial FD, not that all keys are contained in the left side. Also examine that a relation with two attributes, each functionally determining the other, is not completely implausible. For instance, a company may assign its employees unique employee IDs and also record their Social Security numbers. A relation with attributes empID and ssNo would have each attribute functionally determining the other. Put another way, each attribute is a key, since we dont expect to find two tuples that agree on either attribute.

### Tags

- attributes
- relational database
- tuples
- Scrolling Cursors
- Protecting Against Concurrent Updates
- Modifications by Cursor
- Cursors
- Instead-Of Triggers
- Triggers in SQL
- Schema-Level Constraints and Triggers
- Tuple-Based CHECK Constraints
- Constraints on Attributes and Tuples
- Declaring Foreign-Key Constraints
- Keys Declared With UNIQUE
- Constraints and Triggers
- Modifying Views
- View Definitions
- Introduction to Selection of Indexes
- Default Values / Indexes
- Simple Table Declarations
- Defining a Relation Schema in SQL
- Deletion / Updates
- Database Modifications
- Grouping / HAVING Clauses
- Full-Relation Operations
- Natural Joins / Outerjoins
- Subqueries in FROM Clauses
- Conditions Involving Tuples
- Subqueries
- Union, Intersection, and Difference of Queries
- Interpreting Multirelation Queries
- Tuple Variables
- Disambiguating Attributes
- Queries Involving More Than One Relation
- Null Values and Comparisons Involving NULL
- Selection in SQL
- Projection in SQL
- The Database Language SQL
- Additional Constraint Examples
- Extending the Projection Operator
- Grouping
- Extended Operators of Relational Algebra
- Selection on Bags / Product of Bags / Joins of Bags
- Union, Intersection, and Difference of Bags
- Relational Operations on Bags
- A Linear Notation for Algebraic Expressions
- Dependent and Independent Operations
- Renaming
- Combining Operations to Form Queries
- Selection / Cartesian Product
- Set Operations on Relations
- An Algebra of Relational Operations
- Relational Algebra
- Attribute Lists
- Information Integration Via Semistructured Data
- Semistructured Data Representation
- Object-Oriented Versus Object-Relational
- Nested Relations
- The Object-Relational Model
- What If There Is No Key
- Representing ODL Relationships
- Representing Other Type Constructors
- Representing Set-Valued Attributes
- Nonatomic Attributes in Classes
- Declaring Keys in ODL
- Subclasses in ODL / Multiple Inheritance in ODL
- Types in ODL
- Methods in ODL
- Multiplicity of Relationships
- Relationships in ODL / Inverse Relationships
- Attributes in ODL
- Introduction to ODL
- The Type System
- Decomposition into Fourth Normal Form
- Reasoning About Multivalued Dependencies
- Definition of Multivalued Dependencies
- Multivalued Dependencies
- Third Normal Form
- Decomposition into BCNF
- Decomposing Relations
- Projecting Functional Dependencies
- Closing Sets of Functional Dependencies
- The Transitive Rule
- Why the Closure Algorithm Works
- Computing the Closure of Attributes
- Trivial Functional Dependencies
- The Splitting/Combining Rule
- Rules About Functional Dependencies
- Keys of Relations
- Using Null Values to Combine Relations - Comparison of Approaches
- An Object-Oriented Approach
- Converting Subclass Structures to Relations
- Handling Weak Entity Sets
- Combining Relations
- From E/R Relationships to Relations
- From Entity Sets to Relations
- From E/R Diagrams to Relational Designs
- Relation Instances
- Equivalent Representations of a Relation
- Tuples / Domains
- Attributes / Schemas
- The Relational Data Model
- Summary of The Entity-Relationship Data Model
- Weak Entity Set Notation
- Requirements for Weak Entity Sets
- Representing Keys in the E/R Model
- Keys in the E/R Model
- The Modeling of Constraints
- Picking the Right Kind of Element
- Design Principles
- Subclasses in the E/R Model
- Converting Multiway Relationships to Binary
- Attributes on Relationships
- Multiway Relationships
- Elements of the E/R Model
- Database System Implementation
- Database Design
- Multimedia Data
- Relational Database Systems