Bank Database

 Entity-Relationship Diagram

 

Schemas

Branch-schema = (branch-name, assets, branch-city)

branch-name is the name of a branch of the bank.

assets is the amount of money at the branch.

branch-city is the city at which the branch of the bank is located.

branch-name assets branch-city
Clear Water Bay 6,000,000 Hong Kong
Downtown 12,000,000 Edmonton
Howe 9,000,000 Vancouver
McEwan 6,000,000 Calgary
Signal Hill 8,000,000 Calgary
Tsuen Wan 8,000,000 Hong Kong

Back to Entity-Relationship Diagram

 

Loan-schema = (branch-name, loan-number, amount)

branch-name is the name of a branch which grants the loan.

loan-number is the identifier of the loan.

amount is the amount of money in the loan.

branch-name loan-number amount
Clear Water Bay 1 150,000
Howe 2 20,000
Howe 3 600,000
McEwan 4 80,000
Signal Hill 5 90,000
Signal Hill 6 20,000
Downtown 7 30,000
Downtown 8 10,000
Clear Water Bay 9 250,000
Tsuen Wan 10 10,000

Back to Entity-Relationship Diagram

 

Borrower-schema = (customer-ID, loan-number)

customer-ID is the identifier of the customer of the bank

loan-number is the identifier of the loan that the customer borrows in the bank.

customer-id loan-number
C8392380567 1
C8392380567 9
C8954385123 10
C2389490434 4
C9384899234 5
C0930238083 6
C3487327487 7
C3249893849 8
C3948938442 3
C2394893848 2

Back to Entity-Relationship Diagram

 

Customer-schema = (customer-ID, customer-name, customer-street, customer-city)

customer-ID is the identifier of a customer of the bank

customer-name is the name of the customer

customer-street is the street in the address of the customer

customer-city is the city in the address of the customer

customer-id customer-name customer-street customer-city
C8392380567 Amy Ping Ting Road Hong Kong
C8954385123 Bob Texaco Road Hong Kong
C2389490434 Carson Deerfoot Trail Calgary
C9384899234 David Unversity Drive Calgary
C0930238083 John Ogden Road Calgary
C3487327487 Linda Kingsway Drive Edmonton
C3249893849 Mandy Victoria Road Edmonton
C3948938442 Paul Hornby Road Vancouver
C2394893848 Ricky Howe Road Vancouver

Back to Entity-Relationship Diagram

 

Credit-card-schema = (credit-card-number, limit, expiry-date)

credit-card-number is the number printed on a credit card issued by the bank to a customer

limit is the maximum amount of money that a customer can consume using the credit card

expiry-date is the expiry date of the credit card

credit-card-number limit expiry-date
0000-1111-1111-1111 $5,000.00 1-Mar-02
0000-1111-1111-2222 $10,000.00 1-Dec-05
0000-2222-2222-2222 $5,000.00 1-Dec-03
0000-3333-3333-3333 $3,000.00 1-Nov-04
0000-4444-4444-4444 $20,000.00 1-Jan-03
0000-5555-5555-5555 $10,000.00 1-Jan-05
0000-6666-6666-6666 $10,000.00 1-Apr-04
0000-7777-7777-7777 $10,000.00 1-Jan-05
0000-8888-8888-8888 $20,000.00 1-Jan-02
0000-9999-9999-0000 $3,000.00 1-Jan-03
0000-9999-9999-1111 $10,000.00 1-Jan-02

Back to Entity-Relationship Diagram

 

Customer-credit-card-schema = (customer-ID, credit-card-number, account-number)

customer-ID is the identifier of a customer of the bank

credit-card-number is the number printed on the credit card issued by the bank to the customer

account-number is the number of a saving account owned by the customer in the bank

customer-id

credit-card-number

account-number

C8392380567

0000-1111-1111-1111

0-1-2222-3333-4444

C8954385123

0000-2222-2222-2222

0-1-2222-2222-2222

C2389490434

0000-3333-3333-3333

0-1-3333-3333-3333

C9384899234

0000-4444-4444-4444

0-1-4444-4444-4444

C0930238083

0000-5555-5555-5555

0-1-5555-5555-5555

C3487327487

0000-6666-6666-6666

0-1-6666-6666-6666

C3249893849

0000-7777-7777-7777

0-1-7777-7777-7777

C3948938442

0000-8888-8888-8888

0-1-8888-8888-8888

C2394893848

0000-9999-9999-0000

0-1-9999-9999-0000

Back to Entity-Relationship Diagram

 

Account-schema = (account-number, category, balance)

account-number is the number of a saving account owned by a customer.

category is the category of currency of the saving account such as HK dollars and foreign currency.

balance is the current balance of the amount of money in the saving account owned by a customer

account-number category balance
0-1-2222-3333-4444 Hong Kong dollars 10,000.00
0-1-2222-3333-5555 Hong Kong dollars 500,000.00
0-1-2222-2222-2222 Hong Kong dollars 2,000.00
0-1-3333-3333-3333 Canadian dollars 1,000.00
0-1-4444-4444-4444 Canadian dollars 5,000.00
0-1-5555-5555-5555 Canadian dollars 5,000.00
0-1-6666-6666-6666 Canadian dollars 3,000.00
0-1-7777-7777-7777 Canadian dollars 2,000.00
0-1-8888-8888-8888 Canadian dollars 1,000.00
0-1-9999-9999-0000 Canadian dollars 1,000.00
0-1-9999-9999-1111 Canadian dollars 2,000.00

Back to Entity-Relationship Diagram

 

Banker-info-schema = (banker-ID, banker-name, banker-email)

banker-ID is the identifier of the banker working in the bank

banker-name is the name of the banker working in the bank

banker-email is the email of the banker working in the bank

banker-id banker-name  branch-email 
sta_rosana Rosana rosana@abcbank.com
sta_carina Carina carina@abcbank.com
sta_gloria Gloria gloria@abcbank.com
sta_elaine Elaine elaine@abcbank.com
sta_wynne Wynne wynne@abcbank.com
sta_shirley Shirley shirley@abcbank.com
sta_kadora Kadora kadora@abcbank.com

Back to Entity-Relationship Diagram

 

Banker-schema = (branch-name, customer-ID, banker-ID)

branch-name is the name of the branch that the banker works in

customer-ID is the identifier of the customer that has the financial advisor (“personal banker”) in the branch

banker-ID is the identifier of the banker in the branch, who provides financial advice to the customer

branch-name customer-id banker-id
Clear Water Bay C8392380567 sta_rosana
Downtown C3487327487 sta_carina
Howe C3948938442 sta_gloria
McEwan C9384899234 sta_elaine
Signal Hill C0930238083 sta_wynne
Signal Hill C8954385123 sta_shirley
Tsuen Wan C8954385123 sta_kadora

                              Back to Entity-Relationship Diagram

 

Back to home