## CSci 6441.BA2: Database Management Systems

Department of Computer Science

The George Washington University, Spring 2011

CRN 15165## Normalization Assignment Solutions

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.- I assume that
`Diagnosis ← Treatment_code`

doesnothold. 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)For the first set of functional dependencies,

`{ A, B }`

. Therefore,^{+}= { A, B, C, D, E, F, G, H, I, J }`{ 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 }`

and^{+}= { A, D, E, I, J }`{ B }`

and this yields the following decomposition:^{+}= { B, F, G, H }

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)The given statements can be written as FDs as follows:

`{ Serial_number, Manufacturer } → { Model, Batch, Capacity, Retailer }`

`Model → Manufacturer`

`{ Manufacturer, Batch } → Model`

I am assuming two manufacturers can use the same numbering scheme for batches.`{ 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