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