原文 基于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