## CSci 6441.BA2: Database Management Systems

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

## Normalization Assignment

Clearly state any additional assumptions you use in solving the following exercises.

1. Consider the following relations for an order-prcessing application database for Acme Products, Wile E. Coyote, owner. Determine whether the relations are in BCNF and, if not, decompose them.

```Order(Order#, Order_date, Customer#, Total_amount) Order_Item(Order#, Item#, Quantity_ordered, Price_each, Total_price, Discount%) ```

Assume that each item has a different discount. The `Price_each` refers to one item, `Total_price` is the cost per item multiplied by the number of items purchased, `Order_date` is the date on which the order was placed, and the `Total_amount` is the amount of the order.

2. Determine whether the following relation is in BCNF and, if not, decompose it.

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

A tuple describes a visit of a patient to a doctor along with a treatment code and a charge. Assume that each treatment code has a fixed charge (regardless of patient).

3. Consider the relation `R(A, B, C, D, E, F, G, H, I, J)` and the set of functional dependencies `{ AB → C, A → DE, B → F, F → GH, D → IJ }`. What is the key for `R`? Decompose `R` into 2NF and then 3NF.

Repeat the decomposition into 2NF and then 3NF starting with the original version of `R` and using the following set of functional dependencies instead: `{ AB → C, BD → EF, AD → GH, A → I, H → J }`.

4. Consider the relation `DISK_DRIVE(Serial_number, Manufacturer, Model, Batch, Capacity, Retailer)`. Each tuple in the relation `DISK_DRIVE` contains information about a disk drive with a unique serial number, made by a manufacturer, with a particular model number, released in a certain batch, which has a certain storage capacity, and is sold by a certain retailer. For example, the tuple
`Disk_drive('1978619', 'Acme Drives', 'A2235X', '765324', 500, 'CompuMax')`
sepecifies that Acme Drives made a 500GB disk drive with serial number 1978619 and model number A2235X; it was released in batch 765324 and is sold by CompuMax.

Translate each of the following into a FD:

1. The manufacturer and serial number uniqely identifies the drive.
2. A model number is registered by a manufacturer and therfore can not be used by another manufacturer.
3. All disk drives in a particular batch are the same model.
4. All disk drives of a certain model of a particular manufacturer have exactly the same capacity.

After stating the above FDs, decompose `DISK_DRIVE` into 3NF.

This assignment is due Friday, 11 March 2011. You must submit this by adding it to your git repository and pushing the files to github.com.

mmburke@gwu.edu
Modified: Thu Feb 24 22:06:46 EDT 2011