数据库例题1

数据库例题

1：试述关系数据库系统中视图的定义，引进视图的概念有什么好处？视图和基本表之间有什么区别和联系？

定义：

• 视图是从一个或者几个基本表（视图）中导出的表，它与基本表不同，是一个虚表
• 数据库中只存放视图的定义，不存放视图对应的数据

引进视图的好处：

• 视图能简化用户操作
• 视图能使用户多种角度看待数据
• 视图对重构数据库提供了一定程度的逻辑独立性
• 视图能够对机密数据提供安全保护

视图和基本表之间的联系和区别

联系：

• 视图是从一个或者多个基表（视图）中导出的；
• 一旦基本表中的数据发生变化，视图查询的数据也就随之改变了

区别：

• 视图是一个虚表，数据库只保存视图的定义，不存放视图对应的数据；数据库保存基表中的数据

• 视图更像是一个窗口，透过它可以看到数据库中特定的数据以及变化

2：什么是概念模型？试述概念模型的作用

概念模型概念：概念模型，也称为信息模型，是按用户观点来对数据和信息建模，主要用于数据库的设计

概念模型的作用：

• 概念模型实际上是从现实世界到机器世界的一个中间层次。

• 概念模型用于信息世界的建模，是现实世界到信息世界的第一层抽象，是数据库设计人员进行数据库设计的有力工具，也是数据库设计人员和用户之间交流的语言

3：外模式/模式/内模式

模式

描述的是数据的全局逻辑结构，所有用户的公共数据视图；

• 一个数据库只有一种模式

• 作为数据库的中间层，既不涉及数据的物理存储细节和硬件环境，

• 与具体应用程序、应用开发工具和高级程序设计语言无关

• 数据库管理系统提供模式数据定义语言（模式DDL）来严格定义模式

外模式

外模式也称为子模式或者用户模式，是数据库用户的数据视图，描述的是数据的局部逻辑结构

• 外模式通常是模式的子集

• 一个数据库可以有多个外模式

• 一个外模式可以被一个用户的多个应用系统所使用，但是一个应用程序智能使用一个外模式

• 数据库管理系统提供外模式数据定义语言（外模式DDL）来严格定义外模式

内模式

内模式也称为存储模式，是数据物理结构和存储方式的描述

• 一个数据库只有一个内模式
• 是对数据物理结构和存储方式的描述：比如索引方式是B+树索引还是hash索引、存储方式是升序或者降序、数据是否压缩

4：什么叫数据与程序的物理独立性？什么叫数据与程序的逻辑独立性？为什么数据库系统具有数据与程序的独立性？

• 数据与程序的逻辑独立性：用户的应用程序和数据库的逻辑结构是相互独立的

  • 当模式改变时，数据库管理员对外模式/模式映像做出改变，可以使外模式不变。应用程序是依据外模式编写的，从而应用程序不用修改，保证了数据与程序的逻辑独立性

• 数据与程序的物理独立性：用户的应用程序和数据库中数据的物理存储是相互独立的

  • 当数据库的存储方式发生改变，数据库管理员对模式/内模式映像做出改变，可以使模式保持不变，从而应用程序也不必改变，保证了数据与程序的物理独立性

5：自然连接和等值连接

连接运算符是“=”的连接运算称为等值连接。它是从关系 R 与 S 的广义笛卡尔积中选取 A，B 属性值相等的那些元组

自然连接是一种特殊的等值连接，它要求在两个关系中进行比较的分量必须是同名的属性组，在结果中把重复的属性列去掉

6：自主存取控制和强制存取控制

自主存取控制方法：定义各个用户对不同数据对象的存取权限。当用户对数据库访问时首先检查用户的存取权限，防止不合法用户对数据库的存取。

强制存取控制方法：每一个数据对象被（强制地）标以一定的密级，每一个用户也被（强制地）授予某一个级别的许可证。系统规定只有具有某一许可证级别的用户才能存取某一 个密级的数据对象。

7：什么是审计功能，为什么要提供审计功能

审计功能是指 DBMS 的审计模块在用户对数据库执行操作的同时把所有操作自动记录到系统的审计日志中。

利用数据库的审计功能：DBA 可以根据审计跟踪的信息，重现导致数据库现有状况的一系列事件，找出非法存取数据的人、时间和内容等。

8：如何判断一个关系是否属于第三范式

