The Relational Model and Normalization

• Consider the ORDER_ITEM and SKU_DATA tables from Cape Codd.

  +--------+----------------------------+--------------+--------------+
  | SKU    | SKU_Description            | Department   | Buyer        |
  +--------+----------------------------+--------------+--------------+
  | 100100 | Std. Scuba Tank, Yellow    | Water Sports | Pete Hansen  |
  | 100200 | Std. Scuba Tank, Magenta   | Water Sports | Pete Hansen  |
  | 101100 | Dive Mask, Small Clear     | Water Sports | Nancy Meyers |
  | 101200 | Dive Mask, Med Clear       | Water Sports | Nancy Meyers |
  | 201000 | Half-dome Tent             | Camping      | Cindy Lo     |
  | 202000 | Half-dome Tent Vestibule   | Camping      | Cindy Lo     |
  | 301000 | Light Fly Climbing Harness | Climbing     | Jerry Martin |
  | 302000 | Locking Carabiner, Oval    | Climbing     | Jerry Martin |
  +--------+----------------------------+--------------+--------------+
  
  +-------------+--------+----------+--------+---------------+
  | OrderNumber | SKU    | Quantity | Price  | ExtendedPrice |
  +-------------+--------+----------+--------+---------------+
  |        1000 | 201000 |        1 | 300.00 |        300.00 |
  |        1000 | 202000 |        1 | 130.00 |        130.00 |
  |        2000 | 101100 |        4 |  50.00 |        200.00 |
  |        2000 | 101200 |        2 |  50.00 |        100.00 |
  |        3000 | 100200 |        1 | 300.00 |        300.00 |
  |        3000 | 101100 |        2 |  50.00 |        100.00 |
  |        3000 | 101200 |        1 |  50.00 |         50.00 |
  +-------------+--------+----------+--------+---------------+
  
      

• We could merge these into a single table.

  +-------------+--------+----------+--------+---------------+--------+--------------------------+--------------+--------------+
  | OrderNumber | SKU    | Quantity | Price  | ExtendedPrice | SKU    | SKU_Description          | Department   | Buyer        |
  +-------------+--------+----------+--------+---------------+--------+--------------------------+--------------+--------------+
  |        1000 | 201000 |        1 | 300.00 |        300.00 | 201000 | Half-dome Tent           | Camping      | Cindy Lo     |
  |        1000 | 202000 |        1 | 130.00 |        130.00 | 202000 | Half-dome Tent Vestibule | Camping      | Cindy Lo     |
  |        2000 | 101100 |        4 |  50.00 |        200.00 | 101100 | Dive Mask, Small Clear   | Water Sports | Nancy Meyers |
  |        2000 | 101200 |        2 |  50.00 |        100.00 | 101200 | Dive Mask, Med Clear     | Water Sports | Nancy Meyers |
  |        3000 | 100200 |        1 | 300.00 |        300.00 | 100200 | Std. Scuba Tank, Magenta | Water Sports | Pete Hansen  |
  |        3000 | 101100 |        2 |  50.00 |        100.00 | 101100 | Dive Mask, Small Clear   | Water Sports | Nancy Meyers |
  |        3000 | 101200 |        1 |  50.00 |         50.00 | 101200 | Dive Mask, Med Clear     | Water Sports | Nancy Meyers |
  +-------------+--------+----------+--------+---------------+--------+--------------------------+--------------+--------------+
  
      

  • Queries on the second table are much more simple.
  • But the second table will require more space as orders increase.
  • And, does this table include all the information of the first two?
  • Are there problems if the data is changed somehow?
• Consider the table in figure 3-2.
  • Notice that Nancy Meyers has two Majors.
  • Does this have anything to do with the SKU?
  • What if we insert a new SKU for Nancy, which major should we choose? Or do we insert two new rows?
  • This is a multivalued dependency and it causes problems.