数据库系统概念课后习题答案SolutionstoPracticeExercises9s

合集下载

数据库系统概念试题库及答案

数据库系统概念试题库及答案1. 什么是数据库系统？数据库系统是一种用于存储、管理和检索数据的软件系统。

它由数据库管理系统（DBMS）和相关的应用程序组成，用于有效地组织和管理大量数据，并提供数据访问和查询功能。

2. 数据库系统的主要组成部分有哪些？数据库系统主要由以下几个组成部分构成：- 数据库：用于存储数据的集合，可以包含多个表或文件。

- 数据库管理系统（DBMS）：负责管理数据库的软件系统，提供数据访问、查询、修改和控制等功能。

- 数据库应用程序：基于数据库的应用程序，通过DBMS与数据库交互，实现数据的增删改查等操作。

3. 数据库系统的优势有哪些？数据库系统具有以下几个优势：- 数据共享：多个用户可以同时访问和共享数据库中的数据，提高了工作效率和数据的一致性。

- 数据独立性：数据库系统实现了数据与应用程序的分离，使得数据的结构和存储方式可以独立于应用程序进行修改和调整。

- 数据一致性：通过数据库的事务处理和完整性约束等机制，确保数据的一致性和完整性。

- 数据安全性：数据库系统提供了用户认证、权限控制和数据加密等功能，保护数据的安全性和机密性。

4. 数据库系统的基本概念有哪些？数据库系统涉及的基本概念包括：- 数据模型：描述数据的组织方式和数据之间的关系，常见的数据模型有关系模型、层次模型和网络模型等。

- 数据库模式：数据库模式定义了数据库中表、字段和关系的结构，描述了数据的逻辑结构。

- 数据库实例：数据库实例是数据库模式的一个具体实例，包含了实际存储的数据。

- 数据库操作：包括数据的增加、删除、修改和查询等操作，用于对数据库中的数据进行管理和访问。

5. 数据库系统的查询语言有哪些？数据库系统常用的查询语言有：- 结构化查询语言（SQL）：SQL是一种用于操作和查询关系型数据库的标准语言，具有简单易学和广泛应用的特点。

- 查询操作符：数据库系统支持多种查询操作符，如选择、投影、连接和聚合等，用于实现复杂的数据查询和分析。

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

C H A P T E R22Object-Based DatabasesPractice Exercises22.1A car-rental company maintains a database for all vehicles in its cur-rentﬂeet.For all vehicles,it includes the vehicle identiﬁcation number,license number,manufacturer,model,date of purchase,and color.Spe-cial data are included for certain types of vehicles:•Trucks:cargo capacity.•Sports cars:horsepower,renter age requirement.•Vans:number of passengers.•Off-road vehicles:ground clearance,drivetrain(four-or two-wheel drive).Construct an SQL schema deﬁnition for this e inheritancewhere appropriate.Answer:For this problem,we use table inheritance.We assume thatMyDate,Color and DriveTrainType are pre-deﬁned types.create type Vehicle(vehicle id integer,license number char(15),manufacturer char(30),model char(30),purchase date MyDate,color Color)create table vehicle of type Vehiclecreate table truck(cargo capacity integer)under vehiclecreate table sportsCar12Chapter22Object-Based Databases(horsepower integerrenter age requirement integer)under vehiclecreate table van(num passengers integer)under vehiclecreate table offRoadVehicle(ground clearance realdriveTrain DriveTrainType)under vehicle22.2Consider a database schema with a relation Emp whose attributes areas shown below,with types speciﬁed for multivalued attributes.Emp=(ename,ChildrenSet multiset(Children),SkillSet multiset(Skills))Children=(name,birthday)Skills=(type,ExamSet setof(Exams))Exams=(year,city)a.Deﬁne the above schema in SQL,with appropriate types for eachattribute.ing the above schema,write the following queries in SQL.i.Find the names of all employees who have a child born on orafter January1,2000.ii.Find those employees who took an examination for the skilltype“typing”in the city“Dayton”.iii.List all skill types in the relation Emp.Answer:a.No Answer.b.Queries in SQL.i.Program:select enamefrom emp as e,e.ChildrenSet as cwhere’March’in(select birthday.monthfrom c)ii.Program:Practice Exercises3select e.enamefrom emp as e,e.SkillSet as s,s.ExamSet as xwhere s.type=’typing’and x.city=’Dayton’iii.Program:select distinct s.typefrom emp as e,e.SkillSet as s22.3Consider the E-R diagram in Figure22.5,which contains composite,multivalued,and derived attributes.a.Give an SQL schema deﬁnition corresponding to the E-R diagram.b.Give constructors for each of the structured types deﬁned above.Answer:a.The corresponding SQL:1999schema deﬁnition is given below.Note that the derived attribute age has been translated into amethod.create type Name(ﬁrst name varchar(15),middle initial char,last name varchar(15))create type Street(street name varchar(15),street number varchar(4),apartment number varchar(7))create type Address(street Street,city varchar(15),state varchar(15),zip code char(6))create table customer(name Name,customer id varchar(10),address Adress,phones char(7)array[10],dob date)method integer age()b.create function Name(f varchar(15),m char,l varchar(15))returns Namebeginsetﬁrst name=f;set middle initial=m;set last name=l;endcreate function Street(sname varchar(15),sno varchar(4),ano varchar(7))4Chapter22Object-Based Databasesreturns Streetbeginset street name=sname;set street number=sno;set apartment number=ano;endcreate function Address(s Street,c varchar(15),sta varchar(15),zip varchar(6))returns Addressbeginset street=s;set city=c;set state=sta;set zip code=zip;end22.4Consider the relational schema shown in Figure22.6.a.Give a schema deﬁnition in SQL corresponding to the relationalschema,but using references to express foreign-key relationships.b.Write each of the queries given in Exercise6.13on the aboveschema,using SQL.Answer:a.The schema deﬁnition is given below.Note that backward ref-erences can be addedbut they are not so important as in OODBSbecause queries can be written in SQL and joins can take care ofintegrity constraints.create type Employee(person name varchar(30),street varchar(15),city varchar(15))create type Company(company name varchar(15),(city varchar(15))create table employee of Employeecreate table company of Companycreate type Works(person ref(Employee)scope employee,comp ref(Company)scope company,salary int)create table works of Workscreate type Manages(person ref(Employee)scope employee,(manager ref(Employee)scope employee)create table manages of Managesb.i.select comp−>namePractice Exercises5from worksgroup by comphaving count(person)≥all(select count(person)from worksgroup by comp)ii.select comp−>namefrom worksgroup by comphaving sum(salary)≤all(select sum(salary)from worksgroup by comp)iii.select comp−>namefrom worksgroup by comphaving avg(salary)>(select avg(salary)from workswhere comp−>company name="First Bank Corporation")22.5Suppose that you have been hired as a consultant to choose a databasesystem for your client’s application.For each of the following appli-cations,state what type of database system(relational,persistent pro-gramming language–based OODB,object relational;do not specify acommercial product)you would recommend.Justify your recommen-dation.a.A computer-aided design system for a manufacturer of airplanes.b.A system to track contributions made to candidates for publicofﬁce.c.An information system to support the making of movies.Answer:a.A computer-aided design system for a manufacturer of airplanes:An OODB system would be suitable for this.That is because CADrequires complex data types,and being computation oriented,CAD tools are typically used in a programming language envi-ronment needing to access the database.b.A system to track contributions made to candidates for publicofﬁce:A relational system would be apt for this,as data types are ex-pected to be simple,and a powerful querying mechanism is es-sential.c.An information system to support the making of movies:Here there will be extensive use of multimedia and other complexdata types.But queries are probably simple,and thus an objectrelational system is suitable.6Chapter22Object-Based Databases22.6How does the concept of an object in the object-oriented model differfrom the concept of an entity in the entity-relationship model?Answer:An entity is simply a collection of variables or data items.An object is an encapsulation of data as well as the methods(code)tooperate on the data.The data members of an object are directly visibleonly to its methods.The outside world can gain access to the object’sdata only by passing pre-deﬁned messages to it,and these messagesare implemented by the methods.。

数据库系统课后习题及答案

数据库系统课后习题及答案第1章绪论习题参考答案1、试述数据、数据库、数据库管理系统、数据库系统的概念。

（3、4、5页）答：描述事物的符号记录称为数据；数据库是长期储存在计算机内的、有组织的、可共享的数据集合；数据库管理系统是位于用户与操作系统之间的一层数据管理软件; 数据库系统是指在计算机系统中引入数据库后的系统，一般由数据库、数据库管理系统(及其开发工具)、应用系统、数据库管理员和用户构成。

2．使用数据库系统有什么好处?（12页）答：数据库系统使信息系统从以加工数据的程序为中心转向围绕共享的数据库为中心的阶段，这样既便于数据的集中管理，又有利于应用程序的研制和维护，提高了数据的利用率和相容性，提高了决策的可靠性。

3．试述文件系统与数据库系统的区别和联系。

（8、9、10页）答：1）数据结构化是数据库与文件系统的根本区别。

在文件系统中，相互独立的文件的记录内部是有结构的，管其记录内部已有了某些结构，但记录之间没有联系。

数据库系统实现整体数据的结构化，是数据库的主要特征之一。

2）在文件系统中，数据的最小存取单位是记录，粒度不能细到数据项。

而在数据库系统中，存取数据的方式也很灵活，可以存取数据库中的某一个数据项、一组数据项一个记录或或一组记录。

3）文件系统中的文件是为某一特定应用服务的，文件的逻辑结构对该应用程序来说是优化的，因此要想对现有的数据再增加一些新的应用会很困难，系统不容易扩充。

而在数据库系统中数据不再针对某一应用，而是面向全组织，具有整体的结构化。

5．试述数据库系统的特点。

（9、10、11页）答：数据结构化；数据的共享性高、冗余度低、易扩充；数据独立性高；数据由DBMS统一管理和控制。

6．数据库管理系统的主要功能有哪些? （4页）答：数据定义功能、数据操纵功能、数据库的运行管理、数据库的建立和维护功能。

7．试述数据模型的概念（13页）、数据模型的作用、数据模型的三个要素。

（14、15页）答：数据模型(Data Model)也是一种模型，它是现实世界数据特征的抽象。

数据库系统概念第六版课后习题部分答案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))。

《数据库系统概论》课后习题及参考标准答案

