数据库系统概念第五版12章
数据库系统概论第五版课件
两大类数据模型 (续)
信息世界 机器世界
现实世 界
概念模型
认识 抽象
现实世界 概念模型 数据库设计人员完成
概念模型 逻辑模型 数据库设计人员完成
DBMS支持的数据模型
逻辑模型 物理模型 由DBMS完成
现实世界中客观对象的抽象过程
n 实体型B m:n联系
应用程序1 应用程序2
数据集1 数据集2
...… ...…
应用程序ｎ
数据集n
人工管理阶段应用程序与数据之间的对应关系
二、文件系统阶段
时期
20世纪50年代末--60年代中
产生的背景
应用需求
科学计算、管理
硬件水平
磁盘、磁鼓
软件水平
有文件系统
处理方式
联机实时处理、批处理
文件系统阶段(续)
第一章 绪论
1.1 数据库系统概述
1.2 数据模型 1.3 数据库系统结构 1.4 数据库系统的组成 1.5 小结
1.2 数据模型
1.2.1 两大类数据模型 1.2.2 数据模型的组成要素 1.2.3 概念模型 1.2.4 最常用的数据模型 1.2.5 层次模型 1.2.6 网状模型 1.2.7 关系模型
数据库整体数据结构化
学号 姓名 性别 年龄 系别
日期 学校 学历名 家庭出身 籍贯 政治面貌
学号 课程号 成绩
课程号 课程名 学时 姓名 与本人关系 详细情况
日期 奖惩条目
数据库系统实现整体数据结构化
数据的共享性高，冗余度低，易扩充
数据库系统从整体角度看待和描述数据，数据 面向整个系统，可以被多个用户、多个应用共 享使用。
数据库系统概念(第五版)Abraham Silberschatz著,CH8
Chapter 8: Application Design and Development
User Interfaces and Tools Web Interfaces to Databases Web Fundamentals Servlets and JSP Building Large Web Applications Triggers Authorization in SQL Application Security
8.5
©Silberschatz, Korth and Sudarshan
Web Interfaces to Databases
Why interface databases to the Web?
1.
Web browsers have become the de-facto standard user interface to databases Enable large numbers of users to access databases from anywhere Avoid the need for downloading/installing specialized code, while providing a good graphical user interface Examples: banks, airline and rental car reservations, university course registration and grading, an so on.
Database System Concepts - 5th Edition, Oct 23, 2006.
8.3
©Silberschatz, Korth and Sudarshan
数据库系统概念答案(第五版)
C H A P T E R2Exercises2.4Describe the differences in meaning between the terms relation and relation schema.Answer:A relation schema is a type definition,and a relation is an instance of that schema.For example,student(ss#,name)is a relation schema andis a relation based on that schema.2.5Consider the relational database of Figure2.35,where the primary keys are un-derlined.Give an expression in the relational algebra to express each of the fol-lowing queries:a.Find the names of all employees who work for First Bank Corporation.b.Find the names and cities of residence of all employees who work for FirstBank Corporation.c.Find the names,street address,and cities of residence of all employees whowork for First Bank Corporation and earn more than$10,000per annum.d.Find the names of all employees in this database who live in the same cityas the company for which they work.e.Assume the companies may be located in several cities.Find all companieslocated in every city in which Small Bank Corporation is located.Answer:a.Πperson-name(σcompany-name=“First Bank Corporation”(works))78Chapter2Relational Modelemployee(person-name,street,city)works(person-name,company-name,salary)company(company-name,city)manages(person-name,manager-name)Figure2.35.Relational database for Exercises2.1,2.3and2.9.b.Πperson-name,city(employee1(σcompany-name=“First Bank Corporation”(works)))c.Πperson-name,street,city(σ(company-name=“First Bank Corporation”∧salary>10000)works1employee)d.Πperson-name(employee1works1company)e.Note:Small Bank Corporation will be included in each answer.Πcompany-name(company÷(Πcity(σcompany-name=“Small Bank Corporation”(company))))2.6Consider the relation of Figure2.20,which shows the result of the query“Findthe names of all customers who have a loan at the bank.”Rewrite the query to include not only the name,but also the city of residence for each customer.Observe that now customer Jackson no longer appears in the result,even though Jackson does in fact have a loan from the bank.a.Explain why Jackson does not appear in the result.b.Suppose that you want Jackson to appear in the result.How would youmodify the database to achieve this effect?c.Again,suppose that you want Jackson to appear in the result.Write a queryusing an outer join that accomplishes this desire without your having to modify the database.Answer:The rewritten query isΠcustomer-name,customer-city,amount(borrower1loan1customer)a.Although Jackson does have a loan,no address is given for Jackson in thecustomer relation.Since no tuple in customer joins with the Jackson tuple of borrower,Jackson does not appear in the result.b.The best solution is to insert Jackson’s address into the customer relation.Ifthe address is unknown,null values may be used.If the database system does not support nulls,a special value may be used(such as unknown)for Jackson’s street and city.The special value chosen must not be a plausible name for an actual city or street.c.Πcustomer-name,customer-city,amount((borrower1loan)1customer)2.7Consider the relational database of Figure2.35.Give an expression in the rela-tional algebra for each request:a.Give all employees of First Bank Corporation a10percent salary raise.Exercises 9b.Give all managers in this database a 10percent salary raise,unless the salary would be greater than $100,000.In such cases,give only a 3percent raise.c.Delete all tuples in the works relation for employees of Small Bank Corpora-tion.Answer:a.works ←Πperson -name,company -name,1.1∗salary (σ(company -name =“First Bank Corporation”)(works ))∪(works −σcompany -name =“First Bank Corporation”(works ))b.The same situation arises here.As before,t 1,holds the tuples to be updated and t 2holds these tuples in their updated form.t 1←Πworks.person -name,company -name,salary (σworks.person -name =manager -name (works ×manages ))t 2←Πworks.person -name,company -name,salary ∗1.03(σt 1.salary ∗1.1>100000(t 1))t 2←t 2∪(Πworks.person -name,company -name,salary ∗1.1(σt 1.salary ∗1.1≤100000(t 1)))works ←(works −t 1)∪t 2c.works ←works −σcompany −name =“Small Bank Corporation”(works )2.8Using the bank example,write relational-algebra queries to find the accountsheld by more than two customers in the following ways:ing an aggregate function.b.Without using any aggregate functions.Answer:a.t 1←account -number G count customer -name (depositor )Πaccount -number σnum -holders>2 ρaccount -holders (account -number,num -holders )(t 1)b.t 1←(ρd 1(depositor )×ρd 2(depositor )×ρd 3(depositor ))t 2←σ(d 1.account -number =d 2.account -number =d 3.account -number )(t 1)Πd 1.account -number (σ(d 1.customer -name =d 2.customer -name ∧d 2.customer -name =d 3.customer -name ∧d 3.customer -name =d 1.customer -name )(t 2))2.9Consider the relational database of Figure 2.35.Give a relational-algebra expres-sion for each of the following queries:a.Find the company with the most employees.b.Find the company with the smallest payroll.c.Find those companies whose employees earn a higher salary,on average,than the average salary at First Bank Corporation.Answer:10Chapter 2Relational Modela.t 1←company -name G count-distinct person -name (works )t 2←max num -employees (ρcompany -strength (company -name,num -employees )(t 1))Πcompany -name (ρt 3(company -name,num -employees )(t 1)1ρt 4(num -employees )(t 2))b.t 1←company -name G sum salary (works )t 2←min payroll (ρcompany -payroll (company -name,payroll )(t 1))Πcompany -name (ρt 3(company -name,payroll )(t 1)1ρt 4(payroll )(t 2))c.t 1←company -name G avg salary (works )t 2←σcompany -name =“First Bank Corporation”(t 1)Πt pany -name ((ρt 3(company -name,avg -salary )(t 1))1t 3.avg -salary >first -bank.avg -salary (ρfirst -bank (company -name,avg -salary )(t 2)))2.10List two reasons why null values might be introduced into the database.Answer:Nulls may be introduced into the database because the actual value is either unknown or does not exist.For example,an employee whose address has changed and whose new address is not yet known should be retained with a null address.If employee tuples have a composite attribute dependents ,and a particular employee has no dependents,then that tuple’s dependents attribute should be given a null value.2.11Consider the following relational schemaemployee (empno ,name ,office ,age )books (isbn ,title ,authors ,publishe r )loan (empno ,isbn ,date )Write the following queries in relational algebra.a.Find the names of employees who have borrowed a book published by McGraw-Hill.b.Find the names of employees who have borrowed all books published byMcGraw-Hill.c.Find the names of employees who have borrowed more than five different books published by McGraw-Hill.d.For each publisher,find the names of employees who have borrowed morethan five books of that publisher.Answer:No answerExercises3.8Consider the insurance database of Figure 3.11,where the primary keys are un-derlined.Construct the following SQL queries for this relational database.a.Find the number of accidents in which the cars belonging to “John Smith ”were involved.b.Update the damage amount for the car with license number “AABB2000”in the accident with report number “AR2197”to $3000.Answer:Note:The participated relation relates drivers,cars,and accidents.a.SQL query:selectcount (distinct *)fromaccident where exists(select *from participated,personwhere participated.driver id =person.driver idand =’John Smith’and accident.report number =participated.report number )b.SQL query:update participatedset damage amount =3000where report number =“AR2197”and driver id in(select driver idfrom ownswhere license =“AABB2000”)11C H A P T E R312Chapter3SQLperson(driver id,name,address)car(license,model,year)accident(report number,date,location)owns(driver id,license)participated(driver id,car,report number,damage amount)Figure3.11.Insurance database.employee(employee name,street,city)works(employee name,company name,salary)company(company name,city)manages(employee name,manager name)Figure3.12.Employee database.3.9Consider the employee database of Figure3.12,where the primary keys are un-derlined.Give an expression in SQL for each of the following queries.a.Find the names of all employees who work for First Bank Corporation.b.Find all employees in the database who live in the same cities as the com-panies for which they work.c.Find all employees in the database who live in the same cities and on thesame streets as do their managers.d.Find all employees who earn more than the average salary of all employeesof their company.e.Find the company that has the smallest payroll.Answer:a.Find the names of all employees who work for First Bank Corporation.select employee namefrom workswhere company name=’First Bank Corporation’b.Find all employees in the database who live in the same cities as the com-panies for which they work.select e.employee namefrom employee e,works w,company cwhere e.employee name=w.employee name and e.city=c.city andpany name=pany namec.Find all employees in the database who live in the same cities and on thesame streets as do their managers.select P.employee namefrom employee P,employee R,manages Mwhere P.employee name=M.employee name andM.manager name=R.employee name andP.street=R.street and P.city=R.cityExercises13d.Find all employees who earn more than the average salary of all employeesof their company.The following solution assumes that all people work for at most one com-pany.select employee namefrom works Twhere salary>(select avg(salary)from works Swhere pany name=pany name)e.Find the company that has the smallest payroll.select company namefrom worksgroup by company namehaving sum(salary)<=all(select sum(salary)from worksgroup by company name)3.10Consider the relational database of Figure3.12.Give an expression in SQL foreach of the following queries.a.Give all employees of First Bank Corporation a10percent raise.b.Give all managers of First Bank Corporation a10percent raise.c.Delete all tuples in the works relation for employees of Small Bank Corpora-tion.Answer:a.Give all employees of First Bank Corporation a10-percent raise.(the solu-tion assumes that each person works for at most one company.)update worksset salary=salary*1.1where company name=’First Bank Corporation’b.Give all managers of First Bank Corporation a10-percent raise.update worksset salary=salary*1.1where employee name in(select manager namefrom manages)and company name=’First Bank Corporation’c.Delete all tuples in the works relation for employees of Small Bank Corpora-tion.delete workswhere company name=’Small Bank Corporation’3.11Let the following relation schemas be given:14Chapter3SQLR=(A,B,C)S=(D,E,F)Let relations r(R)and s(S)be given.Give an expression in SQL that is equivalentto each of the following queries.a.ΠA(r)b.σB=17(r)c.r×sd.ΠA,F(σC=D(r×s))Answer:a.ΠA(r)select distinct Afrom rb.σB=17(r)select*from rwhere B=17c.r×sselect distinct*from r,sd.ΠA,F(σC=D(r×s))select distinct A,Ffrom r,swhere C=D3.12Let R=(A,B,C),and let r1and r2both be relations on schema R.Give anexpression in SQL that is equivalent to each of the following queries.a.r1∪r2b.r1∩r2c.r1−r2d.ΠAB(r1)1ΠBC(r2)Answer:a.r1∪r2(select*from r1)union(select*from r2)b.r1∩r2We can write this using the intersect operation,which is the preferred approach,but for variety we present an solution using a nested subquery.Exercises15select*from r1where(A,B,C)in(select*from r2)c.r1−r2select∗from r1where(A,B,C)not in(select∗from r2)This can also be solved using the except clause.d.ΠAB(r1)1ΠBC(r2)select r1.A,r2.B,r3.Cfrom r1,r2where r1.B=r2.B3.13Show that,in SQL,<>all is identical to not in.Answer:Let the set S denote the result of an SQL subquery.We compare(x<>all S)with(x not in S).If a particular value x1satisfies(x1<>all S)then for all elements y of S x1=y.Thus x1is not a member of S and must satisfy(x1not in S).Similarly,suppose there is a particular value x2which satisfies(x2not inS).It cannot be equal to any element w belonging to S,and hence(x2<>all S) will be satisfied.Therefore the two expressions are equivalent.3.14Consider the relational database of ing SQL,define a view con-sisting of manager name and the average salary of all employees who work for that manager.Explain why the database system should not allow updates to be expressed in terms of this view.Answer:create view salinfo asselect manager name,avg(salary)from manages m,works wwhere m.employee name=w.employee namegroup by manager nameUpdates should not be allowed in this view because there is no way to de-termine how to change the underlying data.For example,suppose the request is“change the average salary of employees working for Smith to$200”.Should everybody who works for Smith have their salary changed to$200?Or should thefirst(or more,if necessary)employee found who works for Smith have their salary adjusted so that the average is$200?Neither approach really makes sense.3.15Write an SQL query,without using a with clause,tofind all branches wherethe total account deposit is less than the average total account deposit at allbranches,16Chapter3SQLing a nested query in the from clauser.ing a nested query in a having clause.Answer:We output the branch names along with the total account deposit atthe branch.ing a nested query in the from clauser.select branch name,tot balancefrom(select branch name,sum(balance)from accountgroup by branch name)as branch total(branch name,tot balance)where tot balance¡(select avg(tot balance)from(select branch name,sum(balance)from accountgroup by branch name)as branch total(branch name,tot balance))ing a nested query in a having clause.select branch name,sum(balance)from accountgroup by branch namehaving sum(balance)¡(select avg(tot balance)from(select branch name,sum(balance)from accountgroup by branch name)as branch total(branch name,tot balance))3.16List two reasons why null values might be introduced into the database.Answer:No Answer3.17Show how to express the coalesce operation from Exercise3.4using the caseoperation.Answer:No Answer.3.18Give an SQL schema definition for the employee database of Figure3.12.Choosean appropriate domain for each attribute and an appropriate primary key foreach relation schema.Answer:create domain company names char(20)create domain city names char(30)create domain person names char(20)create table employeeExercises17 (employee name person names,street char(30),city city names,primary key(employee name))create table works(employee name person names,company name company names,salary numeric(8,2),primary key(employee name))create table company(company name company names,city city names,primary key(company name))create table manages(employee name person names,manager name person names,primary key(employee name))3.19Using the relations of our sample bank database,write SQL expressions to definethe following views:a.A view containing the account numbers and customer names(but not thebalances)for all accounts at the Deer Park branch.b.A view containing the names and addresses of all customers who have anaccount with the bank,but do not have a loan.c.A view containing the name and average account balance of every customerof the Rock Ridge branch.Answer:No Answer.3.20For each of the views that you defined in Exercise3.19,explain how updateswould be performed(if they should be allowed at all).Answer:No Answer.3.21Consider the following relational schemaemployee(empno,name,office,age)books(isbn,title,authors,publisher)loan(empno,isbn,date)Write the following queries in SQL.a.Print the names of employees who have borrowed any book published byMcGraw-Hill.18Chapter3SQLb.Print the names of employees who have borrowed all books published byMcGraw-Hill.c.For each publisher,print the names of employees who have borrowed morethanfive books of that publisher.Answer:No Answer.3.22Consider the relational schemastudent(student id,student name)registered(student id,course id)Write an SQL query to list the student-id and name of each student along withthe total number of courses that the student is registered for.Students who arenot registered for any course must also be listed,with the number of registeredcourses shown as0.Answer:No Answer.3.23Suppose that we have a relation marks(student id,score).Write an SQL query tofind the dense rank of each student.That is,all students with the top mark get arank of1,those with the next highest mark get a rank of2,and so on.Hint:Splitthe task into parts,using the with clause.Answer:No Answer.C H A P T E R4Exercises4.7Referential-integrity constraints as defined in this chapter involve exactly tworelations.Consider a database that includes the following relations:salaried-worker(name,office,phone,salary)hourly-worker(name,hourly-wage)address(name,street,city)Suppose that we wish to require that every name that appears in address appear in either salaried-worker or hourly-worker,but not necessarily in both.a.Propose a syntax for expressing such constraints.b.Discuss the actions that the system must take to enforce a constraint of thisform.Answer:a.For simplicity,we present a variant of the SQL syntax.As part of the createtable expression for address we includeforeign key(name)references salaried-worker or hourly-workerb.To enforce this constraint,whenever a tuple is inserted into the address rela-tion,a lookup on the name value must be made on the salaried-worker relationand(if that lookup failed)on the hourly-worker relation(or vice-versa).4.8Write a Java function using JDBC metadata features that takes a ResultSet asan input parameter,and prints out the result in tabular form,with appropriate names as column headings.Answer:No Answer.1920Chapter4Advanced SQL4.9Write a Java function using JDBC metadata features that prints a list of all re-lations in the database,displaying for each relation the names and types of itsattributes.Answer:No Answer.4.10Consider an employee database with two relationsemployee(employee-name,street,city)works(employee-name,company-name,salary)where the primary keys are underlined.Write a query tofind companies whoseemployees earn a higher salary,on average,than the average salary at First BankCorporation.ing SQL functions as appropriate.b.Without using SQL functions.Answer:a.create function avg-salary(cname varchar(15))returns integerdeclare result integer;select avg(salary)into resultfrom workswhere pany-name=cnamereturn result;endselect company-namefrom workswhere avg-salary(company-name)>avg-salary(”First Bank Corporation”)b.select company-namefrom worksgroup by company-namehaving avg(salary)>(select avg(salary)from workswhere company-name=”First Bank Corporation”)4.11Rewrite the query in Section4.6.1that returns the name,street and city of allcustomers with more than one account,using the with clause instead of using afunction call.Answer:No Answer.4.12Compare the use of embedded SQL with the use in SQL of functions defined ina general-purpose programming language.Under what circumstances wouldyou use each of these features?Answer:SQL functions are primarily a mechanism for extending the powerof SQL to handle attributes of complex data types(like images),or to performcomplex and non-standard operations.Embedded SQL is useful when imper-ative actions like displaying results and interacting with the user are needed.Exercises21 These cannot be done conveniently in an SQL only environment.Embedded SQL can be used instead of SQL functions by retrieving data and then perform-ing the function’s operations on the SQL result.However a drawback is that a lot of query-evaluation functionality may end up getting repeated in the host language code.4.13Modify the recursive query in Figure4.14to define a relationempl depth(employee name,manager name,depth)where the attribute depth indicates how many levels of intermediate managers are there between the employee and the manager.Employees who are directly under a manager would have a depth of0.Answer:No Answer.4.14Consider the relational schemapart(part id,name,cost)subpart(part id,subpart id,count)A tuple(p1,p2,3)in the subpart relation denotes that the part with part-id p2is adirect subpart of the part with part-id p1,and p1has3copies of p2Note that p2 may itself have further subparts.Write a recursive SQL query that outputs the names of all subparts of the part with part-id“P-100”.Answer:No Answer.4.15Consider again the relational schema from Exercise4.14.Write a JDBC functionusing non-recursive SQL tofind the total cost of part“P-100”,including the costs of all its subparts.Be sure to take into account the fact that a part may have multiple occurrences of a subpart.You may use recursion in Java if you wish.Answer:No Answer.7.22Using the functional dependencies of Practice Exercise7.6,compute B+.Answer:Computing B+by the algorithm in Figure7.9we start with result= {B}.Considering FDs of the formβ→γin F,wefind that the only depen-dencies satisfyingβ⊆result are B→B and B→D.Therefore result= {B,D}.No more dependencies in F apply now.Therefore B+={B,D}7.23Show that the following decomposition of the schema R of Practice Exercise7.1is not a lossless-join decomposition:(A,B,C)(C,D,E).Hint:Give an example of a relation r on schema R such thatΠA,B,C(r)1ΠC,D,E(r)=rAnswer:Following the hint,use the following example of r:A B C D Ea1b1c1d1e1a2b2c1d2e2With R1=(A,B,C),R2=(C,D,E):a.ΠR1(r)would be:A B Ca1b1c1a2b2c1b.ΠR2(r)would be:C D Ec1d1e1c1d2e2c.ΠR1(r)1ΠR2(r)would be: A B C D Ea1b1c1d1e1a1b1c1d2e2a2b2c1d1e1a2b2c1d2e2Clearly,ΠR1(r)1ΠR2(r)=r.Therefore,this is a lossy join.Exercises6.14Explain the distinctions among the terms primary key,candidate key,and su-perkey.Answer:A superkey is a set of one or more attributes that,taken collectively,al-lows us to identify uniquely an entity in the entity set.A superkey may contain extraneous attributes.If K is a superkey,then so is any superset of K.A superkey for which no proper subset is also a superkey is called a candidate key.It is pos-sible that several distinct sets of attributes could serve as candidate keys.The primary key is one of the candidate keys that is chosen by the database designer as the principal means of identifying entities within an entity set.6.15Construct an E-R diagram for a hospital with a set of patients and a set of medi-cal doctors.Associate with each patient a log of the various tests and examina-tions conducted.Answer:See Figure6.16.16Construct appropriate tables for each of the E-R diagrams in Practice Exercises6.1and6.2.Answer:a.Car insurance tables:person(driver-id,name,address)car(license,year,model)accident(report-number,date,location)participated(driver-id,license,report-number,damage-amount)b.Hospital tables:33Figure6.1E-R diagram for a hospital.patients(patient-id,name,insurance,date-admitted,date-checked-out)doctors(doctor-id,name,specialization)test(testid,testname,date,time,result)doctor-patient(patient-id,doctor-id)test-log(testid,patient-id)performed-by(testid,doctor-id)c.University registrar’s tables:student(student-id,name,program)course(courseno,title,syllabus,credits)course-offering(courseno,secno,year,semester,time,room)instructor(instructor-id,name,dept,title)enrols(student-id,courseno,secno,semester,year,grade)teaches(courseno,secno,semester,year,instructor-id)requires(maincourse,prerequisite)。
12章-数据库管理系统-数据库系统概论(第五版)
进程间总的通信开销上升
操作系统的负担增大，空间、时间效率不高
DBMS必须设立并维护若干后台进程，增加了进程切换
要访问的数据不在内存时会造成性能问题
临界区问题(Critical Section)
❖ 适用情况
用户数不庞大(非OLTP应用)：Oracle 7之前版本, Ingres,
Informix早期版本
12.2.1 N方案：DBMS与应用程序相融合的方案
❖ 优点
没有进程切换开销 实现比较简单
❖ 缺点
内存的需求量比较大：多DBMS副本 代码冗余使系统性能下降
❖ 适用情况
用户数少的小型DBMS
An Introduction to Database System
12.2 DBMS进程结构和多线索机制
An Introduction to Database System
12.2.2 2N方案：一个DBMS进程对应一个用户进程
❖ 解决N方案中DBMS代码段在内存中不能被共享
应用程序与DBMS副本分开 2N方案
❖ 一用户一进程（Shadow进程）
N个用户进程---N个DBMS进程（共2N个进程）
An Introduction to Database System
12.2.1 N方案：DBMS与应用程序相融合的方案 12.2.2 2N方案：一个DBMS进程对应一个用户进程 12.2.3 N+1方案：一个DBMS进程对应所有用户进程 12.2.4 N+M方案：M个DBMS进程对应N个用户进程 12.2.5 多线索(Multi_Threaded) DBMS的概念
12.2.3 N+1方案：一个DBMS进程对应所有用户进程
❖ 优点
数据库系统概念(第五版)Abraham Silberschatz著,CH10
Database System Concepts - 5th Edition, Aug 22, 2005.
10.2
©Silberschatz, Korth and Sudarshan
Introduction
XML: Extensible Markup Language Defined by the WWW Consortium (W3C) Derived from SGML (Standard Generalized Markup Language), but simpler to use than SGML Documents have tags giving extra information about sections of the document E.g. <title> XML </title> <slide> Introduction …</slide> Extensible, unlike HTML Users can add new tags, and separately specify how the tag should be handled for display
Chapter 10: XML
Database System Concepts
©Silberschatz, Korth and Sudarshan See for conditions on re-use
XML
Structure of XML Data XML Document Schema Querying and Transformation Application Program Interfaces to XML Storage of XML Data XML Applications
数据库系统概论王珊第5版第五版教材课后习题答案详解pdf
内容说明王珊《数据库系统概论》（第5版）是我国高校采用较多的权威教材之一，无论指定参考书是否为该教材，对该教材的课后习题加以练习和掌握，非常有助于考生的复习和备考。
本册以王珊《数据库系统概论》（第5版）为依据，对课后习题进行了详细的分析与解答，并对相关重要知识点进行了延伸和归纳。
明立考试（公众号）本文档的内容参考了部分网络资料及图书资料，是出于传递更多信息之目的，并不代表故意侵犯原作者或出版人的版权和利益，如果原作者或出版人对本文档有任何异议，请与作者联系，会在第一时间处理！内容说明 (1)声明 (2)目录 (3)第1章绪论 (5)课后习题 (5)参考答案 (6)第2章关系数据库 (12)课后习题 (12)参考答案 (14)第3章关系数据库标准语言SQL (18)课后习题 (18)参考答案 (19)第4章数据库安全性 (22)课后习题 (22)参考答案 (23)第5章数据库完整性 (26)课后习题 (26)参考答案 (27)第6章关系数据理论 (29)课后习题 (29)参考答案 (31)第7章数据库设计 (34)课后习题 (34)参考答案 (35)第8章数据库编程 (39)课后习题 (39)参考答案 (40)第9章关系查询处理和查询优化 (42)课后习题 (42)参考答案 (43)第10章数据库恢复技术 (46)课后习题 (46)参考答案 (48)第11章并发控制 (51)课后习题 (51)参考答案 (53)第12章数据库管理系统 (59)课后习题 (59)参考答案 (60)第13章数据库技术发展概述 (63)课后习题 (63)参考答案 (64)第14章大数据管理 (65)课后习题 (65)参考答案 (66)第15章内存数据库系统 (68)课后习题 (68)参考答案 (69)第16章数据仓库与联机分析处理技术 (71)课后习题 (71)参考答案 (72)第1章绪论课后习题1．试述数据、数据库、数据库管理系统、数据库系统的概念。
第12章 数据库应用程序开发
重点 1.数据库系统和ODBC 1.数据库系统和ODBC 数据库系统和 2.CRecordSet的应用 2.CRecordSet的应用 3.示例系统中数据库操作类的设计 3.示例系统中数据库操作类的设计
2009年1月
重庆理工大学计算机科学与技术系
共10页第1页
数据库应用程序是在数据库管理系统(DBMS)的支持下对数据库中的数据进 数据库应用程序是在数据库管理系统(DBMS)的支持下对数据库中的数据进 (DBMS) 行加工、处理的程序，MFC提供了两种支持 ODBC和DAO。 提供了两种支持： 行加工、处理的程序，MFC提供了两种支持：ODBC和DAO。
12.1 数据库系统的基本概念
• 数据库系统由数据库、数据库管理系统和数据库应用系 数据库系统由数据库、 部分组成，如右图： 统3部分组成，如右图： • 数据库是数据的集合，由一个或多个表组成，一般将表 数据库是数据的集合，由一个或多个表组成， 中的一行称作记录(record)或行(row) (record)或行(row)， 中的一行称作记录(record)或行(row)，将表的列称作字 (field)或列(column)； 或列(column) 段(field)或列(column)；
2009年1月 重庆理工大学计算机科学与技术系 共10页第4页
CDatabase类 12.4 CDatabase类
• 先构造一个CDatabase对象，然后调用Open成员函数： 先构造一个CDatabase对象，然后调用Open成员函数： CDatabase对象 Open成员函数
virtual BOOL Open(LPCTSTR lpszDSN, BOOL bExclusive = FALSE, BOOL bReadOnly = FALSE, LPCTSTR lpszConnect = "ODBC;", BOOL bUseCursorLib = TRUE);throw (CDBException, CMemoryException);
12章-数据库管理系统-数据库系统概论(第五版)
第12章数据库管理系统本章进一步阐述数据库管理系统的基本功能、系统结构及主要实现技术。
本章不是针对数据库管理系统的设计人员写的，而是面向数据库管理员和数据库应用系统开发人员的，目的是使他们从宏观和总体的角度掌握数据库管理系统的基本概念和基本原理，以便更好地使用和维护数据库管理系统。
12.1数据库管理系统的基本功能数据库管理系统已经发展成为继操作系统之后最复杂的系统软件。
前面已讲过，数据库管理系统主要是实现对共享数据有效的组织、存储、管理和存取。
围绕数据，数据库管理系统应具有如下基本功能。
1.数据库定义和创建创建数据库主要是用数据定义语言定义和创建数据库模式、外模式、内模式等数据库对象。
在关系数据库中就是建立数据库(或模式)、表、视图、索引等，还有创建用户、安全保密定义(如用户口令、级别、角色、存取权限)、数据库的完整性定义。
这些定义存储在数据字典(亦称为系统日录)中，是数据库管理系统运行的基本依据。
2.数据组织、存储和管理数据库管理系统要分类组织、存储和管理各种数据，包括数据字典、用户数据、存取路径等。
要确定以何种文件结构和存取方式在存储器上组织这些数据，以及如何实现数据之间的联系。
数据组织和存储的基本目标是提高存储空间利用率和方便存取，提供多种存取方法(如索引查找、hash查找、顺序查找等)以提高存取效率。
3.数据存取数据库管理系统提供用户对数据的操作功能，实现对数据库数据的检索、插入、修改和删除。
一个好的关系数据库管理系统应该提供功能强且易学易用的数据操纵语言、方便的操作方式和较高的数据存取效率。
数据操纵语言有两类：宿主型语言和自立(独立)型语言。
4.数据库事务管理和运行管理这是指数据库管理系统的运行控制和管理功能，包括多用户环境下的事务管理功能和安全性、完整性控制功能；数据库恢复、并发控制和死锁检测（或死锁防止)、安全性检查和存取控制、完整性检查和执行、运行日志的组织管理等。
这些功能保证了数据库系统的正常运行，保证了事务的ACID特性。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
数据库系统概念第五版12章C H A P T E R12Indexing and HashingSolutions to Practice Exercises12.1Reasons for not keeping several search indices include:a.Every index requires additional CPU time and disk I/O overhead duringinserts and deletions.b.Indices on non-primary keys might have to be changed on updates,al-though an index on the primary key might not(this is because updatestypically do not modify the primary key attributes).c.Each extra index requires additional storage space.d.For queries which involve conditions on several search keys,ef?ciencymight not be bad even if only some of the keys have indices on them.Therefore database performance is improved less by adding indices whenmany indices already exist.12.2In general,it is not possible to have two primary indices on the same relationfor different keys because the tuples in a relation would have to be stored in different order to have same values stored together.We could accomplish this by storing the relation twice and duplicating all values,but for a centralized system,this is not ef?cient.5354Chapter12Indexing and Hashing12.3The following were generated by inserting values into the B+-tree in ascendingorder.A node(other than the root)was never allowed to have fewer than n/2values/pointers.a.b.c.12.4?With structure12.3.a:9:InsertInsertExercises 55Delete 23:Delete19:With structure 12.3.b:Insert 9:Insert 10:56Chapter 12Indexing and Hashing Insert 8:Delete23:With structure12.3.c:Insert 9:Insert 10:Insert8:Delete 23:Exercises57 Delete19:12.5If there are K search-key values and m?1siblings are involved in the redistri-bution,the expected height of the tree is:log (m?1)n/m (K)12.6The algorithm for insertion into a B-tree is:Locate the leaf node into which the new key-pointer pairshould be inserted.If there is space remaining in that leaf node,perform the insertion at the correct location,and the task is over.Otherwise insert the key-pointer pair conceptu-ally into the correct location in the leaf node,and then split it along the middle.The middle key-pointer pair does not go into either of the resultant nodes of the split operation.Instead it is inserted into the parent node,along with the tree pointer to the new child.If there is no space in the parent,a similar proce-dure is repeated.The deletion algorithm is:Locate the key value to be deleted,in the B-tree.a.If it is found in a leaf node,delete the key-pointer pair,and the recordfrom the?le.If the leaf node contains less than n/2 ?1entries as a resultof this deletion,it is either merged with its siblings,or some entries areredistributed to it.Merging would imply a deletion,whereas redistributionwould imply change(s)in the parent node’s entries.The deletions mayripple upto the root of the B-tree.b.If the key value is found in an internal node of the B-tree,replace it andits record pointer by the smallest key value in the subtree immediately toits right and the corresponding record pointer.Delete the actual record inthe database?le.Then delete that smallest key value-pointer pair from thesubtree.This deletion may cause further rippling deletions till the root ofthe B-tree.Below are the B-trees we will get after insertion of the given key values.We assume that leaf and non-leaf nodes hold the same number of search key values.58Chapter12Indexing and Hashinga.b.c.Exercises 5912.7Extendable hash structure000 001 010 011 100101 110 11112.8 a.Delete 11:From the answer to Exercise 12.7,change the third bucket to: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 entries instead of eight.For the purpose of this answer, we do not do the coalescing.b.Delete 31:From the answer to 12.7,change the last bucketto:c.Insert 1:From the answer to 12.7,change the ?rst bucket to:d.Insert 15:From the answer to 12.7,change the last bucket to:60Chapter12Indexing and Hashing12.9Let i denote the number of bits of the hash value used in the hash table.Letbsize denote the maximum capacity of each bucket.delete(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);endNote that we can only merge two buckets at a time.The common hash pre?x of the resultant bucket will have length one less than the two buckets merged.Hence we look at the buddy bucket of bucket j differing from it only at the lastbit.If the common hash pre?x of this bucket is not i j,then this implies that thebuddy bucket has been further split and merge is not possible.When merge is successful,further merging may be possible,which is han-dled by a recursive call to coalesce at the end of the function.12.10If the hash table is currently using i bits of the hash value,then maintain acount of buckets for which the length of common hash pre?x is exactly i.Consider a bucket j with length of common hash pre?x i j.If the bucket is being split,and i j is equal to i,then reset the count to1.If the bucket is beingExercises61 split and i j is one less that i,then increase the count by1.It the bucket if being coalesced,and i j is equal to i then decrease the count by1.If the count becomes 0,then the bucket address table can be reduced in size 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 the re-duction,we have to keep an array of counts,i.e.a count for each value of com-mon hash pre?x.The array has to be updated in a similar fashion.The bucket address table can be reduced if the i th entry of the array is0,where i isthe number of bits the table is using.Since bucket table reduction is an expensive operation,it is not always advisable to reduce the table.It should be reduced only when suf?cient number of entries at the end of count array become0. 12.11We reproduce the account relation of Figure12.25below.A-217Brighton750A-101Downtown500A-110Downtown600A-215Mianus700A-102Perryridge400A-201Perryridge900A-218Perryridge700A-222Redwood700A-305Round Hill350Bitmaps for branch nameBrighton100000000Downtown011000000Mianus000100000Perryridge000011100Redwood000000010Round hill000000001Bitmaps for balanceL1000000000L2000010001L3011100110L4100001000where,level L1is below250,level L2is from250to below500,L3from500to below750and level L4is above750.62Chapter12Indexing and HashingTo?nd all accounts in Downtown with a balance of500ormore,we?nd the union of bitmaps for levels L3and L4and then intersect it with the bitmap forDowntown.Downtown011000000L3011100110L4100001000L3∪L4111101110Downtown011000000Downtown∩(L3∪L4)011000000Thus,the required tuples are A-101and A-110.12.12No answer。