上海商学院数据库期末复习

1、<数据库>·数据库 (Database,简称DB)是长期储存在计算机内、有组织的、可共享的大量数据集合。·数据库管理系统（Database Management System，简称DBMS）是位于用户与操作系统之间的一层数据库管理软件。·数据库系统（Database System，DBS）是实现有组织地、动态地存储大量关联数据，方便多用户访问的计算机软件、硬件和数据资源组成的系统，即采用了数据库技术的计算机系统。·数据库系统的构成 由数据库、数据库管理系统（及其开发工具）、应用系统、数据库管理员（和用户）构成。·文件系统管理数据的缺

2、点 随着数据管理规模的扩大，数据量急剧增加，文件系统显露出三个缺陷：数据冗余。 数据与应用的独立性差。数据联系弱 ·文件系统阶段的不足之处 共享性是有一定局限，当不同的应用程序使用具有部分相同的数据时，仍必须建立各自的文件，而不能共享相同的数据，造成数据的冗余度大，不能确保数据的一致性。数据库系统特点（对比文件系统）数据由DBMS统一管理与控制。数据的整体性。数据的共享性高,冗余少。数据的独立性高。数据的整体结构化。优点便于数据的集中管理，减少数据冗余，提高数据的利用率和一致性。可以大大提高应用开发的效率，减少开发人员的工作量。维护方便，可以减轻数据库系统管理人员维护系统的负担。数据

3、库系统的三级模式结构外模式模式映象的用途保证数据的逻辑独立性。（当模式改变时，数据库管理员修改有关的外模式，使外模式保持不变。应用程序是依据数据的外模式编写的，从而应用程序不必修改，保证了数据与程序的逻辑独立性，简称数据的逻辑独立性。）模式内模式映象的用途保证数据的物理独立性。（当数据库的存储结构改变了（例如选用了另一种存储结构），数据库管理员修改模式内模式映象，使模式保持不变应用程序不受影响。保证了数据与程序的物理独立性，简称数据的物理独立性。）数据结构描述数据的组成对象以及对象之间的联系。两类对象与数据类型、内容、性质有关的对象。与数据之间联系有关的对象现实世界中的信息如何能够进入计算机

4、现实世界 系统分析 信息世界 数据库设计 计算机世界 事物及联系 信息化 概念模型 数据化 结构模型 数据模型的两个层次概念模型信息模型，按用户的观点来对数据和信息建模。概念模型不依赖计算机及DBMS。 结构模型逻辑模型，按计算机系统的观点对数据建模，受到计算机平台、DBMS的限制，直接影响数据库的最终实现。主要包括网状模型、层次模型、关系模型等。通常人们按结构模型来区分不同的数据库，称之为数据模型。常用的三种数据模型1关系模型。非关系：2层次模型。3网状模型。（数据结构：以基本层次联系为基本单位。基本层次联系：两个记录以及它们之间的一对多。包括一对一的联系）概念模型中的基本概念(1) 实体（

5、Entity） 客观存在并可相互区别的事物称为实体。可以是具体的人、事、物或抽象的概念。(2) 属性（Attribute） 实体所具有的某一特性称为属性。一个实体可以由若干个属性来刻画。 (3) 码（Key） 唯一标识实体的属性集称为码,其中的属性称主属性。 l 关系数据库关系的性质 1) 同一属性的数据具有同质性。2) 同一关系中，属性名不能重复性。因为属性名具有标识作用。3) 关系中的列位置具有顺序无关性。4) 关系具有元组无冗余性。5) 关系中的元组位置具有顺序无关性。6) 关系中每一个分量都必须是不可分的数据项。关系操作的特点(1)关系操作语言操作一体化 具有数据定义、查询、更新和控制