课后作业习题《数据库系统概论》课程部分习题及参考答案第一章绪论(教材 4１页）1．试述数据、数据库、数据库系统、数据库管理系统的概念。

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

数据的种类有文字、图形、图象、声音、正文等等。

数据与其语义是不可分的。

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

数据库中的数据按一定的数据模型组织、描述和储存,具有较小的冗余度、较高的数据独立性和易扩展性，并可为各种用户共享。

数据库系统：数据库系统( DBS）是指在计算机系统中引入数据库后的系统构成。

数据库系统由数据库、数据库管理系统（及其开发工具)、应用系统、数据库管理员构成。

数据库管理系统：数据库管理系统（DＢMS)是位于用户与操作系统之间的一层数据管理软件。

用于科学地组织和存储数据、高效地获取和维护数据。

DBMS主要功能包括数据定义功能、数据操纵功能、数据库的运行管理功能、数据库的建立和维护功能。

２．使用数据库系统有什么好处？使用数据库系统的好处是由数据库管理系统的特点或优点决定的。

使用数据库系统的好处很多,例如可以大大提高应用开发的效率，方便用户的使用,减轻数据库系统管理人员维护的负担等。

为什么有这些好处，可以结合第 5题来回答。

使用数据库系统可以大大提高应用开发的效率。

因为在数据库系统中应用程序不必考虑数据的定义、存储和数据存取的具体路径,这些工作都由ＤBMＳ来完成。

此外,当应用逻辑改变,数据的逻辑结构需要改变时，由于数据库系统提供了数据与程序之间的独立性。

数据逻辑结构的改变是 DBA的责任,开发人员不必修改应用程序,或者只需要修改很少的应用程序。

从而既简化了应用程序的编制,又大大减少了应用程序的维护和修改。

使用数据库系统可以减轻数据库系统管理人员维护系统的负担。

因为DBMS在数据库建立、运用和维护时对数据库进行统一的管理和控制,包括数据的完整性、安全性，多用户并发控制，故障恢复等等都由DＢＭS执行。

数据库系统基础教程(第二版)课后习题答案2

