数据库处理课后习题答案.pdf

《数据库处理课后习题答案.pdf》由会员分享，可在线阅读，更多相关《数据库处理课后习题答案.pdf（122页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、数据库处理复习要点及参考答案 最近更新时间：7/25/2020 第一章 Microsoft Access 2007（第一次作业）.错误!未定义书签。复习要点.错误!未定义书签。.错误!未定义书签。.错误!未定义书签。.错误!未定义书签。.错误!未定义书签。.错误!未定义书签。.错误!未定义书签。.错误!未定义书签。.错误!未定义书签。.错误!未定义书签。.错误!未定义书签。第二章 结构化查询语言简介（第二次作业）.错误!未定义书签。复习要点.错误!未定义书签。.错误!未定义书签。.错误!未定义书签。Marcia 干洗店项目练习.错误!未定义书签。第三章 关系模型和规范化（第三次作业）.错误!未

2、定义书签。复习重点.错误!未定义书签。.错误!未定义书签。.错误!未定义书签。Marcia 干洗店项目练习.错误!未定义书签。第四章.错误!未定义书签。复习重点.错误!未定义书签。第五章 E-R 图（第四次作业）.错误!未定义书签。复习重点.错误!未定义书签。.错误!未定义书签。Marcia 干洗店项目.错误!未定义书签。第六章 数据库设计（第五次作业）.错误!未定义书签。复习重点.错误!未定义书签。.错误!未定义书签。Marcia 洗衣店项目练习.错误!未定义书签。第七章 数据库创建、视图、触发器（第六次作业）.错误!未定义书签。复习重点.错误!未定义书签。.错误!未定义书签。.错误!未定义

3、书签。.错误!未定义书签。.错误!未定义书签。.错误!未定义书签。.错误!未定义书签。Marcia 干洗店项目.错误!未定义书签。第十章 用 SQL Server 2008 管理数据库触发器、存储过程.错误!未定义书签。第八章 数据库再设计.错误!未定义书签。第九章 管理多用户数据库.错误!未定义书签。第一章 Microsoft Access 2007（第一次作业）复习要点(1).知识网络图 图(2).基本的定义：a.DBS:=用户+数据库应用程序+DBMS+DB。各个部分有什么作用 b.元数据 metadata (3).Access 的使用-作业 Create a Microsoft Acc

