《数据库原理》实验备课教案.docx

1、数据库原理实验备课教案数据库原理实验备课笔记赵震目 录实验一 Access数据库的基本操作3实验二 SQL数据定义语言7实验三 SQL数据查询9实验四 SQL数据操纵.11实验五 视图的定义、查询与更新12实验六 数据库的完整性与安全性14实验七 数据库的并发控制16实验八 简单应用系统开发19实验一 Access数据库的基本操作一、实验目的通过该实验初步了解关系数据库的基本概念，学会QBE查询语言的使用。掌握Access数据库的建立以及各种查询的建立方法。了解Access数据库的其他功能。二、实验仪器PII以上个人计算机三、实验原理关系数据库系统是支持关系模型的数据库系统。关系模型由关系数据

2、结构、关系操作集合和关系完整性元素三部分组成。关系模型中数据的逻辑结构是一张二维表。关系模型中常用的关系操作包括：选择、投影、连接、并、交、差等查询操作，还包括插入、删除和修改等更新操作。QBE是一种域关系演算语言，在Access数据库中可以通过建立查询实现QBE的操作。四、实验内容与步骤（一）实验内容1. 用Access建立一个学生选课数据库，数据库名为Student，其中包括下面3个表，Student表记录学生信息、Course表记录课程信息、SC表记录学生选课信息。要求每个表都建立主键，并输入表中的数据。表1.1 Student表数据SnoSnameSsexSageSdept95001李

3、勇男20CS95002刘晨女19IS95003王敏女18MA95004张立男19IS表1.2 Course表数据CnoCnameCpnoCcredit1数据库542数学23信息系统144操作系统635数据结构746数据处理27PASCAL语言64表1.3 SC表数据SnoCnoGrade950011929500128595001388950022909500238095003487950035922使用Access的QBE建立如下查询：（1）求信息系(IS)全体学生的姓名，查询名称保存为View_1。（2）查询全体学生的全部数据，查询名称保存为View_2。（3） 求年龄大于19岁的学生的学号

4、，查询名称保存为View_3。（4）求计算机系年龄大于19岁的学生的学号，查询名称保存为View_4。（5）查询算机系或者年龄大于19岁的学生的学号，查询名称保存为View_5。（6）查询选修1号课的学生姓名，查询名称保存为View_6。（7）查询信息系学生的平均年龄，查询名称保存为View_7。（8）查全体男生的姓名，要求查询结果按所在系升序排序，对相同系的学生按年龄降序排序，查询名称保存为View_8。3使用Access的QBE完成下列更新操作，要求用查询完成：（1）把95001学生的年龄该为18岁，查询名称保存为View_a。(使用更新查询)（2）把所有学生的年龄都增加1岁，查询名称保存

5、为View_b。(使用更新查询)（3）把信息系女生95701，姓名张三，年龄17岁存入数据库，查询名称保存为View_c。(使用追加查询)（4）删除学生95089，查询名称保存为View_d。(使用删除查询)（5）删除信息系（IS）全体学生记录，查询名称保存为View_e。(使用删除查询)（二）实验步骤（1）启动Access，建立名为student的数据库。（2）在数据库窗口中选择“使用设计器创建表”，点击“新建”按钮，在出现的“新建表”窗口中选择“设计视图”，点击“确定”按钮，在出现的窗口中输入表的字段名和选择数据类型，如图1.1所示：图1.1 表创建窗口 主键的设置：选中要设置主键的字段前

6、面的方块，按右键，在弹出的菜单中选择“主键”，则该字段就被设置为主键。如果表的主键是由多个字段组成，则需要将多个字段同时选中（按住Shift键），然后按照上面步骤设置主键。（3）点击工具栏的“保存”按钮，在弹出的对话框中输入表的名称，单击“确定”按钮。这样就在数据库中建立了一个表。（4）输入数据。在数据库窗口中选择要输入数据的表。点击“打开”按钮（或者直接双击），打开如图1.2所示的表数据录入窗口，在该窗口中可以直接录入表的数据。数据录入结束后，点击关闭按钮，返回到数据库界面。图1.2 表数据录入窗口 （5）建立查询：Access数据库提供了多种查询方法，其中使用最多的是选择查询。建立选择查询

