Data Management AND System Analysis and Project Management A company keeps the f
ID: 3858536 • Letter: D
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
Question 1:-
It is very simple to track the data like the required scenario.
Just use another table named order_info and the attributes would be like below.
Order_info(customer#, order_placed, order_date_placed, amount);
Question 2:-
If we maintain this order_info table we get the information about what order he placed and who is placed and the date order was placed.
By this whenever we want the report then simply we use systemdate - order_date_placed with some conversion functions to we get the days.
And we can list this days and the amount from the table so that we can easily see the data like this from this table.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.