Q.3.1 Three reasons for implementing a data warehouse and data mart system at ABC Bank are:
1. Better Decision Making: By consolidating all the bank's data into a data warehouse, the management team can have a holistic view of customer behavior, market trends, and business performance. This allows them to make more informed decisions and develop targeted strategies for different business units or functions within the bank.
2. Improved Data Quality and Integrity: With a centralized data management system, data can be standardized and validated before being stored in the data warehouse. This ensures that the data is accurate and consistent, improving the overall quality and integrity of the data.
3. Enhanced Reporting and Analysis: A data warehouse facilitates powerful reporting and analysis capabilities. It allows the bank to generate comprehensive reports and perform in-depth analysis on various aspects of the business, such as customer segmentation, profitability analysis, and risk management. This enables the bank to gain valuable insights and identify opportunities for growth and improvement.
Q.3.2.1 Three tables that can be found in the ABC database are:
1. Customer Table: This table would store all the information related to the bank's customers, such as their names, addresses, contact details, and identification numbers.
2. Account Table: This table would contain information about the bank accounts, including the account numbers, types of accounts (savings, checking, investment, etc.), account balances, and account opening dates.
3. Transaction Table: This table would record all the transactions made by customers, including deposit and withdrawal amounts, transaction dates and times, account numbers involved, and transaction types (cash, check, online transfer, etc.).
Q.3.2.2 For each table identified in Q.3.2.1, three fields and their data types are:
1. Customer Table:
- Name (data type: text)
- Address (data type: text)
- Contact number (data type: text)
2. Account Table:
- Account number (data type: numeric)
- Account type (data type: text)
- Account balance (data type: numeric)
3. Transaction Table:
- Transaction ID (data type: numeric)
- Transaction date and time (data type: datetime)
- Transaction type (data type: text)
Q.3.3 A relational database model is a structure that organizes data into tables, where each table represents a specific entity and its attributes. The components of a relational database model include:
- Tables: These are the main components of a relational database model. Each table represents a specific entity and consists of rows and columns. Each row in a table represents a single record, and each column represents a specific attribute or field of that entity.
- Fields/Columns: These are the individual data elements that make up a table. Each field represents a specific attribute of the entity. For example, in a Customer table, fields could include Name, Address, and Contact Number.
- Records/Rows: Each row in a table represents a single record, which is a specific instance or occurrence of the entity being represented. For example, in a Customer table, each row represents a single customer record with their respective attributes.
- Primary Key: A primary key is a unique identifier for each record in a table. It ensures that each record can be uniquely identified and distinguished from other records in the table.
- Foreign Key: A foreign key is a field in one table that refers to the primary key in another table. It establishes a relationship between the two tables and allows for data integrity and consistency.
The illustration of a relational database model would include multiple interconnected tables with their respective fields, and the primary key-foreign key relationships between them.
Q.3.4 Two concerns associated with the use of distributed databases in the scenario are:
1. Data Consistency: With a distributed database system, where data is stored across multiple locations or servers, maintaining data consistency can be challenging. For example, if a customer updates their address at one branch of the bank, it should be reflected consistently across all the branches. In a distributed database, ensuring such consistency requires synchronization mechanisms and protocols.
2. Data Security and Privacy: Storing data in a distributed manner increases the risk of data security breaches. Each location or server may have different security measures in place, making the system vulnerable to unauthorized access or data breaches. Additionally, distributed databases also raise concerns about data privacy, as data may be accessed or shared across different locations without proper authorization or consent. Therefore, robust security measures and privacy protocols are necessary to mitigate these concerns.
Read the scenario below and answer all questions thereafter:
ABC Bank is a large financial institution with millions of customers and thousands of employees.
The bank has a centralised database system that stores all customer information, account data
and transaction records. However, the bank's management team has identified a need for a more
robust and comprehensive data management system that can provide better insights into
customer behaviour, market trends, and business performance.
To address this need, the bank has decided to implement a data warehousing and data mart
system. The data warehouse will serve as a centralised repository for all the bank's data, while the
data marts will be tailored to specific business units or functions within the bank. For example,
there may be a data mart for retail banking, commercial banking, investment banking and risk
management.
Q.3.1 Discuss any three reasons for implementing a data warehouse and data mart
system at ABC Bank
(6)
Q.3.2 Based on the scenario above;
Q.3.2.1 Suggest any three tables that can be found in the ABC database. (3)
Q.3.2.2 For each table identified in Q.3.2.1, list three fields and their data types. (9)
Q.3.3 With the aid of a clearly labelled illustration, explain a relational database model
and its components.
Note: Use any drawing tool e.g., Word, Visio, etc.
(6)
Q.3.4 Discuss two concerns associated with the use of distributed databases using
relevant examples from the scenario.
1 answer