第1章 绪论   

1 .数据库管理系统是数据库系统的一个重要组成部分，它的功能包括数据定义功能、数据操纵功能、数据库的运行管理、数据库的建立和维护功能。

2 .数据库系统是指在计算机系统中引入数据库后的系统，一般由数据库、数据库管理系统（及其开发工具）、应用系统、数据库管理员构成。

3 .数据库管理技术的发展是与计算机技术及其应用的发展联系在一起的，它经历了三个阶段：人工管理阶段，文件系统阶段和数据库系统阶段。

4 .数据库具有数据结构化、最小的冗余度、较高的数据独立性等特点。 5 .DBMS还必须提供数据的安全性保护、数据的完整性检查、并发控制、数据库恢复等数据控制功能。

6 .数据库管理系统的主要功能有哪些？ (填空题)

① 数据库定义功能； ② 数据存取功能； ③ 数据库运行管理； ④ 数据库的建立和维护功能。

7.三级模式之间的两层映象保证了数据库系统中的数据能够具有较高的逻辑独立性和物理独立性。 (问答题)

8 .试述概念模型的作用。 (填空题)

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

 9 .根据模型应用的不同目的，可以将这些模型划分为两类，它们分别属于两个不同的层次。第一类是概念模型，第二类是 数据模型。 (问答题)

10 .定义并解释概念模型中以下术语：实体，实体型，实体集，属性，码，实体联系图（E-R图） (填空题)

实体：客观存在并可以相互区分的事物叫实体。

实体型：具有相同属性的实体具有相同的特征和性质，用实体名及其属性名集合来抽象和刻画同类实体称为实体型。

实体集：同型实体的集合称为实体集。

属性：实体所具有的某一特性，一个实体可由若干个属性来刻画。

码：唯一标识实体的属性集称为码。

实体联系图：E-R图提供了表示实体型、属性和联系的方法：

实体型：用矩形表示，矩形框内写明实体名。

属性：用椭圆形表示，并用无向边将其与相应的实体连接起来。

联系：用菱形表示，菱形框内写明联系名，并用无向边分别与有关实体连接起来，同时在无向边旁标上联系的类型（1 : 1，1 : n或m : n）。

 11 .数据模型的三要素是指数据结构，数据操作，完整性约束。实际数据库系统中所支持的主要数据模型是关系模型，层次模型，网状模型。 

13 .数据模型中的数据结构是对数据系统的静态特征描述，包括数据结构和数据间联系的描述， 数据操作是对数据库系统的动态特征描述，是一组定义在数据上的操作，包括操作的涵义、操作符、运算规则及其语言等。 (问答题)

14 .定义并解释以下术语：DDL、DML (填空题)

DDL：数据定义语言。用来定义数据库模式、外模式、内模式的语言。 DML：数据操纵语言。用来对数据库中的数据进行查询、插入、删除和修改的语句。 

15 .关系模型是目前最常用也是最重要的一种数据模型。采用该模型作为数据的组织方式的数据库系统称为关系数据库系统。 (问答题)

16 .关系的完整性约束条件包括三大类：实体完整性、 参照完整性和用户定义的完整性。 (问答题)

17 .什么叫数据与程序的物理独立性？什么叫数据与程序的逻辑独立性？ 为什么数据库系统具有数据与程序的独立性？ (填空题)

数据与程序的逻辑独立性：当模式改变时（例如增加新的关系、新的属性、改变属性的数据类型等），由数据库管理员对各个外模式／模式的映象作相应改变，可以使外模式保持不变。应用程序是依据数据的外模式编写的，从而应用程序不必修改，保证了数据与程序的逻辑独立性，简称数据的逻辑独立性。

数据与程序的物理独立性：当数据库的存储结构改变了，由数据库管理员对模式／内模式映象作相应改变，可以使模式保持不变，从而应用程序也不必改变。保证了数据与程序的物理独立性，简称数据的物理独立性。数据库管理系统在三级模式之间提供的两层映象保证了数据库系统中的数据能够具有较高的逻辑独立性和物理独立性。

关系操作的特点：集合方式，操作对象和结果都是关系——元组的集合

非关系操作的特点：层次/网状，以记录为操作单位

 2 .关系数据模型中，二维表的列称为属性，二维表的行称为记录或元组。

3 .用户选作元组标识的一个候选码为主码，其属性不能取空值。 (问答题)

Chapter 1

数据—Data：描述事物的符号记录称为数据。

信息(information)：数据所表示的含义称为信息。

数据库:所谓数据库是长期存储在计算机内的、有组织的、可共享的数据集合。

数据库(DB)是各种信息系统（Information System) 的核心。

数据库特点：数据库中的数据具有较小的冗余度、较高的数据独立性和易扩展性，并可为各种用户共享。

即数据及结构相对于应用程序具有独立性

数据库管理系统Database Management System(DBMS)：DBMS是对数据库建立、操纵、维护的系统软件。

主要功能：1、数据定义功能；2、数据操纵功能；3、数据库的运行管理；4、数据库的建立维护。

