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 canyou 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.

Answer rating: 100% (QA)

Solution a Entity Relationship Diagram ERD in Crows Foot Notation Entities Branch branchid name address website contactnumber Employee staffnumber name address contactnumber dateofbirth employeetype M... View the full answer