数据库原理实验指导书.doc

数据库原理实验指导书实验名称：试验一：SQL语言嵌套查询和数据更新操作所属课程：数据库原理实验类型：设计型实验实验类别：基础 专业基础 专业实验学时：4学时一、实验目的1熟悉和掌握对数据表中数据的查询操作和SQL命令的使用，学会灵活熟练的使用SQL 语句的各种形式；2加深理解关系运算的各种操作（尤其的关系的选择，投影，连接和除运算）；3熟悉和掌握数据表中数据的插入、修改、删除操作和命令的使用；4加深理解表的定义对数据更新的作用二、预习与参考1熟悉SQL SERVER 工作环境；2连接到学生-课程数据库3复习对表中数据查询的SQL语言命令；4复习对表中数据的插入、修改和删除的SQL语言命令。三、实验要求（设计要求）针对教材例子，通过SQL SERVER企业管理器和查询分析器两种模式，熟悉数据嵌套查询、更新操作。四、实验方法及步骤在表、上进行简单查询；在表、上进行连接查询；在表、上进行嵌套查询；使用聚合函数的查询；对数据的分组查询；对数据的排序查询。7将数据插入当前数据库的表、中；：用命令形式：用SQL SERVER提供的企业管理器以交互方式进行8将以上插入的数据分别以.SQL文件和.txt文件的形式保存在磁盘上；9修改表、中的数据；：用命令形式：用SQL SERVER提供的企业管理器以交互方式进行10删除表、中的数据。：用命令形式：用SQL SERVER提供的企业管理器以交互方式进行五、实验内容在表S，C，SC上完成以下操作：1 查询学生的基本信息；2 查询“CS”系学生的基本信息；3 查询“CS”系学生年龄不在19到21之间的学生的学号、姓名；4 找出最大年龄；5 找出“CS”系年龄最大的学生，显示其学号、姓名；6 找出各系年龄最大的学生，显示其学号、姓名；7 统计“CS”系学生的人数；8 统计各系学生的人数，结果按升序排列；9 按系统计各系学生的平均年龄，结果按降序排列；10 查询每门课程的课程名；11 查询无先修课的课程的课程名和学时数；12 统计无先修课的课程的学时总数；13 统计每位学生选修课程的门数、学分及其平均成绩；14 统计选修每门课程的学生人数及各门课程的平均成绩；15 找出平均成绩在85分以上的学生，结果按系分组，并按平均成绩的升序排列；16 查询选修了“1”或“2”号课程的学生学号和姓名；17 查询选修了“1”和“2”号课程的学生学号和姓名；18 查询选修了课程名为“数据库系统”且成绩在60分以下的学生的学号、姓名和成绩；19 查询每位学生选修了课程的学生信息（显示：学号，姓名，课程号，课程名，成绩）；20 查询没有选修课程的学生的基本信息；21 查询选修了3门以上课程的学生学号；22 查询选修课程成绩至少有一门在80分以上的学生学号；23 查询选修课程成绩均在80分以上的学生学号；24 查询选修课程平均成绩在80分以上的学生学号；25 选做：针对SPJ数据库中的四个表S，P，J，SPJ，完成教材P74-75-习题5中的查询及教材P127-习题5中的查询。26 将数据分别插入表S、C、SC；27 将表S、C、SC中的数据分别以.SQL文件和.txt文件的形式保存在磁盘上。28 在表S、C、SC上练习数据的插入、修改、删除操作。（比较在表上定义/未定义主码（Primary Key）或外码（Foreign Key）时的情况）29 将表S、C、SC中的数据全部删除，再利用磁盘上备份的数据来恢复数据。30 如果要在表SC中插入某个学生的选课信息（如：学号为“200215121”，课程号为“c123”，成绩待定），应如何进行？31 求各系学生的平均成绩，并把结果存入数据库；32 将“CS”系全体学生的成绩置零；33 删除“CS”系全体学生的选课记录；34 删除学号为“S1”的相关信息；35 将学号为“S1”的学生的学号修改为“S001”；36 把平均成绩大于80分的男同学的学号和平均成绩存入另一个表SGRADE（SNO，AVGGRADE）；37 把选修了课程名为“数据结构”的学生的成绩提高10%；38 把选修了“C2”号课程，且成绩低于该门课程的平均成绩的学生的成绩提高5%；39 把选修了“C2”号课程，且成绩低于该门课程的平均成绩的学生成绩删除掉；40 选做：将数据插入SPJ数据库中的四个表S，P，J，SPJ中，并以.SQL文件和.txt文件的形式保存在磁盘上。六、实验成绩评定方法实验成绩总分以100分计，其中实验准备占30，实验操作占40，其它占30%。七、实验报告要求按规定格式撰写八、实验思考1. 使用SQL的更新语句时，一次可以对几个表进行更新。2. 在进行数据更新时，可能产生破坏数据完整性的情况，因此，在数据更新（尤其是多表更新）时，应注意什么问题。3. 比较表中定义/未定义主码（Primary Key）时，对表S的更新操作有何异同。4. 比较在表之间定义/未定义外码（Foreign Key）时，对表S的更新操作有何异同。5. 若要修改已定义表的属性或删除已定义表的某一属性列，应如何进行？6. 在表上未定义主码时，数据更新的情况？7. 在表S 和SC 表上建立了外码联系时，要删除学号为S2的学生记录，应如何进行？如果在表S 和SC 表上未建立外码联系，情况会是怎样的？ DELETE FROM s WHERE s.sno = s2 ; DELETE FROM sc WHERE sc.sno = s2 ; DELETE FROM sc WHERE sc.sno = s2 ; DELETE FROM s WHERE s.sno = s2 ;实验名称：试验二：通过嵌入式SQL访问数据库所属课程：数据库原理实验类型：设计型实验实验类别：基础 专业基础 专业实验学时：4学时一、实验目的应用C语言、ASP.net、VC#等开发工具，通过ODBC接口，实现嵌入式SQL语言与高级语言的通信，验证SQL以同一种语法提供的两种不同的使用方式，实现数据定义（Create 、Alter、Drop）、数据操作（Insert、Update、Delete、Select）和数据控制（Grant、Revoke）功能。二、预习与参考1熟悉SQL SERVER 工作环境；2连接到学生-课程数据库；3熟悉一种开发工具；4复习对表中数据查询的SQL语言命令；5复习对表中数据的插入、修改和删除的SQL语言命令。三、实验工具通过C语言等编写访问数据库的应用程序来对数据库进行各种数据操作。编程工具自选。四、实验内容和要求所有的SQL操作均在自己建立的新库里进行，在SQL Server 2000中建立一个名为TEMP的库进行各种操作。根据以下要求认真填写实验报告，并且提交源程序（开发语言可以用C、ASP.net、VC#等，下文以C/C+，或VC+为例），保证可正确编译运行。将一段连续的SQL操作写成嵌入到C程序中的程序，调试程序，使得程序能完成一项复杂的数据访问功能。 要求：提交程序五、实验成绩评定方法实验成绩总分以100分计，其中实验准备占30，实验操作占40，其它占30%。六、实验报告要求按规定格式撰写七、参考资料1嵌入式SQL由SQL语句和C/C+代码组成。其中SQL语句由预处理器翻译成C或C+的源代码。对预处理后的源代码进行编译、连接生成可执行程序后方可运行。2SQLServer的预处理程序是nsqlprep.exe。其常用的语法格式如下：Nsqlprep 程序文档名 nosqlaccess Nsqlprep详细的语法格式以及参数意义，请看联机帮助。要求程序文档名的后缀为.sqc，可以省略。3预编译后得到的文档，与程序文档同名，后缀为.c；放在与程序文档名同一个路径下。4nsqlprep.exe在SQLServer的安装目录的 MSSQLBinn下。例如，如果SQLServer的安装目录是D:Program FilesMicrosoft SQL Server，那么nsqlprep.exe则在D:Program FilesMicrosoft SQL ServerMSSQLBinn。但是，默认的安装方式（典型安装）并没有安装应用程序nsqlprep.exe，因此，需要把binn.rar的内容拷贝到该目录下。5连接方式经预处理后的c文件就可以用c的编译器进行编译连接了。使用Visual C+ 6.0进行编译连接，连接方式是动态连接，用到动态链接库SQLakw32.dll，SQLaiw32.dll；此两文件已经随同binn.rar的其他内容，被拷贝到D:Program FilesMicrosoft SQL ServerMSSQLBinn下；但仍然需要把该路径加到系统的路径变量中，以使得程序运行时能找到这两个文件。方法1：把该两文件拷贝到操作系统目录下的子目录system32中方法2：把D:Program FilesMicrosoft SQL ServerMSSQLBinn加到系统环境变量path中。“我的电脑”-“属性”-“高级”-“环境变量”-“path，编辑”，如下图所示：在变量值中加入该路径值；注意，路径间用分号”;”分开。6初始化环境：SQL Server2000为其嵌入式SQL提供了一些特殊的接口；默认的安装方式（典型安装）并没有安装这些接口；因此，需要把devtools.rar解压到SQLServer的系统目录下（注意，不是安装目录）；例如操作系统安装在C盘，则SQLServer的系统目录则是C:Program FilesMicrosoft SQL Server。后面的设置，假设操作系统安装在C盘。（1）初始化SQL Server的预编译环境。（2）初始化Visual C+ 6.0 编译器环境，运行文件：Microsoft Visual StudioVC98BinVCVARS32.BAT（3）初始化SQLServer的预编译环境，运行文件：devtoolssamplesesqlcsetenv.bat。（4）初始化Visual C+ 6.0环境。Tools-options-directories-Include Files: C:Program FilesMicrosoft SQL ServerdevtoolsincludeTools-options-directories-LibFiles:C:Program FilesMicrosoft SQL Serverdevtoolsx86lib最好把上面添加的两个路径，放在各自的列表首位，以防名字冲突。Project-Settings-Link-Object/Library Modules，添加库文件：SQLakw32.lib，Caw32.lib注意，两个文件之间用空格分开。实验名称：试验三：通过ODBC访问数据库实验类型：综合性实验实验类别：基础 专业基础 专业实验学时：2（4）所涉及的课程及知识点：数据库原理；嵌入式SQL语句、ODBC连接、数据查询和数据操纵，规范化、数据库设计、数据库优化一、实验目的SQL以同一种语法提供了两种不同的使用方式：交互式和嵌入式。通过本实验，使学生理解SQL语言与高级语言的通信，理解存储过程的概念和作用，掌握存储过程的编写，了解ODBC的工作原理，了解高级语言是怎样通过ODBC与数据库通信的。二、实验内容以SQL SERVER2000（或ORACLE9I）为后台数据库，以PowerBuilder（或其它工具）为前台开发工具，完成一个小型数据库系统的开发。从下列系统中选择一个或自拟课题，完成系统分析、设计和开发。1图书销售管理系统2通用工资管理系统3报刊订阅管理系统4医药销售管理系统5宾馆客房管理系统6学生选课管理系统7网上商店8其它管理子系统（自己选题需经指导教师认可）。三、实验仪器设备和材料清单要求：PowerDesigner、SQL SERVER 2000（或ORACLE9I）、PoweBuilder（或VC+）四、实验要求课外必须预习，能熟练使用PowerDesigner、SQL SERVER 2000（或ORACLE9I）、PoweBuilder等工具，根据实验任务，查阅资料，进行理论分析和研究，确定实验方案，或根据规定的实验方案，确定实验步骤。准备、编写实验程序，另安排了4学时的课外上机时间，学生可根据自己的实际情况，延长课外机时。各系统具体要求：1图书销售管理系统调查新华书店图书销售业务、设计的图书销售管理系统主要包括进货、退货、统计、销售功能。（1）进货：根据某种图书的库存量及销售情况确定进货数量，根据供应商报价选择供应商。输出进货单并自动修改库存量，把进货信息加到进货库中。（2）退货：顾客将已买的书退还给书店。输出退货单并自动修改库存量，把退货信息加到退货库中。（3）统计：根据销售情况输出统计报表，一般包括每月的销售总额、销售总量及销售排行榜。（4）销售：输入顾客要买书籍的信息，自动显示此书的库存量，如可以销售，打印销售单并修改库存，同时将此次销售的有关信息填加到日销售库中。2通用工资管理系统考察某中小型企业，设计一套企业工资管理系统，其中应具有一定的人事档案管理功能。通过对职工建立人事档案，根据其考勤情况以及相应的工资级别，算出其相应的工资。为了减少输入帐目的错误，可以根据职工的考勤、职务、部门和各种税费自动求出工资。为便于掌握企业的工资信息，在系统中应有各种查询功能，包括个人信息、职工工资、本企业一个月工资情况查询、某一部门的工资情况查询。系统应能输出各种统计报表。3报刊订阅管理系统通过对某企业的报刊订阅业务进行分析、调查，设计该企业的报刊订阅管理系统。主要实现以下功能：（1）录入功能：录入订阅人员信息、报刊基本信息。（2）订阅管理：订阅人员订阅报刊（并计算出金额）。（3）查询功能：按人员查询、报刊查询、部门查询有关订阅信息。对查询结果能预览和打印。（4）统计功能：按报刊统计、按人员统计、按部门统计，对统计结果能预览和打印。（5）系统维护。4医药销售管理系统调查从事医药产品的零售、批发等工作的企业，根据具体情况设计医药销售管理系统。主要功能包括：（1）基础信息管理：药品信息、员工信息、客户信息、供应商信息等（2）进货管理：入库登记、入库登记查询、入库报表等。（3）库房管理：库存查询、库存盘点、退货处理、库存报表等。（4）销售管理：销售登记、销售退货、销售报表及相应的查询等。（5）财务统计：当日统计、当月统计及相应报表等。（6）系统维护。5宾馆客房管理系统考察本市的宾馆，设计客房管理系统，要求：（1）方便的登记、结帐功能、预定客房功能、支持团体登记、结帐。（2）能快速了解宾馆内客房状态。（3）多种方式查询客人信息。（4）完善的结帐报表。（5）安全的系统维护。6学生选课管理系统调查所在的学校教务处，设计学生选课管理系统。要求：（1）选修课程计划管理。（2）选修课程开课管理。（3）学生选课管理。（4）学生选课成绩管理。（5）打印报表。要求：设计学生选课录入界面及学生选课查询界面。设计课程输入界面、学生选课表、课程选修情况查询界面。选课冲突时，按学号或其他分配课程。系统维护。7网上商店调查当当网、淘宝网等网上商店的功能，调研并选择一个自认为有前途的领域设计开发网上商店。五、实验步骤1分析系统功能，画出数据流图，书写数据字典2设计概念模型：用PowerDesigner画出E-R模型3用PowerDesigner生成逻辑模型和物理模型4生成SQL SERVER（或ORACLE9I）数据表，并输入一定的测试数据5配置ODBC（1）打开ODBC数据源管理器（2）点击“添加”按钮，进入下面窗口后，选择“SQL SERVER”驱动程序（以下以SQL SERVER为例）。3点击“完成”安钮。进入下面窗口后，在“名称”栏输入数据源的名称Mytest，在“描述”栏加一说明，在“服务器”栏选择装有数据的数据服务器。然后按照提示操作。6启动PowerBuilder应用程序，配置预置文件（DataBase Profiles）Mytest，设置相应参数。7新建立一个工作空间，建立一个应用程序Myapp。在应用Myapp中的open事件内输入数据库连接参数。/ Profile mytestSQLCA.DBMS = ODBCSQLCA.AutoCommit = FalseSQLCA.DBParm = ConnectString=DSN=mytest;UID=sa;PWD=8新建数据窗口对象，用于显示数据和更新数据9新建窗口对象，用于显示数据窗口对象、各种按钮及系统功能实现。10根据系统功能要求，编程实现数据的查询和更新。六、实验成绩评定方法实验成绩总分以100分计，其中实验准备占30，实验操作占40，其它占30。七、实验报告要求要求学生从实验方法的建立、实验步骤的设计、实验数据的处理、实验结果的分析讨论等方面写出报告。报告包含：1系统需求分析等 2主要数据结构（数据字典和数据表结构） 3主要算法 4遇到的问题及解决方法 5体会与建议6可正确编译的可读性好的源代码八、思考题1触发器的作用是什么？2触发器和存储过程的区别是什么？3存储过程和函数的区别是什么？4ODBC的作用是什么？九、参考资料例子：#include #include #include #include #include #define SNO_LEN 30#define NAME_LEN 50#define DEPART_LEN 100#define SSEX_LEN 5int main()/以king开头的表示的是连接KINGBASEES的变量/以server开头的表示的是连接SQLSERVER的变量SQLHENV kinghenv, serverhenv; /环境句柄SQLHDBC kinghdbc,serverhdbc; /连接句柄SQLHSTMT kinghstmt,serverhstmt; /语句句柄SQLRETURNret;SQLCHAR sNameNAME_LEN, sDepartDEPART_LEN,sSexSSEX_LEN,sSnoSNO_LEN;SQLINTEGER sAge;SQLINTEGER cbAge = 0, cbSno = SQL_NTS, cbSex = SQL_NTS, cbName = SQL_NTS,cbDepart = SQL_NTS;/* Step 2 初始化环境 */ret = SQLAllocEnv(&kinghenv);ret = SQLAllocEnv(&serverhenv);/* Step 3 :建立连接 */ret = SQLAllocHandle(SQL_HANDLE_DBC, kinghenv, &kinghdbc);ret = SQLConnect(kinghdbc,KINGBASE,SQL_NTS,SYSTEM,SQL_NTS,MANAGER,SQL_NTS);ret = SQLAllocHandle(SQL_HANDLE_DBC, serverhenv, &serverhdbc);ret = SQLConnect(serverhdbc,KINGBASE2,SQL_NTS,SYSTEM,SQL_NTS,MANAGER,SQL_NTS);/* Step 4 :初始化语句句柄 */ret = SQLAllocHandle(SQL_HANDLE_STMT, kinghdbc, &kinghstmt);ret=SQLSetStmtAttr(kinghstmt,SQL_ATTR_ROW_BIND_TYPE,(SQLPOINTER)SQL_BIND_BY_COLUMN,SQL_IS_INTEGER );/* Step 5 :两种方式执行语句 */* 预编译带有参数的语句 */ret = SQLPrepare(serverhstmt,INSERT INTO STUDENT (SNO,SNAME,SEX,AGE,DEPARTMENT) VALUES (?, ?, ?, ?, ?), SQL_NTS);if (ret = SQL_SUCCESS | ret = SQL_SUCCESS_WITH_INFO) SQLBindParameter(serverhstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR,SQL_CHAR, SNO_LEN, 0, sSno, 0, &cbSno); SQLBindParameter(serverhstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR,SQL_CHAR, NAME_LEN, 0, sName, 0, &cbName);SQLBindParameter(serverhstmt, 3, SQL_PARAM_INPUT, SQL_C_CHAR,SQL_CHAR, 2, 0, sSex, 0, &cbSex);SQLBindParameter(serverhstmt, 4, SQL_PARAM_INPUT, SQL_C_LONG,SQL_I