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

1、数据库处理课后习题答案数据库处理复习要点及参考答案最近更新时间：4/11/2021第一章 Microsoft Access 2007（第一次作业）复习要点(1).知识网络图图(2).基本的定义：a.DBS:=用户+数据库应用程序+DBMS+DB。 各个部分有什么作用b.元数据metadata (3). Access 的使用- 作业Create a Microsoft Access database named .Answers to the Project Questions are contained in the database , which is available on the t

2、exts 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, LastName, Department, Phone, Email)Where An underlined column name indicates the table key (primary key) o

3、f 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 DEPARTMENT table in the database.Figure 1-27 shows the data for the WPC DEPARTMENT table. Using Dat

4、asheet 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 the database.Create the relationship and referential integrity constraint between DEPART

5、MENT 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 for the EMPLOYEE table and name it WPC Employee Data Form. Make any adjustments necess

6、ary 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 Wedgewood Pacific Corporation Employee Report that presents the data contained in y

7、our 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 the report as shown below, some work in the Report Design view is necessary take

8、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, select by DEPARTMENT. Choose the default options for other questions that the

9、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 view.Using the Access report wizard, create a report that has all of the

10、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 first name. Make any adjustments necessary to the report so that all he

11、adings 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.Explain, to the level of detail in this chapter, what is going o

12、n 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 playingAccess uses SQL SELECT statements to query the database tables for the data to be displayed in the forms and the report. The results

13、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.第二章 结构化查询语言简介（第二次作业）复习要点(1).定义DDL、DML。p32.(2).SQL 的写法 - 基本、重要。SELECT.FROM.WH

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

15、ngeClose) AS MaxFridayChangeClose, AVG (ChangeClose) AS AverageFridayChangeCloseFROM NDXWHERE TDayOfWeeK = Friday; C.The average ChangeClose grouped by TYear. Show TYear.SELECT TYear, AVG (ChangeClose) AS AverageChangeCloseFROM NDXGROUP BY TYearORDER BY TYear; D.The average ChangeClose grouped by TY

16、ear 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 BY TYear, TMonthORDER BY TYear, T

17、Month; 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 column is included in

18、 a table named NDX_FULL.SELECT TYear, TMonth, AVG (ChangeClose) AS AverageFridayChangeCloseFROM NDX_FullGROUP BY TYear, TMonth, TMonthNumberORDER BY TYear, TMonthNumber; E.The average ChangeClose grouped by TYear, TQuarter, TMonth shown in descending order of the average (you will have to give a nam

19、e 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 AverageChangeCloseFROM NDXGROUP BY TYear, TQ

20、uarter, 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 the months in calendar order, we would have t

21、o 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 give a name to the difference in order to sor

22、t by it). Show TYear, TQuarter, and TMonth.SELECT TYear, TQuarter, TMonth, (MAX (ChangeClose) MIN(ChangeClose) AS DifChangeCloseFROM NDXGROUP BY TYear, TQuarter, TMonthORDER BY DifChangeClose DESC;Unfortunately, as discussed above, Microsoft Access cannot process the ORDER BY clause correctly becaus

23、e 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 only groups for which the average is positive

24、.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 aliased computed result.The correct result, ob

25、tained 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 a conversion function to convert the numeri

26、cal 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 month, we could use the NDX_Full table, which

27、 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 TDayOfMonth = 25 AND TMonth = September AND TYear

28、= 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 =25 AND =September AND =2001;The Microsoft Access 2007 result is: It is possible that volume (the number of sh

29、ares 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 investigation.If volume is correlated with the direction of the stock market, this means that

30、 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 is positive.SELECT TMonth, TDayOfMonth, TYear, ChangeCloseFROM NDXWHERE ChangeClose 0;When doe

31、s the market close lower When is negative.SELECT TMonth, TDayOfMonth, TYear, ChangeCloseFROM NDXWHERE ChangeClose 0;SELECT AVG (ChangeClose) AS AvgNegativeChangeFROM NDXWHERE ChangeClose 0;Now, what are the average volumes associated with the positive and negative changesSELECT AVG (ChangeClose) AS AvgPositiveChange, AVG (Volume) AS AvgVolumeOnPositiveChangeFROM ND