ch4 数据库系统概念(第6版)第四章中间SQL
sql 第四范式-概述说明以及解释
sql 第四范式-概述说明以及解释1.引言1.1 概述第四范式是关系数据库设计中的一个重要概念，它是指在数据库设计中，将非主属性间的关系通过引入新的实体进行拆分，达到消除数据冗余和提高数据完整性的目的。
本文将围绕第四范式展开讨论，并探讨其在实际应用中的挑战。
在传统关系数据库设计中，我们常常会遇到冗余数据的问题。
冗余数据不仅浪费了存储空间，还容易导致数据的不一致性和更新异常。
为了解决这个问题，提出了规范化的概念，其中第四范式就是规范化的最高级别。
第四范式要求数据库中每个非主属性都完全依赖于键，并且不存在非主属性之间的传递依赖。
换句话说，第四范式要求数据库中的每个非主属性都是直接依赖于键的，而不是间接依赖于其他非主属性。
第四范式的优点是显而易见的。
首先，它能够消除数据冗余，减少存储空间的占用。
其次，由于数据的一致性得到了保证，更新异常的风险也大大降低。
此外，第四范式还能够提高查询的效率，因为数据的拆分使得数据的访问更加快速和高效。
然而，第四范式在实际应用中也会面临一些挑战。
首先，拆分数据可能导致查询的复杂性增加。
由于数据被分散存储在不同的表中，查询的时候需要进行多次联结操作，增加了查询的成本。
其次，第四范式对于数据一致性的要求较高，需要在应用层面进行更加复杂的控制和约束，这可能带来额外的开发和维护成本。
最后，第四范式需要根据具体业务需求进行合理的实体拆分，这对于数据库设计师来说可能是一项具有挑战性的任务。
综上所述，第四范式是关系数据库设计中一个重要的概念，它可以消除数据冗余、提高数据完整性和查询效率。
然而，在实际应用中，我们需要权衡其优点和挑战，并根据具体业务需求进行合理的设计和实施。
在下文中，我们将详细探讨第四范式的相关概念和优点，以及在实践中可能遇到的挑战。
1.2文章结构1.2 文章结构本文将按照以下结构展开讨论第四范式的相关内容：1. 引言：首先，我们会对整篇文章进行一个概述，明确我们要讨论的问题和目的，引起读者对文章的兴趣。
ch5 数据库系统概念(第6版)第五章高级SQL
SQL:1999 还支持大量的命令式结构，例如
存储过程
存储过程的优点：
使用存储过程可以减少网络流量 增强代码的重用性和共享性 使用存储过程可以加快系统运行速度 使用存储过程保证安全性
存储过程的创建
写SQL语句 测试SQL语句 如得到所需结果，则创建结果 执行过程
触发器
触发器
触发器 是一条语句，当对数据库做修改时，它自动被系 统执行. 要设置触发器机制，必须满足: 指明什么条件下触发器被执行. 指明触发器执行的动作是什么. SQL-92 标准并不包括触发器，但是许多DB系统支持触发 器。 触发器于SQL:1999被引进到SQL标准 , 但是更早就通过非 标准语法被大部分数据库所支持.
SQL允许用if-then-else语句，for和while循环，等等 ，来定义过程.
存储过程
可以在数据库中存储过程 然后通过call语句来执行 允许外部应用程序对数据库进行操作，而无需了解内 部细节
面向对象方面将在22章介绍 (基于对象的数据库)*
函数和过程
SQL:1999 支持函数和过程
过程结构*
注意: 大部分数据库系统对下列标准语法实现了自 己的变种 复合语句: begin … end, While 和 repeat 语句:
end while
repeat
set n = n + 1
数据库第六版第四章答案
Intermediate SQLPractice Exercises4.1Write the following queries in SQL:a.Display a list of all instructors,showing their ID,name,and the num-ber of sections that they have taught.Make sure to show the numberof sections as0for instructors who have not taught any section.Yourquery should use an outerjoin,and should not use scalar subqueries.b.Write the same query as above,but using a scalar subquery,withoutouterjoin.c.Display the list of all course sections offered in Spring2010,alongwith the names of the instructors teaching the section.If a section hasmore than one instructor,it should appear as many times in the resultas it has instructors.If it does not have any instructor,it should stillappear in the result with the instructor name set to“—”.d.Display the list of all departments,with the total number of instructorsin each department,without using scalar subqueries.Make sure tocorrectly handle departments with no instructors.Answer:a.Display a list of all instructors,showing their ID,name,and the num-ber of sections that they have taught.Make sure to show the numberof sections as0for instructors who have not taught any section.Yourquery should use an outerjoin,and should not use scalar subqueries.select ID,name,count(course id,section id,year,semester)as’Number of sections’from instructor natural left outer join teachesgroup by ID,nameThe above query should not be written using count(*)since count*counts null values also.It could be written using count(section id),or1920Chapter4Intermediate SQLany other attribute from teaches which does not occur in instructor,which would be correct although it may be confusing to the reader.(Attributes that occur in instructor would not be null even if the in-structor has not taught any section.)b.Write the same query as above,but using a scalar subquery,withoutouterjoin.select ID,name,(select count(*)as’Number of sections’from teaches T where T.id=I.id)from instructor Ic.Display the list of all course sections offered in Spring2010,alongwith the names of the instructors teaching the section.If a section hasmore than one instructor,it should appear as many times in the resultas it has instructors.If it does not have any instructor,it should stillappear in the result with the instructor name set to“−”.select course id,section id,ID,decode(name,NULL,’−’,name)from(section natural left outer join teaches)natural left outer join instructorwhere semester=’Spring’and year=2010The query may also be written using the coalesce operator,by re-placing decode(..)by coalesce(name,’−’).A more complex versionof the query can be written using union of join result with anotherquery that uses a subquery tofind courses that do not match;refer toexercise4.2.d.Display the list of all departments,with the total number of instructorsin each department,without using scalar subqueries.Make sure tocorrectly handle departments with no instructors.select dept name,count(ID)from department natural left outer join instructorgroup by dept name4.2Outer join expressions can be computed in SQL without using the SQLouter join operation.To illustrate this fact,show how to rewrite each of thefollowing SQL queries without using the outer join expression.a.select*from student natural left outer join takesb.select*from student natural full outer join takesAnswer:a.select*from student natural left outer join takescan be rewritten as:Exercises21 select*from student natural join takesunionselect ID,name,dept name,tot cred,NULL,NULL,NULL,NULL,NULLfrom student S1where not exists(select ID from takes T1where T1.id=S1.id)b.select*from student natural full outer join takescan be rewritten as:(select*from student natural join takes)union(select ID,name,dept name,tot cred,NULL,NULL,NULL,NULL,NULLfrom student S1where not exists(select ID from takes T1where T1.id=S1.id))union(select ID,NULL,NULL,NULL,course id,section id,semester,year,gradefrom takes T1where not exists(select ID from student S1where T1.id=S1.id))4.3Suppose we have three relations r(A,B),s(B,C),and t(B,D),with allattributes declared as not null.Consider the expressions•r natural left outer join(s natural left outer join t),and•(r natural left outer join s)natural left outer join ta.Give instances of relations r,s and t such that in the result of thesecond expression,attribute C has a null value but attribute D has anon-null value.b.Is the above pattern,with C null and D not null possible in the resultof thefirst expression?Explain why or why not.Answer:a.Consider r=(a,b),s=(b1,c1),t=(b,d).The second expression wouldgive(a,b,NULL,d).b.It is not possible for D to be not null while C is null in the result of thefirst expression,since in the subexpression s natural left outer join t,it is not possible for C to be null while D is not null.In the overallexpression C can be null if and only if some r tuple does not have amatching B value in s.However in this case D will also be null.4.4Testing SQL queries:To test if a query specified in English has been cor-rectly written in SQL,the SQL query is typically executed on multiple test22Chapter4Intermediate SQLdatabases,and a human checks if the SQL query result on each test databasematches the intention of the specification in English.a.In Section Section3.3.3The Natural Joinsubsection.3.3.3we saw an ex-ample of an erroneous SQL query which was intended tofind whichcourses had been taught by each instructor;the query computed thenatural join of instructor,teaches,and course,and as a result uninten-tionally equated the dept name attribute of instructor and course.Givean example of a dataset that would help catch this particular error.b.When creating test databases,it is important to create tuples in refer-enced relations that do not have any matching tuple in the referencingrelation,for each foreign key.Explain why,using an example queryon the university database.c.When creating test databases,it is important to create tuples with nullvalues for foreign key attributes,provided the attribute is nullable(SQL allows foreign key attributes to take on null values,as long asthey are not part of the primary key,and have not been declared asnot null).Explain why,using an example query on the universitydatabase.Hint:use the queries from Exercise Exercise4.1Item.138.Answer:a.Consider the case where a professor in Physics department teaches anElec.Eng.course.Even though there is a valid corresponding entryin teaches,it is lost in the natural join of instructor,teaches and course,since the instructors department name does not match the departmentname of the course.A dataset corresponding to the same is:instructor={(12345,’Guass’,’Physics’,10000)}teaches={(12345,’EE321’,1,’Spring’,2009)}course={(’EE321’,’Magnetism’,’Elec.Eng.’,6)}b.The query in question0.a is a good example for this.Instructors whohave not taught a single course,should have number of sections as0in the query result.(Many other similar examples are possible.)c.Consider the queryselect*from teaches natural join instructor;In the above query,we would lose some sections if teaches.ID is al-lowed to be NULL and such tuples exist.If,just because teaches.ID isa foreign key to instructor,we did not create such a tuple,the error inthe above query would not be detected.4.5Show how to define the view student grades(ID,GP A)giving the grade-point average of each student,based on the query in Exercise??;recallthat we used a relation grade points(grade,points)to get the numeric pointsExercises23 associated with a letter grade.Make sure your view definition correctly handles the case of null values for the grade attribute of the takes relation.Answer:We should not add credits for courses with a null grade;further to to correctly handle the case where a student has not completed any course, we should make sure we don’t divide by zero,and should instead return a null value.We break the query into a subquery thatfinds sum of credits and sum of credit-grade-points,taking null grades into account The outer query divides the above to get the average,taking care of divide by0.create view student grades(ID,GP A)asselect ID,credit points/decode(credit sum,0,NULL,credit sum)from((select ID,sum(decode(grade,NULL,0,credits))as credit sum,sum(decode(grade,NULL,0,credits*points))as credit pointsfrom(takes natural join course)natural left outer join grade pointsgroup by ID)unionselect ID,NULLfrom studentwhere ID not in(select ID from takes))The view defined above takes care of NULL grades by considering the creditpoints to be0,and not adding the corresponding credits in credit sum.The query above ensures that if the student has not taken any course with non-NULL credits,and has credit sum=0gets a gpa of NULL.This avoid the division by0,which would otherwise have resulted.An alternative way of writing the above query would be to use student natural left outer join gpa,in order to consider students who have not taken any course.4.6Complete the SQL DDL definition of the university database of Figure Fig-ure4.8Referential Integrityfigcnt.50to include the relations student,takes, advisor,and prereq.Answer:create table student(ID varchar(5),name varchar(20)not null,dept name varchar(20),tot cred numeric(3,0)check(tot cred>=0),primary key(ID),foreign key(dept name)references departmenton delete set null);24Chapter4Intermediate SQLcreate table takes(ID varchar(5),course id varchar(8),section id varchar(8),semester varchar(6),year numeric(4,0),grade varchar(2),primary key(ID,course id,section id,semester,year),foreign key(course id,section id,semester,year)references sectionon delete cascade,foreign key(ID)references studenton delete cascade);create table advisor(i id varchar(5),s id varchar(5),primary key(s ID),foreign key(i ID)references instructor(ID)on delete set null,foreign key(s ID)references student(ID)on delete cascade);create table prereq(course id varchar(8),prereq id varchar(8),primary key(course id,prereq id),foreign key(course id)references courseon delete cascade,foreign key(prereq id)references course);4.7Consider the relational database of Figure Figure4.11figcnt.53.Give an SQLDDL definition of this database.Identify referential-integrity constraintsthat should hold,and include them in the DDL definition.Answer:create table employee(person name char(20),street char(30),city char(30),primary key(person name))Exercises25create table works(person name char(20),company name char(15),salary integer,primary key(person name),foreign key(person name)references employee,foreign key(company name)references company)create table company(company name char(15),city char(30),primary key(company name))pp create table manages(person name char(20),manager name char(20),primary key(person name),foreign key(person name)references employee,foreign key(manager name)references employee)Note that alternative datatypes are possible.Other choices for not nullattributes may be acceptable.4.8As discussed in Section Section4.4.7Complex Check Conditions and Assertionssubsection.4.4we expect the constraint“an instructor cannot teach sections in two differ-ent classrooms in a semester in the same time slot”to hold.a.Write an SQL query that returns all(instructor,section)combinationsthat violate this constraint.b.Write an SQL assertion to enforce this constraint(as discussed in Sec-tion Section4.4.7Complex Check Conditions and Assertionssubsection.4.4.7,current generation database systems do not support such assertions,although they are part of the SQL standard).Answer:a.select ID,name,section id,semester,year,time slot id,count(distinct building,room number)from instructor natural join teaches natural join sectiongroup by(ID,name,section id,semester,year,time slot id)having count(building,room number)>1Note that the distinct keyword is required above.This is to allow twodifferent sections to run concurrently in the same time slot and are26Chapter4Intermediate SQLtaught by the same instructor,without being reported as a constraintviolation.b.create assertion check not exists(select ID,name,section id,semester,year,time slot id,count(distinct building,room number)from instructor natural join teaches natural join sectiongroup by(ID,name,section id,semester,year,time slot id)having count(building,room number)>1)4.9SQL allows a foreign-key dependency to refer to the same relation,as in thefollowing example:create table manager(employee name char(20),manager name char(20),primary key employee name,foreign key(manager name)references manageron delete cascade)Here,employee name is a key to the table manager,meaning that each em-ployee has at most one manager.The foreign-key clause requires that everymanager also be an employee.Explain exactly what happens when a tuplein the relation manager is deleted.Answer:The tuples of all employees of the manager,at all levels,getdeleted as well!This happens in a series of steps.The initial deletion willtrigger deletion of all the tuples corresponding to direct employees ofthe manager.These deletions will in turn cause deletions of second levelemployee tuples,and so on,till all direct and indirect employee tuples aredeleted.4.10SQL-92provides an n-ary operation called coalesce,which is defined asfollows:coalesce(A1,A2,...,A n)returns thefirst nonnull A i in the listA1,A2,...,A n,and returns null if all of A1,A2,...,A n are null.Let a and b be relations with the schemas A(name,address,title)and B(name,address,salary),respectively.Show how to express a natural full outer joinb using the full outer-join operation with an on condition and the coalesceoperation.Make sure that the result relation does not contain two copiesof the attributes name and address,and that the solution is correct even ifsome tuples in a and b have null values for attributes name or address.Answer:Exercises27 select coalesce(,)as name,coalesce(a.address,b.address)as address,a.title,b.salaryfrom a full outer join b on = anda.address=b.address4.11Some researchers have proposed the concept of marked nulls.A markednull⊥i is equal to itself,but if i=j,then⊥i=⊥j.One application of marked nulls is to allow certain updates through views.Consider the view instructor info(Section Section4.2Viewssection.4.2).Show how you can use marked nulls to allow the insertion of the tuple(99999,“Johnson”,“Music”) through instructor info.Answer:To insert the tuple(99999,“(”Johnson),“Music”)into the view instructor info,we can do the following:instructor←(99999,“Johnson”,⊥k,⊥)∪instructordepartment←(⊥k,“Music′′,⊥)∪departmentsuch that⊥k is a new marked null not already existing in the database.Note:“Music”here is the name of a building and may or may not be related to Music department.。
第四版数据库系统概论课后答案全
第四版数据库系统概论课后答案(全)第3章关系数据库标准语言SQL1 ．试述sQL 语言的特点。
答：(l）综合统一。
sQL 语言集数据定义语言DDL 、数据操纵语言DML 、数据控制语言DCL 的功能于一体。
(2）高度非过程化。
用sQL 语言进行数据操作，只要提出“做什么”，而无需指明“怎么做”，因此无需了解存取路径，存取路径的选择以及sQL 语句的操作过程由系统自动完成。
(3）面向集合的操作方式。
sQL 语言采用集合操作方式，不仅操作对象、查找结果可以是元组的集合，而且一次插入、删除、更新操作的对象也可以是元组的集合。
(4）以同一种语法结构提供两种使用方式。
sQL 语言既是自含式语言，又是嵌入式语言。
作为自含式语言，它能够独立地用于联机交互的使用方式；作为嵌入式语言，它能够嵌入到高级语言程序中，供程序员设计程序时使用。
(5）语言简捷，易学易用。
2 ．试述sQL 的定义功能。
sQL 的数据定义功能包括定义表、定义视图和定义索引。
SQL 语言使用cREATE TABLE 语句建立基本表，ALTER TABLE 语句修改基本表定义，DROP TABLE 语句删除基本表；使用CREATE INDEX 语句建立索引，DROP INDEX 语句删除索引；使用CREATE VIEW 语句建立视图，DROP VIEW 语句删除视图。
3 ．用sQL 语句建立第二章习题5 中的4 个表。
答：对于S 表：S ( SNO , SNAME , STATUS , CITY ) ; 建S 表：CREATE TABLE S ( Sno C(2) UNIQUE，Sname C(6) ，Status C(2)，City C(4));对于P 表：P ( PNO , PNAME , COLOR , WEIGHT );建P 表：CREATE TABLE P(Pno C(2) UNIQUE，Pname C(6)，COLOR C(2)，WEIGHT INT);对于J 表：J ( JNO , JNAME , CITY）;建J 表：CREATE TABLE J(Jno C(2) UNlQUE，JNAME C(8)，CITY C(4))对于sPJ 表：sPJ ( sNo , PNo , JNo , QTY）;建SPJ 表：SPJ(SNO,PNO,JNO,QTY)CREATE TABLE SPJ(Sno C(2)，PnoC(2)，JNO C(2)，QTY INT))4.针对上题中建立的 4 个表试用sQL 语言完成第二章习题 5 中的查询。
数据库系统概念(database system concepts)英文第六版 课后练习题 答案 第7章
7.2 Answer: Note: the name of the relationship "course offering" needs to be changed to "section".
a. The E-R diagram is shown in Figure 7.2. Note that an alterantive is to model examinations as weak entities related to a section, rather than as a strong entity. The marks relationship would then be a binary relationship between student and exam, without directly involving section.
7.7 Answer: The primary key of a weak entity set can be inferred from its relationship with the strong entity set. If we add primary key attributes to the weak entity set, they will be present in both the entity set and the relationship set and they have to be the same. Hence there will be redundancy.
b. As indicated in the answer to the previous part, a path in the graph between a pair of entity sets indicates a (possibly indirect) relationship between the two entity sets. If there is a cycle in the graph then every pair of entity sets on the cycle are related to each other in at least two distinct ways. If the E-R diagram is acyclic then there is a unique path between every pair of entity sets and, thus, a unique relationship between every pair of entity sets.
数据库系统概念(英文精编版.第六版)
Atomicity of updates
Failures
may lead to inconsistencies (1) account_A = account_A – 100 (2) account_B = account_B + 100
Example:
Concurrent access by multiple users
Exercises
Computer users interacts with data in the _______ level A. physical B. logical C. view D. all of the above Application users interact with data in the _______ level. A. physical B. logical C. view D. all of the above How the data are actually stored is called _______ A. Physical level B. Logical level C. View level D. Conceptual level
property is called ( )
A. Data inconsistency C. Data isolation B. Data redundancy D. Data integrity
1.3 View of Data
Hierarchy of Abstraction Levels
Three Abstraction Levels of Data
机械工业出版社
本课程学习内容
关系数据模型 关系数据库语言
《数据库系统概论》复习笔记
《数据库系统概论》复习笔记期末复习顺便总结下，书本为⾼等教育出版社的《数据库系统概论》。
第⼀章知识点数据库是长期储存之计算机内的、有组织的、可共享的⼤量数据的集合。
1，数据库数据特点 P4永久存储，有组织，可共享。
2，数据独⽴性及其如何保证 P10，P34逻辑独⽴性：⽤户的应⽤程序与数据库的逻辑结构互相独⽴。
（内模式保证）物理独⽴性：⽤户的应⽤程序与存储在磁盘上的数据库中的数据相互（外模式保证）3，数据模型的组成要素 P13数据结构、数据操作、完整性约束。
4，⽤ER图来表⽰概念模型 P17实体、联系和属性。
联系本⾝也是⼀种实体型，也可以有属性。
第⼆章1，关系的相关概念（如关系、候选码、主属性、⾮主属性） P42-P44单⼀的数据结构---- 关系。
现实世界的实体以及实体间的各种联系均⽤关系来表⽰。
域是⼀组具有相同数据类型的值的集合。
若关系中的某⼀属性组的值能唯⼀地标识⼀个元组，则称该属性组为候选码关系模式的所有属性组是这个关系模式的候选码，称为全码若⼀个关系有多个候选码，则选定其中⼀个为主码候选码的诸属性称为主属性不包含在任何侯选码中的属性称为⾮主属性2，关系代数运算符 P52⾃然连接是在⼴义笛卡尔积R×S中选出同名属性上符合相等条件元组，再进⾏投影，去掉重复的同名属性，组成新的关系。
3，关系代数表达式第三章操作对象操作⽅式创建删除修改模式CREATE SCHEMA DROP SCHEMA表CREATE TABLE DROP TABLE ALTER TABLE 视图CREATE VIEW DROP VIEW索引CREATE INDEX DROP INDEX1，SQL的特点P79-P801. 综合统⼀2. ⾼度⾮过程化3. ⾯向集合的操作⽅式4.以同⼀种语法结构提供多种使⽤⽅式5. 语⾔简洁，易学易⽤2，基本表的定义、删除和修改P84-P87PRIMARY KEYPRIMARY KEY (Sno，Cno)UNIQUEFOREIGN KEY (Cpno) REFERENCES Course(Cno)ALTER TABLE <表名>[ ADD <新列名> <数据类型> [ 完整性约束 ] ][ DROP <完整性约束名> ][ ALTER COLUMN<列名> <数据类型> ]；DROP TABLE <表名>［ RESTRICT| CASCADE］；3，索引的建⽴与删除P89-P90CREATE [UNIQUE] [CLUSTER] INDEX <索引名>ON <表名>(<列名>[<次序>][,<列名>[<次序>] ]…)；唯⼀索引 UNIQUE、⾮唯⼀索引或聚簇索引 CLUSTERDROP INDEX <索引名>；4，数据查询P91-P114唯⼀ DISTINCT确定范围 BETWEEN AND，NOT BETWEEN AND确定集合 IN，NOT IN字符匹配 LIKE，NOT LIKE空值 IS NULL，IS NOT NULL多重条件（逻辑运算） AND，OR，NOTORDER BY⼦句升序： ASC；降序： DESC；缺省值为升序聚集函数：计数COUNT（[DISTINCT|ALL] *）COUNT（[DISTINCT|ALL] <列名>）计算总和SUM（[DISTINCT|ALL] <列名>）计算平均值AVG（[DISTINCT|ALL] <列名>）最⼤最⼩值MAX（[DISTINCT|ALL] <列名>）MIN（[DISTINCT|ALL] <列名>）左外连接 LEFT OUT JOIN XXX ON (XX.A = XXX.A)5，数据更新P115-P118INSERTINTO <表名> [(<属性列1>[，<属性列2 >…)]VALUES (<常量1> [，<常量2>] … )/或⼦查询UPDATE <表名>SET <列名>=<表达式>[，<列名>=<表达式>]…[ WHERE <条件>]；DELETEFROM <表名>[ WHERE <条件>]；6，视图的P118-126CREATE VIEW<视图名> [(<列名> [，<列名>]…)]AS <⼦查询> --⼦查询不允许含有ORDER BY⼦句和DISTINCT短语 [ WITH CHECK OPTION]；DROP VIEW <视图名>；第四章、第五章12，数据库⾓⾊P142-P1433，数据库的三类完整性及其实现P152-P158实体完整性CREATE TABLE中⽤PRIMARY KEY定义参照完整性在CREATE TABLE中⽤FOREIGN KEY短语定义哪些列为外码⽤REFERENCES短语指明这些外码参照哪些表的主码⽤户定义的完整性CREATE TABLE时定义列值⾮空（NOT NULL）列值唯⼀（UNIQUE）检查列值是否满⾜⼀个布尔表达式（CHECK）CONSTRAINT 约束CONSTRAINT <完整性约束条件名>［PRIMARY KEY短语|FOREIGN KEY短语|CHECK短语］使⽤ALTER TABLE语句修改表中的完整性限制可以先删除原来的约束条件，再增加新的约束条件ALTER TABLE StudentDROP CONSTRAINT C1;ALTER TABLE StudentADD CONSTRAINT C1 CHECK (Sno BETWEEN 900000 AND 999999)第六章关系模式是⼀个五元组： R(U, D, DOM, F)12，1NF,2NF，3NF P175-P176如果⼀个关系模式R的所有属性都是不可分的基本数据项，则R∈ 1NF第⼀范式是对关系模式的最起码的要求若R∈1NF，且每⼀个⾮主属性完全函数依赖于码，则R∈ 2NF。
数据库系统及应用(第六版)第4章数据库及表的操作
4.2 数据表操作
4.2.1 表的基本操作
1 表的打开、关闭和浏览
（1）菜单方式
4.2 数据表操作
4.2.1 表的基本操作
1 表的打开、关闭和浏览
（1）菜单方式
4.2 数据表操作
4.2.1 表的基本操作
1 表的打开、关闭和浏览
（2）“数据工作期”方式
4.2 数据表操作
4.2.1 表的基本操作
4.1 数据库操作
4.1.3 创建数据库表
4
修改表结构
（2）打开数据库修改数据表 如果数据库已经打开，则可以使用“数据库设计器”修改当前数据 库内所有的数据表。方法是首先在“数据库设计器”内单击选中某个数 据库表，然后执行【数据库】|【修改】菜单命令。或者右击数据库表 打开快捷菜单，执行【修改】菜单命令。还可以单击“数据库设计器” 工具栏内的“修改表”工具按钮。上述三种操作的目的都是为了打开 “表设计器”。
删除触发器：用于指定一个规则，每当用户对表中的记录进行删 除时触发该规则并进行相应的检查。如果表达式值为“假”，则记录 将不能被删除。
4.1 数据库操作
4.1.3 创建数据库表
4
修改表结构
（1）直接修改数据表 执行【文件】|【打开】菜单命令，打开表文件，然后执行【显示】| 【表设计器】菜单命令。使用这种方式可以在不打开数据库的情况下直接 修改数据库中的表，它等同于使用了以下两条命令： USE<表名> MODIFY STRUCTURE
4.1 数据库操作
4.1.4 添加和移去数据表
1 向数据库中添加表
当一个数据库被打开后，用户可以单击“数据库设计器”工具栏的 【添加表】按钮，或者执行【数据库】|【添加表(A)】菜单命令，显示 “打开”对话框，选择被添加的数据表，然后单击【确定】按钮，将该 表添加到数据库内。用户也可以使用命令方式向当前数据库添加数据表。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
可以通过任何基于逻辑运算符返回结果 TRUE 或 FALSE 的 逻辑（布尔）表达式来创建 CHECK 约束。
例：alter table table3 add constraint a2_check check (salary >=1600 and salary <=30000)
[ WITH CHECK对已在库中数据作检查 | WITH NOCHECK对已在库中数 据不作检查 ] 对单独一列可使用多个 CHECK 约束。按约束创建的顺序对 其取值。通过在表一级上创建 CHECK 约束，可以将该约束 应用到多列上。
视图展开
在查询/另一视图中扩展使用视图
create view physics_fall_2009_watson as (select course_id, room_number from (select course.course_id, building,
room_number from course, section where course.course_id = section.course_id and course.dept_name = ’Physics’ and section.semester = ’Fall’ and section.year = ’2009’) where building= ’Watson’;
CHECK 约束通过限制输入到列中的值来强制域的完整性。这与
FOREIGN KEY 约束控制列中数值相似。区别在于它们如何判断哪些 值有效：FOREIGN KEY 约束从另一个表中获得有效数值列表， CHECK 约束从逻辑表达式判断而非基于其它列的数据。例如，通过 创建 CHECK 约束可将 salary 列的取值范围限制在 $15,000 至 $100,000 之间，从而防止输入的薪金值超出正常的薪金范围。
check子句
check (P) P 是谓词
例如: 确保semester的取值是 fall, winter, spring, summer之一: create table section ( course_id varchar (8), sec_id varchar (8), semester varchar (6), year numeric (4,0), building varchar (15), room_number varchar (7), time slot id varchar (4), primary key (course_id, sec_id, semester, year), check (semester in (’Fall’, ’Winter’, ’Spring’, ’Summer’)) );
参照完整性
被参照实体(表)
被参照实体(表)
问题 保证一个值出现在一个关系属性集中，同时也出现在另一个 关系的某一属性集中。 例如：如果 “Perryridge”是分行名字出现在 account 关 系 的一个元组中，那么在 branch 关系中存在元组 “Perryridge”。 关系模式的属性中可以包含另一关系模式的主码，这样的码 称为外码。（复习）
其中 <query expression>是任何合法的SQL表达式. 视 图名用v表示. 一旦定义了视图，就可以用视图名指代视图产生的虚关系. 视图定义不等同于通过计算查询表达式来新建一个关系.
视图范例
教师的视图，不显示薪水 create view faculty as select ID, name, dept_name from instructor 列出所有Biology department的教师 select name from faculty where dept_name = ‘Biology’ 创建部门总薪水的视图 create view departments_total_salary(dept_name, total_salary) as select dept_name, sum (salary) from instructor group by dept_name;
check子句 域约束（续）*
SQL 2000 中用规则
规则是一个向后兼容的功能，用于执行一些与 CHECK 约束相同的功能。CHECK 约束是用来限制列值的首选 标准方法。CHECK 约束比规则更简明，一个列只能应 用一个规则，但是却可以应用多个 CHECK 约束。 CHECK 约束作为 CREATE TABLE 语句的一部分进行指 定，而规则以单独的对象创建，然后绑定到列上。 下例创建一个规则，执行与前面主题中的 CHECK 约束 示例相同的功能。Microsoft SQL Server 2000 首 选的方法是 CHECK 约束。
select ID, name, dept_name from instructor
视图提供了一个机制，来对特定用户隐藏特定数据. 视图是用户可以看见的“虚关系”，不是逻辑模型 的一部分.
视图定义
视图通过如下形式的create view语句来定义
create view v as < query expression >
一个支票账户余额必须超过$10,000.00 银行雇员的薪水必须至少每小时$4.00 顾客必须有个（非空）电话号码
单个关系的完整性约束（实体完整性）
not null primary key unique check (P), P是谓词
非空和唯一性约束
not null
声明 name 和 budget 是非空的
用视图定义视图
视图可以出现在定义另一视图的表达式中 如果v2用在定义v1的表达式中，视图关系v1直接依赖于视 图关系v2， 如果v1直接依赖于v2 或者在依赖图中有从v2到v1的路径 ，视图关系v1依赖于视图关系v2， 如果其依赖于自身，视图v是递归的.
事务*
工作单元 原子Biblioteka 务 要么完全执行，要么回滚，当做没有发生
从并发事务中隔离 事务隐式开始
由提交工作或者回滚工作结束
大部分数据库都默认: 每个SQL语句都自动提交
可以关闭一个会话中的自动提交 (例如使用 API) 在SQL:1999, 可以使用: begin atomic …. end
大部分数据库不支持
完整性约束
完整性约束提供了一种手段来保证当授权 用户对数据库做修改时不会破坏数据的一 致性，防止对数据的意外破坏.
course natural full outer join prereq
连接关系 - 范例
course inner join prereq on course.course_id = prereq.course_id
course left outer join prereq on course.course_id = prereq.course_id
用视图定义视图
create view physics_fall_2009 as select course.course_id, sec_id, building, room_number from course, section where course.course_id = section.course_id and course.dept_name = ’Physics’ and section.semester = ’Fall’ and section.year = ’2009’; create view physics_fall_2009_watson as select course_id, room_number from physics_fall_2009 where building= ’Watson’;
参照完整性的级联操作
create table course ( course_id char(5) primary key, title varchar(20), dept_name varchar(20) references department
)
create table course ( … dept_name varchar(20), foreign key (dept_name) references department on delete cascade on update cascade, ... ) 可选的级联操作: set null, set default
name varchar(20) not null budget numeric(12,2) not null
unique ( A1, A2, …, Am)
唯一性声明表明了属性A1, A2, … Am构成一个候选码. 候选码允许空值 (主码则不允许).
check子句 域约束
问题 完整性约束通过授权 用户对数据库修改时，保证不会损坏 数据库的一致性。 域约束是完整性约束最基本的形式，它会测试插入数据库 的值。 CHECK 约束 (在SQL-92中 check 允许域受约束)
数据库系统
上海交通大学计算机系 张忠能
zhang-zn@
1
第4章：中间SQL
中间SQL
连接表达式 视图 事务 完整性约束 SQL数据类型和模式 授权
连接关系
连接运算输入两个关系，返回另一个关系作为结 果. 连接运算是一个笛卡尔积，它要求两个关系中的 元组相匹配（符合某种条件）。同时也可以指定 连接的结果所需要展示的属性。 连接运算通常被使用于from子句的子查询表达式 中
右外连接
course natural right outer join prereq
连接关系
连接运算输入两个关系，返回另一个关系作为结果 连接运算通常被使用于from子句的子查询表达式中 连接条件 – 定义了两个关系中的哪些元组能够匹配，哪些 属性在连接结果当中被展示 连接类型 P67