7、的具体步骤如下：在数据库窗口中，选择“查询”对象，选择“在设计视图中创建查询”，点击“新建”按钮，在弹出的“新建查询”对话框中选择“设计视图”，然后点击“确定”按钮。接下来弹出一个“显示表”对话框，从中选择要建立查询的表，点击“添加”按钮，最后点击“关闭”按钮，结果如图所示，该界面就是Access数据库的QBE查询界面：图1.3 Access数据库的QBE窗口在该窗口中，我们就可以构造查询。例如，要查询学生表的所有数据，可以在“字段”行中选中所有的字段，并保证“显示”行的复选框处于选中状态。如果想看结果，可以选择“视图”按钮中的“数据库表视图”。选择“设计视图”可返回设计界面。如果选择“SQL

8、视图”将显示该查询的SQL语句。五、实验报告要求1要求写明实验目的、实验原理、实验内容和实验步骤；2. 对实验中出现的问题和结果进行讨论。六、思考题1. Access数据库中可以建立哪些类型的查询？2Access数据库中的QBE与教材中的QBE有什么不同？实验二 SQL数据定义语言一、实验目的：熟悉SQL*Plus工具的启动及运行环境。掌握SQL的数据定义语言的使用；理解数据库对象的概念。二、实验原理：SQL语言是关系数据库的标准语言。SQL语言是集数据定义语言DDL、数据操纵语言DML、数据控制语言DCL的功能于一体，语言风格统一，可以独立完成数据库生命周期中的全部活动。数据库的对象可以使用

9、SQL语言DDL加以定义。三、实验仪器：PII以上个人计算机四、实验内容与步骤：1. 在SQL*Plus环境下，使用SQL语言定义Student表，该表的结构描述如下： 列名 数据类型 是否为空？ 是否主码 Sno CHAR(5) 否 是 Sname VARCHAR2(10) 否 否 Ssex CHAR(4) 要求性别只能为男或女 Sage NUMBER(2) 可以 年龄限制在16到25之间(包含)。 Sdept CHAR(4) 注意，主码的定义和约束条件的定义。2. 在SQL*Plus环境下，使用SQL语言定义Course表，该表的结构描述如下： 列名 数据类型 是否为空？ 是否主码 Cno

10、 CHAR(2) 否 是 Cname CHAR(14) 否 否 Cpno CHAR(2) 是 否 CCredit NUMBER(1) 否 否 注意，要将Cno定义为主码。3. 在SQL*Plus环境下，使用SQL语言定义Course表，该表的结构描述如下： 列名 数据类型 是否为空？ 是否主码 Sno CHAR(5) 否 是 Cno CHAR(2) 否 是 Grade NUMBER(3) 是 否 注意，该表的主码为Sno和Cno的组合，同时Sno和Cno都是外码。要求定义表时定义这些主码和外码。4. 插入数据 使用INSERT语句将实验一中3个表的数据分别插入这3个表中。 可以使用SELECT

11、语句查看表中的数据。5. 使用ALTER TABLE 语句修改Student表为其增加一个BirthDay列，类型为DATE。6. 使用CREATE INDEX语句在Student表的Sage列上建立一个名为Age_Index的索引，要求索引为升序。使用SELECT语句查询Student表，看有什么变化？为什么？使用DROP INDEX语句删除该索引。五、实验报告要求1要求写明实验目的、实验原理、实验内容和实验步骤；2. 对实验中出现的问题和结果进行讨论。六、思考题：1. 使用SQL定义数据库对象时，这些定义存放在哪里？2. 在什么时候会用到这些定义？实验三 SQL数据查询一、实验目的：熟练掌

12、握SQL查询语句的使用。主要练习各种查询语句的构造，其中包括单表查询、连接查询、嵌套查询和集合查询。重点掌握连接查询和嵌套查询二、实验原理：数据库的查询是数据库的核心操作。SQL语言提供了SELECT语句进行数据库的查询，该语句具有灵活的使用方式和丰富的功能。数据库查询主要包括：单表查询、连接查询、嵌套查询和集合查询几类。在查询中可以通过WHERE条件语句筛选查询结果、通过GROUP BY子句对查询结果分组、通过ORDER BY子句对查询结果排序。连接查询是涉及到两个或两个以上的表的查询。其中连接查询有包括等值连接、自身连接、外连接等。嵌套查询也叫带子查询的查询。它是在一个查询语句的WHERE

