数据库处理课后习题答案

1、数据库处理复习要点及参考答案最近更新时间：7/21/2020第一章 Microsoft Access 2007（第一次作业）3复习要点31.6131.6231.6351.6451.6561.6761.6871.6981.7081.719第二章 结构化查询语言简介（第二次作业）10复习要点102.62102.6317Marcia 干洗店项目练习20第三章 关系模型和规范化（第三次作业）28复习重点283.58283.5931Marcia 干洗店项目练习36第四章45复习重点45第五章 E-R图（第四次作业）45复习重点455.6345Marcia 干洗店项目53第六章 数据库设计（第五次作业）5

2、6复习重点566.5656Marcia 洗衣店项目练习58第七章 数据库创建、视图、触发器（第六次作业）61复习重点617.5617.10627.15627.20637.25647.3565Marcia干洗店项目66第十章 用SQL Server 2008管理数据库触发器、存储过程83第八章 数据库再设计83第九章 管理多用户数据库85第一章 Microsoft Access 2007（第一次作业）复习要点(1). 知识网络图图1.18(2). 基本的定义：a. DBS:=用户+数据库应用程序+DBMS+DB。 各个部分有什么作用？b. 元数据metadata (3). Access 的使用-

3、 作业1.61Create a Microsoft Access database named WPC.accdb.Answers to the Project Questions 1.61-1.70 are contained in the database DBPe11-IM-Ch01-WPC.accdb, which is available on the texts Web site (/kroenke).The database is created as described in Appendix A. The two tables t

4、o be created are:DEPARTMENT (DepartmentName, BudgetCode, OfficeNumber, Phone)EMPLOYEE (EmployeeNumber, FirstName, LastName, Department, Phone, Email)Where An underlined column name indicates the table key (primary key) of the table, and an italicized column indicates a foreign key linking two tables

5、.1.62Figure 1-26 shows the column characteristics for the WPC DEPARTMENT table. Using the column characteristics, create the DEPARTMENT table in the WPC.accdb database.1.63Figure 1-27 shows the data for the WPC DEPARTMENT table. Using Datasheet view, enter the data shown in Figure 1-27 into your DEP

6、ARTMENT table.1.64Figure 1-28 shows the column characteristics for the WPC EMPLOYEE table. Using the column characteristics, create the EMPLOYEE table in the WPC.accdb database.1.65Create the relationship and referential integrity constraint between DEPARTMENT and EMPLOYEE. Enable enforcing of refer

7、ential integrity and cascading of data updates, but do not enable cascading of data from deleted records.1.67Using the Microsoft Access form wizard, create a data input form for the EMPLOYEE table and name it WPC Employee Data Form. Make any adjustments necessary to the form so that all data display

8、 properly. Use this form to enter the rest of the data in the EMPLOYEE table shown in Figure 1-29 into your EMPLOYEE table.1.68Using the Access report wizard, create a report named Wedgewood Pacific Corporation Employee Report that presents the data contained in your EMPLOYEE table sorted first by e

9、mployee last name and then by employee first name. Make any adjustments necessary to the report so that all headings and data display properly. Print a copy of this report.To produce the report as shown below, some work in the Report Design view is necessary take the time to show your students how t

10、o modify report formats in Report Design view.1.69Using the Microsoft Access form wizard, create a form that has all of the data from both tables. When asked how you want to view your data, select by DEPARTMENT. Choose the default options for other questions that the wizard asks. Open your form and

11、page through your departments.To produce the report as shown below, some work in the Form Design view is necessary take the time to show your students how to modify report formats in Form Design view.1.70Using the Access report wizard, create a report that has all of the data from both tables. When

12、asked how you want to view your data, select by DEPARTMENT. For the data contained in your EMPLOYEE table in the report, specify that it will be sorted first by employee last name and then by employee first name. Make any adjustments necessary to the report so that all headings and data display prop

13、erly. Print a copy of this report.To produce the report as shown below, some work in the Report Design view is necessary take the time to show your students how to modify report formats in Report Design view.1.71Explain, to the level of detail in this chapter, what is going on within Microsoft Acces

