Class Exercise: Normalizing to Minimize Anomalies 

         
Objective: to ensure relations(i.e. tables) are "normal" in that each attribute
  has a unique name, values are atomic and from the same domain, each instance
  (row) is uniquely identified, and column/row order is immaterial. Further, 
  each nonKey attribute is dependent on the key and there are no transitive
  dependencies. 
  
1. Examine the following relation, constraints, and example table:

    FitnessRecreationalActivity (sId, cTitle, fee)
    Constraints:  
       sId -> cTitle, fee (A student is limited to one FRA course);
       cTitle -> fee (Fee does not vary by student)

       Ex.  sId  cTitle    fee
            100  Golf       10
            150  Swimming   30
            200  Weights    20
            250  Swimming   30

       Identify the update anomalies:
       a.  Deletion:
       b.  Insertion:
       c.  Modification:

2. Normalize the FitnessRecreationalActivity relation to eliminate the 
   modification anomalies.




3. If students may enroll for more than one FRA course, there may be a 
   relation, constraints, and an example table like this:

    FRA (SId, cTitle, Fee)
    Constraints:  A student may enroll for multiple activities;
                  Fee charges do not vary by student

    Ex. SId cTitle      Fee
        100 Golf         40
        100 Aerobics     10
        150 Swimming     30
        200 Swimming     30
        200 Weights      20
        250 Swimming     30
        250 Golf         40

    a.  Identify the update anomalies:
        1)  Deletion:
        2)  Insertion:
        3)  Modification:

    b.  Explain how the relation has a dependency that involves only part of 
        the key.


    c.  Transform the FRA relation so that all non-key attributes are 
        dependent on the whole key (2NF).
        
*For additional background see text chapter 10: "How to Design a Database".