13、子句或GROUP BY短语中嵌入另一个查询的查询。三、实验仪器：PII以上个人计算机四、实验内容和步骤：以下操作在学生-课程数据库中完成1. 单表查询（1）查询全体学生的详细信息；（2）查询全体学生的姓名，学号和所在系；（3）查询全体学生的姓名及出生年份；（4）查询学校共有哪些系；（5）查询所有年龄在20岁以下的学生姓名及其年龄；（6）查询年龄不在2023岁之间的学生姓名、系别和年龄 。（7）查询课程表中课程名中包含“数据”的课程信息。（8）查询全体学生情况，查询结果按所在系升序排列，系相同按学生年龄降序排列。（9）查询选修了课程的学生人数。（10）查询选修了“数据库”课程的人数、最高分、最低

14、分和平均分。（11）查询男女同学的平均年龄。（12）查询选修了3门以上课程的学生学号。（13）查询有10人以上选修的课程名。2. 连接查询（1）查询每个学生的选课情况。（2）查询每门课程的课程号及间接先修课号。（3）查询既选修了1号课又选修了3号课的学生学号（用自身连接）。（4）查询至少有两人选修的课程号（用自身连接）。（5）查询一门课也没选的学生信息。（用外连接）（6）查询选修2号课且成绩在90分以上的学生学号和姓名。（7）查询选修了“数据库”课且成绩不及格的学生姓名。3. 嵌套查询（1）查询与“刘晨”同学在一个系学习的学生学号、姓名及所在系。（2）查询选修了“信息系统”课程的学生学号和姓名

15、。（3）查询其他系中比信息系某一学生年龄小的学生姓名和年龄。（4）查询其他系中比信息系所有学生年龄都小的学生姓名和年龄。（5）查询没有选修1号课的学生姓名。（6）查询选修了全部课程的学生姓名。（7）查询至少选修了学生95002选修的全部课程的学生学号。4. 集合查询假设有一个Teacher表记录教师的信息，其中包括Tname表示教师名。用UNION、INTERSECT和MINUS完成下列查询。（1）求计算机系的学生和年龄不大于19岁的学生的并集。（2）查询所有在职学生信息。（3）查询现在没有正在进修的教师信息。（4）查询学校所有教师和学生姓名。五、实验报告要求1要求写明实验目的、实验原理、实验

16、内容和实验步骤；2. 对实验中出现的问题和结果进行讨论。六、思考题： 1. 各种类型的查询都适用于什么情况？2. 为什么说嵌套查询比连接查询效率高？实验四 SQL数据操纵一、实验目的：熟练掌握SQL数据更新语句，其中包括INSERT、UPDATE、DELETE三个语句。学会INSERT、UPDATE、DELETE三个语句的使用和注意事项。二、实验原理 对数据库中数据的更新需要使用SQL的INSERT、UPDATED和ELETE语句实现。INSERT语句用来向表中插入数据，UPDATE语句用来修改表中的数据，DELETE语句用来删除表中的数据。对数据库的更新特别要注意保证数据库数据的一致性。三、

17、实验仪器：PII以上个人计算机四、实验内容和步骤 1. 针对学生-课程数据库，完成下列操作：（1）使用INSERT语句向Student表中插入一学生记录，该学生学号为95020，姓名为“陈东”，性别为“男”，年龄为18岁，所在系为信息系。（2）向SC表中插入一行选课记录（95020,5），现在还没有成绩。（3）对每一个系，求学生的平均年龄，并把结果存入数据库。（4）将学生95001所在的系改为MA。（5）将选修“数据库”课程的学生成绩提高10%。（6）删除计算机系所有学生的选课记录。五、实验报告要求1要求写明实验目的、实验原理、实验内容和实验步骤；2. 对实验中出现的问题和结果进行讨论。六、思

18、考题：1. 说明更新操作如何保证数据库的一致性？实验五 视图的定义、查询与更新一、实验目的：熟练掌握使用SQL的CREATE VIEW语句和DROP VIEW语句建立视图和删除视图。能够区分不同类型的视图，其中包括行列子集视图、带表达式的视图和分组视图。掌握对视图的查询。掌握视图更新的限制。二、实验原理视图是关系数据库提供给用户以多种角度观察数据库中数据的重要机制。视图是从一个或几个基本表（或视图）导出的表，虚表。视图只存放视图的定义，不会出现数据冗余。基表中的数据发生变化，从视图中查询出的数据也随之改变。视图操作可以使用SQL的CREATE VIEW、DROP VIEW语句。视图创建后可以像