4、ess database named.Answers to the Project Questions are contained in the database,which is available on the texts Web site(database is created as described in Appendix A.The two tables to be created are:DEPARTMENT(DepartmentName,BudgetCode,OfficeNumber,Phone)EMPLOYEE(EmployeeNumber,FirstName,LastNam

5、e,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.Figure 1-26 shows the column characteristics for the WPC DEPARTMENT table.Using the column characteristics,create the DEPARTME

6、NT table in the database.Figure 1-27 shows the data for the WPC DEPARTMENT table.Using Datasheet view,enter the data shown in Figure 1-27 into your DEPARTMENT table.Figure 1-28 shows the column characteristics for the WPC EMPLOYEE table.Using the column characteristics,create the EMPLOYEE table in t

7、he database.Create the relationship and referential integrity constraint between DEPARTMENT and EMPLOYEE.Enable enforcing of referential integrity and cascading of data updates,but do not enable cascading of data from deleted records.Using the Microsoft Access form wizard,create a data input form fo

8、r the EMPLOYEE table and name it WPC Employee Data Form.Make any adjustments necessary to the form so that all data display properly.Use this form to enter the rest of the data in the EMPLOYEE table shown in Figure 1-29 into your EMPLOYEE table.Using the Access report wizard,create a report named We

9、dgewood Pacific Corporation Employee Report that presents the data contained in your EMPLOYEE table 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 properly.Print a copy of this report.To produce th

10、e 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.Using 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,sel

11、ect by DEPARTMENT.Choose the default options for other questions that the wizard asks.Open your form and 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 v

12、iew.Using the Access report wizard,create a report that has all of the data from both tables.When 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 firs

13、t 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 to modify report formats in Report Design view.

14、Explain,to the level of detail in this chapter,what is going on within Microsoft Access in Project Questions,and.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

15、 be displayed in the forms and the report.The results of the 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.第二章 结构化查询语言简介（第二次作业）复习要点

16、 (1).定义 DDL、DML。p32.(2).SQL 的写法-基本、重要。SELECT.FROM.WHERE.ORDER BY.GROUP BY.HAVING.IN.EXISTS.JOIN.ON(3).难点：多表连接、相关子查询、谓词计算(4).发现数据模式-动脑、扩展。例如题目.(5).实验教材。A.The ChangeClose on Fridays.SELECT ChangeClose FROM NDX WHERE TDayOfWeeK=Friday;B.The minimum,maximum,and average ChangeClose on Fridays.SELECT MIN(

17、ChangeClose)AS MinFridayChangeClose,MAX(ChangeClose)AS MaxFridayChangeClose,AVG(ChangeClose)AS AverageFridayChangeClose FROM NDX WHERE TDayOfWeeK=Friday;C.The average ChangeClose grouped by TYear.Show TYear.SELECT TYear,AVG(ChangeClose)AS AverageChangeClose FROM NDX GROUP BY TYear ORDER BY TYear;D.T

18、he average ChangeClose grouped 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 AverageChangeClose FROM NDX GROUP B

19、Y TYear,TMonth ORDER BY TYear,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 and databases,this col

20、umn is included in a table named NDX_FULL.SELECT TYear,TMonth,AVG(ChangeClose)AS AverageFridayChangeClose FROM NDX_Full GROUP BY TYear,TMonth,TMonthNumber ORDER BY TYear,TMonthNumber;E.The average ChangeClose grouped by TYear,TQuarter,TMonth shown in descending order of the average(you will have to

21、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 AverageChangeClose FROM NDX GROUP BY TYear,T

22、Quarter,TMonth ORDER 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 the months in calendar order,we would have to us

23、e 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 give a name to the difference in order to sort by it).Show

24、TYear,TQuarter,and TMonth.SELECT TYear,TQuarter,TMonth,(MAX(ChangeClose)MIN(ChangeClose)AS DifChangeClose FROM NDX GROUP BY TYear,TQuarter,TMonth ORDER BY DifChangeClose DESC;Unfortunately,as discussed above,Microsoft Access cannot process the ORDER BY clause correctly because it contains an aliased

25、 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 only groups for which the average is positive.SELECT TYear,AVG(ChangeClose

26、)AS AverageChangeClose FROM NDX GROUP BY TYear HAVING AVG(ChangeClose)0 ORDER BY AverageChangeClose DESC;Unfortunately,as discussed abve,Microsoft Access cannot process the ORDER BY clause correctly because it contains an aliased computed result.The correct result,obtained from SQL Server 2008,is:H.

27、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 a conversion function to convert the numerical day of the month to a character

28、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 month,we could use the NDX_Full table,which has a numeric value.We would need to us

29、e 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 DisplayDate FROM NDX WHERE TDayOfMonth=25 AND TMonth=September AND TYear=2001;The SQL Server 2008 result is:The SQL Statement

30、using Microsoft Access 2007 character string functions is:SELECT CStr(TDayOfMonth)+/+TMonth+/+TYear AS DisplayDate FROM NDX WHERE =25 AND =September AND =2001;The Microsoft Access 2007 result is:It is possible that volume(the number of shares traded)has some correlation with the direction of the sto

31、ck market.Use the SQL you have learned in this chapter to investigate that possibility.Develop at least five different SQL statements in your investigation.If volume is correlated with the direction of the stock market,this means that there should be either:(1)POSITIVE CORRELEATION:Higher volume whe

32、n the market closes higher,or(2)NEGATIVE CORRELATION:Higher volume when the market closes lower.When does the market close higher When is positive.SELECT TMonth,TDayOfMonth,TYear,ChangeClose FROM NDX WHERE ChangeClose 0;When does the market close lower When is negative.SELECT TMonth,TDayOfMonth,TYea

33、r,ChangeClose FROM NDX WHERE ChangeClose 0;SELECT AVG(ChangeClose)AS AvgNegativeChange FROM NDX WHERE ChangeClose 0;SELECT AVG(ChangeClose)AS AvgNegativeChange,AVG(Volume)AS AvgVolumeOnNegativeChange FROM NDX WHERE ChangeClose 100 AND =;E.List the Phone and FirstName of all customers whose first nam

34、e starts with B.The correct SQL-92 statement,which uses the wildcard%,is:SELECT Phone,FirstName FROM CUSTOMER WHERE FirstName LIKE B%;However,MS Access uses the wildcard*,which gives the following SQL statement:SELECT Phone,FirstName FROM CUSTOMER WHERE FirstName LIKE B*;F.List the Phone and FirstNa

35、me of all customers whose last name includes the characters,cat.The correct SQL-92 statement,which uses the wildcard%,is:SELECT Phone,FirstName FROM CUSTOMER WHERE LastName LIKE%cat%;However,MS Access uses the wildcard*,which give the following SQL statement:SELECT Phone,FirstName FROM CUSTOMER WHER

36、E LastName LIKE*cat*;G.List the Phone,FirstName,and LastName 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

37、.We could,off course,write statements to find 23 in the 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,LastName FROM CUSTOMER WHERE Phone LIKE _23%;However,MS Access uses the wildcards*and,which give

38、 the following SQL statement:SELECT Phone,FirstName,LastName FROM CUSTOMER WHERE Phone LIKE 23*;H.Determine the maximum and minimum TotalAmounts.注意：由于 ORDER 是 SQL 保留字，必须用 括起来.SELECT MAX(TotalAmt)AS MaxTotalAmount,MIN(TotalAmt)AS MinTotalAmount FROM ORDER;I.Determine the average TotalAmount.注意：由于 ORD

39、ER 是 SQL 保留字，必须用 括起来.SELECT AVG(TotalAmt)AS AvgTotalAmount FROM ORDER;J.Count the number of customers.SELECT Count(*)AS NumberOfCustomers FROM CUSTOMER;K.Group customers by LastName and then by FirstName.SELECT LastName,FirstName FROM CUSTOMER GROUP BY LastName,FirstName;L.Count the number of custom

40、ers having each combination of LastName and FirstName.SELECT LastName,FirstName,COUNT(*)AS Last_First_Combination_Count FROM CUSTOMER GROUP BY LastName,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 resu

41、lts sorted by LastName in ascending order and then FirstName in descending order.注意：由于 ORDER 是 SQL 保留字，必须用 括起来.SELECT FirstName,LastName FROM CUSTOMER WHERE CustomerID IN (SELECT CustomerNumber FROM ORDER WHERE TotalAmount 100)ORDER BY LastName,FirstName DESC;N.Show the FirstName and LastName of all

42、 customers who have had an order with TotalAmount greater than 100.Use a join.Present the results sorted by LastName in ascending order and then FirstName in descending order.注意：由于 ORDER 是 SQL 保留字，必须用 括起来.SELECT FirstName,LastName FROM CUSTOMER,ORDER WHERE =ORDER.CustomerNumber AND TotalAmount 100 O

43、RDER BY LastName,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.Present the results sorted by LastName in ascending order and then FirstName in descending order.注意：由于 ORDER 是 SQL 保留字，必须用 括起来.SELECT FirstName,Last

44、Name FROM CUSTOMER WHERE CustomerID IN (SELECT CustomerNumber FROM ORDER WHERE InvoiceNumber IN (SELECT InvoiceNumber FROM ORDER_ITEM WHERE Item=Dress Shirt)ORDER BY LastName,FirstName DESC;P.Show the FirstName and LastName of all customers who have had an order with an Item named“Dress Shirt”.Use a

45、 join.Present the results sorted by LastName in ascending order and then FirstName in descending order.注意：由于 ORDER 是 SQL 保留字，必须用 括起来.SELECT FirstName,LastName FROM CUSTOMER,ORDER,ORDER_ITEM WHERE =ORDER.CustomerNumber AND ORDER.InvoiceNumber=AND =Dress Shirt ORDER BY LastName,FirstName DESC;Q.Show t

46、he 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 in descending order.注意：由于 ORDER 是 SQL 保留字，必须用 括起来.SELECT FirstName,LastName,TotalAmount FRO

47、M CUSTOMER,ORDER WHERE =ORDER.CustomerNumber AND ORDER.InvoiceNumber IN (SELECT InvoiceNumber FROM ORDER_ITEM WHERE Item=Dress Shirt)ORDER BY LastName,FirstName DESC;第三章 关系模型和规范化（第三次作业）复习重点(1).定义：关系的术语。图(2).从 1NF-BCNF a.1NF-关系的特征-应用，指出哪里不满足。b.2NF-消除组合键。非主属性由整个主键决定（不存在非主属性对候选键的部分依赖）。R(A,B,N,O,P)不能有 A

48、-N。没有非主属性，就是 2NF。c.3NF-消除传递依赖。非主属性由非主属性决定。R(A,B,N,O,P)，不能有 N-P d.BCNF-每个决定因素都是候选键。-将表转化为 BCNF 的过程。图。(3).4NF：不存在多值依赖。Consider the table:STAFF_MEETING(EmployeeName,ProjectName,Date)The rows of this table record the fact that an employee from a particular project attended a meeting on the given date.As

49、sume that a project meets at most once per day.Also,assume that only one employee represents a given project,but that employees can be assigned to multiple projects.a.State the functional dependencies.Since there can only be one project meeting for a particular project per day,we have:(ProjectName,D

50、ate)EmployeeName Since there is only one employee assigned to the meetings for each project,we have:ProjectName EmployeeName b.Transform this table into one or more tables in BCNF.State the primary keys,candidate keys,foreign keys,and referential integrity constraints.STAFF_MEETING FUNCTIONAL DEPEND