Help

Using the EER diagram, we must apply the conversion rules to create tables for a database. First, we look for any mandatory 1:1 relationships as these will be collapsed into one table. In Kim’s Flowers, there are no such relationships. Next, we create a table for each entity. As such we come up with the following:

* FloralInv, CashAccounts, PurchaseOrders, FloralReceipts, Payments, FloralDisposal, PurchaseAgent, Kim, Vendor, FloralDesigner, APClerk

Next, we must determine the relationships based on the EER diagram. All optional relationships are treated as mandatory for the purposes of database modeling. These are as follows:

* 1:M between FloralInv and PurchaseOrders

* 1:M between FloralInv and FloralDisposal

* 1:M between CashAccounts and Payment

* 1:M between PurchaseAgent and PurchaseOrders

* 1:M between Kim and PurchaseOrders

* 1:M between Kim and FloralDisposal

* 1:M between Kim and Payment

* 1:M between Vendor and PurchaseOrders

* 1:M between Vendor and FloralReceipt

* 1:M between Vendor and Payment

* 1:M between FloralDesigner and FloralReceipt

* 1:M between APClerk and Payment

* M:M between PurchaseOrders and FloralReceipt

* M:M between FloralReceipt and Payment

The 1:M relationships will be such that the primary key on the one side will be the foreign key of the other, as seen in the relationships table in Access (attached on page 2 for convenience). The M:M relationships require a new composite table. So, based on this, we come up with the following tables (note underlined indicates primary key and asterisk denotes foreign key):

* FloralInv (ItemNo, Description, QOH, ListPrice)

* CashAccounts (AccountNo, Bank, Type, DateStarted, Balance)

* PurchaseOrders (PONo, Date, ItemNo*, PurchaseAgent#*, Kim*, VendorNO*)

* PurchaseOrders – FloralReceipts (PONo*, ReceiptNo*)

* FloralReceipt (ReceiptNo, Date, ItemNo*, VendorNO*, Designer#*)

* FloralReceipt – Payment (ReceiptNo*,...