19、基本表一样查询。三、实验仪器：PII以上个人计算机四、实验内容和步骤1. 建立信息系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有信息系的学生。2. 建立信息系选修了1号课的学生的视图IS_S1。3. 建立信息系选修了1号课且成绩在90分以上的学生视图IS_S2。4. 将学生的学号及他的平均成绩定义为一个视图S_G。5. 查询信息系选修1号课的的学生学号和姓名，要求使用视图IS_Student。6. 将信息系学生视图IS_Student中的学号为95002的学生姓名该为“刘辰”。7. 向信息系学生视图IS_Student中插入一个新的学生记录，其中学号为95029，姓名为赵新，年龄为

20、20岁。五、实验报告要求1要求写明实验目的、实验原理、实验内容和实验步骤；2. 对实验中出现的问题和结果进行讨论。六、思考题：1. 视图有哪些作用？2. 如何理解对视图的查询最终都转换成对基本表的查询？3. 那些视图可以更新的，哪些视图是不可更新的。实验六 数据库的完整性与安全性一、实验目的：通过该实验使学生了解和掌握数据库完整性的定义与检查，数据库安全性的定义与检查。学会通过CREATE TABLE语句定义完整性约束，通过使用触发器实现完整性检查。学会通过GRANT、REVOKE语句进行授权和回收权限。学会通过建立触发器实现安全性检查。二、实验原理DBMS的完整性控制机制具有三方面的功能：（

21、1）定义功能，提供定义完整性约束条件的机制。（2）检查功能，检查用户发出的操作请求是否违背了完整性约束条件。（3）处理功能，如果发现用户操作请求使数据违背了完整性约束条件，则采取一定的动作来保证数据的完整性。在完整性约束中，外码的约束是最重要的一种约束。这主要涉及到下面四个方面：（1）外码能否接受空值的问题（2）在被参照关系中删除元组的问题（3）在参照关系中插入元组的问题（4）修改关系中主码的问题在数据库中，可以通过GRANT和REVOKE语句实现安全性的控制，也可以通过触发器实现安全性检查。另外，通过视图也可以实现安全性。三、实验仪器：PII以上PC计算机四、实验内容与步骤：1. 通过CRE

22、ATE TABLE语句实现完整性约束的定义具体步骤：（1）建立一个部门表DEPT，其中包括的列及约束如下：Deptno表示部门号，要求取值为1000到9999四位整数，并作为该表的主码；Dname表示部门名，要求部门名唯一；Dloc表示部门的地点，类型为VARCHAR2，宽度为20。（2）建立一个职工表EMP，其中包括的列及约束如下： Empno表示雇员号，要求取值为100000到999999六位整数，并作为该表的主码； Ename表示雇员名，要求不能取空值，不要求取值唯一。 Sal表示实发工资，Deduct表示扣除项，要求应发工资不得超过5000元。应发工资为实发工资和扣除项之和，这两列的数

23、据类型为NUMBER(7,2)。 Deptno表示部门号，它要参照DEPT表中的Deptno列的取值。（3）在上述两个表中输入一些数据。（1000，数学系，理工3号楼） （2000，物理系，理工2号楼） （3000，化学系，理工1号楼）再在DEPT表中输入下面记录，看会出现什么结果，为什么？：（999，计算机，理工3号楼）（3000，工商管理系，经法楼）2. 通过触发器实现完整性规则。（1）建立一个名为Teacher的表，其中包括Tno，Tname，Pos，Sal列，它们的含义分别为教师编号，教师姓名，职称和工资。（2）使用CREATE TRIGGER语句定义触发器，规则是教授的工资不得低于1

24、000元，如果低于1000元，自动改为1000元。 CREATE TRIGGER Update_Sal BEFORE INSERT OR UPDATE OF Sal, Pos ON Teacher FOR EACH ROW WHEN (new.Pos=教授) BEGIN IF :new.Sal1000 THEN :new.Sal :=1000; END IF; END;3. 使用GRANT和REVOKE实现安全性控制（1）把对表SC的INSERT权限授予用户U5，并允许他将此权限再授予其他用户。（2）把查询Student表和修改学生学号的权限授给用户U4。（3）把用户U4修改学生学号的权限收回

