分布式數(shù)據(jù)庫(kù)實(shí)驗(yàn)期末評(píng)測(cè)方案(示例)

1、分布式數(shù)據(jù)庫(kù)實(shí)驗(yàn)期末評(píng)測(cè)方案(示例)注：1. 本方案針對(duì)三個(gè)場(chǎng)地的情況，分別命名為site1、site2、site32. 由于測(cè)試中會(huì)涉及到一個(gè)邏輯表的四個(gè)片斷，故作如下指定：db1: site1db2: site2db3: site3db4: site13. 當(dāng)指某一個(gè)邏輯表的分片時(shí)，用表達(dá)形式：tableNamedbi (i =1,2,3,4)4. 這種表達(dá)方式可以擴(kuò)充：（m為場(chǎng)地?cái)?shù)，n為分片數(shù)）dbi: sitej (i = 1,2,3, n; j = 1,2,3,m;) tableName dbi (i = 1,2,3, n)（一） 僅支持水平劃分1. 對(duì)create、fragment

2、、insert、delete的考察a) create i. 創(chuàng)建新數(shù)據(jù)庫(kù)testdbii. 生成如下的表模式：SAL (title char(20) primary key, sal float not null)EMP (eno char(20) primary key, ename char(20) not null, title char(20)JOB (jno char(20) primary key, jname char(20) not null, budget float not null, loc char(20) not null)ASG (eno char(20), jno

3、 char(20), resp char(20) not null, dur int not null)b) fragment （要求按照要求將劃分信息計(jì)入數(shù)據(jù)字典）i. 按照以下方式劃分：EMP eno E1000 & title N db1eno = N db2eno = E1000 & title = E1000 & title = N db4ASG eno E1000 & jno J0500 db1eno = J0500 db2eno = E1000 & jno = E1000 & jno = J0500 db4JOBjno = J0500 db2ii. SAL不劃分，放置在db1c)

4、 Insert (檢查相應(yīng)場(chǎng)地的數(shù)據(jù)，看是否正確插入)i. insert into EMP (eno, ename, title) values (E0001, J. Doe, Elect. Eng.)- db1ii. insert into EMP (eno, ename, title) values (E0999, M. Smith, Syst. Anal.)- db2iii. insert into EMP (eno, ename, title) values (E1000, L. Polly, Elect. Eng.)- db3iv. insert into JOB (jno, jna

5、me, budget, loc) values (J0499, Instrumentation, 80000, Montreal)- db1v. insert into JOB (jno, jname, budget, loc) values (J0500, Database Develop, 135000, New York)- db2d) delete（檢查相應(yīng)場(chǎng)地的數(shù)據(jù)，看是否正確刪除) i. select * from JOB（應(yīng)返回兩條記錄：(J0499, Instrumentation, 80000, Montreal)(J0500, Database Develop, 13500

6、0, New York)ii. delete from JOB where jno=J0500（應(yīng)返回信息：成功刪除一條記錄；查看db2，子表為空）iii. select * from JOB（應(yīng)返回一條記錄：(J0499, Instrumentation, 80000, Montreal)）iv. delete from JOB where budget90000v. delete from EMP where enoE2002（以上兩步將所有的表都刪空）vi. select * from EMP (應(yīng)返回信息：0條記錄)2. 導(dǎo)入真實(shí)考察數(shù)據(jù)a) 數(shù)據(jù)集規(guī)模: EMP:2500行, ASG

7、:4000行，JOB:1000行，SAL:100行b) 成功導(dǎo)入后，查看每個(gè)站點(diǎn)的子表數(shù)據(jù)分布情況3. 對(duì)select的考察a) 基本的select操作i. 涉及單個(gè)表的一個(gè)片斷select * from EMP where enoE0010ii. 涉及單個(gè)表的多個(gè)片段 select * from ASG where eno=E0990b) 較復(fù)雜的select操作i. 根據(jù)查詢(xún)條件優(yōu)化select * from EMP, SAL where EMP.title = SAL.title and SAL.enoE0100（要點(diǎn)：根據(jù)查詢(xún)條件選擇合適的EMP的分片；選擇操作下移減少連接數(shù)據(jù)集的大小

8、；適當(dāng)進(jìn)行并行操作）ii. 根據(jù)劃分條件優(yōu)化select * from EMP, ASG where EMP.eno = ASG.eno（要點(diǎn)：根據(jù)查詢(xún)條件選擇合適的EMP與ASG的分片進(jìn)行連接；適當(dāng)進(jìn)行并行操作）iii. 查詢(xún)條件到劃分條件的傳遞及優(yōu)化select * from ASG, JOB where ASG.jno = JOB.jno and ASG.jnoJ0200（要點(diǎn)：根據(jù)查詢(xún)條件選擇合適的ASG 和JOB的分片；選擇操作下移減少連接數(shù)據(jù)集的大小；適當(dāng)進(jìn)行并行操作）iv. 四表的復(fù)雜連接select * from EMP, JOB, SAL, ASG where EMP.tit

