Reference no: EM13949783

The Database I am creating is a Bank Database. I am using a very simple bank database, which has few tables and will provide very limited capabilities to the bank.

Bank Database:

The bank database I have created has 4 tables: Branch, Account, Depositor and Customer

The details (attributes) of the bank databases are as shown:

i) The branch table contain information about the branch location.

ii) The Account table has information about the balance and the account holder and where he is having a account(which branch).

iii) The Depositor table has information about which Account is associated with which customer.

iv) The customer table has information about the customer like his name, address etc.

Branch: Branch_name, Branch_city, City_code

Account: Account_number, Branch_name, Balance

Depositor: Customer_name, Account_number

Customer: Customer_name, Customer_street, Customer_city, Phone_number

The underlined attributes are the primary key above.
The SQL statements for creating the above tables are as following:

Branch : Create table branch
(branch_name char(20)
Branch_city char(30)
City_code int
Primary key (branch_name))

Account : Create table account
(account_number char(10)
Branch_name char(20)
balance int
Primary key (account_number))

Depositor : Create table depositor
(customer_name char(20)
account_number char(10))

Customer : Create table customer
(customer_name char(20)
customer_street char(30)
customer_city char(30)
phone_number char(10)

Primary key (customer_name))
This database may provide the following information:

i) The balance present in a customer's Account.
ii) The information about a customer holding a particular Account number X .
iii) How many Customers in the bank has balance less than a some amount Y.

The SQL statements for the above are as follow:

i) To find the balance in account number X

Select balance from from Account where account_number = X

ii) The information about a customer holding a particular Account number X .

Select customer_name, customer_street, customer_city, phone_number from
Customer where customer_name = (select customer name from Depositor
where account_number = X)

iii) How many Customers in the bank has balance less than a some amount Y.

select count(account_number) from Account where balance < Y

Sample Set :

i) Select balance from from Account where account_number = 0111123323

Output :: 2000

ii) Select customer_name, customer_street, customer_city, phone_number from
Customer where customer_name = (select customer name from Depositor
where account_number = 0111123456)

Output :: Customer_name - Hector
Customer_street - bbb
Customer_city - XYZ
Phone_number -- 1982918291

iii) select count(account_number) from Account where balance < 3000

Output :: 2