Database Systems: The Complete BookSolutions for Chapter 2Solutions for Section 2.1Exercise 2.1.1The E/R Diagram.Exercise 2.1.8(a)The E/R DiagramKobvxybzSolutions for Section 2.2Exercise 2.2.1The Addresses entity set is nothing but a single address, so we would prefer to make address an attribute of Customers. Were the bank to record several addresses for a customer, then it might make sense to have an Addresses entity set and make Lives-at a many-many relationship.The Acct-Sets entity set is useless. Each customer has a unique account set containing his or her accounts. However, relating customers directly to their accounts in a many-many relationship conveys the same information and eliminates the account-set concept altogether.Solutions for Section 2.3Exercise 2.3.1(a)Keys ssNo and number are appropriate for Customers and Accounts, respectively. Also, we think it does not make sense for an account to be related to zero customers, so we should round the edge connecting Owns to Customers. It does not seem inappropriate to have a customer with 0 accounts;they might be a borrower, for example, so we put no constraint on the connection from Owns to Accounts. Here is the The E/R Diagram，showing underlined keys andthe numerocity constraint.Exercise 2.3.2(b)If R is many-one from E1 to E2, then two tuples (e1,e2) and (f1,f2) of the relationship set for R must be the same if they agree on the key attributes for E1. To see why, surely e1 and f1 are the same. Because R is many-one from E1 to E2, e2 and f2 must also be the same. Thus, the pairs are the same.Solutions for Section 2.4Exercise 2.4.1Here is the The E/R Diagram.We have omitted attributes other than our choice for the key attributes of Students and Courses. Also omitted are names for the relationships. Attribute grade is not part of the key for Enrollments. The key for Enrollements is studID from Students and dept and number from Courses.Exercise 2.4.4bHere is the The E/R Diagram Again, we have omitted relationship names and attributes other than our choice for the key attributes. The key for Leagues is its own name; this entity set is not weak. The key for Teams is its own name plus the name of the league of which the team is a part, e.g., (Rangers, MLB) or (Rangers, NHL). The key for Players consists of the player's number and the key for the team on which he or she plays. Since the latter key is itself a pair consisting of team and league names, the key for players is the triple (number, teamName, leagueName). e.g., JeffGarcia is (5, 49ers, NFL).Database Systems: The Complete BookSolutions for Chapter 3Solutions for Section 3.1Exercise 3.1.2(a)We can order the three tuples in any of 3! = 6 ways. Also, the columns can be ordered in any of 3! = 6 ways. Thus, the number of presentations is 6*6 = 36.Solutions for Section 3.2Exercise 3.2.1Customers(ssNo, name, address, phone)Flights(number, day, aircraft)Bookings(ssNo, number, day, row, seat)Being a weak entity set, Bookings' relation has the keys for Customers and Flights and Bookings' own attributes.Notice that the relations obtained from the toCust and toFlt relationships are unnecessary. They are:toCust(ssNo, ssNo1, number, day)toFlt(ssNo, number, day, number1, day1)That is, for toCust, the key of Customers is paired with the key for Bookings. Since both include ssNo, this attribute is repeated with two different names, ssNo and ssNo1. A similar situation exists for toFlt.Exercise 3.2.3Ships(name, yearLaunched)SisterOf(name, sisterName)Solutions for Section 3.3Exercise 3.3.1Since Courses is weak, its key is number and the name of its department. We do not have arelation for GivenBy. In part (a), there is a relation for Courses and a relation for LabCourses that has only the key and the computer-allocation attribute. It looks like:Depts(name, chair)Courses(number, deptName, room)LabCourses(number, deptName, allocation)For part (b), LabCourses gets all the attributes of Courses, as:Depts(name, chair)Courses(number, deptName, room)LabCourses(number, deptName, room, allocation)And for (c), Courses and LabCourses are combined, as:Depts(name, chair)Courses(number, deptName, room, allocation)Exercise 3.3.4(a)There is one relation for each entity set, so the number of relations is e. The relation for the root entity set has a attributes, while the other relations, which must include the key attributes, have a+k attributes.Solutions for Section 3.4Exercise 3.4.2Surely ID is a key by itself. However, we think that the attributes x, y, and z together form another key. The reason is that at no time can two molecules occupy the same point.Exercise 3.4.4The key attributes are indicated by capitalization in the schema below:Customers(SSNO, name, address, phone)Flights(NUMBER, DAY, aircraft)Bookings(SSNO, NUMBER, DAY, row, seat)Exercise 3.4.6(a)The superkeys are any subset that contains A1. Thus, there are 2^{n-1} such subsets, since each of the n-1 attributes A2 through An may independently be chosen in or out.Solutions for Section 3.5Exercise 3.5.1(a)We could try inference rules to deduce new dependencies until we are satisfied we have them all.A more systematic way is to consider the closures of all 15 nonempty sets of attributes.For the single attributes we have A+ = A, B+ = B, C+ = ACD, and D+ = AD. Thus, the only new dependency we get with a single attribute on the left is C->A.Now consider pairs of attributes:AB+ = ABCD, so we get new dependency AB->D. AC+ = ACD, and AC->D is nontrivial. AD+ = AD, so nothing new. BC+ = ABCD, so we get BC->A, and BC->D. BD+ = ABCD, giving usBD->A and BD->C. CD+ = ACD, giving CD->A.For the triples of attributes, ACD+ = ACD, but the closures of the other sets are each ABCD. Thus, we get new dependencies ABC->D, ABD->C, and BCD->A.Since ABCD+ = ABCD, we get no new dependencies.The collection of 11 new dependencies mentioned above is: C->A, AB->D, AC->D, BC->A, BC->D, BD->A, BD->C, CD->A, ABC->D, ABD->C, and BCD->A.Exercise 3.5.1(b)From the analysis of closures above, we find that AB, BC, and BD are keys. All other sets either do not have ABCD as the closure or contain one of these three sets.Exercise 3.5.1(c)The superkeys are all those that contain one of those three keys. That is, a superkey that is not a key must contain B and more than one of A, C, and D. Thus, the (proper) superkeys are ABC, ABD, BCD, and ABCD.Exercise 3.5.3(a)We must compute the closure of A1A2...AnC. Since A1A2...An->B is a dependency, surely B is in this set, proving A1A2...AnC->B.Exercise 3.5.4(a)Consider the relationThis relation satisfies A->B but does not satisfy B->A.Exercise 3.5.8(a)If all sets of attributes are closed, then there cannot be any nontrivial functional dependenc ies. For suppose A1A2...An->B is a nontrivial dependency. Then A1A2...An+ contains B and thus A1A2...An is not closed.Exercise 3.5.10(a)We need to compute the closures of all subsets of {ABC}, although there is no need to think about the empty set or the set of all three attributes. Here are the calculations for the remaining six sets: A+ = AB+ = BC+ = ACEAB+ = ABCDEAC+ = ACEBC+ = ABCDEWe ignore D and E, so a basis for the resulting functional dependencies for ABC are: C->A and AB->C. Note that BC->A is true, but follows logically from C->A, and therefore may be omitted from our list.Solutions for Section 3.6Exercise 3.6.1(a)In the solution to Exercise 3.5.1 we found that there are 14 nontrivial dependencies, including the three given ones and 11 derived dependencies. These are: C->A, C->D, D->A, AB->D, AB-> C, AC->D, BC->A, BC->D, BD->A, BD->C, CD->A, ABC->D, ABD->C, and BCD->A.We also learned that the three keys were AB, BC, and BD. Thus, any dependency above that does not have one of these pairs on the left is a BCNF violation. These are: C->A, C->D, D->A, AC->D, and CD->A.One choice is to decompose using C->D. That gives us ABC and CD as decomposed relations. CD is surely in BCNF, since any two-attribute relation is. ABC is not in BCNF, since AB and BC are its only keys, but C->A is a dependency that holds in ABCD and therefore holds in ABC. We must further decompose ABC into AC and BC. Thus, the three relations of the decomposition are AC, BC, and CD.Since all attributes are in at least one key of ABCD, that relation is already in 3NF, and no decomposition is necessary.Exercise 3.6.1(b)(Revised 1/19/02) The only key is AB. Thus, B->C and B->D are both BCNF violations. The derived FD's BD->C and BC->D are also BCNF violations. However, any other nontrivial, derived FD will have A and B on the left, and therefore will contain a key.One possible BCNF decomposition is AB and BCD. It is obtained starting with any of the four violations mentioned above. AB is the only key for AB, and B is the only key for BCD.Since there is only one key for ABCD, the 3NF violations are the same, and so is the decomposition.Solutions for Section 3.7Exercise 3.7.1Since A->->B, and all the tuples have the same value for attribute A, we can pair the B-value from any tuple with the value of the remaining attribute C from any other tuple. Thus, we know that R must have at least the nine tuples of the form (a,b,c), where b is any of b1, b2, or b3, and c is any of c1, c2, or c3. That is, we can derive, using the definition of a multivalued dependency, that each of the tuples (a,b1,c2), (a,b1,c3), (a,b2,c1), (a,b2,c3), (a,b3,c1), and (a,b3,c2) are also in R.Exercise 3.7.2(a)First, people have unique Social Security numbers and unique birthdates. Thus, we expect the functional dependencies ssNo->name and ssNo->birthdate hold. The same applies to children, so we expect childSSNo->childname and childSSNo->childBirthdate. Finally, an automobile has a unique brand, so we expect autoSerialNo->autoMake.There are two multivalued dependencies that do not follow from these functional dependencies. First, the information about one child of a person is independent of other information about that person. That is, if a person with social security number s has a tuple with cn,cs,cb, then if there isany other tuple t for the same person, there will also be another tuple that agrees with t except that it has cn,cs,cb in its components for the child name, Social Security number, and birthdate. That is the multivalued dependencyssNo->->childSSNo childName childBirthdateSimilarly, an automobile serial number and make are independent of any of the other attributes, so we expect the multivalued dependencyssNo->->autoSerialNo autoMakeThe dependencies are summarized below:ssNo -> name birthdatechildSSNo -> childName childBirthdateautoSerialNo -> autoMakessNo ->-> childSSNo childName childBirthdatessNo ->-> autoSerialNo autoMakeExercise 3.7.2(b)We suggest the relation schemas{ssNo, name, birthdate}{ssNo, childSSNo}{childSSNo, childName childBirthdate}{ssNo, autoSerialNo}{autoSerialNo, autoMake}An initial decomposition based on the two multivalued dependencies would give us {ssNo, name, birthDate}{ssNo, childSSNo, childName, childBirthdate}{ssNo, autoSerialNo, autoMake}Functional dependencies force us to decompose the second and third of these.Exercise 3.7.3(a)Since there are no functional dependencies, the only key is all four attributes, ABCD. Thus, each of the nontrvial multivalued dependencies A->->B and A->->C violate 4NF. We must separate out the attributes of these dependencies, first decomposing into AB and ACD, and then decomposing the latter into AC and AD because A->->C is still a 4NF violation for ACD. The final set of relations are AB, AC, and AD.Exercise 3.7.7(a)Let W be the set of attributes not in X, Y, or Z. Consider two tuples xyzw and xy'z'w' in the relation R in question. Because X ->-> Y, we can swap the y's, so xy'zw and xyz'w' are in R. Because X ->-> Z, we can take the pair of tuples xyzw and xyz'w' and swap Z's to get xyz'w and xyzw'. Similarly, we can take the pair xy'z'w' and xy'zw and swap Z's to get xy'zw' and xy'z'w.In conclusion, we started with tuples xyzw and xy'z'w' and showed that xyzw' and xy'z'w must also be in the relation. That is exactly the statement of the MVD X ->-> Y-union-Z. Note that the above statements all make sense even if there are attributes in common among X, Y, and Z.Exercise 3.7.8(a)Consider a relation R with schema ABCD and the instance with four tuples abcd, abcd', ab'c'd, and ab'c'd'. This instance satisfies the MVD A->-> BC. However, it does not satisfy A->-> B. For example, if it did satisfy A->-> B, then because the instance contains the tuples abcd and ab'c'd, we would expect it to contain abc'd and ab'cd, neither of which is in the instance.Database Systems: The Complete BookSolutions for Chapter 4Solutions for Section 4.2Exercise 4.2.1class Customer {attribute string name;attribute string addr;attribute string phone;attribute integer ssNo;relationship Set<Account> ownsAcctsinverse Account::ownedBy;}class Account {attribute integer number;attribute string type;attribute real balance;relationship Set<Customer> ownedByinverse Customer::ownsAccts}Exercise 4.2.4class Person {attribute string name;relationship Person motherOfinverse Person::childrenOfFemalerelationship Person fatherOfinverse Person::childrenOfMalerelationship Set<Person> childreninverse Person::parentsOfrelationship Set<Person> childrenOfFemaleinverse Person::motherOfrelationship Set<Person> childrenOfMaleinverse Person::fatherOfrelationship Set<Person> parentsOfinverse Person::children}Notice that there are six different relationships here. For example, the inverse of the relationship that connects a person to their (unique) mother is a relationship that connects a mother (i.e., a female person) to the set of her children. That relationship, which we call childrenOfFemale, is different from the children relationship, which connects anyone -- male or female -- to their children.Exercise 4.2.7A relationship R is its own inverse if and only if for every pair (a,b) in R, the pair (b,a) is also in R. In the terminology of set theory, the relation R is ``symmetric.''Solutions for Section 4.3Exercise 4.3.1We think that Social Security number should me the key for Customer, and account number should be the key for Account. Here is the ODL solution with key and extent declarations.class Customer(extent Customers key ssNo){attribute string name;attribute string addr;attribute string phone;attribute integer ssNo;relationship Set<Account> ownsAcctsinverse Account::ownedBy;}class Account(extent Accounts key number){attribute integer number;attribute string type;attribute real balance;relationship Set<Customer> ownedByinverse Customer::ownsAccts}Solutions for Section 4.4Exercise 4.4.1(a)Since the relationship between customers and accounts is many-many, we should create a separate relation from that relationship-pair.Customers(ssNo, name, address, phone)Accounts(number, type, balance)CustAcct(ssNo, number)Exercise 4.4.1(d)Ther is only one attribute, but three pairs of relationships from Person to itself. Since motherOf and fatherOf are many-one, we can store their inverses in the relation for Person. That is, for each person, childrenOfMale and childrenOfFemale will indicate that persons's father and mother. The children relationship is many-many, and requires its own relation. This relation actually turns out to be redundant, in the sense that its tuples can be deduced from the relationships stored with Person. The schema:Persons(name, childrenOfFemale, childrenOfMale)Parent-Child(parent, child)Exercise 4.4.4Y ou get a schema like:Studios(name, address, ownedMovie)Since name -> address is the only FD, the key is {name, ownedMovie}, and the FD has a left side that is not a superkey.Exercise 4.4.5(a,b,c)(a) Struct Card { string rank, string suit };(b) class Hand {attribute Set theHand;};For part (c) we have:Hands(handId, rank, suit)Notice that the class Hand has no key, so we need to create one: handID. Each hand has, in the relation Hands, one tuple for each card in the hand.Exercise 4.4.5(e)Struct PlayerHand { string Player, Hand theHand };class Deal {attribute Set theDeal;}Alternatively, PlayerHand can be defined directly within the declaration of attribute theDeal. Exercise 4.4.5(h)Since keys for Hand and Deal are lacking, a mechanical way to design the database schema is to have one relation connecting deals and player-hand pairs, and another to specify the contents of hands. That is:Deals(dealID, player, handID)Hands(handID, rank, suit)However, if we think about it, we can get rid of handID and connect the deal and the player directly to the player's cards, as:Deals(dealID, player, rank, suit)Exercise 4.4.5(i)First, card is really a pair consisting of a suit and a rank, so we need two attributes in a relation schema to represent cards. However, much more important is the fact that the proposed schema does not distinguish which card is in which hand. Thus, we need another attribute that indicates which hand within the deal a card belongs to, something like:Deals(dealID, handID, rank, suit)Exercise 4.4.6(c)Attribute b is really a bag of (f,g) pairs. Thus, associated with each a-value will be zero or more (f,g) pairs, each of which can occur several times. We shall use an attribute count to indicate the number of occurrences, although if relations allow duplicate tuples we could simply allow duplicate (a,f,g) triples in the relation. The proposed schema is:C(a, f, g, count)Solutions for Section 4.5Exercise 4.5.1(b)Studios(name, address, movies{(title, year, inColor, length,stars{(name, address, birthdate)})})Since the information about a star is repeated once for each of their movies, there is redundancy. To eliminate it, we have to use a separate relation for stars and use pointers from studios. That is: Stars(name, address, birthdate)Studios(name, address, movies{(title, year, inColor, length,stars{*Stars})})Since each movie is owned by one studio, the information about a movie appears in only one tuple of Studios, and there is no redundancy.Exercise 4.5.2Customers(name, address, phone, ssNo, accts{*Accounts})Accounts(number, type, balance, owners{*Customers})Solutions for Section 4.6Exercise 4.6.1(a)We need to add new nodes labeled George Lucas and Gary Kurtz. Then, from the node sw (which represents the movie Star Wars), we add arcs to these two new nodes, labeled direc tedBy and producedBy, respectively.Exercise 4.6.2Create nodes for each account and each customer. From each customer node is an arc to a node representing the attributes of the customer, e.g., an arc labeled name to the customer's name. Likewise, there is an arc from each account node to each attribute of that account, e.g., an arc labeled balance to the value of the balance.To represent ownership of accounts by customers, we place an arc labeled owns from each customer node to the node of each account that customer holds (possibly jointly). Also, we placean arc labeled ownedBy from each account node to the customer node for each owner of that account.Exercise 4.6.5In the semistructured model, nodes represent data elements, i.e., entities rather than entity sets. In the E/R model, nodes of all types represent schema elements, and the data is not represented at all. Solutions for Section 4.7Exercise 4.7.1(a)<STARS-MOVIES><STAR starId = "cf" starredIn = "sw, esb, rj"><NAME>Carrie Fisher</NAME><ADDRESS><STREET>123 Maple St.</STREET><CITY>Hollywood</CITY></ADDRESS><ADDRESS><STREET>5 Locust Ln.</STREET><CITY>Malibu</CITY></ADDRESS></STAR><STAR starId = "mh" starredIn = "sw, esb, rj"><NAME>Mark Hamill</NAME><ADDRESS><STREET>456 Oak Rd.<STREET><CITY>Brentwood</CITY></ADDRESS></STAR><STAR starId = "hf" starredIn = "sw, esb, rj, wit"><NAME>Harrison Ford</NAME><ADDRESS><STREET>whatever</STREET><CITY>whatever</CITY></ADDRESS></STAR><MOVIE movieId = "sw" starsOf = "cf, mh"><TITLE>Star Wars</TITLE><YEAR>1977</YEAR></MOVIE><MOVIE movieId = "esb" starsOf = "cf, mh"><TITLE>Empire Strikes Back</TITLE><YEAR>1980</YEAR></MOVIE><MOVIE movieId = "rj" starsOf = "cf, mh"><TITLE>Return of the Jedi</TITLE><YEAR>1983</YEAR></MOVIE><MOVIE movieID = "wit" starsOf = "hf"><TITLE>Witness</TITLE><YEAR>1985</YEAR></MOVIE></STARS-MOVIES>Exercise 4.7.2<!DOCTYPE Bank [<!ELEMENT BANK (CUSTOMER* ACCOUNT*)><!ELEMENT CUSTOMER (NAME, ADDRESS, PHONE, SSNO)> <!A TTLIST CUSTOMERcustId IDowns IDREFS><!ELEMENT NAME (#PCDA TA)><!ELEMENT ADDRESS (#PCDA TA)><!ELEMENT PHONE (#PCDA TA)><!ELEMENT SSNO (#PCDA TA)><!ELEMENT ACCOUNT (NUMBER, TYPE, BALANCE)><!A TTLIST ACCOUNTacctId IDownedBy IDREFS><!ELEMENT NUMBER (#PCDA TA)><!ELEMENT TYPE (#PCDA TA)><!ELEMENT BALANCE (#PCDA TA)>]>Database Systems: The CompleteBookSolutions for Chapter 5Solutions for Section 5.2Exercise 5.2.1(a)PI_model( SIGMA_{speed >= 1000} ) (PC)Exercise 5.2.1(f)The trick is to theta-join PC with itself on the condition that the hard disk sizes are equal. That gives us tuples that have two PC model numbers with the same value of hd. However, these two PC's could in fact be the same, so we must also require in the theta-join that the model numbers be unequal. Finally, we want the hard disk sizes, so we project onto hd.The expression is easiest to see if we write it using some temporary values. We start by renaming PC twice so we can talk about two occurrences of the same attributes.R1 = RHO_{PC1} (PC)R2 = RHO_{PC2} (PC)R3 = R1 JOIN_{PC1.hd = PC2.hd AND PC1.model <> PC2.model} R2R4 = PI_{PC1.hd} (R3)Exercise 5.2.1(h)First, we find R1, the model-speed pairs from both PC and Laptop. Then, we find from R1 those computers that are ``fast,'' at least 133Mh. At the same time, we join R1 with Product to connect model numbers to their manufacturers and we project out the speed to get R2. Then we join R2 with itself (after renaming) to find pairs of different models by the same maker. Finally, we get our answer, R5, by projecting onto one of the maker attributes. A sequence of steps giving the desired expression is: R1 = PI_{model,speed} (PC) UNION PI_{model,speed} (Laptop)R2 = PI_{maker,model} (SIGMA_{speed>=700} (R1) JOIN Product)R3 = RHO_{T(maker2, model2)} (R2)R4 = R2 JOIN_{maker = maker2 AND model <> model2} (R3)R5 = PI_{maker} (R4)Exercise 5.2.2Here are figures for the expression trees of Exercise 5.2.1 Part (a)Part (f)Part (h). Note that the third figure is not really a tree, since it uses a common subexpression. We could duplicate the nodes to make it a tree, but using common subexpressions is a valuable form of query optimization. One of the benefits one gets from constructing ``trees'' for queries is the ability to combine nodes that represent common subexpressions.Exercise 5.2.7The relation that results from the natural join has only one attribute from each pair of equated attributes. The theta-join has attributes for both, and their columns are identical.Exercise 5.2.9(a)If all the tuples of R and S are different, then the union has n+m tuples, and this number is the maximum possible.The minimum number of tuples that can appear in the result occurs if every tuple of one relation also appears in the other. Surely the union has at least as many tuples as the larger of R and that is, max(n,m) tuples. However, it is possible for every tuple of the smaller to appear in the other, so it is possible that there are as few as max(n,m) tuples in the union.Exercise 5.2.10In the following we use the name of a relation both as its instance (set of tuples) and as its schema (set of attributes). The context determines uniquely which is meant.PI_R(R JOIN S) Note, however, that this expression works only for sets; it does not preserve the multipicity of tuples in R. The next two expressions work for bags.R JOIN DELTA(PI_{R INTERSECT S}(S)) In this expression, each projection of a tuple from S onto the attributes that are also in R appears exactly once in the second argument of the join, so it preserves multiplicity of tuples in R, except for those thatdo not join with S, which disappear. The DELTA operator removes duplicates, as described in Section 5.4.R - [R - PI_R(R JOIN S)] Here, the strategy is to find the dangling tuples of R and remove them.Solutions for Section 5.3Exercise 5.3.1As a bag, the value is {700, 1500, 866, 866, 1000, 1300, 1400, 700, 1200, 750, 1100, 350, 733}. The order is unimportant, of course. The average is 959.As a set, the value is {700, 1500, 866, 1000, 1300, 1400, 1200, 750, 1100, 350, 733}, and the average is 967. H3>Exercise 5.3.4(a)As sets, an element x is in the left-side expression(R UNION S) UNION Tif and only if it is in at least one of R, S, and T. Likewise, it is in the right-side expressionR UNION (S UNION T)under exactly the same conditions. Thus, the two expressions have exactly the same members, and the sets are equal.As bags, an element x is in the left-side expression as many times as the sum of the number of times it is in R, S, and T. The same holds for the right side. Thus, as bags the expressions also have the same value.Exercise 5.3.4(h)As sets, element x is in the left sideR UNION (S INTERSECT T)if and only if x is either in R or in both S and T. Element x is in the right side(R UNION S) INTERSECT (R UNION T)if and only if it is in both R UNION S and R UNION T. If x is in R, then it is in both unions. If x is in both S and T, then it is in both union. However, if x is neither in R nor in both of S and T, then it cannot be in both unions. For example, suppose x is not in R and not in S. Then x is not in R UNION S. Thus, the statement of when x is in the right side is exactly the same as when it is in the left side: x is either in R or in both of S and T.Now, consider the expression for bags. Element x is in the left side the sum of the number of times it is in R plus the smaller of the number of times x is in S and the number of times x is in T. Likewise, the number of times x is in the right side is the smaller ofThe sum of the number of times x is in R and in S.The sum of the number of times x is in R and in T.A moment's reflection tells us that this minimum is the sum of the number of times x is in R plus the smaller of the number of times x is in S and in T, exactly as for the left side.Exercise 5.3.5(a)For sets, we observe that element x is in the left side(R INTERSECT S) - T。

数据库系统教程课后答案

数据库系统教程课后答案数据库系统是一种用于管理和组织大量数据的软件系统，它通过数据模型、数据结构和数据操作等方式来实现数据的存储、访问、更新和管理等功能。

数据库系统广泛应用于各个领域，如企业管理、电子商务、科学研究等。

在数据库系统的学习过程中，会出现一些练习题目和问题，需要我们进行思考和解答。

下面我将结合一些常见的课后答案，对数据库系统进行详细的解析。

首先，我们需要了解数据库的基本概念和组成部分。

数据库是按照一定的数据模型组织、存储和管理数据的集合，它由数据库管理系统(DBMS)、数据库、数据库模式(或称为数据库结构)、数据库实例(或称为数据系统)等组成。

其中，数据库管理系统是进行数据库管理的软件系统，它负责数据库的创建、维护和管理等操作。

接着，我们需要了解数据库的设计和规范化。

数据库设计是指根据用户的需求和系统的要求，将现实世界的数据和关系转化为数据库模式的过程。

在设计数据库时，要符合一定的规范化原则，如第一范式、第二范式和第三范式等。

规范化可以提高数据库的性能、减少数据冗余，同时也方便数据库的操作和维护。

然后，我们需要了解数据库的查询和操作。

数据库查询是指根据用户的需求，从数据库中检索出符合条件的数据的过程。

查询语言是进行数据库查询的工具，如结构化查询语言(SQL)。

SQL语言包括数据定义语言(DDL)、数据操纵语言(DML)和数据控制语言(DCL)等。

DDL用于定义和管理数据库的结构，如创建表、定义键等；DML用于对数据库中的数据进行操作，如插入、更新和删除等；DCL用于控制数据库的安全性和权限，如授权和回收权限。

此外，我们还需要了解数据库的事务和并发控制。

事务是指一组对数据库的操作，要么全部执行成功，要么全部放弃。

事务具备ACID特性，即原子性、一致性、隔离性和持久性。

并发控制是指多个事务同时对数据库进行操作时，保证数据库的一致性和完整性的措施。

并发控制可以通过加锁、多版本并发控制(MVCC)、时间戳等方式来实现。

数据库系统概念(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.

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

C H A P T E R12Query ProcessingPractice Exercises12.1Assume(for simplicity in this exercise)that only one tupleﬁts in a blockand memory holds at most3blocks.Show the runs created on each passof the sort-merge algorithm,when applied to sort the following tuples ontheﬁrst attribute:(kangaroo,17),(wallaby,21),(emu,1),(wombat,13),(platypus,3),(lion,8),(warthog,4),(zebra,11),(meerkat,6),(hyena,9),(hornbill,2),(baboon,12).Answer:We will refer to the tuples(kangaroo,17)through(baboon,12)using tuple numbers t1through t12.We refer to the j th run used by the i thpass,as r i j.The initial sorted runs have three blocks each.They are:r11={t3,t1,t2}r12={t6,t5,t4}r13={t9,t7,t8}r14={t12,t11,t10}Each pass merges three runs.Therefore the runs after the end of theﬁrstpass are:r21={t3,t1,t6,t9,t5,t2,t7,t4,t8}r22={t12,t11,t10}At the end of the second pass,the tuples are completely sorted into onerun:r31={t12,t3,t11,t10,t1,t6,t9,t5,t2,t7,t4,t8}12.2Consider the bank database of Figure12.13,where the primary keys areunderlined,and the following SQL query:12Chapter12Query Processingselect T.branch namefrom branch T,branch Swhere T.assets>S.assets and S.branch city=“Brooklyn”Write an efﬁcient relational-algebra expression that is equivalent to thisquery.Justify your choice.Answer:Query:T.branch name(( branch name,assets(␳T(branch)))1T.assets>S.assets( assets(␴(branch city=’Brooklyn’)(␳S(branch)))))This expression performs the theta join on the smallest amount of datapossible.It does this by restricting the right hand side operand of the jointo only those branches in Brooklyn,and also eliminating the unneededattributes from both the operands.12.3Let relations r1(A,B,C)and r2(C,D,E)have the following properties:r1has20,000tuples,r2has45,000tuples,25tuples of r1ﬁt on one block,and30tuples of r2ﬁt on one block.Estimate the number of block transfers andseeks required,using each of the following join strategies for r11r2:a.Nested-loop join.b.Block nested-loop join.c.Merge join.d.Hash join.Answer:r1needs800blocks,and r2needs1500blocks.Let us assume M pagesof memory.If M>800,the join can easily be done in1500+800diskaccesses,using even plain nested-loop join.So we consider only the casewhere M≤800pages.a.Nested-loop join:Using r1as the outer relation we need20000∗1500+800=30,000,800disk accesses,if r2is the outer relation we need45000∗800+1500=36,001,500disk accesses.b.Block nested-loop join:If r1is the outer relation,we need⌈800M−1⌉∗1500+800disk accesses,if r2is the outer relation we need⌈1500M−1⌉∗800+1500disk accesses.c.Merge-join:Assuming that r1and r2are not initially sorted on the join key,the to-tal sorting cost inclusive of the output is B s=1500(2⌈log M−1(1500/M)⌉+Exercises32)+800(2⌈log M−1(800/M)⌉+2)disk accesses.Assuming all tupleswith the same value for the join attributesﬁt in memory,the totalcost is B s+1500+800disk accesses.d.Hash-join:We assume no overﬂow occurs.Since r1is smaller,we use it as thebuild relation and r2as the probe relation.If M>800/M,i.e.no needfor recursive partitioning,then the cost is3(1500+800)=6900diskaccesses,else the cost is2(1500+800)⌈log M−1(800)−1⌉+1500+800disk accesses.12.4The indexed nested-loop join algorithm described in Section12.5.3can beinefﬁcient if the index is a secondary index,and there are multiple tuples with the same value for the join attributes.Why is it inefﬁcient?Describea way,using sorting,to reduce the cost of retrieving tuples of the innerrelation.Under what conditions would this algorithm be more efﬁcient than hybrid merge join?Answer:If there are multiple tuples in the inner relation with the same value for the join attributes,we may have to access that many blocks of the inner relation for each tuple of the outer relation.That is why it is inefﬁcient.To reduce this cost we can perform a join of the outer relation tuples with just the secondary index leaf entries,postponing the inner relation tuple retrieval.The resultﬁle obtained is then sorted on the inner relation addresses,allowing an efﬁcient physical order scan to complete the join.Hybrid merge–join requires the outer relation to be sorted.The above algorithm does not have this requirement,but for each tuple in the outer relation it needs to perform an index lookup on the inner relation.If the outer relation is much larger than the inner relation,this index lookup cost will be less than the sorting cost,thus this algorithm will be more efﬁcient.12.5Let r and s be relations with no indices,and assume that the relationsare not sorted.Assuming inﬁnite memory,what is the lowest-cost way (in terms of I/O operations)to compute r1s?What is the amount of memory required for this algorithm?Answer:We can store the entire smaller relation in memory,read the larger relation block by block and perform nested loop join using the larger one as the outer relation.The number of I/O operations is equal to b r+b s,and memory requirement is min(b r,b s)+2pages.12.6Consider the bank database of Figure12.13,where the primary keys areunderlined.Suppose that a B+-tree index on branch city is available on relation branch,and that no other index is available.List different ways to handle the following selections that involve negation:a.␴¬(branch city<“Brooklyn”)(branch)4Chapter12Query Processingb.␴¬(branch city=“Brooklyn”)(branch)c.␴¬(branch city<“Brooklyn”∨assets<5000)(branch)Answer:e the index to locate theﬁrst tuple whose branch cityﬁeld hasvalue“Brooklyn”.From this tuple,follow the pointer chains till theend,retrieving all the tuples.b.For this query,the index serves no purpose.We can scan theﬁlesequentially and select all tuples whose branch cityﬁeld is anythingother than“Brooklyn”.c.This query is equivalent to the query␴(branch city≥′Brooklyn′∧assets<5000)(branch)Using the branch-city index,we can retrieve all tuples with branch-cityvalue greater than or equal to“Brooklyn”by following the pointerchains from theﬁrst“Brooklyn”tuple.We also apply the additionalcriteria of assets<5000on every tuple.12.7Write pseudocode for an iterator that implements indexed nested-loopjoin,where the outer relation is pipelined.Your pseudocode must deﬁnethe standard iterator functions open(),next(),and close().Show what stateinformation the iterator must maintain between calls.Answer:Let outer be the iterator which returns successive tuples fromthe pipelined outer relation.Let inner be the iterator which returns suc-cessive tuples of the inner relation having a given value at the join at-tributes.The inner iterator returns these tuples by performing an indexlookup.The functions IndexedNLJoin::open,IndexedNLJoin::close andIndexedNLJoin::next to implement the indexed nested-loop join iteratorare given below.The two iterators outer and inner,the value of the lastread outer relation tuple t r and aﬂag done r indicating whether the end ofthe outer relation scan has been reached are the state information whichneed to be remembered by IndexedNLJoin between calls.IndexedNLJoin::open()beginouter.open();inner.open();done r:=false;if(outer.next()=false)move tuple from outer’s output buffer to t r;elsedone r:=true;endExercises5IndexedNLJoin::close()beginouter.close();inner.close();endboolean IndexedNLJoin::next()beginwhile(¬done r)beginif(inner.next(t r[JoinAttrs])=false)beginmove tuple from inner’s output buffer to t s;compute t r1t s and place it in output buffer;return true;endelseif(outer.next()=false)beginmove tuple from outer’s output buffer to t r;rewind inner toﬁrst tuple of s;endelsedone r:=true;endreturn false;end12.8Design sort-based and hash-based algorithms for computing the relationaldivision operation(see Practise Exercises of Chapter6for a deﬁnition of the division operation).Answer:Suppose r(T∪S)and s(S)be two relations and r÷s has to be computed.For sorting based algorithm,sort relation s on S.Sort relation r on (T,S).Now,start scanning r and look at the T attribute values of theﬁrst tuple.Scan r till tuples have same value of T.Also scan s simultaneously and check whether every tuple of s also occurs as the S attribute of r,ina fashion similar to merge join.If this is the case,output that value of Tand proceed with the next value of T.Relation s may have to be scanned multiple times but r will only be scanned once.Total disk accesses,after6Chapter12Query Processingsorting both the relations,will be|r|+N∗|s|,where N is the number ofdistinct values of T in r.We assume that for any value of T,all tuples in r with that T valueﬁt inmemory,and consider the general case at the end.Partition the relation ron attributes in T such that each partitionﬁts in memory(always possiblebecause of our assumption).Consider partitions one at a time.Build ahash table on the tuples,at the same time collecting all distinct T valuesin a separate hash table.For each value of T,Now,for each value V T ofT,each value s of S,probe the hash table on(V T,s).If any of the values isabsent,discard the value V T,else output the value V T.In the case that not all r tuples with one value for Tﬁt in memory,partitionr and s on the S attributes such that the condition is satisﬁed,run thealgorithm on each corresponding pair of partitions r i and s i.Output theintersection of the T values generated in each partition.12.9What is the effect on the cost of merging runs if the number of bufferblocks per run is increased,while keeping overall memory available forbuffering runsﬁxed?Answer:Seek overhead is reduced,but the the number of runs that canbe merged in a pass decreases potentially leading to more passes.A valueof b b that minimizes overall cost should be chosen.。

数据库系统概论第五版课后习题答案之欧阳治创编

第1章绪论1 ．试述数据、数据库、数据库系统、数据库管理系统的概念。

答：( l ）数据（Data ) ：描述事物的符号记录称为数据。

数据的种类有数字、文字、图形、图像、声音、正文等。

数据与其语义是不可分的。

解析在现代计算机系统中数据的概念是广义的。

早期的计算机系统主要用于科学计算，处理的数据是整数、实数、浮点数等传统数学中的数据。

现代计算机能存储和处理的对象十分广泛，表示这些对象的数据也越来越复杂。

数据与其语义是不可分的。

500 这个数字可以表示一件物品的价格是500 元，也可以表示一个学术会议参加的人数有500 人，还可以表示一袋奶粉重500 克。

( 2 ）数据库（DataBase ，简称DB ) ：数据库是长期储存在计算机内的、有组织的、可共享的数据集合。

数据库中的数据按一定的数据模型组织、描述和储存，具有较小的冗余度、较高的数据独立性和易扩展性，并可为各种用户共享。

( 3 ）数据库系统（DataBas 。

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

解析数据库系统和数据库是两个概念。

数据库系统是一个人一机系统，数据库是数据库系统的一个组成部分。

但是在日常工作中人们常常把数据库系统简称为数据库。

希望读者能够从人们讲话或文章的上下文中区分“数据库系统”和“数据库”，不要引起混淆。

( 4 ）数据库管理系统（DataBase Management sytem ，简称DBMs ) ：数据库管理系统是位于用户与操作系统之间的一层数据管理软件，用于科学地组织和存储数据、高效地获取和维护数据。

DBMS 的主要功能包括数据定义功能、数据操纵功能、数据库的运行管理功能、数据库的建立和维护功能。

解析DBMS 是一个大型的复杂的软件系统，是计算机中的基础软件。

目前，专门研制DBMS 的厂商及其研制的DBMS 产品很多。

数据库系统概论（第四版）答案

数据库系统‎概论（第四‎版）答案‎第2章关‎系数据库‎1 ．试‎述关系模型‎的三个组成‎部分。

‎答：关系模‎型由关系数‎据结构、关‎系操作集合‎和关系完整‎性约束三部‎分组成。

‎2 ．试‎述关系数据‎语言的特点‎和分类。

‎答：关系‎数据语言可‎以分为三类‎：关系‎代数语言。

‎关系演‎算语言：元‎组关系演算‎语言和域关‎系演算语言‎。

SQ‎L：具有关‎系代数和关‎系演算双重‎特点的语言‎。

这些‎关系数据语‎言的共同特‎点是，语言‎具有完备的‎表达能力，‎是非过程化‎的集合操作‎语言，功能‎强，能够嵌‎入高级语言‎中使用。

‎4 ．试‎述关系模型‎的完整性规‎则。

在参照‎完整性中，‎为什么外部‎码属性的值‎也可以为空‎？什么情况‎下才可以为‎空？答‎：实体完整‎性规则是指‎若属性A是‎基本关系R‎的主属性，‎则属性A不‎能取空值。

‎若属性‎(或属性组‎)F是基本‎关系R的外‎码，它与基‎本关系S的‎主码Ks相‎对应(基本‎关系R和S‎不一定是不‎同的关系)‎，则对于R‎中每个元组‎在F上的值‎必须为：或‎者取空值(‎F的每个属‎性值均为空‎值)；或者‎等于S中某‎个元组的主‎码值。

即属‎性F本身不‎是主属性，‎则可以取空‎值，否则不‎能取空值。

‎5．设‎有一个SP‎J数据库，‎包括S，P‎，J，SP‎J四个关系‎模式：‎1）求供应‎工程J1零‎件的供应商‎号码SNO‎：πS‎n o(σS‎n o=…J‎1‟（SP‎J）)‎2）求供应‎工程J1零‎件P1的供‎应商号码S‎N O：‎πSno(‎σSno=‎…J1‟∧‎P no=…‎P1…(S‎P J))‎3）求供‎应工程J1‎零件为红色‎的供应商号‎码SNO：‎πSn‎o(σPn‎o=…P1‎…（σC‎O LOR=‎‟红… （‎P）∞SP‎J）)‎4）求没有‎使用天津供‎应商生产的‎红色零件的‎工程号JN‎O：π‎J no(S‎P J)- ‎πJNO（‎σcity‎=…天津‟‎∧Colo‎r=…红…‎（S∞S‎P J∞P）‎)5）‎求至少用了‎供应商S1‎所供应的全‎部零件的工‎程号JNO‎：πJ‎n o，Pn‎o(SPJ‎)† πP‎n o（σS‎n o=…S‎1… （S‎P J））‎6．试述‎等值连接与‎自然连接的‎区别和联系‎。

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

C H A P T E R8Relational Database DesignExercises8.1Suppose that we decompose the schema R=(A,B,C,D,E)into(A,B,C)(A,D,E).Show that this decomposition is a lossless-join decomposition if thefollowing set F of functional dependencies holds:A→BCCD→EB→DE→AAnswer:A decomposition{R1,R2}is a lossless-join decomposition ifR1∩R2→R1or R1∩R2→R2.Let R1=(A,B,C),R2=(A,D,E),and R1∩R2=A.Since A is a candidate key(see PracticeExercise8.6),Therefore R1∩R2→R1.8.2List all functional dependencies satisﬁed by the relation of Figure8.17.Answer:The nontrivial functional dependencies are:A→B andC→B,and a dependency they logically imply:AC→B.There are19trivial functional dependencies of the form␣→␤,where␤⊆␣.Cdoes not functionally determine A because theﬁrst and third tuples havethe same C but different A values.The same tuples also show B does notfunctionally determine A.Likewise,A does not functionally determineC because theﬁrst two tuples have the same A value and different Cvalues.The same tuples also show B does not functionally determine C.8.3Explain how functional dependencies can be used to indicate the fol-lowing:910Chapter8Relational Database Design•A one-to-one relationship set exists between entity sets student andinstructor.•A many-to-one relationship set exists between entity sets studentand instructor.Answer:Let Pk(r)denote the primary key attribute of relation r.•The functional dependencies Pk(student)→Pk(instructor)andPk(instructor)→Pk(student)indicate a one-to-one relationshipbecause any two tuples with the same value for student must havethe same value for instructor,and any two tuples agreeing oninstructor must have the same value for student.•The functional dependency Pk(student)→Pk(instructor)indicates amany-to-one relationship since any student value which is repeatedwill have the same instructor value,but many student values mayhave the same instructor value.8.4Use Armstrong’s axioms to prove the soundness of the union rule.(Hint:Use the augmentation rule to show that,if␣→␤,then␣→␣␤.Apply theaugmentation rule again,using␣→␥,and then apply the transitivityrule.)Answer:To prove that:if␣→␤and␣→␥then␣→␤␥Following the hint,we derive:␣→␤given␣␣→␣␤augmentation rule␣→␣␤union of identical sets␣→␥given␣␤→␥␤augmentation rule␣→␤␥transitivity rule and set union commutativity8.5Use Armstrong’s axioms to prove the soundness of the pseudotransitiv-ity rule.Answer:Proof using Armstrong’s axioms of the Pseudotransitivity Rule:if␣→␤and␥␤→␦,then␣␥→␦.␣→␤given␣␥→␥␤augmentation rule and set union commutativity␥␤→␦given␣␥→␦transitivity rule8.6Compute the closure of the following set F of functional dependenciesfor relation schema R=(A,B,C,D,E).Exercises 11A →BCCD →EB →DE →AList the candidate keys for R .Answer:Note:It is not reasonable to expect students to enumerate all of F +.Some shorthand representation of the result should be acceptable as long as the nontrivial members of F +are found.Starting with A →BC ,we can conclude:A →B and A →C .Since A →B and B →D ,A →D (decomposition,transitive)Since A →C D and C D →E ,A →E (union,decom-position,transi-tive)Since A →A ,we have (reﬂexive)A →ABC DE from the above steps (union)Since E →A ,E →ABC DE (transitive)Since C D →E ,C D →ABC DE (transitive)Since B →D and BC →C D ,BC →ABC DE (augmentative,transitive)Also,C →C ,D →D ,B D →D ,etc.Therefore,any functional dependency with A ,E ,BC ,or C D on the left hand side of the arrow is in F +,no matter which other attributes appear in the FD.Allow *to represent any set of attributes in R ,then F +is B D →B ,B D →D ,C →C ,D →D ,B D →B D ,B →D ,B →B ,B →B D ,and all FDs of the form A ∗→␣,BC ∗→␣,C D ∗→␣,E ∗→␣where ␣is any subset of {A ,B ,C ,D ,E }.The candidate keys are A ,BC ,C D ,and E .8.7Using the functional dependencies of Practice Exercise 8.6,compute thecanonical cover F c .Answer:The given set of FDs F is:-A →BCCD →EB →DE →AThe left side of each FD in F is unique.Also none of the attributes in the left side or right side of any of the FDs is extraneous.Therefore the canonical cover F c is equal to F .12Chapter8Relational Database Design8.8Consider the algorithm in Figure8.18to compute␣+.Show that thisalgorithm is more efﬁcient than the one presented in Figure8.8(Sec-tion8.4.2)and that it computes␣+correctly.Answer:The algorithm is correct because:•If A is added to result then there is a proof that␣→A.To see this,observe that␣→␣trivially so␣is correctly part of result.IfA∈␣is added to result there must be some FD␤→␥such thatA∈␥and␤is already a subset of result.(Otherwise f dcountwould be nonzero and the if condition would be false.)A full proofcan be given by induction on the depth of recursion for an executionof addin,but such a proof can be expected only from students witha good mathematical background.•If A∈␣+,then A is eventually added to result.We prove this byinduction on the length of the proof of␣→A using Armstrong’saxioms.First observe that if procedure addin is called with someargument␤,all the attributes in␤will be added to result.Also if aparticular FD’s fdcount becomes0,all the attributes in its tail willdeﬁnitely be added to result.The base case of the proof,A∈␣⇒A∈␣+,is obviously true because theﬁrst call to addinhas the argument␣.The inductive hypotheses is that if␣→A canbe proved in n steps or less then A∈result.If there is a proof inn+1steps that␣→A,then the last step was an application ofeither reﬂexivity,augmentation or transitivity on a fact␣→␤proved in n or fewer steps.If reﬂexivity or augmentation was usedin the(n+1)st step,A must have been in result by the end of the n thstep itself.Otherwise,by the inductive hypothesis␤⊆result.Therefore the dependency used in proving␤→␥,A∈␥willhave f dcount set to0by the end of the n th step.Hence A will beadded to result.To see that this algorithm is more efﬁcient than the one presented inthe chapter note that we scan each FD once in the main program.Theresulting array a ppears has size proportional to the size of the givenFDs.The recursive calls to addin result in processing linear in the sizeof a ppears.Hence the algorithm has time complexity which is linear inthe size of the given FDs.On the other hand,the algorithm given in thetext has quadratic time complexity,as it may perform the loop as manytimes as the number of FDs,in each loop scanning all of them once.8.9Given the database schema R(a,b,c),and a relation r on the schema R,write an SQL query to test whether the functional dependency b→cholds on relation r.Also write an SQL assertion that enforces the func-tional dependency.Assume that no null values are present.(Althoughpart of the SQL standard,such assertions are not supported by anydatabase implementation currently.)Answer:Exercises13a.The query is given below.Its result is non-empty if and only ifb→c does not hold on r.select bfrom rgroup by bhaving count(distinct c)>1b.create assertion b to c check(not exists(select bfrom rgroup by bhaving count(distinct c)>1))8.10Our discussion of lossless-join decomposition implicitly assumed thatattributes on the left-hand side of a functional dependency cannot take on null values.What could go wrong on decomposition,if this property is violated?Answer:The natural join operator is deﬁned in terms of the cartesian product and the selection operator.The selection operator,gives unknown for any query on a null value.Thus,the natural join excludes all tuples with null values on the common attributes from theﬁnal result.Thus, the decomposition would be lossy(in a manner different from the usual case of lossy decomposition),if null values occur in the left-hand side of the functional dependency used to decompose the relation.(Null values in attributes that occur only in the right-hand side of the functional dependency do not cause any problems.)8.11In the BCNF decomposition algorithm,suppose you use a functional de-pendency␣→␤to decompose a relation schema r(␣,␤,␥)into r1(␣,␤) and r2(␣,␥).a.What primary and foreign-key constraint do you expect to holdon the decomposed relations?b.Give an example of an inconsistency that can arise due to anerroneous update,if the foreign-key constraint were not enforcedon the decomposed relations above.c.When a relation is decomposed into3NF using the algorithm inSection8.5.2,what primary and foreign key dependencies wouldyou expect will hold on the decomposed schema?14Chapter8Relational Database DesignAnswer:a.␣should be a primary key for r1,and␣should be the foreign keyfrom r2,referencing r1.b.If the foreign key constraint is not enforced,then a deletion of atuple from r1would not have a corresponding deletion from thereferencing tuples in r2.Instead of deleting a tuple from r,thiswould amount to simply setting the value of␣to null in sometuples.c.For every schema r i(␣␤)added to the schema because of a rule␣→␤,␣should be made the primary key.Also,a candidate key␥for the original relation is located in some newly created relationr k,and is a primary key for that relation.Foreign key constraints are created as follows:for each relationr i created above,if the primary key attributes of r i also occur inany other relation r j,then a foreign key constraint is created fromthose attributes in r j,referencing(the primary key of)r i.8.12Let R1,R2,...,R n be a decomposition of schema U.Let u(U)be a rela-(u).Show thattion,and let r i= RIu⊆r11r21···1r nAnswer:Consider some tuple t in u.(u)implies that t[R i]∈r i,1≤i≤n.Thus,Note that r i= Rit[R1]1t[R2]1...1t[R n]∈r11r21...1r nBy the deﬁnition of natural join,t[R1]1t[R2]1...1t[R n]= ␣(␴␤(t[R1]×t[R2]×...×t[R n]))where the condition␤is satisﬁed if values of attributes with the samename in a tuple are equal and where␣=U.The cartesian productof single tuples generates one tuple.The selection process is satisﬁedbecause all attributes with the same name must have the same valuesince they are projections from the same tuple.Finally,the projectionclause removes duplicate attribute names.By the deﬁnition of decomposition,U=R1∪R2∪...∪R n,which meansthat all attributes of t are in t[R1]1t[R2]1...1t[R n].That is,t is equalto the result of this join.Since t is any arbitrary tuple in u,u⊆r11r21...1r n8.13Show that the decomposition in Practice Exercise8.1is not a dependency-preserving decomposition.Answer:The dependency B→D is not preserved.F1,the restrictionof F to(A,B,C)is A→ABC,A→AB,A→AC,A→BC,Exercises 15A →B ,A →C ,A →A ,B →B ,C →C ,AB →AC ,AB →ABC ,AB →BC ,AB →AB ,AB →A ,AB →B ,AB →C ,AC (same as AB ),BC (same as AB ),ABC (same as AB ).F 2,the restriction of F to (C ,D ,E )is A →ADE ,A →AD ,A →AE ,A →DE ,A →A ,A →D ,A →E ,D →D ,E (same as A ),AD ,AE ,DE ,ADE (same as A ).(F 1∪F 2)+is easily seen not to contain B →D since the only FD in F 1∪F 2with B as the left side is B →B ,a trivial FD .We shall see in Practice Exercise 8.15that B →D is indeed in F +.Thus B →D is not preserved.Note that C D →ABC DE is also not preserved.A simpler argument is as follows:F 1contains no dependencies with D on the right side of the arrow.F 2contains no dependencies withB on the left side of the arrow.Therefore for B →D to be preserved theremustbe an FD B →␣in F +1and ␣→D in F +2(so B →D would follow by transitivity).Since the intersection of the two schemes is A ,␣=A .Observe that B →A is not in F +1since B +=B D .8.14Show that it is possible to ensure that a dependency-preserving decom-position into 3NF is a lossless-join decomposition by guaranteeing that at least one schema contains a candidate key for the schema being decom-posed.(Hint :Show that the join of all the projections onto the schemas of the decomposition cannot have more tuples than the original relation.)Answer:Let F be a set of functional dependencies that hold on a schema R .Let ␴={R 1,R 2,...,R n }be a dependency-preserving 3NF decompo-sition of R .Let X be a candidate key for R .Consider a legal instance r of R .Let j = X (r )1 R 1(r )1 R 2(r ) (1)R n (r ).We want to prove that r =j .We claim that if t 1and t 2are two tuples in j such that t 1[X ]=t2[X ],then t 1=t 2.To prove this claim,we use the following inductive argument –Let F ′=F 1∪F 2∪...∪F n ,where each F i is the restriction of F to the schema R i in ␴.Consider the use of the algorithm given in Figure 8.8to compute the closure of X under F ′.We use induction on the number of times that the f or loop in this algorithm is executed.•Basis :In the ﬁrst step of the algorithm,result is assigned to X ,and hence given that t 1[X ]=t 2[X ],we know that t 1[result ]=t 2[result ]is true.•Induction Step :Let t 1[result ]=t 2[result ]be true at the end of thek th execution of the f or loop.Suppose the functional dependency considered in the k +1th execution of the f or loop is ␤→␥,and that ␤⊆result .␤⊆result implies that t 1[␤]=t 2[␤]is true.The facts that ␤→␥holds for some attribute set Ri in ␴,and that t 1[R i ]and t 2[R i ]are inR i (r )imply that t 1[␥]=t 2[␥]is also true.Since ␥is now added to result by the algorithm,we know that t 1[result ]=t 2[result ]is true at theend of the k +1th execution of the f or loop.16Chapter8Relational Database DesignSince␴is dependency-preserving and X is a key for R,all attributes in Rare in result when the algorithm terminates.Thus,t1[R]=t2[R]is true,that is,t1=t2–as claimed earlier.Our claim implies that the size of X(j)is equal to the size of j.Notealso that X(j)= X(r)=r(since X is a key for R).Thus we haveproved that the size of j equals that of ing the result of PracticeExercise8.12,we know that r⊆j.Hence we conclude that r=j.Note that since X is trivially in3NF,␴∪{X}is a dependency-preservinglossless-join decomposition into3NF.8.15Give an example of a relation schema R′and set F′of functional depen-dencies such that there are at least three distinct lossless-join decompo-sitions of R′into BCNF.Answer:Given the relation R′=(A,B,C,D)the set of functionaldependencies F′=A→B,C→D,B→C allows three distinctBCNF decompositions.R1={(A,B),(C,D),(B,C)}is in BCNF as isR2={(A,B),(C,D),(A,C)}R2={(A,B),(C,D),(A,C)}R3={(B,C),(A,D),(A,B)}8.16Let a prime attribute be one that appears in at least one candidate key.Let␣and␤be sets of attributes such that␣→␤holds,but␤→␣does not hold.Let A be an attribute that is not in␣,is not in␤,and forwhich␤→A holds.We say that A is transitively dependent on␣.Wecan restate our deﬁnition of3NF as follows:A relation schema R is in3NF with respect to a set F of functional dependencies if there are nononprime attributes A in R for which A is transitively dependent on akey for R.Show that this new deﬁnition is equivalent to the original one.Answer:Suppose R is in3NF according to the textbook deﬁnition.Weshow that it is in3NF according to the deﬁnition in the exercise.Let A bea nonprime attribute in R that is transitively dependent on a key␣forR.Then there exists␤⊆R such that␤→A,␣→␤,A∈␣,A∈␤,and␤→␣does not hold.But then␤→A violates the textbookdeﬁnition of3NF since•A∈␤implies␤→A is nontrivial•Since␤→␣does not hold,␤is not a superkey•A is not any candidate key,since A is nonprimeExercises17 Now we show that if R is in3NF according to the exercise deﬁnition,it is in3NF according to the textbook deﬁnition.Suppose R is not in3NF according the the textbook deﬁnition.Then there is an FD␣→␤that fails all three conditions.Thus•␣→␤is nontrivial.•␣is not a superkey for R.•Some A in␤−␣is not in any candidate key.This implies that A is nonprime and␣→A.Let␥be a candidate key for R.Then␥→␣,␣→␥does not hold(since␣is not a superkey), A∈␣,and A∈␥(since A is nonprime).Thus A is transitively dependent on␥,violating the exercise deﬁnition.8.17A functional dependency␣→␤is called a partial dependency if thereis a proper subset␥of␣such that␥→␤.We say that␤is partially dependent on␣.A relation schema R is in second normal form(2NF)if each attribute A in R meets one of the following criteria:•It appears in a candidate key.•It is not partially dependent on a candidate key.Show that every3NF schema is in2NF.(Hint:Show that every partial dependency is a transitive dependency.)Answer:Referring to the deﬁnitions in Practice Exercise8.16,a relation schema R is said to be in3NF if there is no non-prime attribute A in R for which A is transitively dependent on a key for R.We can also rewrite the deﬁnition of2NF given here as:“A relation schema R is in2NF if no non-prime attribute A is partially dependent on any candidate key for R.”To prove that every3NF schema is in2NF,it sufﬁces to show that if a non-prime attribute A is partially dependent on a candidate key␣,thenA is also transitively dependent on the key␣.Let A be a non-prime attribute in R.Let␣be a candidate key for R.Suppose A is partially dependent on␣.•From the deﬁnition of a partial dependency,we know that for someproper subset␤of␣,␤→A.•Since␤⊂␣,␣→␤.Also,␤→␣does not hold,since␣is acandidate key.•Finally,since A is non-prime,it cannot be in either␤or␣.Thus we conclude that␣→A is a transitive dependency.Hence we have proved that every3NF schema is also in2NF.8.18Give an example of a relation schema R and a set of dependencies suchthat R is in BCNF but is not in4NF.18Chapter8Relational Database DesignAnswer:R(A,B,C)A→→BExercises19result:=∅;/*fdcount is an array whose i th element contains the number of attributes on the left side of the i th FD that arenot yet known to be in␣+*/for i:=1to|F|dobeginlet␤→␥denote the i th FD;fdcount[i]:=|␤|;end/*appears is an array with one entry for each attribute.The entry for attribute A is a list of integers.Each integeri on the list indicates that A appears on the left sideof the i th FD*/for each attribute A dobeginappears[A]:=NI L;for i:=1to|F|dobeginlet␤→␥denote the i th FD;if A∈␤then add i to appears[A];endendaddin(␣);return(result);procedure addin(␣);for each attribute A in␣dobeginif A∈result thenbeginresult:=result∪{A};for each element i of appears[A]dobeginfdcount[i]:=fdcount[i]−1;if fdcount[i]:=0thenbeginlet␤→␥denote the i th FD;addin(␥);endendendendFigure8.18.An algorithm to compute␣+.。

数据库系统概念答案

数据库系统概念答案1. 什么是数据库系统？数据库系统是指由数据库、数据库管理系统（DBMS）和应用程序组成的系统。

它用于存储、管理和操作数据，并提供了一种机制来管理和访问数据。

通过数据库系统，用户可以方便地存储和检索数据，实现数据的共享和保护。

2. 数据库系统的三级模式结构是什么？数据库系统的三级模式结构是指外模式、概念模式和内模式。

•外模式是用户与数据库系统交互的第一层，它描述了用户如何看待和访问数据库的部分内容。

每个用户可以定义自己的外模式，以满足其特定的信息需求。

•概念模式是数据库系统的第二层，它描述了整个数据库的逻辑结构和内容。

概念模式是一个全局的、相对独立的视图，用于描述数据库中的数据、数据之间的关系以及约束条件。

•内模式是数据库系统的最低级别，它描述了数据在存储介质上的物理表示和存储结构。

内模式定义了数据的存储方式、索引方式以及与物理存储相关的相关细节。

3. 什么是数据模型？数据模型是用于描述和表示现实世界中的对象、关系和约束的一种工具。

它提供了一种方式来组织数据、定义数据的结构和操作数据的规则。

常见的数据模型包括层次模型、网状模型、关系模型和对象模型等。

其中，关系模型是目前应用最广泛的一种数据模型，它使用表格（关系）来表示实体和实体之间的关系。

4. 数据库系统的特点有哪些？数据库系统具有以下特点：•数据独立性：数据库系统实现了数据和程序的分离，数据的物理存储和逻辑存储是独立的，用户可以通过外模式访问数据，而不必关心数据如何存储。

•数据共享：数据库系统允许多个用户共享数据库中的数据，不同的用户可以通过不同的外模式访问相同的数据，提高了数据的共享和使用效率。

•数据一致性：数据库系统可以保证数据的一致性，通过事务机制和数据完整性约束来确保数据的正确性和一致性。

•数据安全性：数据库系统提供了一系列的安全性机制，如用户认证、权限控制和数据加密等，以保护数据的安全和完整性。

5. 数据库设计的步骤是什么？数据库设计是指根据用户需求和系统要求，设计出满足这些需求的数据库结构和内容的过程。

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

Chapter 26: Advanced Transam Concepts, 6th Ed.
©Silberschatz, Korth and Sudarshan See for conditions on re-use
With the growth of networks, and the existence of multiple autonomous
database systems, workflows provide a convenient way of carrying out tasks that involve multiple systems.
Provide infrastructure for building and administering complex transaction
processing systems with a large number of clients and multiple servers.
Provide services such as:
Some commercial TP monitors: CICS from IBM, Pathway from Tandem,
Top End from NCR, and Encina from Transarc
Database System Concepts - 6th Edition
26.3
©Silberschatz, Korth and Sudarshan
TP Monitor Architectures
Database System Concepts - 6th Edition
26.4
©Silberschatz, Korth and Sudarshan

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

C H A P T E R21Information RetrievalPractice Exercises21.1Compute the relevance(using appropriate deﬁnitions of term fre-quency and inverse document frequency)of each of the Practice Ex-ercises in this chapter to the query“SQL relation”.Answer:We do not consider the questions containing neither of thekeywords as their relevance to the keywords is zero.The number ofwords in a question include stop words.We use the equations givenin Section21.2to compute relevance;the log term in the equation isassumed to be to the base2.21.2Suppose you want toﬁnd documents that contain at least k of a givenset of n keywords.Suppose also you have a keyword index that givesyou a(sorted)list of identiﬁers of documents that contain a speciﬁedkeyword.Give an efﬁcient algorithm toﬁnd the desired set of docu-ments.Answer:Let S be a set of n keywords.An algorithm toﬁnd all docu-ments that contain at least k of these keywords is given below:12Chapter21Information RetrievalThis algorithm calculates a reference count for each document identi-ﬁer.A reference count of i for a document identiﬁer d means that atleast i of the keywords in S occur in the document identiﬁed by d.The algorithm maintains a list of records,each having twoﬁelds–adocument identiﬁer,and the reference count for this identiﬁer.This listis maintained sorted on the document identiﬁerﬁeld.initialize the list L to the empty list;for(each keyword c in S)dobeginD:=the list of documents identiﬁers corresponding to c;for(each document identiﬁer d in D)doif(a record R with document identiﬁer as d is on list L)thenR.re f erence count:=R.re f erence count+1;else beginmake a new record R;R.document id:=d;R.re f erence count:=1;add R to L;end;end;for(each record R in L)doif(R.re f erence count>=k)thenoutput R;Note that execution of the second for statement causes the list D to“merge”with the list L.Since the lists L and D are sorted,the timetaken for this merge is proportional to the sum of the lengths of the twolists.Thus the algorithm runs in time(at most)proportional to n timesthe sum total of the number of document identiﬁers corresponding toeach keyword in S.21.3Suggest how to implement the iterative technique for computing Page-Rank given that the T matrix(even in adjacency list representation)does notﬁt in memory.Answer:No answer21.4Suggest how a document containing a word(such as“leopard”)canbe indexed such that it is efﬁciently retrieved by queries using a moregeneral concept(such as“carnivore”or“mammal”).You can assumethat the concept hierarchy is not very deep,so each concept has onlya few generalizations(a concept can,however,have a large number ofspecializations).You can also assume that you are provided with a func-tion that returns the concept for each word in a document.Also suggesthow a query using a specialized concept can retrieve documents usinga more general concept.Answer:Add doc to index lists for more general concepts also.Practice Exercises3 21.5Suppose inverted lists are maintained in blocks,with each block not-ing the largest popularity rank and TF-IDF scores of documents in the remaining blocks in the list.Suggest how merging of inverted lists can stop early if the user wants only the top K answers.Answer:For all documents whose scores are not complete use upper bounds to compute best possible score.If K th largest completed score is greater than the largest upper bound among incomplete scores output the K top answers.No answer。

数据库系统原理版课后习题参考答案

数据库系统原理版课后习题参考答案答案仅供参考第一章数据库系统概述选择题B、B、A简答题1.请简述数据,数据库,数据库管理系统,数据库系统的概念。

P27数据是描述事物的记录符号，是指用物理符号记录下来的，可以鉴别的信息。

数据库即存储数据的仓库，严格意义上是指长期存储在计算机中的有组织的、可共享的数据集合。

数据库管理系统是专门用于建立和管理数据库的一套软件，介于应用程序和操作系统之间。

数据库系统是指在计算机中引入数据库技术之后的系统，包括数据库、数据库管理系统及相关实用工具、应用程序、数据库管理员和用户。

2.请简述早数据库管理技术中，与人工管理、文件系统相比，数据库系统的优点。

数据共享性高数据冗余小易于保证数据一致性数据独立性高可以实施统一管理与控制减少了应用程序开发与维护的工作量3.请简述数据库系统的三级模式和两层映像的含义。

P31答：数据库的三级模式是指数据库系统是由模式、外模式和内模式三级工程的，对应了数据的三级抽象。

两层映像是指三级模式之间的映像关系，即外模式/模式映像和模式/内模式映像。

4.请简述关系模型与网状模型、层次模型的区别。

P35使用二维表结构表示实体及实体间的联系建立在严格的数学概念的基础上概念单一，统一用关系表示实体和实体之间的联系，数据结构简单清晰，用户易懂易用存取路径对用户透明，具有更高的数据独立性、更好的安全保密性。

．第二章关系数据库选择题C、C、D简答题1.请简述关系数据库的基本特征。

P48答：关系数据库的基本特征是使用关系数据模型组织数据。

2.请简述什么是参照完整性约束。

P55答：参照完整性约束是指：若属性或属性组F是基本关系R的外码，与基本关系S的主码K相对应，则对于R中每个元组在F上的取值只允许有两种可能，要么是空值，要么与S中某个元组的主码值对应。

3.请简述关系规范化过程。

答：对于存在数据冗余、插入异常、删除异常问题的关系模式，应采取将一个关系模式分解为多个关系模式的方法进行处理。

数据库系统概念(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)。

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

C H A P T E R17Database System ArchitecturesPractice Exercises17.1Instead of storing shared structures in shared memory,an alternativearchitecture would be to store them in the local memory of a specialprocess,and access the shared data by interprocess communicationwith the process.What would be the drawback of such an architecture?Answer:The drawbacks would be that two interprocess messageswould be required to acquire locks,one for the request and one toconﬁrm grant.Interprocess communication is much more expensivethan memory access,so the cost of locking would increase.The processstoring the shared structures could also become a bottleneck.The beneﬁt of this alternative is that the lock table is protected betterfrom erroneous updates since only one process can access it.17.2In typical client–server systems the server machine is much more pow-erful than the clients;that is,its processor is faster,it may have multipleprocessors,and it has more memory and disk capacity.Consider in-stead a scenario where client and server machines have exactly thesame power.Would it make sense to build a client–server system insuch a scenario?Why?Which scenario would be better suited to adata-server architecture?Answer:With powerful clients,it still makes sense to have a client-server system,rather than a fully centralized system.If the data-serverarchitecture is used,the powerful clients can off-load all the long andcompute intensive transaction processing work from the server,freeingit to perform only the work of satisfying read-write requests.even ifthe transaction-server model is used,the clients still take care of theuser-interface work,which is typically very compute-intensive.A fully distributed system might seem attractive in the presence ofpowerful clients,but client-server systems still have the advantage ofsimpler concurrency control and recovery schemes to be implemented1718Chapter17Database System Architectureson the server alone,instead of having these actions distributed in allthe machines.17.3Consider a database system based on a client–server architecture,withthe server acting as a data server.a.What is the effect of the speed of the interconnection betweenthe client and the server on the choice between tuple and pageshipping?b.If page shipping is used,the cache of data at the client can beorganized either as a tuple cache or a page cache.The page cachestores data in units of a page,while the tuple cache stores data inunits of tuples.Assume tuples are smaller than pages.Describeone beneﬁt of a tuple cache over a page cache.Answer:a.We assume that tuples are smaller than a page andﬁt in a page.If the interconnection link is slow it is better to choose tuple ship-ping,as in page shipping a lot of time will be wasted in shippingtuples that might never be needed.With a fast interconnectionthough,the communication overheads and latencies,not the ac-tual volume of data to be shipped,becomes the bottle neck.Inthis scenario page shipping would be preferable.b.Two beneﬁts of an having a tuple-cache rather than a page-cache,even if page shipping is used,are:i.When a client runs out of cache space,it can replace objectswithout replacing entire pages.The reduced caching granu-larity might result in better cache-hit ratios.ii.It is possible for the server to ask clients to return some ofthe locks which they hold,but don’t need(lock de-escalation).Thus there is scope for greater concurrency.If page caching isused,this is not possible.17.4Suppose a transaction is written in C with embedded SQL,and about80percent of the time is spent in the SQL code,with the remaining20percent spent in C code.How much speedup can one hope to attain ifparallelism is used only for the SQL code?Explain.Answer:Since the part which cannot be parallelized takes20%of thetotal running time,the best speedup we can hope for has to be less than5.17.5Some database operations such as joins can see a signiﬁcant differencein speed when data(for example,one of the relations involved in ajoin)ﬁts in memory as compared to the situation where the data doesnotﬁt in memory.Show how this fact can explain the phenomenonof superlinear speedup,where an application sees a speedup greaterthan the amount of resources allocated to it.Answer:FILLPractice Exercises19 17.6Parallel systems often have a network structure where sets of n pro-cessors connect to a single Ethernet switch,and the Ethernet switches themselves connect to another Ethernet switch.Does this architecture correspond to a bus,mesh or hypercube architecture?If not,how would you describe this interconnection architecture?Answer:FILL。

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

数据库系统概念课后习题答案SolutionstoPracticeExercises9s

数据库系统概念试题库及答案

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

数据库系统课后习题及答案

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

《数据库系统概论》课后习题及参考标准答案

数据库系统基础教程(第二版)课后习题答案2

数据库系统教程课后答案

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

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

数据库系统概论第五版课后习题答案之欧阳治创编

数据库系统概论（第四版）答案

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

数据库系统概念答案

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

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

数据库系统原理版课后习题参考答案

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

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

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

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

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

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

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

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

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

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