CSci 241.AL: Database Management SystemsDepartment of Computer Science
The George Washington University, Fall 2010
Relational Schema Assignment
You should complete the following exercises and submit them by 22 October 2010.
- Exercise 3.3.1, part f, pg. 93.
- Exercise 3.3.4, pg. 93.
- Exercises 1 and 2 described below.
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_eachrefers to one item,
Total_priceis the cost per item multiplied by the number of items purchased,
Order_dateis the date on which the order was placed, and the
Total_amountis 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).
Submit your answers as plain text files via your github repository. Your github repository's URL is
<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.
firstname.lastname@example.org Modified: Wed Oct 13 22:06:46 EDT 2010