第一范式：属性不可分，并且表中不含表的，通常认为是第一范式，第一范式就是一张不可分的二维表

第二范式：在1NF的基础上，判断是否存在非主属性对码的部分依赖，如果存在，就不属于2NF

第三范式：在2NF的基础上，判断是否存在非主属性对于码的传递依赖，如果存在，就不属于3NF

BCNF：在3NF的基础上，判断决定因素中是否包含码，决定因素中不包含码就不属于BCNF

9：什么是数据字典，数据字典中通常有哪些内容

数据字典是关系数据库管理系统内部的一组系统表，记录了数据库中的所有定义信息。

包括关系模式定义、视图定义、索引定义、完整性约束定义、各类用户对数据库的操作权限、统计信息等

8：函数依赖例题，求候选码，求闭包的过程

求候选码的简单方法方法：
（1）如果有属性不在函数依赖集中出现，那么它必须包含在候选码中；
（2）如果有属性不在函数依赖集中任何函数依赖的右边出现，那么它必须包含在候选码中；
（3）如果有属性只在函数依赖集的左边出现，则该属性一定包含在候选码中。
（4）如果有属性或属性组能唯一标识元组，则它就是候选码；

按以下步骤求候选键：
1.只在依赖关系右部出现的属性，不属于候选码;
2.只在依赖关系左部出现的属性，一定存在于某候选码当中;
3.外部属性一定存在于任何候选码当中;
4.其他属性逐个与2,3的属性组合，求属性闭包，直至X的闭包等于U,若等于U,则X为候选码。

例题1：

R < U , F > , U = ( A , B , C , D , E , G ) , F = { A B → C , C D → E , E → A . A → G } R<U,F>,U=(A,B,C,D,E,G),F=\{AB\to C,CD\to E,E\to A.A\to G\} R<U,F>,U=(A,B,C,D,E,G),F={AB→C,CD→E,E→A.A→G}

求候选码

• G只在函数依赖右边出现，G不为候选码；
• B、D只在函数依赖左边出现，一定为候选码
• B和D单独求闭包，并不能求出U；且BD也不能闭包求出U
• BD可以和A、C、E组合
  • ABD：$AB\to C,CD\to E,A\to G$ABD的闭包为ABDCEG=U
  • BDC：$CD\to E,E\to A,A\to G$，BDC的闭包为BDCEAG=U
  • BDE：$E\to A,A\to G,AB\to C$，BDE的闭包为BDEAGC=U

因为(ABD)、(BCD)、(BDE)的闭包都是ABCDEG，候选码有3个分别是ABC、BCD和BDE

例题2：

关系模式 $R(A,B,C,D,E,F)$

其函数依赖集为： F = { E → D , C → B , C E → F , B → A } F=\{E→D,C→B,CE→F,B→A\} F={E→D,C→B,CE→F,B→A}

1：指出 R 的所有候选码并说明原因；

• 只在依赖关系右部出现的为D、F、A，这些属性必不包含在码中
• 只在依赖关系左部出现的为E、C，这些属性包含在码中
• 考虑单独的E，求E的闭包：{E,D}
• 考虑单独的C，求C的闭包：{C,B,A}
• 考虑CE，求CE的闭包：{A,B,C,D,E,F}

R的候选码为CE

2：R 最高属于第几范式，为什么?

1NF：关系模式R中的所有属性都是不可分的基本数据项，就属于第一范式，一般的一张二维表就属于第一范式

2NF：消除了非主属性对主属性的部分依赖，如果存在非主属性都主属性的部分依赖就只属于第一范式而不是第二范式

3NF：消除了非主属性的主属性的传递依赖

BCNF：在3NF的基础上，每个决定因素中都包含码

R最高属于第一范式，因为存在部分依赖， { E − > D , C − > B } \{E->D,C->B\} {E−>D,C−>B}

3：分解 R 为 3NF

首先分解为2NF，消除非主属性的部分依赖

​ R1(E,D)、R2（C,B,A）、R3(C,E,F)

分解为3NF，消除非主属性对主属性的传递依赖

​ R1(E,D)、R2（C,B）、R3(C,E,F)、R4(B,A)

例题3：

已知关系 R(A, B, C, D), 有函数依赖集 F = { A→C; B→D; BD→A }

1）求出R的所有候选码

• 只在依赖集右边出现的为C，C必不在码中

• 只在依赖集左边出现的为B，B在码中