9、le = SAL.title and EMP.eno = ASG.eno and JOB.jno = ASG.jno and EMP.enoE0010（要點(diǎn)：選擇操作下移減少連接數(shù)據(jù)集的大??；適當(dāng)進(jìn)行并行操作；優(yōu)化結(jié)果不唯一）（二） 支持水平劃分垂直劃分1. 對(duì)create、fragment、insert、delete的考察a) create i. 創(chuàng)建新數(shù)據(jù)庫(kù)testdbii. 生成如下的表模式：SAL (title char(20) primary key, sal float not null)EMP (eno char(20) primary key, ename char(20) n

10、ot null, title char(20)JOB (jno char(20) primary key, jname char(20) not null, budget float not null, loc char(20) not null)Gxx (eno char(20), jno char(20), resp char(20) not null, dur int not null)b) fragment （要求按照要求將劃分信息計(jì)入數(shù)據(jù)字典）i. 按照以下方式劃分：EMP eno E1000 & title N db1eno = N db2eno = E1000 & title =

11、 E1000 & title = N db4ASG eno E1000 & jno J0500 db1eno = J0500 db2eno = E1000 & jno = E1000 & jno = J0500 db4JOBjno J0500(jno, jname, budget) db1jno = J0500 (jno, jname, budget) db3 jno = J0500 (jno, loc) db4 ii. SAL不劃分，放置在db1c) Insert (檢查相應(yīng)場(chǎng)地的數(shù)據(jù)，看是否正確插入)i. insert into EMP (eno, ename, title) values

12、 (E0001, J. Doe, Elect. Eng.)- db1ii. insert into EMP (eno, ename, title) values (E0999, M. Smith, Syst. Anal.)- db2iii. insert into EMP (eno, ename, title) values (E1000, L. Polly, Elect. Eng.)- db3iv. insert into JOB (jno, jname, budget, loc) values (J0499, Instrumentation, 80000, Montreal)- db1,

13、db2v. insert into JOB (jno, jname, budget, loc) values (J0500, Database Develop, 135000, New York)- db3, db4d) delete（檢查相應(yīng)場(chǎng)地的數(shù)據(jù)，看是否正確刪除）i. select * from JOB（應(yīng)返回兩條記錄：(J0499, Instrumentation, 80000, Montreal)(J0500, Database Develop, 135000, New York)ii. delete from JOB where jno=J0500（應(yīng)返回信息：成功刪除一條記錄；

14、查看db3與db4，子表為空）iii. select * from JOB（應(yīng)返回一條記錄：(J0499, Instrumentation, 80000, Montreal)）iv. delete from JOB where budget90000v. delete from EMP where enoE2002（以上兩步將所有的表都刪空）vi. select * from EMP (應(yīng)返回信息：0條記錄)2. 導(dǎo)入真實(shí)考察數(shù)據(jù)a) 數(shù)據(jù)集規(guī)模: EMP:2500行, ASG:4000行，JOB:1000行，SAL:100行b) 成功導(dǎo)入后，查看每個(gè)站點(diǎn)的子表數(shù)據(jù)分布情況3. 對(duì)select

15、的考察a) 基本的select操作i. 涉及單個(gè)表的水平劃分的一個(gè)片斷select * from EMP where enoE0010ii. 涉及單個(gè)表的水平劃分的多個(gè)片段select * from ASG where eno=E0990iii. 涉及單個(gè)表的垂直劃分的一個(gè)片斷select jno, jname from JOB where jnoJ0300iv. 涉及單個(gè)表的垂直劃分的多個(gè)片段（縱向）select jno, jname from JOBv. 涉及單個(gè)表的垂直劃分的多個(gè)片段（橫向）select * from JOB where JNOJ0100b) 較復(fù)雜的select操作i. 根據(jù)查詢(xún)條件優(yōu)化select * from EMP, SAL where EMP.title = SAL.title and SAL.enoE0100（要點(diǎn)：根據(jù)查詢(xún)條件選擇合適的EMP的分片；選擇操作下移減少連接數(shù)據(jù)集的大?。贿m當(dāng)進(jìn)行并行操作）ii. 根據(jù)劃分條件優(yōu)化select * from EMP, ASG where EMP.eno = ASG.eno（要點(diǎn)：根據(jù)查詢(xún)條件選擇合適的EMP與ASG的分片進(jìn)行連接；適當(dāng)進(jìn)行并行操作）iii. 查詢(xún)條件到劃分條件的傳遞及優(yōu)化select * from ASG, JOB where ASG.jn