资源目录
压缩包内文档预览：
编号：211097282
类型：共享资源
大小：12.43MB
格式：ZIP
上传时间：2022-05-05
上传人：考****
认证信息
个人认证
武**（实名认证）
山西
IP属地：山西
30
积分
- 关 键 词：
-
数据库系统概念第六版
数据库
系统
概念
第六
配套
课后
习题
答案
- 资源描述：
-
《数据库系统概念第六版》配套课后习题答案,数据库系统概念第六版,数据库,系统,概念,第六,配套,课后,习题,答案
- 内容简介：
-
CHAPTER5Advanced SQLPractice Exercises5.1Describe the circumstances in which you would choose to use embed-dedSQLrather thanSQLalone or only a general-purpose programminglanguage.Answer:Writing queries inSQLis typically much easier than codingthesamequeriesinageneral-purposeprogramminglanguage.HowevernotallkindsofqueriescanbewritteninSQL.Alsonondeclarativeactionssuch as printing a report, interacting with a user, or sending the resultsof a query to a graphical user interface cannot be done from withinSQL.Under circumstances in which we want the best of both worlds, we canchoose embeddedSQLor dynamicSQL, rather than usingSQLalone orusing only a general-purpose programming language.EmbeddedSQLhas the advantage of programs being less complicatedsince it avoids the clutter of theODBCorJDBCfunction calls, but requiresa specialized preprocessor.5.2Write a Java function usingJDBCmetadata features that takes aResult-Setasaninputparameter,andprintsouttheresultintabularform,withappropriate names as column headings.Answer:public class ResultSetTable implements TabelModel ResultSet result;ResultSetMetaData metadata;int num cols;ResultSetTable(ResultSet result) throws SQLException this.result = result;metadata = result.getMetaData();num cols = metadata.getColumnCount();for(int i = 1; i = num cols; i+) System.out.print(metadata.getColumnName(i) + );12Chapter 5Advanced SQLSystem.out.println();while(result.next() for(int i = 1; i = num cols; i+) System.out.print(result.getString(metadata.getColumnName(i) + );System.out.println();5.3Write a Java function usingJDBCmetadata features that prints a list ofall relations in the database, displaying for each relation the names andtypes of its attributes.Answer:DatabaseMetaData dbmd = conn.getMetaData();ResultSet rs = dbmd.getTables();while (rs.next() System.out.println(rs.getString(TABLE NAME);ResultSet rs1 = dbmd.getColumns(null, schema-name,rs.getString(TABLE NAME), %);while (rs1.next() System.out.println(rs1.getString(COLUMN NAME),rs.getString(TYPE NAME);5.4Show how to enforce the constraint “an instructor cannot teach in twodifferent classrooms in a semester in the same time slot.” using a trigger(remember that the constraint can be violated by changes to the teachesrelation as well as to the section relation).Answer: FILL5.5Write triggers to enforce the referential integrity constraint from sectionto time slot, on updates to section, and time slot. Note that the ones wewrote in Figure 5.8 do not cover the update operation.Answer: FILL5.6To maintain the tot cred attribute of the student relation, carry out thefollowing:a.Modify the trigger on updates of takes, to handle all updates thatcan affect the value of tot cred.b.Write a trigger to handle inserts to the takes relation.Exercises3c.Under what assumptions is it reasonable not to create triggers onthe course relation?Answer: FILL5.7ConsiderthebankdatabaseofFigure5.25.Letusdefineaviewbranch custas follows:create view branch cust asselect branch name, customer namefrom depositor, accountwhere depositor.account number = account.account numberSuppose that the view is materialized; that is, the view is computed andstored. Write triggers to maintain the view, that is, to keep it up-to-dateon insertions to and deletions from depositor or account. Do not botherabout updates.Answer:For inserting into the materialized view branch cust we mustset a database trigger on an insert into depositor and account. We assumethat the database system uses immediate binding for rule execution. Fur-ther, assume that the current version of a relation is denoted by therelation name itself, while the set of newly inserted tuples is denoted byqualifying the relation name with the prefix inserted.The active rules for this insertion are given below define trigger insert into branch cust via depositorafter insert on depositorreferencing new table as inserted for each statementinsert into branch custselect branch name, customer namefrom inserted, accountwhere inserted.account number = account.account numberdefine trigger insert into branch cust via accountafter insert on accountreferencing new table as inserted for each statementinsert into branch custselect branch name, customer namefrom depositor, insertedwhere depositor.account number = inserted.account numberNote that if the execution binding was deferred (instead of immediate),then the result of the join of the set of new tuples of account with the setof new tuples of depositor would have been inserted by both active rules,leading to duplication of the corresponding tuples in branch cust.The deletion of a tuple from branch cust is similar to insertion, exce ptthat a deletion from either depositor or account will cause the natural joinof theserelationstohave alessernumber of tuples.Wedenotethe newly4Chapter 5Advanced SQLdeleted set of tuples by qualifying the relation name with the keyworddeleted.define trigger delete from branch cust via depositorafter delete on depositorreferencing old table as deleted for each statementdelete from branch custselect branch name, customer namefrom deleted, accountwhere deleted.account number = account.account numberdefine trigger delete from branch cust via accountafter delete on accountreferencing old table as deleted for each statementdelete from branch custselect branch name, customer namefrom depositor, deletedwhere depositor.account number = deleted.account number5.8Consider the bank database of Figure 5.25. Write anSQLtrigger to carryout the following action: On delete of an account, for each owner of theaccount, check if the owner has any remaining accounts, and if she doesnot, delete her from the depositor relation.Answer:create trigger check-delete-trigger after delete on accountreferencing old row as orowfor each rowdelete from depositorwhere depositor.customer name not in( select customer name from depositorwhere account number orow.account number )end5.9Showhowtoexpressgroupbycube(a,b,c,d)usingrollup;youranswershould have only one group by clause.Answer:groupby rollup(a), rollup(b), rollup(c ), rollup(d)5.10Given a relation S(student,subject,marks), write a query to find the topn students by total marks, by using ranking.Answer: Weassumethatmultiplestudentsdonot havethesamemarkssince otherwise the question is not deterministic; the query below
- 温馨提示:
1: 本站所有资源如无特殊说明，都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
2: 本站的文档不包含任何第三方提供的附件图纸等，如果需要附件，请联系上传者。文件的所有权益归上传用户所有。
3.本站RAR压缩包中若带图纸，网页内容里面会有图纸预览，若没有图纸预览就没有图纸。
4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
5. 人人文库网仅提供信息存储空间，仅对用户上传内容的表现方式做保护处理，对用户上传分享的文档内容本身不做任何修改或编辑，并不能对任何下载内容负责。
6. 下载文件中如有侵权或不适当内容，请与我们联系，我们立即纠正。
7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。
人人文库网所有资源均是用户自行上传分享，仅供网友学习交流，未经上传用户书面授权，请勿作他用。
川公网安备: 51019002004831号