CSci 6441.BA2: Database Management Systems

Department of Computer Science
The George Washington University, Spring 2011
CRN 15165

Normalization Assignment Solutions

---

1. The only functional dependencies for the relation Order have Order# as the left-hand side. Since all non-trivial functional dependencies are of the form X → A where X is a superkey, the relation is in BCNF. On a separate note, since Total_amount can be calculated from other fields, we possibly would remove it from the initial version of the schema.

   Order_Item is not in BCNF because there are several functional dependencies that do not have a superkey on the left-hand side. One example is Item# → Discount%. The following decomposes Order_Item into relations satisfying BCNF:

   Items(Item#, Price_each, Discount%)
   Order_item(Order#, Item#, Quantity_ordered)
   

   Total_price and Total_amount can be calculated from the other fields. As an optimization we might include them in the production implementation.

2. I assume that Diagnosis ← Treatment_code does not hold. In other words, for a given diagnosis there is more than one possible treatment. I am also making the simplifying assumption that a patient does not receive multiple diagnoses for a given visit (otherwise we would need to change the key). With these assumptions, there is the non-trivial FD Treatment_code → Charge and this violates BCNF. So we should decompose as follows:

   Rx(Doctor#, Patient#, Date, Diagnosis, Treatment_code)
   Treatments(Treatment_code, Charge)
   

3. For the first set of functional dependencies, { A, B }+ = { A, B, C, D, E, F, G, H, I, J }. Therefore, { A, B } is the key. In fact, this is the only key (we can verify this by computing the closure of each combination of attributes). Getting to 2NF is simplified by the fact that we only have one key. We can calculate the closure of each of the components of the key: { A }+ = { A, D, E, I, J } and { B }+ = { B, F, G, H } and this yields the following decomposition:

   R1(A, B, C)
   R2(A, D, E, I, J)
   R3(B, F, G, H)
   

   Now to get to 3NF we must remove transitive dependencies. This yields the following decomposition:

   R1(A, B, C)
   R21(A, D, E)
   R22(D, I, J)
   R31(B, F)
   R32(F, G, H)
   

   Analyzing the second set of functional dependencies gives { A, B, D } as the sole key. Looking at the partial dependencies gets us to 2NF:

   R1(A, B, C)
   R2(B, D, E, F)
   R3(A, D, G, H, J)
   R4(A, B, A)
   R5(A, I)
   

   Now R3 is the only relation with a transitive dependency so fixing this, gives us following the 3NF decomposition:

   R1(A, B, C)
   R2(B, D, E, F)
   R31(A, D, G, H)
   R32(H, J)
   R4(A, B, A)
   R5(A, I)
   

4. The given statements can be written as FDs as follows:

   1. { Serial_number, Manufacturer } → { Model, Batch, Capacity, Retailer }
   2. Model → Manufacturer
   3. { Manufacturer, Batch } → Model
      I am assuming two manufacturers can use the same numbering scheme for batches.
   4. { Manufacturer, Model } → Capacity
      By part b, we can simplify this to Model → Capacity

   Given the FDs above, we can decompose Disk_drive as follows to get 3NF:

   Disk_drive(Manufacturer, Serial_number, Batch, Retailer)
   Batches(Manufacturer, Batch, Model)
   Models(Model, Capacity)
   

---

mmburke@gwu.edu

Modified: Sun Mar 27 22:06:46 EDT 2011