• 求B的闭包：{B,D,A,C}=U，B为候选码

• 求A的闭包：{A,C}

• 求D的闭包：{D}

R的候选码为B，非主属性A,C,D

2）判断R是否满足第三范式

满足2NF，因为非主属性都完全依赖于码

不满足3NF，因为存在传递依赖

3）如果不满足，对其进行分解， 直至满足 3NF

R2(B,D,A)、R3(A,C)

例题4

设关系模式 R（A，B，C，D，E）F是依赖集，F={ AB→C，BC→A，AC →B，D→E }

试写出 R 的所有候选码，并说明理由

• 只出现在依赖关系右边，E不包含在候选码中
• 只出现在依赖关系左边，D包含在候选码中
• D闭包{D,E}
• 考虑DA，DA闭包{D,E,A}
• 考虑DAB，DAB闭包{D,E,A,B,C}
• DAC,DBC的闭包都为{D,E,A,B,C}
• R的候选码为ABD、ACD、BCD

例题6：

设有关系 R(W, X, Y, Z)，依赖关系 F={X->Z，WX->Y}。判断该关系模式符合第几范式，并说明理由。

• WX主属性，ZY非主属性
• 候选码为WX
• 存在X->Z，存在非主属性对于码的部分依赖关系，符合第一范式

例题7

设有关系 R(X,Y,Z)满足依赖关系 F={Y→Z，XZ→Y}。该关系最高 达到第几范式（最高考虑到 BC 范式）？并说明理由。（10 分）

• X只在依赖关系左边出现，包含在主码中
• X闭包：{X}
• 考虑XY，XY闭包：{XYZ}
• 考虑XZ，XZ闭包：{XYZ}

候选码为XY,XZ，主属性为X,Y,Z

Y->Z中，决定因素中不包含码，最高3NF

例题8

设有函数依赖集 F={AB→CE，A→C，GP→B，EP→A，CDE→P， HB→P，D→HG，ABC→PG}，求属性集 D 关于 F 的闭包 $(D{)}_F^{+}$。

X0=D X1=DHG

$(D{)}_F^{+}$=DHG

例题9

设有关系模式 R（X, Y, Z），依赖集 F={XY→Z, XZ→Y, Y→Z}，判断 R 最高满足第几范式并说明理由。（8 分）

X只在依赖关系左边出现，X为主属性，包含在候选码中，候选码为XY 、XZ

X，Y，Z都为主属性

2NF：每一个非主属性完全依赖于任何一个候选码

3NF：在2NF的基础上，非主属性不传递依赖于码

BCNF：每一个决定因素都包含码

最高属于3NF

9：SQL语句

例题一：

1：JNO=’J2‘ 使用的 PNAME 和 QTY

SELECT P.PNAME, SPJ.QTY FROM P,SPJ
WHERE SPJ.JNO='J2';

2：S.CITY=’上海‘ 供应的PNO

SELECT DISTINCT SPJ.PNO FROM SPJ,S
WHERE S.SNO=SPJ.SNO AND S.CITY='上海'
或者
SELECT DISTINCT PNO FROM SPJ 
WHERE SNO IN(SELECT SNO FROM S WHERE CITY='上海');

3：S.CITY=‘上海’ ，找出使用上海产的零件的工程名称

SELECT JNAME FROM J WHERE JNO IN
(SELECT JNO FROM SPJ,S WHERE S.CITY='上海' AND S.SNO=SPJ.SNO);

4：没有使用天津产的零件（S.city=‘天津’）的工程号码

SELECT JNO FROM J WHERE NOT EXISTS(SELECT JNO FROM SPJ,S WHERE S.SNO=SPJ.SNO AND S.CITY='天津');

5：全部红色零件改为蓝色

UPDATE P SET COLOR='蓝' WHERE COLOR='红';

6：由S5供给J4的零件P6改为由S3供应

UPDATE SPJ SET SNO='S3' WHERE SNO='S5' AND JNO='J4' AND PNO='P6';

7：从供应商关系中删除S2记录，并从供应情况关系中删除相应记录

DELETE FROM SPJ WHERE SNO='S2';
DELETE FROM S WHERE SNO='S2';

8：将（S2,J6,P4,200）插入供应情况关系中

INSERT INTO SPJ(SNO,PNO,JNO,QTY)VALUES(S2,J6,P4,200);

