## 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.

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.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).

- 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 }`

.- 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 tuplesepecifies 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.`Disk_drive('1978619', 'Acme Drives', 'A2235X', '765324', 500, 'CompuMax')`

Translate each of the following into a FD:

- The manufacturer and serial number uniqely identifies the drive.
- A model number is registered by a manufacturer and therfore can not be used by another manufacturer.
- All disk drives in a particular batch are the same model.
- 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