25、。五、实验报告要求1要求写明实验目的、实验原理、实验内容和实验步骤；2. 对实验中出现的问题和结果进行讨论。六、思考题1. 数据库的完整性有哪几类：2. 在数据库中实现安全性控制措施有哪些？实验七 数据库的并发控制一、实验目的：通过该实验使学生了解数据库的并发控制机制，掌握封锁机制的原理。二、实验原理：Oracle数据库采用封锁技术保证并发操作的可串行性。Oracle的锁分为两大类：数据锁和字典锁。数据锁也叫DML锁，Oracle提供了5种数据锁：共享锁（S锁）、排它锁（X锁）、行级共享锁（RS锁）、行级排它锁（RX锁）和共享行级排它锁（SRX锁）。其封锁粒度包括行级和表级。在通常情况下，Or

26、acle数据封锁由系统控制，对用户是透明的。Oracle也允许用户用LOCK TABLE语句显式对数据对象加锁。Oracle数据锁的一个显著特点是，在缺省情况下，读数据不加锁。也就是说，当一个用户更新数据时，另一个用户可以同时读取相应的数据，反之亦然。Oracle提供了有效的死锁检测机制，周期性诊断系统中有无死锁，若存在死锁，则撤消执行更新次数最少的事务。三、实验仪器PII以上PC计算机四、实验内容与步骤1. 封锁的测试具体步骤：（1）用同一个帐户启动两个SQL*Plus窗口，模拟两个用户并发操作。（2）在第一个窗口中输入下列语句：UPDATE Student SET Sage=20 WHER

27、E Sno=95001;此时，第一个用户更新数据，即对更新的行加了锁。你能否测试加了什么锁，是行级共享锁（RS锁）还是行级排它锁（RX锁）？（3）在第二个窗口中输入下列语句：UPDATE Student SET Ssex=女 WHERE Sno=95001;此时，该语句不能立即执行，因为要更新的行（Sno=95001）已被第一个用户封锁了，该用户必须等待。（4）在第一个窗口中输入SQL语句： COMMIT;此时，第一个用户提交了他的事务，他就释放了在Sno=95001行上的锁，第二个用户才能获得在该行上的锁，得以继续执行，同时他也持有对该行的锁。（5）接下来，在第一个窗口中执行下列SQL语句：

28、 DELETE FROM Student WHERE Sno=95001;该语句能否执行，为什么？2. 测试死琐的发生（1）启动两个SQL*Plus,模拟两个用户并发操作。（2）在第一个窗口中输入下列语句：UPDATE Student SET Sage=25 WHERE Sno=95001;（3）在第二个窗口中输入下列语句： UPDATE Student SET Ssex=女 WHERE Sno=95002;（4）接下来，在第一个窗口中输入下列语句： UPDATE Student SET Sage=20 WHERE Sno=95002; 注意，此时因为Sno=95002行已被第二个用户封锁，该

29、用户的更新语句不能继续执行，需等待用户2释放在该行上的锁。（5）在第二个窗口中输入下列SQL语句：UPDATE Student SET Ssex=男 WHERE Sno=95001;注意，此时由于Sno=95001行已被用户1封锁，因此该用户必须等待用户1释放该行上的锁。这样，用户1等待用户2，用户2等待用户1，这就产生了死锁。但请注意，Oracle对死锁的处理是：当检测到死锁发生，它将撤消一个事务的执行。上面的封锁属于隐式加锁。3. 使用LOCK TABLE显式加锁Oracle提供了LOCK TABLE语句实现对表的显式加锁，该语句的一般格式为：LOCK TABLE IN MODE；其中可以为SHARE和EXCLUSIVE，SHARE表示共享锁，EXCLUSIVE表示排它锁。五、实验报告要求1要求写明实验目的、实验原理、实验内容和实验步骤；2. 对实验中出现的问题和结果进行讨论。六、思考题：1. 如果系统对某行隐式加锁了，其他用户能否对没有加锁的行做更新操作？2. 使用LOCK TABLE语句将Course表加上排它锁。实验八 简单应用系统开发一、实验目的：本实验主要培养学生综合运用知识的能力。该实验涉及到所学的数据库的各种知识。其中包括SQL语言以及数据库设计的知识。要求完成一个实际部门的数据库应用系统设计的全过程，从中体验数据库