Cambridge Limited is a large engineering firm that provides spare parts, lubricants and similar products to support heavy industry, and has provided you the following extract of their Entity Relationship Diagram.

You can assume that all sales orders received before 9AM are shipped that day. Customers are invoiced on the next day. Cambridge does not accept backorders.

In addition, Cambridge notes that most of their customers are large multi-divisional companies. When providing credit to these customers each division is given a credit limit, and the company has an overall credit limit (approximately 90% of the combined credit limit of the subsidiaries).

a) What is the purpose of the Warehouse Product entity? Explain with reference to the diagram both from a process perspective and from a data perspective. (2 marks)

b) Given the relationship between sales orders and invoices, what does this suggest about how Cambridge fulfils customer orders. Explain with reference to the diagram.

c)

Following the recent insolvency of one of its customers Cambridge has discovered that it had exceeded its credit limit and is asking for your help in determining how the check for available credit should occur for a division.

Required: Explain what entity or entities and attributes should be involved in a check for available credit of Cambridges multi divisional customers.

d)

Imagine it is the morning of July 1st, and the company wishes to prepare financial statements for the year ended June 30.

What entity or entities and attributes would be involved in the preparation of the BALANCE SHEET?

What account(s) on the statement would be affected? How would each of the identified entities and fields contribute to calculating (added, multiplied, subtracted, some records filtered out etc.) the values of these accounts on the statement.

Receives Triggered by H Sent to Receives (Relationship for division) Customer Hierarchy Customer Located within PK Hierarchy ID Established for Controls HPK Customer ID FK HQ Customer ** Company Name FK Division Customer ID** Address Reports to ( (Relationship for HQ) Country ** NOTE: HQ Customer ID and Division Customer ID both relate Postcode to Customer ID. Each Division is treated as a separate customer State Email address Credit Limit Billed for Invoice PK Invoice ID FK Order ID FK Picking ID FK Customer ID Invoice Date Total Amount Status Places Contains Placed by Billed by Appears on Invoice line Contains Appears on Sales order PK Order ID FK Customer ID Date placed Total amount Status Stores Warehouse Product PK Warehouse ID PK Product ID Quantity on hand Quantity committed Stored in Sales line PK Sales Line ID FK Order ID FK Product ID Quantity Line total PK Invoice Line ID FK Invoice ID FK Product ID Quantity Line total Triggers Contains Shows Shown in Stores Stored in Shown on Receives Contains Warehouse PK Warehouse ID Address Country State Postcode Phone number Email address Product PK Product ID Description Sales price Cost price Total qty on hand *NOTE: these attributes are totaled * automatically from Warehouse Total qty committed Product records RFID/Barcode # Picking line PK Picking line ID FK Picking ID FK Product ID Quantity Appears on Triggered by Sent to Shown in Contains Triggered by Sent to Triggers Picking list PK Picking ID FK Warehouse ID FK Order ID Picked date Status Triggers Shipping PK Picking ID FK Customer ID Shipping date Status Receives Triggered by H Sent to Receives (Relationship for division) Customer Hierarchy Customer Located within PK Hierarchy ID Established for Controls HPK Customer ID FK HQ Customer ** Company Name FK Division Customer ID** Address Reports to ( (Relationship for HQ) Country ** NOTE: HQ Customer ID and Division Customer ID both relate Postcode to Customer ID. Each Division is treated as a separate customer State Email address Credit Limit Billed for Invoice PK Invoice ID FK Order ID FK Picking ID FK Customer ID Invoice Date Total Amount Status Places Contains Placed by Billed by Appears on Invoice line Contains Appears on Sales order PK Order ID FK Customer ID Date placed Total amount Status Stores Warehouse Product PK Warehouse ID PK Product ID Quantity on hand Quantity committed Stored in Sales line PK Sales Line ID FK Order ID FK Product ID Quantity Line total PK Invoice Line ID FK Invoice ID FK Product ID Quantity Line total Triggers Contains Shows Shown in Stores Stored in Shown on Receives Contains Warehouse PK Warehouse ID Address Country State Postcode Phone number Email address Product PK Product ID Description Sales price Cost price Total qty on hand *NOTE: these attributes are totaled * automatically from Warehouse Total qty committed Product records RFID/Barcode # Picking line PK Picking line ID FK Picking ID FK Product ID Quantity Appears on Triggered by Sent to Shown in Contains Triggered by Sent to Triggers Picking list PK Picking ID FK Warehouse ID FK Order ID Picked date Status Triggers Shipping PK Picking ID FK Customer ID Shipping date Status