6、一体化的特点，既可以作为宿主语言嵌入到主语言中，又可以作为独立语言交互使用。(2) 关系操作的方式是一次一集合方式 其他系统的操作是一次一记录方式，而关系操作的方式则是一次一集合方式。关系操作数据结构单一的特点，能够使其利用集合运算和关系规范化等数学理论进行优化和处理操作，但关系操作与其他系统配合时需要解决处理方式的矛盾。(3) 关系操作语言是高度非过程化的语言 具有强大的表达能力。用户使用关系语言时，只需要指出做什么，而不需要指出怎么做，数据存取路径的选择、数据操作方法的选择和优化都由DBMS自动完成。 关系的完整性具体应用1实体完整性若属性A是基本关系R的主属性，则属性A的值不能为空值。

7、如：学生（学号，姓名，性别，出生日期）中“学号”不能为空。选修（学号，课程号，成绩）“学号、课程号”为主码，则两个属性都不能取空值。2参照完整性 关系间的引用 实体及实体间联系用关系来描述，存在关系与关系间的引用。例： 学生实体、专业实体学生（学号，姓名，性别，专业号，年龄）专业（专业号，专业名）v 学生关系引用了专业关系的主码“专业号”。 学生关系中的“专业号”值必须是确实存在的专业的专业号 ，即专业关系中有该专业的记录。外码 例： 选修关系的“学号” 与学生关系的主码“学号”相对应。选修关系的“课程号”与课程关系的主码“课程号”相对应。“学号”和“课程号”是选修关系的外码。学生关系和课程关

8、系均为被参照关系。选修关系为参照关系 用户定义的完整性。例：课程(课程号，课程名，学分)。非主属性“课程名”也不能取空值。“学分”属性只能取值在06之间参照完整性规则 例： 选修（学号，课程号，成绩）“学号”和“课程号”可能的取值 ：（1）选修关系中的主属性，不能取空值（2）只能取相应的被参照关系中已经存在的主码值如何来实现上述的关系数据库完整性约束？1实体完整性：给每个关系定义主码。2参照完整性：建立关联。3用户自定义的完整性：定义有效性规则、应用程序控制。l 数据库设计数据库系统设计包括哪些内容？p175数据库系统设计的各个步骤的任务1需求分析的任务：p177 2概念结构设计p186 就是

9、将需求分析得到的用户需求抽象为信息结构即概念模型的过程。3逻辑结构设计的任务：把概念结构设计阶段产生的系统基本E-R图转换为某种具体的DBMS所支持的数据模型相符合的逻辑结构。4物理结构设计：根据具体的DBMS的特点和应用处理的需要，将逻辑结构设计的关系模式进行物理存储安排，建立索引，形成数据库的内模式。5数据库的实施：设计人员使用具体的关系数据库管理系统提供的数据定义语言DDL和其他的实用程序将数据库逻辑结构设计和物理结构设计严格地描述出来，在计算机上建立起实际数据库结构，然后装入数据、进行测试和试运行。6数据库的运行和维护：由于应用环境在不断的变化，数据库运行过程中物理存储也会不断的变化，

10、因此对数据库设计进行评价、调整、修改等维护工作概念设计（画E-R图）逻辑设计（转换为关系模型）例题：概念设计假设工厂中有如下实体： 产品（产品编号，产品名，规格）工人（工人编号，姓名，性别，年龄，职称）车间（车间编号，车间名称，负责人）上述实体中存在如下联系：一个车间可生产多种产品，一种产品可由多个车间生产；一个车间可有多个工人，一个工人只能属于一个车间。画出E-R图，用下划线标明各实体的主码，并标明实体间的联系的类型（1:n,m:n,1:1）。例题：逻辑设计逻辑设计读者（借书证号，姓名，单位）图书（书号，书名，位置，出版社名）出版社（出版社名，地址，电话，邮编）借阅（借书证号，书号，借书日期

11、，还书日期）外码:借书证号 引用读者关系中的借书证号 ,书号引用图书关系中的书号.关系代数（并运算）（差运算）（交运算）（选择）（投影）（连接）学生选课库的关系模式为：学生（学号，姓名，性别，年龄，所在系）；课程（课程号，课程名，先行课）；选课（学号，课程号，成绩）.查询选修了课程名为“数据库”的学生的学号和姓名。SELECT StuInfo.Sname, ScoreInfo.SID ,CourseInfo.CnameFROM StuInfo,ScoreInfo ,CourseInfoWHERE CourseInfo.Cname='高等数学' and StuInfo.SID=

