数据库原理与应用习题集(11套).docx

=1第1章习题一、选择题1下列四项中，不属于数据库系统优点的是（）。A实现数据共享 B确保数据的安全性与保密性C控制数据冗余 D数据依赖程序2数据库系统中，对用户使用的数据视图的描述称为（ ）。A概念模式 B内模式C存储模式 D外模式3下列四项中，不属于数据库特点的是( )。A. 数据共享 B. 数据完整性C. 数据冗余很高 D. 数据独立性高4数据库的三级模式之间存在着两级映像，使数据库系统具有较高的数据（ ）。A相容性 B独立性C共享性 D一致性5数据库系统的三级模式结构中，定义索引的组织方式属于（）。A概念模式B外模式C逻辑模式D内模式6. E-R模型属于（）。A. 概念模型B. 层次模型C. 网状模型D. 关系模型7学生社团可以接纳多名学生参加，但每个学生只能参加一个社团，从社团到学生之间的联系类型是( )。A. 多对多 B. 一对一 C. 多对一 D. 一对多8反映现实世界中实体及实体间联系的信息模型是( )。A. 关系模型 B. 层次模型 C. 网状模型 D. E-R模型二、设计题1某医院病房计算机管理中需要如下信息：科室：科名，科地址，科电话，医生姓名病房：病房号，床位号，所属科室名医生：姓名，职称，所属科室名，年龄，工作证号病人：病历号，姓名，性别，诊断，主管医生，病房号其中，一个科室有多个病房、多个医生，一个病房只能属于一个科室，一个医生只属于一个科室，但可负责多个病人的诊治，一个病人的主管医生只有一个。设计该计算机管理系统的ER图，并注明属性和联系类型。2假设某公司的业务规则如下：（1）公司下设几个部门，如技术部、财务部、市场部等。（2）每个部门承担多个工程项目，每个工程项目属于一个部门。（3）每个部门有多名职工，每一名职工只能属于一个部门。（4）一个部门可能参与多个工程项目，且每个工程项目有多名职工参与施工。根据职工在工程项目中完成的情况发放酬金。（5）工程项目有工程号、工程名两个属性；部门有部门号、部门名称两个属性；职工有职工号、姓名、性别属性。请根据上述规则设计E-R模型。3. 某公司的业务规则如下：（1）每位职工可以参加几个不同的工程，且每个工程有多名职工参与；（2）每位职工有一个职位，且多名职工可能有相同的职位；（3）职位决定小时工资率，公司按职工在每一个工程中完成的工时，计算酬金；（4）职工的属性有职工号、姓名、职位和小时工资率；（5）工程的属性有工程号和工程名称。试根据上述业务规则设计E-R模型。答案：一、选择题 DDCB DADD二、设计题1. 2.3. =2第3章习题 1学校有若干个系，每个系有若干班级和教研室，每个教研室有若干教师，每名教师只教一门课，每门课可由多个教师教；每个班有若干学生，每个学生选修若干课程，每门课程可由若干学生选修。请用E-R图画出该学校的概念模型，注明联系类型，再将E-R模型转换为关系模型。2工厂生产的每种产品由不同的零件组成，有的零件可用于不同的产品。这些零件由不同的原材料制成，不同的零件所用的材料可以相同。一个仓库存放多种产品，一种产品存放在一个仓库中。零件按所属的不同产品分别放在仓库中，原材料按照类别放在若干仓库中（不跨仓库存放）。请用E-R图画出此关于产品，零件，材料，仓库的概念模型，注明联系类型，再将E-R模型转换为关系模型。3一个图书馆管理系统中有如下信息：图书：书号、书名、数量、位置借书人：借书证号、姓名、单位出版社：出版社名、邮编、地址、电话、E-mail其中约定：任何人可以借多种书，任何一种书可以被多个人借，借书和还书时，要登记相应的借书日期和还书日期；一个出版社可以出版多种书籍，同一本书仅为一个出版社所出版，出版社名具有唯一性。根据以上情况，完成如下设计：（1）设计系统的E-R图；（2）将E-R图转换为关系模式；（3）指出转换后的每个关系模式的关系键。答案1.1n假定：主码用_ 表示外码用 表示 7个实体各自为一个关系模式，6个1:N的联系没有成为一个独立的关系模式，全部合并到多方实体该E-R图可以转换为以下关系模式：学院（院名，地址，成立日期，教师人数，学生人数）系（系名，系址，系主任，院名）班级（班号，专业名，入学年份，学生数，班级导师，系名）学生（学号，姓名，性别，出生日期，联系电话，籍贯，班号）教研室（教研室名称，教师人数，电话，办公室编号，系名）教师（教工号，姓名，性别，出生日期，联系电话，教研室名称,课号）课程（课号，课名，学时，学分，开课学期，必修课否）1个M:N的联系必须成为一个独立的关系模式选修（学号，课号，成绩）2.该E-R图可以转换为以下关系模式：4个实体各自为一个关系模式，2个1:N的联系没有成为一个独立的关系模式，而是合并到多方实体，合并时包括联系得属性产品（产品名，型号，重量，仓库号，数量c）零件（零件名，规格）材料（材料编号，名称，仓库号，数量a）仓库（仓库号，地点，面积，保管员）产品-零件（产品名，零件名，组装日期b）零件-材料（材料编号，零件名，生产日期a） 3个M:N的联系必须成为一个独立的关系模式零件-仓库（零件名，仓库号，数量b）3.该E-R图可以转换为以下关系模式：图书（书号，书名，数量，位置，出版社名） 借书人（借书证号，姓名，单位） 出版社（出版社名，邮编，地址，电话，E-mail）借阅（借书证号，书号，借书日期，还书日期）=3第5章习题1试用T-SQL语言创建ZYGL(职员管理)数据库，要求数据文件的初始大小为1MB，最大大小为50MB，增长方式按10%增长；日志文件的初始大小为3MB，按1MB增长。2试用T-SQL语言修改ZYGL数据库的主数据文件，将增长方式修改为按5MB增长。3试用T-SQL语言修改ZYGL数据库，为ZYGL增加一个数据文件ZYGLBAK。4试用T-SQL语言删除ZYGL数据库。-1.CREATE DATABASE zygl ON (NAME = zygl_Data, FILENAME =E:sqlzygl_Data.MDF , SIZE = 1, MAXSIZE = 30, FILEGROWTH = 10%) LOG ON (NAME = zygl_Log, FILENAME =E:sqlzygl_Log.LDF ,SIZE = 3, FILEGROWTH = 1)GO-2ATLER DATABASE ZYGLMODIFY FILE ( NAME =ZYGL_DATA, FILEGROWTH =5 )-3ATLER DATABASE ZYGLADD FILE (NAME =ZYGLBAK , FILENAME =E:sqlzygl_Data1 .NDF , SIZE = 1, MAXSIZE = 50, FILEGROWTH = 10% )-4 DROP DATABASE ZYGL=4第6章习题设计题现有一个数据库scd，库中包含以下系、学生、班级各表： student（学号，姓名，年龄，班号） class（班号，专业名，系名，入学年份） department（系号，系名） 假设表中将有如下数据：student （ 2008101，张山，18，101；2008102，李斯，16，102； 2008103，王玲，17，111；2008105，李飞，19，112 ） class（ 101，软件，计算机，2005； 102，微电子，计算机，2006； 111，无机化学，化学，2004；112，高分子化学，化学，2006 ） department （001， 数学；002， 计算机；003， 化学 ） 1. 用SQL语言创建以上各数据表，在定义中要求为每一字段（列）选择合适的数据类型和长度，并设置适当的约束，即声明： （1）每个表的主键和所有可能的外键。 （2）学生姓名不能为空。（3）系部系名不能为空，且唯一。（4）学生的年龄介于15到40岁之间，默认为18。 2试用SQL语言完成下列功能：（1）学校又新增加了两个系部：一个物理系，编号为006，一个经济系，编号为008。（2）将入学年份在2004年以前的班级删除。（3）学生张山转到111班，请更新相关的表。（4）为每个学生的年龄增加1岁，请更新相关的表。 3试用SQL语言修改表结构：由于年龄可以通过出生日期计算而来，对student表删除“年龄”字段，增加一个“出生日期”字段。（提示：必须先删除对年龄的检查约束，才能删除年龄列）答案-1.create database scdgoUse scdGoCREATE TABLE department(系号 char(3) PRIMARY KEY, 系名 char(20) not null unique)GoCREATE TABLE class(班号 char(3) PRIMARY KEY, 专业名 char(20), 系名 char(20) references department(系名) ,入学年份 int)GoCREATE TABLE student(学号 char(7) PRIMARY KEY,姓名 char(8) NOT NULL,年龄 int constraint ck_nl CHECK(年龄 =15 and 年龄=40) constraint df_nl DEFAULT 18, -指定约束名ck_nl 和df_nl,以便以后好删除班号 char(3) references class(班号)Insert into department values (001, 数学);Insert into department values (002, 计算机);Insert into department values (003, 化学) Insert into class values ( 101,软件,计算机,2005); Insert into class values ( 102,微电子,计算机,2006);Insert into class values (111,无机化学,化学,2004);Insert into class values (112,高分子化学,化学,2006 )Insert into student values(2008101,张山,18,101);Insert into student values(2008102,李斯,16,102); Insert into student values( 2008103,王玲,17,111)Insert into student values( 2008104,李飞,19,112)-2.-1)Insert into department values(006,物理系)Insert into department values(008,经济系) -2)Delete class where 入学年份1753. 查询所有姓“李”同学的基本情况。Select *From xsWhere 姓名 like 李%4.查询至少选修了两门课程的学生学号。Select 学号 From xs_kcGroup by 学号Having count(课程号)=25. 将所有学生信息按身高逆序输出。Select * From xsOrder by 身高 desc6 检索平均成绩在75分以上的学生姓名、性别和专业。方法一：Select 姓名，性别，专业名From xsWhere 学号 in ( Select 学号 From xs_kc Group by 学号Having avg(成绩)75)方法二：Select 姓名，性别，专业名From xs_kc JOIN xs ON xs.学号=xs_kc.学号Group by xs.学号, 姓名，性别，专业名Having avg(成绩)757. 检索“李林”同学不学的课程号。Select 课程号From kcWhere 课程号 not in ( Select 课程号 from xs, xs_kc Where xs.学号=xs_kc.学号 and xs.姓名=李林)8. 查询只有两门课不及格的学生姓名。Select 姓名From xs Where 学号 in (Select 学号 From xs_kcWhere 成绩75)Create view avg75 As Select 姓名，性别，专业名，avg(成绩)From xs_kc JOIN xs ON xs.学号=xs_kc.学号Group by 姓名，性别，专业名Having avg(成绩)75=6第8章习题对于数据库scd，库中包含以下系、学生、班级各表： student （学号，姓名，年龄，班号） class（班号，专业名，系名，入学年份） department （系号，系名） 请使用T-SQL语言完成以下各题。1 将student表的班号与姓名这两列组合创建一个升序的非聚集索引。2 为department表的系名建立一个唯一索引，如果输入了重复的键，将忽略该INSERT或UPDATE语句，并使用填充因子FILLFACTOR 为50%。3 为数据库scd建立一个默认对象，使其对应于年龄为18，将其绑定到student表的年龄列上。4 为数据库scd建立一个规则对象，并将其绑定到学生表的专业名列上，规定专业名的取值只能为护理学、 地质勘探和考古学之一。5 修改class表为其建立一个CHECK约束，检查入学年份是否小于2009。答案1 将student表的班号与姓名这两列组合创建一个升序的非聚集索引。Create nonclustered index ix_bj_xm on student (班号 asc , 姓名 asc)2 为department表的系名建立一个唯一索引，如果输入了重复的键，将忽略该INSERT或UPDATE语句，并使用填充因子FILLFACTOR 为50%。Create unique index uqix_xm on department (系名 asc) WITH IGNORE_DUP_KEY， FILLFACTOR = 503 为数据库scd建立一个默认对象，使其对应于年龄为18，将其绑定到student表的年龄列上。Create default d_age as 18Go Exec sp_bindefault d_age , student.年龄4 为数据库scd建立一个规则对象，并将其绑定到学生表的专业名列上，规定专业名的取值只能为护理学、 地质勘探和考古学之一。 Create rule r_zym as zym in (护理学, 地质勘探,考古学) Go Exec sp_bindrule r_zym, student.专业名5 修改class表为其建立一个CHECK约束，检查入学年份是否小于2008。Alter table class Add constraint ck_rxnf check (入学年份=80 THEN 学习时间长 WHEN 学时=54 THEN 学习时间一般 ELSE 学习时间短 ENDFROM KC（2）在CJGL数据库中，使用case函数处理：如果学生的专业是“计算机”，显示热门，是“通信工程”显示一般，否则显示冷门。SELECT 学号, 姓名, 专业热度= CASE 专业名 WHEN 计算机 THEN 热门 WHEN 通信工程 THEN 一般 ELSE 冷门 ENDFROM xs（3）计算18！=18*17*16*2*1。DECLARE i int , p bigintSELECT i=1, p=1WHILE i=18 BEGIN SET p=p*i SET i=i+1 ENDPRINT 18*17*16*2*1=+CAST(p AS char(20)（4）计算S = 1！+2！+3！+10！DECLARE i int , p bigint, s bigintSELECT i=1, p=1, s=0WHILE i=10 BEGIN SET p=p*i SET s=s+p SET i=i+1 ENDPRINT 1！+2！+3！+10！=+CAST(s AS char(20)2以下各题在CJGL数据库中实现：（1）创建一个自定义函数，可以求解任意数n的阶乘，即n!。并用n=10调用该函数。CREATE FUNCTION F_C(n INT)RETURNS INT ASBEGINDECLARE i int , p bigintSELECT i=1, p=1WHILE i50beginRaiserror (该班人数超过人，插入或修改已撤销., 16, 1)Rollback transactionendGO=8第9章习题2在职员管理数据库ZYGL中完成以下操作：1 创建一个自定义数据类型ID_type，用于描述员工号，执行如下程序：USE ZYGLEXEC sp_addtype ID_type, char(3) , not null GO 2 在SQL Server管理平台中通过表设计器修改职员表和工资表，对它们的“员工号”的数据类型使用自定义数据类型ID_type。3 创建一个自定义函数用于统计某部门的人数，部门名为函数的输入参数，如果没有该部门，则返回-1值。USE ZYGLCREATE FUNCTION total_rs(name_bm char(10)RETURNS int ASBEGINDECLARE num intIF not EXISTS( SELECT * FROM 部门表 WHERE 部门名=name_bm) SELECT num= -1ELSE SELECT num=count(*) FROM 职员表,部门表 WHERE 职员表.部门号=部门表.部门号 and 部门名=name_bmRETURN numEND4 分别以部门名“办公室”和“销售科”调用函数total_rs：USE ZYGLSELECT dbo.total_rs(销售科) AS 部门人数记录结果。SELECT dbo.total_rs(办公室) AS 部门人数记录结果。5 创建一个可以按给定员工姓名，输出其姓名、部门名及其工资信息的存储过程。USE ZYGLIF EXISTS (SELECT name FROM sysobjects WHERE name = yg_info AND type = P) DROP PROCEDURE yg_infoGO/*创建存储过程*/CREATE PROCEDURE yg_info name char(8)ASSELECT a.员工号, 姓名, 部门名, 应发工资, 实发工资FROM 职员表 a INNER JOIN 部门表 b ON a.部门号 = b.部门号 INNER JOIN 工资表 t ON a.员工号= t.员工号 WHERE 姓名=name GO6 调用yg_info存储过程查询员工 岳亮的信息。USE ZYGLEXEC yg_info 岳亮7 创建一个触发器，当插入或更新某个员工的工资记录时，检查该员工的基本工资是否超过平均基本工资的三倍，如果超过，就发出警告信息同时撤消插入或更新。执行如下程序：USE ZYGLIF EXISTS (SELECT name FROM sysobjects WHERE name = reminder_gz AND type = TR) DROP TRIGGER reminder_gzGOCREATE TRIGGER reminder_gz ON 工资表FOR INSERT, UPDATEASDECLARE jbgz decimal(7,2),pjgz decimal(7,2)SELECT jbgz =基本工资 FROM insertedSELECT pjgz=avg(基本工资) FROM 工资表IF jbgzpjgz*3BEGINROLLBACK TRANSACTION -撤消插入操作RAISERROR (该工资数据有误，超过平均值三倍，操作已经撤消！, 16, 10) /*返回一个错误信息*/ENDGO8 在SQL Server管理平台中打开 ZYGL库的工资表，将001号员工的基本工资分别修改为2600与8600，看触发器的触发反应。=9第10章习题1在职员管理数据库ZYGL中完成以下操作：1采用游标的方式将每位员工的津贴提高20。注意津贴的变化对实发工资和应发工资的影响。方法一：游标的非CURRENT形式的UPDATE语句USE ZYGLGODECLARE id char(3)DECLARE salary_cursor CURSOR FOR SELECT 员工号 FROM 工资表OPEN salary_cursorFETCH NEXT FROM salary_cursorINTO idWHILE FETCH_STATUS=0BEGIN UPDATE 工资表 SET 津贴=津贴*1.2 WHERE 员工号=id FETCH NEXT FROM salary_cursor INTO idENDCLOSE salary_cursorDEALLOCATE salary_cursorSELECT * FROM 工资表GO方法二：游标的CURRENT形式的UPDATE语句USE ZYGLGODECLARE salary_cursor CURSOR FOR SELECT 津贴 FROM 工资表OPEN salary_cursorFETCH NEXT FROM salary_cursorWHILE FETCH_STATUS=0BEGIN UPDATE 工资表 SET 津贴=津贴*1.2 WHERE CURRENT OF salary_cursor FETCH NEXT FROM salary_cursorENDCLOSE salary_cursorDEALLOCATE salary_cursorSELECT * FROM 工资表GO2采用游标的方式查找每月实发工资最多的员工的姓名。USE ZYGLGODECLARE id char(3)DECLARE temp_id char(3)DECLARE salary decimal(9,2)DECLARE temp_salary decimal(9,2)DECLARE name char(8)DECLARE salary_cursor CURSOR FOR SELECT 员工号, 实发工资 FROM 工资表OPEN salary_cursorFETCH NEXT FROM salary_cursorINTO id , salarySET temp_id=idSET temp_salary=salaryWHILE FETCH_STATUS=0BEGIN if salarytemp_salary BEGIN