数据库原理实验教学讲稿Word格式.docx

数据库原理实验教学讲稿Word格式.docx

《数据库原理实验教学讲稿Word格式.docx》由会员分享，可在线阅读，更多相关《数据库原理实验教学讲稿Word格式.docx（13页珍藏版）》请在冰豆网上搜索。

\MSSQL\DATA\COMPANY.MDF,大小为20MB,最大为50MB,以25%的速度增长。

日志文件的逻辑名称为COMPANY_LOG,操作系统文件的名称为D:

\MSSQL\DATA\COMPANY.LDF,大小为3MB,最大为10MB,以1MB的速度增长。

2.修改数据库

（1）将数据库COMPANY1的主数据文件的初始分配空间大小扩充到40MB.

（2）将数据库COMPANY1改名为COMPANY

3.创建表

在名为COMPANY数据库中创建以下四个表：

（1）部门表，表名DEPA

列名数据类型描述

DNODECIMAL

（2）部门编号

DNAMEVARCHAR（16）部门名称

ADDRVARCHAR（20）部门地址

（2）雇员表，表名EMPL

ENODECIMAL（4）员工编号

ENAMECHAR（8）员工姓名

BIRTHDATEDATETIME出生日期

SALARYDECIMAL（7,2）工资

DNODECIMAL

（2）所在部门编号

（3）项目表，表名PROJ

PNODECIMAL（3）项目编号

PNAMEVARCHAR（24）项目名称

（4）工作表，表名JOB

DAYSINT工作天数

4.修改表结构

1）修改部门表DEPA，添加一列部门电话，列名Telephone，数据类型VARCHAR（15）。

2）为项目表PROJ添加Begindate和Enddate列，数据类型为DATETIME。

3）删除项目表PROJ中的Enddate列。

5.删除表

1）删除项目表PROJ。

6、添加记录：

1）向DEPA表添加14号部门，客户中心，地址为开发区紫光路2号。

2）向DEPA表添加15号部门，技术支持部，地址未详。

3）向DEPA表添加13号部门，财务部，地址未详。

……

4）向EMPL表添加1401号员工，张山，出生日期1977年9月1日，工资4050元，14号部门。

5）向EMPL表添加1402号员工，何宜，生日不祥，工资不祥，15号部门。

6）向PROJ表添加103号项目，历史学院档案库管理系统。

7）向PROJ表添加104号项目，ATM机管理系统。

8）向JOB表添加1401号员工，参与了104号项目35天。

9）向JOB表添加1402号员工，参与了103号项目20天。

7、修改记录：

1）把DEPA表中’客户中心’的地址改为’大连甘井子区红岭路’;

2）把DEPA表中15号部门的地址用沈阳东郊路120号填充。

3）把EMPL表中何宜的生日、工资用1981年12月20日，3000元填充。

4）把EMPL表中张山的部门号改为15号。

5）把JOB表中参与104号项目的每人增加5天。

6）把JOB表中参与103号项目的每人天数乘以系数0.8。

8、删除记录：

1）删除地址为空的部门。

2）删除JOB表中参与104号项目的员工。

3）删除JOB表中天数低于25天的员工。

4）删除生于1980年后，且工资低于4000的员工。

5）删除属于14号部门的员工。

6）删除15号部门的信息。

9．使用对象资源管理器分离和附加数据库COMPANY

实验2数据库的单表查询、分组查询与聚集函数的使用

本实验的目的是使学生熟悉SQLServerManagementStudio的使用方法。

加深对SQL语言的查询语句的理解。

熟练掌握数据库的单表查询（包括投影、选择条件表达，数据排序，模糊查询等）,熟练掌握数据库的分组、统计、计算和集合的操作方法。

二、实验时数2学时

1.简单的SELECT查询

1）查询雇员表Empl中所有员工的详细信息，并且列名用汉字表示。

2）查询项目表Proj中所有项目的详细信息，并且列名用汉字表示。

2.使用WHERE子句进行查询

1）查询雇员表Empl中李强的员工编号。

2）查询项目表Proj中项目编号为“101”的项目名称。

3.使用DISTINCT关键字去掉重复行

1）查询雇员表Empl中出现的所有部门编号，要求无重复。

2）查询工作表Job中参与过项目开发的所有员工编号，要求无重复。

4.使用ORDERBY子句对查询结果进行排序

