You are to develop a dimensional model for a bank data warehouse. The bank currently has...

   

Transcribed Image Text:

You are to develop a dimensional model for a bank data warehouse. The bank currently has a database system with the following tables: . Account - each record describes the characteristics of an account. . Client-each record describes the demographics and other characteristics of a client. • Disposition - each record relates a client with an account i.e., this relation describes the rights of clients to operate accounts. Loan-each record describes a loan granted for a given account . . • One client can have more than one account, and more than one client can own and access a single account. Clients and accounts are related together (i.e., mapped to each other) in relation Disposition. At most one loan can be granted for an account. However, many transactions may occur in an account. Even in a day, more than one transaction may take place in an account. Each district has only one bank branch. Account Transaction-cach record describes one transaction on an account. District-each record describes demographic characteristics of a district. Attribute account id open date district id Client frequency Attribute client id birth date gender district_id Primary key Antall date of opening the account Location ID of bank branch frequency of issuance of statements "monthly issuance "weekly issuance" issuance after transaction" Primary key Customer birthday Customer gender Customer location Description Description Disposition Attribute disp id client id account id type Loan Attribute loan id account id duration amount payment District Transaction Primary key Attribute district d district name avg salary cities no inhabitants no Attribute Tran id account id tran date type region urban inhabitants ratio operation amount Disposition type of the client for the account. "OWNER" stands for account owner "DISPONENT stands for account user Primary kry Loan granted date "A" stands for contract finished, no problema, "B" stands for contract finished, lean not paid, "C" stands for running contract, OK so far. "D" stands for running contract, client in debe Duration of the loan Amount of the lean Monthly payment Description Primary key average salary Number of cities Number of inhabitants Primary kry: Transaction date "credi" stands for credit withdraw stands for withdrawal "credit card withdrawal "collection from another bank "remittance to another hank Transaction amount Account balance after this transaction Description The region the district belongs to Ratio of urban inhabitants Description Description "credit in cash withdrawal in cash "stands for unknown The bank wants to perform the following query: - List loan amount by branch demographics, owner demographics, loan date and loan status a. (5 pts.) Identify the measure and constraints in the query. b. (10 pts.)Develop a dimensional model. Your model must have FACT and DIMENSION tables. For each table, you need to identify all keys (primary and foreign, if applicable) and attributes to include. You need to also identify the relationships among the tables. You are to develop a dimensional model for a bank data warehouse. The bank currently has a database system with the following tables: . Account - each record describes the characteristics of an account. . Client-each record describes the demographics and other characteristics of a client. • Disposition - each record relates a client with an account i.e., this relation describes the rights of clients to operate accounts. Loan-each record describes a loan granted for a given account . . • One client can have more than one account, and more than one client can own and access a single account. Clients and accounts are related together (i.e., mapped to each other) in relation Disposition. At most one loan can be granted for an account. However, many transactions may occur in an account. Even in a day, more than one transaction may take place in an account. Each district has only one bank branch. Account Transaction-cach record describes one transaction on an account. District-each record describes demographic characteristics of a district. Attribute account id open date district id Client frequency Attribute client id birth date gender district_id Primary key Antall date of opening the account Location ID of bank branch frequency of issuance of statements "monthly issuance "weekly issuance" issuance after transaction" Primary key Customer birthday Customer gender Customer location Description Description Disposition Attribute disp id client id account id type Loan Attribute loan id account id duration amount payment District Transaction Primary key Attribute district d district name avg salary cities no inhabitants no Attribute Tran id account id tran date type region urban inhabitants ratio operation amount Disposition type of the client for the account. "OWNER" stands for account owner "DISPONENT stands for account user Primary kry Loan granted date "A" stands for contract finished, no problema, "B" stands for contract finished, lean not paid, "C" stands for running contract, OK so far. "D" stands for running contract, client in debe Duration of the loan Amount of the lean Monthly payment Description Primary key average salary Number of cities Number of inhabitants Primary kry: Transaction date "credi" stands for credit withdraw stands for withdrawal "credit card withdrawal "collection from another bank "remittance to another hank Transaction amount Account balance after this transaction Description The region the district belongs to Ratio of urban inhabitants Description Description "credit in cash withdrawal in cash "stands for unknown The bank wants to perform the following query: - List loan amount by branch demographics, owner demographics, loan date and loan status a. (5 pts.) Identify the measure and constraints in the query. b. (10 pts.)Develop a dimensional model. Your model must have FACT and DIMENSION tables. For each table, you need to identify all keys (primary and foreign, if applicable) and attributes to include. You need to also identify the relationships among the tables.

Answer rating: 100% (QA)

Solution a Fact Constellation Schema A fact constellation has multiple fact tables ... View the full answer