数据库系统概念(database system concepts)英文第六版 PPT 第25章

合集下载

数据库系统概念原书第六版第八章

8.5.1 BCNF分解

Database System Concepts - 5th Edition, July 28, 2005.
7.2
©Silberschatz, Korth and Sudarshan
Database System Concepts - 5th Edition, July 28, 2005.
/*删NF· 算法通过为正则覆盖中的每个依赖显式地构造一个模式确保依赖的保持。该算法通过保证至少有一个模式包含被分解模式的候选码，确保该分解是一个无损分解。
Database System Concepts - 5th Edition, July 28, 2005.
7.9
©Silberschatz, Korth and Sudarshan
8.6.3

4NF 分解算法
Database System Concepts - 5th Edition, July 28, 2005.
7.10
©Silberschatz, Korth and Sudarshan
第八章关系数据库设计
8.5 分解算法4
8.6 使用多值依赖的分解
8.7 更多的范式
8.8 数据库设计过程 8.9 时态数据建模
Database System Concepts - 5th Edition, July 28, 2005.
7.1
©Silberschatz, Korth and Sudarshan
7.3
©Silberschatz, Korth and Sudarshan
Database System Concepts - 5th Edition, July 28, 2005.

数据库系统概念第六版课后习题部分答案2s

C H A P T E R2Introduction to the Relational ModelPractice Exercises2.1Consider the relational database of Figure??.What are the appropriateprimary keys?Answer:The answer is shown in Figure2.1,with primary keys under-lined.2.2Consider the foreign key constraint from the dept name attribute of in-structor to the department relation.Give examples of inserts and deletes tothese relations,which can cause a violation of the foreign key constraint.Answer:•Inserting a tuple:(10111,Ostrom,Economics,110,000)into the instructor table,where the department table does not have thedepartment Economics,would violate the foreign key constraint.•Deleting the tuple:(Biology,Watson,90000)from the department table,where at least one student or instructortuple has dept name as Biology,would violate the foreign key con-straint.employee(person name,street,city)works(person name company name,salary)company(company name,city)Figure2.1Relational database for Practice Exercise2.1.12Chapter2Introduction to the Relational Model2.3Consider the time slot relation.Given that a particular time slot can meetmore than once in a week,explain why day and start time are part of theprimary key of this relation,while end time is not.Answer:The attributes day and start time are part of the primary keysince a particular class will most likely meet on several different days,and may even meet more than once in a day.However,end time is notpart of the primary key since a particular class that starts at a particulartime on a particular day cannot end at more than one time.2.4In the instance of instructor shown in Figure??,no two instructors havethe same name.From this,can we conclude that name can be used as asuperkey(or primary key)of instructor?Answer:No.For this possible instance of the instructor table the namesare unique,but in general this may not be always the case(unless theuniversity has a rule that two instructors cannot have the same name,which is a rather unlikey scenario).2.5What is the result ofﬁrst performing the cross product of student andadvisor,and then performing a selection operation on the result with thepredicate s id=ID?(Using the symbolic notation of relational algebra,this query can be written as␴s id=I D(student×advisor).)Answer:The result attributes include all attribute values of studentfollowed by all attributes of advisor.The tuples in the result are asfollows.For each student who has an advisor,the result has a rowcontaining that students attributes,followed by an s id attribute identicalto the students ID attribute,followed by the i id attribute containing theID of the students advisor.Students who do not have an advisor will not appear in the result.Astudent who has more than one advisor will appear a correspondingnumber of times in the result.2.6Consider the following expressions,which use the result of a relationalalgebra operation as the input to another operation.For each expression,explain in words what the expression does.a.␴year≥2009(takes)1studentb.␴year≥2009(takes1student)c. ID,name,course id(student1takes)Answer:a.For each student who takes at least one course in2009,displaythe students information along with the information about whatcourses the student took.The attributes in the result are:ID,name,dept name,tot cred,course id,section id,semester,year,gradeb.Same as(a);selection can be done before the join operation.c.Provide a list of consisting ofExercises3ID,name,course idof all students who took any course in the university.2.7Consider the relational database of Figure??.Give an expression in therelational algebra to express each of the following queries:a.Find the names of all employees who live in city“Miami”.b.Find the names of all employees whose salary is greater than$100,000.c.Find the names of all employees who live in“Miami”and whosesalary is greater than$100,000.Answer:a. name(␴city=“Miami”(employee))b. name(␴salary>100000(employee))c. name(␴city=“Miami”∧salary>100000(employee))2.8Consider the bank database of Figure??.Give an expression in therelational algebra for each of the following queries.a.Find the names of all branches located in“Chicago”.b.Find the names of all borrowers who have a loan in branch“Down-town”.Answer:a. branch name(␴branch city=“Chicago”(branch))b. customer name(␴branch name=“Downtown”(borro w er1loan))。

《数据库系统概念》课件第三章.ppt

The schema for each relation. The domain of values associated with each attribute. Integrity constraints And as we will see later, also other information such as
The set of indices to be maintained for each relations. Security and authorization information for each relation. The physical storage structure of each relation on disk.
create table r (A1 D1, A2 D2, ..., An Dn, (integrity-constraint1), ..., (integrity-constraintk))
r is the name of the relation
each Ai is an attribute name in the schema of relation r Di is the data type of values in the domain of attribute Ai Example:
Database System Concepts - 6th Edition
3.3
©Silberschatz, Korth and Sudarshan
Data Definition Language
The SQL data-definition language (DDL) allows the specification of information about relations, including:

数据库系统概论第六版教学大纲

数据库系统概论第六版教学大纲
数据库系统概论第六版的教学大纲主要包括以下几个部分：
1. 数据库系统的基本概念：介绍数据库系统的定义、发展历程和基本原理，以及数据库系统的组成和体系结构。

2. 数据模型：介绍常见的数据模型，如层次模型、网状模型和关系模型，以及面向对象数据模型和XML数据模型等。

3. 数据库设计和规范化：介绍数据库设计的基本概念和原理，以及数据库设计和规范化的方法和步骤，包括需求分析、概念设计、逻辑设计、物理设计等。

4. 数据库管理系统：介绍数据库管理系统的基本功能和组成，以及数据库管理系统的工作原理和实现技术，包括存储管理、事务管理、并发控制、数据备份和恢复等。

5. 数据库系统应用：介绍数据库系统的应用领域和案例，包括企业级应用、电子商务、电子政务、物联网等。

6. 数据库系统性能优化：介绍数据库系统性能优化的方法和技巧，包括查询优化、索引优化、系统配置优化等。

7. 数据库安全和隐私保护：介绍数据库安全和隐私保护的基本概念和原理，以及数据库安全和隐私保护的技术和方法，包括访问控制、加密技术、审计等。

8. 数据库系统的未来发展：介绍数据库系统的未来发展趋势和方向，包括大数据处理、云计算、人工智能等。

以上是数据库系统概论第六版教学大纲的简要介绍，具体的教学内容和教学方法可以根据实际情况进行调整和补充。

数据库系统概念(database system concepts)英文第六版 PPT 第四章

Database System Concepts - 6th Edition
4.12
©Silberschatz, Korth and Sudarshan
View Definition
A view is defined using the create view statement which has
logical model (that is, all the actual relations stored in the database.)
Consider a person who needs to know an instructors name
and department, but not the salary. This person should see a relation described, in SQL, by select ID, name, dept_name from instructor
©Silberschatz, Korth and Sudarshan
Joined Relations
Join operations take two relations and return as a result
another relation.
A join operation is a Cartesian product which requires that
Join type – defines how tuples in each relation that do not
match any tuple in the other relation (based on the join condition) are treated.

数据库系统概念(database system concepts)英文第六版课后练习题答案第11章

2Chapter11Indexing and Hashingb.c.11.4Answer:•With structure11.3.a:Insert9:10:InsertExercises3Delete23:Delete19:•With structure11.3.b:Insert9:Insert4Chapter 11Indexing and HashingDelete23:Delete 19:•With structure 11.3.c:Insert9:Insert10:Insert8:Delete 23:Delete 19:Exercises511.5Answer:If there are K search-key values and m −1siblings are involvedin the redistribution,the expected height of the tree is:log ⌊(m −1)n /m ⌋(K )11.6Answer:Extendable hash structure000 001010 011 100101 110 11111.7Answer:a.Delete 11:From the answer to Exercise 11.6,change the third bucketto:At this stage,it is possible to coalesce the second and third buckets.Then it is enough if the bucket address table has just four entriesinstead of eight.For the purpose of this answer,we do not do the coalescing.b.Delete 31:From the answer to 11.6,change the last bucket to:6Chapter11Indexing and Hashingc.Insert1:From the answer to11.6,change theﬁrst bucket to:d.Insert15:From the answer to11.6,change the last bucket to:11.8Answer:The pseudocode is shown in Figure11.1.11.9Answer:Let i denote the number of bits of the hash value used in thehash table.Let bsize denote the maximum capacity of each bucket.Thepseudocode is shown in Figure11.2.Note that we can only merge two buckets at a time.The common hashpreﬁx of the resultant bucket will have length one less than the two bucketsmerged.Hence we look at the buddy bucket of bucket j differing from itonly at the last bit.If the common hash preﬁx of this bucket is not i j,thenthis implies that the buddy bucket has been further split and merge is notpossible.When merge is successful,further merging may be possible,which is handled by a recursive call to coalesce at the end of the function.11.10Answer:If the hash table is currently using i bits of the hash value,thenmaintain a count of buckets for which the length of common hash preﬁxis exactly i.Consider a bucket j with length of common hash preﬁx i j.If the bucketis being split,and i j is equal to i,then reset the count to1.If the bucketis being split and i j is one less that i,then increase the count by1.It thebucket if being coalesced,and i j is equal to i then decrease the count by1.If the count becomes0,then the bucket address table can be reduced insize at that point.However,note that if the bucket address table is not reduced at that point,then the count has no signiﬁcance afterwards.If we want to postpone thereduction,we have to keep an array of counts,i.e.a count for each value ofExercises7 functionﬁndIterator(value V){/*Returns an iterator for the search on the value V*/Iterator iter();Set iter.v alue=V;Set C=root nodewhile(C is not a leaf node)beginLet i=samllest number such that V<=C.K iif there is no such number i then beginLet P m=last non-null pointer in the nodeSet C=C.P m;endelse Set C=C.P i;end/*C is a leaf node*/Let i be the least value such that K i=Vif there is such a value i then beginSet iter.index=i;Set iter.page=C;Set iter.acti v e=T RUE;endelse if(V is the greater than the largest value in the leaf)then beginif(C.P n.K1=V)then beginSet iter.page=C.P n;Set iter.index=1;Set iter.acti v e=T RUE;endelse Set iter.acti v e=F AL SE;endelse Set iter.acti v e=F AL SE;return(iter)}Class Iterator{variables:value V/*The value on which the index is searched*/boolean active/*Stores the current state of the iterator(TRUE or FALSE)*/int index/*Index of the next matching entry(if active is TRUE)*/PageID page/*Page Number of the next matching entry(if active is TRUE)*/ function next(){if(active)then beginSet ret Page=page;Set retI ndex=index;if(index+1=page.size)then beginpage=page.P nindex=0endelse index=index+1;if(page.K index=V)then acti v e=F AL SE;return(ret Page,retI ndex)endelse return null;}}Figure11.1Pseudocode forﬁndIterator and the Iterator class8Chapter11Indexing and Hashingdelete(value K l)beginj=ﬁrst i high-order bits of h(K l);delete value K l from bucket j;coalesce(bucket j);endcoalesce(bucket j)begini j=bits used in bucket j;k=any bucket withﬁrst(i j−1)bits same as thatof bucket j while the bit i j is reversed;i k=bits used in bucket k;if(i j=i k)return;/*buckets cannot be merged*/if(entries in j+entries in k>bsize)return;/*buckets cannot be merged*/move entries of bucket k into bucket j;decrease the value of i j by1;make all the bucket-address-table entries,which pointed to bucket k,point to j;coalesce(bucket j);endFigure11.2Pseudocode for deletioncommon hash preﬁx.The array has to be updated in a similar fashion.Thebucket address table can be reduced if the i th entry of the array is0,wherei is the number of bits the table is using.Since bucket table reduction isan expensive operation,it is not always advisable to reduce the table.Itshould be reduced only when sufﬁcient number of entries at the end ofcount array become0.11.11Answer:We reproduce the instructor relation below.Exercises9 ID dept salary10101Comp.Sci.Wu9000015151MusicEinstein9500032343HistoryGold8700045565Comp.Sci.Caliﬁeri6200076543FinanceCrick7200083821Comp.Sci.Kim80000a.Bitmap for salary,with S1,S2,S3and S4representing the given inter-vals in the same orderS1000000000000S3010*********b.The question is a bit trivial if there is no bitmap on the deptname attribute is:Comp.Sci010********* Music000101000000 History000000000100 Elec.Eng.010********* Finance010*********10Chapter11Indexing and HashingScan on these records with salary80000or more gives Wu and Singhas the instructors who satisfy the given query.11.12Answer:If the index entries are inserted in ascending order,the newentries get directed to the last leaf node.When this leaf node getsﬁlled,it is split into two.Of the two nodes generated by the split,the left nodeis left untouched and the insertions takes place on the right node.Thismakes the occupancy of the leaf nodes to about50percent,except the lastleaf.If keys that are inserted are sorted in descending order,the above situationwould still occur,but symmetrically,with the right node of a split nevergetting touched again,and occupancy would again be50percent for allnodes other than theﬁrst leaf.11.13Answer:a.The cost to locate the page number of the required leaf page foran insertion is negligible since the non-leaf nodes are in memory.On the leaf level it takes one random disk access to read and onerandom disk access to update it along with the cost to write onepage.Insertions which lead to splitting of leaf nodes require anadditional page write.Hence to build a B+-tree with n r entries ittakes a maximum of2∗n r random disk accesses and n r+2∗(n r/f)page writes.The second part of the cost comes from the fact that inthe worst case each leaf is halfﬁlled,so the number of splits thatoccur is twice n r/f.The above formula ignores the cost of writing non-leaf nodes,sincewe assume they are in memory,but in reality they would also bewritten eventually.This cost is closely approximated by2∗(n r/f)/f,which is the number of internal nodes just above the leaf;we canadd further terms to account for higher levels of nodes,but these aremuch smaller than the number of leaves and can be ignored.b.Substituting the values in the above formula and neglecting the costfor page writes,it takes about10,000,000∗20milliseconds,or56hours,since each insertion costs20milliseconds.Exercises11c.function insert leaf(value K,pointer P)if(tree is empty)create an empty leaf node L,which is also the rootelse Find the last leaf node in the leaf nodes chain Lif(L has less than n−1key values)then insert(K,P)at theﬁrst available location in Lelse beginCreate leaf node L1Set L.P n=L1;Set K1=last value from page Linsert parent(1,L,K1,L1)insert(K,P)at theﬁrst location in L1endfunction insert parent(level l,pointer P,value K,pointer P1)if(level l is empty)then beginCreate an empty non-leaf node N,which is also the rootinsert(P,K,P1)at the starting of the node Nreturnelse beginFind the right most node N at level lif(N has less than n pointers)then insert(K,P1)at theﬁrst available location in Nelse beginCreate a new non-leaf page N1insert(P1)at the starting of the node Ninsert parent(l+1,pointer N,value K,pointer N1)endendThe insert leaf function is called for each of the value,pointerpairs in ascending order.Similar function can also be build for de-scending order.The search for the last leaf or non-leaf node at anylevel can be avoided by storing the current last page details in anarray.The last node in each level might be less than halfﬁlled.To makethis index structure meet the requirements of a B+-tree,we can re-distribute the keys of the last two pages at each level.Since the lastbut one node is always full,redistribution makes sure that both ofthen are at least halfﬁlled.11.14Answer:In a B+-tree index orﬁle organization,leaf nodes that areadjacent to each other in the tree may be located at different places ondisk.When aﬁle organization is newly created on a set of records,it ispossible to allocate blocks that are mostly contiguous on disk to leafsnodes that are contiguous in the tree.As insertions and deletions occur12Chapter11Indexing and Hashingon the tree,sequentiality is increasingly lost,and sequential access has towait for disk seeks increasingly often.a.One way to solve this problem is to rebuild the index to restoresequentiality.b.i.In the worst case each n-block unit and each node of the B+-treeis halfﬁlled.This gives the worst case occupancy as25percent.ii.No.While splitting the n-block unit theﬁrst n/2leaf pages areplaced in one n-block unit,and the remaining in the second n-block unit.That is,every n-block split maintains the order.Hence,the nodes in the n-block units are consecutive.iii.In the regular B+-tree construction,the leaf pages might not besequential and hence in the worst case,it takes one seek per leafing the block at a time method,for each n-node block,we will have at least n/2leaf nodes in it.Each n-node block canbe read using one seek.Hence the worst case seeks comes downby a factor of n/2.iv.Allowing redistribution among the nodes of the same block,doesnot require additional seeks,where as,in regular B+-tree werequire as many seeks as the number of leaf pages involvedin the redistribution.This makes redistribution for leaf blocksefﬁcient with this scheme.Also the worst case occupancy comesback to nearly50percent.(Splitting of leaf nodes is preferredwhen the participating leaf nodes are nearly full.Hence nearly50percent instead of exact50percent)。