12、ScoreInfo.SID 查询“计算机系”的学生所选修的课程的课程号和课程名？SELECT StuInfo.Dept, ScoreInfo.CID ,CourseInfo.CnameFROM StuInfo,ScoreInfo ,CourseInfoWHERE StuInfo.Dept ='计算机系' and StuInfo.SID= ScoreInfo.SID 没有选修课程号为“C2”课程的学生学号。SELECT SID,CID FROM ScoreInfoWHERE CID!= C2 既选修“C2”课程，又选修“C3”课程的学生学号和成绩。SELECT SID,CID,s

13、core FROM ScoreInfoWHERE CID= C2and cid=C3选修课程号为“C2”或“C3”课程的学生学号。T-SQL语言创建表、修改表（见表的管理实验）数据输入、删除和修改查询(条件,排序,分组统计,联接)使用T-SQL命令在teachingData数据库中创建表SInfo表，SInfo表的表结构如下：列名数据类型长度备注SIDmyID 设为主关键字SNamemyName1 不能为空Sexchar2其值只能为“男”或“女”，默认值为“男”，Birthdaysmalldatetime 允许为空DeptmyDept  maj

14、orvarchar30 Classvarchar18允许为空IDCardNochar18不允许为空创建表create table SInfo(SID myID primary key,Sname myName1 not null,Sex char(2) default '男'check(Sex='男'or Sex='女'),Birthday smalldatetime,Dept myDept,major varchar(30),Class varchar(18),IDCardNo char(18) not null)修改表·修

15、改表SInfo表的表结构，设置DCardNo值为唯一的。alter table SInfoadd unique(IDCardNo) ·修改表SInfo的表结构，设置Major值只能为” 信息管理”、“电子商务”和“计算机科学”。 数据输入、删除、修改将某门课成绩提高3分。（课程号C0005）UPDATE CourseInfo SET成绩=成绩+3 /score=score+3 WHERE CID ='C0005' 输入某位学生某门课的成绩。（学号100026，课程号C0002，成绩86）INSERT INTO scoreInfo (SID, CID, score) V

16、ALUES (100026，C0002，86)删除某一门课的选课记录。（课程号C0005）DELETE FROM CourseInfo WHERE CID=' C0005'删除某个学生的选课记录。（学号100026）DELETE FROM ScoreInfo WHERE SID=' 100026'数据查询学生选课库的关系模式为：学生（学号，姓名，性别，年龄，所在系）；课程（课程号，课程名，先行课）；选课（学号，课程号，成绩）.用SQL语句表达下列要求:1) 查询某个学生的选课记录,包括课程号、成绩，并按课程号降序排序。（学号为0001026或姓名为王萍）Sele

17、ct cid,score from scoreInfoWhere sid=0001026 Order by cid desc2）查询某个系的学生的选课情况，显示学号、课程号、成绩，先按课程号升序，再按成绩降序排序。（计算机系SELECT SID, CID,Score FROM CourseInfo WHERE Dept= '计算机系'Order by cid，score desc3）查询每门课的平均分、最高分、选修人数并进一步筛选（人数>20，分数>80）。Select avg(score) as 平均分，max(score) as 最高分,count(sid) a

18、s 人数From scoreInfo group by cnameHaving Score > 80 and count(sid) >=204）查询选修课程名为数据分析的学生记录，包括学号、姓名、所在系。SELECT StuInfo.Sname,dept ，CourseInfo.Cname，ScoreInfo.SIDFROM StuInfo,ScoreInfo,CourseInfoWHERE CourseInfo.CName='数据分析' and StuInfo.SID= ScoreInfo.SID视图视图与基本表的区别联系P158 视图是从一个或多个基本表或视图中