常见的数据库管理系统 (DBMS)

大型DBMS:ORACLE    SQL-SERVER    DB2    SYBASE

中型DBMS:    MYSQL,      INFORMIX,  INTERBASE

小型（桌面）DBMS:    ACCESS,  FOXPRO,  SQL-ANYWHERE

数据库系统—Database System(DBS)：在计算机系统中引入数据库后的系统，

通常由： 数据库 DB, 操作系统OS，数据库管理系统 DBMS，开发工具SDK，应用系统APP ，管理员DBA ，用户USER

计算机数据管理经历阶段：1、人工管理阶段 ；2、文件系统阶段 ；3、数据库系统阶段

数据库系统管理的特点：

1.数据结构化(数据库的文件系统的根本区别）

2.数据独立性高（数据结构变，程序不须变）

3.数据共享性高，冗余度低，易扩充

4.数据由DBMS统一管理和控制 ，并使数据管理具有：

数据的安全性(security)保护（数据不被非法访问）

数据完整性（Integrity)检查(数据的正确性有效性相容性)

并发控制（Concurrency)控制

数据恢复（Recovery)

数据模型是对现实世界数据特征的抽象。

数据模型的三要素 ：

1数据结构：对实体类型和实体间联系的表达和实现。

2数据操作：允许执行的操作。主要：检索和更新

3数据约束条件：完整性规则的集合。

完整性规则:数据及其联系所具有的制约和依存规则，以保证数据的正确、有效和相容。

数据的完整性：指数据的正确性、有效性和相容性。

概念模型：计算机数据处理的第一层抽象，反映人对客观事物的认识。

概念模型的表示方法：实体-联系图（E-R图）

用矩形表示实体、用椭圆表示属性、用菱形表示联系。

属性和实体间、实体和联系间用线段连接

联系线标注联系类型（1，n), 如果联系有属性,也要标注

常见的数据模型有四种（按发展时间为序）：

层次模型、网状模型、关系模型、对象模型 

用表格(称为关系) 表示实体以及实体间联系的数据模型叫关系模型。

关系（Relation)：一个二维表格；

元组（Tuple) ：表中的一行即为一个元组；

属性（Attribute)：表中的一列即为一个属性；

码（key)：表中某属性组，可唯一确定任一元组；

域（Domain)：属性的取值范围；

分量：元组中的一个属性值；

关系模式：对关系的描述，表述为：关系名（属性1，属性2，….，属性n）

例：学生数据库的关系模式：学生（学号，姓名，性别，专业，入学时间）

数据库模式（Schema)： 数据库中全体数据的结构和特征的描述称为数据库模式。

数据库系统三级模式结构：模式、外模式、存储模式三级模式的概念。

三级模式之间有两级映象：外模式/模式映象、模式/内模式映象

