Question 2 (7 marks) A large international bank would like to create a data warehouse with information about the loans g

[answerhappygod]

Question 2 7 Marks A Large International Bank Would Like To Create A Data Warehouse With Information About The Loans G 1 (134.3 KiB) Viewed 20 times

Question 2 (7 marks) A large international bank would like to create a data warehouse with information about the loans given to its customers. A customer is described by a unique account number associated with a loan, full name and address. The bank records the dates when the customers are provided with the loans and the dates when the loans are fully repaid. A date consists of a day, month and year. The banks offer the following types of loans: home, investment, personal. Different types of loans are offered at different interest rates. All loans must be insured at the insurance companies. An insurance company is described by a unique name and address. The loans are issued by the tellers located at the branches. A description of a teller consists of a unique employee number and full name. A branch is described by a unique name. The bank plans to use a data warehouse to implement the following classes of analytical applications. (1) Find the total number of loans issued per day, per month, per year, per branch, per bank teller, per city, per state, per country, per loan type, per customer. (2) Find the total amount of money loaned to the customers per day, per month, per year, per city, per country, per loan type. (3) Find the total interest rates on the loans per day, per month, per year, per city, per country, per loan type. (4) Find an average period of time needed for the loan repayment per loan type, per customer, per city, per country. (5) Find the total number of different currencies used for the loans. (6) Find the total amount of money on loans per currency. Your task is to create a conceptual schema of a data warehouse needed by the bank. To draw a conceptual schema, use a graphical notation explained to you in a presentation 11 Conceptual Data Warehouse Design. Draw a conceptual schema by hand on a piece of paper, take a picture or scan it and submit an image.