数据库原理B实验报告完整版4-6(共12页)

1、 实验四 完整性 日期 2011-4-6一 实验目的1. 熟悉通过SQL对数据进行完整性控制。2. 深入理解数据库系统的完整性概念。二、实验原理为维护数据库的完整性，DBMS必须： 1.提供定义完整性约束条件的机制 2.提供完整性检查的方法 3.违约处理Create table 语法中包含了对完整性的定义。CREATE TABLE database_name. owner .| owner. table_name ( < column_definition > | column_name AS computed_column_expression | < table_cons

2、traint > := CONSTRAINT constraint_name | PRIMARY KEY | UNIQUE ,.n ) ON filegroup | DEFAULT TEXTIMAGE_ON filegroup | DEFAULT < column_definition > := column_name data_type COLLATE < collation_name > DEFAULT constant_expression | IDENTITY ( seed , increment ) NOT FOR REPLICATION ROWGUID

3、COL < column_constraint > .n < column_constraint > := CONSTRAINT constraint_name NULL | NOT NULL | PRIMARY KEY | UNIQUE CLUSTERED | NONCLUSTERED WITH FILLFACTOR = fillfactor ON filegroup | DEFAULT | FOREIGN KEY REFERENCES ref_table ( ref_column ) ON DELETE CASCADE | NO ACTION ON UPDATE C

4、ASCADE | NO ACTION NOT FOR REPLICATION | CHECK NOT FOR REPLICATION ( logical_expression ) < table_constraint > := CONSTRAINT constraint_name PRIMARY KEY | UNIQUE CLUSTERED | NONCLUSTERED ( column ASC | DESC ,.n ) WITH FILLFACTOR = fillfactor ON filegroup | DEFAULT | FOREIGN KEY ( column ,.n )

5、REFERENCES ref_table ( ref_column ,.n ) ON DELETE CASCADE | NO ACTION ON UPDATE CASCADE | NO ACTION NOT FOR REPLICATION | CHECK NOT FOR REPLICATION ( search_conditions ) 三、实验仪器和设备1. 计算机2. SQL Server 2000四、预习要求复习完整性相关的SQL语句的基本语法。五、实验内容及步骤1. 实体完整性 定义表的主码关系模型的实体完整性在 CREATE TABLE 中用 PRIMARY KEY 定义。 定义主码

