Question 1

(10 marks)

Read the scenario below and its corresponding UML class model and Tax Invoice, and answer the questions that follow.

The Caulfield Office Choice (COC) is a wholesale distributor of office supplies, such as stationery, furniture, ink, tonersand related items. Customers receive an updated catalogue annually by email and place orders by sending in a purchase order. COC's policy is to process customers' order immediately and to despatch their order (goods and tax invoice) on the same day. Below is a sample of COCs invoice. Each order has to be fully paid in one payment by the due date. COC deposits all payments received from customers into its bank account.

Served by:

102 Bruce Lee

Bill To:

The attributes of some of the tables from the resulting database are provided below. Indicate which attributes are primary keys and which ones are foreign keys.

Inventory [Description, Cost Price, Unit Price, ProductID#, Qty on hand]

Cash [Bank, Account Description, Account#, Balance]

Order/Sale [Invoice Date, Invoice#, Customer#, Employee#, Cash Receipt#]

Cash Receipts [Receipt Date, Cash Receipt #, Receipt Amount, Customer#, Employee#, Account#]

Employees [Employee#, Last Name, First Name, Address, City, Zip, Phone, Hire Date, Department, Salary/Wage, Pay Amount]

Customers [Customer#, Customer Name, Customer Address, Customer City/State/Zip, Customer Phone]

Order/Sale Items [ProductID#, Unit Price, Invoice#, Qty Ordered]

(7 marks)

Referring to the documents below, prepare the Batch Transmission Sheet (Batch Header) using the most appropriate fields for each of the totals. Justify the field that you use for the hash total. Write your answer in the form provided below.

Record Count

Hash Total

Total Items

Control Total ($)

Question 3

(12 marks)

Read the case below and then complete the UML class diagram provided with classes and multiplicities for the expenditure cycle of Anna's Clarinet Shop.

Anna's Clarinet Shop orders clarinets and related products from various suppliers. Anna's maintains information about each supplier's company information together with the contact person. Each purchase order shows the order number, date, inventory numbers of all items ordered, item description, item unit price, GST, and the order total. The manager of Anna's places each order by email whenever inventory is running low. Some suppliers fill each individual order separately. Others, however, consolidate orders and fill all of them in one weekly delivery. Anna's suppliers never make partial shipments. If the suppliers are out of stock of a certain item, they wait until they obtain that item and then ship the entire order. Some suppliers require payment at the time of delivery but others email Anna's a monthly statement detailing all purchases during the current period. Two suppliers allow Anna's to make instalment payments for any individual purchase orders that exceed $10,000. Anna's pays all suppliers from a single bank account.

Required

Write on the partial UML class diagram provided below the missing relationships, names of the classes and the missing multiplicities for each relationship. Ensure your classes fit the REA conventional layout.

Question 4

(11 marks) Read the scenario below and answer the questions that follow.

Simpson Sam Electronics used to operate a manual accounting system. After his son, Bart, graduated from university he helped his father to set up a relational database using Microsoft-Access. The database consisted of four (4) tables (Customer, Inventory, Sales and Sales-Inventory). Bart also created relationships among the tables.

a) Show the output of the Query by Design shown in the picture below. (2 marks)

b) Show the output of the SQL Query shown below. (2 marks)

SELECT [Sales Invoice #], Salesperson, Quantity

FROM Sales, [Sales-Inventory]

WHERE Sales. [Sales Invoice #] = [Sales-Inventory].[Sales Invoice #], [Item #] = '10';

c) Identify the two errors in the syntax of the SQL Query shown below. Correct the syntax. (1 mark)

SELECT *

FROM Sales-Inventory

ORDER BY Quantity

d) Show the output of the SQL Query shown below. (2 marks)

SELECT Sum([Sales-lnventory],[Quantity]*[Inventory],[Unit Price]) AS [Total Sales] FROM Customer, Inventory, [Sales-lnventory]

WHERE Inventory.[Item #] = [Sales-lnventory],[Item #], [Sales-lnventory],[Item #]=10;

e) Show the output of the Query by Design shown in the picture below. (3 marks)

f) Identify the error in the syntax of the SQL Query shown below with the output desired as shown in the table. Correct the syntax. (1 mark)

SELECT [Sales Invoice #], Date, [Customer #], [Customer Name] FROM Customer, Sales

WHERE Customer.[Customer #] = Sales.[Customer #]

ORDER BY Sales.[Sales Invoice #];