数据库实践 Hw02

Assignment 02: get familiar with operations in MySQL

创建数据库

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

create database if not exists bank;
show create database bank;
use bank;
show tables;
1
2
3
4

可以获得如下内容

后续展示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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121

最终结果如下

2. 导入数据

直接使用附件即可

3. 建立外链

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

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

直接使用下部分内容即可

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);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

结果

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

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

然后去下载数据字典的pdf