Question

Consider the information that is maintained by a bank. In addition to customer records, the bank maintains records on accounts and loans. Figure 1-20 and Figure 1-21 (below) are two examples of database tables for a regional bank. How might this data be aggregated and analyzed to create information and knowledge?

CustomerID	Name	Address	City	State	Zip
100001	Don Baker	1215 E. New York	Aurora	IL	60504
100002	Yuxiang Jiang	1230 Douglas Road	Oswego	IL	60543
100003	Emily Brown	632 Fox Valley Road	Aurora	IL	60504
100004	Mario Sanchez	24 E. Ogden	Naperville	IL	60563

Figure 1-20: Customers table

           

CustomerID	AccountNumber	AccountType	DateOpened	Balance
100001	4875940	Checking	10/19/1971	2500.00
100001	1660375	Savings	08/10/1973	1200.00
100002	1783032	Savings	05/15/1987	500.00
100002	4793289	Checking	05/15/1987	3200.00
100003	6213690	Checking	02/14/1996	6700.00
100004	1890571	Savings	10/16/2007	5300.00
100004	8390126	Checking	12/02/2008	2700.00

Figure 1-21: Accounts table

Answer 1

We can join data from these two tables Customers and Accounts and derive and aggregate a lot of information and knowledge.

Some of the information which can be aggregated from these two tables are:

• How many customers from Aurora are opening bank account and what is the average balance they are maintaining?
• In which quarter of a year, customers open most number of accounts?
• What is the average number of parallel accounts customers are maintaining?