Data Management AND System Analysis and Project Management A company keeps the f

Question

Data Management AND System Analysis and Project Management

A company keeps the following database table to track account balance of customers:

Customer(Customer#, CustomerName, CustomerBalance)

The company updates the table using the following process.

(i) When a customer places an order, the customerbalance is increased by the order amount. (ii) (ii) When a customer makes a payment, the customerbalance is decreased by the payment amount.

That is, the customerbalance in the above table stores the current balance at any instant in time. Thus, the company can obtain the current balance owed by a customer at the time of inquiry.

The finance manager of this company is interested in an aged balance for each customer. That is, he/she wants the customer balance to be split into categories like <30-day, 30-60day, and >60-day balances. For example, if a customer has not paid $1000 for orders placed more than 60 days ago, then this $1000 should be in the category 60-day balance.

Quesion1: Suggest a set of one or more database tables to produce the aged balance report.

Question2: Briefly describe how these tables will be updated and how the aged balance report can be generated using these tables.

Explanation / Answer

Hi,

I have given the answer to the question 1 and question 2 with explanantion.Please find them below:-

================================================================================

ANS1:-

Apart from the given table

1) Customer (Customer#, CustomerName, CustomerBalance)

The other table which will be required is:-

2) CustTransaction (Transaction#, Customer#, TransactionType, Amount, TransactionDate)

This table will have unique transaction# as primary key, customer# as foreign key and the type of transaction details i.e order/payment, amount and date of transaction.

ANS2:-

Now when a customer is newly registered it will have unique customer id in Customer table and also it will have 0 balances.

When a order is placed then a new transaction id i.e transaction# will be there for every type of transaction whether order or payment.

When transaction takes place then it will have unique transaction id i.e transaction#, customer id i.e Customer# from Customer table and type of transaction (order/payment) , the amount and the Transaction date.

Whenever there is order then it will add the amount to the CustomerBalance of the table Customer with the help of Customer# as the matching key.

The query will be:-

UPDATE Customer

SET    CustomerBalance+=(SELECT Amount

                          FROM   CustTransaction

                          WHERE TransactionType=’ORDER’);

Whenever there is a debit then it will subtract the amount from the CustomerBalance of the table Customer with the help of Customer# as the matching key.

The query will be:-

UPDATE Customer

SET    CustomerBalance-=(SELECT Amount

                          FROM   CustTransaction

                          WHERE TransactionType=’PAYMENT’);

Aged Balance Report:-

Select CustomerName,CustomerBalance from Customer ,CustTransaction where (GETDATE() - TransactionDate) > 30;

Select CustomerName,CustomerBalance from Customer ,CustTransaction where (GETDATE() - TransactionDate) > 30 and where (GETDATE() - TransactionDate) <60;

Select CustomerName,CustomerBalance from Customer ,CustTransaction where (GETDATE() - TransactionDate) > 60;

=====================================================================================

Please let me know in case any queries.

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.