9：创建视图

CREATE VIEW XXX AS SELECT xxx FROM XXX WITH CHECK OPTION;

10：赋予权限

GRANT SELECT,UPDATE(X) ON XXX TO XXX WITH GRANT OPTION; 

例题二

设属性集 X={B,G}，函数依赖集 F 函数依赖组成： {AC→PE, PG→A, B→CE, A→P, A→B, GC→A, PAB→G, AG→BG, ABCP →H}

求 X 关于 F 的闭包。

{B,G}、{B,C,E,G}、{B,C,E,G,A}、{B,C,E,G,A,P}、{B,C,E,G,A,P,H}

例题三

设有

• 学生表 Student(Sno, Sname, Ssex, Sage, Sdept)
• 课程表 Course(Cno, Cname, Cpno, Ccredit)
• 学生选课表 SC(Sno,Cno,Grade)。

用 SQL 语言：

（1）建立一个视图 V-SC（Sno, Sname, Cno, Cname，Grade）

（2）从视图 V-SC 上查询平均成绩在 90 分以上的学生姓名（Sname）、课程名（Cname） 及成绩（Grade）

1）

CREATE VIEW V-SC AS SELECT Sno,Sname,Cno,Cname,Grade FROM Student,Course,SC;

2）

SELECT Sname,Cname,Grade FROM V-SC 
GROUP BY Sno HAVING AVG(Grade)>=90;

例题四：

设有关系模式如下：

S(Sno, Sname, Age, Sex)，学生表：Sno 代表学号，Sname 代表学生姓名，Age 代表学生年龄， Sex 代表学生性别

SC(Sno, Cno, Grade)，选课表：Sno 代表学号，Cno 代表课程号，Grade 代表成绩

C(Cno, Cname, Teacher)，课程表：Cno 代表课程号，Cname 代 表课程名，Teacher 代表任课教师姓名

1、查询平均成绩大于 85 分的学生的姓名及其平均成绩（5 分）。

SELECT Sname,AVG(Grade) FROM S,SC
WHERE S.Sno=SC.Sno
GROUP BY Sno HAVING AVG(Grade)>85;

2、把三个表的查询和插入权限授予用户张明，并允许张明进一步 把这些权限授予其他用户（5 分）

GRANT INSERT,SELECT ON TABLE S,SC,C TO 张明 WITH GRANT OPTION；

例5

书店（书店编号，书店名，地址）

图书（书号，书名，定价）

图书馆（馆号，馆名，城市，电话）

图书发行（馆号，书号，书店号，数量）

（1）用 SQL 语句查询已发行图书中最贵的图书的书名和定价。（5 分）

SELECT 书名,定价 FROM 图书,图书发行 WHERE 定价=
(SELECT MAX(定价) FROM 图书,图书发行 WHERE 图书.书号=图书发行.书号)

（2）写出以下 SQL 语句的含义（查的是什么？）（5 分）

SELECT 馆名 FROM 图书馆 
WHERE 馆号 IN  (SELECT 馆号 FROM 图书发行 WHERE 书号 IN  （SELECT 书号 FROM 图书 WHERE 书名=’数据库系统概念’））；

查询：发行数据库系统概念图书的图书馆管名

例6

学生表：Student （Sno，Sname，Sex，Sage，Sdept）

课程表：Course（Cno，Cname，Ccredit）

学生选课表：SC（Sno，Cno，Grade）

找出每个学生超过他自己选修课程平均成绩的课程号

SELECT Cno FROM SC X 
WHERE Grade> (
    SELECT  AVG(Grade) FROM SC Y 
    WHERE Y.Sno=X.Sno
);

建立计科系学生的视图 CS_Student，包括学号、姓名、性别、年龄，并要求进行 插入和修改操作时仍需保证该视图只有计科系的学生；

CREATE VIEW CS_Student AS SELECT Sno,Sname,Sex,Sage FROM Student WHERE Sdept='计科系' WITH CHECK OPTION;

通过计科系学生视图 CS_Student 修改数据，把学号为“S201608101”的学生姓名改为王 小平；

UPDATE CS_Student SET Sname='王小平' WHERE Sno='S201608101';

DDL：数据库模式定义语言，关键字：create

DML：数据操纵语言，关键字：Insert、delete、update

DCL：数据库控制语言 ，关键字：grant、remove

DQL：数据库查询语言，关键字：select