数据库处理课后习题答案.docx
《数据库处理课后习题答案.docx》由会员分享，可在线阅读，更多相关《数据库处理课后习题答案.docx（93页珍藏版）》请在冰点文库上搜索。
数据库处理课后习题答案
《数据库处理》复习要点及参考答案
最近更新时间：
9/2/2018
第一章MicrosoftAccess2007（第一次作业）3
复习要点3
1.613
1.623
1.635
1.645
1.656
1.676
1.687
1.698
1.708
1.719
第二章结构化查询语言简介（第二次作业）10
复习要点10
2.6210
2.6317
Marcia干洗店项目练习20
第三章关系模型和规范化（第三次作业）28
复习重点28
3.5828
3.5931
Marcia干洗店项目练习36
第四章45
复习重点45
第五章E-R图（第四次作业）45
复习重点45
5.6345
Marcia干洗店项目53
第六章数据库设计（第五次作业）56
复习重点56
6.5656
Marcia洗衣店项目练习58
第七章数据库创建、视图、触发器（第六次作业）61
复习重点61
7.561
7.1062
7.1562
7.2063
7.2564
7.3565
Marcia干洗店项目66
第十章用SQLServer2008管理数据库触发器、存储过程83
第八章数据库再设计83
第九章管理多用户数据库85
第一章MicrosoftAccess2007（第一次作业）
复习要点
（1）.知识网络图
图1.18
（2）.基本的定义：
a.DBS:
=用户+数据库应用程序+DBMS+DB。
各个部分有什么作用？
b.元数据metadata
（3）.Access的使用-作业
1.61
CreateaMicrosoftAccessdatabasenamedWPC.accdb.
AnswerstotheProjectQuestions1.61-1.70arecontainedinthedatabaseDBPe11-IM-Ch01-WPC.accdb,whichisavailableonthetext’sWebsite（
ThedatabaseiscreatedasdescribedinAppendixA.Thetwotablestobecreatedare:
DEPARTMENT（DepartmentName,BudgetCode,OfficeNumber,Phone）
EMPLOYEE（EmployeeNumber,FirstName,LastName,Department,Phone,Email）
WhereAnunderlinedcolumnnameindicatesthetablekey（primarykey）ofthetable,andanitalicizedcolumnindicatesaforeignkeylinkingtwotables.
1.62
Figure1-26showsthecolumncharacteristicsfortheWPCDEPARTMENTtable.Usingthecolumncharacteristics,createtheDEPARTMENTtableintheWPC.accdbdatabase.
1.63
Figure1-27showsthedatafortheWPCDEPARTMENTtable.UsingDatasheetview,enterthedatashowninFigure1-27intoyourDEPARTMENTtable.
1.64
Figure1-28showsthecolumncharacteristicsfortheWPCEMPLOYEEtable.Usingthecolumncharacteristics,createtheEMPLOYEEtableintheWPC.accdbdatabase.
1.65
CreatetherelationshipandreferentialintegrityconstraintbetweenDEPARTMENTandEMPLOYEE.Enableenforcingofreferentialintegrityandcascadingofdataupdates,butdonotenablecascadingofdatafromdeletedrecords.
1.67
UsingtheMicrosoftAccessformwizard,createadatainputformfortheEMPLOYEEtableandnameitWPCEmployeeDataForm.Makeanyadjustmentsnecessarytotheformsothatalldatadisplayproperly.UsethisformtoentertherestofthedataintheEMPLOYEEtableshowninFigure1-29intoyourEMPLOYEEtable.
1.68
UsingtheAccessreportwizard,createareportnamedWedgewoodPacificCorporationEmployeeReportthatpresentsthedatacontainedinyourEMPLOYEEtablesortedfirstbyemployeelastnameandthenbyemployeefirstname.Makeanyadjustmentsnecessarytothereportsothatallheadingsanddatadisplayproperly.Printacopyofthisreport.
Toproducethereportasshownbelow,someworkintheReportDesignviewisnecessary–takethetimetoshowyourstudentshowtomodifyreportformatsinReportDesignview.
1.69
UsingtheMicrosoftAccessformwizard,createaformthathasallofthedatafrombothtables.Whenaskedhowyouwanttoviewyourdata,selectbyDEPARTMENT.Choosethedefaultoptionsforotherquestionsthatthewizardasks.Openyourformandpagethroughyourdepartments.
Toproducethereportasshownbelow,someworkintheFormDesignviewisnecessary–takethetimetoshowyourstudentshowtomodifyreportformatsinFormDesignview.
1.70
UsingtheAccessreportwizard,createareportthathasallofthedatafrombothtables.Whenaskedhowyouwanttoviewyourdata,selectbyDEPARTMENT.ForthedatacontainedinyourEMPLOYEEtableinthereport,specifythatitwillbesortedfirstbyemployeelastnameandthenbyemployeefirstname.Makeanyadjustmentsnecessarytothereportsothatallheadingsanddatadisplayproperly.Printacopyofthisreport.
Toproducethereportasshownbelow,someworkintheReportDesignviewisnecessary–takethetimetoshowyourstudentshowtomodifyreportformatsinReportDesignview.
1.71
Explain,tothelevelofdetailinthischapter,whatisgoingonwithinMicrosoftAccessinProjectQuestions1.67,1.68,1.69,and1.70.Whatsubcomponentcreatedtheformandreport?
Whereisthedatastored?
WhatroledoyouthinkSQLisplaying?
AccessusesSQLSELECTstatementstoquerythedatabasetablesforthedatatobedisplayedintheformsandthereport.Theresultsofthequeryarestoredinatemporarytablecreatedtoholdthisdata,andthistableisthesourceofthedatadisplayedintheformandthereport.SQLisusedtogatherthedataneededfordisplayintheformandreport.
第二章结构化查询语言简介（第二次作业）
复习要点
（1）.定义DDL、DML。
p32.
（2）.SQL的写法-基本、重要。
SELECT...FROM...WHERE...ORDERBY...GROUPBY...HAVING...IN...EXISTS...JOIN...ON
（3）.难点：
多表连接、相关子查询、谓词计算
（4）.发现数据模式-动脑、扩展。
例如题目2.63.
（5）.实验教材。
2.62
A.TheChangeCloseonFridays.
SELECTChangeClose
FROMNDX
WHERETDayOfWeeK='Friday';
B.
Theminimum,maximum,andaverageChangeCloseonFridays.
SELECTMIN（ChangeClose）ASMinFridayChangeClose,
MAX（ChangeClose）ASMaxFridayChangeClose,
AVG（ChangeClose）ASAverageFridayChangeClose
FROMNDX
WHERETDayOfWeeK='Friday';
C.TheaverageChangeClosegroupedbyTYear.ShowTYear.
SELECTTYear,AVG（ChangeClose）ASAverageChangeClose
FROMNDX
GROUPBYTYear
ORDERBYTYear;
D.TheaverageChangeClosegroupedbyTYearandTMonth.ShowTYearandTMonth.
SinceTYearandTMontharebeingdisplayed,itmakessensetosorttheresultsbyTYearandTMonthalthoughthisisnotexplicitlystatedinthequestion.
SELECTTYear,TMonth,
AVG（ChangeClose）ASAverageChangeClose
FROMNDX
GROUPBYTYear,TMonth
ORDERBYTYear,TMonth;
Unfortunately,thetableNDXdoesnotcontainanumericvalueofthemonth,soinordertosortthemonthscorrectly,weneedaTMonthNumberwhichhasacolumncontainingarepresentativenumberforeachmonth（January=1,February=2,etc.）.IntheDBPe11-NDX.accdbandDBPe11-IM-Ch02-NDX.accdbdatabases,thiscolumnisincludedinatablenamedNDX_FULL.
SELECTTYear,TMonth,
AVG（ChangeClose）ASAverageFridayChangeClose
FROMNDX_Full
GROUPBYTYear,TMonth,TMonthNumber
ORDERBYTYear,TMonthNumber;
E.
TheaverageChangeClosegroupedbyTYear,TQuarter,TMonthshownindescendingorderoftheaverage（youwillhavetogiveanametotheaverageinordertosortbyit）.ShowTYear,TQuarter,andTMonth.Notethatmonthsappearinalphabeticalandnotcalendarorder.Explainwhatyouneedtodotoobtainmonthsincalendarorder.
SELECTTYear,TQuarter,TMonth,
AVG（ChangeClose）ASAverageChangeClose
FROMNDX
GROUPBYTYear,TQuarter,TMonth
ORDERBYAverageChangeCloseDESC;
Unfortunately,asdiscussedabove,MicrosoftAccesscannotprocesstheORDERBYclausecorrectlywhenanSQLbuilt-infunctionisused.
Thecorrectresult,obtainedfromSQLServer2008,is:
Inordertoobtainthemonthsincalendarorder,wewouldhavetouseanumericalvalueforeachmonth（1,2,3,…,12）andsortbythosevalues.
F.ThedifferencebetweenthemaximumChangeCloseandtheminimumChangeClosegroupedbyTYear,TQuarter,TMonthshownindescendingorderofthedifference（youwillhavetogiveanametothedifferenceinordertosortbyit）.ShowTYear,TQuarter,andTMonth.
SELECTTYear,TQuarter,TMonth,
（MAX（ChangeClose）–MIN（ChangeClose））ASDifChangeClose
FROMNDX
GROUPBYTYear,TQuarter,TMonth
ORDERBYDifChangeCloseDESC;
Unfortunately,asdiscussedabove,MicrosoftAccesscannotprocesstheORDERBYclausecorrectlybecauseitcontainsanaliasedcomputedresult.
Thecorrectresult,obtainedfromSQLServer2008,is:
G.TheaverageChangeClosegroupedbyTYearshownindescendingorderoftheaverage（youwillhavetogiveanametotheaverageinordertosortbyit）.Showonlygroupsforwhichtheaverageispositive.
SELECTTYear,
AVG（ChangeClose）ASAverageChangeClose
FROMNDX
GROUPBYTYear
HAVINGAVG（ChangeClose）>0
ORDERBYAverageChangeCloseDESC;
Unfortunately,asdiscussedabve,MicrosoftAccesscannotprocesstheORDERBYclausecorrectlybecauseitcontainsanaliasedcomputedresult.
Thecorrectresult,obtainedfromSQLServer2008,is:
H.Displayasinglefieldwiththedateintheform:
day/monthy/year.Donotbeconcernedwithtrailingblanks.
ThesolutiontothisquestionrequiresthestudenttousetheDBMShelpfunctionorotherreferencestofigureoutaconversionfunctiontoconvertthenumericaldayofthemonthtoacharacterstringthatcanbecombinedwithotherdataalreadyincharacterformat.
ThetableNDXdoesnothaveanumericvalueformonth,sothenamesofthemonthswillappearinthesolution.Ifwewantthenumericvalueofthemonth,wecouldusetheNDX_Fulltable,whichhasanumericvalue.Wewouldneedtousethedatatypeconversiononthisfieldaswell.
TheSQLStatementusingSQLServer2008characterstringfunctionsis:
SELECTCAST（TDayOfMonthASChar
（2））+'/'+
TMonth+'/'+TYearASDisplayDate
FROMNDX
WHERETDayOfMonth=25
ANDTMonth='September'
ANDTYear='2001';
TheSQLServer2008resultis:
TheSQLStatementusingMicrosoftAccess2007characterstringfunctionsis:
SELECTCStr（TDayOfMonth）+'/'+
TMonth+'/'+TYearASDisplayDate
FROMNDX
WHERENDX.TDayOfMonth=25
ANDNDX.TMonth='September'
ANDNDX.TYear='2001';
TheMicrosoftAccess2007resultis:
2.63
Itispossiblethatvolume（thenumberofsharestraded）hassomecorrelationwiththedirectionofthestockmarket.UsetheSQLyouhavelearnedinthischaptertoinvestigatethatpossibility.DevelopatleastfivedifferentSQLstatementsinyourinvestigation.
Ifvolumeiscorrelatedwiththedirectionofthestockmarket,thismeansthatthereshouldbeeither:
（1）POSI
升级会员 