数据库处理课后习题答案.docx
- 文档编号:8903388
- 上传时间:2023-05-15
- 格式:DOCX
- 页数:93
- 大小:2.21MB
数据库处理课后习题答案.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
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 处理 课后 习题 答案