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