Bank Database Design Document
Description And Assumption
I named this bank “GreatBank”. GreatBank is composed of several branches and every branch has a number of employees, distributed in different departments. I assume that the sex and race attributes of employee are enumerable and so I store them as integer. As for sex, ‘0’ stands for unknown sex, ‘1’ stands for man and ‘2’ stands for woman. Race is divided into white(‘0’) and black(‘1’). I assume that there are only one branch in the same street while there may be number of employees in the same street.
ER Diagram
P1 is the ER diagram. There are seven entities in total and eight relations, among which four relations are with additional attributes. The functionality is shown in the picture.
PDM Diagram
P2 is the physical date model diagram of the bank system. There are ten tables in total. The primary and foreign keys for each table are shown in the diagram.
P1: Bank ER Diagram
P2: Physical Data Model
Table Structures And Constraints
Table account
P3: Account Structures
P4: Account Constraints
Table branch
P5: Branch Structures
P6: Branch Constraints
Table branchAddress
P7: BranchAddress Structures
P8: BranchAddress Constraints
Table customer
P9: Customer Structures
P10: Customer Constraints
Queries
1.
l Command: select * from "employeeTitle";
l Desc: Query all of the title positions in the bank system
2.
l Command: select * from "branch";
l Desc: Query all of the branches in the bank system
3.
l Command: select * from "customer";
l Desc: Query all of the customers in the bank system
4.
l Command: select * from "loan";
l Desc: Query all of the loan records in the bank system
5.
l Command: select * from "account";
l Desc: Query all of the accounts existed in the bank system
6.
l Command: select * from " loan " where " customer_id" = 1;
l Desc: Query all of the loan records of customer with customer_id 1 in the bank system
7.
l Command: select * from " account " where " customer_id" = 1;
l Desc: Query all of the account of customer with customer_id 1 in the bank system
8.
l Command: select * from " employee " where " branch_id" = 1;
l Desc: Query all of the employees hired by branch with branch_id 1
9.
l Command: select * from " department " where " branch_id" = 1;
l Desc: Query all of the departments of branch with branch_id 1
10.
l Command: select * from " loan " where " branch_id" = 1;
l Desc: Query all of the loan records issued by branch with branch_id 1 in the bank system
11.
l Command: select * from "employee" group by employeeTitle_id;
l Desc: Query the distribution of every title position
12.
l Command: select * from "employee" group by employee_highest_degress;
l Desc: Query the distribution of degree of all the employees in the bank system
13.
l Command: select * from "loan" group by branch_id;
l Desc: Query all of the loan records of every branch
14.
l Command: select * from "transact" group by customer_id;
l Desc: Query all of the transact records of every customer
15.
l Command: select * from "transact" group by transact_type;
l Desc: Query all of the records of differnet transact type
16.
l Command: select * from "transact" group by customer_id having sum(transact_amount) > 2000;
l Desc: Query all of the transact records of customers with total amount greater than 2000
17.
l Command: select * from "department" having department_budget > 10000;
l Desc: Query all of the departments with budget greater than 10000
18.
l Command: select * from "account" having account_balance > 20000;
l Desc: Query all of the accounts with balance greater than 20000
19.
l Command: select * from "loan" having loan_amount > 10000;
l Desc: Query all of the loan records with amount greater than 10000
20.
l Command: select * from "account" having account_deposit > 10000;
l Desc: Query all of the accounts with initial deposit greater than 10000
21.
l Command: select * from "account" order by account_balance;
l Desc: Query all of the accounts ordered by balance
22.
l Command: select * from "account" order by account_opened_date;
l Desc: Query all of the accounts ordered by opened date
23.
l Command: select * from "customer" order by customer_card_exp_date;
l Desc: Query all of the customers ordered by the card expiration date
24.
l Command: select * from "loan" order by loan_amount;
l Desc: Query all of the loan records ordered by amount
25.
l Command: select * from "loan" order by loan_issued_date;
l Desc: Query all of the loan records ordered by issued date
26.
l Command: select branch_name from "branch";
l Desc: Query all of the branch name
27.
l Command: select employeeTitle_title_name from "employeeTitle";
l Desc: Query all of the employee titles in the bank system
28.
l Command: select * from "employee" where branch_id = 1;
l Desc: Query all of the employees in the branch with id 1
29.
l Command: select * from "loan";
l Desc: Query all of the loan records
30.
l Command: select * from " employee " where employee_manager_id = 2;
l Desc: Query all of the employees managed by manager with id 2
31.
l Command: select * from "employee" where employee_highest_degree = 1;
l Desc: Query all of the employees with highest degree of bachelor(corresponding to 1)
32.
l Command: select * from "employee" where employeeAddress_id = 1;
l Desc: Query all of the employees in the address with id 1
33.
l Command: select * from "department" where branch_id = 1;
l Desc: Query all of the departments of branch with id 1
34.
l Command: select * from "transcat" where customer_id = 1;
l Desc: Query all of the transact records of customer with id 1
35.
l Command: select * from "loan" where loan_type = 1;
l Desc: Query all of the loan records with type 1
36.
l Command: select sum(loan_amount) from "loan" where customer_id = 1
l Desc: Query the amount of loan by customer with id 1
37.
l Command: select count(loan_id) from "loan" where employee_id = 1;
l Desc: Query the number of loan records dealed by employee with id 1
38.
l Command: select count(employee_name) from "employee" where branch_id = 1;
l Desc: Query the number of employees hired by branch with id 1
39.
l Command: select sum(department_budget) from "department" where branch_id = 1;
l Desc: Query the amount of budget of all the departments in the branch with id 1
40.
l Command: select sum(transact_amount) from "transcat" where customer_id = 1
l Desc: Query the amount of transact by customer with id 1
41.
l Command: select * from “branch” natural join “branchAddress”
l Desc: Query all of the information of every branch
42.
l Command: select * from “employee” natural join “employeeTitle”
l Desc: Query the employee with title information
43.
l Command: select * from “employee” inner join “employeeAddress” on employee.employeeAddress_id = employeeAddress .employeeAddress_id
l Desc: Query the employee information with address information
44.
l Command: select * from “customer” inner join “account” on customer.customer_id = account.customer_id
l Desc: Query the account of the corresponding customer id
45.
l Command: select * from “customer” inner join “transcat” on customer.customer_id = transcat.customer_id
l Desc: Query the transact records of every customer
46.
l Command: select * from “customer” inner join “loan” on customer.customer_id = loan.customer_id
l Desc: Query the loan records of every customer
47.
l Command: select * from “customer” left outer join “loan” on customer.customer_id = loan.customer_id
l Desc: Query the loan records of every customer, showing the customer information even without records
48.
l Command: select * from “customer” left outer join “transcat” on customer.customer_id = transcat.customer_id
l Desc: Query the transcat records of every customer, showing the customer information even without records
49.
l Command: select * from “employee” left outer join “transcat” on employee. employee _id = transcat. employee _id
l Desc: Query the transcat records of every employee, showing the employee information even without records
50.
l Command: select * from “employee” F inner join “employee” s on f.employeeAddress_id = s.employeeAddress_id
l Desc: Query the employees living in the same address
51.
l Command: select * from “employee” where employee_id in (select employee_id from “loan” where loan_amout > 20000)
l Desc: Query the employees information who has dealed with loan greater than 20000
52.
l Command: select * from “customer” where customer_id in (select customer_id from “loan” where loan_amout > 20000)
l Desc: Query the customer information who has loaned greater than 20000
53.
l Command: select * from “customer” where customer_id in (select customer_id from “account” where account_balance> 20000)
l Desc: Query the customer information with balance greater than 20000
54.
l Command: select * from “employee” where employee_id in (select employee_id from “transcat” where transact_amout > 20000)
l Desc: Query the employees information who has dealed with transcat greater than 20000
55.
l Command: select * from “customer” where customer_id in (select customer_id from “transcat” where transcat _amout > 20000)
l Desc: Query the customer information who has transcated greater than 20000
56. Create View view1 as
Select branch_name, count(employee_id) from branch natural join employee
57. Create View view2 as
Select account_id,account_opened_date,account_balance,account_type, account_deposit, customer_name,customer_address from customer natural join account
58. Create View view3 as
Select customer_name,customer_address,customer_cell_phone,loan_amount from customer natural join loan
59. Create View view4 as
Select customer_name,transact_type, transact_date, transact_desc, transact_amount, transact_balance, transact_location from customer natural join transcat
60. Create View view5 as
Select * from customer natural join account
