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.
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.
Q.3.1 Three reasons for implementing a data warehouse and data mart system at ABC Bank are:
1. Improved data analysis: The data warehouse and data marts will allow ABC Bank to gather and consolidate vast amounts of data from various sources, providing a comprehensive view of customer behavior, market trends, and business performance. This will enable the bank to perform in-depth analysis and gain valuable insights for making informed decisions.
2. Enhanced decision-making: With a robust data management system in place, ABC Bank can access accurate and up-to-date data from different business units or functions. This will enable the bank's management team to make more informed and timely decisions, leading to improved overall business performance.
3. Customized reporting: By implementing data marts tailored to specific business units or functions, ABC Bank can provide targeted and customized reporting to various stakeholders. This will allow each department or team to focus on their specific needs and requirements, ensuring that the data presented is relevant and actionable.
Q.3.2.1 Three tables that can be found in the ABC database may include:
1. Customer table: This table would store all the information related to ABC Bank's customers, such as customer ID, name, contact details, address, date of birth, etc.
2. Account table: This table would contain details about the various types of accounts held by ABC Bank's customers, including account number, account type (savings, checking, etc.), account balance, account status, etc.
3. Transaction table: This table would record all the transactions made by ABC Bank's customers, including transaction ID, account number, transaction type (deposit, withdrawal, transfer), transaction amount, transaction date, etc.
Q.3.2.2 For each table identified in Q.3.2.1, three fields and their data types can be listed as follows:
1. Customer table:
- Customer ID (Data Type: Integer)
- Name (Data Type: String)
- Date of Birth (Data Type: Date)
2. Account table:
- Account Number (Data Type: String)
- Account Type (Data Type: String)
- Account Balance (Data Type: Decimal)
3. Transaction table:
- Transaction ID (Data Type: Integer)
- Account Number (Data Type: String)
- Transaction Amount (Data Type: Decimal)
Q.3.3 Please refer to the image below illustrating a relational database model and its components:
[Insert clear and labelled illustration of a relational database model]
In a relational database model, data is organized into tables, which are represented as boxes in the illustration. Each table represents a unique entity or concept in the database. The columns within a table represent attributes or properties of that entity, and each row represents a specific record or instance of the entity.
The relationship between tables is established through primary keys and foreign keys. Primary keys uniquely identify each record in a table, while foreign keys establish a link between tables based on the primary key of one table and the related attribute in another table. This allows data to be associated and retrieved efficiently using relational operations like joins and queries.
Q.3.4 Two concerns associated with the use of distributed databases, based on the scenario, are:
1. Data consistency: With a distributed database system, where data is spread across different locations or servers, ensuring data consistency becomes a challenge. In the case of ABC Bank, if customer information or transaction records are not synchronized properly across all distributed databases, there can be discrepancies and inaccuracies in data. For example, if a customer updates their address in one location and it is not propagated to other locations, it can lead to confusion and inconvenience.
2. Data security: Distributed databases introduce additional security challenges compared to a centralized system. Each distributed database node needs to be secured individually, which can increase the complexity and cost of implementing robust security measures. In ABC Bank's case, since they deal with sensitive customer information and financial data, ensuring the confidentiality, integrity, and availability of data across all distributed databases is crucial. Any security breaches or data leaks can lead to significant reputational and financial damage.