Subject Code: TECH1400

Subject Code: TECH1400

Subject Name: Database Design and Management

Assessment Title: Case Study Analysis and Design

Table of Contents

TOC o "1-3" h z u Introduction PAGEREF _Toc164089992 h 3Entity Relationship Model Design PAGEREF _Toc164089993 h 3ERM Overview PAGEREF _Toc164089994 h 3Design Choices PAGEREF _Toc164089995 h 4Normalisation PAGEREF _Toc164089996 h 4Entity Relationship Model Design for Barclays Bank: PAGEREF _Toc164089997 h 5Application in the Chosen Organisation PAGEREF _Toc164089998 h 5Research and Analysis PAGEREF _Toc164089999 h 6Conclusion and Recommendations PAGEREF _Toc164090000 h 7References PAGEREF _Toc164090001 h 8

IntroductionBarclays Bank, founded in 1690, is one of the oldest and largest financial institutions in the UK. Moreover, it offers a broad range of services such as banking for individuals, wealth management, and investment banking on a global scale ADDIN ZOTERO_ITEM CSL_CITATION {"citationID":"JnEuSNAS","properties":{"formattedCitation":"(Ackrill and Hannah, 2001)","plainCitation":"(Ackrill and Hannah, 2001)","noteIndex":0},"citationItems":[{"id":1168,"uris":["http://zotero.org/users/local/YuUwhbLT/items/6R5E6YWS"],"itemData":{"id":1168,"type":"book","publisher":"Cambridge University Press","source":"Google Scholar","title":"Barclays: The business of banking, 1690-1996","title-short":"Barclays","URL":"https://books.google.com/books?hl=en&lr=&id=yAwipk34KP0C&oi=fnd&pg=PR12&dq=Barclays+Bank,+founded+in+1690,+&ots=s4WcIwjtsI&sig=w3qkg1mdRV4eTNwP7jjrLPDnDWg","author":[{"family":"Ackrill","given":"Margaret"},{"family":"Hannah","given":"Leslie"}],"accessed":{"date-parts":[["2024",4,15]]},"issued":{"date-parts":[["2001"]]}}}],"schema":"https://github.com/citation-style-language/schema/raw/master/csl-citation.json"} (Ackrill and Hannah, 2001). The bank's processes extensively employ relational databases, which are essential for the management of a huge amount of data through different functions like customer information management, transaction processing and risk analysis. These databases facilitate the bank's efficient and secure handling of personal and financial information. These, in turn, enable the bank's delivery of high-quality service while meeting regulatory requirements. The report focused on creating an Entity Relationship Model (ERM) for Barclays Bank in order to carry out a thorough analysis of the data structure and how it is interlinked within the relational database system ADDIN ZOTERO_ITEM CSL_CITATION {"citationID":"337GtSIc","properties":{"formattedCitation":"(Kitema, 2021)","plainCitation":"(Kitema, 2021)","noteIndex":0},"citationItems":[{"id":1171,"uris":["http://zotero.org/users/local/YuUwhbLT/items/JCR4LVS3"],"itemData":{"id":1171,"type":"thesis","genre":"PhD Thesis","publisher":"University of Nairobi","source":"Google Scholar","title":"Separation as a Corporate Strategy: the Case Study of Barclays Bank Plc and Absa Bank PLC","title-short":"Separation as a Corporate Strategy","URL":"http://erepository.uonbi.ac.ke/handle/11295/160634","author":[{"family":"Kitema","given":"Caroline"}],"accessed":{"date-parts":[["2024",4,15]]},"issued":{"date-parts":[["2021"]]}}}],"schema":"https://github.com/citation-style-language/schema/raw/master/csl-citation.json"} (Kitema, 2021). The emphasis will be on the practical part of applying the principles of key database management theory to construct a model which represents real-life data connections and normalisation concepts needed for solving the efficiency and data integrity problems in the financial environment on a large scale. This method will demonstrate the implementation of theoretical database concepts in the banking environment, allowing them to be clearly understood and useful when dealing with data assets of the bank.

