数据库处理课后习题答案.docx
- 文档编号:25798428
- 上传时间:2023-06-14
- 格式:DOCX
- 页数:88
- 大小:2.35MB
数据库处理课后习题答案.docx
《数据库处理课后习题答案.docx》由会员分享,可在线阅读,更多相关《数据库处理课后习题答案.docx(88页珍藏版)》请在冰豆网上搜索。
数据库处理课后习题答案
《数据库处理》复习要点及参考答案
最近更新时间:
4/11/2021
第一章MicrosoftAccess2007(第一次作业)
复习要点
(1).知识网络图
图
(2).基本的定义:
a.DBS:
=用户+数据库应用程序+DBMS+DB。
各个部分有什么作用
b.元数据metadata
(3).Access的使用-作业
CreateaMicrosoftAccessdatabasenamed.
AnswerstotheProjectQuestionsarecontainedinthedatabase,whichisavailableonthetext’sWebsite(databaseiscreatedasdescribedinAppendixA.Thetwotablestobecreatedare:
DEPARTMENT(DepartmentName,BudgetCode,OfficeNumber,Phone)
EMPLOYEE(EmployeeNumber,FirstName,LastName,Department,Phone,Email)
WhereAnunderlinedcolumnnameindicatesthetablekey(primarykey)ofthetable,andanitalicizedcolumnindicatesaforeignkeylinkingtwotables.
Figure1-26showsthecolumncharacteristicsfortheWPCDEPARTMENTtable.Usingthecolumncharacteristics,createtheDEPARTMENTtableinthedatabase.
Figure1-27showsthedatafortheWPCDEPARTMENTtable.UsingDatasheetview,enterthedatashowninFigure1-27intoyourDEPARTMENTtable.
Figure1-28showsthecolumncharacteristicsfortheWPCEMPLOYEEtable.Usingthecolumncharacteristics,createtheEMPLOYEEtableinthedatabase.
CreatetherelationshipandreferentialintegrityconstraintbetweenDEPARTMENTandEMPLOYEE.Enableenforcingofreferentialintegrityandcascadingofdataupdates,butdonotenablecascadingofdatafromdeletedrecords.
UsingtheMicrosoftAccessformwizard,createadatainputformfortheEMPLOYEEtableandnameitWPCEmployeeDataForm.Makeanyadjustmentsnecessarytotheformsothatalldatadisplayproperly.UsethisformtoentertherestofthedataintheEMPLOYEEtableshowninFigure1-29intoyourEMPLOYEEtable.
UsingtheAccessreportwizard,createareportnamedWedgewoodPacificCorporationEmployeeReportthatpresentsthedatacontainedinyourEMPLOYEEtablesortedfirstbyemployeelastnameandthenbyemployeefirstname.Makeanyadjustmentsnecessarytothereportsothatallheadingsanddatadisplayproperly.Printacopyofthisreport.
Toproducethereportasshownbelow,someworkintheReportDesignviewisnecessary–takethetimetoshowyourstudentshowtomodifyreportformatsinReportDesignview.
UsingtheMicrosoftAccessformwizard,createaformthathasallofthedatafrombothtables.Whenaskedhowyouwanttoviewyourdata,selectbyDEPARTMENT.Choosethedefaultoptionsforotherquestionsthatthewizardasks.Openyourformandpagethroughyourdepartments.
Toproducethereportasshownbelow,someworkintheFormDesignviewisnecessary–takethetimetoshowyourstudentshowtomodifyreportformatsinFormDesignview.
UsingtheAccessreportwizard,createareportthathasallofthedatafrombothtables.Whenaskedhowyouwanttoviewyourdata,selectbyDEPARTMENT.ForthedatacontainedinyourEMPLOYEEtableinthereport,specifythatitwillbesortedfirstbyemployeelastnameandthenbyemployeefirstname.Makeanyadjustmentsnecessarytothereportsothatallheadingsanddatadisplayproperly.Printacopyofthisreport.
Toproducethereportasshownbelow,someworkintheReportDesignviewisnecessary–takethetimetoshowyourstudentshowtomodifyreportformatsinReportDesignview.
Explain,tothelevelofdetailinthischapter,whatisgoingonwithinMicrosoftAccessinProjectQuestions,,,and.WhatsubcomponentcreatedtheformandreportWhereisthedatastoredWhatroledoyouthinkSQLisplaying
AccessusesSQLSELECTstatementstoquerythedatabasetablesforthedatatobedisplayedintheformsandthereport.Theresultsofthequeryarestoredinatemporarytablecreatedtoholdthisdata,andthistableisthesourceofthedatadisplayedintheformandthereport.SQLisusedtogatherthedataneededfordisplayintheformandreport.
第二章结构化查询语言简介(第二次作业)
复习要点
(1).定义DDL、DML。
p32.
(2).SQL的写法-基本、重要。
SELECT...FROM...WHERE...ORDERBY...GROUPBY...HAVING...IN...EXISTS...JOIN...ON
(3).难点:
多表连接、相关子查询、谓词计算
(4).发现数据模式-动脑、扩展。
例如题目.
(5).实验教材。
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.).Intheanddatabases,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
WHERE=25
AND='September'
AND='2001';
TheMicrosoftAccess2007resultis:
Itispossiblethatvolume(thenumberofsharestraded)hassomecorrelationwiththedirectionofthestockmarket.UsetheSQLyouhavelearnedinthischaptertoinvestigatethatpossibility.DevelopatleastfivedifferentSQLstatementsinyourinvestigation.
Ifvolumeiscorrelatedwiththedirectionofthestockmarket,thismeansthatthereshouldbeeither:
(1)POSITIVECORRELEATION:
Highervolumewhenthemarketcloseshigher,or
(2)NEGATIVECORRELATION:
Highervolumewhenthemarketcloseslower.
WhendoesthemarketclosehigherWhenispositive.
SELECTTMonth,TDayOfMonth,TYear,ChangeClose
FROMNDX
WHEREChangeClose>0;
WhendoesthemarketcloselowerWhenisnegative.
SELECTTMonth,TDayOfMonth,TYear,ChangeClose
FROMNDX
WHEREChangeClose<0;
Now,whataretheaveragepositiveandnegativechanges
SELECTAVG(ChangeClose)ASAvgPositiveChange
FROMNDX
WHEREChangeClose>0;
SELECTAVG(ChangeClose)ASAvgNegativeChange
FROMNDX
WHEREChangeClose<0;
Now,whataretheaveragevolumesassociatedwiththepositiveandnegativechanges
SELECTAVG(ChangeClose)ASAvgPositiveChange,
AVG(Volume)ASAvgVolumeOnPositiveChange
FROMND
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 处理 课后 习题 答案