Dynasty Bank was founded in Sydney in 1960 and has been expanding rapidly. Initially, Dynasty Bank stored data in a file-based system, which was then transitioned into using spreadsheets. As years progressed, their business has grown exponentially leading to an increase in the number of branches and customers across the country. You are hired as a consultant to develop a prototype database. You can assume the Information Technology (IT) staff will develop the applications using the prototype database. Below are some of the specifications you collected from the initial meetings with the managers of business units. You will develop a prototype database based on your meetings, and you acknowledge that not all attributes are included.

Below are the business rules you have been given by Dynasty Bank: Dynasty Bank has several branches across Australia. You can assume same services are provided across all locations. Every branch is uniquely identified in the system. Other details which the system needs to store about a bank are name, address, website, and contact number.

Every bank branch employs multiple employees. Every staff member is provided with a unique staff number. The company also needs to keep track of other details about their staff members like their name, address, contact number and date of birth. Staff can either be a manager, a bank teller, a loan officer, a credit analyst, or an IT technical support. For a manager and an IT technical support staff, their salaries need to be recorded. For a loan officer, a bank teller and a credit analyst, their hourly rate and hours per week are recorded. An IT technical support staff can either be a senior IT technical support or a junior IT technical support staff. A junior IT technical staff's education degree, year of completion and ATAR (Australian Tertiary Admission Rank) are recorded, whereas a senior IT technical staff's years of experience are recorded.

An employee can work full-time or part-time. Every part-time employee can work at multiple branches whereas a full-time employee can only work at one branch. The manager and an IT technical support staff of a branch are employed on a full-time basis whereas a loan officer, a bank teller, and a credit analyst work on a part-time basis. A bank only has one manager and one senior IT technical support staff and at least one bank teller. More than one junior IT technical support staff can work at the same time in a branch, which, however, is not required. A loan officer is only required to work in the morning, whereas at least one credit analyst is needed in each location during business hours, with more credit analysts needed during the busy hours (i.e., Saturday mornings). A staff can be managed by one staff member who could either be a manger, an IT technical support, a loan officer, a bank teller, or a credit analyst.

A bank branch has multiple offices and one to two automated tell machines (ATMs). An office must belong to only one particular branch. Every office is uniquely identified in the system. Other details of an office include the name, description, and capacity. Each ATM should be uniquely identified with the corresponding branch.

The Dynasty Bank offers standard checking and savings accounts to customers. A customer can open a checking account, a savings account, or both checking and savings accounts with the bank. A customer can also open a loan account by taking a loan from the bank.

For a customer, normal customer details such as customer ID, customer name, address (including state and postcode), phone number(s), occupation, annual income, and credit scores are recorded.

For a savings or a checking account, a customer's basic account information, such as the interest rate, account balance, along with the history of account activities (e.g., the amount deposited, transferred in, transferred out, or withdrawn) are also recorded.

Similarly, for a loan account, basic terms of the loan, including the stipulated loan rate, the loan amount, the loan duration, the repayment, and the loan balance are recorded, along with the history of the loan activities.

Required: With reference to the above case, please answer

a) Use Crow's Foot notation to create an Entity Relationship Diagram (ERD) that provides a suitable model based on the above business rules. You can state your assumptions, but they must be reasonable.

b) Advise how Dynasty Bank can ensure their database security. Explain which risks the database is vulnerable to, and why, with examples from the business rules. Recommend mitigations to address identified risks.

c) You are a data analyst who was recently hired by Dynasty Bank to design a data mart to support their marketing analytics. The bank is ambitious in growing their business by providing new services and/or expanding new branches. Design by depicting a star schema that contains relevant dimension tables and at least one fact table that may be used to aid the bank's decision making. Clearly identify the attributes for each table. Your design shall support multi-dimensional analyses with variable granularity levels to allow the user to drill down and roll up a dimension. Please explain your design.

d) Due to the recent COVID pandemic and mandatory lockdowns, Dynasty Bank has made some strategic changes. First, they have allowed employees, especially those work in the back office such as analysts to work from home. Second, with the help of some IT company, the bank was able to quickly move some of its business online. Customers are allowed to perform some banking functions online, such as checking account balances, making payments, and transferring money. During a single day, Dynasty Bank's websites can receive over half a million visits. Using state-of-the-art web analytics tools, in order to understand customers' online behaviour the bank tracks and analyses the traffic of its websites, including the number of page views, movement of mouse cursors, and the timing of mouse clicks. As a result, more data are collected increasingly over time to be used to aid the marketing analytics described in c). What changes would you recommend to Dynasty Bank's database infrastructure network to handle the data generated from the new online banking, work from home, and web analytics? You can aid with a diagram to support your recommendation of the modified infrastructure network. You do not need to create an ERD, or a logical database, or a physical database. Please note that you cannot use or copy diagrams from other sources