实验资料 实验源码

实验目的

  1. 巩固关系数据模型及其基本概念;
  2. 掌握使用SQL创建关系模式;
  3. 掌握使用SQL创建主键和外键约束;
  4. 掌握关系模式与ER模型的转换。

实验原理

  1. 采用PostgreSQL数据库作为实验用DBMS;
  2. 用SQL创建数据库模式;
  3. 用SQL创建主键和外键约束;
  4. 将关系模式转换为ER模型并绘制ER图。

实验内容

详情见实验指导书

实验步骤

  1. 建表
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
CREATE TABLE bmsql_warehouse(
w_id INT NOT NULL,
w_ytd DECIMAL(12,2),
w_tax DECIMAL(4,4),
w_name VARCHAR(10),
w_street_1 VARCHAR(20),
w_street_2 VARCHAR(20),
w_city VARCHAR(20),
w_state CHAR(2),
w_zip CHAR(9)
);
CREATE TABLE bmsql_district(
d_w_id INT NOT NULL,
d_id INT NOT NULL,
d_ytd DECIMAL(12,2),
d_tax DECIMAL(4,4),
d_next_o_id int,
d_name VARCHAR(10),
d_street_1 VARCHAR(20),
d_street_2 VARCHAR(20),
d_city VARCHAR(20),
d_state CHAR(2),
d_zip CHAR(9)
);
CREATE TABLE bmsql_customer(
c_w_id INT NOT NULL,
c_d_id INT NOT NULL,
c_id INT NOT NULL,
c_discount DECIMAL(4,4),
c_credit CHAR(2),
c_last VARCHAR(16),
c_first VARCHAR(16),
c_credit_lim DECIMAL(12,2),
c_balance DECIMAL(12,2),
c_ytd_payment DECIMAL(12,2),
c_payment_cnt INT,
c_delivery_cnt INT,
c_street_1 VARCHAR(20),
c_street_2 VARCHAR(20),
c_state CHAR(2),
c_zip CHAR(9),
c_phone CHAR(16),
c_since TIMESTAMP,
c_middle CHAR(2),
c_data VARCHAR(500)
);
CREATE TABLE bmsql_history(
hist_id INT,
h_c_id INT,
h_c_d_id INT,
h_c_w_id INT,
h_d_id INT,
h_w_id INT,
h_date TIMESTAMP,
h_amount DECIMAL(6,2),
h_data VARCHAR(24)
);
CREATE TABLE bmsql_new_order(
no_w_id INT NOT NULL,
no_d_id INT NOT NULL,
no_o_id INT NOT NULL
);
CREATE TABLE bmsql_oorder(
o_w_id INT NOT NULL,
o_d_id INT NOT NULL,
o_id INT NOT NULL,
o_c_id INT
);
CREATE TABLE bmsql_order_line(
ol_w_id INT NOT NULL,
ol_d_id INT NOT NULL,
ol_o_id INT NOT NULL,
ol_number INT NOT NULL,
ol_i_id INT NOT NULL,
ol_delivery_d TIMESTAMP,
ol_amount DECIMAL(6,2),
ol_supply_w_id INT,
ol_quantity INT,
ol_dist_info CHAR(24)
);
CREATE TABLE bmsql_item(
i_id INT NOT NULL,
i_name VARCHAR(24),
i_price DECIMAL(5,2),
i_data VARCHAR(50),
i_im_id INT
);
CREATE TABLE bmsql_stock(
s_w_id INT NOT NULL,
s_i_id INT NOT NULL,
s_quantity INT,
s_ytd INT,
s_order_cnt INT,
s_remote_cnt INT,
s_data VARCHAR(50),
s_dist01 CHAR(24),
s_dist02 CHAR(24),
s_dist03 CHAR(24),
s_dist04 CHAR(24),
s_dist05 CHAR(24),
s_dist06 CHAR(24),
s_dist07 CHAR(24),
s_dist08 CHAR(24),
s_dist09 CHAR(24),
s_dist10 CHAR(24)
);
  1. 建立约束
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
-- Primary Key
-- Correct
-- 1
alter table bmsql_warehouse add constraint bmsql_warehouse_pkey
primary key (w_id);
-- 2
alter table bmsql_district add constraint bmsql_district_pkey
primary key (d_w_id, d_id);
-- 3
alter table bmsql_customer add constraint bmsql_customer_pkey
primary key (c_w_id, c_d_id, c_id);
-- 4
alter table bmsql_oorder add constraint bmsql_oorder_pkey
primary key (o_w_id, o_d_id, o_id);
-- 5
alter table bmsql_new_order add constraint bmsql_new_order_pkey
primary key (no_w_id, no_d_id, no_o_id);
-- 6
alter table bmsql_order_line add constraint bmsql_order_line_pkey
primary key (ol_w_id, ol_d_id, ol_o_id, ol_number);
-- 7
alter table bmsql_stock add constraint bmsql_stock_pkey
primary key (s_w_id, s_i_id);
-- 8
alter table bmsql_item add constraint bmsql_item_pkey
primary key (i_id);

-- Foreign Key
-- Correct
-- 1
alter table bmsql_district add constraint d_warehouse_fkey
foreign key (d_w_id)
references bmsql_warehouse (w_id);
-- 2
alter table bmsql_customer add constraint c_district_fkey
foreign key (c_w_id, c_d_id)
references bmsql_district (d_w_id, d_id);
-- 3
alter table bmsql_history add constraint h_customer_fkey
foreign key (h_c_w_id, h_c_d_id, h_c_id)
references bmsql_oorder (o_w_id, o_d_id, o_id);
-- 4
alter table bmsql_history add constraint h_district_fkey
foreign key (h_w_id, h_d_id)
references bmsql_district (d_w_id, d_id);
-- 5
alter table bmsql_new_order add constraint no_order_fkey
foreign key (no_w_id, no_d_id, no_o_id)
references bmsql_oorder (o_w_id, o_d_id, o_id);
-- 6
alter table bmsql_oorder add constraint o_customer_fkey
foreign key (o_w_id, o_d_id, o_c_id)
references bmsql_customer (c_w_id, c_d_id, c_id);
-- 7
alter table bmsql_order_line add constraint ol_order_fkey
foreign key (ol_w_id, ol_d_id, ol_o_id)
references bmsql_oorder (o_w_id, o_d_id, o_id);
-- 8
alter table bmsql_order_line add constraint ol_stock_fkey
foreign key (ol_supply_w_id, ol_i_id)
references bmsql_stock (s_w_id, s_i_id);
-- 9
alter table bmsql_stock add constraint s_warehouse_fkey
foreign key (s_w_id)
references bmsql_warehouse (w_id);
-- 10
alter table bmsql_stock add constraint s_item_fkey
foreign key (s_i_id)
references bmsql_item (i_id);

实验结果

E/R模型图

1

实验总结

经过此次实验,知道在实践中可能需要大量使用SQL语句或交互页面建立主键与外键。每个表的属性很多,非常容易导致思路混乱。可以建立约束前先画好E/R图理清思路,帮助自己更好地且效率更高的完成工作。