外模式(Sub-Schema)(用户模式）：用户的数据视图、是数据的局部逻辑结构，模式的子集

模式(Schema)（概念模式）：所有用户的公共数据视图；是数据库中全体数据的全局逻辑结构和特性的描述。

内模式(Storage Schema)（存贮模式）：又称存储模式，是数据的物理结构及存储方式。

数据的逻辑独立性：当模式改变时，修改外模式/模式映象，使外模式保持不变，从而应用程序可以保持不变。

物理独立性：存储结构改变时，修改模式/内模式映象，使模式保持不变，从而应用程序可以保持不变。

数据库系统外部的体系结构：单用户结构、主从式结构、分布式结构、客户/服务器结构、

浏览器/应用服务器/数据库服务器结构。

Chapter 2

关系模式是对关系的描述：关系的结构（属性构成、属性来自的域、属性与域之间的映象关系），完整性约束条件，属性间的数据依赖关系集合

关系模式可以形式化地表示为：R（U，D，dom，F）

R：关系名；U：组成该关系的属性名集合；D：属性组U中属性所来自的域；

dom：属性向域的映象集合；F：属性间的数据依赖关系集合

关系模式通常可以简记为R (A1，A2，…，An)。A1，A2，…，An：属性名

数据完整性:数据的正确性、有效性和相容性。

关系模型中三类完整性约束：实体完整性、参照完整性、用户定义的完整性

实体完整性规则：若属性A是基本关系R的主属性，则属性A不能取空值

参照完整性规则：若属性（或属性组）F是关系R的外码。它与关系S的主码Ks相对应（关系R和S可是一个关系），则对于R中每个元组在F上的值必须为：或者取空值（F的每个属性值均为空值）或者等于S中某个元组的主码值。

用户定义的完整性

例:课程(课程号，课程名，学分)

“课程号”主属性必须取唯一值；非主属性“课程名”也不能取空值；“学分”属性只能取值{1，2，3，4}

关系数据库语言： 实现关系操作(查询,插改删)的语言

关系代数语言：一种抽象的查询语言，用对关系的运算来表达查询(ISBL)

关系演算语言：用谓词来表达查询要求

元组关系演算语言：谓词变元的基本对象是元组变量(APLHA )

域关系演算语言 ：谓词变元的基本对象是域变量(QBE)

具有关系代数和关系演算双重特点的语言：SQL（Structured Query Language）

关系代数运算的三个要素：运算对象，运算结果，运算符

【运算对象和运算结果的操作对象都是：关系( 元组的集合）】

传统的集合运算：并、差、交、广义笛卡尔积

专门的关系运算：选择、投影、连接、除

并：R∪S = { t|tR∨tS }         差：R -S = { t|tR∧tS }

交：R∩S = { t|tR∧tS }         笛卡尔积：R×S = {|R ∧ S }

选择（Selection）：在关系R中选择满足给定条件的诸元组。从行的角度进行的运算。

            F：选择条件，是一个逻辑表达式

[例1]  查询信息系（IS系）全体学生  

投影：从R中选择出若干属性列组成新的关系，从列的角度进行运算

    A：R中的属性列

注意：但投影之后不仅取消了原关系中的某些列，而且还可能取消某些元组（避免重复行）

[例3]  查询学生的姓名和所在系。 即求Student关系学生姓名和所在系两个属性上的投影

  

查询没有选过课的学号：

查询没有不及格的学号：

连接：从两个关系的笛卡尔积中选取属性间满足一定条件的元组

A和B：分别为R和S上的属性组；     θ：比较运算符 

例，查询没有选过课的学号，姓名。

R1=        R2=

象集（Image Set），关系R(X , Z), X, Z是属性组，x是X上的取值，定义x在R中的象集为

Zx = { t[Z] | tRt[X]= x }

  (从R中选出在X上取值为x的元组，去掉X上的分量，只留Z上的分量)

SNO=95001在R中的象集：Z95001 ={1，2，3 }，

SNO=95002在R中的象集：Z95002 ={2,   3  }，

SNO=95003在R中的象集：Z95003 ={1，2   }，

除运算：给定关系R (X，Y) 和S (Y，Z)，其中X，Y，Z为属性组。

R与S的除运算得到一个新的关系P(X)，

P是R中满足下列条件的元组在X属性列上的投影：

     元组在X上分量值x的象集Yx包含S在Y上投影的集合。

  R÷S = {[X] |R∧}        Yx：x在R中的象集，x = [X]

[例7]  查询至少选修1号课程和3号课程的学生号码 

首先建立一个临时关系K：

然后求：

[例 8]  查询选修了2号课程的学生的学号。

[例9]  查询至少选修了一门其直接先行课为5号课程的课程的学生姓名。

[例10]  查询选修了全部课程的学生号码和姓名。

[例11]查未被选修的课号

[例12]查询选修了95002所选全部课程的学生号码

图书管理数据库有关系模式：

图书(书号，书名，价格，作者）

读者（读者号，姓名，性别，年龄）

借阅（读者号,书号,借日期，还日期,罚款金额）

用关系代数查询：

1  查询价格大于50的书名和作者名

2  查询(一次)罚款金额>20元的读者名

3  查询被年龄大于60的读者借过的书名和作者名

4  查询看了所有书的读者名

5  查询看了'张三‘所看过的所有书的读者名

6  查询没有借过书的读者姓名

元组关系演算语言ALPHA

检索语句：GET

更新语句：PUT，HOLD，UPDATE，DELETE，DROP

格式    GET　工作空间名 （表达式1）

[例1]  查询所有被选修的课程号码。     GET  W  (SC.Cno) 

[例2]  查询所有学生的数据。     GET  W  (Student)

格式    GET　工作空间名（表达式1）：操作条件

[例3]查询信息系(IS) 年龄小于20岁的学号和年龄。

  GET  W  (Student.Sno，Student.Sage): Student.Sdept='IS'∧student.Sage<20

[例4]  查询计算机科学系(CS)学生的学号、年龄，结果按年龄降序排序。UP

GET  W  (Student.Sno，Student.Sage): Student.Sdept='CS'  DOWN Student.Sage

[例6]  查询信息系年龄最大的三个学生的学号及其年龄，结果按年龄降序排序。

GET  W (3)  (Student.Sno，Student.Sage):Student.Sdept='IS' DOWN Student.Sage

定义元组变量        格式：    RANGE  关系名  变量名

[例7]   查询选修2号课程的学生名字和成绩。(条件来自工作区的关系，不用存在量词)

GET W (Student.Sname，SC.Grade): SC.Sno=Student.Sno∧SC.Cno='2'

[例8]  查询选修2号课程的学生名字。

   RANGE  SC  X

   GET W (Student.Sname): X(X.Sno=Student.Sno∧X.Cno='2')

查询语义：查询这样的学生名，条件是存在选课元组X，X的学号与该生的学号相等并且X的课号为2（表明该生选了2号课）

用存在量词的检索(条件来自工作区外的关系）

[例9] 查询选修了这样课程的学生学号，其直接先行课是6号课程。

   RANGE Course  CX

   GET  W  (SC.Sno): CX (CX.Cno=SC.Cno∧CX.Pcno='6')

[例10]查询至少选修一门先行课为6号课程的学生名字或查询选修过先行课为6号课程的学生名字.                RANGE  Course  CX       SC        SCX

GET W (Student.Sname):

SCX(SCX.Sno=Student.SnoCX (CX.Cno=SCX.Cno∧CX.Pcno='6')) 

 GET  W  (Student.Sname):

    ?SCXCX (SCX.Sno=Student.Sno∧CX.Cno=SCX.Cno∧CX.Pcno='6')

 [例12]  查询不选1号课程的学生名字。

查询语义：查询这样的学生，条件是对所有选课元组， 都不表明该生选了1号课（或者与该生学号不同， 或者课号不是1)

  RANGE  SC  SCX

  GET  W (Student.Sname):   SCX (SCX.Sno≠Student.Sno∨SCX.Cno≠'1')

GET  W  (Student.Sname): SCX (SCX.Sno=Student.Sno∧SCX.Cno='1')

[例13]  查询选修了全部课程的学生姓名。

RANGE  Course  CX            SC         SCX

GET W(Student.Sname):CXSCX(SCX.Sno=Student.Sno∧SCX.Cno=CX.Cno)

解释：对于STUDENT中的一个学生，如果对所有的课程，都存在着SC的元组SCX，表明这个 学生选修了该课程，则这个学生属于查询范围。

 [例14]  查询最少选修了95002学生所选课程的学生学号。

RANGE  Couse  CX     SC     SCX     SC     SCY

GET  W  (Student.Sno):CX(SCX (SCX.Sno='95002'∧SCX.Cno=CX.Cno)                                           ?SCY(SCY.Sno=Student.Sno∧ SCY.Cno= CX.Cno))

查询语义：查询这样的学生，条件是对所有课程，如果95002选了，则该学生也选修了该课

 PQ== PQ

GET  W  (Student.Sno):CX(SCX(SCX.Sno='95002'∧SCX.Cno=CX.Cno)?SCY(SCY.Sno=Student.Sno∧SCY.Cno= CX.Cno))

 

   

CREATE TABLE Student

        (Sno       CHAR(5)  PRIMARY KEY，

         Sname  CHAR(20)  NOT NULL，         

         Ssex      CHAR(1) ，

         Sage      INT，

         Sdept    CHAR(15)

Primary key (Sno, Cno));

sex char(2) default '男'  check( sex='男' or sex='女')

)；

  DROP TABLE　Student ;

ALTER TABLE <表名>

[ ADD <新列名> <数据类型> [ 完整性约束 ] ]

[ DROP <完整性约束名> ]

[ MODIFY <列名> <数据类型> ]；

<表名>：要修改的基本表

ADD子句：增加新列和新的完整性约束条件

DROP子句：删除指定的完整性约束条件

MODIFY子句：用于修改列名和数据类型

[例2]  向Student表增加“入学时间”列，其数据类型为日期型。

     ALTER TABLE Student ADD Scome DATE；

删除属性列      例：ALTER TABLE Student  Drop Scome；

[例3]  将年龄的数据类型改为半字长整数。

 ALTER TABLE Student MODIFY Sage SMALLINT；

[例6]  为Student的sname列建立索引。

CREATE  INDEX  StuName ON Student(Sname)；

  [例6]  为Course的Cname列建立唯一值索引。

CREATE  UNIQUE INDEX  CouName  ON Course(Cname)；

在Student表的Sname（姓名）列上建立一个聚簇索引，而且Student表中的记录将按照Sname值的升序存放。       CREATE CLUSTER INDEX Stusname ON   Student(Sname)；

    DROP INDEX Stusname；

学生表：Student(Sno，Sname，Ssex，Sage，Sdept)

课程表：Course(Cno，Cname，Cpno，Ccredit)

学生选课表：SC(Sno，Cno，Grade)

[例5]  查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。

SELECT Sname，'Year of Birth: '，2012 Sage，ISLOWER(Sdept) FROM Student；

SELECT DISTINCT Sno FROM SC;

[例17]  查询名字中第2个字为"阳"字的学生的姓名和学号。

SELECT Sname，Sno FROM Student WHERE Sname LIKE '__阳%'；

 [例19]  查询DB_Design课程的课程号和学分。

SELECT Cno，Ccredit FROM Course WHERE Cname LIKE 'DB\_Design'  ESCAPE '\'

SELECT Sno，Cno FROM SC WHERE Grade IS NOT NULL；

[例32]  查询每个学生及其选修课程的情况。

用WHERE子句指定连接条件：

SELECT  * FROM     Student，SC  WHERE  Student.Sno = SC.Sno；

或，用FROM子句指定连接条件：

SELECT  * FROM  Student JOIN SC  ON   Student.Sno=SC.Sno；

自身连接

[例34]  查询每一门课的先行课名

SELECT  First.Cname  课名, Second.Cname  先行课名   FROM  Course  First，course  Second   WHERE  First.Cpno = Second.Cno；

[例36]  查询每个学生的学号、姓名、选修的课程名及成绩。

SELECT Student.Sno，Sname，Cname，Grade FROM  Student JOIN SC  ON Student.Sno = SC.Sno JOIN Course ON  SC.Cno = Course.Cno；

子查询：

SELECT Sno，Sname，Sdept FROM Student WHERE Sdept  IN (SELECT Sdept FROM Student WHERE Sname= ‘ 刘晨 ’)；

EXISTS子查询方式 (类似关系演算方式)

[例42]  查询没有选修1号课程的学生姓名。

SELECT Sname FROM Student WHERE NOT EXISTS (SELECT * FROM SC WHERE    Sno=Student.Sno AND Cno= '1')；

[例43]  查询选修了全部课程的学生姓名。

SELECT Sname FROM Student WHERE NOT EXISTS （SELECT * FROM Course WHERE NOT EXISTS SELECT * FROM SC WHERE SC.Sno= Student.Sno AND SC.Cno= Course.Cno)）；

[例44]  查询至少选修了学生95002选修的全部课程的学生号码。

思路：不存在这样的课95002选了这个学生未选

用关系演算实现：

RANGE  Couse  CX            SC     SCX                  SC     SCY

GET  W(Student.Sno): CX(SCX(SCX.Sno='95002'∧SCX.Cno=CX.Cno)∧SCY(SCY.Sno=Student.Sno∧CY.Cno= CX.Cno))

SELECT  SNO  FROM  STUDENT   S  WHERE NOT EXISTS   ( SELECT * FROM COURSE   C  WHERE  EXISTS    (SELECT * FROM SC WHERE SC.SNO='95002' AND  SC.CNO=C.CNO) AND NOT EXISTS   ( SELECT * FROM SC WHERE SC.SNO=S.SNO AND   SC.CNO=C.CNO ) )

2 查询被'张三'选修的课名

RANGE SC SCX

RANGE S  SX

GET W（C.CNAME):  存在SCX(SCX.CNO=C.CNO 并且   存在SX(SX.SNO=SCX.SNO 并且 SX.SNAME='张三'))