Entity Relationship Model DesignERM OverviewAn Entity Relationship Model (ERM) is a graphical conceptual method used to model and visualise how data in a database is related to each other. According to ADDIN ZOTERO_ITEM CSL_CITATION {"citationID":"QHcAdnq5","properties":{"formattedCitation":"(Shin, 2022)","plainCitation":"(Shin, 2022)","noteIndex":0},"citationItems":[{"id":1173,"uris":["http://zotero.org/users/local/YuUwhbLT/items/RNT32QBK"],"itemData":{"id":1173,"type":"article-journal","container-title":"IEEE Access","note":"publisher: IEEE","page":"9490894923","source":"Google Scholar","title":"Teaching Method for EntityRelationship Models Based on Semantic Network Theory","volume":"10","author":[{"family":"Shin","given":"Shin-Shing"}],"issued":{"date-parts":[["2022"]]}}}],"schema":"https://github.com/citation-style-language/schema/raw/master/csl-citation.json"} Shin (2022), ERMs serve as the backbone of database structuring and designing operations since the conceptual data model they provide organises and structures entities (i.e., data objects) and shows the types of relationships that exist among them. The importance of an ERM for a multifaceted Organisation such as Barclays Bank cannot be emphasised enough, as it will make sure that an efficient and consistent data structure backs up all the relevant areas of the bank. This support can undertake both simple customer management as well as complex transactional processes and compliance reporting. In the ERM designed for Barclays, there are four major entities, including Customers, Accounts, Transactions, and Loans, which are mapped out. This model not only assists in visualising the bank's operational and data flow framework but also helps in identifying potential areas where data processes can be optimised so that data is managed efficiently and retrieved quickly, which is essential for real-time banking operations and decision-making.

Design ChoicesThe ERM for Barclays Bank incorporates several primary entities: Customers, Accounts, Transactions, and Loans. Every single element is connected by means of specially designed relations that recreate the relations of real life. For example, customers are associated with Accounts through a relationship that can be named to mean "owns" or "holds," which implies that one and multiple customers can own one and various accounts, and each account is held by one and numerous customers (reflecting joint accounts). The other two prominent units are Transactions which link Accounts (through which executed) and Loans (repayment or disbursement) and highlight the use of money within the bank ADDIN ZOTERO_ITEM CSL_CITATION {"citationID":"2Vfh4TNW","properties":{"formattedCitation":"(Allen {i{}et al.}, 2020)","plainCitation":"(Allen et al., 2020)","noteIndex":0},"citationItems":[{"id":1176,"uris":["http://zotero.org/users/local/YuUwhbLT/items/L62QPL49"],"itemData":{"id":1176,"type":"report","publisher":"National Bureau of Economic Research","source":"Google Scholar","title":"Design choices for central bank digital currency: Policy and technical considerations","title-short":"Design choices for central bank digital currency","URL":"https://www.nber.org/papers/w27634","author":[{"family":"Allen","given":"Sarah"},{"family":"apkun","given":"Srdjjan"},{"family":"Eyal","given":"Ittay"},{"family":"Fanti","given":"Giulia"},{"family":"Ford","given":"Bryan A."},{"family":"Grimmelmann","given":"James"},{"family":"Juels","given":"Ari"},{"family":"Kostiainen","given":"Kari"},{"family":"Meiklejohn","given":"Sarah"},{"family":"Miller","given":"Andrew"}],"accessed":{"date-parts":[["2024",4,15]]},"issued":{"date-parts":[["2020"]]}}}],"schema":"https://github.com/citation-style-language/schema/raw/master/csl-citation.json"} (Allen et al., 2020). The correlation between Loans and Customers (emitted from) states which borrower takes the loan and their responsibility for repayments. These entities and relationships are fundamental, because they help the bank to perform such functions as managing customer information, executing and tracking financial transactions, assessing the economic health of the clients, and complying with the legal requirements. One of the major functions of Enterprise Risk Management consists of the detailed revelation of these relationships for internal audits, customer services, and strategic planning.

