CSci 241.AL: Database Management Systems

Department of Computer Science
The George Washington University, Fall 2010
CRN 73415

Relational Schema Assignment

You should complete the following exercises and submit them by 22 October 2010.

  1. Exercise 3.3.1, part f, pg. 93.
  2. Exercise 3.3.4, pg. 93.
  3. Exercises 1 and 2 described below.

Additional Exercises

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

Details

Submit your answers as plain text files via your github repository. Your github repository's URL is

https://github.com/gwu-f10-241-al/<netid>

where <netid> is your GWU net ID. You should test adding a file to your git repository (and pushing the changes to github) in advance of the due date for the assignment.

mmburke@gwu.edu
Modified: Wed Oct 13 22:06:46 EDT 2010