数据库系统概念(英文精编版.第六版)

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
机械工业出版社
本课程学习内容
关系数据模型关系数据库语言

数据库系统概念(database system concepts)英文第六版第一章

n In the early days, database applications were built directly on top of file systems
Databa se Sy stem Concept s - 6th Edition
1 .3
©Silber schatz , Korth and S u dar
n Relational model (Chapter 2) n Example of tabular data in the relational model Columns
_____ Rows
Databa se Sy stem Concept s - 6th Edition
1 .10
©Silber schatz , Korth and S u dar
n Physical Data Independence – the ability to modify the physical schema without changing the logical schema l Applications depend on the logical schema l In general, the interfaces between the various levels and components should be well defined so that changes in some parts do not seriously influence others.
1 .5
©Silber schatz , Korth and S u dar
n Phys ical level : describes how a record (e.g., customer) is stored. n Logical level : describes data stored in database, and the relationships among the data. type instructor = record ID : string;

1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。

Representation of Geometric Constructs
Database System Concepts - 6th Edition
25.9
© Silberschatz, Korth and Sudarshan
Represented of Geometric Information
Various geometric constructs can be represented in a database in a
normalized fashion.
Database System Concepts - 6th Edition
25.2
© Silberschatz, Korth and Sudarshan
Time In Databases
While most databases tend to model reality at a point in time (at the
Chapter 25: Advanced Data Types and New Applications
Database System Concepts, 6th Ed.
© Silberschatz, Korth and Sudarshan See for conditions on re-use
timestamp: the fields of date and time, with six fractional digits for the
seconds field.
Times are specified in the Universal Coordinated Time, abbreviated
of time as well as time related queries.

Database System Concepts - 6th Edition
25.4
© Silberschatz, Korth and Sudarshan
Time Specification in SQL-92
date: four digits for the year (1--9999), two digits for the month (1--12),
may invalidate functional dependency

A temporal functional dependency x Y holds on a relation schema R if, for all legal instances r of R, all snapshots of r satisfy the functional dependency X Y. SQL:1999 to improve support of temporal data.
A snapshot of a temporal relation at time t consists of the tuples that
are valid at time t, with the time-interval attributes projected out.
Temporal selection: involves time attributes Temporal projection: the tuples in the projection inherit their time-
which can be represented as a union of intervals.
The transaction time for a fact is the time interval during which the fact
is current within the database system.
Computer Aided Design (CAD) databases store design information
about how objects are constructed E.g.: designs of buildings, aircraft, layouts of integrated-circuits
and two digits for the date (1--31).
time: two digits for the hour, two digits for the minute, and two digits for
the second, plus optional fractional digits.

SQL:1999 Part 7 (SQL/Temporal) is a proposed extension to
Database System Concepts - 6th Edition
25.7
© Silberschatz, Korth and Sudarshan
Spatial and Geographic Databases
Database System Concepts, 6th Ed.
© Silberschatz, Korth and Sudarshan See for conditions on re-use
Spatial and Geographic Databases
Spatial databases store information related to spatial locations, and
Intersect can be applied on two intervals, to give a single (possibly
empty) interval; the union of two intervals may or may not be a single interval.
Geographic databases store geographic information (e.g., maps):
often called geographic information systems or GIS.
Database System Concepts - 6th Edition
intervals from the tuples in the original relation.
Temporal join: the time-interval of a tuple in the result is the
intersection of the time-intervals of the tuples from which it is derived. It intersection is empty, tuple is discarded from join.
Chapter 25: Advanced Data Types and New Applications
Temporal Data Spatial and Geographic Databases Multimedia Databases Mobility and Personal Databases
Represent a line segment by the coordinates of its endpoints. Approximate a curve by partitioning it into a sequence of segments

Create a list of vertices in order, or Represent each segment as a separate tuple that also carries with it the identifier of the curve (2D features such as roads). List of vertices in order, starting vertex is the same as the ending vertex, or Represent boundary edges as separate tuples, with each containing identifier of the polygon, or Use triangulation — divide polygon into triangles
Database System Concepts - 6th Edition
25.6
© Silberschatz, Korth and Sudarshan
Temporal Query Languages (Cont.)
Functional dependencies must be used with care: adding a time field
“current” time), temporal databases model the states of the real world across time.
Facts in temporal relations have associated times when they are valid,
Database System Concepts - 6th Edition
25.3
© Silberschatz, Korth and Sudarshan
Time In Databases (Cont.)
Example of a temporal relation:
Temporal query languages have been proposed to simplify modeling
support efficient storage, indexing and querying of spatial data.