NormalisationNormalisation is one of the techniques to normalise the data for Barclays Bank used in order to ensure data integrity as well as to eliminate redundancy, which is especially important for reducing errors and increasing the performance of the bank's DBMSs ADDIN ZOTERO_ITEM CSL_CITATION {"citationID":"1tqb3fWB","properties":{"formattedCitation":"(Albarak {i{}et al.}, 2020)","plainCitation":"(Albarak et al., 2020)","noteIndex":0},"citationItems":[{"id":1180,"uris":["http://zotero.org/users/local/YuUwhbLT/items/EUN5Y979"],"itemData":{"id":1180,"type":"article-journal","container-title":"IEEE Transactions on Software Engineering","issue":"3","note":"publisher: IEEE","page":"755772","source":"Google Scholar","title":"Managing technical debt in database normalization","volume":"48","author":[{"family":"Albarak","given":"Mashel"},{"family":"Bahsoon","given":"Rami"},{"family":"Ozkaya","given":"Ipek"},{"family":"Nord","given":"Robert"}],"issued":{"date-parts":[["2020"]]}}}],"schema":"https://github.com/citation-style-language/schema/raw/master/csl-citation.json"} (Albarak et al., 2020). Normalisation implies organising the database using certain procedures that typically include various normal forms, which is a gradual procedure to eliminate data redundancy and dependency. The database design which is initially followed for Barclays supports the First Normal Form (1NF), which requires the removal of repetitive groups and, hence, making sure that all the entries are unique and non-repeating. Next, the process moves to the Second Normal Form (2NF) and Third Normal Form (3NF), where the data is further tuned from splitting a subset of data that is applicable to multiple rows of a table into different tables so that the non-key dependants attributes become independent from each other across different tables ADDIN ZOTERO_ITEM CSL_CITATION {"citationID":"pT2bkxHx","properties":{"formattedCitation":"(Purohit, 2021)","plainCitation":"(Purohit, 2021)","noteIndex":0},"citationItems":[{"id":1181,"uris":["http://zotero.org/users/local/YuUwhbLT/items/SB34PCCF"],"itemData":{"id":1181,"type":"article-journal","container-title":"International Journal of Data Science and Analysis","issue":"3","note":"publisher: Science Publishing Group","page":"8997","source":"Google Scholar","title":"Separation of Data Cleansing Concept from EDA","volume":"7","author":[{"family":"Purohit","given":"Khanjan"}],"issued":{"date-parts":[["2021"]]}}}],"schema":"https://github.com/citation-style-language/schema/raw/master/csl-citation.json"} (Purohit, 2021). For instance, information such as customer address is segregated from the transaction records to keep track of consistency and reduce data duplication. The normalisation sections of the bank system are of paramount importance because they guarantee that operations avoid making unexpected errors, like data inaccuracies, due to additions, removals, and insertions of data, which might distort the global bank's data image.

Entity Relationship Model Design for Barclays Bank:The following is a conceptual design of the Enterprise Risk Management (ERM) model of Barclays Bank. This ERM includes the entities mentioned and their key relationships:

1. Customers - the keystone of the model affiliated with Accounts and Loans.

2. Accounts - Connected with Customer and Transaction.

3. Transactions - the interface that bridges two accounts and can manage the loan balance.

4. Loans- Trace it down to customer balances and see how the money flows.

Figure 1:Barclays Bank ERM

Application in the Chosen OrganisationBarclays Bank handles different types of data, which are crucial during diverse banking operations in the Organisation. The basic types of data, such as personal customer information comprising names, addresses, contact details, employment background, and financial health, are some of the most popular ADDIN ZOTERO_ITEM CSL_CITATION {"citationID":"wOV7Jfxn","properties":{"formattedCitation":"(Siwale, 2022)","plainCitation":"(Siwale, 2022)","noteIndex":0},"citationItems":[{"id":1183,"uris":["http://zotero.org/users/local/YuUwhbLT/items/T8ERLAJ5"],"itemData":{"id":1183,"type":"thesis","genre":"PhD Thesis","publisher":"The University of Zambia","source":"Google Scholar","title":"Effects of electronic banking on service delivery: a case of Barclays bank plc.","title-short":"Effects of electronic banking on service delivery","URL":"https://dspace.unza.zm/handle/123456789/7527","author":[{"family":"Siwale","given":"Lillian"}],"accessed":{"date-parts":[["2024",4,15]]},"issued":{"date-parts":[["2022"]]}}}],"schema":"https://github.com/citation-style-language/schema/raw/master/csl-citation.json"} (Siwale, 2022). The relevant information for account details includes account numbers, types of accounts and their balances and the associated customer IDs. The histories of transactions are also recorded diligently, whereby the dates, amounts, types (e.g., deposit, withdrawal, payment) and accounts are included. This data is a very significant component of the bank's daily performance, doing the job of relationship management, financial tracking and risk management systems in one way or the other. Such data sets are both for operational purposes and regulatory purposes. They are used for the AML checks and ensuring the adequate compliance of the financial services regulations. Each of these data is interlinked and contributes to the creation of a global data ecosystem which encompasses strategic decision-making and operational efficiency.

