数据库实践 Hw02

Assignment 02: get familiar with operations in MySQL

创建数据库

创建名字为bank的银行数据库

create database if not exists bank;
show create database bank;
use bank;
show tables;

可以获得如下内容

后续展示bank数据库中的tables

tables为空

bank数据中创建表

根据题目要求

1. 创建

按照题目要求创建第一个表，展示后发现成功创造

后续按照要求继续创造即可

create table if not exists acc_transaction
(
    TXN_ID bigint(20) AUTO_INCREMENT not null,
    # AUTO_INCREMENT表示这是一个自增数据，不设置第一个数据默认为0
    # 由于该参数一定会填数据
    AMOUNT decimal(12, 4) not null,
    # not null 表示非空
    TXN_DATE datetime not null,
    TXN_TYPE_CD varchar(10),
    ACCOUNT_ID int(11),
    EXECUTION_BRANCH_ID int(11),
    TELLER_EMP_ID int(11),
    primary key(TXN_ID)
);

create table if not exists account
(
    ACCOUNT_ID int(11) AUTO_INCREMENT not null,
    AVAIL_BALANCE decimal(12, 4),
    CLOSE_DATE date,
    LAST_ACTIVITY_DATE date,
    OPEN_DATE date not null,
    STATUS varchar(10),
    CUST_ID int,
    OPEN_BRANCH_ID int not null,
    OPEN_EMP_ID int not null,
    PRODUCT_CD varchar(10) not null,
    primary key(ACCOUNT_ID)
);

create table if not exists branch
(
    BRANCH_ID int(11) AUTO_INCREMENT not null,
    ADDRESS varchar(100),
    CITY varchar(20),
    NAME varchar(20) not null,
    PROVICIAL_REGION varchar(30),
    POSTAL_CODE varchar(10),
    primary key (BRANCH_ID)
);

create table if not exists business
(
    INCORP_DATE date,
    NAME varchar(255) not null,
    CREDIT_CODE varchar(20) not null,
    CUST_ID int(11) not null
);

create table if not exists customer
(
    CUST_ID int(11) AUTO_INCREMENT not null,
    ADDRESS varchar(30),
    CITY varchar(20),
    CUST_TYPE_CD varchar(1) not null,
    PROVICIAL_REGION varchar(30),
    POSTAL_CODE varchar(10),
    primary key (CUST_ID)
);

create table if not exists department
(
    DEPT_ID int(11) AUTO_INCREMENT not null,
    NAME varchar(20) not null,
    primary key (DEPT_ID)
);

create table if not exists employee
(
    EMP_ID int(11) AUTO_INCREMENT not null,
    END_DATE date,
    FIRST_NAME varchar(20) not null,
    LAST_NAME varchar(20) not null,
    START_DATE date not null,
    TITLE varchar(20),
    ASSIGNED_BRANCH_ID int(11),
    DEPT_ID int(11),
    SUPERIOR_EMP_ID int(11),
    primary key (EMP_ID)
);

create table if not exists individual
(
    ID_NUMBER varchar(20) not null,
    BIRTH_DATE date,
    FIRST_NAME varchar(30) not null,
    LAST_NAME varchar(30) not null,
    CUST_ID int(11) not null,
    primary key (CUST_ID)
);

create table if not exists officer
(
    OFFICER_ID int(11) AUTO_INCREMENT not null,
    END_DATE date,
    FIRST_NAME varchar(30) not null,
    LAST_NAME varchar(30) not null,
    START_DATE date not null,
    TITLE varchar(100),
    CUST_ID int(11),
    primary key (OFFICER_ID)
);

create table if not exists product
(
    PRODUCT_CD varchar(10) not null,
    DATE_OFFERED date,
    DATE_RETIRED date,
    NAME varchar(50) not null,
    PRODUCT_TYPE_CD varchar(255),
    primary key (PRODUCT_CD)
);

create table if not exists product_type
(
    PRODUCT_TYPE_CD varchar(255) not null,
    NAME varchar(50),
    primary key (PRODUCT_TYPE_CD)
);

show tables;

最终结果如下

2. 导入数据

直接使用附件即可

3. 建立外链

通过上万查询，发现建立外链的通识表达如下

alter table 表名 add constraint 约束名称 约束类型 (列名) references 被引用的表名称（列名）

直接使用下部分内容即可

alter table acc_transaction add constraint acc_id foreign key(ACCOUNT_ID) references account(ACCOUNT_ID);
alter table acc_transaction add constraint exe_id foreign key(EXECUTION_BRANCH_ID) references branch(BRANCH_ID);
alter table acc_transaction add constraint tel_id foreign key(TELLER_EMP_ID) references employee(EMP_ID);

alter table account add constraint a_cus_id foreign key(CUST_ID) references customer(CUST_ID);
alter table account add constraint opb_id foreign key(OPEN_BRANCH_ID) references branch(BRANCH_ID);
alter table account add constraint ope_id foreign key(OPEN_EMP_ID) references employee(EMP_ID);
alter table account add constraint pro_cd foreign key(PRODUCT_CD) references product(PRODUCT_CD);

alter table business add constraint b_cus_id foreign key(CUST_ID) references customer(CUST_ID);

alter table employee add constraint ass_b_id foreign key(ASSIGNED_BRANCH_ID) references branch(BRANCH_ID);
alter table employee add constraint dep_id foreign key(DEPT_ID) references department(DEPT_ID);
alter table employee add constraint sup_id foreign key(SUPERIOR_EMP_ID) references employee(EMP_ID);

alter table individual add constraint i_cus_id foreign key(CUST_ID) references customer(CUST_ID);

alter table officer add constraint o_cus_id foreign key(CUST_ID) references customer(CUST_ID);

alter table product add constraint pro_t_cd foreign key(PRODUCT_TYPE_CD) references product_type(PRODUCT_TYPE_CD);

结果

最后点击 Database -> Reserve Engineering 来生成bank tables的ER图

PS：针对GaussDB，把除了create_bank的文件外的所有操作再执行一遍即可

然后去下载数据字典的pdf