Alternative Approaches to Database Design

1. We have taken the approach of starting with a single relation scheme and decomposing it.
   • One goal was lossless-join decomposition.
   • For that, we decided we needed to talk about the join of all relations on the decomposed database.
   • Figure 6.20 shows a borrow relation decomposed in PJNF, where the loan amount is not yet determined.
   • If we compute the natural join, we find that all tuples referring to loan number 58 disappear.
   • In other words, there is no borrow relation corresponding to the relations of figure 6.20.
   • We call the tuples that disappear when the join is computed dangling tuples.
   • Formally, if are a set of relations, a tuple of relation is a dangling tuple if is not in the relation

     

   • Dangling tuples may occur in practical applications.
   • They represent incomplete information.
   • The relation is called a universal relation since it involves all the attributes in the universe defined by .
   • The only way to write a universal relation for our example is include null values.
   • Because of the difficulty in managing null values, it may be desirable to view the decomposed relations as representing the database rather than the universal relation.
   • We still might need null values if we tried to enter a loan number without a customer name, branch name, or amount.
   • In this case, a particular decomposition defines a restricted form of incomplete information that is acceptable in our database.
2. The normal forms we have defined generate good database design from the point of view of representation of incomplete information.
   • We need a loan number to represent any information in our example.
   • We do not want to store data for which the key attributes are unknown.
   • The normal forms we have defined do not allow us to do this unless we use null values.
   • Thus our normal forms allow representation of acceptable incomplete information via dangling tuples while prohibiting the storage of undesirable incomplete information.
3. Another point in our method of design is that attribute names must be unique in the universal relation.
   • We call this the unique role assumption.
   • If we defined the relations

     

     expressions like branch-loan loan-customer are possible but meaningless.

   • In SQL, there is no natural join operation, and so references to names are disambiguated by prefixing relation names.
   • In this case, non-uniqueness might be both convenient and allowed.
   • The unique role assumption is generally preferable, and if it is not made, special care must be taken when constructing a normalized design.