资源目录
压缩包内文档预览：
编号：211097282
类型：共享资源
大小：12.43MB
格式：ZIP
上传时间：2022-05-05
上传人：考****
认证信息
个人认证
武**（实名认证）
山西
IP属地：山西
30
积分
- 关 键 词：
-
数据库系统概念第六版
数据库
系统
概念
第六
配套
课后
习题
答案
- 资源描述：
-
《数据库系统概念第六版》配套课后习题答案,数据库系统概念第六版,数据库,系统,概念,第六,配套,课后,习题,答案
- 内容简介：
-
CHAPTER4Intermediate SQLExercises4.1Write the following queries inSQL:a. Display a list of all instructors, showing theirID, name, and thenumber of sections that they have taught. Make sure to show thenumber of sections as 0 for instructors who have not taught anysection. Your query should use an outerjoin, and should not usescalar subqueries.b. Write the same query as above, but using a scalar subquery, with-out outerjoin.c. Display the list of all course sections offered in Spring 2010, alongwith the names of the instructors teaching the section. If a sectionhas more than one instructor, it should appear as many times inthe result as it has instructors. If it does not have any instructor, itshould still appear in the result with the instructor name set to “”.d. Display the list of all departments, with the total number of in-structors in each department, without using scalar subqueries.Make sure to correctly handle departments with no instructors.Answer:a. Display a list of all instructors, showing theirID, name, and thenumber of sections that they have taught. Make sure to show thenumber of sections as 0 for instructors who have not taught anysection. Your query should use an outerjoin, and should not usescalar subqueries.1112Chapter 4Intermediate SQLselectID, name,count(course id, section id, year,semester) as Number of sectionsfrom instructor natural left outer join teachesgroup byID, nameThe above query should not be written using count(*) since count* counts null values also. It could be written using count(sectionid), or any other attribute from teaches which does not occur ininstructor, which would be correct although it may be confusingtothe reader.(Attributesthat occur in instructor would not be nulleven if the instructor has not taught any section.)b. Write the same query as above, but using a scalar subquery, with-out outerjoin.selectID, name,(select count(*) as Number of sectionsfrom teaches T where T.id = I.id)from instructor Ic. Display the list of all course sections offered in Spring 2010, alongwith the names of the instructors teaching the section. If a sectionhas more than one instructor, it should appear as many times inthe result as it has instructors. If it does not have any instructor,it should still appear in the result with the instructor name set to“”.select course id, section id,ID,decode(name, NULL, , name)from (section natural left outer join teaches)natural left outer join instructorwhere semester=Spring and year= 2010The query may also be written using the coalesce operator, byreplacing decode(.) by coalesce(name, ). A more complex ver-sion of the query can be written using union of join result withanother query that uses a subquery to find courses that do notmatch; refer to exercise 4.2.d. Display the list of all departments, with the total number of in-structors in each department, without using scalar subqueries.Make sure to correctly handle departments with no instructors.select dept name, count(ID)from department natural left outer join instructorgroup by dept name4.2Outer join expressions can be computed inSQLwithout using theSQLouter join operation. To illustrate this fact, show how to rewrite each ofthe followingSQLqueries without using the outer join expression.a. select * from student natural left outer join takesExercises13b. select * from student natural full outer join takesAnswer:a. select * from student natural left outer join takescan be rewritten as:select * from student natural join takesunionselectID, name, dept name, tot cred, NULL, NULL, NULL, NULL, NULLfrom student S1 where not exists(selectIDfrom takes T1 where T1.id = S1.id)b. select * from student natural full outer join takescan be rewritten as:(select * from student natural join takes)union(selectID, name, dept name, tot cred, NULL, NULL, NULL, NULL, NULLfrom student S1where not exists(selectIDfrom takes T1 where T1.id = S1.id)union(selectID, NULL, NULL, NULL, course id, section id, semester, year, gradefrom takes T1where not exists(selectIDfrom student S1 whereT1.id = S1.id)4.3Suppose we have three relations r(A, B), s(B, C), and t(B, D), with allattributes declared as not null. Consider the expressions r natural left outer join (s natural left outer join t), and (r natural left outer join s) natural left outer join ta. Give instances of relations r, s and t such that in the result of thesecondexpression,attributeC hasanullvaluebut attribute Dhasa non-null value.b. Is the above pattern, with C null and D not null possible in theresult of the first expression? Explain why or why not.Answer:a. Consider r = (a,b), s = (b1,c1), t = (b,d). The second expressionwould give (a,b,NULL,d).b. It is not possible for Dto be not null while C is null in the result ofthe first expression, since in the subexpression s natural left outerjoin t, it is not possible for C to be null while D is not null. In theoverall expression C can be null if and only if some r tuple does14Chapter 4Intermediate SQLnot have amatching B valueins. Howeverinthis case Dwill alsobe null.4.4TestingSQLqueries: To test if a query specified in English has beencorrectly written inSQL, theSQLquery is typically executed on multipletest databases, and a human checks if theSQLquery result on each testdatabase matches the intention of the specification in English.a. InSection?wesawanexampleofanerroneousSQLquerywhichwas intended to find which courses had been taught by each in-structor; the query computed the natural join of instructor, teaches,and course, and as a result unintentionally equated the dept nameattributeofinstructorandcourse.Giveanexampleofadatasetthatwould help catch this particular error.b. When creating test databases, it is important to create tuples inreferenced relations that do not have any matching tuple in thereferencing relation, for each foreign key. Explain why, using anexample query on the university database.c. When creating test databases, it is important to create tuples withnull values for foreign key attributes, provided the attribute isnullable (SQLallows foreign key attributes to take on null values,as long as they are not part of the primary key, and have not beendeclared as not null). Explain why, using an example query onthe university database.Hint: use the queries from Exercise ?.Answer:a. ConsiderthecasewhereaprofessorinPhysicsdepartmentteachesan Elec. Eng. course. Even though there is a valid correspondingentry in teaches, it is lost in the natural join of instructor, teachesand course, since the instructors departmentname does not matchthe department name of the course. A dataset corresponding tothe same is:instructor = (12345,Guass, Physics, 10000)teaches = (12345, EE321, 1, Spring, 2009)course = (EE321, Magnetism, Elec. Eng., 6)b. The query in question 0.a is a good example for this. Instructorswho have not taught a single course, should have number ofsections as 0 in the query result. (Many other similar examplesare possible.)c. Consider the queryselect * from teaches natural join instructor;In the above query, we would lose some sections if teaches.IDis allowed to be NULL and such tuples exist. If, just becauseExercises15teaches.IDis a foreign key to instructor, we did not create such atuple, the error in the above query would not be detected.4.5Show how to define the view student grades (ID, GPA) giving the grade-point average of each student, based on the query in Exercise ?; recallthatweusedarelationgrade points(grade,points)togetthenumericpointsassociated with a letter grade. Make sure your view definition correctlyhandlesthecase ofnullvaluesforthegrade attributeofthe takesrelation.Answer: Weshouldnotaddcreditsforcourseswithanullgrade;furtherto to correctly handle the case where a student has not completed anycourse,weshouldmakesurewedontdividebyzero,andshouldinsteadreturn a null value.We break the query into a subquery that finds sum of credits and sumof credit-grade-points, taking null grades into account The outer querydivides the above to get the average, taking care of divide by 0.create view student grades(ID, GPA) asselectID, credit points / decode(credit sum, 0, NULL, credit sum)from (selectID, sum(decode(grade, NULL, 0, credits) as credit sum,sum(decode(grade, NULL, 0, credits*points) as credit pointsfrom(takes natural join course) natural left outer join grade pointsgroup byID)unionselectID, NULLfromstudentwhereIDnot in (selectIDfrom takes)The view defined above takes care of NULL grades by consideringthe creditpoints to be 0, and not adding the corresponding credits incredit sum.The query above ensures that if the student has not taken any coursewith non-NULL credits, and has credit sum = 0 gets a gpa of NULL. Thisavoid the division by 0, which would otherwise have resulted.An alternative way of writing the above query would be to use studentnatural left outer join gpa, in order to consider students who have nottaken any course.4.6Complete theSQL DDLdefinition of the university database of Figure ?to include the relations student, takes, advisor, and prereq.Answer:16Chapter 4Intermediate SQLcreate table student(IDvarchar (5),namevarchar (20) not null,dept namevarchar (20),tot crednumer
- 温馨提示:
1: 本站所有资源如无特殊说明，都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
2: 本站的文档不包含任何第三方提供的附件图纸等，如果需要附件，请联系上传者。文件的所有权益归上传用户所有。
3.本站RAR压缩包中若带图纸，网页内容里面会有图纸预览，若没有图纸预览就没有图纸。
4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
5. 人人文库网仅提供信息存储空间，仅对用户上传内容的表现方式做保护处理，对用户上传分享的文档内容本身不做任何修改或编辑，并不能对任何下载内容负责。
6. 下载文件中如有侵权或不适当内容，请与我们联系，我们立即纠正。
7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。
人人文库网所有资源均是用户自行上传分享，仅供网友学习交流，未经上传用户书面授权，请勿作他用。
川公网安备: 51019002004831号