19、导出的虚表。视图的用途视图的创建（CREATE VIEW）视图的修改（修改视图定义： ALTER VIEW；修改视图记录INSERT 、UPDATE和DELETE）视图的删除（Drop View ）学生选课库的关系模式为：学生（学号，姓名，性别，年龄，所在系）；课程（课程号，课程名，先行课）；选课（学号，课程号，成绩）.建立视图S1查看管理系所有男同学的信息;CREATE VIEW S1(SID,Sname,Sex)AS SELECT *FROM stuinfo,courseinfo,scoreinfoWHERE stuinfo.sid=scoreinfo.sid and courseinfo

20、.cid=scoreinfo.cid and Sex= '男' and Dept= '管理系' 通过视图S1查询年龄在18-20周岁之间的男同学的信息.select *from S1where stuinfo.sid=scoreinfo.sid and courseinfo.cid=scoreinfo.cid and age between 18 and 20 and Sex=男l 数据库安全保护数据库安全性及控制方法（数据库系统安全模型）1用户标识与鉴定是系统提供的最外层安全保护措施。只有在DBMS成功注册了的人员才是该数据库的用户，才可以访问数据库。 2用户

21、标识与鉴定解决了检查用户是否合法的问题，但是合法用户的存取权限不尽相同。数据安全性的核心问题是DBMS的存取控制机制，确保进入系统的用户只能进行合法的操作。3操作系统一级也有自己的保护措施。4数据最后还可以以加密的形式存储到数据库中。 SQL Server 2005安全体系结构:1客户机操作系统的安全性 2SQL Server的登录安全性3数据库的安全性4数据库对象的安全性SQL Server登录帐号和用户帐号登录服务器的帐号称为登录帐号(Login) ，也称登录名；登录帐号是指能登录到SQL Server服务器的帐号，属于服务器的层面，本身并不能让用户访问服务器中的数据库。如果登录服务器的用

22、户要访问数据库时必须拥有数据库用户帐号。两种登录帐号 1标准SQL Server登录帐号 2经过映射的WINDOWS帐号用户账号数据库的访问权是通过映射数据库的用户和登录帐号之间的关系来实现。特殊数据库用户:(1) dbo用户_即数据库拥有者或数据库创建者，dbo在其所拥有的数据库中拥有所有的操作权限。dbo的身份可被重新分配给另一个用户，系统管理员Sa可以作为他所管理的任何数据库的dbo用户。(2) guest用户_如果guest用户在数据库被启用，则允许任意一个登录账号作为guest用户访问数据库，其中包括那些不是数据库用户的SQL Server服务器登录帐号。数据库故障恢复的必要性及技术

23、查询例题1查询学号为06开头的学生的学号、姓名、专业？SELECT SID,SNAME,Major FROM StuInfoWHERE SID LIKE '06%'2查询所有没有考试成绩的学生的学号sid和相应的课程号cidselect sid, cid from scoreinfo where score is null3通过查询表StuInfo求信息管理专业中共有多少男生？Select major as 专业,count(sid) as 专业_男生数from StuInfo Where sex='男'group by majorHaving major=信息

24、管理4查询所有学生考试成绩在80分以上的课程的课程号、课程名、学年和学期。？SELECT ScoreInfo.CID,Cname,schyear,termFROM CourseInfo,ScoreInfo WHERE CourseInfo.CID= ScoreInfo.CID AND Score>805查询与王晓红老师职称相同教师的工号TID、姓名TName和职称Title。SELECT TID,TName, title FROM TchInfoWHERE Title=(SELECT Title FROM TchInfoWHERE TName='王晓红') and tna

25、me!='王晓红6建立计算机学院选修了00000001号课程且成绩在60分以上的学生的视图。 CREATE VIEW Stu_Pass1 AS SELECT 学号,姓名,成绩 FROM StuCourse1 WHERE 成绩>=607建立计算机学院选修了00000001号课程的学生视图StuCourse1，显示学号、姓名和成绩 。CREATE VIEW StuCourse1(学号,姓名,成绩)AS SELECT StuInfo.SID,Sname,ScoreFROM StuInfo,ScoreInfoWHERE Dept= '计算机系' ANDStuInfo.SI

