Background

Good Fortune Restaurant Supplies (GFRS) is owned by Anna Vuong, a graduate of UWS. GFRS has grown rapidly and has found that managing its sales function is essential to providing excellent customer service and operating profitably. GFRS has asked you to design a database that will help it control its sale and cash collection operation. GFRS sells ingredients and supplies to several different restaurants, food and drink outlets, and a processed food factory. Donna Hay is the account manager who looks after all sale-related activities. Paul Murray is the creditor manager who is responsible for assessing and approving customer credit applications. Anna Vuong is the accounts receivable manager who is responsible for cash collection and accounting function. All orders from customers are processed by the account officer/manager, and approved by the credit officer/manager. The cash payments are received by the accounts receivable officer/manager. Sale orders from customers are confirmed with a printed customer purchase order signed by the customers purchasing agent. Normally, GFRS delivers shortly after orders are received and approved. A partial delivery of a sale order to a customer is acceptable.

When a delivery/shipment to a customer is made, an invoice is prepared by one of the four account officers/manager and sent to the customer. A remittance advice is attached to the bottom of the invoice. Customers are required to tear off the remittance advice and sent it back with their payments. Anna has made an arrangement with each customer that a direct bank transfer is made by the customer per month to GFRSs bank account to cover all invoices issued for the previous month. This arrangement has reduced the chance of bad debt and internal control issues in relation to cash collection.

The business stores its sale and cash collection transactions on a worksheet. Anna claims that she has done her best to make sure the data on the worksheet is correct.

There are fourteen (14) worksheets in the workbook. The Unnormalised Table worksheet contains all the necessary data for you to copy to other worksheets. You should not change anything in the Unnormalised Table worksheet. The definitions of all attributes in the Unnormalised Table are listed in the following data dictionary. You must not change the name of any attribute in the Unnormalised Table worksheet when you copy data to a different worksheet to create a new table. Do not copy the entire Unnormalised Table worksheet to other worksheets. Only copy the columns required to other worksheets.

There are three un-named worksheets (New Table 1, 2, & 3) given in the workbook. If you think a separate table is required, you need to change the name of one of the unnamed worksheet to the desired name. For example, if there is a many-to-many relationship between Student and Subject, the extra table required should be named as Student-Subject or Subject-Student.

The first column of any worksheet (a table) should be reserved for the primary key of the table. If there is a composite key involved, it should occupy the first two columns (for a two-attributes composite key) or the first three columns (for a three-attributes composite key) and so on.

Add the appropriate foreign key(s) to a table (worksheet) when it is needed.

Heres the raw datas name of each unnormalised table provided;

Saleorder#

SaleOrderDate

ApprovedByStaff#

StaffName

StaffPositionName

StaffPositionID

StaffContactPhone

StaffStartDate

Customer#

CustomerName

CustomerAddress

CustomerSuburb

CustomerState

CustomerPostcode

CustomerPhone

CustomerContact

Inventory#

InventoryDescription

InventoryListPrice

SaleOrderPrice

QuantityOrdered

Invoice#

ProcessedByStaff#

StaffName

StaffPositionName

StaffPositionID

StaffContactPhone

StaffStartDate

ShippingDate

InvoicePrice

QuantitySold

ReceivedByStaff#

StaffName

RemittanceAdvice#

StaffPositionName

StaffPositionID

StaffContactPhone

StaffStartDate

CashAccount#

CashReceiptDate

CashReceiptAmount

AmountDescription

BankName

DateAccountEstablished

staff#

Normalised the above data into into 10 tables s in third normal form (3NF) including the below tables :

Note - You need to put the primary key or the composite keys in the first and subsequent columns and follow by any foreign keys.

PLEASE ONLY USE THE BELOW TABLES, PLEASE DONT USE DIFFERENT TABLES. ONLY THE GIVE EXTRA TABLES NEW NAMES. ONLY USE THE BELOW!! last answer given used irrelevent tables.

1: SaleOrder

2: Inventory

3: Employee

4: Customer

5: Sale

6: CashReceipt

7: CashAccount

8: EmployeePosition

9: extra table 1 (Please add your own relevant table)

10: extra table 2 (Please add your own relevant table)

11: extra table 3 (Please add your own relevant table)