Database Management System for Banks
Due to the emergence of various technologies, there are several businesses who have been benefited from it. The adoption of the technologies has shifted the entire business operation and provided them with a new approach of dealing with the data. Different sector has leveraged the use of the technology such as information system with the help of which they fully automate their business operations.
The information is generally the sociotechnical, formal, and organizational system which is designed in order to process, collect, distribute, and store the data and information efficiently and effectively. In this report, a bank wants to leverage the use of database management system in order to manage their core business activities. A bank database management system will be going to be implemented for their business which will help in several ways.
The purpose of this report is to elaborate the use of database management system, and the help of a database schema provided a physical database will going to be implemented using a database software MySQL Workbench. Once the implementation gets done few queries will be prepared based on the nature of the business. These queries will help the bank to get the important meaningful information for decision making.
In this section, the database design and implementation will be going to be shown for the proposed business. With the help of the database schema provided the entire process will be done.
The entity relationship diagram is generally referred as the type of flowchart which helps to showcase how the entities like the people, concepts, or objects are related with each other within the proposed system. ERD is mainly used for designing and debugging the relational database in the software engineering, research, and Business Information Systems fields. Here, with the help of pre-defined shapes the entities and their associated attributes including appropriate constraints are shown graphically. Below the ERD of bank database has been shown based on which later, the physical database would be implemented.
Figure: Entity Relationship Diagram
Source: Created by author
Database implementation is the final process where the designed database schema gets its physical form. Here, with the help of a software certain structured query languages are used in order to create the tables, insert the relevant data into it, and to perform useful queries to get the meaning insights.
Query 1:
select * from bank_database.bank_branch
inner join bank_database.loan on bank_database.bank_branch.BCODE = bank_database.loan.BCODE
order by bank_database.loan.AMOUNT desc
limit 1;
select t.SSN, t.NAME, t.PHONE, t.ADDR, max(t.netbalance) as net_balance
from (select bank_database.customer.SSN, bank_database.customer.NAME, bank_database.customer.PHONE, bank_database.customer.ADDR, (bank_database.account.BALANCE-bank_database.loan.AMOUNT) as netbalance
from bank_database.customer
inner join bank_database.account_customer on bank_database.customer.SSN = bank_database.account_customer.SSN
inner join bank_database.account on bank_database.account_customer.ACCTNO = bank_database.account.ACCTNO
inner join bank_database.bank_branch on bank_database.account.BCODE = bank_database.bank_branch.BCODE
inner join bank_database.loan on bank_database.bank_branch.BRANCHNO = bank_database.loan.BNO
inner join bank_database.loan_customer on bank_database.loan.LOANNO = bank_database.loan_customer.LOANNO)
select bank_database.bank.NAME, bank_database.bank_branch.ADDR
from bank_database.bank
inner join bank_database.bank_branch on bank_database.bank.CODE = bank_database.bank_branch.BCODE
inner join bank_database.account on bank_database.bank_branch.BRANCHNO = bank_database.account.BNO
inner join bank_database.loan on bank_database.bank_branch.BRANCHNO = bank_database.loan.BNO
limit 10;
Output:
Below there are three insert statement has been provided in order to insert three new data into the created table. But the data which will going to be inserted will violate the rule of data integrity.
Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`bank_database`.`account_customer`, CONSTRAINT `account_customer_ibfk_2` FOREIGN KEY (`SSN`) REFERENCES `customer` (`SSN`))
Below a delete command has been used in order to delete a record which is already there inside one of the tables. But the record which has been chosen to perform this action will violate the integrity constraint rule as the record has a primary key. So, without deleting the record with the foreign key, the record of primary key will not be deleted until a cascade constraint command is used.
Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`bank_database`.`account_customer`, CONSTRAINT `account_customer_ibfk_2` FOREIGN KEY (`SSN`) REFERENCES `customer` (`SSN`))
Before:
Response:
After:
Conclusion
Database management system is the most preferrable and important technology which has been used nowadays by every organization. Because of its capability of providing advance functionalities and features the way of handling the business and huge number of data become easier. In this report, a complete implementation of a bank database has been shown including all of its concepts. So, I conclude that all the above statements are true and can be used for further improvement and analysis.
Berski, S. and Bilau, M., 2019. Safety Mechanisms in Relational Database as Part of the IT System of the Enterprise. New Trends in Production Engineering, 2(2), pp.12-23.
Ceri, S., 2017. Distributed databases. Tata McGraw-Hill Education.
Jans, M. and Soffer, P., 2017, September. From relational database to event log: decisions with quality impact. In International Conference on Business Process Management (pp. 588-599). Springer, Cham.
Nidzwetzki, J.K. and Güting, R.H., 2017. Distributed secondo: an extensible and scalable database management system. Distributed and Parallel Databases, 35(3), pp.197-248.
Sahatqija, K., Ajdari, J., Zenuni, X., Raufi, B. and Ismaili, F., 2018, May. Comparison between relational and NOSQL databases. In 2018 41st international convention on information and communication technology, electronics and microelectronics (MIPRO) (pp. 0216-0221). IEEE.
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