The Entity Relationship Model (ERM) elaborated for Barclays Bank displays the complex interdependencies in an obvious manner. The linking of customers to accounts helps in the acquisition of ownership, while the linking of transactions to accounts shows that financial activities are taking place ADDIN ZOTERO_ITEM CSL_CITATION {"citationID":"1T4mfpLy","properties":{"formattedCitation":"(Barclayscorporate, 2024)","plainCitation":"(Barclayscorporate, 2024)","noteIndex":0},"citationItems":[{"id":1185,"uris":["http://zotero.org/users/local/YuUwhbLT/items/5BS4Q42S"],"itemData":{"id":1185,"type":"webpage","abstract":"With Barclays liquidity products and solutions, optimise returns on cash deposits through notional pooling, cash pooling and cash concentration.","language":"en","title":"Liquidity Management | Barclays Corporate Banking","URL":"https://www.barclayscorporate.com/solutions/liquidity-management/","author":[{"family":"Barclayscorporate","given":""}],"accessed":{"date-parts":[["2024",4,15]]},"issued":{"date-parts":[["2024"]]}}}],"schema":"https://github.com/citation-style-language/schema/raw/master/csl-citation.json"} (Barclayscorporate, 2024). The loan entity refers to both the customers who are being granted loans and transactions for which the balances are changed through repayment and disbursement. This relationship mapping lays an essential foundation in the bank`s structure to create customer profiles, manage financial products, and monitor money flow through the bank. Additionally, to ensure cohesion of the database, Barclays most probably apply concepts like "keys" to link different tables (e.g. Customer IDs in the accounts table) and "triggers" for automatic updates following transactions. The protection of sensitive data requires security measures, and this may include, among others, encryption and access controls, and compliance with regulations like the General Data Protection Regulation and local financial authority requirements should not be overlooked. The ERM is intended to cater for the Organisation's growing data and information needs through a secure, compliant, and efficient manner that guarantees better service delivery and regulatory compliance. Through comprehensive mapping and normalisation, this means that the data retrieval is not only fast enough, but also reduces redundancy and maximises data integrity. These are the two main aspects of data management in the banking sector, which is regulated and heavily data-driven.

Research and AnalysisThe development of the Entity-Relationship Model (ERM) for Barclays Bank is reliant on extensive studies of literature sources such as academic journals and guidelines for database management within the banking field. Scholars of database systems, for example, Michael Stonebraker and Lawrence A. Rowe, authors of "The Design of POSTGRES," have provided the foundations in principles of database architectures, which is critical for the comprehension of the complex systems of financial data ADDIN ZOTERO_ITEM CSL_CITATION {"citationID":"BTSfmCGJ","properties":{"formattedCitation":"(Stonebraker and Rowe, 1986)","plainCitation":"(Stonebraker and Rowe, 1986)","noteIndex":0},"citationItems":[{"id":1188,"uris":["http://zotero.org/users/local/YuUwhbLT/items/KSYTZ6JF"],"itemData":{"id":1188,"type":"article-journal","abstract":"This paper presents the preliminary design of a new database management system, called POSTGRES, that is the successor to the INGRES relational database system. The main design goals of the new system are ton n n provide better support for complex objects,n n n provide user extendibility for data types, operators and access methods,n n n provide facilities for active databases (i.e., alerters and triggers) and inferencing including forward- and backward-chaining,n n n simplify the DBMS code for crash recovery,n n n produce a design that can take advantage of optical disks, workstations composed of multiple tightly-coupled processors, and custom designed VLSI chips, andn n n make as few changes as possible (preferably none) to the relational model.n n n n The paper describes the query language, programming language interface, system architecture, query processing strategy, and storage system for the new system.","container-title":"ACM SIGMOD Record","DOI":"10.1145/16856.16888","ISSN":"0163-5808","issue":"2","journalAbbreviation":"SIGMOD Rec.","language":"en","page":"340-355","source":"DOI.org (Crossref)","title":"The design of POSTGRES","volume":"15","author":[{"family":"Stonebraker","given":"Michael"},{"family":"Rowe","given":"Lawrence A."}],"issued":{"date-parts":[["1986",6,15]]}}}],"schema":"https://github.com/citation-style-language/schema/raw/master/csl-citation.json"} (Stonebraker and Rowe, 1986). These principles have been, in parallel, complemented by a range of guidelines for the financial services sector by the Financial Services Technology Consortium, which offers detailed standards on data management, security, and compliance in the banking environment ADDIN ZOTERO_ITEM CSL_CITATION {"citationID":"75MvlOmv","properties":{"formattedCitation":"(FSTC, 2015)","plainCitation":"(FSTC, 2015)","noteIndex":0},"citationItems":[{"id":1190,"uris":["http://zotero.org/users/local/YuUwhbLT/items/ISLQEBU2"],"itemData":{"id":1190,"type":"post-weblog","abstract":"Promoted open-standard technologies in the financial services industry *Merged with Financial Services Roundtable in 2009 Continue reading ","container-title":"ConsortiumInfo.org","language":"en-US","title":"Financial Services Technology Consortium (FSTC)","URL":"https://www.consortiuminfo.org/list/financial-services-technology-consortium-fstc/","author":[{"family":"FSTC","given":""}],"accessed":{"date-parts":[["2024",4,15]]},"issued":{"date-parts":[["2015"]]}}}],"schema":"https://github.com/citation-style-language/schema/raw/master/csl-citation.json"} (FSTC, 2015). These sources altogether focus on the need for streamlined database architecture that verifies data authenticity, security and accessibility - all of which are integral for a bank like Barclays. The ERM framework was developed with these best practices in mind with its comprehensive normalisation processes to reduce redundancy, and strategic entity relationships that guarantee information accuracy and connectivity. The alignment of the bank with a set of best practices guarantees operational efficiency as well as compliance with very strict regulatory demands. However, the ERM perfectly deals with these needs by building a logical structure of data that is easy to read, but challenges such as maintaining data security in an unstable digital threat landscape and ensuring real-time processing speed exist. Despite this, the model's design follows the industry's guidance for handling the intricacies of large-scale, secure, and compliant database systems in the financial sector, which makes it a useful and useful tool in the bank's efforts for ongoing data management improvements.

Conclusion and RecommendationsThe ERM presented here for Barclays Bank has been shown throughout the Report to be an effective tool to organise and deal adequately with the very complex relationships among customers, accounts, transactions, and loans, which are the main tools that the bank uses operationally. This model, in turn, corresponds to the principles of best database management practice that are useful for Organisation, security, and compliance of the structured data relationships and normalisation methods. While ERM helps in data integrity and operational efficiency through automation to a great extent, there are always some opportunities to enhance the systems. The data analytics tools of modern times could be included in the ERM of the bank in order to ensure dynamic data exchange and real-time analytics with the help of these tools. This innovation may allow us to get more detailed information about customer behaviour, which in turn can increase the risk management potential.

Moreover, the integration of blockchain technology can help reinforce the security and integrity of transaction data for online banking services, which is the most important part of digital banking security. However, future design restrictions may include the scalability challenge as the bank grows in size and the requirement to undertake regular rewrites to keep up with regulatory changes. The refinements ahead should increase the flexibility of the regulatory framework to expand its coverage across the financial markets and integrate emerging technologies into the ERM.

References ADDIN ZOTERO_BIBL {"uncited":[],"omitted":[],"custom":[]} CSL_BIBLIOGRAPHY Ackrill, M. and Hannah, L. (2001) Barclays: The business of banking, 1690-1996. Cambridge University Press. Available at: https://books.google.com/books?hl=en&lr=&id=yAwipk34KP0C&oi=fnd&pg=PR12&dq=Barclays+Bank,+founded+in+1690,+&ots=s4WcIwjtsI&sig=w3qkg1mdRV4eTNwP7jjrLPDnDWg (Accessed: 15 April 2024).

Albarak, M. et al. (2020) Managing technical debt in database normalisation, IEEE Transactions on Software Engineering, 48(3), pp. 755772.

Allen, S. et al. (2020) Design choices for central bank digital currency: Policy and technical considerations. National Bureau of Economic Research. Available at: https://www.nber.org/papers/w27634 (Accessed: 15 April 2024).

Barclayscorporate (2024) Liquidity Management | Barclays Corporate Banking. Available at: https://www.barclayscorporate.com/solutions/liquidity-management/ (Accessed: 15 April 2024).

FSTC (2015) Financial Services Technology Consortium (FSTC), ConsortiumInfo.org. Available at: https://www.consortiuminfo.org/list/financial-services-technology-consortium-fstc/ (Accessed: 15 April 2024).

Kitema, C. (2021) Separation as a Corporate Strategy: the Case Study of Barclays Bank Plc and Absa Bank PLC. PhD Thesis. University of Nairobi. Available at: http://erepository.uonbi.ac.ke/handle/11295/160634 (Accessed: 15 April 2024).

Purohit, K. (2021) Separation of Data Cleansing Concept from EDA, International Journal of Data Science and Analysis, 7(3), pp. 8997.

Shin, S.-S. (2022) Teaching Method for EntityRelationship Models Based on Semantic Network Theory, IEEE Access, 10, pp. 9490894923.

Siwale, L. (2022) Effects of electronic banking on service delivery: a case of Barclays Bank plc. PhD Thesis. The University of Zambia. Available at: https://dspace.unza.zm/handle/123456789/7527 (Accessed: 15 April 2024).

Stonebraker, M. and Rowe, L.A. (1986) The design of POSTGRES, ACM SIGMOD Record, 15(2), pp. 340355. Available at: https://doi.org/10.1145/16856.16888.