用于演示的Bank方案对应的SQL：
/*
1、branch
开展银行交易业务的场所
*/
DROP TABLE IF EXISTS branch;
CREATE TABLE branch -- 开展银行交易业务的场所
(
branch_id SMALLINT UNSIGNED PRIMARY KEY,
name varchar(20),
address varchar(20),
city varchar(20),
state varchar(2),
zip varchar(12)
);
/*
1、product_type
具有相似功能的产品的分组
*/
DROP TABLE IF EXISTS product_type;
CREATE TABLE product_type
(
product_type_cd varchar(10) PRIMARY KEY,
name varchar(50)
);
/*
3、department
执行特定职能的雇员分组
*/
DROP TABLE IF EXISTS department;
CREATE TABLE department
(
dept_id SMALLINT UNSIGNED PRIMARY KEY,
name VARCHAR(20)
);
/*
4、customer
与银行有业务往来的个人或公司
*/
DROP TABLE IF EXISTS customer;
CREATE TABLE customer
(
cust_id INTEGER UNSIGNED PRIMARY KEY,
fed_id VARCHAR(12),
cust_type_cd VARCHAR(2),
address VARCHAR(30),
city VARCHAR(20),
state VARCHAR(20),
postal_code VARCHAR(10)
);
/*
5、product
向客户提供的银行服务
*/
DROP TABLE IF EXISTS product;
CREATE TABLE product
(
product_cd VARCHAR(10) PRIMARY KEY,
name VARCHAR(50),
product_type_cd VARCHAR(10),
date_offered date,
date_retired date,
CONSTRAINT fk_product_type_cd FOREIGN KEY (product_type_cd) REFERENCES product_type (product_type_cd)
);
/*
6、employee
银行的工作人员
*/
DROP TABLE IF EXISTS employee;
CREATE TABLE employee
(
emp_id SMALLINT UNSIGNED PRIMARY KEY,
fname VARCHAR(20),
lname VARCHAR(20),
start_date DATE,
end_date DATE,
superior_emp_id SMALLINT UNSIGNED, -- FK: 关联到 自己的 emp_id
dept_id SMALLINT UNSIGNED, -- FK: 关联到 department表的 dept_id
title VARCHAR(20),
assigned_branch_id SMALLINT UNSIGNED, -- FK: 关联到 branch表的 branch_id
CONSTRAINT fk_superior_emp_id FOREIGN KEY (superior_emp_id) REFERENCES employee (emp_id),
CONSTRAINT fk_dept_id FOREIGN KEY (dept_id) REFERENCES department (dept_id),
CONSTRAINT fk_assigned_branch_id FOREIGN KEY (assigned_branch_id) REFERENCES branch (branch_id)
);
/*
7、account
为特定顾客开放的特定产品
*/
DROP TABLE IF EXISTS account;
CREATE TABLE account
(
account_id INTEGER UNSIGNED PRIMARY KEY,
product_cd VARCHAR(10), -- FK: 关联到 product表的 product_cd
cust_id INTEGER UNSIGNED, -- FK: 关联到 customer表的 cust_id
open_date DATE,
close_date DATE,
last_activity_date DATE,
status VARCHAR(10),
open_branch_id SMALLINT UNSIGNED, -- FK: 关联到 branch表的 branch_id
open_emp_id SMALLINT UNSIGNED, -- FK: 关联到 employee表的 emp_id
avail_balance FLOAT(10,2),
pending_balance FLOAT(10,2),
CONSTRAINT fk_product_cd FOREIGN KEY (product_cd) REFERENCES product (product_cd),
CONSTRAINT fk_cust_id FOREIGN KEY (cust_id) REFERENCES customer (cust_id),
CONSTRAINT fk_open_branch_id FOREIGN KEY (open_branch_id) REFERENCES branch (branch_id),
CONSTRAINT fk_open_emp_id FOREIGN KEY (open_emp_id) REFERENCES employee (emp_id)
);
/*
8、transaction
改变账户余额的操作
*/
DROP TABLE IF EXISTS transaction;
CREATE TABLE transaction
(
txn_id INTEGER UNSIGNED PRIMARY KEY,
txn_date DATETIME,
account_id INTEGER UNSIGNED, -- FK: 关联 account表的 account_id
txn_type_cd VARCHAR(10),
amount DOUBLE(10,2),
teller_emp_id SMALLINT UNSIGNED, -- FK: 关联 employee表的 emp_id
execution_branch_id SMALLINT UNSIGNED, -- FK: 关联 branch表的 branch_id
funds_avail_date DATETIME,
CONSTRAINT fk_account_id FOREIGN KEY (account_id) REFERENCES account (account_id),
CONSTRAINT fk_teller_emp_id FOREIGN KEY (teller_emp_id) REFERENCES employee (emp_id),
CONSTRAINT fk_execution_branch_id FOREIGN KEY (execution_branch_id) REFERENCES branch (branch_id)
);
/*
9、business
公司顾客（customer的子表）
*/
DROP TABLE IF EXISTS business;
CREATE TABLE business
(
cust_id INTEGER UNSIGNED PRIMARY KEY, -- FK: 关联 customer表的 cust_id
name VARCHAR(40),
state_id VARCHAR(10),
incorp_date DATE,
CONSTRAINT fk_business_cust_id FOREIGN KEY (cust_id) REFERENCES customer (cust_id)
);
/*
10、officer
允许为公司客户发起商务交易的人
*/
DROP TABLE IF EXISTS officer;
CREATE TABLE officer
(
officer_id SMALLINT UNSIGNED PRIMARY KEY,
cust_id INTEGER UNSIGNED, -- FK
fname VARCHAR(30),
lname VARCHAR(30),
title VARCHAR(20),
start_date DATE,
end_date DATE,
CONSTRAINT fk_officer_cust_id FOREIGN KEY (cust_id) REFERENCES business (cust_id)
);
/*
11、individual
个人顾客（customer表的子类型）
*/
DROP TABLE IF EXISTS individual;
CREATE TABLE individual
(
cust_id INTEGER UNSIGNED PRIMARY KEY, -- FK
fname VARCHAR(30),
lname VARCHAR(30),
birth_date DATE,
CONSTRAINT fk_individual_cust_id FOREIGN KEY (cust_id) REFERENCES customer (cust_id)
);