分布式数据库.doc

分布式数据库一、实验目的：（1）通过实验加深对课程的理论部分的理解，培养学生理论联系实际的能力。（2）在实验系统的设计与实现上给学生留有较大的自由发挥空间，鼓励与培养学生的创新能力。（3）通过合作完成一个实验系统，培养学生的团队精神与协同工作的习惯。二、实验内容：（1）第一部分：建表及数据量要求Student(SID char(4) primary key, SName char(20) not null,Class char(20) not null)；1000Course(CID char(3) primary key, CName char(20 ) not null)；30Teacher(TID char(4) primary key, TName char(20) not null , CID char(3) ,Class char(20) not null)；30Exam (SID char(4) primary key, CID char(3) primary key, Mark double )；2000 设一个教师只上一门课，一门课可由多个教师上。（2）第二部分：数据分布要求：垂直和水平划分Student表：根据某种规则，将Student表水平分片：S1，S2，S3，分别存放在三个不同的站点SITE1，SITE2，SITE3。Teacher表：根据某种规则，将Teacher表垂直分片：T1，T2，分别存放在两个不同的站点SITE1，SITE2。Exam 表：根据某种规则，将Exam表水平分片：E1，E2，E3，分别存放在三个不同的站点SITE1，SITE2，SITE3。Course表：不划分，分配到site1（3）第三部分：数据查询、插入、删除和更新 要求在任意站点可实现以上功能。（4）第四部分：系统性能讨论 讨论你所采用DBMS的分布式特性，以及本系统的安全性、可靠性、数据一致性措施和效果。三、设计基本思想1拟采用MS SQLServer数据库实现本系统，数据库的分片的透明性在数据库的层次上实现，建立一个全局视图，尽量避免使用各个表的分片。2在SQLServer内部水平分片透明主要利用分区视图来实现。分区视图在一个或多个服务器间水平连接一组成员表中的分区数据，使数据看起来就象来自一个表。在实现分区视图之前，必须先水平分区表。原始表被分成若干个较小的成员表。每个成员表包含与原始表相同数量的列，并且每一列具有与原始表中的相应列同样的特性（如数据类型、大小、排序规则）。如果正在创建分布式分区视图，则每个成员表分别位于不同的成员服务器上。3垂直分片的透明性的实现：可以为这些表建立视图，为了保证数据的可更新，再针对此视图建立相关的触发器。3在SQLServer内部为了远程服务器的访问可以通过sp_addlinkedserver建立连接服务器，然后用sp_addlinkedsrvlogin建立此服务器的登录。四、实现步骤1首先安装三个SQLServer服务器，分别命名为site1,site2,site3,在其上面建立数据库对应为db1,db2,db3。2在site1的db1数据库中建立如下个表：create table S1(SIDchar(4)not null,SNamechar(20)not null,Classroomchar(20)not null CHECK (Classroom =计0201),primary key(SID,Classroom)create table Course(CIDchar(3)not null,CNamechar(20)not null,Primary key(CID)create table T1(TIDchar(4)not null,TNamechar(20)not null,primary key(TID)create table E1 (SIDchar(4)not null, CIDchar(3)not null, Markfloatnot null CHECK (Mark 60) ,primary key(SID,CID,Mark)其中S1为Student的水平分片1，其中存放计0201的学生，为了保证使用分区视图时的数据可以更新，将Classroom分片的字段设置为主键。T1为Teacher的垂直分片1，E1位Exam的水平分片1,其中存=60 and Mark=80) ,primary key(SID,CID,Mark)3为每个服务器建立其它服务器的连接。Site1中执行：exec sp_addlinkedserver server=site2 ,srvproduct =SQL SERVEREXEC sp_addlinkedsrvlogin site2, false, null, sa, jia123exec sp_addlinkedserver server=site3 ,srvproduct =SQL SERVEREXEC sp_addlinkedsrvlogin site3, false, null, sa, jia123Site2中执行：exec sp_addlinkedserver server=site1 ,srvproduct =SQL SERVEREXEC sp_addlinkedsrvlogin site1, false, null, sa, jia123exec sp_addlinkedserver server=site3 ,srvproduct =SQL SERVEREXEC sp_addlinkedsrvlogin site3, false, null, sa, jia123Site3中执行：exec sp_addlinkedserver server=site1 ,srvproduct =SQL SERVEREXEC sp_addlinkedsrvlogin site1, false, null, sa, jia123exec sp_addlinkedserver server=site2 ,srvproduct =SQL SERVEREXEC sp_addlinkedsrvlogin site2, false, null, sa, jia1234建立各个分区视图。Site1中执行：-建立Student视图create view Student asselect * from S1union allselect * from site2.db2.dbo.S2union allselect * from site3.db3.dbo.S3go-建立Exam视图create view Exam asselect * from E1union allselect * from site2.db2.dbo.E2union allselect * from site3.db3.dbo.E3go-建立Teacher视图create view t2 as select * from site2.db2.dbo.t2gocreate view Teacher asselect t1.TID,t1.Tname ,t2.CID,t2.Classroom from t1,t2where t1.tid=t2.tidSite2中执行：-建立Student视图create view Student asselect * from S2union allselect * from site1.db1.dbo.S1union allselect * from site3.db3.dbo.S3go-建立Course视图create view Course asselect * from site1.db1.dbo.Coursego-建立Exam视图create view Exam asselect * from E2union allselect * from site1.db1.dbo.E1union allselect * from site3.db3.dbo.E3go-建立Teacher视图create view t1 as select * from site1.db1.dbo.t1gocreate view Teacher asselect t1.TID,t1.Tname ,t2.CID,t2.Classroom from t1,t2where t1.tid=t2.tidSite3中执行：create view Student asselect * from S3union allselect * from site2.db2.dbo.S2union allselect * from site1.db1.dbo.S1go-建立Course视图create view Course asselect * from site1.db1.dbo.Coursego-建立Exam视图create view Exam asselect * from E3union allselect * from site2.db2.dbo.E2union allselect * from site1.db1.dbo.E1go-建立Teacher视图create view t1 as select * from site1.db1.dbo.t1gocreate view t2 as select * from site2.db2.dbo.t2gocreate view Teacher asselect t1.TID,t1.Tname ,t2.CID,t2.Classroom from t1,t2where t1.tid=t2.tid5在每个数据库中建立Teacher的触发器。drop trigger Trig_INS_Teacher gocreate trigger Trig_INS_Teacher ON Teacherinstead of insert asbegininsert into t1 select tid,tname from insertedinsert into t2 select tid ,cid ,classroom from insertedend godrop trigger Trig_DEL_Teachergocreate trigger Trig_DEL_Teacher ON Teacherinstead of deleteasbegindelete t1 where t1.tid in(select tid from deleted)delete t2 where t2.tid in(select tid from deleted)end drop trigger Trig_upd_Teachergodrop trigger Trig_upd_Teachergocreate trigger Trig_upd_Teacher ON Teacherinstead of updateasbegindelete t1 where t1.tid in(select tid from deleted)delete t2 where t2.tid in(select tid from deleted)insert into t1 select tid,tname from insertedinsert into t2 select tid ,cid ,classroom from insertedend附：SQL Server2005中分区表创建文件组ALTER DATABASE DeanDB ADD FILEGROUP FG1ALTER DATABASE DeanDB ADD FILE ( NAME = NFG1, FILENAME = NC:DeanDataFG1.ndf , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP FG1类似的建立四个文件和文件组，并把每一个存储数据的文件放在不同的磁盘驱动器里。创建分区函数：CREATE PARTITION FUNCTION SendSMSPF(datetime)AS RANGE RIGHT FOR VALUES (20