Bank方案SQL

用于演示的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)
);