6、的方法分为定义为列级约束条件和定义为表级约束条件两种。 例 4-1定义表 Student，并将其中的 Sno 属性定义为主码。 CREATE TABLE STUDENT( SNO CHAR(7) PRIMARY KEY, SNAME CHAR(8) NOT NULL, SSEX CHAR(2), SAGE SMALLINT, SDEPT CHAR(20); 例 4-2定义表 SC,将其中的属性 Sno,Cno 定义为主码。 对于多个属性构成的码， 只能够将其定义为表级约束条件， 而无法用列级约束条件来实现。 CREATE TABLE SC( SNO CHAR(7) NOT NULL, CNO

7、CHAR(4) NOT NULL, GRADE SMALLINT, PRIMARY KEY (SNO,CNO); 2. 参照完整性定义表的外码关系模型的参照完整性是在 CREATE TABLE 中用 FOREIGN KEY 语句来定义的，并用REFERENCES 来指明外码参照的是哪些表的主码。 定义表 SC，其中 Sno 参照表 Student 的主码 Sno，Cno 参照表 Course 的主码 Cno。 CREATE TABLE SC( SNO CHAR(7) NOT NULL, CNO CHAR(4) NOT NULL, GRADE SMALLINT, PRIMARY KEY (SNO

8、,CNO), FOREIGN KEY (SNO) REFERENCES STUDENT(SNO), FOREIGN KEY (CNO) REFERENCES COURSE(CNO); 3. 用户定义完整性用户定义的属性上的约束条件例 6-1列值非空。在定义 SC 表时，Sno、Cno 和 Grade 属性都不允许取空值。在不特别声明的情况下，非码属性的值是允许取空值的。 CREATE TABLE SC( SNO CHAR(7) NOT NULL, /*列值非空的约束 NOT NULL*/ CNO CHAR(4) NOT NULL, GRADE SMALLINT NOT NULL); 例 6-2

9、列值唯一。建立部门表 DEPT，要求部门名称 Dname 取值唯一，部门编号 Deptno 属性为主码。 CREATE TABLE DEPT( DEPTNO NUMERIC(7) PRIMARY KEY, DNAME VARCHAR(9) UNIQUE, /*UNIQUE 约束要求 Dname 取值唯一*/ LOCATION VARCHAR(10); 例 6-3CHECK 短语指定列值应该满足的条件。 定义表 Student， 属性 Ssex 的值只允许取 “男”或“女” ；定义表 SC，属性 Grade 的值定义在 0100 之间。 CREATE TABLE STUDENT( SNO CHA

10、R(7) PRIMARY KEY, SNAME CHAR(8) NOT NULL, SSEX CHAR(2) CHECK(SSEX IN(男,女), /*CHECK 语句约束条件*/ SAGE SMALLINT, SDEPT CHAR(20); CREATE TABLE SC( SNO CHAR(7) NOT NULL, CNO CHAR(4) NOT NULL, GRADE SMALLINT CHECK (GRADE>0 AND GRADE<100), /*CHECK语句约束条件*/ PRIMARY KEY (SNO,CNO), FOREIGN KEY (SNO) REFEREN

11、CES STUDENT(SNO), FOREIGN KEY (CNO) REFERENCES COURSE(CNO); 用户定义的元组上的约束条件CHECK 短语不光能够定义属性列上的约束条件，还允许用户定义元组级的约束条件。定义表 Student，要求当学生性别为男时，其名字不能以 Ms.打头。 CREATE TABLE STUDENT( SNO CHAR(7) PRIMARY KEY, SNAME CHAR(8) NOT NULL, SSEX CHAR(2), SAGE SMALLINT, SDEPT CHAR(20), CHECK (SSEX = 女 OR SNAME NOT LIKE

12、Ms.%); /*定义了 Sname 和 Ssex 之间的约束条件*/ 4. CONSTRAINT 完整性约束命名子句。 在定义表时利用约束命名子句对完整性约束条件命名，能够灵活地增加或删除一个完整性约束条件。 例 8-1定义表 Student，要求学号在 9000099999 之间，姓名不能取空值，年龄小于 30，性别只能是“男”或“女” 。要求全部用约束命名子句实现。 CREATE TABLE STUDENT( SNO NUMERIC(5) CONSTRAINT C1 CHECK (SNO BETWEEN 90000 AND 99999), SNAME VARCHAR(20) CONSTR

13、AINT C2 NOT NULL, SAGE NUMERIC(3) CONSTRAINT C3 CHECK (SAGE < 30), SSEX VARCHAR(2) CONSTRAINT C4 CHECK (SSEX IN (男,女), CONSTRAINT StudentKey PRIMARY KEY(SNO); 在表 Student 上共定义了 5 个约束条件，包括主码约束以及 C1、C2、C3、C4 四个列级约束。 例 8-2修改表 Student 中的完整性限制，去掉对性别的限制，并将年龄的限制由小于 30 改为小于 40。 ALTER TABLE STUDENT /*去掉对性别

14、的限制条件 C4*/ DROP CONSTRAINT C4; ALTER TABLE STUDENT /*先删掉原来的约束条件再增加一个新的约束条件*/DROP CONSTRAINT C3; ALTER TABLE STUDENT ADD CONSTRAINT C3 CHECK(SAGE <40); 5. 触发器。 触发器可以看成是一类特殊的存储过程， 在满足某个特定条件时自动触发执行， 是提高数据库服务器性能的有力工具。触发器分为三类，更新触发器、插入触发器和删除触发器。能够定义触发器的用户有： 1） 表的所有者； 2） 系统管理员； 3） 拥有创建触发器的权限，且拥有对操作对象的相应

15、的操作权限的用户。 定义表 TAB,并在其上定义触发器 TRI，在对 TAB 的插入和更新前检查，如果插入或更新的值在 1001000 之间的话，将值置为 50；如果值大于 1000 的话，则给出新值不允许大于 1000 的提示。 CREATE TABLE tab (col int); INSERT INTO tab VALUES(10); INSERT INTO tab VALUES(20); CREATE TRIGGER tri BEFORE UPDATE OR INSERT ON tab FOR EACH ROW AS BEGIN IF new.col > 100 AND new.

16、col < 1000 THEN new.col := 50; END IF; IF new.col > 1000 THEN RAISE EXCEPTION New values can not more than 1000; END IF; END; 检测：1）向表 TAB 插入数据（150） 执行：INSERT INTO TAB VALUES(150); 结果：插入的数据为 150。值在 1001000 之间，触发器 TRI 自动执行，将插入的值置为 50。 2）对表中的数据进行更新，将 20 更新为 1500。 执行：UPDATE TAB SET COL = 1500 WHER

17、E COL = 20; 结果：系统报出New values can not more than 1000的错误， 对表进行查询后发现，表中的数据并没有发生改变。说明在更新数据时，值大于 1000则触发触发器 TRI 自动执行，系统报错，更新无效。 删除触发器 TRI。 DROP TRIGGER TRI ON TAB; 删除触发器 TRI 之后，在对 TAB 进行插入和更新就不再有以上限制了。 六、心得感受通过本次实验，我懂得了数据库的完整性语句的书写，熟悉了通过SQL对数据进行完整性控制的过程，从而深入理解数据库系统的完整性概念，为以后学习和工作奠定了基础。 实验五 通过嵌入式SQL访问数据库

18、 日期 2011-4-13一、实验目的熟悉通过嵌入式SQL编程访问数据库。二、实验原理将SQL嵌入到高级语言中混合编程，程序中会含有两种不同计算模型的语句。（1）SQL语句² 描述性的面向集合的语句² 负责操纵数据库（2）高级语言语句² 过程性的面向记录的语句² 负责控制程序流程执行过程：1由DBMS的预处理程序对源程序进行扫描，识别出SQL语句2把它们转换成主语言调用语句，以使主语言编译程序能识别它3最后由主语言的编译程序将整个源程序编译成目标码。三、实验仪器和设备1. 计算机2. SQL Server 2000四、预习要求嵌入式SQL语句的基本用法。

19、五、实验内容及步骤1. 查询某一门课程的信息。要查询的课程由用户在程序运行过程中指定，放在主变量中。EXEC SQL BEGIN DECLARE SECTION; /*说明主变量 deptname， HSno， HSname, *HSsex，HSage，NEWAGE。*/ Char deptname20; char HSno9; char HSname20; char HSsex2; int HSage; int NEWAGE;EXEC SQL END DECLARE SECTION; /*声明部分结束*/ long SQLCODE; EXEC SQL INCLUDE sqlca; /*定义

20、SQL 通信区*/ int main(void) int count = 0; char yn; /*变量 yn 代表 yes 或者 no*/printf("Please choose the department name(CS/MA/IS): "); /*为主变量 deptname 赋值*/ scanf("%s", &deptname); EXEC SQL CONNECT TO 学生课程数据库localhost:54321 USER "SYSTEM"/"MANAGER" /*连接数据库 TEST*/ E

21、XEC SQL DECLARE SX CURSOR FOR /*说明游标，为查询和更新做准备*/ SELECT Sno, Sname, Ssex, Sage FROM Student WHERE SDept = :deptname; EXEC SQL OPEN SX; /*打开游标*/ for ( ; ; ) /*用循环结构逐条处理结果集的记录*/ 2. 查询选修某一门课程的选课信息，要查询的课程号由用户在程序运行过程中指定，放在主变量中，然后根据用户的要求修改其中某些记录的成绩字段。EXEC SQL FETCH SX INTO :HSno, :HSname, :HSsex,:HSage; /

22、*游标向前推进一行，取结果送主变量*/ if (sqlca.sqlcode != 0) break; /*其中参数 0 代表 SUCCESS*/ if(count+ = 0) /*如果是第一行的话，先打出行头*/ printf("n%-10s %-20s %-10s %-10sn", "Sno", "Sname", "Ssex", "Sage"); printf("%-10s %-20s %-10s %-10dn", HSno, HSname, HSsex, HSage);

23、printf("UPDATE AGE(y/n)?");do scanf("%c",&yn); while(yn != 'N' && yn != 'n' && yn != 'Y' && yn != 'y'); if(yn = 'y' | yn = 'Y') /*如果选择修改的操作。*/ printf("INPUT NEW AGE:"); scanf("%d",&

24、;NEWAGE); /*用户输入新年龄，存放在主变量 EXEC SQL UPDATE Student * NEWAGE 中*/ SET Sage = :NEWAGE WHERE CURRENT OF SX; /*对 Student 表中的年龄进行更新*/ EXEC SQL CLOSE SX; /*关闭游标*/ EXEC SQL COMMIT WORK; /*提交更新*/ EXEC SQL DISCONNECT 学生课程数据库; /*断开连接*/ 3.对 C 程序进行处理，生成可执行文件。 本实验是在 windows 环境下对 C 程序进行预编译和编译、链接的。没有给出 Linux 环境下的编译

25、过程，读者如果有兴趣，可以自行实验。 1. 预编译。 本实验中，test.pc 存放在 C:Program FilesBasesoftKingbaseES4.1bin 目录下，与预处理程序 esqlc 存放在同一目录下，这样做的目的是便于之后的三步处理过程，不需要特别指定存放路径。如果存放在其他目录下，只需要在文件名前指定完整路径即可。 （1） “开始” “运行” ，输入 cmd 命令，进入命令行编辑器。 （2）命令行编辑器默认的路径为 C:Documents and Settings用户名>，我们首先需要转到预处理程序 esqlc 所在的目录下。键入： cd C:Program Fil

26、esBasesoftKingbaseES4.1bin。 （3）进行预编译。 esqlc -I "C:Program FilesBasesoftKingbaseES4.1include" -o test.c test.pc （4）如果预编译成功，则自动生成一个.c 文件。结果提示可能会有一个“WARNING: EXEC SQL SQLCA: SQL language extension” ，但对结果不造成影响。 2. 编译和链接。 经过预处理之后，生成了标准的 C 语言程序 test.c，就可以使用标准的 C 语言编译器进行编译了。本实验直接使用命令行的方式对该 C 程序进行

27、编译和链接。 （1）编译。 在编译过程中需要使用到 cl.exe 程序,cl.exe 是 Visual C+ 的编译器名称。如果安装Visual C+在 C:Program FilesMicrosoft Visual StudioVC98 下，则其编译器 cl.exe 所在路径应该是 C:Program FilesMicrosoft Visual StudioVC98Bin。 在 cl 后面可以加上各类参数命令,进行不同的操作。 本实验中使用到的 “/c” 命令是表示只编译， 不链接， 相当于在 Visual C+ 6.0 中，选择"Build"菜单下的"Comp

28、ile"。编译命令如下： cl /c /DWIN32 /I"C:Program FilesBasesoftKingbaseES4.1include" /Fotest.obj test.c （2）链接。 由于在编译时我们选用“/c”命令只对 test.c 进行了编译并没有进行链接，而该 C 程序执行需要链接 esql.lib 函数库，所以在链接时我们需要指定这个库文件的路径。链接命令如下:link /out:test.exe test "C:Program FilesBasesoftKingbaseES4.1libesql.lib" 最后生成可执

29、行文件 test.exe，即可实现具体功能。六、心得感受 通过此次实验，我懂得了嵌入式SQL编程访问数据库所运用的语句，知道了其访问数据库的步骤和过程，为以后做数据库方面的工作打下了基础。实验六 编写存储过程访问数据库 日期 2011-5-8一、实验目的1. 熟悉使用存储过程来进行数据库应用程序的设计。2. 体会存储过程的优点。二、实验原理存储过程（Stored Procedure）是一组为了完成特定功能的SQL 语句集，经编译后存储在数据库。中用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。在SQL Server 的系列版本中存储过程分为两类：系统提供的存储过程和用户

30、自定义存储过程。系统过程主要存储在master 数据库中并以sp_为前缀，并且系统存储过程主要是从系统表中获取信息，从而为系统管理员管理SQL Server 提供支持。通过系统存储过程，MS SQL Server 中的许多管理性或信息性的活动（如了解数据库对象、数据库信息）都可以被顺利有效地完成。尽管这些系统存储过程被放在master 数据库中，但是仍可以在其它数据库中对其进行调用，在调用时不必在存储过程名前加上数据库名。而且当创建一个新数据库时，一些系统存储过程会在新数据库中被自动创建。用户自定义存储过程是由用户创建并能完成某一特定功能（如查询用户所需数据信息）的存储过程。三、实验仪器和设备

31、1. 计算机2. SQL Server 2000四、预习要求1. 复习创建存储过程的语法。2. 复习存储过程的特点。五、实验内容及步骤对学生课程数据库，编写存储过程，完成下面功能：1.统计离散数学的成绩分布情况，即按照各分数段统计人数；1.创建存储过程 1） 创建需要的表结构。 因为存储过程在执行后在客户端并没有返回值，因此需要建立一个表存放执行后的结果，并返回到客户端显示。根据实验要求，我们要统计选修了离散数学的学生的成绩分布，因此我们建立表 Rank，其中第一列 division 显示成绩分段划分，第二列 number 显示的是成绩在该分数段的学生人数。 CREATE TABLE Rank

32、( division CHAR(20), number INT); 2） 编写存储过程。 CREATE OR REPLACE PROCEDURE statistic_mark(name CHAR(50) /存储过程带有一个字符型参数值，便于统计不同科目的分数分布情况AS DECLARE less60 INT := 0; /分为五个分数段，并置初始值为 0 b60a70 INT := 0; b70a80 INT := 0; b80a90 INT := 0; more90 INT := 0; curcno CHAR(4); /设字符型变量 curcno 存放输入的课程名称参数 BEGIN SELE

33、CT cno INTO curcno /根据课程名称在 Course 表中查询查询该课程号 FROM Course WHERE cname = name; IF NOT FOUND THEN /如果不存在用户输入的课程名称，返回错误信息RAISE EXCEPTION '输入错误，没有该课程' END IF; SELECT count(*) INTO less60 /查询分数低于 60 分的学生人数 FROM sc WHERE cno = curcno AND grade < 60; SELECT count(*) INTO b60a70 /查询分数在 60 到 70 之间

34、的学生人数 FROM sc WHERE cno = curcno AND grade >= 60 AND grade < 70; SELECT count(*) INTO b70a80 /查询分数在 70 到 80 之间的学生人数 FROM sc WHERE cno = curcno AND grade >= 70 AND grade < 80; SELECT count(*) INTO b80a90 /查询分数在 80 到 90 之间的学生人数 FROM sc WHERE cno = curcno AND grade >= 80 AND grade < 9

35、0; SELECT count(*) INTO more90 /查询分数高于 90 的学生人数 FROM sc WHERE cno = curcno AND grade >= 90; /向表 Rank 中插入五条记录，显示成绩分布情况 INSERT INTO Rank VALUES('0,60)',less60); INSERT INTO Rank VALUES('60,70)',b60a70); INSERT INTO Rank VALUES('70,80)',b70a80); INSERT INTO Rank VALUES('8

36、0,90)',b80a90); INSERT INTO Rank VALUES('90,100',more90); END; 2. 执行存储过程 首先执行编写好的存储过程 statistic_mark，然后在表 Rank 中查看执行结果。 PERFORM PROCEDURE statistic_mark(离散数学); SELECT * FORM Rank; 2）统计任意一门课的平均成绩。 1. 创建存储过程 1） 创建需要的表结构。 根据实验要求，我们要统计任意一门课的平均成绩，因此我们Avggrade，其中第一列 cname 显示统计的课程名称，第二列 avgg 显示

37、选修了该课程的学生平均成绩。 CREATE TABLE Avggrade( cname CHAR(50), avgg NUMERIC(10, 6); 2） 编写存储过程。 CREATE OR REPLACE PROCEDURE collect_avggrade() AS DECLARE /声明变量 curname CHAR(50); curcno CHAR(4); curavgg NUMERIC(10, 6); CURSOR mycursor FOR /声明游标 mycursor 查询课程号和名称 SELECT cno, cname FROM course; BEGIN OPEN mycurs

38、or; /打开游标 IF mycursor%ISOPEN THEN /条件控制，游标打开时进行以下处理 LOOP /循环控制 FETCH mycursor INTO curcno, curname; /游标推进一行，取结果送变量 EXIT WHEN (mycursor%NOTFOUND); /如果没有返回值则退出循环 SELECT AVG(grade) INTO curavgg FROM SC /求该课程的平均值送变量 WHERE cno = curcno; /向 Avggrade 表中插入记录，显示课程名称和平均成绩 INSERT INTO Avggrade VALUES(curname, curavgg); END LOOP; /结束循环控制 END IF; /结束条件控制 CLOSE mycursor; /关闭游