案例留学生数据库编程英文UML+设计题目:Bank Database Design Document
当前位置:以往案例 > >案例留学生数据库编程英文UML+设计题目:Bank Database Design Document
2018-07-19

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.

1.jpg1.jpg

P1: Bank ER Diagram

2.png

P2: Physical Data Model


Table Structures And Constraints

Table account

3.png

P3: Account Structures

4.png

P4: Account Constraints

Table branch

5.png

P5: Branch Structures

6.png

P6: Branch Constraints

Table branchAddress

7.png

P7: BranchAddress Structures

8.png

P8: BranchAddress Constraints

Table customer

9.png

P9: Customer Structures

10.png

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


在线提交订单