SELECT  CNAME  FROM  C

WHERE  EXISTS

( SELECT *  FROM SC

 WHERE SC.CNO=C.CNO  AND  EXISTS

   (SLEECT * FROM  S

    WHERE S.SNO=SC.SNO AND

    S.SNAME='张三') )

4 查询被所有同学选修的课名

SELECT CNAME FROM C

WHERE NOT EXISTS (SELECT * FROM  S

  WHERE NOT EXISTS ( SELECT * FROM SC

     WHERE SC.SNO=S.SNO AND SC.CNO=C.CNO))

5 查询选了'张三'选修所有课的学生名

SELECT SNAME FROM S

WHERE  NOT EXISTS(SELECT * FROM C

  WHERE 

     EXISTS (SELECT * FROM SC

     WHERE SC.CNO=C.CNO  AND EXISTS

      ( SELECT * FROM S WHERE S.SNO=SC.SNO AND S.SNAME=’ 张三'))

  AND

   NOT EXISTS

     (SELECT * FROM SC

    WHERE SC.CNO=C.CNO  AND SC.SNO=S.SNO)

 )

[例45]  查询计算机科学系的学生及年龄不大于19岁的学生。

SELECT * FROM Student WHERE Sdept= 'CS' UNION SELECT * FROM Student WHERE Sage<=19；

SELECT  DISTINCT  * FROM Student WHERE Sdept= 'CS'  OR  Sage<=19

查询未选课的学号

(所有学号与选过可得学号的差集)

SELECT Sno FROM Student WHERE Sno NOT  IN (SELECT  Sno FROM  SC);

INSERT  INTO Student  VALUES ('95020'，'陈冬'，'男'，'IS'，18)

INSERT INTO  Deptage(Sdept，Avgage) SELECT  Sdept，AVG(Sage) FROM  Student GROUP BY Sdept；

UPDATE  Student SET Sage=22 WHERE  Sno=' 95001 '；

DELETE FROM Student  WHERE Sno='95019'；

DELETE FROM SC；

CREATE VIEW IS_Student(No,Name,Age)  AS SELECT  Sno, Sname, Sage FROM    Student WHERE  Sdept= 'IS'；

[例2]  建立信息系学生的视图，并要求透过该视图进行的更新操作只涉及信息系学生。

CREATE VIEW IS_Student AS SELECT Sno，Sname，Sage FROM  Student WHERE  Sdept= 'IS'  WITH CHECK OPTION；

DROP VIEW IS_S1；

指定WITH CHECK OPTION子句后，DBMS在更新视图时会进行检查，防止用户通过视图对不属于视图范围内的基本表数据进行更新。

Chapter 4

为用户 'ZhangPing' 创建了一个密码为'Rose'的 SQL Server 登录

语法格式:  EXEC sp_addlogin  'UserName', 'Password' 

EXEC sp_addlogin  'ZhangPing ', 'Rose'

将用户 ZhangPing 添加到当前数据库

语法格式:   EXEC sp_adduser 'UserName'

EXEC sp_adduser 'ZhangPing'

4. 将BOOK 查询权授予PUBLIC

grant select on  BOOK to public;

5. 将READER的查询权授予ZhangPing

grant select on  READER to ZhangPing;

6. 将BOOK插入权删除权修改权授予ZhangPing

grant insert,delete,update on BOOK to ZhangPing;

7. 将LOAN的插入权删除权修改权从ZhangPing收回

revoke insert,delete,update on loan from ZhangPing

8.  重新以ZhangPing登录

登录后更改密码, 将密码'Rose'改为 'coffee'

格式:   EXEC sp_password '旧密码', '新密码'

EXEC sp_password 'Rose','coffee'

10. 查询BOOK所有数据，  （有权执行）。

13.  重新以DBA登录 将create table权授予ZhangPing

grant create table to ZhangPing

Chapter 5

2、修改批处理文件CREATE_TABLES.TXT中对表ＳＣ的定义，对外关键字加入删除与修改级联,并重新执行建立数据表

   CREATE TABLE SC(

SNO  NUMERIC(5)  REFERENCES STUDENT

               ON UPDATE CASCADE

               ON DELETE CASCADE,

    CNO NUMERIC(2)  REFERENCES COURSE(CNO)

               ON UPDATE CASCADE

               ON DELETE CASCADE,

    GRADE  NUMERIC(6,2),

        PRIMARY KEY(SNO,CNO)

);

第六章 关系数据理论

理解并给出下列术语的定义：函数依赖、部分函数依赖、完全函数依赖、传递依赖、候选码、主码、 外码、全码(All-key)、多值依赖。

定义6.1   设R(U)是一个属性集U上的关系模式，X和Y是U的子集。

若对于R(U)的任意一个可能的关系r，r中对于X的每一个具体值，Y都有唯一的具体值与之对应, 则称 “X函数决定Y” 或  “Y函数依赖于X”，记作X→Y。

例：在关系SC(Sno, Cno, Grade)中，

平凡函数依赖：   Sno → Sno     (Sno, Cno) → Sno   (Sno, Cno) → Cno

非平凡函数依赖：    (Sno, Cno) → Grade

若X→Y，但YX,则称X→Y是平凡的函数依赖,否则称X →Y是非平凡的函数依赖

候选码（Candidate key）：若关系中的某一属性组的值能唯一地标识一个元组，且该属性组没有多余的属性，则称该属性组为候选码,候选码简称为码。

1NF的定义：如果一个关系模式R的所有属性都是不可分的基本数据项，则R∈1NF。

问题正好出现在对码部分依赖的属性上

定义6.6  若关系模式R∈1NF，并且每一个非主属性都完全函数依赖于R的码，则R∈2NF。

(2NF也就是不允许关系模式存在非主属性对码的部分函数依赖)

SCG（Sno, Sname, Sage ，Cno， Grade）分解为两个关系模式

S( Sno, Sname, Sage )       S： Sno→Sname    Sno → Sage

SC（Sno， Cno， Grade）     SC:（Sno， Cno） → Grade

没有非主属性对码部分函数依赖，S，SC分别达到2NF

有关系模式 SL(Sno, Sname, Sdept, Sloc)，并且一个系的学生住处相同     

函数依赖：Sno→Sdept、Sno→Sname、Sdept→Sloc、Sno→Sloc

如果关系的码只有一个属性,一定是2NF，故已达到2NF

定义6.8 关系模式R<U，F> 中若不存在这样的码X、属性组Y及非主属性Z,使得X→Y，Y → Z成立，则称R<U，F> ∈ 3NF。（Y → X , ZY ）（即没有非主属性对码的传递函数依赖）

采用投影分解法，把SL分解为两个关系模式，以消除传递函数依赖：

SD（Sno， Sname,  Sdept）   DL（Sdept， Sloc）SD的码为Sno， DL的码为Sdept。

因为非主属性直接依赖与码,所以是3NF。

定义6.9   设关系模式R<U，F>∈1NF，如果对于R的每个非平凡函数依赖X→Y，X必含有码，那么R∈BCNF。等价定义：若每一个非平凡决定属性集（因素）都包含码，则R∈BCNF

例如：关系模式学生教师课程STC（S，T，C）  S学生，T教师，C课程。

(1)每一位教师仅教一门课,每门课有若干个教师教

(2)某个学生选定一门课后,就对应一个确定的教师    (不能重复选同一门课）

(3)某个学生选定一个教师后,就对应一门确定的课程

(S，C)→T，(S，T)→C，T→C

(S，C)和(S，T)都是码 

S、T、C都是主属性STC∈3NF 

T→C，T是决定属性集，T不是码，故STCBCNF

将STC分解为二个关系模式： ST(S，T)  ∈ BCNF       TC(T，C) ∈ BCNF

第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、BC范式（BCNF）、第四范式（4NF）

定义6.10    设R(U)是一个属性集U上的一个关系模式， X、 Y和Z是U的子集，并且Z＝U－X－Y，多值依赖 X→→Y成立当且仅当对R的任一关系r，r在（X，Z）上的每个值对应一组Y的值，这组值仅仅决定于X值而与Z值无关(即不管如何变换Z值,这组Y值不变,只与X值相关,或者说Y的这组多值只依赖于X)。

若X→→Y，而Z＝φ，则称X→→Y为平凡的多值依赖

R(T1,T2)    T1 →→T2(平凡)

S(SNO,SNAME,SAGE)  (SNO,SNAME)→→SAGE(平凡)    否则称X→→Y为非平凡的多值依赖

定义6.10   关系模式R<U，F>∈1NF，如果对于R的每个非平凡多值依赖X→→Y（YX），

X都含有码，则R∈4NF。

说明:4NF不允许有典型的多值依赖(非平凡,非函数依赖)；

4NF只允许有函数依赖型的多值依赖, 且左边含有码

课堂作业: 试举出三个多值依赖的例子。分别说明更新异常，并分解。

(1)（中小学）一个年级有多个学生，一个年级每个学生使用相同的一套教材。

R1（年级，学生号，教材名）

(2)一个专业多个班级，一个专业每个班级开设相同的一组课程。

R2（专业，班级，课程名）

(3)一个仓库有多个保管员，一个仓库每个保管员保管员相同的一组货物。

R3（仓库，保管员，货物）

课堂作业：

现在有关于学生成绩管理的关系模式，

关系模式有属性：学号，姓名，班号，班名，系号，系名，课号，课名，学期，成绩。

ALL（S#，SNAME，CLA#，CLANAME，D#，DNAME，C#，CNAME，TERM，GRADE）

写出其存在的合理的函数依赖，说明是何范式，举例说明更新异常，并分解，转化为高式，如仍存在更新异常，则继续分解。

函数依赖：

S#->SNAME,  S#->CLA#, CLA#->CLANAME, CLA#->D#, D#->DNAME,C#->CNAME,(S#,C#,TERM)->GRADE

主码是：(S#,C#,TERM)

存在非主属性对码部分依赖（SNAME，CNAME），属于1NF

存在问题:

学生信息冗余,修改学生信息? 插入新生?  删学生成绩?

课程信息冗余,修改课程信息? 插入新课程?  删学生成绩?

消除学生信息冗余和课程信息冗余。分解为：

S（S#，SNAME，CLA#，CLASSNAME，D#，DNAME）

SC（S#，C#，TERM，GRADE）           C（C#，CNAME）

其中SC中有(S#,C#,TERM)->GRADE

C中有C#->CNAME,

没有非主属性对码的部分依赖，达到2NF;没有非主属性对码的部分和传递依赖，达到3NF

决定因素包含码，达到BCNF，只有平凡的多值依赖，达到4NF.

练习：  有供应商和零件的关系模式：

SP（SNO，SNAME，PNO，PCOLOR，QTY）

请写出：SP的函数依赖？   SP的码，主属性，非主属性？

   SP属于第几范式？   SP有什么更新异常？   如何分解？

练习：  职工关系模式：（每种职务有固定的职务工资）

职工（职工编号,职工姓名,职务,职务工资,基本工资）

练习：  入库单关系模式：

入库单（入库单号，日期，材料编号，材料名称，材料规格，计量单位，入库数量）    

第7章 数据库设计

数据库设计有哪些基本步骤？

1、需求分析；2、概念结构设计；3、逻辑结构设计

4、物理结构设计；5、数据库实施；6、数据库运行与维护

简述数据库设计各个阶段内容：

1、需求分析阶段:了解用户的需求，包括数据与处理。(数据流图，数据字典)

2、概念结构设计阶段:设计独立于DBMS的概念模型（ER图）

3、逻辑结构设计阶段:设计DBMS逻辑模型，并优化

4、物理结构设计阶段:选取合适的存储结构与方法

5、数据库实施阶段:建立数据库，编制与调试应用程序，组织数据入库

6、数据库运行与维护阶段:正式运行，并对数据库评价调整与修改

E-R图向关系模型的转换规则：

(1)一个实体型转换为一个关系模式。（属性，码）

(2)m：n联系转换为一个独立关系模式。(码=各实体型码合,  联系的属性)）

(3)三个或三个以上实体间的多元联系转为一个关系模式；(码=各实体码组合,联系的属性)

(4)对于1：n联系，与n端合并，在n端关系中加入1端的码及联系属性;

(5)对于1：1联系，与其一合并， 加入对方的码；

(6)具有相同码的关系可合并

将下面E-R图转换为一组关系模式:

第八章 数据库程序设计

EXEC sp_databases       列出当前系统中的数据库

EXEC  sp_renamedb 'Northwin','Northwind1'       修改数据库的名称(单用户访问)

USE stuDB

GO

EXEC sp_tables      当前数据库中查询的对象的列表

EXEC sp_columns SC          返回某个表列的信息

EXEC sp_help SC         查看表SC的结构信息

EXEC sp_helpconstraint SC       查看表SC的约束

EXEC sp_helpindex SC            查看表SC的索引

EXEC sp_helptext   view1            查看视图的语句文本

EXEC sp_stored_procedures       查看当前数据库中的存储过程

2 建立过程 PSTU, 输出某学生的姓名和总学分

drop procedure pstu

go

use studb

go

create procedure pstu

@no int

as

declare @name char(20)

declare @total int

select @name=sname from student s where s.sno=@no;

select @total=sum(ccredit) from course where cno in(select cno from sc where sno=@no); 

print 'name='+@name+'totol credit='+cast(@total as char(5))

go

exec pstu '95001'

3建立过程 PSTU2, 输出某学生的姓名, 选课门数, 平均分, 最高分,最低分, 总学分, 如果总学分<9,则显示“此学生学分不足！”，否则显示“此学生学分已足！”，

create procedure pstu2

@no int

as

declare @name char(20)

declare @totalm int

declare @avgg int

declare @maxg int

declare @ming int

declare @allcredit int

select @name=sname from student s where s.sno=@no;

select @totalm=count(cno),@avgg=avg(grade),@maxg=max(grade),@ming=min(grade) from sc where sc.sno=@no;

select @allcredit=sum(ccredit) from course where cno in(select cno from sc where sno=@no); 

if(@allcredit<9)

print '此学生学分不足';

else

print '此学生学分已足！';

print 'name='+@name+'totol credit='+cast(@allcredit as char(5));

print 'max='+cast(@maxg as char(3))+'min='+cast(@ming as char(3))+'avg='+cast(@avgg as char(5));

print '总门数='+cast(@totalm as char(4));

go

exec pstu2 '95001'

4 一个存贮过程, （参数为课号，类型为int），输出该课成绩优，良，差的人数（使用游标）.

create procedure p1

@cno int

as

declare @gradeA int

declare @gradeB int

declare @gradeC int

declare @grade int

declare c1 cursor for select grade from sc where cno=@cno;

select @gradeA=0,@gradeB=0,@gradeC=0;

open c1;

while 1=1

begin

    fetch next from c1 into @grade

    if @@fetch_status<>0

        break;

    if @grade is not null

        if @grade>=90

            set @gradeA=@gradeA+1;

        else if @grade>=80

            set @gradeB=@gradeB+1;

        else

            set @gradeC=@gradeC+1;

end

close c1;

deallocate c1;

select @gradeA '优人数',@gradeB '良人数',@gradeC '差人数';

exec p1 2

1 编写触发器， 使学生的年龄修改只能增1

DROP TRIGGER trig_update_sage    --为防止重建时报错, 先删一下

GO

CREATE TRIGGER trig_update_sage

ON STUDENT                       

FOR UPDATE    AS         --STUDENT表的UPDATE触发器

      DECLARE @beforeSage int,@afterSage int    --定义变量 

      SELECT   @beforeSage=SAGE FROM deleted  --deleted存旧数据  

      SELECT @afterSage=SAGE FROM inserted  -- inserted存新数据

      IF @afterSage-@beforeSage<>1

          AND @afterSage-@beforeSage<>0

        BEGIN

            RAISERROR ('修改只能增1',16,1)       --报错 

            ROLLBACK TRANSACTION              --回滚,撤销UPDATE语句

         END

GO

2 编写触发器， 在deletedSTU中记录STUDENT表删除的行

DROP TRIGGER trig_delete_STU

GO

CREATE TRIGGER trig_delete_STU

ON STUDENT

  FOR DELETE

   AS

     INSERT INTO DELETEDSTU SELECT * FROM deleted

GO

3在学生表中有选课门数列(CCOUNT)和总成绩列(GTOTAL) , 对SC表编写插入触发器,   实现这样的功能：当对SC表插入数据时，自动更新学生表中选课门数列和总成绩列.

CREATE TRIGGER trig_insert_sc

ON  SC

FOR insert    AS

      DECLARE @grade int, @sno int  

      SELECT   @sno=SNO,@grade=GRADE FROM inserted   

      IF @GRADE IS NOT NULL

         UPDATE STUDENT

         SET CCOUNT=CCOUNT+1,  GTOTAL=GTOTAL+@GRADE

         WHERE SNO=@sno    

GO

4 对SC表编写触发器,  当对SC表删除数据时，自动更新学生表中选课门数列和总成绩列.

CREATE TRIGGER trig_delete_sc ON  SC

FOR DELETE    AS                                 --SC表DELETE触发器

      DECLARE @sno int ,  @grade int    --定义变量

      DECLARE C1 CURSOR FOR          --定义游标

       SELECT SNO,GRADE FROM  deleted

     OPEN C1                                           --打开游标

     WHILE  1=1                                       --循环

     BEGIN

         FETCH NEXT FROM C1 INTO  @SNO, @GRADE  --读游标

         IF @@fetch_status<>0                  --如果读完了

             BREAK 

        IF @GRADE IS NOT NULL

         UPDATE STUDENT     SET CCOUNT=CCOUNT-1,

           GTOTAL=GTOTAL-@GRADE    WHERE SNO=@sno

      END

      CLOSE C1

      DEALLOCATE C1

GO