14、s in Project Questions 1.67, 1.68, 1.69, and 1.70. What subcomponent created the form and report? Where is the data stored? What role do you think SQL is playing?Access uses SQL SELECT statements to query the database tables for the data to be displayed in the forms and the report. The results of th

15、e query are stored in a temporary table created to hold this data, and this table is the source of the data displayed in the form and the report. SQL is used to gather the data needed for display in the form and report.第二章 结构化查询语言简介（第二次作业）复习要点(1). 定义DDL、DML。p32.(2). SQL 的写法 - 基本、重要。SELECT.FROM.WHERE

16、.ORDER BY.GROUP BY.HAVING.IN.EXISTS.JOIN.ON(3). 难点：多表连接、相关子查询、谓词计算(4). 发现数据模式- 动脑、扩展。 例如题目2.63.(5). 实验教材。2.62 A. The ChangeClose on Fridays.SELECT ChangeCloseFROM NDXWHERETDayOfWeeK = Friday;B. The minimum, maximum, and average ChangeClose on Fridays.SELECT MIN (ChangeClose) AS MinFridayChangeClose,

17、MAX (ChangeClose) AS MaxFridayChangeClose,AVG (ChangeClose) AS AverageFridayChangeCloseFROM NDXWHERETDayOfWeeK = Friday; C. The average ChangeClose grouped by TYear. Show TYear.SELECT TYear, AVG (ChangeClose) AS AverageChangeCloseFROM NDXGROUP BYTYearORDER BY TYear; D. The average ChangeClose groupe

18、d by TYear and TMonth. Show TYear and TMonth.Since TYear and TMonth are being displayed, it makes sense to sort the results by TYear and TMonth although this is not explicitly stated in the question.SELECT TYear, TMonth,AVG (ChangeClose) AS AverageChangeCloseFROM NDXGROUP BYTYear, TMonthORDER BY TYe

19、ar, TMonth; Unfortunately, the table NDX does not contain a numeric value of the month, so in order to sort the months correctly, we need a TMonthNumber which has a column containing a representative number for each month (January = 1, February = 2, etc.). In the DBPe11-NDX.accdb and DBPe11-IM-Ch02-

20、NDX.accdb databases, this column is included in a table named NDX_FULL.SELECT TYear, TMonth,AVG (ChangeClose) AS AverageFridayChangeCloseFROM NDX_FullGROUP BYTYear, TMonth, TMonthNumberORDER BY TYear, TMonthNumber; E. The average ChangeClose grouped by TYear, TQuarter, TMonth shown in descending ord

21、er of the average (you will have to give a name to the average in order to sort by it). Show TYear, TQuarter, and TMonth. Note that months appear in alphabetical and not calendar order. Explain what you need to do to obtain months in calendar order.SELECT TYear, TQuarter, TMonth,AVG (ChangeClose) AS

22、 AverageChangeCloseFROM NDXGROUP BYTYear, TQuarter, TMonthORDER BY AverageChangeClose DESC;Unfortunately, as discussed above, Microsoft Access cannot process the ORDER BY clause correctly when an SQL built-in function is used.The correct result, obtained from SQL Server 2008, is:In order to obtain t

