数据库课程设计说明书

大连交通大学数据库课程设计

第五章 数据库物理设计

5.1 索引的建立

先给出索引的相关定义，然后自己选择表或者属性建立索引（唯一索引、聚簇索引），要求表和索引存储在不同的表空间里，写出相关的SQL语句。 唯一索引

CONNECT TO LGY19;

CREATE INDEX ADMINISTRATOR.UNIQUL ON ADMINISTRATOR.SHUJIA (PADRESS ASC) PCTFREE 10 MINPCTUSED 10 ALLOW REVERSE SCANS PAGE SPLIT SYMMETRIC COLLECT SAMPLED DETAILED STATISTICS CONNECT RESET; CONNECT TO LGY19;

CREATE INDEX ADMINISTRATOR.JIUCU ON ADMINISTRATOR.BOOKSTAYIN (PNUMBER ASC) PCTFREE 10 MINPCTUSED 10 ALLOW REVERSE SCANS PAGE SPLIT SYMMETRIC COLLECT SAMPLED DETAILED STATISTICS ; CONNECT RESET;

5.2 表空间的建立

给出表空间的相关定义，然后建立需要用到的表空间（至少2个以上，存放在目录容器和文件容器中），写出相关的SQL语句

1.文件容器 CONNECT TO LGY19;

CREATE REGULAR TABLESPACE DMS01 PAGESIZE 4 K MANAGEDBY DATABASE USING ( FILE 'c:\\dms\\dms01' 5120 ) EXTENTSIZE 16 OVERHEAD 12.67 PREFETCHSIZE 16TRANSFERRATE0.18BUFFERPOOLIBMDEFAULTBPDROPPEDTABLERECOVERYON;CONNECT RESET;CONNECT RESET; CONNECT TO LGY19;

CREAT EREGULAR TABLESPACE DMS02 PAGESIZE 4 K MANAGED BY DATABASE

13

大连交通大学数据库课程设计

USING ( FILE 'c:\\dms\\dms02' 5120 ) EXTENTSIZE 16 OVERHEAD 12.67 PREFETCHSIZE 16TRANSFERRATE0.18BUFFERPOOLIBMDEFAULTBP DROPPED TABLE RECOVERY ON;CONNECT RESET；

2. 目录容器

CONNECT TO LGY19;

CREATE REGULAR TABLESPACE SMS03 PAGESIZE 4 K MANAGEDBY SYSTEMUSING('C:\\sms\\smso1')EXTENTSIZE16OVERHEAD10.5

PREFETCHSIZE 16 TRANSFERRATE 0.14 BUFFERPOOL IBMDEFAULTBP DROPPED TABLE RECOVERY ON;CONNECT RESET;

第六章 数据库的实施

6.1系统实验要求

建立三个用户：user1，user2，user3

（1）使第一个用户user1和db2admin一起成为admin组的成员，使admin具有SYSADM的权限。

（2）使第二个用户user2拥有DBADM的权限。

（3）将创建的表之一上的所有特权授予给自己建立的用户user3。

6.2表的建立与数据的载入

此处给出建立表和视图的SQL语句！ 1. CONNECT TO LGY19;

CREATE TABLE ADMINISTRATOR.BOOK (BNUMBER CHARACTER (6) NOT NULL

BNAME VARCHAR (15) NOT NULL , AUTHOR VARCHAR (15) NO NULL BDATE DATE NOT NULL LNAMBER SMALLINT NOTNULL,

CONSTRAINT CC1262855876375 PRIMARY KEY (BNUMBER)) IN DMS01 INDEX IN DMS02

CONNECT RESET; 2. CONNECT TO LGY19;

CREATE TABLE ADMINISTRATOR.PEOPLE (ID CHARACTER (6) NOT NULL PNAME

14

大连交通大学数据库课程设计

VARCHAR (15) NOT NULL ,

TMAMUBER VARCHAR (15) NOT NULL SEX CHARACTER (1) NOT NUL L

ADRESS CHARACTER (15) NOT NULL CONSTRAINT CC1262856121156 PRIMARY KEY (ID) ) IN DMS02 INDEX IN DMS01 ; CONNECT RESET; 3. CONNECT TO LGY19;

CREATE TABLE DB2ADMIN.SHUJIA (PNUMBER CHARACTER (6) NOT NULL,

PADRESS CHARACTER (6) NOT ULL BLEIXING VARCHAR (15) NOT NULL ,

CONSTRAINT CC1262676030612 PRIMARY KEY ( PNUMBER) ) IN DMS01 INDEX IN DMS02 ;CONNECT RESET;

4. CONNECT TO LGY19;

CREATETABLEADMINISTRATOR .BARROW (BNUMBERCHARACTER (6) NOT NULL ID CHARACTER (6) NOT NULL JDATE DATE NOT NULL

CONSTRAINTCC1262856379687PRIMARY KEY ( BNUMBER, ID) , CONSTRAINT CC1262856394140FOREIGNKEY(BNUMBER)REFERENCESADMINISTRATOR.BOOK (BNUMBER) ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE QUERY OPTIMIZATION , CONSTRAINT CC1262856401656 FOREIGN KEY (ID) REFERENCES ADMINISTRATOR.PEOPLE (ID) ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE QUERY OPTIMIZATION ) IN DMS02 INDEX IN DMS01 ; CONNECT RESET; 5. CONNECT TO LGY19;

CREATE TABLE ADMINISTRATOR.BOOKSTAY (BNUMBER CHARACTER (6) NOTNULL,PNUMBERCHARACTER(6) NOT NULL , CONSTRAINT CC1262856548281 PRIMARY KEY ( BNUMBER, PNUMBER) , CONSTRAINT CC1262856557218 FOREIGN KEY (BNUMBER) REFERENCES ADMINISTRATOR.BOOK (BNUMBER) ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE QUERY OPTIMIZATION , CONSTRAINT

CC1262856575734

FOREIGN

15

KEY (PNUMBER) REFERENCES

大连交通大学数据库课程设计

ADMINISTRATOR.SHUJIA (PNUMBER) ON DELETE NO ACTION ON UPDATE NO ACTION ENFORCED ENABLE QUERY OPTIMIZATION) IN DMS01 INDEX IN DMS02 ; CONNECT RESET; 视图：

CONNECT TO LGY19;

CREATE view DB2ADMIN.BOOKSTAYIN AS SELECT bname,shujia.pnumber,padress,bleixing FROM book,shujia,bookstay

WHERE book.bnumber=bookstay.bnumber and shujia.pnumber=bookstay.pnumber; CONNECT RESET

6.3触发器的设计

给出建立触发器的SQL语句!

CREATE TRIGGER ADMINISTRATOR.CHUFAQI NOCASCADEBEFORE UPDATE ON ADMINISTRATOR.BOOK REFERENCING OLD AS oldr NEW AS newr FOR EACH ROW MODE DB2SQL

WHEN （oldr.lnamber>1 ) BEGIN ATOMIC

Set newr.lnamber=oldr.lnamber; END^

CONNECT RESET^

6.4 数据库的运行

此处给出每个表的抓图及载入数据的语句（可以只给出一条语句）！

16