1）查询雇员表Empl中所有员工的详细信息，结果按员工姓名降序排列。

2）查询工作表Job中的工作信息，结果按工作天数升序排列。

5.使用LIKE子句进行模糊查询

1）查询所有姓王的员工的编号及姓名，并且以员工编号及员工姓名作为列名。

2）查询雇员表Empl中姓名以“伟”字结尾的员工信息。

6.使用聚集函数MIN、MAX进行查询

1）查询雇员表Empl中员工的最低工资，并且列名用“最低工资”表示。

2）查询雇员表Empl中员工的最高工资，并且列名用“最高工资”表示。

7.使用聚集函数COUNT进行查询

1）查询雇员表Empl中所有员工的总人数，并且列名用“员工总数”表示。

2）查询14号部门的人数，并且列名用“客户中心部人数”表示。

8.使用分组子句GROUPBY与聚集函数MIN、MAX进行查询

1）查询每个部门中年龄最大的员工，显示部门编号和出生日期。

2）分别统计雇员表Empl中各部门员工的最高工资，并且列名分别用“部门编号”及“最高工资”表示。

9.使用分组子句GROUPBY与聚集函数SUM、AVG进行查询

1）分别统计雇员表Empl中各部门员工的工资总额，并且列名分别用“部门编号”及“各部门工资总额”表示。

2）分别统计雇员表Empl中各部门员工工资的平均数，并且列名分别用“部门编号”及“各部门工资平均数”表示。

10.使用分组子句GROUPBY与聚集函数COUNT进行查询

1）统计员工人数多于4人的部门信息，并且列名分别用“部门编号”及“员工人数”表示。

2）分别统计工作表Job中各项目的参与开发的员工人数，并且列名分别用“项目编号”及“员工人数”表示。

实验3数据库的多表连接和子查询

本实验的目的是使学生加深对SQL和SQL语言的查询语句的理解。

熟练掌握数据库的多表连接查询和子查询。

㈠多表连接查询：

1.对两张数据表使用内连接进行查询

1）查询姓名为“王一伟”的员工所在部门的名称，并且列名用汉字表示。

2）查询编号为“1002”的员工姓名及该员工所在部门的名称，并且列名用汉字表示。

2.对多张数据表使用内连接进行查询

1）查询姓名为“陈晨”的员工参与过的所有项目的名称，并且列名用汉字表示。

2）查询编号为“1202”的员工姓名及该员工参与过的所有项目的名称，并且列名用汉字表示。

3.对两张数据表使用左连接进行查询

1）使用左连接查询所有员工的基本信息及参与项目开发情况，如果某员工参与过项目开发，则列出该员工所参与的所有项目的编号，否则该项以空值表示。

2）使用左连接查询所有员工的基本信息及所在部门名称，如果某员工尚未分配到任何部门，则该员工的部门名称项以空值表示。

4.对两张数据表使用右连接进行查询

1）使用右连接查询所有员工的基本信息及参与项目开发情况，如果某员工参与过项目开发，则列出该员工所参与的所有项目的编号，否则该项以空值表示。

2）使用右连接查询所有员工的基本信息及所在部门名称，如果某员工尚未分配到任何部门，则该员工的部门名称项以空值表示。

㈡子查询：

1.多表单值子查询

1）查询编号为1001的员工的部门名。

2）查询‘客户中心’部门的员工数。

3）查询陈晨所在的项目编号。

2.多表多值子查询

1）查询1985年以后出生的员工在所在项目的工作天数。

2）查询参与项目天数超过40天的员工姓名。

3）查询工资低于2000的员工的所属部门。

3.多层嵌套子查询

1）查询‘客户中心'

部门中，收入高于平均工资的员工。

2）查询“开发系统”项目的参与部门。

3）查询'

宣传'

部门雇员参加的项目名称。

4．相关子查询

1）查询参与两个项目以上的员工姓名。

2）查询所有未参与项目的员工姓名。

实验4数据库的视图与索引定义

本实验的目的是使学生掌握索引与视图的定义与维护操作，加深对索引与视图在关系数据库中的作用的理解。

[相关知识]

建立索引是加快查询速度的有效手段。

用户可以根据应用环境的需要，在基本表上建立一个或多个索引，以提供多个存取路径，加快查找速度。

视图是根据子模式建立的虚拟表。