23、he months in calendar order, we would have to use a numerical value for each month (1, 2, 3, , 12) and sort by those values.F. The difference between the maximum ChangeClose and the minimum ChangeClose grouped by TYear, TQuarter, TMonth shown in descending order of the difference (you will have to g

24、ive a name to the difference in order to sort by it). Show TYear, TQuarter, and TMonth.SELECT TYear, TQuarter, TMonth,(MAX (ChangeClose) MIN(ChangeClose) AS DifChangeCloseFROM NDXGROUP BYTYear, TQuarter, TMonthORDER BY DifChangeClose DESC;Unfortunately, as discussed above, Microsoft Access cannot pr

25、ocess the ORDER BY clause correctly because it contains an aliased computed result .The correct result, obtained from SQL Server 2008, is:G. The average ChangeClose grouped by TYear shown in descending order of the average (you will have to give a name to the average in order to sort by it). Show on

26、ly groups for which the average is positive.SELECT TYear, AVG (ChangeClose) AS AverageChangeCloseFROM NDXGROUP BY TYearHAVING AVG (ChangeClose) 0ORDER BY AverageChangeClose DESC;Unfortunately, as discussed abve, Microsoft Access cannot process the ORDER BY clause correctly because it contains an ali

27、ased computed result.The correct result, obtained from SQL Server 2008, is:H. Display a single field with the date in the form: day/monthy/year. Do not be concerned with trailing blanks.The solution to this question requires the student to use the DBMS help function or other references to figure out

28、 a conversion function to convert the numerical day of the month to a character string that can be combined with other data already in character format.The table NDX does not have a numeric value for month, so the names of the months will appear in the solution. If we want the numeric value of the m

29、onth, we could use the NDX_Full table, which has a numeric value. We would need to use the data type conversion on this field as well.The SQL Statement using SQL Server 2008 character string functions is:SELECT CAST (TDayOfMonth AS Char (2) + / + TMonth + / + TYear AS DisplayDateFROM NDXWHERE TDayOf

30、Month = 25AND TMonth = SeptemberAND TYear = 2001;The SQL Server 2008 result is:The SQL Statement using Microsoft Access 2007 character string functions is:SELECT CStr(TDayOfMonth) + / + TMonth + / +TYear AS DisplayDateFROM NDXWHERE NDX.TDayOfMonth =25AND NDX.TMonth =SeptemberAND NDX.TYear =2001;The

31、Microsoft Access 2007 result is: 2.63 It is possible that volume (the number of shares traded) has some correlation with the direction of the stock market. Use the SQL you have learned in this chapter to investigate that possibility. Develop at least five different SQL statements in your investigati

32、on.If volume is correlated with the direction of the stock market, this means that there should be either:(1) POSITIVE CORRELEATION: Higher volume when the market closes higher, or(2) NEGATIVE CORRELATION: Higher volume when the market closes lower.When does the market close higher? When NDX.ChangeC

33、lose is positive.SELECT TMonth, TDayOfMonth, TYear, ChangeCloseFROM NDXWHERE ChangeClose 0;When does the market close lower? When NDX.ChangeClose is negative.SELECT TMonth, TDayOfMonth, TYear, ChangeCloseFROM NDXWHERE ChangeClose 0;SELECT AVG (ChangeClose) AS AvgNegativeChangeFROM NDXWHERE ChangeClo

34、se 0;SELECT AVG (ChangeClose) AS AvgNegativeChange, AVG (Volume) AS AvgVolumeOnNegativeChangeFROM NDXWHERE ChangeClose 100ANDCUSTOMER.CustomerID = ORDER.CustomerNumber;E. List the Phone and FirstName of all customers whose first name starts with B.The correct SQL-92 statement, which uses the wildcar

35、d %, is:SELECT Phone, FirstNameFROM CUSTOMERWHEREFirstName LIKE B%;However, MS Access uses the wildcard *, which gives the following SQL statement:SELECT Phone, FirstNameFROM CUSTOMERWHEREFirstName LIKE B*;F. List the Phone and FirstName of all customers whose last name includes the characters, cat.

36、The correct SQL-92 statement, which uses the wildcard %, is:SELECT Phone, FirstNameFROM CUSTOMERWHERELastName LIKE %cat%;However, MS Access uses the wildcard *, which give the following SQL statement:SELECT Phone, FirstNameFROM CUSTOMERWHERELastName LIKE *cat*;G. List the Phone, FirstName, and LastN

37、ame for all customers whose second and third characters of phone number is 23.Note that since the phone numbers in this database include the area code, we are really finding phone numbers with 23 as the second and third numbers in the area code. We could, off course, write statements to find 23 in t

38、he prefix or in the 4-digit sequence portion of the phone number.The correct SQL-92 statement, which uses the wildcards % and _, is:SELECT Phone, FirstName, LastNameFROM CUSTOMERWHEREPhone LIKE _23%;However, MS Access uses the wildcards * and ?, which give the following SQL statement:SELECT Phone, F

39、irstName, LastNameFROM CUSTOMERWHEREPhone LIKE ?23*;H. Determine the maximum and minimum TotalAmounts.注意：由于ORDER 是SQL 保留字，必须用 括起来.SELECT MAX (TotalAmt) AS MaxTotalAmount,MIN (TotalAmt) AS MinTotalAmountFROM ORDER;I. Determine the average TotalAmount.注意：由于ORDER 是SQL 保留字，必须用 括起来.SELECT AVG (TotalAmt)

40、AS AvgTotalAmountFROM ORDER;J. Count the number of customers.SELECT Count (*)AS NumberOfCustomersFROM CUSTOMER;K. Group customers by LastName and then by FirstName.SELECT LastName, FirstNameFROM CUSTOMERGROUP BYLastName, FirstName;L. Count the number of customers having each combination of LastName

41、and FirstName.SELECT LastName, FirstName,COUNT (*) AS Last_First_Combination_CountFROM CUSTOMERGROUP BYLastName, FirstName;M. Show the FirstName and LastName of all customers who have had an order with TotalAmount greater than 100. Use a subquery. 普通子查询分层次实现。相关子查询。Present the results sorted by LastN

42、ame in ascending order and then FirstName in descending order.注意：由于ORDER 是SQL 保留字，必须用 括起来.SELECT FirstName, LastNameFROM CUSTOMERWHERECustomerID IN(SELECT CustomerNumberFROM ORDERWHERE TotalAmount 100)ORDER BYLastName, FirstName DESC;N. Show the FirstName and LastName of all customers who have had a

43、n order with TotalAmount greater than 100. Use a join. 连接的两种表示方法WhereJoin Present the results sorted by LastName in ascending order and then FirstName in descending order.注意：由于ORDER 是SQL 保留字，必须用 括起来.SELECT FirstName, LastNameFROM CUSTOMER, ORDERWHERECUSTOMER.CustomerID = ORDER.CustomerNumberANDTotal

44、Amount 100ORDER BYLastName, FirstName DESC;O. Show the FirstName and LastName of all customers who have had an order with an Item named “Dress Shirt”. Use a subquery.画出简易的ER图。 Present the results sorted by LastName in ascending order and then FirstName in descending order.注意：由于ORDER 是SQL 保留字，必须用 括起来

45、.SELECT FirstName, LastNameFROM CUSTOMERWHERECustomerID IN(SELECTCustomerNumberFROM ORDERWHERE InvoiceNumber IN (SELECT InvoiceNumberFROM ORDER_ITEMWHERE Item = Dress Shirta. 在哪个表)ORDER BYLastName, FirstName DESC;P. Show the FirstName and LastName of all customers who have had an order with an Item

46、named “Dress Shirt”. Use a join. Present the results sorted by LastName in ascending order and then FirstName in descending order.注意：由于ORDER 是SQL 保留字，必须用 括起来.SELECT FirstName, LastNameFROM CUSTOMER, ORDER, ORDER_ITEMWHERECUSTOMER.CustomerID = ORDER.CustomerNumberANDORDER.InvoiceNumber = ORDER_ITEM.I

47、nvoiceNumberANDORDER_ITEM.Item = Dress ShirtORDER BYLastName, FirstName DESC;Q. Show the FirstName, LastName and TotalAmount of all customers who have had an order with an Item named “Dress Shirt”. Use a join with a subquery. Present results sorted by LastName in ascending order and then FirstName i

48、n descending order.注意：由于ORDER 是SQL 保留字，必须用 括起来.SELECT FirstName, LastName, TotalAmountFROM CUSTOMER, ORDERWHERECUSTOMER.CustomerID = ORDER.CustomerNumberANDORDER.InvoiceNumber IN(SELECT InvoiceNumber FROM ORDER_ITEM WHERE Item = Dress Shirt)ORDER BYLastName, FirstName DESC;第三章 关系模型和规范化（第三次作业）复习重点(1). 定义：关系的术语。 图3.9(2). 从1NF- BCNFa. 1NF- 关系的特征-应用，指出哪里不满足。b. 2NF- 消除组合键。 非主属性由整个主键决定（不存在非主属性对候选键的部分依赖）。 R(A, B, N,O,P) 不能有 A-N。 没有非主属性，就是2NF。c. 3NF-消除传递依赖。非主属性由非主属性决定。R(A, B, N,O,P)， 不能有 N-Pd. BCNF- 每个决定因素都是候选键。- 将表转化为BCNF的过程。 图3.13 。 面向主题。(3). 4NF：不存在多值依赖。3.58Consider the