invoices
Suppose you are tasked with designing a relational database for storing invoices like the following:
+---------------------------------------------------------------------------+
| INVOICE Dried Fruit of Vermont |
| |
| Invoice # 282 |
| Date October 1, 2012 |
| Customer # 17 |
| Address Food, Inc. |
| 23 Main St. |
| Bobston, VT |
|------------+-----------------+--------------------+------------+----------|
| Quantity | Item Id | Description | Unit Price | Subtotal |
|------------+-----------------+--------------------+------------+----------|
| 5 | 19 | Dried Strawberries | $10.00 | $50.00 |
| 25 | 28 | Dried Apricots | $7.00 | $175.00 |
| 10 | 11 | Dried Apples | $5.00 | $50.00 |
|------------+-----------------+--------------------+------------+----------|
| Total | $275.00 |
+---------------------------------------------------------------------------+
+---------------------------------------------------------------------------+
| INVOICE Dried Fruit of Vermont |
| |
| Invoice # 283 |
| Date October 2, 2012 |
| Customer # 24 |
| Address Dave's Grocery |
| 12 Maple Rd. |
| Stevesville, VT |
|------------+-----------------+--------------------+------------+----------|
| Quantity | Item Id | Description | Unit Price | Subtotal |
|------------+-----------------+--------------------+------------+----------|
| 100 | 19 | Dried Strawberries | $10.00 | $1000.00 |
| 100 | 22 | Dried Figs | $7.50 | $750.00 |
|------------+-----------------+--------------------+------------+----------|
| Total | $1750.00 |
+---------------------------------------------------------------------------+
+---------------------------------------------------------------------------+
| INVOICE Dried Fruit of Vermont |
| |
| Invoice # 284 |
| Date October 2, 2012 |
| Customer # 17 |
| Address Food, Inc. |
| 23 Main St. |
| Bobston, VT |
|------------+-----------------+--------------------+------------+----------|
| Quantity | Item Id | Description | Unit Price | Subtotal |
|------------+-----------------+--------------------+------------+----------|
| 20 | 19 | Dried Strawberries | $10.00 | $200.00 |
| 15 | 11 | Dried Apples | $5.00 | $75.00 |
|------------+-----------------+--------------------+------------+----------|
| Total | $275.00 |
+---------------------------------------------------------------------------+
Your database needs to store all of the information from an arbitrary set of such invoices with as little duplication of information as possible. You need to be able to ask the database questions like:
- How many Apples did Food Inc order this month?
- How many Strawberries were ordered on October 2nd?
- How many Apples were sent to Stevesville on October 1st?