26、D=ScoreInfo.SID AND ScoreInfo.CID= '00000001'8修改视图“Student_1”为查看计算机学院学生的学号、姓名、性别和专业。ALTER VIEW Student_1AS SELECT SID,sname,sex,MajorFROM StuInfoWHERE Dept= '计算机系'9利用视图StuCourse1为计算机系中每位学生的高等数学成绩减少5分。Update StuCourse1Set 成绩=成绩-510从teachingData数据库中删除视图student_2。 Drop View student_2实验七

27、 数据库的完整性1. 用T-SQL语句将StuInfo表中的属性SID设置为PRIMARY KEY。ALTER TABLE StuInfoADD CONSTRAINT PK_sid PRIMARY KEY(SID)2. 用T-SQL语句将成绩表ScoreInfo中的SID和CID字段设置复合主键，并将该约束命名为PK_SC。ALTER TABLE ScoreInfoADD CONSTRAINT PK_SC PRIMARY KEY (SID,CID)3. 使用T-SQL语句在数据库TeachingData中建立学生信息表StuInfo和成绩表ScoreInfo之间的联系，设置ScoreInfo表

28、中的SID为外键，参照StuInfo表中的SID属性值。ALTER TABLE ScoreInfoADD CONSTRAINT FK_sid FOREIGN KEY(SID) REFERENCES StuInfo(SID)4. 在TeachingData数据库中，使用T-SQL命令为课程信息表（CourseInfo）中的课程名CName属性建立UNIQUE约束。ALTER TABLE CourseInfoADD UNIQUE(CName)5. 使用T-SQL命令为TeachingData数据库的成绩表ScoreInfo设置约束：要求成绩（Score）属性的取值在0100之间。ALTER TAB

29、LE ScoreInfoADD CONSTRAINT CK_score CHECK (Score>=0 and Score<=100)6. 在TeachingData数据库中，用T-SQL语句为教师信息表TchInfo中教师职称Title添加约束CK_Title，要求Title的取值只能为“助教”、“讲师”、“副教授”、“教授”。ALTER TABLE TchInfoADD CONSTRAINT CK_Title CHECK (Title='助教' or Title='讲师' or Title='副教授' or Title='

30、教授')7. 在TeachingData数据库中，用T-SQL语句为教师信息表TchInfo中教师职称Title添加一约束DF_Title，要求将Title的默认值设置为“讲师”。ALTER TABLE TchInfoADD CONSTRAINT DF_Title DEFAULT ('讲师') FOR Title8. 创建一个规则，用以限制绑定属性中的取值范围在0150之间。CREATE RULE r1 AS c>=0 and c<=1509. 创建一个规则，用以限制绑定属性只能取基础课、必修课或选修课。CREATE RULE CProperty_rule

31、AS cp IN ('基础课','必选课','选修课')10. 将规则CProperty_rule与课程信息表CourseInfo中的CProperty字段绑定。EXEC sp_BINDRULE'CProperty_rule' , 'CourseInfo.CProperty'GO11. 解除规则CProperty_rule与课程信息表CourseInfo中CProperty属性的绑定。EXEC sp_UNBINDRULE 'CourseInfo.CProperty'GO12. 删除规则r1DROP

32、RULE r1数据的查询（一）基本查询1、查询TchInfo表中教师的工号TID、姓名TName和职称Title，要求查询结果列将TID、TName和Title字段分别显示为工号、姓名和职称。答案：USE teachingDataSELECT TID as 工号,TNAME as 姓名, Title as 职称FROM TchInfo2、查询TchInfo表中教师的全部信息。答案：USE teachingDataSELECT *FROM TchInfo3、查询TchInfo表前6行记录。答案：USE teachingDataSELECT top 6 * FROM TchInfo4、查询TchI

33、nfo表中前50%的数据。USE teachingDataSELECT top 50 percent *FROM TchInfo（二）条件查询1、在表StuInfo中查询05级、计算机系的学生姓名、性别和所在系。答案：USE teachingDataSELECT sname,sex,deptFROM StuInfoWHERE major='计算机科学' and grade='05级'2、查询StuInfo中出生年月介于1984年9月1日到1985年8月31日的学生信息。USE teachingDataSELECT *FROM StuInfoWHERE birthday between '19840901&#