一个视图可以由一个表构造，也可以由多个表构造。

查看和修改视图就如同对表的操作一样，非常容易。

㈠索引

1.创建索引

1）为PROJ表的PNAME列创建非聚集索引Pindex。

2）为DEPA表的DNAME（降序），ADDR两列创建唯一非聚集索引Dindex。

3）在PROJ表的PNAME列上创建唯一非聚集索引Pindex，若该索引已存在则删除后重建。

2．查看索引

1）查看DEPA表的索引信息。

2）查看数据库COMPANY索引使用的空间信息

3.索引更名

1）将DEPA表的索引Dindex名更改为Dindex1。

4.删除索引

1）删除DEPA表的索引Dindex1。

㈡视图

1.创建视图

1）创建14号部门员工的视图EMPL_14（ENO，ENAME，SALARY）。

2）创建包含项目名与员工名及工作天数的视图Prjemp。

3）在视图EMPL_14上创建工资在3000以上的员工的视图EMPL_14H3。

4）创建每个部门的平均工资的视图E_AVG（DNO，SAVG）。

2.查询视图

1）根据视图Prjemp查询做'

设计系统'

项目的所有员工及工作天数。

2）根据视图E_AVG查询‘客户中心’的平均工资。

3．更新视图

1）在视图EMPL_14中将1042号员工的姓名改为‘李立春’。

2）向视图EMPL_14中插入一条新记录（1050，‘赵英’，3500）。

3）删除视图EMPL_14中1042号员工的记录。

4.删除视图

1）删除视图Prjemp。

实验5数据库的安全性控制

本实验的目的是通过实验使学生加深对数据安全性的理解，并掌握SQLServer中有关用户，角色及操作权限的管理方法。

1.建立数据库登录帐户S1和D1，密码均为1234，登录帐户S1默认数据库分别为COMPANY，其它保持默认值。

2.将S1映射为COMPANY数据库的同名数据库用户。

3.将D1映射为COMPANY数据库的数据库用户，数据库用户名为S2。

3．对数据库用户授予权限

1）授予S1对EMPL表插入数据的权限，并验证该权限（方法：

通过登录帐户S1登录SQLServer，然后插入一条数据来验证该权限）。

2）授予S2创建表的权限，并验证该权限（方法：

通过登录帐户D1登录SQLServer，然后创建-张表来验证该权限）。

3）收回S1对EMPL表插入数据的权限，并验证该权限（方法：

4.创建用户角色

1）在COMPANY数据库中创建角色MYROLE，授予该角色查询和修改EMPL表的权限。

2）将S1添加为角色MYROLE的成员，并验证s1是否能够查询EMPL表。

3）将S2添加为角色MYROLE的成员，并验证s2是否能够修改EMPL表。

5．收回数据库用户权限

1）采用用户角色方法收回S1和S2修改EMPL表的权限，并验证S1是否还能修改EMPL表。

2）拒绝S2查询EMPL表的权限，并验证S2是否还能查询EMPL表。

实验6数据完整性

一、实验目的

本实验的目的是通过实验使学生加深对数据完整性的理解，掌握数据完整性的约束条件，学会创建和使用触发器。

SQLServer实现数据完整性的具体方法有4种：

约束、缺省、规则和触发器。

触发器是实施复杂完整性的特殊类型的存储类型。

触发器不需要专门语句调用，当对它所保护数据进行修改时自动激活，以防止对数据进行不正确，未授权或不一致的修改。

1.创建包含非空约束字段的表

创建一个员工联系信息表Contact，一共有3个字段：

雇员号（Eno）数据类型为4位长定长字符串；

电话（Tel）数据类型为8位长定长字符串；

地址（Addr）数据类型为20位变长字符串；

要求雇员号和电话不能为空。

2.创建包含唯一约束字段的表

创建员工邮箱表Mail，一共有2个字段：

邮箱地址（Mail）数据类型为20位变长字符串；

要求雇员号非空，邮箱地址唯一。

3.创建包含主码约束字段的表

创建一个员工卡信息表Card，一共有3个字段：

卡号（Cno）数据类型为8位长定长字符串；

开卡日期（Startday）日期格式；

要求雇员号和卡号联合做主键。

4.创建包含检查约束字段的表

创建项目进度表Process，一共有3个字段：

项目号（Pno）数据类型为4位长定长字符串；

