Table of Contents INTRODUCTION...........................................................................................................................1 TASK 1............................................................................................................................................1 1.1 Selection of a case study for designing and implementation of a data base.....................1 1.2 Creation of ER diagram with details................................................................................2 TASK 2............................................................................................................................................3 2.1 Logical database design according to the case study.......................................................3 2.2 Creation of database table................................................................................................7 2.3 Four useful indexes in tables............................................................................................9 2.4 Data population..............................................................................................................10 2.5 SQL query writing..........................................................................................................10 CONCLUSION..............................................................................................................................15
INTRODUCTION A database can simply be defined as a collection of data that is generally accessed or stored electronically from a computer systems. There are many types of database available out of which here oracle SQL developer has been used. In this assignment a database will be created based on operations of a bank. According to bank case study here six tables will be created with some data inserted in it. After insertion of data some SQL queries will be fired to achieve the result. TASK 1 1.1 Selection of a case study for designing and implementation of a data base For this database designing and implementation assignment a bank and its operations case study has been taken which consist of banker and their customer information (Nguyen and et.al.,2018). It also consists of Transaction, account details, credit card details and loan details (if taken by customers). It has one generalisation/specialization structure i.e. customer has a credit card and an account. Generalisationis a process of extracting shared characteristics from more than one classes. Those classes can also be combined into a generalized super class. It occurs when differences between two classes is ignored and similarities between lower entities are acknowledged to form a higher entity. It is a bottom to up process (Eastman, 2018). Specialisationis a process of creating sub classes from an existing class. It is a top to down process. It also splits an entity to form multiple new entities that inherit some features of splitting entity. It is applied on single entity. (a) Relevant background information on the organization: A bank is an organization which involves saving, borrowing and lending of money to its customers. They provide customers loan of a fixed amount, offers to save their deposits in return for paying an annual interest payment to customers. A bank has many operations but its main principle operations are cash withdraw, cash deposit, providing loan and saving deposit of customers. Its target customers are lower value, middle value and higher vale customers, also all the customers who has closed their accounts in the bank (Pei and et.al., 2018). (b) operations supported by customers This database support transaction, account, providing credit card, loan related operation of all the customers. 1
1.2 Creation of ER diagram with details In this ER diagram there are five strong entities and one weak entity. Banker, customer, account, loan and credit card are the strong entities and transaction is the weak entity as it is dependent on the customer(Zhang and et.al., 2018). There are many entities in this diagram that consist of a primary key like EntitiesAttributes Bankerbanker_id ( Primary Key ) Banker_email banker-name Customerscust_id ( Primary key ) cust_name cust_address account_no 2
Paraphrase This Document
Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
Accountaccount no( Primary key ) balance account_type Credit cardcard_no ( Primary key ) cust_id limit Loanloan_id ( Primary key ) loan_amount account_no Transactionatm_no ( Primary key ) withdraw_cash cust_id Relationship between all the entities are shown in the ER diagram above. TASK 2 2.1 Logical database design according to the case study Logical database designing is a process to decide the arrangement of attributes of all the entities into database (Hoang and et.al., 2018). It reflects proper structured tables of database that reflect companies environment. In this database all the logical database design factors have been considered like primary key and foreign keys in all the tables. 3
4
5
Secure Best Marks with AI Grader
Need help grading? Try our AI Grader for instant feedback on your assignments.
2.2 Creation of database table All the tables in the database has been created according to the logical database design. Appropriate order of table creation has been considered like tables with primary keys have been created first and then tables with foreign keys have been created. Create table banker Create table account 7
Create table transaction 2.3 Four useful indexes in tables 9
2.4 Data population Each table has some set of data stored in it. Each row in a set is a member of set and those sets have some data stored in it. Populate in database simply means to add record into the tables in database (Hung and et.al., 2018). It can be any kind of data i.e. whether inserting actual data into the tables for implementation or some dummy data for testing. That large amount of data present in database is called data population. In this database each row has five rows with some data inserted into them. 2.5 SQL query writing Selection of particular table columns 10
Inner Join of at least 2 tables 11
Secure Best Marks with AI Grader
Need help grading? Try our AI Grader for instant feedback on your assignments.
A condition using IN, NOT NULL, or similar. CONCLUSION In this assignment database is created successfully with six different tables inserted in it. In each table five rows had been added. Each table has a primary key and some table's primary keys has been used as a foreign key in another tables. 16
REFERENCES Books and journals: Eastman, C.M., 2018.Building product models: computer environments, supporting design and construction. CRC press. Hoang, V.D and et.al., 2018, March. Improving Traffic Signs Recognition Based Region Proposal and Deep Neural Networks. InAsian Conference on Intelligent Information and Database Systems(pp. 604-613). Springer, Cham. Hung, N.V. and et.al.,2018. Database Design For E-Governance Applications: A Framework For The Management Information Systems Of The Vietnam Commitee For Ethnic MinorityAffairs(CEMA).InternationalJournalofCivilServiceReformand Practice,3(1). Nguyen, N.T. And et.al.,2018.Intelligent Information and Database Systems: 10th Asian Conference,ACIIDS2018,DongHoiCity,Vietnam,March19-21,2018, Proceedings(Vol. 10751). Springer. Pei, J and et.al., 2018.Database Systems for Advanced Applications. Springer. Zhang, M. and et.al., 2018. Workload Management in Database Management Systems: A Taxonomy.IEEE Transactions on Knowledge and Data Engineering,30(7), pp.1386- 1402. 17