*Third Normal Form*

Sometimes, one encounters a relation schema and its FD's that are not in BCNF but that one doesn't want to decompose further. The following example is typical.**Example :** Suppose we have a relation Bookings with attributes:

1. title, the name of a movie.

2. theater, the name of a theater where the movie is being shown.

3. city, the city where the theater is located.

The intent behind a tuple (m, t, c) is that the movie with title m is currently being shown at theater t in city c.

We might sensibly affirm the following FD's:

**theater city**

**title city theater**

The first says that a theater is located in one city. The second is not clear but is based on the assumed practice of not booking a movie into two theaters in the same city. We shall affirm this FD if only for the sake of the example.

Let us first find the keys. No single attribute is a key. For instance, title is not a key because a movie can play in various theaters at once and in various cities at once. Also, theater is not a key, because though theater functionally determines city, there are multiscreen theaters that show various movies at once. Therefore, theater does not determine title. Finally, city is not a key because cities generally have more than one theater and more than one movie playing.

On the other hand, two of the three sets of two attributes are keys. Obviously {title, city} is a key because of the given FD that says these attributes functionally determine theater.

It is also true that {theater, title} is a key. To see why, start with the given FD theater → city. By the augmentation rule, theater title → city follows. Apparently, if theater alone functionally determines city, then surely theatre and title together will do so.

The remaining pair of attributes, city and theater, do not functionally determine title, and are therefore not a key. We resolve that the only two keys are

**{title, city}**

**{theater, title}**

Now we right away see a BCNF violation. We were given functional dependency theater → city, but its left side, theater, is not a superkey. We are therefore enticed to decompose, using this BCNF-violating FD, into the two relation schemas:

**{theater, city}**

**{theater, title}**

There is a problem with this decomposition, concerning the FD

**title city theater**

There could be current relations for the decomposed schemas that satisfy the FD theater → city (which can be checked in the relation {theater, city} ) but that, when joined, yield a relation not satisfying title city → theater. For example, the two relations

and

are allowable according to the FDs that apply to each of the above relations, but when we join them we get two tuples

that violate the FD title city → theater.

The solution to the above problem is to relax our BCNF requirement slightly, in order to allow the infrequent relation schema, like that of above example, which cannot be decomposed into BCNF relations without our losing the ability to check each FD within one relation. This relaxed condition is called the third normal form condition:

● A relation R is in third normal form (3NF) if : whenever A

_{1}A

_{2}. . . A

_{n}→ B is a nontrivial FD, either {A

_{1}, A

_{2}, . . . ,A

_{n}} is a superkey, or B is a member of some key.

An attribute that is a member of some key is sometimes said to be prime. Therefore, the 3NF condition can be stated as "for each nontrivial FD, either the left side is a superkey, or the right side is prime".

Note that the difference between this 3NF condition and the BCNF condition is the clause "or B is a member of some key (i.e., prime)". This clause "excuses" a FD like theater → city in above example, because the right side, city, is prime.

It is outside the scope of this blog to prove that 3NF is actually enough for its purposes. That is, we can always decompose a relation schema in a way that does not lose information, into schemas that are in 3NF and allow all FDs to be checked. When these relations are not in BCNF, there will be some redundancy left in the schema, however.

### Tags

- schema
- attributes
- functional dependency
- Instead-Of Triggers
- 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
- Disambiguating Attributes
- Queries Involving More Than One Relation
- Null Values and Comparisons Involving NULL
- Selection in SQL
- Projection in SQL
- Additional Constraint Examples
- Extending the Projection Operator
- Extended Operators of Relational Algebra
- 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
- Natural Joins / Theta-Joins
- Selection / Cartesian Product
- Set Operations on Relations
- An Algebra of Relational Operations
- Relational Algebra
- Attribute Lists
- Document Type Definitions
- XML and Its Data Model
- Semistructured Data Representation
- Object-Oriented Versus Object-Relational
- References
- Nested Relations
- 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
- Relationships Among Normal Forms
- Decomposition into Fourth Normal Form
- Reasoning About Multivalued Dependencies
- Definition of Multivalued Dependencies
- Multivalued Dependencies
- Boyce-Codd Normal Form
- 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
- Functional Dependencies
- 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
- 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
- Attributes on Relationships
- Elements of the E/R Model
- The Entity-Relationship Data Model
- Relational Database Systems