完成百分比（Percentage）数据类型为DECIMAL（3,2）；

已用费用（Pem）数据类型为INT；

要求：

⑴完成百分比用小数表示且数值小于1；

⑵已用费用的取值为0-100000（用规则实现）。

5.创建包含默认值约束字段的表

创建通勤表Commudity，一共有2个字段：

通勤标志（Flag）数据类型为1位定长字符串；

要求通勤标志默认为‘Y’。

6.创建包含外码约束字段的表

1）创建部门经理表Mgr，一共有2个字段：

部门号（Dno）数据类型为4位长定长字符串

经理员工号（Eno）数据类型为4位长定长字符串

要求部门号是主键（用CONSTRAINT约束实现）

2）创建部门收支表CHARGE，一共有2个字段：

收支（CHARGE）数据类型为DECIMAL（7，2）

要求部门号是外键

7．创建触发器

在表EMPL上创建一个触发器EMPL_date,当更改员工编号时同步更改Job表中对应的员工编号。

实验7数据库设计

使学生能够对给定的实际需求，选择一种编程语言或开发工具，完成数据库设计，并实现数据的查询等操作，从而掌握数据库应用系统的设计方法和步骤。

熟悉大型数据库管理系统的结构与组成；

熟悉数据库应用系统的设计方法和开发过程；

掌握一种大型数据库管理系统（SQLSERVER或ORACLE）的应用技术和开发工具的使用；

熟悉数据库设计工具的使用；

熟悉数据库安全的相关知识和技术；

熟悉数据库系统的管理和维护。

二、实验时数4学时

三、实验内容

完整实践数据库应用系统设计的工程过程:

需求分析\概念设计\逻辑设计\物理设计\实施\编写应用程序：

（1）需求分析:

选定一个应用，对所设计的数据库系统有一定的调研分析,其结果应该以简单的需求分析说明书\数据字典和数据流图（2级数据流即可）表示；

（2）概念分析:

使用E-R图作为描述工具.描述出局部和全局的E-R图；

（3）逻辑设计:

将E-R图转化为相应的关系模式,并根据范式理论进行优化分析，关系模式不少于3个；

（4）物理设计:

根据需要设计必要的索引等结构；

（5）实施:

以某种DBMS为工具创建出完整的数据库.在数据库中要表现出的知识点包括:

①设置数据完整性（参照完整性、实体完整性、自定义完整性（包括触发器）。

②设置用户,并相应设计用户的权限和角色（用户设计至少3个，有不同的权限）。

③建立存储过程完成相关操作至少2个,以文件方式保存。

④

（6）编写应用程序：

利用开发工具C++、JAVA、.NET平台或其它动态网页开发工具编写应用程序；

（7）以上内容,请详细描述,并有必要抓图；

（8）提交系统的源码程序、设计性实验报告。

实验8数据库的备份与恢复

本实验的目的是通过实验使学生了解SQLServer的数据备份和恢复机制，掌握SQLServer中数据库备份和恢复的方法。

1．创建备份设备

创建一个名为company_dev的磁盘备份设备，其对应的文件是D:

\company_dev.bak，该备份设备用于COMPANY数据库的备份。

写出创建该备份设备的SQL语句。

2．对数据库进行完全备份

对COMPANY数据库进行一次完全备份，备份到company_dev备份设备中。

并且写出完成完全备份的SQL语句。

3．对数据库进行差异备份

对COMPANY数据库完成完全备份后，往EMPL表中添加两条新记录。

然后对COMPANY数据库进行差异备份，并且写出完成差异备份的SQL语句。

4．对数据库进行事务日志备份

对COMPANY数据库完成差异备份后，执行SQL语句修改数据库的内容。

然后对COMPANY数据库进行事务日志备份，并且写出完成事务日志备份的SQL语句。

5．模拟数据库故障发生

模拟用户不小心删除了COMPANY数据库，从而需要使用备份恢复数据库。

6．恢复最近的完全数据库备份

作为恢复的第一步，使用完全备份恢复COMPANY数据库。

7．恢复完全备份之后的最近的差异数据库备份

利用差异备份恢复COMPANY数据库，并写出相应的SQL语句。

8．恢复完全或差异备份之后的数据库事务日志备份

利用事务日志备份恢复COMPANY数据库，并写出相应的SQL语句。