原文 基于Sql Server 2008的分布式数据库的实践（四）
数据库设计
1.E-R图
2.数据库创建
Win 7
|
1
|
create
database
V3
|
Win 2003
|
1
|
create
database
V3
|
3.数据表设计
Win7 创建数据表student_7
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
create
table
student_7
(
sid
int
not
null
,
sex nvarchar(1)
not
null
,
sname
varchar
(20)
not
null
,
school
varchar
(20)
not
null
,
scount
varchar
(20)
not
null
,
spwd
varchar
(20)
not
null
,
constraint
pk_student_7
primary
key
(sid,sex),
constraint
uq_student_7_scount
unique
(scount),
constraint
chk_student_7_sex
check
(sex=
'1'
)
)
|
Check(sex=1)指明存放sex=1的数据，即女生。
Win2003 创建数据表student_2003
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
create
table
student_2003
(
sid
int
not
null
,
sex nvarchar(1)
not
null
,
sname
varchar
(20)
not
null
,
school
varchar
(20)
not
null
,
scount
varchar
(20)
not
null
,
spwd
varchar
(20)
not
null
,
constraint
pk_student_2003
primary
key
(sid,sex),
constraint
uq_student_2003_scount
unique
(scount),
constraint
chk_student_2003_sex
check
(sex=
'0'
)
)
|
Check(sex=0)指明存放sex=0的数据，即男生。
Win7 创建视图V3_student
|
1
2
3
4
5
|
create
view
V3_student
as
select
*
from
student_7
union
all
select
*
from
[192.168.116.130].[V3].[dbo].[student_2003]
|
Win2003 创建视图V3_student
|
1
2
3
4
5
|
create
view
V3_student
as
select
*
from
student_2003
union
all
select
*
from
[192.168.233.1].[V3].[dbo].[student_7]
|
student水平分片数据表已经建立，现在可以在任何位置，只要访问本地V3_student分布式分区视图，就实现了所有分布式数据库的操作。此时，对数据库的全局操作和局部操作就如同操作本地集中式数据库一样。
-----------------------------------------------------------------------------------------------------------------
Win7创建数据表teacher
|
1
2
3
4
5
6
7
8
9
10
11
12
|
create
table
teacher
(
tid
int
not
null
,
tname
varchar
(20)
not
null
,
tage
int
not
null
,
tsex
int
not
null
,
tcount
varchar
(20)
not
null
,
tpwd
varchar
(20)
not
null
,
tsuper
int
not
null
,
primary
key
(tid),
unique
(tcount)
)
|
Win2003创建数据表teacher
|
1
2
3
4
5
6
7
8
|
create
table
teacher
(
tid
int
not
null
,
nowage
int
not
null
,
tel
char
(20)
not
null
,
address
varchar
(80)
not
null
,
primary
key
(tid)
)
|
Win7 创建存储过程V3_teacher
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
create
proc V3_teacher
(
@tid
int
,
@tname
varchar
(20),
@tage
int
,
@tsex
int
,
@tcount
varchar
(20),
@tpwd
varchar
(20),
@super
int
,
@nowage
int
,
@tel
char
(20) ,
@address
varchar
(80)
)
as
set
XACT_ABORT
on
BEGIN
DISTRIBUTED
TRANSACTION
insert
into
teacher
values
(@tid,@tname,@tage,@tsex,@tcount,@tpwd,@super);
insert
into
[192.168.116.130].[V3].[dbo].[teacher]
values
(@tid,@nowage,@tel,@address);
COMMIT
TRANSACTION
|
采用存储过程实现垂直分片。此时插入数据之后，将分别插入到不同地址上的SQL Server的teacher的数据表里面。
-----------------------------------------------------------------------------------------------------------------
Win7创建数据表class
|
1
2
3
4
5
6
7
8
9
|
create
table
class
(
cid
int
not
null
,
sid
int
not
null
,
tid
int
not
null
,
cname
varchar
(20)
not
null
,
score
int
not
null
,
primary
key
(cid,sid)
)
|
本地数据表。
-----------------------------------------------------------------------------------------------------------------
Win 7:
Win2003:
4.程序代码测试
水平分片测试
垂直分片测试
转载请注明出处：http://www.cnblogs.com/yydcdut/p/3459836.html
![NL2SQL进阶系列(4)：ConvAI、DIN-SQL等16个业界开源应用实践详解[Text2SQL]](https://ucc.alicdn.com/fnj5anauszhew_20240416_41590901e8a84d199dd84ce376412f5b.jpeg?x-oss-process=image/resize,h_160,m_lfit)